Embedded DataSource

<jdbc:embedded-database id="dataSource" generate-name="true" type="HSQL">
    <jdbc:script location="classpath:schema.sql"/>
    <jdbc:script location="classpath:test-data.sql"/>
</jdbc:embedded-database>

EmbeddedDatabase db = new EmbeddedDatabaseBuilder()
    .generateUniqueName(true)
    .setType(H2)
    .setScriptEncoding("UTF-8")
    .ignoreFailedDrops(true)
    .addScript("schema.sql")
    .addScripts("user_data.sql", "country_data.sql")
    .build();

db.shutdown()

Embedded DataSource

public class DataAccessIntegrationTestTemplate {
    private EmbeddedDatabase db;

    @Before
    public void setUp() {
        // creates an HSQL in-memory database populated from default scripts
        // classpath:schema.sql and classpath:data.sql
        db = new EmbeddedDatabaseBuilder()
                .generateUniqueName(true)
                .addDefaultScripts()
                .build();
    }

    @Test
    public void testDataAccess() {
        JdbcTemplate template = new JdbcTemplate(db);
        template.query( /* ... */ );
    }

    @After
    public void tearDown() {
        db.shutdown();
    }

}

Initializing DataSource

<jdbc:initialize-database data-source="dataSource">
    <jdbc:script location="classpath:com/foo/sql/db-schema.sql"/>
    <jdbc:script location="classpath:com/foo/sql/db-test-data.sql"/>
</jdbc:initialize-database>

<jdbc:initialize-database data-source="dataSource"
    enabled="#{systemProperties.INITIALIZE_DATABASE}">
    <jdbc:script location="..."/>
</jdbc:initialize-database>

<jdbc:initialize-database data-source="dataSource" ignore-failures="DROPS">
    <jdbc:script location="..."/>
</jdbc:initialize-database>

JdbcTemplate

• Takes care of transaction
• Closes resources
• Translates Exceptions

JdbcTemplate

EmbeddedDatabase dataSource = new EmbeddedDatabaseBuilder()
        .generateUniqueName(true)
        .setType(EmbeddedDatabaseType.H2)
        .build();

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

Querying for simple objects

int rowCount = jdbcTemplate.queryForObject("select count(*) from t_actor", Integer.class);

int countOfActorsNamedJoe = jdbcTemplate.queryForObject(
        "select count(*) from t_actor where first_name = ?", Integer.class, "Joe");

String lastName = jdbcTemplate.queryForObject(
        "select last_name from t_actor where id = ?",
        new Object[]{1212L}, String.class);

Querying for POJOs

Actor actor = jdbcTemplate.queryForObject(
        "select first_name, last_name from t_actor where id = ?",
        new Object[]{1212L},
        new RowMapper<Actor>() {
            public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
                Actor result = new Actor();
                result.setFirstName(rs.getString("first_name"));
                result.setLastName(rs.getString("last_name"));
                return result;
            }
        });

Actor actor = jdbcTemplate.queryForObject(
        "select first_name, last_name from t_actor where id = ?",
        new Object[]{1212L},
        (rs, rowNum) -> {
            Actor result = new Actor();
            result.setFirstName(rs.getString("first_name"));
            result.setLastName(rs.getString("last_name"));
            return result;
        });

Querying for collections

List<Actor> actors = jdbcTemplate.query(
        "select first_name, last_name from t_actor",
        new RowMapper<Actor>() {
            public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
                Actor actor = new Actor();
                actor.setFirstName(rs.getString("first_name"));
                actor.setLastName(rs.getString("last_name"));
                return actor;
            }
        });

List<Actor> actors = jdbcTemplate.query(
        "select first_name, last_name from t_actor",
        (rs, rowNum) -> {
            Actor actor1 = new Actor();
            actor1.setFirstName(rs.getString("first_name"));
            actor1.setLastName(rs.getString("last_name"));
            return actor1;
        });

public List<Map<String, Object>> getList() {
    return this.jdbcTemplate.queryForList("select * from mytable");
}

Updating

int numberOfInsertedActors = jdbcTemplate.update(
        "insert into t_actor (first_name, last_name) values (?, ?)",
        "Leonor", "Watling");

int numberOfChangedActors = jdbcTemplate.update(
        "update t_actor set last_name = ? where id = ?",
        "Banjo", 5276L);

