populate batch databases from spring properties , Spring Boot unit test

[harald michael]

I am setting the property to initilize batch schema to always, but is seems to be ignored. Running the script manually with @Sql  does create these tables
Below are the properties and some code to ilustrate the project. It is strange that the datasource porperties are processed and the datasource can be autowired.

Is there a way to have this property being considered without needing to create the tables by running the script?
Thank you.

#initialize batch schema 
spring.batch.jdbc.initialize-schema=always

#h2 database
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:myDb;DB_CLOSE_DELAY=-1
spring.datasource.username=
spring.datasource.password=
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

@SpringBootTest(classes = {TestConfig.class, PropertyTestConfig.class} 
@TestMethodOrder(OrderAnnotation.class)
//@Sql({ "classpath:org/springframework/batch/core/schema-h2.sql" }) 
class TestClass
{
//TestCases
....
}


@Configuration
@ComponentScan({ "xxx*" })
@EnableJpaRepositories(basePackages = "xxx", entityManagerFactoryRef = "entityManagerFactory")
public class TestConfig 
{
....

    // for @Value to work

    @Bean
    public static PropertySourcesPlaceholderConfigurer propertyConfigInDev()
    {
        try
        {
            final PropertySourcesPlaceholderConfigurer ppc = new PropertySourcesPlaceholderConfigurer();
            ppc.setLocations(ArrayUtils.addAll(
                     new PathMatchingResourcePatternResolver().getResources("classpath*:application.properties")
                     ));
            return ppc;
        } catch (final IOException e)
        {
            e.printStackTrace();
        }
        return null;
    }
	
	.....

    
    
    class BatchConfig extends DefaultBatchConfigurer
    {

        @PersistenceUnit(unitName = "entityManagerFactory")
        private EntityManagerFactory                   entityManagerFactory;
        
        //this does not work 
        @Override
        public void setDataSource(final DataSource dataSource) {
            super.setDataSource(dataSource);
        }

        
        @Override
        @Bean(name = "transactionManager")
        public PlatformTransactionManager getTransactionManager()
        {

            return new ChainedTransactionManager(
                    new JpaTransactionManager(this.entityManagerFactory));
        }
    }
	
}	

pom.xml dependencies for test scope:

	<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.batch</groupId>
			<artifactId>spring-batch-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.easymock</groupId>
			<artifactId>easymock</artifactId>
			<version>4.3</version>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.junit.vintage</groupId>
			<artifactId>junit-vintage-engine</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<scope>test</scope>
		</dependency>
		
	<dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-devtools</artifactId>
        <scope>test</scope>
    </dependency>

[Tim Holloway]

I'm not really sure what you mean. Spring Boot has the ability to run 2 SQL scripts at first launch where one script is intended to construct the schema and the other is intended to pre-load data. Those scripts have to be located at a certain pre-defined place in the project (which I'm too lazy to look up).

If I'm reading you correctly, you get what you want when you uncomment the @Sql annotation in your sample. But I'm pretty sure that the @Sql is not pointing to the standard location. So if you always keep your scripts at the place where your @Sql indicates instead of at the standard location, then, yes, I'd expect the default behavior (without @Sql) to not work.

[harald michael]

Yes, it works with the @Sql annotation. What I would like to have is creating the batch tables using only the "spring.batch.jdbc.initialize-schema=always"  property without @Sql annotations.
This property works in the main application, but in unit tests it is ignored, thus the need to run the script manually.

[Tim Holloway]

Ah. As I recall, there's a separate path for SQL scripts when you run the test (so you don't have to reference the production environment while testing).

I don't recall offhand where that is, though generally in Maven, stuff having to do with testing is located in directories under /src/test. You should be able to find the exact location in the Spring Boot docs, though.

NOTE: corrected Maven test path, Was badly mistyped.

[Himai Minh]

I think the sql file for test should be under src/test/src/resources folder.
Here is an example:
https://github.com/eugenp/tutorials/blob/master/persistence-modules/spring-boot-persistence/src/test/resources/import_employees.sql

https://github.com/eugenp/tutorials/blob/master/persistence-modules/spring-boot-persistence/src/test/java/com/baeldung/springbootinitialload/tests/SpringBootInitialLoadIntegrationTest.java

