Spring support for JDBC templates--jdbctemplate

Spring's jdbctemplate is a template encapsulation of JDBC, which provides a set of JDBC templates that allow us to write persistent layer code to reduce redundant code, simplify the JDBC code, and make the code look more concise. Before we introduce spring's jdbctemplate usage approach, let's talk about a problem, which is a common JDBC code that writes data to a 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.executeupdate ();        } 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.executeupdate ();        } finally {connection.close (); }    }

From the code above, you can see two methods in the basic 99% of the code is duplicated, in addition to the SQL statements, are duplicated code, duplicate code is bad taste, will let us produce a lot of redundant code, not easy to maintain and modify, and write up also tired.

So spring provides the jdbctemplate is to solve this problem, in fact, spring jdbctemplate a bit like dbutils, but sometimes not dbuitls easy to use. Here to learn to use the spring JdbcTemplate to play crud, after all, jdbctemplate in the actual development is generally not used, usually using MyBatis, hibernate and other mature, excellent data persistence layer framework, But you still have to know that spring has one of these JDBC template classes.

Spring's support for different persistence:

Spring does not only support jdbc,spring for a variety of supported persistence technologies, but it provides simple-to-operate templates and callbacks:

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 that need to be configured with JdbcTemplate:

  <dependencies> <dependency> <groupid>org.springframework</groupid>            ; <artifactId>spring-context</artifactId> <version>4.3.14.RELEASE</version> </d ependency> <dependency> <groupId>org.springframework</groupId> <artif Actid>spring-jdbc</artifactid> <version>4.3.14.RELEASE</version> </dependency>        ; <dependency> <groupId>mysql</groupId> <artifactid>mysql-connector-java</a            rtifactid> <version>5.1.39</version> </dependency> <dependency> <groupId>com.mchange</groupId> <artifactId>c3p0</artifactId> <versio N>0.9.5.2</version> </dependency> </dependencies>  

Basic steps for using JdbcTemplate:

1. Configure the spring configuration file with the following content:

<?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:contex t= "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/conte XT Http://www.springframework.org/schema/context/spring-context.xsd "> <context:annotation-config/&gt    ; <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:logintimeout= "p:maxpoolsize=" p:minpoolsize= "1"/></beans>

2. To create a database table field encapsulates a 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. To write a 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 dataSource;    public int springInsert(Student student) {        // 实例化jdbc模板对象，并传入数据源        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);        String sql = "INSERT INTO student(sname,age,sex,address) VALUES (?,?,?,?)";        // 调用update方法执行insert        int row = jdbcTemplate.update(sql, student.getName(), student.getAge(), student.getSex(), student.getAddress());        return row;    }}

As you can see, after using JdbcTemplate, it is only necessary to write the SQL statement and then invoke the corresponding execution method, which does not need to care about the database connection object getting, shutting down, and reducing the code of the large number of setting values.

And the above is just one of the writing, we can also directly inherit JdbcTemplate, so you can directly call the parent class method:

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    // 重写父类的setDataSource来设置数据源对象    public void setDataSource(DataSource dataSource) {        super.setDataSource(dataSource);    }    public int springInsert(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;    }}

The following through the JdbcTemplate to write a simple sample additions and deletions to the small example:

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 ability to automatically map a table field to an object's properties, we need to implement one of its interfaces to manually configure the mapping:

package org.zero01.dao;import org.springframework.jdbc.core.RowMapper;import org.zero01.pojo.Student;import java.sql.ResultSet;import java.sql.SQLException;// Student对象的属性映射类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 Setdatasource of parent class to set data source object public void Setdatasour    CE (DataSource DataSource) {super.setdatasource (DataSource); }//Insert single-line data public int insert (Student Student) {String-sql = "INSERT into Student (sname,age,sex,address) VAL        UES (?,?,?,?) ";        int row = update (SQL, Student.getname (), Student.getage (), Student.getsex (), student.getaddress ());    return row;    }//Delete public int Delete (int. SID) {return update ("Delete from student WHERE sid=?", sid) by ID; }//Querying multiple rows of data public list<student> SelectAll () {//Querying multiple objects requires you to pass the mapping class for mapping list<student> stude Ntlist= Query ("SELECT * FROM Student", New Studentmapper ());    return studentlist; }//Paging query data public list<student> selectbylimit (int start, int end) {//Query multiple objects requires you to pass the mapping class for mapping Lis        t<student> studentlist = Query ("SELECT * from Student LIMIT" + Start + "," + End, New Studentmapper ());    return studentlist;        }//Query single row data based on ID public Student selectbyid (int sid) {//storage parameter object[] objects = {SID};        Student Student = queryForObject ("select * from Student where sid=?", objects, New Studentmapper ());    return student; }//The total number of rows in the query table public long Countall () {Long countnumber = queryForObject ("SELECT count (*) from student", long.        Class);    return countnumber; }//Update single row 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 you can see above, by using the jdbctemplate provided by spring, we only need to write a specific SQL statement, which is much simpler than writing the normal JDBC code, and there is no redundant code. When the project does not need to use MyBatis, hibernate and other frameworks, the use of JdbcTemplate is also good.

Spring support for JDBC templates--jdbctemplate