Spring JdbcTemplate Tutorial

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

The problems with the JDBC API

The problems with the JDBC API are as follows:

Before and after executing the query, we need to write a lot of code, such as creating connections, statements, closing result sets, connections, etc. We need to execute exception handling code on the database logic. We need to handle transactions. It takes a lot of time to duplicate all this code from one database logic to another database logic.

Advantages of Spring JdbcTemplate

Spring JdbcTemplate eliminates all the problems of the above JDBC API. It provides methods to write queries directly, thereby saving a lot of work and time.

Spring Jdbc methods

Spring framework provides the following methods for JDBC database access:

JdbcTemplate NamedParameterJdbcTemplate SimpleJdbcTemplate SimpleJdbcInsert and SimpleJdbcCall

JdbcTemplate class

It is the central class for Spring JDBC support. It is responsible for creating and releasing resources, such as creating and closing connection objects, etc. Therefore, if you forget to close the connection, it will not cause any problems.

It handles exceptions and provides exception message information through org.springframework.dao help from exception classes defined in the package.

We can use the JdbcTemplate class to perform all database operations, such as inserting, updating, deleting from the database, and retrieving data.

Let's look at the methods of the Spring JdbcTemplate class.

Example of Spring JdbcTemplate

We assume that you have already installed Oracle10The following table was created in the g database.

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

Employee.java

This class includes3properties with constructors, setters, and getters.

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

EmployeeDao.java

It contains a property jdbcTemplate and three methods saveEmployee(), updateEmployee, and deleteEmployee().

package com.w3codebox;
import org.springframework.jdbc.core.JdbcTemplate;
public class EmployeeDao {
　　　　private JdbcTemplate jdbcTemplate;
　　　　public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
　　　　　　this.jdbcTemplate = jdbcTemplate;
　　　　}
　　　　public int saveEmployee(Employee e){
　　　　　　String query="insert into employee values('"+e.getId()+"','"+e.getName()+"','"+e.getSalary()+"');"
　　　　　　return jdbcTemplate.update(query);
　　　　}
　　　　public int updateEmployee(Employee e){
　　　　　　String query="update employee set　
　　　　　　name='"+e.getName()+"',salary='"+e.getSalary()+"' where id='"+e.getId()+"' ";
　　　　　　return jdbcTemplate.update(query);
　　　　}
　　　　public int deleteEmployee(Employee e){
　　　　　　String query="delete from employee where id='"+e.getId()+"' ";
　　　　　　return jdbcTemplate.update(query);
　　　　}
}

applicationContext.xml

DriverManagerDataSource used to hold 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 property. 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="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 the applicationContext.xml file and calls the saveEmployee() method. You can also call the updateEmployee() and deleteEmployee() methods by uncommenting the code.

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");
　　int status = dao.saveEmployee(new Employee(102,"Amit",35000));
　　System.out.println(status);
　　　　
　　/*int status = dao.updateEmployee(new Employee(102,"Sonoo",15000));
　　System.out.println(status);
　　*/
　　　　
　　/*Employee e = new Employee();
　　e.setId(102);
　　int status = dao.deleteEmployee(e);
　　System.out.println(status);*/
　　
}
}