Catalog
Overview of Spring JDBC Framework
Using traditional JDBC to connect databases, it becomes very troublesome to write unnecessary code to handle exceptions, open and close database connections, etc. However, the Spring JDBC Framework is responsible for all low-level details starting with opening connections, preparing and executing SQL statements, handling exceptions, handling transactions, and finally closing connections.
Therefore, all you need to do is define the connection parameters and specify the SQL statements to be executed, and perform the required work for each iteration while retrieving data from the database.
Spring JDBC provides several methods and corresponding different classes to interface with the database. I'll take the classic and most popular approach to using the framework's JdbcTemplate class. This is the central framework class for managing all database communications and exception handling.
JdbcTemplate class
The JDBC template class performs SQL queries, update statements, stored procedure calls, iterates over the result set, and extracts the returned parameter values. It also captures JDBC exceptions and converts them into generic, more informative packages defined in org. spring framework. dao.
Once configured, instances of the JdbcTemplate class are thread-safe. Therefore, you can configure an instance of JdbcTemplate and then inject this shared reference safely into multiple DAOs.
When using JDBC template classes, it is common to configure a data source in the Spring configuration file, inject the shared data source bean into the DAO class, and create JdbcTemplate in the setter of the data source.
Configuring data sources
Let's create a database table Student in the database test. Let's assume that you are using MySQL database, and if you use any other database, you can change DDL and SQL queries accordingly.
CREATE TABLE Student( ID INT NOT NULL AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, PRIMARY KEY (ID) );
Now you need to provide a DataSource for the JDBC template so that it can be configured to obtain database privileges:
<bean id = "dataSource" class = "org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name = "driverClassName" value = "com.mysql.jdbc.Driver"/> <property name = "url" value = "jdbc:mysql://localhost:3306/TEST"/> <property name = "username" value = "root"/> <property name = "password" value = "password"/> </bean>
Data Access Object (DAO)
DAO represents data access objects and is usually used for database interaction. DAOs exist to provide a way to read and write data to a database, and they should expose this function by accessing their interfaces through other parts of the application.
DAO support in Spring makes it easy to use data access technologies such as JDBC, Hibernate, JPA or JDO in a consistent manner.
Execute SQL commands
Let's see how to use SQL and JDBCTemplate objects to perform CRUD (create, read, update and delete) operations on database tables.
Org. spring framework. jdbc. core. JdbcTemplate is the central class in the JDBC core package. It simplifies the use of JDBC and helps avoid common errors. It executes the core JDBC workflow, allowing application code to provide SQL and extract results. This class performs SQL queries or updates, initiates iterations on resultset, captures JDBC exceptions, and converts them into more general and useful exceptions defined in org. spring framework. dao.
Note: JdbcTemplate is thread safe. Thread safety will be introduced in subsequent articles.
Here's how to use it. For complete information, see Spring JdbcTemplate API Reference
Example
The following projects I build with Maven, create a Maven project, and update the pom.xml file as follows
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>top.ninwoo.spring</groupId>
<artifactId>build-demo</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<!-- Spring rely on -->
<!-- 1.Spring Core dependence -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>4.1.7.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>4.1.7.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.1.7.RELEASE</version>
</dependency>
<!-- 2.Spring dao rely on -->
<!-- spring-jdbc Included are some examples jdbcTemplate Tool class -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.1.7.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>4.1.7.RELEASE</version>
</dependency>
<!-- 3.Spring web rely on -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>4.1.7.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>4.1.7.RELEASE</version>
</dependency>
<!-- 4.Spring test Dependency: Easy to do unit testing and integration testing -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.6</version>
</dependency>
</dependencies>
</project>- StudentDAO.java: Define Student Data Interface
public interface StudentDAO {
/**
* This is the method to be used to initialize
* database resources ie. connection.
*/
public void setDataSource(DataSource ds);
/**
* This is the method to be used to create
* a record in the Student table.
*/
public void create(String name, Integer age);
/**
* This is the method to be used to list down
* a record from the Student table corresponding
* to a passed student id.
*/
public Student getStudent(Integer id);
/**
* This is the method to be used to list down
* all the records from the Student table.
*/
public List<Student> listStudents();
/**
* This is the method to be used to delete
* a record from the Student table corresponding
* to a passed student id.
*/
public void delete(Integer id);
/**
* This is the method to be used to update
* a record into the Student table.
*/
public void update(Integer id, Integer age);
}- Student.java: Student class
public class Student {
private Integer age;
private String name;
private Integer id;
public void setAge(Integer age) {
this.age = age;
}
public Integer getAge() {
return age;
}
public void setName(String name) {
this.name = name;
}
public String getName() {
return name;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getId() {
return id;
}
}- StudentMapper.java: Map database entries to Student objects, and the introduction of the RowMapper interface will be supplemented at the end of this article.
public class StudentMapper implements RowMapper<Student> {
public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
Student student = new Student();
student.setId(rs.getInt("id"));
student.setName(rs.getString("name"));
student.setAge(rs.getInt("age"));
return student;
}
}This is a functional interface, so it can be used as an assignment target for lambda expressions or method references.
RowMapper must implement the mapRow method to map each row of data in the ResultSet. This method should not call next() on ResultSet; it should only map the value of the current row.
@Nullable
T mapRow(java.sql.ResultSet rs,
int rowNum)
throws java.sql.SQLExceptionParameters: rs - the ResultSet to map (pre-initialized for the current row) rowNum - the number of the current row Returns: the result object for the current row (may be null) Throws: java.sql.SQLException - if a SQLException is encountered getting column values (that is, there's no need to catch SQLException)
- Student JDBCTemplate. java: Implementation of Student Data Interface
public class StudentJDBCTemplate implements StudentDAO {
private DataSource dataSource;
private JdbcTemplate jdbcTemplateObject;
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
this.jdbcTemplateObject = new JdbcTemplate(dataSource);
}
public void create(String name, Integer age) {
String SQL = "insert into Student (name, age) values (?, ?)";
jdbcTemplateObject.update( SQL, name, age);
System.out.println("Created Record Name = " + name + " Age = " + age);
return;
}
public Student getStudent(Integer id) {
String SQL = "select * from Student where id = ?";
Student student = jdbcTemplateObject.queryForObject(SQL,
new Object[]{id}, new StudentMapper());
return student;
}
public List<Student> listStudents() {
String SQL = "select * from Student";
List <Student> students = jdbcTemplateObject.query(SQL, new StudentMapper());
return students;
}
public void delete(Integer id) {
String SQL = "delete from Student where id = ?";
jdbcTemplateObject.update(SQL, id);
System.out.println("Deleted Record with ID = " + id );
return;
}
public void update(Integer id, Integer age){
String SQL = "update Student set age = ? where id = ?";
jdbcTemplateObject.update(SQL, age, id);
System.out.println("Updated Record with ID = " + id );
return;
}
}Constructor:
- JdbcTemplate()
- JdbcTemplate(javax.sql.DataSource dataSource)
update:
public int update(java.lang.String sql,
@Nullable
java.lang.Object... args)
throws DataAccessExceptionqueryForObject
<T> T queryForObject(java.lang.String sql,
java.lang.Object[] args,
RowMapper<T> rowMapper)
throws DataAccessException- MainApp.java: Main function
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.tutorialspoint.StudentJDBCTemplate;
public class MainApp {
public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext("Beans.xml");
StudentJDBCTemplate studentJDBCTemplate =
(StudentJDBCTemplate)context.getBean("studentJDBCTemplate");
System.out.println("------Records Creation--------" );
studentJDBCTemplate.create("Zara", 11);
studentJDBCTemplate.create("Nuha", 2);
studentJDBCTemplate.create("Ayan", 15);
System.out.println("------Listing Multiple Records--------" );
List<Student> students = studentJDBCTemplate.listStudents();
for (Student record : students) {
System.out.print("ID : " + record.getId() );
System.out.print(", Name : " + record.getName() );
System.out.println(", Age : " + record.getAge());
}
System.out.println("----Updating Record with ID = 2 -----" );
studentJDBCTemplate.update(2, 20);
System.out.println("----Listing Record with ID = 2 -----" );
Student student = studentJDBCTemplate.getStudent(2);
System.out.print("ID : " + student.getId() );
System.out.print(", Name : " + student.getName() );
System.out.println(", Age : " + student.getAge());
}
}- Beans.xml
<?xml version = "1.0" encoding = "UTF-8"?>
<beans xmlns = "http://www.springframework.org/schema/beans"
xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation = "http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd ">
<!-- Initialization for data source -->
<bean id="dataSource"
class = "org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name = "driverClassName" value = "com.mysql.jdbc.Driver"/>
<property name = "url" value = "jdbc:mysql://localhost:3306/TEST"/>
<property name = "username" value = "root"/>
<property name = "password" value = "password"/>
</bean>
<!-- Definition for studentJDBCTemplate bean -->
<bean id = "studentJDBCTemplate"
class = "com.tutorialspoint.StudentJDBCTemplate">
<property name = "dataSource" ref = "dataSource" />
</bean>
</beans>- output
------Records Creation-------- Created Record Name = Zara Age = 11 Created Record Name = Nuha Age = 2 Created Record Name = Ayan Age = 15 ------Listing Multiple Records-------- ID : 1, Name : Zara, Age : 11 ID : 2, Name : Nuha, Age : 2 ID : 3, Name : Ayan, Age : 15 ----Updating Record with ID = 2 ----- Updated Record with ID = 2 ----Listing Record with ID = 2 ----- ID : 2, Name : Nuha, Age : 20