Simplifying JDBC operations with spring JdbcTemplate

A friend who has been in touch with Java Web Development must know that the hibernate framework, though not denying its power, has never felt a sense of it, always feeling inflexible and too bloated.

Today, in spring, an auxiliary class (JDBC Template) for JDBC, which encapsulates the operation of JDBC, is very convenient to use.

First, the use of "fool" (not dependent on the XML configuration):

Write a test unit directly:

1 package com.lcw.spring.jdbc; 2  3 import org.junit.Test; 4 import org.springframework.jdbc.core.JdbcTemplate; 5 import Org.springframework.jdbc.datasource.DriverManagerDataSource; 6  7 public class JdbcTemplate {8      9     @Test10 public     Void Demo () {         Drivermanagerdatasource Datasource=new Drivermanagerdatasource ();         datasource.setdriverclassname ("Com.mysql.jdbc.Driver");         Datasource.seturl ("jdbc:mysql:///spring");         datasource.setusername ("root");         Datasource.setpassword (         jdbctemplate jdbctemplate=new jdbctemplate (dataSource),         jdbctemplate.execute ("Create Table temp (ID int primary key,name varchar) ")     }21 22}

It's very simple, and then look at the use of a combination of configuration files, complete implementation of a class of additions and deletions to change

First, the demo directory structure:

Appliactioncontext.xml

 1 <?xml version= "1.0" encoding= "UTF-8"?> 2 <beans xmlns= "Http://www.springframework.org/schema/beans" 3 x Mlns:p= "http://www.springframework.org/schema/p" 4 xmlns:xsi= "http://www.w3.org/2001/XMLSchema-instance" 5 x si:schemalocation= "6 Http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/ Spring-beans.xsd "> 7 8 <!--data Source configuration--9 <bean id=" DataSource "class=" Org.springframework.jdbc.dat Asource. Drivermanagerdatasource ">10 <property name=" Driverclassname "value=" Com.mysql.jdbc.Driver "></property >11 <property name= "url" value= "jdbc:mysql:///spring" ></property>12 <property name= "user Name "value=" root "></property>13 <property name=" password "value=" "></property>14 </be         an>15 <bean id= "JdbcTemplate" class= "Org.springframework.jdbc.core.JdbcTemplate" >19 <property name= "DatasourCe "ref=" DataSource "></property>20 </bean>21 <bean id=" Userdao "class=" com.curd.sp Ring.impl.UserDAOImpl ">24 <property name=" JdbcTemplate "ref=" JdbcTemplate "></property>25 </b Ean>26 </beans>

Interface: Iuserdao.java

1 package Com.curd.spring.dao; 2  3 import java.util.List; 4  5 import com.curd.spring.vo.User; 6  7 public interface Iuserdao {8  9     PU Blic void AddUser (user user), ten public     void deleteuser (int id), and public     void UpdateUser (user user); 15
   public String searchusername (int id), public     User searchuser (int id), public     list< User> FindAll (); 20 21}

Interface Implementation class: Userdaoimpl.java

According to the previous spring dependency injection, we need to use the constructor in the interface implementation class to get the JdbcTemplate

Spring has helped us think of this, providing us with the Jdbcdaosupport support class, where all DAO inherits this class and automatically gets JdbcTemplate (if injected datasource).

1     <bean id= "JdbcTemplate" class= "org.springframework.jdbc.core.JdbcTemplate" >2 <property name=         " DataSource "ref=" DataSource "></property>3     </bean>4     5     6     <bean id=" Userdao "class = "Com.curd.spring.impl.UserDAOImpl" >7         <property name= "JdbcTemplate" ref= "JdbcTemplate" ></ Property>8     </bean>

In our implementation class, we can get the operation object directly using Getjdbctemplate.

JdbcTemplate mainly provides the following methods:

1. Execute method: Can be used to execute any SQL statements, generally used to execute DDL statements;

2, 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;

3, Query method and Queryforxxx method: Used to execute query related statements;

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

 1 package Com.curd.spring.impl; 2 3 Import Java.sql.ResultSet; 4 Import java.sql.SQLException; 5 Import Java.util.List; 6 7 Import Org.springframework.jdbc.core.RowMapper; 8 Import Org.springframework.jdbc.core.support.JdbcDaoSupport; 9 Import com.curd.spring.dao.iuserdao;10 Import com.curd.spring.vo.user;11 public class Userdaoimpl extends Jdbcdaosupport implements Iuserdao {$ public void AddUser (user user) {$ sql = "INSERT INTO User VA Lues (?,?,?) ";     This.getjdbctemplate (). Update (SQL, User.getid (), User.getusername (), User.getpassword ()); 18 }19 public void deleteuser (int id) {$ String sql = "Delete from user where id=?"; This.getjdbctemplate (). Update (SQL, ID),}25 public void updateUser (user user) {Strin G sql = "Update user set username=?,password=?" where id=? "; This.getjdbctemplate (). Update (SQL, User.getusername (), User.getpassword (), User.getiD ());}31-public string searchusername (int id) {///simple query, query by ID, return string: sql = "SELECT username From user where id=? "; 34//return type is String (String.class) this.getjdbctemplate (). queryForObject (SQL, string.class, id); 36 3         7}38 41 Public list<user> FindAll () {///complex query returns the List collection, max String sql = "SELECT * from User";         Return this.getjdbctemplate (). query (SQL, New Userrowmapper ()),}44 public User searchuser (int id) {46 String sql= "SELECT * from user where id=?"; Return This.getjdbctemplate (). queryForObject (SQL, New Userrowmapper (), id),}49 class Userrowmappe R implements Rowmapper<user> {//rs is the return result set, encapsulated in per-action unit the G-Public User Maprow (ResultSet rs, int rowNum) th             Rows SQLException {User user = new user () User.setid (Rs.getint ("id")); 56 User.setusername (rs.getstring ("username")); User.setpassword (RS.getstring ("password")); user;59}60 61}62 63} 

Test class: Usertest.java

 1 package com.curd.spring.test; 2 3 Import java.util.List; 4 5 Import Org.junit.Test; 6 Import Org.springframework.context.ApplicationContext; 7 Import Org.springframework.context.support.ClassPathXmlApplicationContext; 8 9 Import com.curd.spring.dao.iuserdao;10 import com.curd.spring.vo.user;11 public class Usertest {@Tes t//public void Demo1 () {User user=new User (); User.setid (3); User.setusername ("admin "), User.setpassword (" 123456 "), ApplicationContext applicationcontext=new classpathxmlappli         Cationcontext ("Applicationcontext.xml"); Iuserdao dao= (Iuserdao) Applicationcontext.getbean ("UserDao"); 23          Dao.adduser (user),}26 @Test//change to public void Demo2 () {User user=new User (); 30 User.setid (1); User.setusername ("admin"), User.setpassword ("admin"), App Licationcontext applicationcontext=new CLASSPAthxmlapplicationcontext ("Applicationcontext.xml"), Iuserdao dao= (Iuserdao) Applicationcontext.getbean ("UserDao "); Dao.updateuser (user), PNS}38 @Test//censored public void Demo3 () {ApplicationContext Applicationcontext=new classpathxmlapplicationcontext ("Applicationcontext.xml"); Iuserdao dao= (IUserDAO) Applica Tioncontext.getbean ("Userdao"); Dao.deleteuser (3);}45 @Test//Check (simple query, return string)-public void D         Emo4 () {ApplicationContext applicationcontext=new classpathxmlapplicationcontext ("Applicationcontext.xml"); 49         Iuserdao dao= (Iuserdao) Applicationcontext.getbean ("Userdao"); String name=dao.searchusername (1); 51 SYSTEM.OUT.PRINTLN (name);}53 @Test//Check (simple query, return object) on public void Demo5 () {Applicationconte XT applicationcontext=new Classpathxmlapplicationcontext ("Applicationcontext.xml"); Iuserdao dao= (IUserDAO) APPL Icationcontext.getbean ("USerdao "), User=dao.searchuser (1), System.out.println (User.getusername ());}61 est//(complex query, return object collection) Demo6 () {ApplicationContext applicationcontext=new classpathxmlapplication Context ("Applicationcontext.xml"); Iuserdao dao= (Iuserdao) Applicationcontext.getbean ("Userdao"); <User> Users=dao.findall (); System.out.println (Users.size ()); 68}69 70 71 72}

How about, very simple, in the lack of JDBC SQL flexible operation and to remove the complexity of operations ~

Report:

1. Spring provides a support class for each persistence technology, injecting the template tool class into the DAO
(1) JDBC:org.springframework.jdbc.core.support.JdbcDaoSupport
(2) Hibernate 3.0:org.springframework.orm.hibernate3.support.hibernatedaosupport
(3) IBatis:org.springframework.orm.ibatis.support.SqlMapClientDaoSupport

The user writes DAO only needs to inherit Jdbcdaosupport, can inject JdbcTemplate

2. Add, modify, delete with int update (String sql, Object ... args) via JdbcTemplate

3, simple query, return the original data type, String type
String sql = "SELECT COUNT (*) from user"; int queryForInt (String sql)
String sql = "SELECT name from user where id =?"; <T> T queryForObject (String sql, class<t> requiredtype, Object ... args)

4. Complex query
JdbcTemplate no handler, manual completion of object encapsulation

Writing entity Classes RowMapper
Class Userrowmapper implements Rowmapper<user> {
@Override
Public User Maprow (ResultSet rs, int rowNum) throws SQLException {
RS points to each data, does not need to call next on its own, the RS point to the Data conversion user Object
User user = new user ();
User.setid (Rs.getint ("id"));
User.setname (rs.getstring ("name"));
return user;
}
}

Querying a single object <T> T queryforobject (String sql, rowmapper<t> RowMapper, object ... args)
Return This.getjdbctemplate (). queryForObject (SQL, New Userrowmapper (), id);

Querying all Objects List collection <T> list<t> query (String sql, rowmapper<t> RowMapper, object ... args)
Return this.getjdbctemplate (). query (SQL, New Userrowmapper ());

The methods provided above can meet our daily needs.

Simplifying JDBC operations with spring JdbcTemplate