Spring Note: SqlUpdate

The org.springframework.jdbc.object.SqlUpdate class is another tool that I use a lot. This class encapsulates INSERT, UPDATE, and DELETE queries as beans defined within your application context. In most situations, you will be using the SqlUpdate class as-is with multiple configurations within a DAO's context. Since the class is not abstract, it is quite easy to extend for creating dummy/mock instances for testing.

ITSpringJdbc.java

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration
public class ITSpringJdbc {
  @Resource SqlUpdate updateBean;
  @Resource JdbcTemplate jdbcTemplate;

  @Transactional
  @Test public void testUpdate() {
    final String[] args = { "T" };
    updateBean.update(args);
    final int count = jdbcTemplate.queryForInt(
      "select count(*) from DUAL where dummy = 'T'");
    assertThat(count, is(1));
  }
}

ITSpringJdbc-context.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:util="http://www.springframework.org/schema/util"
  xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd">

  <bean id="dataSource.properties"
    class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
    <property name="locations">
      <list>
        <value>classpath:connection.properties</value>
      </list>
    </property>
  </bean>

  <bean id="dataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="${driverClassName}" />
    <property name="url" value="${uri}" />
    <property name="username" value="${username}" />
    <property name="password" value="${password}" />
  </bean>

  <bean id="updateBean"
    class="org.springframework.jdbc.object.SqlUpdate">
    <property name="dataSource" ref="dataSource"/>
    <property name="sql">
      <value>
        insert into DUAL (dummy) values (?) 
      </value>
    </property>
    <property name="types">
      <list value-type="java.lang.Integer">
        <util:constant
          static-field="java.sql.Types.VARCHAR" />
      </list>
    </property>
  </bean>

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