Spring JdbcTemplate Tutorial

In Spring JDBC development, you can use JdbcTemplate and JdbcDaoSupport classes to simplify the overall database operation processes.

Spring JdbcTemplate is a powerful mechanism to connect to the database and execute SQL queries. It internally uses JDBC api, but eliminates a lot of problems of JDBC API.

Problems of JDBC API

The problems of JDBC API are as follows:

  • We need to write a lot of code before and after executing the query, such as creating connection, statement, closing resultset, connection etc.
  • We need to perform exception handling code on the database logic.
  • We need to handle transaction.
  • Repetition of all these codes from one to another database logic is a time consuming task.

Main Advantage of Spring JdbcTemplate

Spring JdbcTemplate eliminates all the above mentioned problems of JDBC API. It provides you methods to write the queries directly.


Spring Jdbc Approaches

Spring framework provides following approaches for JDBC database access:

  • SimpleJdbcTemplate
  • SimpleJdbcInsert and SimpleJdbcCall
  • JdbcTemplate
  • NamedParameterJdbcTemplate

JdbcTemplate class

This is the central class in the JDBC core package. It simplifies the use of JDBC and helps to avoid common errors. It executes core JDBC workflow, leaving application code to provide SQL and extract results. This class executes SQL queries or updates, initiating iteration over ResultSets and catching JDBC exceptions and translating them to the generic, more informative exception hierarchy defined in theorg.springframework.dao package.


Note: The DataSource should always be configured as a bean in the application context, in the first case given to the service directly, in the second case to the prepared template.

Because this class is parameterizable by the callback interfaces and the SQLExceptionTranslator interface, there should be no need to subclass it.

All SQL operations performed by this class are logged at debug level, using "org.springframework.jdbc.core.JdbcTemplate" as log category.

Let's see the methods of spring JdbcTemplate class.

No. Method Description
1) public int update(String query) is used to insert, update and delete records.
2) public int update(String query,Object... args) is used to insert, update and delete records using PreparedStatement using given arguments.
3) public void execute(String query) is used to execute DDL query.
4) public T execute(String sql, PreparedStatementCallback action) executes the query by using PreparedStatement callback.
5) public T query(String sql, ResultSetExtractor rse) is used to fetch records using ResultSetExtractor.
6) public List query(String sql, RowMapper rse) is used to fetch records using RowMapper.

Example of Spring JdbcTemplate

Example Without JdbcTemplate

Witout JdbcTemplate, you have to create many redundant codes (create connection , close connection , handle exception) in all the DAO database operation methods – insert, update and delete. It just 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 " +
				"(CUST_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) {}
			}
		}
	}

2. Example With JdbcTemplate

With JdbcTemplate, you save a lot of typing on the redundant codes, becuase JdbcTemplate will handle it automatically.

	
		private DataSource dataSource;
	private JdbcTemplate jdbcTemplate;
		
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public void insert(Customer customer){
			
		String sql = "INSERT INTO CUSTOMER " +
			"(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";
				 
		jdbcTemplate = new JdbcTemplate(dataSource);
				
		jdbcTemplate.update(sql, new Object[] { customer.getCustId(),
			customer.getName(),customer.getAge()  
		});
				
	}

3. Example With JdbcDaoSupport

By extended the JdbcDaoSupport, set the datasource and JdbcTemplate in your class is no longer required, you just need to inject the correct datasource into JdbcCustomerDAO. 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 " +
			"(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";
				 
		getJdbcTemplate().update(sql, new Object[] { customer.getCustId(),
				customer.getName(),customer.getAge()  
		});
				
	}
	

	<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">
	
	<bean id="dataSource" 
         class="org.springframework.jdbc.datasource.DriverManagerDataSource">

		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/javatportaljava" />
		<property name="username" value="root" />
		<property name="password" value="password" />
	</bean>
	
</beans>
	

	
	<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

	<bean id="customerDAO" class="com.javatportal.customer.dao.impl.JdbcCustomerDAO">
		<property name="dataSource" ref="dataSource" />
	</bean>
	
</beans>