"Spring" Spring JDBC principle and application example explained

Lin Bingwen Evankaka original works. Reprint please specify the source Http://blog.csdn.net/evankaka

I. Overview

There are a number of options for basic JDBC access to the database using spring. Spring provides at least three different modes of operation: the spring JDBC Abstract Framework core package provides the JDBC template class, where JdbcTemplate is the core class of the core package, so the other template classes are based on its encapsulation, and the JDBC template class is the first mode of operation. Three modes are as follows:

• JdbcTemplate: The most basic JDBC template in spring, simple access to the database with JDBC and simple indexed parameter queries

• Namedparameterjdbctemplate: The ability to bind a value to a named parameter in SQL at query time instead of an index parameter namedparameterjdbctemplate contains a jdbctemplate inside. So jdbctemplate can do things namedparameterjdbctemplate are able to namedparameterjdbctemplate relative to jdbctemplate the main increase in parameters can be named function.
  

• Simplejdbctemplate: Simplifies the use of JDBC templates with Java5 features such as auto-boxing, general-purpose, and variable parameter lists Simplejdbctemplate contains a namedparameterjdbctemplate inside; So the things that namedparameterjdbctemplate can do simplejdbctemplate are capable, Simplejdbctemplate mainly increases the JDK5.0 's generic and variable length parameter support relative to Namedparameterjdbctemplate.
  

The following mainly JdbcTemplate:

The JdbcTemplate class helps us eliminate lengthy code by using template design patterns, doing only what we need to do (i.e., variable parts), and doing some fixed parts for us, such as creating and closing connections. The JdbcTemplate class implements a callback interface for the mutable part, such as Connectioncallback to return a connection to the user via a callback interface, so that the connection can be used to do anything, Statementcallback returns a statement to the user via a callback interface, which allows the statement to do anything, and so on, and some other callback interfaces.

JdbcTemplate supported callback interfaces The value of the Spring JDBC Abstraction Framework is reflected in the following areas: (Note: After using the spring JDBC Abstract Framework, the application developer only needs to complete the coding of the italic part.) ）

• Defining Database Connection Parameters

• Open a database connection

• Declaring SQL statements

• precompiling and executing SQL statements

• Traverse Query Results (if needed)

• Handle each traversal operation

• Handle any exceptions that are thrown

• Processing transactions

• To close a database connection
  

Spring will do all the tedious, bottom-level details of all development using the JDBC API.

second, the use of steps 1. Use JdbcTemplate to access data only need to configure DataSource to be able to let JdbcTemplate work, the following configuration:

<!--Configuring the data source--><bean id= "DataSource" class= "Org.springframework.jdbc.datasource.DriverManagerDataSource" ><property name= "Driverclassname" value= "Com.mysql.jdbc.Driver"/><property name= "url" value= "JDBC: Mysql://localhost:3306/test "/><property name=" username "value=" root "/><property name=" password "value= "[Email protected]"/></bean><!--Configure a JdbcTemplate instance and inject this "shared", "safe" instance into different DAO classes to--><bean id= " JdbcTemplate "class=" org.springframework.jdbc.core.JdbcTemplate "><property name=" DataSource "ref=" DataSource "/></bean>

2. Now we can assemble the JdbcTemplate into DAO and use it to access the database

@Autowiredprivate JdbcTemplate JdbcTemplate;

3. Start the operation of the database, such as the following insert

@Overridepublic void Insert (Student Student) {jdbctemplate.update ("insert INTO Student VALUES ('" + student.getid () + "', ' "+ student.getname () +" ', ' "+ student.getage () +" ', ' "+ student.getsex () +" ') ");}

iii. Examples of use

Here we will instance spring JDBC and MySQL database connection, build table, perform insert, find, delete and other functions.

1, a new Javaproject project, import package Mysql-connector-java-5.1.22-bin.jar+spring3.2+commons-logging-1.2.jar

2, first build the database corresponding model:

Package com.mucfc.model;/**  * Database Transfer class * author Lin Bingwen ([email protected] Blog: Http://blog.csdn.net/evankaka)  * Time 2015.5.24*/public class Student {private int id;private string name;private int age;private string Sex;public Student () {}p Ublic Student (int id,string name,int age,string sex) {this.id=id;this.name=name;this.age=age;this.sex=sex;} public int getId () {return ID;} public void setId (int id) {this.id = ID;} Public String GetName () {return name;} public void SetName (String name) {this.name = name;} public int getage () {return age;} public void Setage (int.) {this.age = age;} Public String Getsex () {return sex;} public void Setsex (String sex) {this.sex = sex;}}

and then there's a database for each row of encapsulation

Package Com.mucfc.model;import java.sql.resultset;/**  * Packaging data in a row * author Lin Bingwen ([email protected] Blog:/http// Blog.csdn.net/evankaka)  * Time 2015.5.24*/import java.sql.sqlexception;/** * RowMapper each row in the data can be encapsulated into a user-defined class */import Org.springframework.jdbc.core.RowMapper;   public class Studentmapper implements rowmapper<student>{public   Student Maprow (ResultSet rs, int rowNum) Throws SQLException {      Student Student = new Student ();      Student.setid (Rs.getint ("id"));      Student.setname (rs.getstring ("name"));      Student.setage (Rs.getint ("Age"));      Student.setsex (rs.getstring ("Sex"));      return student;}   }

3. DAO Layer:

Interface class:

