Spring學習之對JDBC的支援

Spring學習之對JDBC的支援
JdbcTemplate簡介為了使 JDBC 更加便於使用, Spring 在 JDBC API 上定義了一個抽象層, 以此建立一個 JDBC 存取架構.作為 Spring JDBC 架構的核心, JDBC 模板的設計目的是為不同類型的 JDBC 操作提供模板方法. 每個模板方法都能控制整個過程, 並允許覆蓋過程中的特定任務. 通過這種方式, 可以在儘可能保留靈活性的情況下, 將資料庫存取的工作量降到最低. JdbcTemplate類被設計成為安全執行緒的, 所以可以再 IOC 容器中聲明它的單個執行個體, 並將這個執行個體注入到所有的 DAO 執行個體中. 用 sql語句和參數更新資料庫:public int update(String sql,Object... args) throws DataAccessException 批次更新資料庫:public int[] batchUpdate(String sql,List<Object[]> batchArgs) 查詢單行:public <T> T  queryForObject(String sql,parameterizedRowsMapper<T> rm, Object... args) throws DataAccessException 查詢多行:public <T> List<T>  query(String sql,parameterizedRowsMapper<T> rm, Object... args) throws DataAccessException 單值查詢:public <T> T  queryForObject(String sql,Class<T> requiredType, Object... args) throws DataAccessException

 //設定檔db.propertiesjdbc.user=rootjdbc.password=1230jdbc.driverClass=com.mysql.jdbc.Driverjdbc.jdbcUrl=jdbc:mysql:///springjdbc.initPoolSize=5jdbc.maxPoolSize=10//IOC配置<?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:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd  http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd  http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd">   <context:component-scan base-package="com.atguigu.spring"></context:component-scan>   <!-- 匯入資源檔 --> <context:property-placeholder location="classpath:db.properties"/>   <!-- 配置 C3P0 資料來源 --> <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>   <!-- 配置 Spirng 的 JdbcTemplate --> <bean id="jdbcTemplate"   class="org.springframework.jdbc.core.JdbcTemplate">  <property name="dataSource" ref="dataSource"></property> </bean>   </beans>//測試JdbcTemplate package com.atguigu.spring.jdbc;import java.sql.SQLException;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;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;import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;import org.springframework.jdbc.core.namedparam.SqlParameterSource;public class JDBCTest {   private ApplicationContext ctx = null; private JdbcTemplate jdbcTemplate; private EmployeeDao employeeDao; private DepartmentDao departmentDao; private NamedParameterJdbcTemplate namedParameterJdbcTemplate;   {  ctx = new ClassPathXmlApplicationContext("applicationContext.xml");  jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");  employeeDao = ctx.getBean(EmployeeDao.class);  departmentDao = ctx.getBean(DepartmentDao.class);  namedParameterJdbcTemplate = ctx.getBean(NamedParameterJdbcTemplate.class); }   /**  * 使用具名參數時, 可以使用 update(String sql, SqlParameterSource paramSource) 方法進行更新操作  * 1. SQL 陳述式中的參數名和類的屬性一致!  * 2. 使用 SqlParameterSource 的 BeanPropertySqlParameterSource 實作類別作為參數.   */ @Test public void testNamedParameterJdbcTemplate2(){  String sql = "INSERT INTO employees(last_name, email, dept_id) "    + "VALUES(:lastName,:email,:dpetId)";     Employee employee = new Employee();  employee.setLastName("XYZ");  employee.setEmail("xyz@sina.com");  employee.setDpetId(3);     SqlParameterSource paramSource = new BeanPropertySqlParameterSource(employee);  namedParameterJdbcTemplate.update(sql, paramSource); }   /**  * 可以為參數起名字.   * 1. 好處: 若有多個參數, 則不用再去對應位置, 直接對應參數名, 便於維護  * 2. 缺點: 較為麻煩.   */ @Test public void testNamedParameterJdbcTemplate(){  String sql = "INSERT INTO employees(last_name, email, dept_id) VALUES(:ln,:email,:deptid)";     Map<String, Object> paramMap = new HashMap<>();  paramMap.put("ln", "FF");  paramMap.put("email", "ff@atguigu.com");  paramMap.put("deptid", 2);     namedParameterJdbcTemplate.update(sql, paramMap); }   @Test public void testDepartmentDao(){  System.out.println(departmentDao.get(1)); }   @Test public void testEmployeeDao(){  System.out.println(employeeDao.get(1)); }   /**  * 擷取單個列的值, 或做統計查詢  * 使用 queryForObject(String sql, Class<Long> requiredType)   */ @Test public void testQueryForObject2(){  String sql = "SELECT count(id) FROM employees";  long count = jdbcTemplate.queryForObject(sql, Long.class);     System.out.println(count); }   /**  * 查到實體類的集合  * 注意調用的不是 queryForList 方法  */ @Test public void testQueryForList(){  String sql = "SELECT id, last_name lastName, email FROM employees WHERE id > ?";  RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);  List<Employee> employees = jdbcTemplate.query(sql, rowMapper,5);     System.out.println(employees); }   /**  * 從資料庫中擷取一條記錄, 實際得到對應的一個對象  * 注意不是調用 queryForObject(String sql, Class<Employee> requiredType, Object... args) 方法!  * 而需要調用 queryForObject(String sql, RowMapper<Employee> rowMapper, Object... args)  * 1. 其中的 RowMapper 指定如何去映射結果集的行, 常用的實作類別為 BeanPropertyRowMapper  * 2. 使用 SQL 中列的別名完成列名和類的屬性名稱的映射. 例如 last_name lastName  * 3. 不支援級聯屬性. JdbcTemplate 到底是一個 JDBC 的小工具, 而不是 ORM 架構  */ @Test public void testQueryForObject(){  String sql = "SELECT id, last_name lastName, email, dept_id as \"department.id\" FROM employees WHERE id = ?";  RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);  Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, 1);     System.out.println(employee); }   /**  * 執行批次更新: 批量的 INSERT, UPDATE, DELETE  * 最後一個參數是 Object[] 的 List 類型: 因為修改一條記錄需要一個 Object 的數組, 那麼多條不就需要多個 Object 的數組嗎  */ @Test public void testBatchUpdate(){  String sql = "INSERT INTO employees(last_name, email, dept_id) VALUES(?,?,?)";     List<Object[]> batchArgs = new ArrayList<>();     batchArgs.add(new Object[]{"AA", "aa@atguigu.com", 1});  batchArgs.add(new Object[]{"BB", "bb@atguigu.com", 2});  batchArgs.add(new Object[]{"CC", "cc@atguigu.com", 3});  batchArgs.add(new Object[]{"DD", "dd@atguigu.com", 3});  batchArgs.add(new Object[]{"EE", "ee@atguigu.com", 2});     jdbcTemplate.batchUpdate(sql, batchArgs); }   /**  * 執行 INSERT, UPDATE, DELETE  */ @Test public void testUpdate(){  String sql = "UPDATE employees SET last_name = ? WHERE id = ?";  jdbcTemplate.update(sql, "Jack", 5); }   @Test public void testDataSource() throws SQLException {  DataSource dataSource = ctx.getBean(DataSource.class);  System.out.println(dataSource.getConnection()); }}//開發中dao的代碼package com.atguigu.spring.jdbc;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 {   @Autowired private JdbcTemplate jdbcTemplate;   public Employee get(Integer id){  String sql = "SELECT id, last_name lastName, email FROM employees WHERE id = ?";  RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);  Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, id);     return employee; }}