HSQLDB In-memory database JDBC Template Query failing

Question

I have a unit test, that uses JDBCTemplate and an in-memory database. My HSQLDB Settings are :

jdbc.driver.className=org.hsqldb.jdbcDriver
jdbc.url=jdbc\:hsqldb\:mem\:test;DB_CLOSE_DELAY\=-1
jdbc.username=sa
jdbc.password=

I am creating a table and then trying to check if any data exists in it through JDBCTemplate before inserting a new record in it.

I am invoking JDBC query through JDBCTemplate as below:

String query = "select id from Person where id=?";
int isExist = jdbcTemplate.update(query, "1");

In my config xml, I have wired the dataSource as below:

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" lazy-init="default" autowire="default">
    <property name="driverClassName" value="${jdbc.driver.className}" />
    <property name="url" value="${jdbc.url}" />
    <property name="username" value="${jdbc.username}" />
    <property name="password" value="${jdbc.password}" />
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource"/>
</bean>

I am getting an error that says :

org.hsqldb.HsqlException : statement does not generate a row count

The same code works if i remove in memory database and actually connect to an Oracle Database but for tests I need in memory database.

Can you please help?

Thanks

Answer 1

You should use the JdbcTemplate.query method, the update method is for insert/delete/update statements. For example:

int isExist = jdbcTemplate.queryForInt(query, "1");