[harald michael]

Thank you for the answers, but I think I did not describe exactly what I am trying to achieve so I try again.
Yes, calling sql files works called with ScriptUtils.executeScript , with @Sql or automatically at the locations Spring is searching for them having the right name at the same time.

But I do not want to do it like this, I just want SpringBoot to use the "spring.batch.jdbc.initialize-schema=always"  property in application.properties (I have created a new one in test/resources) which is read instead of the one in main/resources.
The problem is that batch tables are not initialized through this property , thus it is ignored, despite having a PropertySourcesPlaceholderConfigurer pointing to the application.properties files as can be seen in the first post . So the question is how can I make SpringBoot to use this property and create batch tables?  And this without calling sql scripts.
Thank you.

[Tim Holloway]

If you have an application.properties located properly under src/test/resources, then running tests should be using that property file instead of the src/main/resources one.

Your best bet may therefore be to crank up the logging levels on Spring. especially Spring Boot and Spring JDBC and see if it doesn't report where properties are coming from, what their values are and what's being done with them.

[harald michael]

yes, that sounds a good plan, as there are for example spring.datasource.. properties which are considered whn runnign the tests.
Will let you know once I have some results.

[harald michael]

I was able to print out all properties from the different property sources and they are all there, below some of the properties from application.properties.  I have set debug to true in bootstrap.yml and also these two

logging.level.org.springframework.jdbc.core.JdbcTemplate=TRACE
logging.level.org.springframework.batch=TRACE

but in the logs it is not visible what's being done with them.

property source : Config resource 'class path resource [application.properties]' via location 'optional:classpath:/'

logging.level.org.springframework.jdbc.core.JdbcTemplate = TRACE

logging.level.org.springframework.batch = TRACE
spring.datasource.driverClassName = org.h2.Driver
spring.datasource.driver-class-name = org.h2.Driver
spring.datasource.url = jdbc:h2:mem:myDb;DB_CLOSE_DELAY=-1
spring.datasource.username =
spring.datasource.password =
spring.datasource.hikari.minimumIdle = 5
spring.datasource.hikari.maximumPoolSize = 2000
spring.datasource.hikari.idleTimeout = 30000
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto = create
spring.jpa.show-sql = false
spring.jpa.properties.hibernate.format_sql = true
spring.jpa.properties.hibernate.type = trace
spring.batch.jdbc.initialize-schema = always

Yes, I think it would be useful to find a way to see what is being done with the properties.

[Tim Holloway]

I'm a little lost here. Just realized that you're set up for Spring JPA, but you have a "spring.batch.jdbc.initialize-data" directive.

One thing to note, though, is that if you're working with a version of Spring Boot before 2.5, that property was named spring.batch.initialize-data. That is no "jdbc" in the option name.

[harald michael]

well according to the spring documentation this is the correct property : https://docs.spring.io/spring-boot/docs/current/reference/html/howto.html#howto.data-initialization.batch

also in this question someone says to use "spring.batch.jdbc.initialize-schema" and "spring.batch.initialize-schema is deprecated since 2.5.0 for removal in 2.7.0"
https://stackoverflow.com/questions/33249942/spring-batch-framework-auto-create-batch-table

This property works fine in the main application , the batch tables are created when it is set to 'always', just in the test there must be something with the autoconfiguration which is different.
The spring.datasource.. properties also worked only after a lot of research I found out that this  annotation was needed to be able to have the data source configured from these properties, otherwise it would also be ignored or overriden:

@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)

Maybe there is something similar for the batch property..

[Tim Holloway]

I just realized that Spring Batch apparently has its own set of database tables independent of the application's tables and that this is all about ensuring that those tables should be set up and in place, irrespective of how you handle your application tables.

There is a caveat here, though. If you have Spring Data set up to completely construct/destroy the application database, that would include the Spring Batch tables and their contents, so definitely they'd need to be (re-)constructed before use.

At this point it would seem to be most important that your Spring Batch JDBC initialization option name must match the version of Spring Batch that you are using. And it wouldn't hurt to have Maven list the dependencies and their versions as regards what goes into the build, as Maven can end up using a different version than what you expected.

