Spring JdbcTemplate Detailed

JdbcTemplate Introduction

Spring's operations on the database are encapsulated in a deep level of JDBC, using the spring injection function to register the datasource with the JdbcTemplate.

The JdbcTemplate is located in. Its fully qualified name is org.springframework.jdbc.core.JdbcTemplate. To use jdbctemlate, you need one. This package contains the transaction and exception control

　　

JdbcTemplate provides the following five types of methods mainly:

• Execute method: Can be used to execute any SQL statement, generally used to execute DDL statements;

• Update method and BatchUpdate method: Update method is used to execute new, modify, delete and other statements; The BatchUpdate method is used to execute batch-related statements;

• Query method and Queryforxxx method: used to execute queries related to the statement;

• Call method: Used to execute stored procedures, function-related statements.

The following case analysis

Create a new attribute profile under SRC

1 Jdbc.user=root2 jdbc.password=1234563 jdbc.driverclass=com.mysql.jdbc.driver4 jdbc.jdbcUrl=jdbc\:mysql\:///test

We usually put the configuration information of the database in a single file, so as to facilitate post-maintenance

Configure Spring configuration file Applicationcontext.xml

1 <context:property-placeholder location= "Classpath:db.properties"/> 2 <bean id= "DataSource" class= " Com.mchange.v2.c3p0.ComboPooledDataSource "> 3     <property name=" user "value=" ${jdbc.user} "></ Property> 4     <property name= "password" value= "${jdbc.password}" ></property> 5     <property Name= "Driverclass" value= "${jdbc.driverclass}" ></property> 6     <property name= "Jdbcurl" value= "${ Jdbc.jdbcurl} "></property> 7 </bean> 8  9 <bean id=" JdbcTemplate "class=" Org.springframework.jdbc.core.JdbcTemplate ">10     <property name=" DataSource "ref=" DataSource "></ Property>11 </bean>

The first line of code: used to read the data in the Db.properties file.

The second line of code: used to configure a data source, where the data implementation class comes from an attribute class in c3p0. Where the value of the property is from Db.properties

Line Nineth: Configure a JdbcTemplate instance and inject a datasource data source

Test Code

1. Update () method

A. Inserting data via update

1//Start IOC container 2 applicationcontext ctx=new classpathxmlapplicationcontext ("Applicationcontext.xml"); 3// Get the IOC container in JdbcTemplate instance 4 jdbctemplate jdbctemplate= (jdbctemplate) Ctx.getbean ("JdbcTemplate"); 5 String sql= "Insert into user (Name,deptid) VALUES (?,?) "; 6 int count= jdbctemplate.update (sql, New object[]{"Caoyc", 3}); 7 System.out.println (count);

Here the Update method, the second parameter can be a mutable parameter. As you can see in the database, the data is inserted correctly

B. modifying data through Update

1 String sql= "Update user set name=?,deptid=?" where id=? "; 2 jdbctemplate.update (sql,new object[]{"Zhh", 5,51});

C. Delete Data via update

1 String sql= "Delete from user where id=?"; 2 jdbctemplate.update (sql,51);

2. BatchUpdate () Bulk INSERT, update, and Delete methods

A, BULK insert

1 String sql= "INSERT into User (Name,deptid) VALUES (?,?)"; 2 3 list<object[]> batchargs=new arraylist<object[]> (); 4 Batchargs.add (New object[]{"Caoyc", 6}); 5 Batchargs.add (New object[]{"Zhh", 8}), 6 batchargs.add (new object[]{"CJX", 8}), 7 8 jdbctemplate.batchupdate (SQL, Batchargs);

BatchUpdate method The second parameter is a list collection with an element of object[] array type

3. Reading data from data to entity objects

　　Set a user entity class first

1 package Com.proc; 2  3 public class User {4     private integer ID; 5     private String name; 6     private integer deptid; 7     Publi C Integer GetId () {8         return ID; 9     }10 public     void SetId (Integer id) {         this.id = id;12     }13     Pub Lic string GetName () {         name;15 return     }16 public     void SetName (String name) {         this.name = name;18     }19 public     integer Getdeptid () {         return deptid;21     }22 public     void Setdeptid (integer DeptID) {         This.deptid = deptid;24     }25-Public     String toString () {         "User [id=" + ID + ", n Ame= "+ name +", deptid= "+ DeptID +"] ";     }29}