int numberOfDeletedActors = jdbcTemplate.update(
        "delete from actor where id = ?",
        Long.valueOf(actorId));

Other operations

jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");


// Executes stored procedure
jdbcTemplate.update(
        "call SUPPORT.REFRESH_ACTORS_SUMMARY(?)",
        Long.valueOf(unionId));

NamedParameter

JdbcTemplate

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
int countOfActorsNamedJoe = jdbcTemplate.queryForObject(
        "select count(*) from t_actor where first_name = ?", Integer.class, "Joe");

NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource);

String sql = "select count(*) from T_ACTOR where first_name = :first_name";
SqlParameterSource namedParameters = new MapSqlParameterSource("first_name", "Joe");

int countOfActorsNamedJoe = template.queryForObject(sql, namedParameters, Integer.class);

SqlParameterSource

String sql = "select count(*) from T_ACTOR where first_name = :first_name";

SqlParameterSource namedParameters = new MapSqlParameterSource("first_name", "Joe");

int countOfActorsNamedJoe = template.queryForObject(sql, namedParameters, Integer.class);

String sql = "select count(*) from T_ACTOR where first_name = :first_name";

Map<String, Object> namedParameters = new HashMap<>();
namedParameters.put("first_name", "John";

int numberOfJohns = template.queryForObject(sql, namedParameters, Integer.class);

MapSqlParameterSource

MapSqlParameterSource namedParameters = new MapSqlParameterSource("firstName", "John");
namedParameters.addValue("lastName", "Smith");
namedParameters.addValue("age", 54);
namedParameters.addValues(someMap)

MapSqlParameterSource namedParameters = new MapSqlParameterSource("firstName", "John")
        .addValue("lastName", "Smith")
        .addValue("age", 54)
        .addValues(someMap);

BeanPropertySqlParameterSource

public class Actor {
    private Long id;
    private String firstName;
    private String lastName;

    public String getFirstName() {
        return this.firstName;
    }

    public String getLastName() {
        return this.lastName;
    }

    public Long getId() {
        return this.id;
    }

    // setters omitted...
}

public int countOfActors(Actor exampleActor) {
  String sql =
     "select count(*) from T_ACTOR where first_name = :firstName and last_name = :lastName";

  SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(exampleActor);

  return this.namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Integer.class);
}

SqlExceptionTranslator

• SQLException → org.springframework.dao.DataAccessException
• SQLErrorCodeSQLExceptionTranslator is default implementation
• SQLEfforCodesFactory translates error codes using sql-error-codes.xml

SqlExceptionTranslator

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN 2.0//EN" "http://www.springframework.org/dtd/spring-beans-2.0.dtd">

<!--
	- Default SQL error codes for well-known databases.
	- Can be overridden by definitions in a "sql-error-codes.xml" file
	- in the root of the class path.
	-
	- If the Database Product Name contains characters that are invalid
	- to use in the id attribute (like a space) then we need to add a property
	- named "databaseProductName"/"databaseProductNames" that holds this value.
	- If this property is present, then it will be used instead of the id for
	- looking up the error codes based on the current database.
	-->
