English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية

Spring Tutorial

example of PreparedStatement in Spring JdbcTemplate

with the help of the JdbcTemplate class execute()method, we can use Spring JdbcTemplate to execute parameterized queries. To use parameterized queries, we pass PreparedStatementCallback instance.

The syntax of the execute method to use parameterized queries

public T execute(String sql,PreparedStatementCallback<T>);

PreparedStatementCallback interface

It handles input parameters and output results. In this case, you do not need to worry about single quotes and double quotes.

methods of PreparedStatementCallback interface

It has only one method doInPreparedStatement. The syntax of the method is as follows: 

public T doInPreparedStatement(PreparedStatement ps)throws SQLException, DataAccessException

Example of using PreparedStatement in Spring

We assume that you have already installed Oracle10a table was created in the database G. 

create table employee(
id number(10),
name varchar2(100),
salary number(10)
);

Employee.java

This class includes3a class with a constructor, setter, and getter properties. 

package com.w3codebox;
public class Employee {
private int id;
private String name;
private float salary;
//no-arg and parameterized constructors
//getters and setters
}

EmployeeDao.java

It contains a property jdbcTemplate and a method saveEmployeeByPreparedStatement. You must understand the concept of anonymous classes to understand the code of this method. 

package com.w3codebox;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
public class EmployeeDao {
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
    this.jdbcTemplate = jdbcTemplate;
}
public boolean saveEmployeeByPreparedStatement(final Employee e){
    String query="insert into employee values(?,?,?)";
    return jdbcTemplate.execute(query,new PreparedStatementCallback<Boolean>(){
    @Override
    public boolean doInPreparedStatement(PreparedStatement ps)
            throws SQLException, DataAccessException {
        ps.setInt(1,e.getId());
        ps.setString(2,e.getName());
        ps.setfloat(3,e.getSalary());
        return ps.execute();
    }
    });
}
}

applicationContext.xml

DriverManagerDataSource Used to contain information about the database, such as the driver class name, connection URL, username, and password.

There is a JdbcTemplate class of type DriverManagerDataSource with a name}} datasource Therefore, we need to provide a reference to the DriverManagerDataSource object for the dataSource property in the JdbcTemplate class.

Here, we use the JdbcTemplate object in the EmployeeDao class, so we pass it through the setter method, but you can also use the constructor. 

<?xml version="1.0" encoding="UTF-8"?>
<beans
    xmlns="http://www.springframework.org/schema/beans
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance
    xmlns:p="http://www.springframework.org/schema/p
    xsi:schemaLocation="http://www.springframework.org/schema/beans 
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">
<bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@localhost:1521:xe" />
<property name="username" value="system" />
<property name="password" value="oracle" />
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="ds"></property>
</bean>
<bean id="edao" class="com.w3codebox.EmployeeDao">
<property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean>
</beans>

Test.java

This class retrieves Beans from applicationContext.xml file and calls the saveEmployeeByPreparedStatement() method. 

package com.w3codebox;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class Test {
public static void main(String[] args) {
    ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext.xml");
    EmployeeDao dao=(EmployeeDao)ctx.getBean("edao");
    dao.saveEmployeeByPreparedStatement(new Employee(108,"Amit",35000));
}
}
ResultSetExtractor Example | Retrieving Records via Spring JdbcTemplateSpring AOP AspectJ Xml Configuration Example