How To Use JdbcTemplate In Spring Framework
Posted By : Gourav Kumar | 30-Jul-2019
In this blog we are going to learn, how can we use JdbcTemplate class to provide spring database integration using xml based configuration.
JdbcTemplate class present into org.springframework.jdbc.core package and it accepts org.springframework.jdbc.datasource.DriverManagerDataSource object to establish connection with particular database which require information pass into DriverManagerDataSource class through dependency injection in applicationContext.xml such as driver class name, url, username and password.
JdbcTemplate class most common methods to perform DML operations:
i) update(String sql, Object... args): This method is use to perform manipulation operation on table and pass arguments using varargs such as insert, update, delete.
ii) update(String sql, PreparedStatementSetter pss): This method is also use to perform manipulation operation using PreparedStatementSetter interface which is use to pass arguments through setXXX methods like PreparedStatement interface used into jdbc.
iii) query(String sql, ResultSetExtractor<T> rse): This method is use to retrive only single record from table using select sql query.
iv) query(String sql, RowMapper<T> rm): This method is use to retrive more than one records using RowMapper so we don't need to collect each retrived record from the table and we can automatically get List<T> of records.
v) query(String sql, Object[] args, RowMapper<T> rm): This method is use to retrive the records on the basis of passing arguments using select sql query and map all the records through RowMapper and return List<T> of records.
vi) query(String sql, Object[] args, ResultSetExtractor<T> rse): This method is use to retrive single record on the basis of passing arguments using select sql query and map the single record into ResultSetExtractor<T> and return particular bean object.
We need to perform following steps to use JdbcTemplate:
1) Add dependency into pom file
<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>x.x.x.RELEASE</version>
</dependency>
OR
add spring core jars and include one more jar with these jars spring-jdbc-x.x.x.RELEASE.jar.
2) In applicationContext.xml file
a) Add org.springframework.jdbc.datasource.DriverManagerDataSource class definition with its properties.
<bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="oracle.jdbc.OracleDriver"></property>
<property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"></property>
<property name="username" value="root"></property>
<property name="password" value="root"></property>
</bean>
b) Now org.springframework.jdbc.core.JdbcTemplate class definition accept DriverManagerDataSource object to inject into dataSource property.
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="ds"></property>
</bean>
3) Create Employee bean class
package com.oodles;
public class Employee {
private int id;
private String name;
private Gender gender;
private long salary;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Gender getGender() {
return gender;
}
public void setGender(Gender gender) {
this.gender = gender;
}
public long getSalary() {
return salary;
}
public void setSalary(long salary) {
this.salary = salary;
}
public String toString(){
return this.getId() + " :: " + this.getName() + " :: " + this.getGender().name() + " :: " + this.getSalary();
}
}
4) Use JdbcTemplate class methods into MainClass class
package com.oodles;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class MainClass {
public static void main(String... args) {
ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
JdbcTemplate jdbcTemplate = context.getBean("jdbcTemplate", JdbcTemplate.class);
//---------Add one employee into emptab----------
String sqlnsert = "insert into emptab (id, name, gender, salary) value(?,?,?,?)";
int count = jdbcTemplate.update(sql, 103, "Mohan singh", Gender.MALE.getValue(), 12000);
//or
int count = jdbcTemplate.update(sql, new PreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps) throws SQLException {
ps.setInt(1, 103);
ps.setString(2, "Mohan singh");
ps.setInt(3, Gender.MALE.getValue());
ps.setLong(4, 12000);
}
});
System.out.println("Number of inserted record :: "+count);
//-------Update employee record------------------
String sqlUpdate = "update emptab set name=?, gender=?, salary=? where id=?";
int count = jdbcTemplate.update(sql, "Monika singh", Gender.FEMALE.getValue(), 18500, 102);
//or
int count = jdbcTemplate.update(sql, new PreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps) throws SQLException {
ps.setString(1, "Monika singh");
ps.setInt(2, Gender.FEMALE.getValue());
ps.setLong(3, 18500);
ps.setInt(4, 102);
}
});
System.out.println("Number of updated records :: "+count);
//-------Delete employee record------------
String sqlDelete = "delete from emptab where id=?";
int count = jdbcTemplate.update(sql, 100);
//or
int count = jdbcTemplate.update(sql, new PreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps) throws SQLException {
ps.setInt(1, 100);
}
});
System.out.println("Number of deleted records :: "+count);
//-----All employee records------------
String sqlSelectAll = "select * from emptab";
List<Employee> employees = jdbcTemplate.query(sql, Employee.class);
for( Employee employee in employees ) {
System.out.println( employee );
}
//-----Single employee record-----------
String sqlSelect = "select * from emptab where id=?";
Employee emp = jdbcTemplate.query(sql, new Object[]{101}, new ResultSetExtractor<Employee>() {
@Override
public Employee extractData(ResultSet rs) throws SQLException, DataAccessException {
Employee obj = new Employee();
obj.setId(rs.getInt(1));
obj.setName(rs.getString(2));
obj.setGender(Gender.values()[rs.getInt(3)]);
obj.setSalary(rs.getLong(4));
}
});
System.out.println(emp);
}
}
5) Output:
Number of inserted record :: 1
Number of updated records :: 1
Number of deleted records :: 1
101 :: Amit ahuja :: MALE :: 10000
102 :: Monika singh :: FEMALE :: 18500
103 :: Mohit singh :: MALE :: 12000
101 :: Amit ahuja :: MALE :: 10000
Conclusion: We can use JdbcTemplate class different methods to perform DML operation on database table but before all database integration information you need to inject into DriverManagerDataSource class while configuration and then inject DriverManagerDataSource id or name value into JdbcTemplate while configuration using ref tag or attribute in applicationContext.xml file to interact with the database on the basis of above given example.
Cookies are important to the proper functioning of a site. To improve your experience, we use cookies to remember log-in details and provide secure log-in, collect statistics to optimize site functionality, and deliver content tailored to your interests. Click Agree and Proceed to accept cookies and go directly to the site or click on View Cookie Settings to see detailed descriptions of the types of cookies and choose whether to accept certain cookies while on the site.
About Author
Gourav Kumar
Gourav is a bright Web App Developer and has good knowledge of Core java, Spring and Hibernate and his hobbies listen and sing songs.