Spring Framework Notes (24) Two ways to use JDBC in--spring

To make JDBC easier to use, Spring defines an abstraction layer on the JDBC API to create a JDBC access framework.

As the core of the Spring JDBC Framework, the JDBC template is designed to provide a template approach for different types of JDBC operations. Each template method can control the entire process and allow specific tasks to be overridden during the process. In this way, the workload of database access can be minimized with the greatest flexibility possible.

Now let's introduce the spring JDBC API that the various crud might use:

Updating a database using JdbcTemplate

To update the database with SQL statements and parameters:

public int update (String sql, Object ... args) throws DataAccessException

Batch Update database:

Public int[] BatchUpdate (String sql, list<object[]> Batchargs) throws DataAccessException

Querying a database using JdbcTemplate

Query line:

Public <T> T queryForObject (String sql, rowmapper<t> rowmapper, Object ... args) throws DataAccessException

Query Multiple lines:

Public <T> list<t> Query (String sql, rowmapper<t> rowmapper, Object ... args) throws DataAccessException

Single value query:

Public <T> T queryForObject (String sql, class<t> requiredtype) throws DataAccessException

Benefits of using spring JDBC-- simplifying the JDBC template query

It is inefficient to create a new instance of JdbcTemplate each time it is used.

The JdbcTemplate class is designed to be thread-safe, so you can declare a single instance of it in the IOC container and inject that instance into all the DAO instances.

JdbcTemplate also leverages the specific Java 1.5 (auto-boxing, generics, variable length, etc.) to simplify development

The Spring JDBC Framework also provides a jdbcdaosupport class to simplify DAO implementations. The class declares the JdbcTemplate property, which can be injected from the IOC container or automatically created from the data source.

Well, let's give an example.

In the beginning, we gave all the pom that we needed for the jar of spring JDBC, rather than a few.

<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>com.happbks.spring</ groupid><artifactid>jdbcspring</artifactid><version>0.0.1-snapshot</version>< packaging>jar</packaging><name>jdbcspring</name><url>http://maven.apache.org</ url><properties><project.build.sourceencoding>utf-8</project.build.sourceencoding></ Properties><dependencies><dependency><groupid>junit</groupid><artifactid> Junit</artifactid><version>4.10</version><scope>test</scope></dependency> <dependency><groupid>org.springframework</groupid><artifactid>spring-context</ Artifactid><version>4.1.7.release</veRsion></dependency><dependency><groupid>org.springframework</groupid><artifactid >spring-aspects</artifactId><version>4.1.7.RELEASE</version></dependency>< Dependency><groupid>com.mchange</groupid><artifactid>c3p0</artifactid><version >0.9.5.1</version></dependency><dependency><groupid>org.springframework</groupid ><artifactid>spring-jdbc</artifactid><version>4.2.0.release</version></ Dependency><dependency><groupid>org.springframework</groupid><artifactid>spring-web </artifactId><version>4.2.0.RELEASE</version></dependency><dependency>< Groupid>org.springframework</groupid><artifactid>spring-webmvc</artifactid><version >4.2.0.release</version></dependency><dependency><groupid>org.springframework</ Groupid><artifactid>spring-orm</artifactid><version>4.2.0.release</version></dependency><dependency>< groupid>mysql</groupid><artifactid>mysql-connector-java</artifactid><version>5.1.36 </version></dependency></dependencies></project>

First we need to build a database springjdbc. Then create two tables for employee and department.

We then insert several sets of data:

Department

Employee

After that, we define the associated bean class:

package com.happbks.spring.jdbcspring;public  class DepartmentBean {String id; String departmentname;public string getid ()  {return id;} Public void setid (String id)  {this.id = id;} Public string getdepartmentname ()  {return departmentname;} Public void setdepartmentname (String departmentname)  {this.departmentName =  Departmentname;} Public departmentbean (String id, string departmentname)  {super ();this.id =  Id;this.departmentname = departmentname;} Public departmentbean ()  {super ();// todo auto-generated constructor stub}@ Overridepublic string tostring ()  {return  "departmentbean [id="  + id +   ",  departmentname=" + departmentname +  "]";}} 

package com.happbks.spring.jdbcspring;public class employeebean {integer id; string lastname; String email;departmentbean department;public integer getid ()  {return id;} Public void setid (Integer id)  {this.id = id;} Public string getlastname ()  {return lastname;} Public void setlastname (String lastname)  {this.lastname = lastname;} Public string getemail ()  {return email;} Public void setemail (String email)  {this.email = email;} Public departmentbean getdepartment ()  {return department;} Public void setdepartment (departmentbean department)  {this.department =  Department;} Public employeebean (Integer id, string lastname, string email,departmentbean  department)  {super (); this.id = id;this.lastname = lastname;tHis.email = email;this.department = department;} Public employeebean ()  {super ();// todo auto-generated constructor stub}@ Overridepublic string tostring ()  {return  "employeebean [id="  + id +   ",  lastname="  + lastName +  ",  email=" + email +  ",  Department= " + department + "] ";}}

Note that we expect to change the dept_id corresponding to a member of the Departmentbean type object. See if it can be implemented with spring JDBC.

We then define the spring IOC container configuration file Applicationcontext.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 " xmlns:aop="/HTTP/ Www.springframework.org/schema/aop "xmlns:context=" Http://www.springframework.org/schema/context "xsi: Schemalocation= "http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/ Spring-aop-4.1.xsdhttp://www.springframework.org/schema/beans http://www.springframework.org/schema/beans /spring-beans.xsdhttp://www.springframework.org/schema/context http://www.springframework.org/schema/ Context/spring-context-4.1.xsd "><!--  Auto-scan package  --><context:component-scan base-package = "Com.happBKs.spring.jdbcSpring" ></context:component-scan><!--  Import resource file  --><context: property-placeholder location= "Classpath:db.properties"  /><!--  Configure C3P0 data sources  -->< Bean id= "DataSource"  class= "com.mchange.v2.C3p0.combopooleddatasource "><property name=" user " value=" ${jdbc.user} "></property>< Property name= "Password"  value= "${jdbc.password}" ></property><property name= " Jdbcurl " value=" ${jdbc.jdbcurl} "></property><property name=" DriverClass " value=" ${ Jdbc.driverclass} "></property><property name=" Initialpoolsize " value=" ${ Jdbc.initpoolsize} "></property><property name=" Maxpoolsize " value=" ${jdbc.maxPoolSize} " ></property></bean><!--  Configure the JDBC template class  --><bean id= "JdbcTemplate"  class = "Org.springframework.jdbc.core.JdbcTemplate" ><property name= "DataSource"  ref= "DataSource" > </property></bean></beans>

and db.properties:

Jdbc.user=rootjdbc.password=jdbc.driverclass=com.mysql.jdbc.driverjdbc.jdbcurl=jdbc:mysql:///springjdbc? useunicode=true&characterencoding=utf8jdbc.initpoolsize=5jdbc.maxpoolsize=10

OK, here we try to update the operation and query operations:

package com.happbks.spring.jdbcspring;import static org.junit.assert.*;import  java.sql.sqlexception;import java.util.arraylist;import java.util.list;import  javax.sql.datasource;import org.junit.test;import org.springframework.context.applicationcontext; import org.springframework.context.support.classpathxmlapplicationcontext;import  org.springframework.jdbc.core.beanpropertyrowmapper;import org.springframework.jdbc.core.jdbctemplate; Import org.springframework.jdbc.core.rowmapper;public class jdbctest {applicationcontext  ctx=null; jdbctemplate jdbctemplate; {Ctx=new classpathxmlapplicationcontext ("Applicationcontext.xml"); jdbctemplate= (jdbcTemplate) Ctx.getBean ( "JdbcTemplate");} @Testpublic  void test ()  throws sqlexception {datasource datasource=ctx.getbean ( Datasource.class);//datasource datasource= (DataSource) Ctx.getbean ("DataSource"); System.out.println (DataSource. getconnection ());} /* *  execute update,  insert, delete */@Testpublic  void testupdate () {string sql= ' Update employee set last_name=? where id=? "; Jdbctemplate.update (SQL, "Yao encourages", 5);} /* *  Perform bulk update, INSERT, delete  */@Testpublic  void testbatchupdate ()  {string sql= insert  employee (last_name,email,dept_id)  values (?,?,?) "; List<object[]> batchargs=new arraylist<object[]> (); Batchargs.add (new Object[]{"Temp 1 "," [email protected] ", 4}); Batchargs.add (new object[]{" Temp 2 "," [email protected] ", 4}); Batchargs.add (new object[]{"Temp 3", "[email protected]", 4}); Jdbctemplate.batchupdate (sql,  Batchargs);} /* *  get a record from the database, actually get a corresponding object  *  Note: The method that should be used is <EmployeeBean> EmployeeBean  Org.springframework.jdbc.core.JdbcTemplate.queryForObject (string sql, rowmapper<employeebean>  rowmapper, object... args)  throws dataaccessexception *  rather than <EmployeeBean> EmployeeBean  Org.springframework.jdbc.core.JdbcTemplate.queryForObject (string sql, class<employeebean>  requiredtype, object... args)  throws dataaccessexception *  * 1.   where RowMapper specifies how to map the rows of the result set, the common implementation class is beanpropertyrowmapper *      here, When a method of applying a class later has parameters that are interface types, we need to create an implementation class object for that interface, but we do not know what the specific implementation class is, and you can use Ctrl+t to view the tree of the implementation class for that interface  * 2.  Use the alias of the query field in the SQL statement to complete the mapping between the field name of the database table and the property name of the class object, for example, last_name lastname * 3.  does not support cascading properties. String sql= "select id,last_name lastname, email, dept_id as \" is not supported Department.id\ " from employee where id=?";  *      because JdbcTemplate is just a JDBC tool, not an ORM framework  */@Testpublic  void  Testqueryforobject () {string sql= "select id,last_name lastname, email from  Employee where id=? "; RowMapper<employeebean> rowmapper=new beanpropertyrowmapper<employeebean> ( Employeebean.class); Employeebean employee=jdbctemplate.queryforobject (sql, rowmapper,1);  System.out.println ( Employee);} @Testpublic  void testqueryforlist () {string sql= "select id,last_name lastname,  Email from employee where id>? "; Rowmapper<employeebean> rowmapper=new beanpropertyrowmapper<employeebean> ( Employeebean.class); List<employeebean> employeelist=jdbctemplate.query (sql, rowmapper,5);  System.out.println ( EmployeeList);} /* *  gets the value of a single column, you can use the sum, count, and AVG functions in SQL  */@Testpublic  void testqueryforobjectfield () { String sql= "Select count (ID)  from employee"; Int count=jdbctemplate.queryforobject (sql , Integer.class);  system.out.println (count);}}

Here, the result of the output, I will not list, only select a few:

Result of void Testbatchupdate ():

Result of void Testqueryforobject ():

Employeebean [Id=1, Lastname= Wang Accountant, [email protected], Department=null]

Well, spring JDBC is still jdbc not ORM, so cascade objects in time and space.

So how do we use spring JDBC to write DAO modules when we actually develop them?

Just give it to the code.

Package Com.happbks.spring.jdbcspring;import Org.springframework.beans.factory.annotation.autowired;import Org.springframework.jdbc.core.beanpropertyrowmapper;import Org.springframework.jdbc.core.jdbctemplate;import Org.springframework.jdbc.core.rowmapper;import org.springframework.stereotype.Repository; @Repositorypublic class EmployeeDAO {@Autowiredprivate jdbctemplate jdbctemplate;public employeebean get (int id) {String sql= "select Id,last_ Name LastName, email from employee where id=? "; Rowmapper<employeebean> rowmapper=new beanpropertyrowmapper<employeebean> (EmployeeBean.class); Employeebean employee=jdbctemplate.queryforobject (SQL, rowmapper,id); return employee;}}

Test code:

EmployeeDAO EmployeeDAO; {Employeedao=ctx.getbean (employeedao.class);} @Testpublic void Testemployeedao () {System.out.println (Employeedao.get (1));}

All of the above describes the application of the JdbcTemplate class object creation, to help us complete the various functions of JDBC. As mentioned in the subtitle of this article, there is another class and another usage that can help us implement this function, which is jdbcdaosupport.

Here, we define the Departmentdao class to be implemented in this way, and take a look at it:

package com.happbks.spring.jdbcspring;import javax.sql.datasource;import  org.springframework.beans.factory.annotation.autowired;import  Org.springframework.jdbc.core.beanpropertyrowmapper;import org.springframework.jdbc.core.rowmapper;import  org.springframework.jdbc.core.support.JdbcDaoSupport;import  org.springframework.stereotype.repository;/* *  does not recommend using Jdbcdaosupport, and it is recommended to use JdbcTemplate directly as a DAO class member variable  */@Repositorypublic  class departmentdao extends jdbcdaosupport {@ Autowiredpublic void setdatasource2 (Datasource datasource) {setdatasource (DataSource);//public  final void setdatasource (Datasource datasource)  {}// Note: Because Jdbcdaosupport's Setdatasource method is final, it cannot be rewritten,//A workaround is used here, a new domain dataSource2 is set up, The actual use of the Jdbcdaosupport Setdatasource method Public departmentbean get (Integer id)  {String sql  =  ""; Rowmapper<departmentbean> rowmapper = new&nbSp Beanpropertyrowmapper<departmentbean> (departmentbean.class);D epartmentbean department =  Getjdbctemplate (). queryForObject (sql,rowmapper, id); return department;}}

Test code:

Departmentdao Departmentdao; {Departmentdao = Ctx.getbean (Departmentdao.class);} @Testpublic void Testdepartmentdao () {System.out.println (Departmentdao.get (1));}

Spring Framework Notes (24) Two ways to use JDBC in--spring