[harald michael]

Well I understand your point, but I am not sure it applies, as  when using the same property in the main application it works and both Hibernate initializes its tables and also batch tables are initialized. I also saw that when the batch property is set to never and the hibernate one to 'create' , the Hibernate entity tables are recreated without affecting batch tables, which is good  I think.
But it looks like something is different in the test case, I think we should look into the   classes of  spring-boot-test-autonfigure-2.5.6.jar   , I saw there is the Autoconfigure stuff including jdbc and property related. I think it would be very useful if we can find a way to log what is being done with the properties as you wrote before, if you have an idea I would be glad to test it. I set logging.level.org.springframework=TRACE  , it logged indeed a lot of stuff, but there was nothing related to how properties are read / handled. As posted before, they are all fonud and read successfully.

Here is the pom with relevant dependncies to this topic with added versions:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.5.6</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>


	<properties>
		<java.version>11</java.version>
		<spring-cloud.version>2020.0.4</spring-cloud.version>
	</properties>




	<dependencies>


		<!-- Spring -->

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-batch</artifactId>  - 2.5.6
		</dependency>
		<dependency>
			<groupId>org.springframework.cloud</groupId>
			<artifactId>spring-cloud-starter-config</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.cloud</groupId>
			<artifactId>spring-cloud-starter-bootstrap</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>   - 2.5.6
		</dependency>
		<dependency>
			<groupId>org.springframework.batch</groupId>
			<artifactId>spring-batch-core</artifactId>  - 4.3.3
		</dependency>
		<dependency>
			<groupId>org.springframework.batch</groupId>
			<artifactId>spring-batch-integration</artifactId>
			<version>4.3.5</version>
		</dependency>

		<dependency>
			<groupId>org.springframework.data</groupId>
			<artifactId>spring-data-jpa</artifactId>   - 2.5.6
			<exclusions>
				<exclusion>
					<groupId>org.springframework.boot</groupId>
					<artifactId>spring-boot-starter-logging</artifactId>
				</exclusion>
			</exclusions>
		</dependency>
		<dependency>
	



		<!-- Hibernate -->
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-core</artifactId>  -5.4.32
		</dependency>




		<!-- Testing -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>   - 2.5.6
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.batch</groupId>
			<artifactId>spring-batch-test</artifactId>  - 4.3.3
			<scope>test</scope>

		<dependency>
			<groupId>org.easymock</groupId>
			<artifactId>easymock</artifactId>
			<version>4.3</version>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.junit.vintage</groupId>
			<artifactId>junit-vintage-engine</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<scope>test</scope>
		</dependency>
		
	<dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-devtools</artifactId>
        <scope>test</scope>
    </dependency>

	</dependencies>








</project>

[harald michael]

I have enabled this logging:  

logging.level.org.springframework.boot.autoconfigure=TRACE

  which gives the autoconfigure log.

Here is for example hat is logged for the "spring.datasource.url" property:

2022-07-15 11:26:32 [main] TRACE o.s.b.a.j.DataSourceAutoConfiguration$EmbeddedDatabaseCondition:86 - Condition DataSourceAutoConfiguration.EmbeddedDatabaseCondition on org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration$EmbeddedDatabaseConfiguration did not match due to EmbeddedDataSource spring.datasource.url is set


   DataSourceAutoConfiguration.EmbeddedDatabaseConfiguration:
      Did not match:
         - EmbeddedDataSource spring.datasource.url is set (DataSourceAutoConfiguration.EmbeddedDatabaseCondition)

The spring.batch.jdbc.initialize-schema  property is not present in this output log...  This is what I try to find out, why it is ignored..

I have set breakpoints in this class, but the debugger is not stopping there:  
org.springframework.boot.autoconfigure.batch.BasicBatchConfigurer

[Tim Holloway]

Well, I was rather afraid of this. It would appear that your configuration option name doesn't match the version of Spring Batch that you are actually running. Which is why I suggested checking the Maven Dependency diagnostics.

Stupid question: Are you running your Spring Batch tests like this: https://docs.spring.io/spring-batch/docs/current/reference/html/testing.html

