Spring - JdbcTemplate Example

Spring provides a simplification in handling database access with the Spring JDBC Template.


The Spring JDBC Template has the following advantages compared with standard JDBC.

• The JdbcTemplate class is the central class in the JDBC core package. 
• The Spring JDBC template allows to clean-up the resources automatically, It simplifies the use of JDBC since it handles the creation and release of resources. This helps to avoid common errors such as forgetting to always close the connection
• The Spring JDBC template converts the standard JDBC SQLExceptions into RuntimeExceptions. This allows the programmer to react more flexible to the errors. The Spring JDBC template converts also the vendor specific error messages into better understandable error messages.
• The Spring JDBC template offers several ways to query the database. queryForList() returns a list of HashMaps. The name of the column is the key in the hashmap for the values in the table.
• More convenient is the usage of ResultSetExtractor or RowMapper which allows to translates the SQL result direct into an object (ResultSetExtractor) or a list of objects (RowMapper). Both these methods will be demonstrated in the coding.

In Spring framework, the JdbcTemplate and JdbcDaoSupport classes are used to simplify the overall database operation processes. The last tutorial, Spring + Jdbc example will be reuse to compare the different between a project before and after JdbcTemplate support.

No JdbcTemplate

In the last example (Spring-Jdbc Example), you need to create many redundant codes (create connection , close the connection , handle the exception) in all the DAO persist methods – insert, update and delete. It’s not efficient, ugly, error prone and tedious.

private DataSource dataSource;

 public void setDataSource(DataSource dataSource) {

this.dataSource = dataSource;

}

public void insert(Customer customer){

String sql = "INSERT INTO CUSTOMER " +

"(ID, NAME, AGE) VALUES (?, ?, ?)";

Connection conn = null;

  try {

conn = dataSource.getConnection();

PreparedStatement ps = conn.prepareStatement(sql);

ps.setInt(1, customer.getCustId());

ps.setString(2, customer.getName());

ps.setInt(3, customer.getAge());

ps.executeUpdate();

ps.close();

} catch (SQLException e) {

throw new RuntimeException(e);

} finally {

if (conn != null) {

try {

conn.close();

} catch (SQLException e) {}

}

}

}

Use JdbcTemplate

By extended the JdbcDaoSupport, set the datasource and JdbcTemplate in your class is no longer required, you just need to wire the datasource into JdbcCustomerDAO. The JdbcDaoSupport will handle it, and you can get the JdbcTemplate by using a getJdbcTemplate() method.

public class JdbcCustomerDAO extends JdbcDaoSupport implements CustomerDAO

{

//no need to set datasource here

   public void insert(Customer customer){

String sql = "INSERT INTO CUSTOMER (ID, NAME, AGE) VALUES (?, ?, ?)";

getJdbcTemplate().update(sql, new Object[] { customer.getCustId(),

customer.getName(),customer.getAge()  

});

  }

Conclusion

 In Spring JDBC programming, It’s always recommend to use JdbcTemplate and JdbcDaoSupport to simplify the overall processes and make your code more reusable.

If you want to see the output, please refer previous example (Spring -Jdbc Example) and copy the above JdbcCustomerDAO and paste it in the previous example.