在使用普通的JDBC数据库时，就会很麻烦的写不必要的代码来处理异常、打开和关闭数据库连接等。但 Spring JDBC 框架负责所有的底层细节，从开始打开连接、准备和执行 SQL 语句、处理异常、处理事务到最后关闭连接。
举一个简单的JdbcTemplate应用的例子，以后再进行详细说明。
【举例】
数据库：sqlserver
CREATE TABLE [dbo].[Student]( [id] [int] IDENTITY(1,1) NOT NULL, [name] [varchar](20) NOT NULL, [age] [int] NOT NULL )
pom.xml
<?xml version="1.0" encoding="UTF-8"?> <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.my</groupId> <artifactId>SpringStudy</artifactId> <version>1.0-SNAPSHOT</version> <properties> <spring.version>4.1.6.RELEASE</spring.version> </properties> <dependencies> <!--spring--> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-orm</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context-support</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-web</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-beans</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>javax.servlet</groupId> <artifactId>javax.servlet-api</artifactId> <version>3.1.0</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>3.8.1</version> <scope>test</scope> </dependency> <!-- AspectJ --> <dependency> <groupId>org.aspectj</groupId> <artifactId>aspectjrt</artifactId> <version>1.6.11</version> </dependency> <dependency> <groupId>org.aspectj</groupId> <artifactId>aspectjweaver</artifactId> <version>1.6.11</version> </dependency> <dependency> <groupId>cglib</groupId> <artifactId>cglib</artifactId> <version>2.1</version> </dependency> <!--c3p0--> <dependency> <groupId>com.mchange</groupId> <artifactId>c3p0</artifactId> <version>0.9.5.2</version> </dependency> <dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>sqljdbc4</artifactId> <version>4.0</version> </dependency> </dependencies> </project>
Beans.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" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd "> <!-- Initialization for data source --> <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <!-- 使用外部属性文件的属性 --> <property name="user" value="sa"></property> <property name="password" value="123"></property> <property name="driverClass" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"></property> <property name="jdbcUrl" value="jdbc:sqlserver://localhost:1433;DatabaseName=TEST"></property> <property name="initialPoolSize" value="5"></property> <property name="maxPoolSize" value="10"></property> </bean> <!--Definition for studentJDBCTemplate bean--> <bean id="studentJDBCTemplate" class="com.my.StudentJDBCTemplate"> <property name="dataSource" ref="dataSource"/> </bean> </beans>
程序：
package com.my; /** * @Author jyy * @Description {} * @Date 2018/8/8 16:40 */ public class Student { private Integer age; private String name; private Integer id; public void setAge(Integer age) { this.age = age; } public Integer getAge() { return age; } public void setName(String name) { this.name = name; } public String getName() { return name; } public void setId(Integer id) { this.id = id; } public Integer getId() { return id; } }
package com.my; import javax.sql.DataSource; import java.util.List; /** * @Author jyy * @Description {} * @Date 2018/8/8 16:39 */ public interface StudentDAO { /** * This is the method to be used to initialize * database resources ie. connection. */ public void setDataSource(DataSource ds); /** * This is the method to be used to create * a record in the Student table. */ public void create(String name, Integer age); /** * This is the method to be used to list down * a record from the Student table corresponding * to a passed student id. */ public Student getStudent(Integer id); /** * This is the method to be used to list down * all the records from the Student table. */ public List<Student> listStudents(); /** * This is the method to be used to delete * a record from the Student table corresponding * to a passed student id. */ public void delete(Integer id); /** * This is the method to be used to update * a record into the Student table. */ public void update(Integer id, Integer age); }
package com.my; import org.springframework.jdbc.core.JdbcTemplate; import javax.sql.DataSource; import java.util.List; /** * @Author jyy * @Description {} * @Date 2018/8/8 16:42 */ public class StudentJDBCTemplate implements StudentDAO { private DataSource dataSource; private JdbcTemplate jdbcTemplateObject; public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; this.jdbcTemplateObject = new JdbcTemplate(dataSource); } public void create(String name, Integer age) { String SQL = "insert into Student (name, age) values (?, ?)"; jdbcTemplateObject.update( SQL, name, age); System.out.println("Created Record Name = " + name + " Age = " + age); return; } public Student getStudent(Integer id) { String SQL = "select * from Student where id = ?"; Student student = jdbcTemplateObject.queryForObject(SQL, new Object[]{id}, new StudentMapper()); return student; } public List<Student> listStudents() { String SQL = "select * from Student"; List<Student> students = jdbcTemplateObject.query(SQL, new StudentMapper()); return students; } public void delete(Integer id){ String SQL = "delete from Student where id = ?"; jdbcTemplateObject.update(SQL, id); System.out.println("Deleted Record with ID = " + id ); return; } public void update(Integer id, Integer age){ String SQL = "update Student set age = ? where id = ?"; jdbcTemplateObject.update(SQL, age, id); System.out.println("Updated Record with ID = " + id ); return; } }
package com.my; import org.springframework.jdbc.core.RowMapper; import java.sql.ResultSet; import java.sql.SQLException; /** * @Author jyy * @Description {} * @Date 2018/8/8 16:41 */ 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")); return student; } }
package com.my; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import javax.sql.DataSource; import java.util.List; /** * @Author jyy * @Description {} * @Date 2018/7/13 10:06 */ public class MainApp { @Autowired static DataSource dataSource; public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext("Beans.xml"); StudentJDBCTemplate studentJDBCTemplate = (StudentJDBCTemplate) context.getBean("studentJDBCTemplate"); System.out.println("------Records Creation--------"); studentJDBCTemplate.create("Zara", 11); studentJDBCTemplate.create("Nuha", 2); studentJDBCTemplate.create("Ayan", 15); System.out.println("------Listing Multiple Records--------"); List<Student> students = studentJDBCTemplate.listStudents(); for (Student record : students) { System.out.print("ID : " + record.getId()); System.out.print(", Name : " + record.getName()); System.out.println(", Age : " + record.getAge()); } System.out.println("----Updating Record with ID = 2 -----"); studentJDBCTemplate.update(2, 20); System.out.println("----Listing Record with ID = 2 -----"); Student student = studentJDBCTemplate.getStudent(2); System.out.print("ID : " + student.getId()); System.out.print(", Name : " + student.getName()); System.out.println(", Age : " + student.getAge()); } }
输出结果：
------Records Creation-------- Created Record Name = Zara Age = 11 Created Record Name = Nuha Age = 2 Created Record Name = Ayan Age = 15 ------Listing Multiple Records-------- ID : 1, Name : Zara, Age : 11 ID : 2, Name : Nuha, Age : 20 ID : 3, Name : Ayan, Age : 15 ID : 4, Name : Zara, Age : 11 ID : 5, Name : Nuha, Age : 2 ID : 6, Name : Ayan, Age : 15 ----Updating Record with ID = 2 ----- Updated Record with ID = 2 ----Listing Record with ID = 2 ----- ID : 2, Name : Nuha, Age : 20