A. Read a single object

1 String sql= "Select Id,name,deptid from user where id=?"; 2 3 rowmapper<user> rowmapper=new beanpropertyrowmapper<user> (User.class); 4 User user= Jdbctemplate.queryforobject (SQL, rowmapper,52); 5 System.out.println (user);

Output Result:

User [id=52, Name=caoyc, deptid=6]

"Note": 1, using beanproperytrowmapper requires that the SQL data query out the column and entity properties need one by one corresponding. If the column and attribute names in the data are inconsistent, you need to use as to re-fetch an alias in the SQL statement

2. Cannot get associated object using JdbcTemplate object

B. Read multiple objects

1 String sql= "Select Id,name,deptid from User"; 2 3 rowmapper<user> rowmapper=new Beanpropertyrowmapper<user > (user.class); 4 list<user> users= jdbctemplate.query (sql, RowMapper); 5 for (User user:users) {6     SYSTEM.OUT.PRINTLN (user); 7}

Output results

...

User [id=49, name= name, deptid=5]
User [Id=50, name= name, deptid=8]
User [id=52, Name=caoyc, deptid=6]
User [id=53, NAME=ZHH, deptid=8]
User [id=54, NAME=CJX, deptid=8]

---

C, get a record of a column or count, AVG, SUM, and other functions return unique values

1 String sql= "SELECT COUNT (*) from user"; 2 3 int count= jdbctemplate.queryforobject (sql, Integer.class); 4 System.out.prin TLN (count);

How to use it in real development

Userdao.java

1 package Com.proc; 2  3 import org.springframework.beans.factory.annotation.Autowired; 4 Import Org.springframework.jdbc.core.BeanPropertyRowMapper; 5 Import Org.springframework.jdbc.core.JdbcTemplate; 6 Import Org.springframework.jdbc.core.RowMapper; 7 Import Org.springframework.stereotype.Repository; 8  9 @Repository10 public class Userdao {     @Autowired13     private jdbctemplate jdbctemplate;14     15 Public     User get (int id) {         sql= String ' select Id,name,deptid from User where id=? ';         rowmapper<user> rowmapper=new beanpropertyrowmapper<user> (user.class);         Jdbctemplate.queryforobject (SQL, rowmapper,id);     }20}

XML configuration:

 1 <?xml version= "1.0" encoding= "UTF-8"?> 2 <beans xmlns= "Http://www.springframework.org/schema/beans" 3 xmln S:xsi= "Http://www.w3.org/2001/XMLSchema-instance" 4 xmlns:aop= "Http://www.springframework.org/schema/aop" 5 xmlns: Context= "Http://www.springframework.org/schema/context" 6 xsi:schemalocation= "http://www.springframework.org/ SCHEMA/AOP http://www.springframework.org/schema/aop/spring-aop-4.3.xsd 7 http://www.springframework.org/schema/b  EANs http://www.springframework.org/schema/beans/spring-beans.xsd 8 Http://www.springframework.org/schema/context Http://www.springframework.org/schema/context/spring-context-4.3.xsd "> 9 <context:component-scan Base-package= "Com.proc" ></context:component-scan>11 <context:property-placeholder location= "     Classpath:db.properties "/>12 <bean id=" DataSource "class=" Com.mchange.v2.c3p0.ComboPooledDataSource ">13     <property name= "user" value= "${jdbc.user}" ></property>14<property name= "Password" value= "${jdbc.password}" ></property>15 <property name= "DriverClass" value= "${jdbc.driverclass}" ></property>16 <property name= "Jdbcurl" value= "${jdbc.jdbcurl}" ></property >17 </bean>18 <bean id= "JdbcTemplate" class= "Org.springframework.jdbc.core.JdbcTemplate" >20 < Property Name= "DataSource" ref= "DataSource" ></property>21 </bean>22 </beans>

Code testing

1 Userdao userdao= (Userdao) Ctx.getbean ("Userdao"); 2 System.out.println (Userdao.get (53));

Spring JdbcTemplate Detailed