0

I am struggling with a code that uses Spring's SimpleJdbcInsert. 

SimpleJdbcInsert jdbcInsert = (new SimpleJdbcInsert(transactionManager.getDataSource())
                .withTableName(MY_TABLE).usingGeneratedKeyColumns("my_table_id")).usingColumns(MY_COLUMNS);

This works fine when I use it on a connection that has the default schema as "PUBLIC", as in where I do not have any schemas set up. However, when I add a few schemas, and then make MY_SCHEMA1 as the default schema for the connection, this does not work any more. It does not find the table MY_TABLE at all.

It starts working if I add .withSchemaName(MY_SCHEMA1) to SimpleJdbcInsert. However, I do not have that option.

Please help.

Note: I am using this on H2 and DB2.

partha
  • 2,286
  • 5
  • 27
  • 37

2 Answers2

0

As far as I know, whenever a single DB is divided into multiple schemas, you have to specifically specify the schema you are pointing to(irrespective of the default one). I think, that is the reason it works fine when you use add .withSchemaName(MY_SCHEMA1). However, as you have mentioned you don not have this option, you can specify the schema name somewhere else, like in server config files, your application's properties file.

Yasha
  • 161
  • 1
  • 4
  • 13
  • if you have to find schema name at runtime, use DB links. create DB links with if-else conditions in your default schema to the tables you want to use accordingly. – Yasha Nov 27 '13 at 09:54
0

Dynamically invoking methods like withSchemaName() and useColumns() at arbitrary times is the main claim to usefulness of "query builder" classes like SimpleJdbcInsert: why are you using it for a constant?

If you insist on a final SimpleJdbcInsert variable, it can be a generic one (written like in the code example, without a schema) and you can then call withSchemaName() on it, after you find out which schema you want to use, to get a completely specified SimpleJdbcInsert you can execute.

Lorenzo Gatti
  • 1,260
  • 1
  • 10
  • 15
  • That was the idea. However, I could not figure out a way to programmatically find out the default schema of a given connection. I had raised a seperate question on http://stackoverflow.com/questions/20238520/how-to-get-the-default-schema-of-a-sql-connection – partha Nov 27 '13 at 10:04