I noted that there is a special annotation for Spring Batch testing and I would not be surprised to hear that it autonomously sets up its own private test database and tables to run the tests and then destroys them at the end of the tests.

[harald michael]

ok, I would be interested to know what makes you think the configuration option name doesn't match the version of Spring Batch .
I have added the 'maven-error-diagnostics' - is this the one you  mean for diagnostics?

Yes, I use the @SpringBatchTest annotation which provides the tables (a job repository object). But with or without this the property is still ignored.

[Tim Holloway]

The problem with a lot of systems like this is that they may simply ignore options that they do not understand. And since this is an "optional" option, it wouldn't say anything if the option name that it actually wanted was missing.

If you're asking about what I said regarding Maven, no, what I meant was the "mvn dependency:tree" goal. It will list what the actual versions of your dependencies are in case you thought you were getting something else. Which often happens when there are internal dependency version conflicts.

[harald michael]

ok, i picked here the ones which I found relvant to the question :

 +- org.springframework.boot:spring-boot-starter-batch:jar:2.5.6:compile
[INFO] |  +- org.springframework.boot:spring-boot-starter:jar:2.5.6:compile
[INFO] |  |  \- org.springframework.boot:spring-boot-starter-logging:jar:2.5.6:compile
[INFO] |  |     +- ch.qos.logback:logback-classic:jar:1.2.6:compile
[INFO] |  |     |  \- ch.qos.logback:logback-core:jar:1.2.6:compile
[INFO] |  |     +- org.apache.logging.log4j:log4j-to-slf4j:jar:2.14.1:compile
[INFO] |  |     \- org.slf4j:jul-to-slf4j:jar:1.7.32:compile
[INFO] |  \- org.springframework.boot:spring-boot-starter-jdbc:jar:2.5.6:compile
[INFO] |     \- com.zaxxer:HikariCP:jar:4.0.3:compile

+- org.springframework.boot:spring-boot-starter-actuator:jar:2.5.6:compile
[INFO] |  \- org.springframework.boot:spring-boot-actuator-autoconfigure:jar:2.5.6:compile
[INFO] |     \- org.springframework.boot:spring-boot-actuator:jar:2.5.6:compile

+- org.springframework.batch:spring-batch-core:jar:4.3.3:compile
[INFO] |  +- io.micrometer:micrometer-core:jar:1.7.5:compile
[INFO] |  |  +- org.hdrhistogram:HdrHistogram:jar:2.1.12:compile
[INFO] |  |  \- org.latencyutils:LatencyUtils:jar:2.0.3:runtime
[INFO] |  +- javax.batch:javax.batch-api:jar:1.0:compile
[INFO] |  +- org.codehaus.jettison:jettison:jar:1.2:compile
[INFO] |  +- org.springframework.batch:spring-batch-infrastructure:jar:4.3.3:compile
[INFO] |  +- org.springframework:spring-aop:jar:5.3.12:compile
[INFO] |  +- org.springframework:spring-beans:jar:5.3.12:compile
[INFO] |  +- org.springframework:spring-context:jar:5.3.12:compile
[INFO] |  +- org.springframework:spring-core:jar:5.3.12:compile
[INFO] |  |  \- org.springframework:spring-jcl:jar:5.3.12:compile
[INFO] |  \- org.springframework:spring-tx:jar:5.3.12:compile
[INFO] +- org.springframework.batch:spring-batch-integration:jar:4.3.5:compile
[INFO] |  +- org.springframework.integration:spring-integration-core:jar:5.5.5:compile
[INFO] |  |  \- io.projectreactor:reactor-core:jar:3.4.11:compile
[INFO] |  |     \- org.reactivestreams:reactive-streams:jar:1.0.3:compile
[INFO] |  +- org.springframework.retry:spring-retry:jar:1.3.1:compile
[INFO] |  \- org.springframework:spring-messaging:jar:5.3.12:compile

 +- org.springframework.boot:spring-boot-starter-test:jar:2.5.6:test
