In this article, you will learn how to use JdbcTemplate
to implement a JDBC update operation. We will work through different versions of JdbcTemplate.update
from the most generic to the most simplest version.
JdbcTemplate
class declares a number of overloaded update()
methods to control the overall update process.
I will show you an example for each version of the update()
method.
JDBC Update Process
First let me define the JDBC update process.
In a JDBC update operation, you will have to perform the following tasks:
- Obtain a database connection from the data source.
- Create a PreparedStatement object from the connection.
- Bind the parameters to the PreparedStatement object.
- Execute the PreparedStatement object
Couple of more tasks which are not shown in the diagram are:
- Handle
SQLException
as it is a checked exception. - Clean up the statement object and connection.
Example of JdbcTemplate update with statement creator
JDBC is a very low-level API, but with the JDBC template, you will spend less time in writing the boilerplate code and more time working on your application logic.
In this example, we will use the callback object PreparedStatementCreator
. You implement this interface to skip the first task of obtaining a Connection
object. JdbcTemplate
takes care of that and the Connection
object is passed as an argument to the callback method. The implementation in the callback method will have to create a PreparedStatement
and then bind the parameter values.
SpringJdbcTemplateStmtCreatorExample:
package com.javarticles.jdbc.jdbctemplate; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.List; import java.util.Map; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.PreparedStatementCreator; public class SpringJdbcTemplateStmtCreatorExample { private JdbcTemplate jdbcTemplate; public static void main(String[] args) throws SQLException { ApplicationContext context = new ClassPathXmlApplicationContext( "applicationContext.xml"); SpringJdbcTemplateStmtCreatorExample springDsExample = (SpringJdbcTemplateStmtCreatorExample) context .getBean("springJtStmtCreatorExample"); springDsExample.insertEmployee(new Employee(1, "John", 35)); } public void insertEmployee(final Employee employee) throws SQLException { getJdbcTemplate().update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection con) throws SQLException { PreparedStatement stmt = con .prepareStatement("INSERT into EMPLOYEE(ID, NAME, AGE) VALUES (?, ?, ?)"); stmt.setInt(1, employee.getId()); stmt.setString(2, employee.getName()); stmt.setInt(3, employee.getAge()); return stmt; } }); List<Map<String, Object>> list = getJdbcTemplate().queryForList( "SELECT NAME FROM EMPLOYEE"); for (Map<String, Object> row : list) { System.out.println(row.get("name")); } } public JdbcTemplate getJdbcTemplate() { return jdbcTemplate; } public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } }
Before start running the example, you must configure the beans.
applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:jdbc="http://www.springframework.org/schema/jdbc" 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.xsd http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd"> <jdbc:initialize-database data-source="dataSource" enabled="true"> <jdbc:script location="classpath:db-schema.sql" /> </jdbc:initialize-database> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost/test" /> <property name="username" value="root" /> <property name="password" value="mnrpass" /> </bean> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource" /> </bean> <bean id="springJtStmtCreatorExample" class="com.javarticles.jdbc.jdbctemplate.SpringJdbcTemplateStmtCreatorExample"> <property name="jdbcTemplate"> <ref bean="jdbcTemplate" /> </property> </bean> </beans>
Output:
John
Example of JdbcTemplate update with statement setter
In this example, we will use the callback object PreparedStatementSetter
. You implement this interface to skip event the second task of creating PreparedStatement
.
In this version of update()
template method, you will pass a SQL statement and a PreparedStatementSetter
callback object as arguments. JdbcTemplate.update
method will create a PreparedStatement
object for you from your SQL statement. All you have to do in the callback method setValues(PreparedStatement ps)
is to bind your parameters to the PreparedStatement
object.
SpringJdbcTemplateStmtSetterExample:
package com.javarticles.jdbc.jdbctemplate; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.List; import java.util.Map; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.PreparedStatementSetter; public class SpringJdbcTemplateStmtSetterExample { private JdbcTemplate jdbcTemplate; public static void main(String[] args) throws SQLException { ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml"); SpringJdbcTemplateStmtSetterExample stmtSetterExample = (SpringJdbcTemplateStmtSetterExample) context.getBean("springJtStmtSetterExample"); stmtSetterExample.insertEmployee(new Employee(1, "John", 35)); } public void insertEmployee(final Employee employee) throws SQLException { String sql = "INSERT into EMPLOYEE(ID, NAME, AGE) VALUES (?, ?, ?)"; getJdbcTemplate().update(sql, new PreparedStatementSetter() { public void setValues(PreparedStatement stmt) throws SQLException { stmt.setInt(1, employee.getId()); stmt.setString(2, employee.getName()); stmt.setInt(3, employee.getAge()); } }); List<Map<String, Object>> list = getJdbcTemplate().queryForList("SELECT NAME FROM EMPLOYEE"); for (Map<String, Object> row : list) { System.out.println(row.get("name")); } } public JdbcTemplate getJdbcTemplate() { return jdbcTemplate; } public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } }
Example of JdbcTemplate update with Parameter Values
JdbcTemplate
has another version of update
method which just accepts the SQL statement and an object array as statement parameters. It takes care of everything else like creating a PreparedStatement
object from your SQL statement, binding the parameters for you. We don’t have to call any setter method on the PreparedStatement
. Based on the parameter type, JdbcTemplate
calls the correct setter method for us.
package com.javarticles.jdbc.jdbctemplate; import java.sql.SQLException; import java.util.List; import java.util.Map; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.jdbc.core.JdbcTemplate; public class SpringJdbcTemplateParameterExample { private JdbcTemplate jdbcTemplate; public static void main(String[] args) throws SQLException { ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml"); SpringJdbcTemplateParameterExample stmtParmExample = (SpringJdbcTemplateParameterExample) context.getBean("springJtParameterExample"); stmtParmExample.insertEmployee(new Employee(1, "John", 35)); } public void insertEmployee(final Employee employee) throws SQLException { String sql = "INSERT into EMPLOYEE(ID, NAME, AGE) VALUES (?, ?, ?)"; getJdbcTemplate().update(sql, new Object[]{employee.getId(), employee.getName(), employee.getAge()}); List<Map<String, Object>> list = getJdbcTemplate().queryForList("SELECT NAME FROM EMPLOYEE"); for (Map<String, Object> row : list) { System.out.println(row.get("name")); } } public JdbcTemplate getJdbcTemplate() { return jdbcTemplate; } public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } }
Download the Source Code
In this article, I showed you an examples of different versions of JdbcTemplate.update
. You can download the source code here: springtemplateInsert.zip