Spring supports JDBC templates: JdbcTemplate

Spring supports JDBC templates: JdbcTemplate

Spring JdbcTemplate is a template encapsulation for JDBC. It provides a set of JDBC templates that allow us to reduce unnecessary code and simplify the JDBC code when writing code at the persistent layer, make the Code look more concise. Let's discuss a problem before introducing Spring's JdbcTemplate usage. The following is a common JDBC code for writing data to the database:

Public int jdbcInsert (Student student) throws SQLException {

Connection connection = null;

Try {
Connection = dataSource. getConnection ();

String SQL = "INSERT INTO student (sname, age, sex, address) VALUES (?,?,?,?) ";
PreparedStatement preparedStatement = connection. prepareStatement (SQL );
PreparedStatement. setString (1, student. getName ());
PreparedStatement. setInt (2, student. getAge ());
PreparedStatement. setString (3, student. getSex ());
PreparedStatement. setString (4, student. getAddress ());

Return preparedStatement.exe cuteUpdate ();
} Finally {
Connection. close ();
}
}

Public int jdbcUpdate (Student student) throws SQLException {

Connection connection = null;

Try {
Connection = dataSource. getConnection ();

String SQL = "UPDATE student SET sname = ?, Age = ?, Sex = ?, Address =? ";
PreparedStatement preparedStatement = connection. prepareStatement (SQL );
PreparedStatement. setString (1, student. getName ());
PreparedStatement. setInt (2, student. getAge ());
PreparedStatement. setString (3, student. getSex ());
PreparedStatement. setString (4, student. getAddress ());

Return preparedStatement.exe cuteUpdate ();
} Finally {
Connection. close ();
}
}

From the code above, we can see that basically 99% of the Code in the two methods are repeated, except for SQL statements, they are repeated code, and repeated code is bad taste, it will make us generate a lot of redundant code, which is not easy to maintain and modify, and it is still hard to write.

Therefore, the JdbcTemplate provided by Spring is used to solve this problem. In fact, Spring JDBCTemplate is a bit like DBUtils, but sometimes it is not as easy to use as DBUitls. Here, let's take a look at using Spring's JdbcTemplate to play CRUD. After all, JdbcTemplate is generally not used in actual development. It is usually used by mature and excellent data persistence layer frameworks such as Mybatis and Hibernate, however, we still need to know that Spring has such a jdbc template class.

Spring supports different Persistence:

Spring not only supports JDBC, but also provides simple operation templates and callbacks for various supported Persistence Technologies:

ORM persistence Technology	Template Class
JDBC	Org. springframework. jdbc. core. JdbcTemplate
Hibernate5.0	Org. springframework. orm. hibernate5.HibernateTemplate
IBatis (MyBatis)	Org. springframework. orm. ibatis. SqlMapClientTemplate
JPA	Org. springfrmaework. orm. jpa. JpaTemplate

Dependencies to be configured using JdbcTemplate:

<Dependencies>
<Dependency>
<GroupId> org. springframework </groupId>
<ArtifactId> spring-context </artifactId>
<Version> 4.3.14.RELEASE </version>
</Dependency>
<Dependency>
<GroupId> org. springframework </groupId>
<ArtifactId> spring-jdbc </artifactId>
<Version> 4.3.14.RELEASE </version>
</Dependency>
<Dependency>
<GroupId> mysql </groupId>
<ArtifactId> mysql-connector-java </artifactId>
<Version> 5.1.39 </version>
</Dependency>
<Dependency>
<GroupId> com. mchange </groupId>
<ArtifactId> c3p0 </artifactId>
<Version> 0.9.5.2 </version>
</Dependency>
</Dependencies>

To use JdbcTemplate, follow these steps:

1. Configure the Spring configuration file. The content is as follows:

<? 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"
Xmlns: context = "http://www.springframework.org/schema/context"
Xsi: schemaLocation = "http://www.springframework.org/schema/beans
Http://www.springframework.org/schema/beans/spring-beans.xsd
Http://www.springframework.org/schema/context
Http://www.springframework.org/schema/context/spring-context.xsd
">

<Context: annotation-config/>
<Context: component-scan base-package = "org. zero01"/>

<Bean id = "dataSource" class = "com. mchange. v2.c3p0. ComboPooledDataSource"
P: driverClass = "com. mysql. jdbc. Driver"
P: jdbcUrl = "jdbc: mysql: // school"
P: user = "root"
P: password = "Zero-One1 ."
P: logintime out = "2000"
P: maxPoolSize = "10"
P: minPoolSize = "1"
/>
</Beans>

2. Create a database table field encapsulation class:

Package org. zero01.pojo;

Import org. springframework. stereotype. Component;

@ (JavaWeb) Component ("stu ")
Public class Student {

Private int sid;
Private String name;
Private int age;
Private String sex;
Private String address;

Public int getSid (){
Return sid;
}

Public void setSid (int sid ){
This. sid = sid;
}

Public String getName (){
Return name;
}

Public void setName (String name ){
This. name = name;
}

Public int getAge (){
Return age;
}

Public void setAge (int age ){
This. age = age;
}

Public String getSex (){
Return sex;
}

Public void setSex (String sex ){
This. sex = sex;
}

Public String getAddress (){
Return address;
}

Public void setAddress (String address ){
This. address = address;
}
}

3. Write dao class:

Package org. zero01.dao;

Import org. springframework. beans. factory. annotation. Autowired;
Import org. springframework. jdbc. core. JdbcTemplate;
Import org. springframework. stereotype. Component;
Import org. zero01.pojo. Student;

