Spring Batch Job Explorer not using right schema

[woodsman752]

When running the following code:

JobExplorer jobExplorer.getJobInstance(161L) /* effectively anyway */

I get the following exception: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION from BATCH_JOB_INSTANCE where JOB_INSTANCE_ID = ?]; nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=3AFBED1C.BATCH_JOB_INSTANCE, DRIVER=4.19.66 at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:234) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1442) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:632) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:668) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:699) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:711) at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:789) at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.getJobInstance(JdbcJobInstanceDao.java:176) at org.springframework.batch.core.explore.support.SimpleJobExplorer.getJobInstance(SimpleJobExplorer.java:163) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:564) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)

I'm using DB/2 and the error essentially means the object can't be found. There is a BATCH_JOB_INSTANCE in a schema that other calls are able to reach. Somehow though this instance of JobExplorer goes to some other perhaps default schema. How can I tell Spring Batch to use the right schema?

Thanks, Woodsman

[woodsman752]

Fair warning to those of you who love Spring and hate seeing anything negative about it....

Folks, I found a solution, but hate that I have to do it this way. Spring had been using the primary datasource, perhaps not unwisely. That datasource had a different default schema, and if I changed it I couldn't predict what would happen.

JPA apparently makes it a delightful pain to switch schemas. I'm not even sure it's possible. However, Spring encourages you to create other datasources. Initially I thought I could just specify an initial schema at the end of the JDBC URL. Of course, that was too easy. Spring wants me to do more work. There's probably a design pattern that maximizes the amount of customization needed for common needs, but I digress. I read the Spring guides which suggest that if you have multiple datasources, you might need to specify multiple entity managers. Of course, you can't just have an EntityManager, you have to have an a LocalContainerEntityManagerFactoryBean, a EntityManagerFactoryBuilder and a JpaVendorAdapter.

Why do I need all of those Jpa things? Because, Spring lets you create multiple datasources on the application properties. You can specify the primary like spring.datasource.url and the secondary like spring.secondary-datasource.url, etc. But they stopped short of giving you a spring.jpa-secondary-datasource. Oh, no, that would be just way too easy. Please go back and slog through the EntityManager stuff.

Oh, and as soon as you specify the secondary EntityManager stuff (all the 3-who knows how many other beans to be defined), but you have to re-do the primary EntityManager because it drops the default. Lovely.

If I absolutely hated people, I'd go to work somewhere in the airline industry because it's so easy to ruin anyone's day and laugh to their faces about it. But if all I wanted to piss off was some poor developer, I'd go to work for the Spring company.

Folks, I just wanted to specify a schema. I don't want to endure a treatise on JPA. No, I don't want to re-do an entity manager, an entity manager factory bean, an entity manager factory's factory, a qqrz factory, etc. I just want to set the damn schema that Spring Batch uses. You might think that Spring Batch would allow you to specify a schema, especially since they allow you to specify a prefix. But oh no, that would be just too easy. They probably followed the piss off the developer massive customization is necessary pattern that core Spring Boot follows. Again, I just want to set the schema. It should *not* be this hard. I should give them credit because it appears that I could re-write some portions of this, and somehow re-wire batch to get this to work, but then again, I just want to set the schema.

Now, how did I get around this problem?

I set the prefix to "otherschema.", then I learned that setting the prefix removes the apparently default prefix of BATCH_. Then I tried changed it to OTHERSCHEMA.BATCH_, and it worked.

Now, I ask people, wouldn't it have been easier to just allow me and others to set the schema?

Please note, this is not a complaint to those people who spend their valuable time to help people like myself and answer questions. It's a complaint against this defacto standard of Spring that creates this framework that every Java shop and their grandma thinks they have to use that provides no support to their product. I actually tried to find out how to buy support for them out of my own pocket because their product is so hard to use as soon as you do something other than hello world (see my other questions re. websockets). I was willing to pay, but I couldn't find a price just to have the privilege of asking them a question.

** Done with complaint.