JdbcTemplate Example to Insert Records

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.

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

JDBC Update Process

Example of JdbcTemplate update with statement creator

Example of JdbcTemplate update with statement setter

Example of JdbcTemplate update with Parameter Values

Download the Source Code

Leave A Reply Cancel Reply