<beans>

	<bean id="DB2" class="org.springframework.jdbc.support.SQLErrorCodes">
		<property name="databaseProductName">
			<value>DB2*</value>
		</property>
		<property name="badSqlGrammarCodes">
			<value>-007,-029,-097,-104,-109,-115,-128,-199,-204,-206,-301,-408,-441,-491</value>
		</property>
		<property name="duplicateKeyCodes">
			<value>-803</value>
		</property>
		<property name="dataIntegrityViolationCodes">
			<value>-407,-530,-531,-532,-543,-544,-545,-603,-667</value>
		</property>
		<property name="dataAccessResourceFailureCodes">
			<value>-904,-971</value>
		</property>
		<property name="transientDataAccessResourceCodes">
			<value>-1035,-1218,-30080,-30081</value>
		</property>
		<property name="deadlockLoserCodes">
			<value>-911,-913</value>
		</property>
	</bean>

	<bean id="Derby" class="org.springframework.jdbc.support.SQLErrorCodes">
		<property name="databaseProductName">
			<value>Apache Derby</value>
		</property>
		<property name="useSqlStateForTranslation">
			<value>true</value>
		</property>
		<property name="badSqlGrammarCodes">
			<value>42802,42821,42X01,42X02,42X03,42X04,42X05,42X06,42X07,42X08</value>
		</property>
		<property name="duplicateKeyCodes">
			<value>23505</value>
		</property>
		<property name="dataIntegrityViolationCodes">
			<value>22001,22005,23502,23503,23513,X0Y32</value>
		</property>
		<property name="dataAccessResourceFailureCodes">
			<value>04501,08004,42Y07</value>
		</property>
		<property name="cannotAcquireLockCodes">
			<value>40XL1</value>
		</property>
		<property name="deadlockLoserCodes">
			<value>40001</value>
		</property>
	</bean>

	<bean id="H2" class="org.springframework.jdbc.support.SQLErrorCodes">
		<property name="badSqlGrammarCodes">
			<value>42000,42001,42101,42102,42111,42112,42121,42122,42132</value>
		</property>
		<property name="duplicateKeyCodes">
			<value>23001,23505</value>
		</property>
		<property name="dataIntegrityViolationCodes">
			<value>22001,22003,22012,22018,22025,23000,23002,23003,23502,23503,23506,23507,23513</value>
		</property>
		<property name="dataAccessResourceFailureCodes">
			<value>90046,90100,90117,90121,90126</value>
		</property>
		<property name="cannotAcquireLockCodes">
			<value>50200</value>
		</property>
	</bean>

	<bean id="HSQL" class="org.springframework.jdbc.support.SQLErrorCodes">
		<property name="databaseProductName">
			<value>HSQL Database Engine</value>
		</property>
		<property name="badSqlGrammarCodes">
			<value>-22,-28</value>
		</property>
		<property name="duplicateKeyCodes">
			<value>-104</value>
		</property>
		<property name="dataIntegrityViolationCodes">
			<value>-9</value>
		</property>
		<property name="dataAccessResourceFailureCodes">
			<value>-80</value>
		</property>
	</bean>

	<bean id="Informix" class="org.springframework.jdbc.support.SQLErrorCodes">
		<property name="databaseProductName">
			<value>Informix Dynamic Server</value>
		</property>
		<property name="badSqlGrammarCodes">
			<value>-201,-217,-696</value>
		</property>
		<property name="duplicateKeyCodes">
			<value>-239,-268,-6017</value>
		</property>
		<property name="dataIntegrityViolationCodes">
			<value>-692,-11030</value>
		</property>
	</bean>

	<bean id="MS-SQL" class="org.springframework.jdbc.support.SQLErrorCodes">
		<property name="databaseProductName">
			<value>Microsoft SQL Server</value>
		</property>
		<property name="badSqlGrammarCodes">
			<value>156,170,207,208,209</value>
		</property>
		<property name="permissionDeniedCodes">
			<value>229</value>
		</property>
		<property name="duplicateKeyCodes">
			<value>2601,2627</value>
		</property>
		<property name="dataIntegrityViolationCodes">
			<value>544,8114,8115</value>
		</property>
		<property name="dataAccessResourceFailureCodes">
			<value>4060</value>
		</property>
		<property name="cannotAcquireLockCodes">
			<value>1222</value>
		</property>
		<property name="deadlockLoserCodes">
			<value>1205</value>
		</property>
	</bean>

	<bean id="MySQL" class="org.springframework.jdbc.support.SQLErrorCodes">
		<property name="badSqlGrammarCodes">
			<value>1054,1064,1146</value>
		</property>
		<property name="duplicateKeyCodes">
			<value>1062</value>
		</property>
		<property name="dataIntegrityViolationCodes">
			<value>630,839,840,893,1169,1215,1216,1217,1364,1451,1452,1557</value>
		</property>
		<property name="dataAccessResourceFailureCodes">
			<value>1</value>
		</property>
		<property name="cannotAcquireLockCodes">
			<value>1205</value>
		</property>
		<property name="deadlockLoserCodes">
			<value>1213</value>
		</property>
	</bean>

	<bean id="Oracle" class="org.springframework.jdbc.support.SQLErrorCodes">
		<property name="badSqlGrammarCodes">
			<value>900,903,904,917,936,942,17006,6550</value>
		</property>
		<property name="invalidResultSetAccessCodes">
			<value>17003</value>
		</property>
		<property name="duplicateKeyCodes">
			<value>1</value>
		</property>
		<property name="dataIntegrityViolationCodes">
			<value>1400,1722,2291,2292</value>
		</property>
		<property name="dataAccessResourceFailureCodes">
			<value>17002,17447</value>
		</property>
		<property name="cannotAcquireLockCodes">
			<value>54,30006</value>
		</property>
		<property name="cannotSerializeTransactionCodes">
			<value>8177</value>
		</property>
		<property name="deadlockLoserCodes">
			<value>60</value>
		</property>
	</bean>

	<bean id="PostgreSQL" class="org.springframework.jdbc.support.SQLErrorCodes">
		<property name="useSqlStateForTranslation">
			<value>true</value>
		</property>
		<property name="badSqlGrammarCodes">
			<value>03000,42000,42601,42602,42622,42804,42P01</value>
		</property>
		<property name="duplicateKeyCodes">
			<value>23505</value>
		</property>
		<property name="dataIntegrityViolationCodes">
			<value>23000,23502,23503,23514</value>
		</property>
		<property name="dataAccessResourceFailureCodes">
			<value>53000,53100,53200,53300</value>
		</property>
		<property name="cannotAcquireLockCodes">
			<value>55P03</value>
		</property>
		<property name="cannotSerializeTransactionCodes">
			<value>40001</value>
		</property>
		<property name="deadlockLoserCodes">
			<value>40P01</value>
		</property>
	</bean>

	<bean id="Sybase" class="org.springframework.jdbc.support.SQLErrorCodes">
		<property name="databaseProductNames">
			<list>
				<value>Sybase SQL Server</value>
				<value>SQL Server</value>
				<value>Adaptive Server Enterprise</value>
				<value>ASE</value> <!-- name as returned by jTDS driver -->
				<value>sql server</value> <!-- name as returned by jTDS driver -->
			</list>
		</property>
		<property name="badSqlGrammarCodes">
			<value>101,102,103,104,105,106,107,108,109,110,111,112,113,116,120,121,123,207,208,213,257,512</value>
		</property>
		<property name="duplicateKeyCodes">
			<value>2601,2615,2626</value>
		</property>
		<property name="dataIntegrityViolationCodes">
			<value>233,511,515,530,546,547,2615,2714</value>
		</property>
		<property name="transientDataAccessResourceCodes">
			<value>921,1105</value>
		</property>
		<property name="cannotAcquireLockCodes">
			<value>12205</value>
		</property>
		<property name="deadlockLoserCodes">
			<value>1205</value>
		</property>
	</bean>

	<!-- http://help.sap.com/saphelp_hanaplatform/helpdata/en/20/a78d3275191014b41bae7c4a46d835/content.htm -->
	<bean id="Hana" class="org.springframework.jdbc.support.SQLErrorCodes">
		<property name="databaseProductName">
			<value>SAP DB</value>
		</property>
		<property name="badSqlGrammarCodes">
			<value>
				257,259,260,261,262,263,264,267,268,269,270,271,272,273,275,276,277,278,
				278,279,280,281,282,283,284,285,286,288,289,290,294,295,296,297,299,308,309,
				313,315,316,318,319,320,321,322,323,324,328,329,330,333,335,336,337,338,340,
				343,350,351,352,362,368
			</value>
		</property>
		<property name="permissionDeniedCodes">
			<value>10,258</value>
		</property>
		<property name="duplicateKeyCodes">
			<value>301</value>
		</property>
		<property name="dataIntegrityViolationCodes">
			<value>461,462</value>
		</property>
		<property name="dataAccessResourceFailureCodes">
			<value>-813,-709,-708,1024,1025,1026,1027,1029,1030,1031</value>
		</property>
		<property name="invalidResultSetAccessCodes">
			<value>-11210,582,587,588,594</value>
		</property>
		<property name="cannotAcquireLockCodes">
			<value>131</value>
		</property>
		<property name="cannotSerializeTransactionCodes">
			<value>138,143</value>
		</property>
		<property name="deadlockLoserCodes">
			<value>133</value>
		</property>
	</bean>


