PreparedStatement in Spring JdbcTemplate

Example of PreparedStatement in Spring JdbcTemplate.

We can execute parameterized query using Spring JdbcTemplate by the help of execute()method of JdbcTemplate class. To use parameterized query, we pass the instance of PreparedStatementCallback in the execute method.

Syntax of execute method to use parameterized query

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

PreparedStatementCallback interface

It processes the input parameters and output results. In such case, you don't need to care about single and double quotes.

Method of PreparedStatementCallback interface

It has only one method doInPreparedStatement. Syntax of the method is given below:

1. public T doInPreparedStatement(PreparedStatement ps)throws SQLException, DataAccessException  

---

Example of using PreparedStatement in Spring

We are assuming that you have created the following table inside the Oracle10g database.

1. create table employee(  
2. id int(10),  
3. name varchar(100),  
4. salary int(10)  
5. );  

Employee.java

This class contains 3 properties with constructors and setter and getters.

1. package com.tpyyes;  
2.   
3. public class Employee {  
4. private int id;  
5. private String name;  
6. private float salary;  
7. //no-arg and parameterized constructors  
8. //getters and setters  
9. }  

EmployeeDao.java

It contains one property jdbcTemplate and one method saveEmployeeByPreparedStatement. You must understand the concept of annonymous class to understand the code of the method.

1. package com.tpyyes;  
2. import java.sql.PreparedStatement;  
3. import java.sql.SQLException;  
4. import org.springframework.dao.DataAccessException;  
5. import org.springframework.jdbc.core.JdbcTemplate;  
6. import org.springframework.jdbc.core.PreparedStatementCallback;  
7.   
8. public class EmployeeDao {  
9. private JdbcTemplate jdbcTemplate;  
10.   
11. public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {  
12.     this.jdbcTemplate = jdbcTemplate;  
13. }  
14.   
15. public Boolean saveEmployeeByPreparedStatement(final Employee e){  
16.     String query="insert into employee values(?,?,?)";  
17.     return jdbcTemplate.execute(query,new PreparedStatementCallback<Boolean>(){  
18.     @Override  
19.     public Boolean doInPreparedStatement(PreparedStatement ps)  
20.             throws SQLException, DataAccessException {  
21.               
22.         ps.setInt(1,e.getId());  
23.         ps.setString(2,e.getName());  
24.         ps.setFloat(3,e.getSalary());  
25.               
26.         return ps.execute();  
27.               
28.     }  
29.     });  
30. }    
31. }  

applicationContext.xml

The DriverManagerDataSource is used to contain the information about the database such as driver class name, connnection URL, username and password.

There are a property named datasource in the JdbcTemplate class of DriverManagerDataSource type. So, we need to provide the reference of DriverManagerDataSource object in the JdbcTemplate class for the datasource property.

Here, we are using the JdbcTemplate object in the EmployeeDao class, so we are passing it by the setter method but you can use constructor also.

1. <?xml version="1.0" encoding="UTF-8"?>  
2. <beans  
3.     xmlns="http://www.springframework.org/schema/beans"  
4.     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
5.     xmlns:p="http://www.springframework.org/schema/p"  
6.     xsi:schemaLocation="http://www.springframework.org/schema/beans   
7. http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">  
8.   
9. <bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">  
10. <property name="driverClassName" value="com.mysql.jdbc.Driver" />  
11. <property name="url" value="jdbc:mysql://localhost:3306/test?useUnicode=true" />  
12. <property name="username" value="root" />  
13. <property name="password" value="root" />  
14. </bean>  
15.   
16. <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">  
17. <property name="dataSource" ref="ds"></property>  
18. </bean>  
19.   
20. <bean id="edao" class="com.tpyyes.EmployeeDao">  
21. <property name="jdbcTemplate" ref="jdbcTemplate"></property>  
22. </bean>  
23. </beans>  

Test.java

This class gets the bean from the applicationContext.xml file and calls the saveEmployeeByPreparedStatement() method.

1. package com.tpyyes;  
2.   
3. import org.springframework.context.ApplicationContext;  
4. import org.springframework.context.support.ClassPathXmlApplicationContext;  
5. public class Test {    
6. public static void main(String[] args) {  
7.     ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext.xml");           
8.     EmployeeDao dao=(EmployeeDao)ctx.getBean("edao");  
9.     dao.saveEmployeeByPreparedStatement(new Employee(108,"Amit",35000));  
10. }  
11. }