23Spring使用JdbcTemplate和JdbcDaoSupport
数据库：
CREATE DATABASE IF NOT EXISTS `spring` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `spring`; -- MySQL dump 10.13 Distrib 5.6.13, for Win32 (x86) -- -- Host: localhost Database: spring -- ------------------------------------------------------ -- Server version 5.6.14 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `departments` -- DROP TABLE IF EXISTS `departments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `departments` ( `id` int(11) NOT NULL, `dept_name` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `departments` -- LOCK TABLES `departments` WRITE; /*!40000 ALTER TABLE `departments` DISABLE KEYS */; INSERT INTO `departments` VALUES (1,'财务部'),(2,'开发部'),(3,'人事部'),(4,'公关部'); /*!40000 ALTER TABLE `departments` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `employees` -- DROP TABLE IF EXISTS `employees`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `last_name` varchar(45) DEFAULT NULL, `email` varchar(45) DEFAULT NULL, `dept_id` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `employees` -- LOCK TABLES `employees` WRITE; /*!40000 ALTER TABLE `employees` DISABLE KEYS */; INSERT INTO `employees` VALUES (1,'Tom','tom.@163.com','1'),(2,'Jerry','Jerry@126.com','2'),(3,'Mike','Mike@sohu.com','3'),(4,'Rose','Rose@sina.com','3'),(5,'jack','ATGUIGU@163.com','2'); /*!40000 ALTER TABLE `employees` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2015-06-24 23:10:05
代码：
package jdbc; /** * Created by jecyhw on 2015/6/24. */ public class Employee { private Integer id; private String lastName; private String email; private Department department; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public Department getDepartment() { return department; } public void setDepartment(Department department) { this.department = department; } @Override public String toString() { return "Employee{" + "id=" + id + ", lastName='" + lastName + '\'' + ", email='" + email + '\'' + ", department=" + department + '}'; } }
package jdbc; /** * Created by jecyhw on 2015/6/24. */ public class Department { private Integer id; private String name; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } @Override public String toString() { return "Department{" + "id=" + id + ", name='" + name + '\'' + '}'; } }
package 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; /** * Created by jecyhw on 2015/6/24. */ @Repository public 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; } }
package jdbc; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport; import org.springframework.stereotype.Repository; import javax.sql.DataSource; /** * 不推荐使用JdbcDaoSupport，而推荐直接使用JdbcTemplate作为Dao类的成员变量 */ @Repository public class DepartmentDao extends JdbcDaoSupport { @Autowired public void setDataSource2(DataSource dataSource) { setDataSource(dataSource); } public Department get(Integer id) { String sql = "select id, dept_name name from departments where id=?"; RowMapper<Department> rowMapper = new BeanPropertyRowMapper<>(Department.class); return getJdbcTemplate().queryForObject(sql, rowMapper, id); } }
package jdbc; 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 javax.sql.DataSource; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; /** * Created by jecyhw on 2015/6/24. */ public class JDBCTest { private ApplicationContext ctx = null; private JdbcTemplate jdbcTemplate; private EmployeeDao employeeDao; private DepartmentDao departmentDao; { ctx = new ClassPathXmlApplicationContext("23-1.xml"); jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate"); employeeDao = ctx.getBean(EmployeeDao.class); departmentDao = ctx.getBean(DepartmentDao.class); } @Test public void testDepartmentDao() { System.out.println(departmentDao.get(1)); } @Test public void testEmployeeDao() { System.out.println(employeeDao.get(1)); } /** * 获取单个列的值，或做统计查询 * 使用queryForObject(String sql, Class<T> requiredType) */ 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, 2); System.out.println(employees); } /** * 从数据库中获取一条记录，实际得到对应的一个对象 * 注意不是调用queryForObject(String sql, Class<T> requiredType, Object... args) * 需要调用queryForObject(String sql, RowMapper<T> 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@lalala.com", 1}); batchArgs.add(new Object[] {"bb", "bb@lalala.com", 1}); batchArgs.add(new Object[] {"cc", "cc@lalala.com", 1}); batchArgs.add(new Object[] {"dd", "dd@lalala.com", 1}); batchArgs.add(new Object[] {"ee", "ee@lalala.com", 1}); 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()); } }
//db.Properties文件
jdbc.user=root jdbc.password=123654 jdbc.driverClass=com.mysql.jdbc.Driver jdbc.jdbcUrl=jdbc:mysql:///spring jdbc.initPoolSize=5 jdbc.maxPoolSize=10
<?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/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd"> <!--导入资源文件--> <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> <!--自动扫描--> <context:component-scan base-package="jdbc"></context:component-scan> <!--配置Spring的JdbcTemplate--> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"></property> </bean> </beans>