</beans>

DataSource

• Generalized connection factory
• Hides connection pooling
• Obtained with JNDI or created manually (Apache Commons DBCP, C3P0)

DriverManagerDataSource

• For tests only
• Does not support pooling

DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("org.hsqldb.jdbcDriver");
dataSource.setUrl("jdbc:hsqldb:hsql://localhost:");
dataSource.setUsername("sa");
dataSource.setPassword("");

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

<context:property-placeholder location="jdbc.properties"/>

DBCP and C3P0

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="${jdbc.driverClassName}"/>
    <property name="url" value="${jdbc.url}"/>
    <property name="username" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
</bean>

<context:property-placeholder location="jdbc.properties"/>

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="${jdbc.driverClassName}"/>
    <property name="jdbcUrl" value="${jdbc.url}"/>
    <property name="user" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
</bean>

<context:property-placeholder location="jdbc.properties"/>

Batch

• Optimizes multiple queries
• Can split huge batches

List<Person> persons = getPersons();
jdbcTemplate.execute("create table persons (id int primary key auto_increment, " +
        "first_name varchar(20), last_name varchar(20), age int)");

jdbcTemplate.batchUpdate("insert into persons (first_name, last_name, age) values (?, ?, ?)",
  new BatchPreparedStatementSetter() {
    @Override
    public void setValues(PreparedStatement ps, int i) throws SQLException {
        Person person = persons.get(i);
        ps.setString(1, person.getFirstName());
        ps.setString(2, person.getLastName());
        ps.setInt(3, person.getAge());
    }

    @Override
    public int getBatchSize() {
        return persons.size();
    }
});

