This article explains JdbcTemplate in Spring and using it to perform CRUD operations.
Prerequisite:
Creating Spring JDBC project using Annotation based configuration
Spring JdbcTemplate
Spring JdbcTemplate hides the complexities of database interactions and provide a simple interface to use.
It provides several methods for different database operations.
We have created an Employee table using Java in-memory H2 database using the following script :
CREATE TABLE `employee` ( `id` int(11) NOT NULL AUTO_INCREMENT, `age` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) )
You can refer the following article for doing the same using H2 database or use any RDBMS like Oracle, MYSQL, Sybase etc.
H2 database basics
Lets see some examples of performing CRUD operations on Employee table using JdbcTemplate.
JdbcTemplate for Insert, Update and Delete operations
JdbcTemplate provides several overloaded update() methods for insert, update and delete operations.
Here are some examples:
Insert a record in Employee table using JdbcTemplate
String sql = "insert into employee(age,name) values(?,?)";
jdbcTemplate.update(sql,new Object[]{emp.getAge(),emp.getName()});
Update a record in employee table using JdbcTemplate
String sql = "update employee set age = ? where id = ?";
jdbcTemplate.update(sql,new Object[]{emp.getAge(),emp.getId()});
Delete a record from Employee table using JdbcTemplate
String sql = "delete from employee where id = ?";
jdbcTemplate.update(sql,new Object[]{id});
JdbcTemplate for Querying data
JdbcTemplate offers various methods like query(), queryForInt(), queryForLong(),
queryForObject(), queryForList(), queryForMap(), and queryForRowSet() methods that we can use to query data.
Here are some examples:
Read an Int value from Employee table using JdbcTemplate
String sql = "select age from employee where id = ?";
int age = jdbcTemplate.queryForInt(sql,new Object[]{id});
Similarly, we can use jdbcTemplate.queryForLong() for reading a long value.
Read a String value from Employee table using JdbcTemplate
String sql = "select name from employee where id = ?";
String name = jdbcTemplate.queryForObject(sql,new Object[]{id},String.class);
Read an Employee record from Employee table using JdbcTemplate
String sql = "select * from employee where id = ?";
Employee emp = jdbcTemplate.queryForObject(sql,new Object[]{id}, new RowMapper<Employee>(){
public Employee mapRow(ResultSet rs, int rownum)
throws SQLException {
Employee emp = new Employee();
emp.setId(rs.getInt("id"));
emp.setAge(rs.getInt("age"));
emp.setName(rs.getString("name"));
return emp;
}
});
Read all Employee records from Employee table using JdbcTemplate
public List<Employee> getAllEmployees(){
String sql = "select * from employee";
List<Employee> empList = jdbcTemplate.query(sql, new RowMapper<Employee>(){
public Employee mapRow(ResultSet rs, int rownum)
throws SQLException {
Employee emp = new Employee();
emp.setId(rs.getInt("id"));
emp.setAge(rs.getInt("age"));
emp.setName(rs.getString("name"));
return emp;
}
});
return empList;
}
Here is the complete EmployeeDAOImpl class for your reference :
package com.topjavatutorial.app;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
@Repository
public class EmployeeDAOImpl implements EmployeeDAO {
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate){
this.jdbcTemplate = jdbcTemplate;
}
public String getEmployeeName(long id) {
String sql = "select name from employee where id = ?";
String name = jdbcTemplate.queryForObject(sql,new Object[]{id},String.class);
return name;
}
public void addEmployee(Employee emp){
String sql = "insert into employee(age,name) values(?,?)";
jdbcTemplate.update(sql,new Object[]{emp.getAge(),emp.getName()});
}
public void updateEmployee(Employee emp){
String sql = "update employee set age = ? where id = ?";
jdbcTemplate.update(sql,new Object[]{emp.getAge(),emp.getId()});
}
public void deleteEmployee(long id){
String sql = "delete from employee where id = ?";
jdbcTemplate.update(sql,new Object[]{id});
}
public Employee getEmployee(int id){
String sql = "select * from employee where id = ?";
Employee emp = jdbcTemplate.queryForObject(sql,new Object[]{id}, new RowMapper<Employee>(){
public Employee mapRow(ResultSet rs, int rownum)
throws SQLException {
Employee emp = new Employee();
emp.setId(rs.getInt("id"));
emp.setAge(rs.getInt("age"));
emp.setName(rs.getString("name"));
return emp;
}
});
return emp;
}
public List<Employee> getAllEmployees(){
String sql = "select * from employee";
List<Employee> empList = jdbcTemplate.query(sql, new RowMapper<Employee>(){
public Employee mapRow(ResultSet rs, int rownum)
throws SQLException {
Employee emp = new Employee();
emp.setId(rs.getInt("id"));
emp.setAge(rs.getInt("age"));
emp.setName(rs.getString("name"));
return emp;
}
});
return empList;
}
}
JdbcTemplate to execute DDL statements
We can use execute() method of JdbcTemplate to execute DDL statements.
execute() method can be used to run any arbitrary sql statement.
Here is an example of running a DDL statement using JdbcTemplate.
public void CreateEmployeeTable(){
String sql = " CREATE TABLE Employee(ID INT PRIMARY KEY AUTO_INCREMENT, AGE INT,NAME VARCHAR(255)); ";
jdbcTemplate.execute(sql);
}
This creates the Employee table with columns id, age and name as provided. This statement can also be added to EmployeeDAOImpl above.
Reference
http://docs.spring.io
© 2016, https:. All rights reserved. On republishing this post, you must provide link to original post