The Spring-JDBC component is a part of the Spring framework and is an abstraction on top of the standard Java JDBC API. It takes care of all the low-level API-calls and provides some base classes to implement the DAO-pattern.
The only work left to the developer is:
- Creation of the statement
- Extraction of the result
- Execution of the statement
Additionally all tasks are simplified to the minimum.
Configuration of Spring-JDBC
The configuration can be done by just defining two more beans in your spring context configuration. The first one is the datasource, providing the JDBC connections. The other one simplifies the configuration for your own DAO implementations.
<bean id=“dataSource” class=“org.apache.commons.dbcp.BasicDataSource”
destroy-method=“close”>
<property name=“driverClassName” value=“yourJdbcDriverClass”/>
<property name=“url” value=“yourUrlToTheDatabase”/>
<property name=“username” value=“user”/>
<property name=“password” value=“password”/>
</bean>
<bean id=“abstractBaseDAO” abstract=“true”
class=“org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport”>
<property name=“dataSource” ref=“dataSource”/>
</bean>
<bean id=“userDAO” parent=“abstractBaseDAO” class=“org.xyz.dao.UserDAO”/>
Now every bean, declaring the abstractBaseDAO as its parent, gets the dataSource injected. (i.e. the userDAO bean – remember the UserDAO has to extend the SimpleJdbcDaoSupport)
There are three DaoSupport classes, which serve one of three JdbcTemplates to their subclasses.
- JdbcTemplate the central class of the JDBC abstraction
- NamedParameterJdbcTemplate allows the use of named parameters
- SimpleJdbcTemplate allows the use of Java 5 features like generics and varargs
The SimpleJdbcTemplate builds on top of the NamedParameterJdbcTemplate and the NamedParameterJdbcTemplate build on top of the JdbcTemplate.
Creation of the statement
By using the SimpleJdbcTemplate you are able to use named parameters for your statement.
String sql = “SELECT * FROM USERS WHERE ID = :id”;
The value for the named parameters can be defined in different ways, like a Map:
Map<String, Object> params = new HashMap<String, Object>();
params.put(“id”, Integer.valueOf(1));
or a SqlParameterSource:
SqlParameterSource params = new MapSqlParameterSource(“id”,
Integer.valueOf(1));
SqlParameterSource params = new BeanPropertySqlParameterSource(user);
The SqlParameterSource is an interface for defining named parameters. One implementation is the MapSqlParameterSource which simply holds a map of defined parameters.
The BeanPropertySqlParameterSource for example, obtains the parameter values from the beanproperties and maps those to the named parameters.
Handling of the the returned result
There are several ways offered by the JdbcTemplate classes to handle the result. The SimpleJdbcTemplate serves the possibility to use a ParameterizedRowMapper. This is a generic interface to map one row into a new instance of the specified target class.
A simple implementation:
new ParameterizedRowMapper<User>() {
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
return new User(rs.getInt(“ID”), rs.getString(“USERNAME”));
}
};
Another already existing implementation is the ParameterizedBeanPropertyRowMapper, which maps the columns into the matching beanproperties.
Execution of the statement
The UserDAO which extends the SimpleJdbcDaoSupport inherits a method – getSimpleJdbcTemplate(). The SimpleJdbcTemplate offers a collection of methods to execute the statement.
queryForInt(…);
- returns an integer (i.e. for “SELECT COUNT(*) …” statements)
queryForObject(…);
- returns a single instance of the rowmappers type and throws an ‘IncorrectResultSizeDataAccessException’ if zero or more than one rows were found.
query(…);
- returns a ‘List’ of the rowmappers type
update(…);
- used for insert, update and delete statements
When using the ‘SimpleJdbcTemplate’ you are not tied to use only its methods. You can get an instance of one of the other JdbcTemplates too.
getNamedParameterJdbcOperations();
getJdbcOperations();
Fazit
The Spring JDBC Abstraction Layer is an easy to use framework around the standard JDBC API. You have multiple styles to choose to design your DAO – layer which affect the maintainability, but the performance too. For example the bean property reading rowmapper will never be as performant as a custom implemented rowmapper.