Package Com.mucfc.dao;import java.util.list;import com.mucfc.model.student;/**  *dao Interface class * Author Lin Bingwen ([email protected] Blog: Http://blog.csdn.net/evankaka)  * Time 2015.5.24*/public interface Studentdao {/** * CREATE DATABASE table structure * @param sql */public VO ID Create ();/** * Insert a student data * @param student */public void Insert (student student);/** * Get object by primary KEY * @param ID * @return Stu Dent */public Student getstudent (Integer ID);/** * Get all students in the table * @param ID * @return Student */public list<student> li Ststudents ();/** * Delete Object by primary key * @param id */public void Delete (Integer ID);/** * Change object by PRIMARY key * @param id */public void Update (S Tudent student);}

Implementation class:

Package Com.mucfc.dao;import Java.util.iterator;import Java.util.list;import java.util.map;import Org.springframework.beans.factory.annotation.autowired;import Org.springframework.jdbc.core.jdbctemplate;import  Org.springframework.stereotype.component;import Com.mucfc.model.student;import com.mucfc.model.StudentMapper;/** *dao Implementation class * Author Lin Bingwen ([email protected] Blog: http://blog.csdn.net/evankaka) * Time 2015.5.24*/@Componentpublic class Studentdaoimpl implements Studentdao {@Autowiredprivate jdbctemplate jdbctemplate; @Overridepublic void Create () { System.out.println ("Perform build table operation"), Jdbctemplate.execute ("DROP table IF EXISTS student"); Jdbctemplate.execute ("CREATE TABLE Student (ID int primary key, name varchar (+), age Int,sex varchar (2)) "); @Overridepublic void Insert (Student Student) {System.out.println ("================ performs insert operation ================"); Jdbctemplate.update ("INSERT into student VALUES ('" + student.getid () + "', '" + student.getname () + "', '" + student.getage () + "', '" + student.getsex () + "‘)");} @Overridepublic Student getstudent (Integer id) {System.out.println ("================ performs a lookup of a single data Operation ================");      String SQL = "SELECT * from Student where id =?";      Student Student = jdbctemplate.queryforobject (sql,new object[]{id},new studentmapper ()); return student;} @Overridepublic list<student> liststudents () {System.out.println ("================ perform find all operations ================"    );      List rows = Jdbctemplate.queryforlist ("SELECT * from student");      Iterator it = Rows.iterator ();          while (It.hasnext ()) {Map Studentmap = (map) it.next ();          System.out.print ("Student ID:" +studentmap.get ("id") + ";");          System.out.print ("Student Name:" +studentmap.get ("name") + ";");          System.out.print ("Age of the Student:" +studentmap.get ("age") + ";");      SYSTEM.OUT.PRINTLN ("Student Sex:" +studentmap.get ("Sex")); } return rows;  @Overridepublic void Delete (Integer id) {System.out.println ("================ performs the deletion of a single data Operation ================"); String SQL = "Delete from Student whEre id =? ";      Jdbctemplate.update (SQL, id);      System.out.println ("Deleted Record with id =" + ID); return;} @Overridepublic void Update (Student Student) {System.out.println ("================ perform update of single data Operation ================"); Jdbctemplate.update ("update student SET name =?, age=?,sex=?") WHERE id =? ", new object[] {student.getname (), Student.getage (), Student.getsex (), Student.getid ()});}

4. Spring Configuration

Create a new beans.xml 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:context= "Http://www.springframework.org/schema/context" xmlns:aop= "HTTP://WWW.SPRINGFRAMEWORK.ORG/SCHEMA/AOP" xsi:schemalocation= "Http://www.springframework.org/schem A/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd Http://www.springframewor K.ORG/SCHEMA/AOP http://www.springframework.org/schema/aop/spring-aop-3.0.xsd Http://www.springframe  Work.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd "><!--configuration Data source --><bean id= "DataSource" class= "Org.springframework.jdbc.datasource.DriverManagerDataSource" >< Property Name= "Driverclassname" value= "Com.mysql.jdbc.Driver"/><property name= "url" value= "jdbc:mysql:// Localhost:3306/test "/><property name=" username "value=" root "/>&lT;property name= "Password" value= "[email protected]"/></bean><!--Configure a JdbcTemplate instance and place this "shared", " Safe "instances injected into different DAO classes go to--><bean id=" JdbcTemplate "class=" Org.springframework.jdbc.core.JdbcTemplate ">< Property Name= "DataSource" ref= "DataSource"/></bean><!--automatic scanning of annotated bean--><context:component-scan Base-package= "Com.mucfc.dao"/></beans>

5. Test:

Package Com.mucfc.service;import Org.springframework.context.applicationcontext;import Org.springframework.context.support.classpathxmlapplicationcontext;import Com.mucfc.dao.studentdaoimpl;import Com.mucfc.model.student;public class Test {public static void main (string[] args) {ApplicationContext context = new class Pathxmlapplicationcontext ("Beans.xml"); Studentdaoimpl studentdaoimpl= (Studentdaoimpl) Context.getbean ("Studentdaoimpl"); Studentdaoimpl.create (); Student student1=new Student (1, "Red Red", 12, "female"); Studentdaoimpl.insert (STUDENT1); Studentdaoimpl.insert (New Student (2, "plainly", 16, "male")); Studentdaoimpl.insert (New Student (3, "Xiao Wang", 22, "male")); Studentdaoimpl.insert (New Student (4, "Xiao Liu", 15, "male"));  Studentdaoimpl.insert (New Student (5, "Zhang San", 23, "male")); Studentdaoimpl.liststudents (); System.out.println (Studentdaoimpl.getstudent (2)); Studentdaoimpl.update (New Student (2, "Daming", 15, "male")); System.out.println (Studentdaoimpl.getstudent (2)); Studentdaoimpl.delete (2); Studentdaoimpl.liststudents ();}}

Output Result:

Lin Bingwen Evankaka original works. Reprint please specify the source Http://blog.csdn.net/evankaka

"Spring" Spring JDBC principle and application example explained