Batch (with array)

SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(actors.toArray());
int[] updateCounts = namedParameterJdbcTemplate.batchUpdate(
        "update t_actor set first_name = :firstName, last_name = :lastName where id = :id",
        batch);

SimpleJdbc

SimpleJdbcInsert

public class JdbcActorDao implements ActorDao {

    private JdbcTemplate jdbcTemplate;
    private SimpleJdbcInsert insertActor;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.insertActor = new SimpleJdbcInsert(dataSource).withTableName("t_actor");
    }

    public void add(Actor actor) {
        Map<String, Object> parameters = new HashMap<String, Object>(3);
        parameters.put("id", actor.getId());
        parameters.put("first_name", actor.getFirstName());
        parameters.put("last_name", actor.getLastName());
        insertActor.execute(parameters);
    }

    // ... additional methods
}

SimpleJdbcInsert with generated id's

public class JdbcActorDao implements ActorDao {

    private JdbcTemplate jdbcTemplate;
    private SimpleJdbcInsert insertActor;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.insertActor = new SimpleJdbcInsert(dataSource)
                .withTableName("t_actor")
                .usingGeneratedKeyColumns("id");
    }

    public void add(Actor actor) {
        Map<String, Object> parameters = new HashMap<String, Object>(2);
        parameters.put("first_name", actor.getFirstName());
        parameters.put("last_name", actor.getLastName());
        Number newId = insertActor.executeAndReturnKey(parameters);
        actor.setId(newId.longValue());
    }

    // ... additional methods
}

SimpleJdbcInsert with specified columns

public class JdbcActorDao implements ActorDao {

    private JdbcTemplate jdbcTemplate;
    private SimpleJdbcInsert insertActor;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.insertActor = new SimpleJdbcInsert(dataSource)
                .withTableName("t_actor")
                .usingColumns("first_name", "last_name")
                .usingGeneratedKeyColumns("id");
    }

    public void add(Actor actor) {
        Map<String, Object> parameters = new HashMap<String, Object>(2);
        parameters.put("first_name", actor.getFirstName());
        parameters.put("last_name", actor.getLastName());
        Number newId = insertActor.executeAndReturnKey(parameters);
        actor.setId(newId.longValue());
    }

    // ... additional methods

}

SimpleJdbcInsert with SqlParameterSource

public class JdbcActorDao implements ActorDao {

    private JdbcTemplate jdbcTemplate;
    private SimpleJdbcInsert insertActor;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.insertActor = new SimpleJdbcInsert(dataSource)
                .withTableName("t_actor")
                .usingGeneratedKeyColumns("id");
    }

    public void add(Actor actor) {
        SqlParameterSource parameters = new BeanPropertySqlParameterSource(actor);
        Number newId = insertActor.executeAndReturnKey(parameters);
        actor.setId(newId.longValue());
    }

    // ... additional methods

}