Spring--JdbcTemplate
JdbcTemplate简介
- 为了使JDBC更加易于使用,Spring在JDBC API上定义了一个抽象层,以此建立一个JDBC存取框架
- 作为Spring JDBC框架的核心,JDBC模板的设计目的是为不同类型的JDBC操作提供模板方法,每个模板方法都能控制整个过程,并允许覆盖过程中的特定任务。通过这种方式,可以在尽可能保留灵活性的情况下,将数据库存取的工作量降到最低。
代码示例:
首先是数据库的配置。数据库中包含employee和department两张表。
1 jdbc.user=scott 2 jdbc.password=tiger 3 jdbc.driverClass=oracle.jdbc.driver.OracleDriver 4 jdbc.jdbcUrl=jdbc:oracle:thin:@localhost:1521:orcl 5 6 jdbc.initialPoolSize=5 7 jdbc.maxPoolSize=10
然后是spring的配置文件
1 <?xml version="1.0" encoding="UTF-8"?> 2 <beans xmlns="http://www.springframework.org/schema/beans" 3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 4 xmlns:context="http://www.springframework.org/schema/context" 5 xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd 6 http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd"> 7 8 <context:component-scan base-package="com.yl.spring.jdbc"></context:component-scan> 9 10 <!-- 导入属性文件 --> 11 <context:property-placeholder location="classpath:db.properties"/> 12 <!-- 配置c3p0数据源 --> 13 <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> 14 <property name="user" value="${jdbc.user}"></property> 15 <property name="password" value="${jdbc.password}"></property> 16 <property name="driverClass" value="${jdbc.driverClass}"></property> 17 <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property> 18 19 <property name="initialPoolSize" value="${jdbc.initialPoolSize}"></property> 20 <property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property> 21 </bean> 22 23 <!-- 配置Spring的JdbcTemplate --> 24 <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> 25 <property name="dataSource" ref="dataSource"></property> 26 </bean> 27 </beans>
然后是两个实体类:Employee和Department
1 package com.yl.spring.jdbc; 2 3 public class Employee { 4 private Integer id; 5 private String lastName; 6 private String email; 7 8 private Department department; 9 10 public Integer getId() { 11 return id; 12 } 13 14 public void setId(Integer id) { 15 this.id = id; 16 } 17 18 public String getLastName() { 19 return lastName; 20 } 21 22 public void setLastName(String lastName) { 23 this.lastName = lastName; 24 } 25 26 public String getEmail() { 27 return email; 28 } 29 30 public void setEmail(String email) { 31 this.email = email; 32 } 33 34 public Department getDepartment() { 35 return department; 36 } 37 38 public void setDepartment(Department department) { 39 this.department = department; 40 } 41 42 @Override 43 public String toString() { 44 return "Employee [id=" + id + ", lastName=" + lastName + ", email=" 45 + email + ", department=" + department + "]"; 46 } 47 48 49 }
1 package com.yl.spring.jdbc; 2 3 public class Department { 4 private Integer id; 5 private String name; 6 public Integer getId() { 7 return id; 8 } 9 public void setId(Integer id) { 10 this.id = id; 11 } 12 public String getName() { 13 return name; 14 } 15 public void setName(String name) { 16 this.name = name; 17 } 18 @Override 19 public String toString() { 20 return "Department [id=" + id + ", name=" + name + "]"; 21 } 22 23 24 }
然后是测试类
1 package com.yl.spring.jdbc; 2 3 4 import java.sql.SQLException; 5 import java.util.ArrayList; 6 import java.util.List; 7 8 import javax.sql.DataSource; 9 10 import org.junit.Test; 11 import org.springframework.context.ApplicationContext; 12 import org.springframework.context.support.ClassPathXmlApplicationContext; 13 import org.springframework.jdbc.core.BeanPropertyRowMapper; 14 import org.springframework.jdbc.core.JdbcTemplate; 15 import org.springframework.jdbc.core.RowMapper; 16 17 public class JDBCTest { 18 19 private ApplicationContext ctx = null; 20 private JdbcTemplate jdbcTemplate; 21 private EmployeeDao employeeDao; 22 23 { 24 ctx = new ClassPathXmlApplicationContext("applicationContext.xml"); 25 jdbcTemplate = (JdbcTemplate)ctx.getBean("jdbcTemplate"); 26 employeeDao = ctx.getBean(EmployeeDao.class); 27 } 28 29 @Test 30 public void testEmployeeDao() { 31 System.out.println(employeeDao.get(1)); 32 } 33 34 /** 35 * 获取单个列的值,或做统计查询 36 */ 37 @Test 38 public void testQueryForObject2() { 39 String sql = "SELECT count(id) FROM employee"; 40 long count = jdbcTemplate.queryForObject(sql, Long.class); 41 System.out.println(count); 42 } 43 44 /** 45 * 查到实体类的集合 46 */ 47 @Test 48 public void testQueryForList() { 49 String sql = "SELECT id, last_name, email FROM employee WHERE id > ?"; 50 RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<Employee>(Employee.class); 51 List<Employee> employees = jdbcTemplate.query(sql, rowMapper, 5); 52 System.out.println(employees); 53 } 54 55 /** 56 * 从数据库中获取一条记录,实际得到对应的一个对象 57 * 1.RowMapper指定如何映射结果集的行,常用的实现类为BeanPropertyRowMapper 58 * 2.使用SQL中列的别名完成列名和类的属性名的映射。例如last_name和lastName 59 * 3.不支持级联属性。JdbcTemplate到底是一个JDBC工具,不是ORM框架。 60 */ 61 @Test 62 public void TestForQueryObject() { 63 String sql = "SELECT id, last_name, email FROM employee WHERE id = ?"; 64 RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<Employee>(Employee.class); 65 66 Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, 1); 67 System.out.println(employee); 68 } 69 70 /** 71 * 批量执行更新:批量的INSERT, UPDATE, DELETE 72 * 最后一个参数是Object[]的list类型:因为修改一条记录需要一个Object的数组,那么多条就是需要多个Object的数组 73 */ 74 @Test 75 public void testBatchUpdate() { 76 String sql = "INSERT INTO employee(id, last_name, email, dept_id) VALUES(?, ?, ?, ?)"; 77 List<Object[]> batchArgs = new ArrayList<Object[]>(); 78 batchArgs.add(new Object[]{6, "AA", "AA@163.com", 1}); 79 batchArgs.add(new Object[]{7, "BB", "BB@163.com", 2}); 80 batchArgs.add(new Object[]{8, "CC", "CC@163.com", 3}); 81 batchArgs.add(new Object[]{9, "DD", "DD@163.com", 3}); 82 batchArgs.add(new Object[]{10, "EE", "EE@163.com", 2}); 83 jdbcTemplate.batchUpdate(sql, batchArgs); 84 } 85 86 /** 87 * 执行INSERT, UPDATE, DELETE 88 */ 89 @Test 90 public void testUpdate() { 91 String sql = "UPDATE employee SET last_name=? WHERE id=?"; 92 jdbcTemplate.update(sql, "Jack", 5); 93 } 94 95 @Test 96 public void test() throws SQLException { 97 DataSource dataSource = (DataSource)ctx.getBean("dataSource"); 98 System.out.println(dataSource.getConnection()); 99 } 100 101 }
如果在项目中使用JdbcTemplate,那么可以参考下面的写法来写对应的dao:
1 package com.yl.spring.jdbc; 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 @Repository 10 public class EmployeeDao { 11 12 @Autowired 13 private JdbcTemplate jdbcTemplate; 14 15 public Employee get(Integer id) { 16 String sql = "SELECT id, last_name, email FROM employee WHERE id = ?"; 17 RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<Employee>(Employee.class); 18 19 Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, id); 20 21 return employee; 22 } 23 }