Spring JbcTemplate mini tutorial

Spring JdbcTemplate is a powerful mechanism to connect to the database and execute SQL queries. It internally uses JDBC API but eliminates a lot of problems of JDBC API.

Problems of JDBC API

The problems of JDBC API are as follows:

• We need to write a lot of code before and after executing the query, such as creating a connection, statement, closing resultset, connection, etc.
• We need to perform exception handling code on the database logic.
• We need to handle the transaction.
• Repetition of all these codes from one to another database logic is a time-consuming task.

Advantage of Spring JdbcTemplate

Spring JdbcTemplate eliminates all the above-mentioned problems of JDBC API. It provides you methods to write the queries directly, so it saves a lot of work and time.

Spring Jdbc Approaches

Spring framework provides following approaches for JDBC database access:

• JdbcTemplate
• NamedParameterJdbcTemplate
• SimpleJdbcTemplate
• SimpleJdbcInsert and SimpleJdbcCall

JdbcTemplate class

It is the central class in the Spring JDBC support classes. It takes care of creation and release of resources such as creating and closing of connection object etc. So it will not lead to any problem if you forget to close the connection.

It handles the exception and provides the informative exception messages by the help of exception classes defined in the org.springframework.daopackage.

We can perform all the database operations by the help of JdbcTemplate class such as insertion, updation, deletion and retrieval of the data from the database.

Let's see the methods of spring JdbcTemplate class.

No.	Method	Description
1)	public int update(String query)	is used to insert, update and delete records.
2)	public int update(String query,Object... args)	is used to insert, update and delete records using PreparedStatement using given arguments.
3)	public void execute(String query)	is used to execute DDL query.
4)	public T execute(String sql, PreparedStatementCallbackaction)	executes the query by using PreparedStatement callback.
5)	public T query(String sql, ResultSetExtractor rse)	is used to fetch records using ResultSetExtractor.
6)	public List query(String sql, RowMapper rse)	is used to fetch records using RowMapper.

---

Example of Spring JdbcTemplate

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

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

Employee.java

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

1. package com.javatpoint;  
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 three methods saveEmployee(), updateEmployee and deleteEmployee().

1. package com.javatpoint;  
2. import org.springframework.jdbc.core.JdbcTemplate;  
3.   
4. public class EmployeeDao {  
5. private JdbcTemplate jdbcTemplate;  
6.   
7. public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {  
8.     this.jdbcTemplate = jdbcTemplate;  
9. }  
10.   
11. public int saveEmployee(Employee e){  
12.     String query="insert into employee values(  
13.     '"+e.getId()+"','"+e.getName()+"','"+e.getSalary()+"')";  
14.     return jdbcTemplate.update(query);  
15. }  
16. public int updateEmployee(Employee e){  
17.     String query="update employee set   
18.     name='"+e.getName()+"',salary='"+e.getSalary()+"' where id='"+e.getId()+"' ";  
19.     return jdbcTemplate.update(query);  
20. }  
21. public int deleteEmployee(Employee e){  
22.     String query="delete from employee where id='"+e.getId()+"' ";  
23.     return jdbcTemplate.update(query);  
24. }  
25.   
26. }