Import javax. SQL. DataSource;

@ Component ("stuDAO ")
Public class StudentDAO {

@ Autowired
Private DataSource;

Public int springInsert (Student student ){
// Instantiate the jdbc template object and input the data source
JdbcTemplate jdbcTemplate = new JdbcTemplate (dataSource );
String SQL = "INSERT INTO student (sname, age, sex, address) VALUES (?,?,?,?) ";
// Call the update method to execute insert
Int row = jdbcTemplate. update (SQL, student. getName (), student. getAge (), student. getSex (), student. getAddress ());
Return row;
}
}

As you can see, after using JdbcTemplate, you only need to write an SQL statement and then call the corresponding execution method. You do not need to care about the acquisition, closure, and a lot of Set Value code of the database connection object.

The above is just one of the writing methods. We can also inherit JdbcTemplate directly, so that we can directly call the method of the parent class:

Package org. zero01.dao;

Import org. springframework. beans. factory. annotation. Autowired;
Import org. springframework. jdbc. core. JdbcTemplate;
Import org. springframework. stereotype. Component;
Import org. zero01.pojo. Student;

Import javax. SQL. DataSource;

@ Component ("stuDAO ")
Public class StudentDAO extends JdbcTemplate {

@ Autowired
// Override the setDataSource of the parent class to set the data source object
Public void setDataSource (DataSource dataSource ){
Super. setDataSource (dataSource );
}

Public int springInsert (Student student ){
String SQL = "INSERT INTO student (sname, age, sex, address) VALUES (?,?,?,?) ";
// Directly call the method of the parent class.
Int row = update (SQL, student. getName (), student. getAge (), student. getSex (), student. getAddress ());
Return row;
}
}

The following uses JdbcTemplate to compile a simple example of adding, deleting, querying, and modifying:

Interface:

Package org. zero01.dao;

Import org. zero01.pojo. Student;

Import java. util. List;

Public interface DAO {

Public int insert (Student student );

Public int delete (int sid );

Public List <Student> selectAll ();

Public List <Student> selectByLimit (int start, int end );

Public Student selectById (int sid );

Public long countAll ();

Public int update (Student student );
}

Because JdbcTemplate does not provide the function of automatically ing table fields to object attributes, we need to implement its own interface for manual configuration ing:

Package org. zero01.dao;

Import org. springframework. jdbc. core. RowMapper;
Import org. zero01.pojo. Student;

Import java. SQL. ResultSet;
Import java. SQL. SQLException;

// The property ing Class of the Student object
Public class StudentMapper implements RowMapper <Student> {

Public Student mapRow (ResultSet resultSet, int I) throws SQLException {
Student student = new Student ();
Student. setSid (resultSet. getInt ("sid "));
Student. setName (resultSet. getString ("sname "));
Student. setAge (resultSet. getInt ("age "));
Student. setSex (resultSet. getString ("sex "));
Student. setAddress (resultSet. getString ("address "));

Return student;
}
}

 

StudentDAO class:

Package org. zero01.dao;

Import org. springframework. beans. factory. annotation. Autowired;
Import org. springframework. jdbc. core. JdbcTemplate;
Import org. springframework. stereotype. Component;
Import org. zero01.pojo. Student;

Import javax. SQL. DataSource;
Import java. util. List;

@ Component ("stuDAO ")
Public class StudentDAO extends JdbcTemplate implements DAO {

@ Autowired
// Override the setDataSource of the parent class to set the data source object
Public void setDataSource (DataSource dataSource ){
Super. setDataSource (dataSource );
}

// Insert a single row of data
Public int insert (Student student ){
String SQL = "INSERT INTO student (sname, age, sex, address) VALUES (?,?,?,?) ";
Int row = update (SQL, student. getName (), student. getAge (), student. getSex (), student. getAddress ());

Return row;
}

// Delete by id
Public int delete (int sid ){
Return update ("delete from student WHERE sid =? ", Sid );
}

// Query multiple rows of data
Public List <Student> selectAll (){

// To query multiple objects, you need to pass the ing Class for ing.
List <Student> studentList = query ("SELECT * FROM student", new StudentMapper ());

Return studentList;
}

// Query data by PAGE
Public List <Student> selectByLimit (int start, int end ){

// To query multiple objects, you need to pass the ing Class for ing.
List <Student> studentList = query ("SELECT * FROM student LIMIT" + start + "," + end, new StudentMapper ());

Return studentList;
}

// Query a single row of data by id
Public Student selectById (int sid ){
// Storage Parameters
Object [] objects = {sid };
Student student = queryForObject ("SELECT * FROM student where sid =? ", Objects, new StudentMapper ());

Return student;
}

// Query the total number of rows in the table
Public long countAll (){
Long countNumber = queryForObject ("SELECT count (*) FROM student", Long. class );

Return countNumber;
}

// Update a single row of data
Public int update (Student student ){
String SQL = "UPDATE student SET sname = ?, Age = ?, Sex = ?, Address =? WHERE sid =? ";
Return update (SQL, student. getName (), student. getAge (), student. getSex (), student. getAddress (), student. getSid ());
}
}

As shown above, we can see that by using the JdbcTemplate provided by Spring, we only need to write specific SQL statements, which is much simpler than writing common JDBC code, and there is no extra code. When the project does not need to use frameworks such as Mybatis and Hibernate, JdbcTemplate is also good.

This article permanently updates link: https://www.bkjia.com/Linux/2018-03/151182.htm