JdbcSQLDataException: Parameter "#1" is not set; SQL statement runtime error on JdbcMetadataStore start

[igdianov]

In what version(s) of Spring Integration are you seeing this issue?

6.2.0-M3

Describe the bug

JdbcSQLDataException: Parameter "#1" is not set; SQL statement runtime error on JdbcMetadataStore start:

Caused by: org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [INSERT INTO INT_METADATA_STORE(METADATA_KEY, METADATA_VALUE, REGION)
SELECT ?, ?, ?
	FROM INT_METADATA_STORE
	WHERE METADATA_KEY=? AND REGION=?
HAVING COUNT(*)=0
]; Parameter "#1" is not set; SQL statement:
INSERT INTO INT_METADATA_STORE(METADATA_KEY, METADATA_VALUE, REGION)
SELECT ?, ?, ?
	FROM INT_METADATA_STORE
	WHERE METADATA_KEY=? AND REGION=?
HAVING COUNT(*)=0 [90012-224]
	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:91)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107)
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1581)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:404)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:472)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:482)
	at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:515)
	at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:522)
	at org.springframework.integration.jdbc.metadata.JdbcMetadataStore.start(JdbcMetadataStore.java:201)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:352)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:713)
	at org.springframework.integration.jdbc.metadata.JdbcMetadataStore$$SpringCGLIB$$0.start(<generated>)
	at org.springframework.context.support.DefaultLifecycleProcessor.doStart(DefaultLifecycleProcessor.java:258)
	... 35 more
Caused by: org.h2.jdbc.JdbcSQLDataException: Parameter "#1" is not set; SQL statement:
INSERT INTO INT_METADATA_STORE(METADATA_KEY, METADATA_VALUE, REGION)
SELECT ?, ?, ?
	FROM INT_METADATA_STORE
	WHERE METADATA_KEY=? AND REGION=?
HAVING COUNT(*)=0 [90012-224]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:658)
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:489)
	at org.h2.message.DbException.get(DbException.java:223)
	at org.h2.message.DbException.get(DbException.java:199)
	at org.h2.expression.Parameter.checkSet(Parameter.java:75)
	at org.h2.command.Prepared.checkParameters(Prepared.java:207)
	at org.h2.command.CommandContainer.query(CommandContainer.java:251)
	at org.h2.command.Command.executeQuery(Command.java:192)
	at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:101)
	at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:110)
	at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java)
	at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:459)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:389)
	... 45 more

To Reproduce

Create JdbcMetadataStore bean in the application context with H2 database:

@SpringBootTest
@TestPropertySource(properties = { "spring.sql.init.platform=h2" })
public class JdbcMessageStoreTests {

    @SpringBootApplication
    static class MessagesApplication {
        @Bean
        public ConcurrentMetadataStore metadataStore(JdbcTemplate jdbcTemplate) {
            return new JdbcMetadataStore(jdbcTemplate);
        }
    }

    @Test
    void contextLoads(){
    }
}

Expected behavior

Application with JdbcMetadataStore starts without errors.

Sample

There is a wrong query reference used for the count query in JdbcMetadataStore in the afterPropertiesSet() method:

spring-integration/spring-integration-jdbc/src/main/java/org/springframework/integration/jdbc/metadata/JdbcMetadataStore.java

Line 177 in 82d9ecf

this.countQuery = String.format(this.putIfAbsentValueQuery, this.tablePrefix);

[artembilan]

Correction: we talk about JdbcMetadataStore, but not JdbcMessageStore.
Feel free to provide the for the proper query reference .
Thank you!

[igdianov]

@artembilan The correct query reference should use this.countQuery instead of this.putIfAbsentValueQuery

spring-integration/spring-integration-jdbc/src/main/java/org/springframework/integration/jdbc/metadata/JdbcMetadataStore.java

Line 110 in 82d9ecf

private String countQuery = """