[INFO] |  +- org.springframework.boot:spring-boot-test:jar:2.5.6:test
[INFO] |  +- org.springframework.boot:spring-boot-test-autoconfigure:jar:2.5.6:test
[INFO] |  +- com.jayway.jsonpath:json-path:jar:2.5.0:test
[INFO] |  |  \- net.minidev:json-smart:jar:2.4.7:test
[INFO] |  |     \- net.minidev:accessors-smart:jar:2.4.7:test
[INFO] |  +- jakarta.xml.bind:jakarta.xml.bind-api:jar:2.3.3:compile
[INFO] |  +- org.assertj:assertj-core:jar:3.19.0:test
[INFO] |  +- org.hamcrest:hamcrest:jar:2.2:test
[INFO] |  +- org.junit.jupiter:junit-jupiter:jar:5.7.2:test
[INFO] |  |  +- org.junit.jupiter:junit-jupiter-api:jar:5.7.2:test
[INFO] |  |  +- org.junit.jupiter:junit-jupiter-params:jar:5.7.2:test
[INFO] |  |  \- org.junit.jupiter:junit-jupiter-engine:jar:5.7.2:test
[INFO] |  +- org.mockito:mockito-core:jar:3.9.0:test
[INFO] |  |  \- net.bytebuddy:byte-buddy-agent:jar:1.10.22:test
[INFO] |  +- org.mockito:mockito-junit-jupiter:jar:3.9.0:test
[INFO] |  +- org.skyscreamer:jsonassert:jar:1.5.0:test
[INFO] |  |  \- com.vaadin.external.google:android-json:jar:0.0.20131108.vaadin1:test
[INFO] |  +- org.springframework:spring-test:jar:5.3.12:test
[INFO] |  \- org.xmlunit:xmlunit-core:jar:2.8.3:test
[INFO] +- org.springframework.batch:spring-batch-test:jar:4.3.3:test
[INFO] |  +- junit:junit:jar:4.13.2:test
[INFO] |  |  \- org.hamcrest:hamcrest-core:jar:2.2:test
[INFO] |  \- org.springframework:spring-jdbc:jar:5.3.12:compile



+- com.h2database:h2:jar:1.4.200:test
[INFO] \- org.springframework.boot:spring-boot-devtools:jar:2.5.6:test
[INFO]    +- org.springframework.boot:spring-boot:jar:2.5.6:compile
[INFO]    \- org.springframework.boot:spring-boot-autoconfigure:jar:2.5.6:compile

[Tim Holloway]

OK. I now know what versions of what you are dealing with, but unfortunately the Spring Batch jar is pretty complex so I can only make guesses here.

One possibility is that when running tests, the Spring Batch job data is not being kept in your MySQL database, but rather in an in-memory HSQL database that is created when the test starts, then vanishes when the test ends. It's quite possible, since for tests you really wouldn't need a large job schedule database and tests work best when there's no stale old data to confuse them.

You should be able to confirm if this is what's happening since you'd be able to schedule test jobs and run them even though there were no permanent entries in the MySQL database. If scheduling fails, then that's not it and we'll have to look elsewhere.

I couldn't actually find the initialize-database keyword in any of its forms in the Spring Batch source code so I have no idea who's handling that and whether it works only for production or not.

[harald michael]

yes, tests are ran with the h2 in memory database, which works fine. As indicated above, the datasource is successfully created from the spring.datasource... properties by the Autoconfiguration components of Spring Boot / Spring Boot test.

The properties are read by Spring boot at start and then the Autoconfiguration functionality is used in an attempt to autoconfigure as much as possible. So what would help me here would be to find out where the differences are between autoconfiguring main application (where the property is considered) and the test .  As indicated above, the datasource is configured only after setting this annotation:

@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)

As you said before it would be helpful to find a log which says how the properties are handled, as they are all being read into the property sources.

[Tim Holloway]

I think you need to consider the Spring Batch persistent resources to be a "black box" and simply trust them to work as documented (if not, you yell at the people who created/maintain Spring Batch).

Or, in other words, code your application to use the Spring Batch API to query/modify/create the database tables relating to Spring Batch and use regular JPA for your actual application database data.

It's good that you concerned yourself about such things, but in practical terms, as long as you are not going to be doing development work on Spring Batch itself., I think you're better off not worrying about such details.