Spring--JdbcTemplate

来源：互联网发布：数据库里如何切换用户编辑：程序博客网时间：2024/05/21 22:36

JdbcTemplate简介

为了使JDBC更加易于使用，Spring在JDBC API上定义了一个抽象层，以此建立一个JDBC存取框架
作为Spring JDBC框架的核心，JDBC模板的设计目的是为不同类型的JDBC操作提供模板方法，每个模板方法都能控制整个过程，并允许覆盖过程中的特定任务。通过这种方式，可以在尽可能保留灵活性的情况下，将数据库存取的工作量降到最低。

代码示例：

首先是数据库的配置。数据库中包含employee和department两张表。

1 jdbc.user=scott2 jdbc.password=tiger3 jdbc.driverClass=oracle.jdbc.driver.OracleDriver4 jdbc.jdbcUrl=jdbc:oracle:thin:@localhost:1521:orcl5 6 jdbc.initialPoolSize=57 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     @Override43     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     @Override19     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 @Repository10 public class EmployeeDao {11     12     @Autowired13     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 }

0 0