Spring框架学习（13）：JdbcTemplate和JdbcDaoSupport

这篇文章主要讲一下使用JdbcTemplate连接数据库

我在本地使用的数据库是MySQL5.7

为了使用JdbcTemplate和连接上数据库，需要添加c3p0的依赖包和mysql-connectorJ的依赖包,我是使用maven添加的依赖：

<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc --><dependency>    <groupId>org.springframework</groupId>    <artifactId>spring-jdbc</artifactId>    <version>4.3.7.RELEASE</version></dependency><!-- https://mvnrepository.com/artifact/org.springframework/spring-core --><dependency>    <groupId>org.springframework</groupId>    <artifactId>spring-core</artifactId>    <version>4.3.7.RELEASE</version></dependency><!-- https://mvnrepository.com/artifact/com.mchange/c3p0 --><dependency>    <groupId>com.mchange</groupId>    <artifactId>c3p0</artifactId>    <version>0.9.5.2</version></dependency><!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --><dependency>    <groupId>mysql</groupId>    <artifactId>mysql-connector-java</artifactId>    <version>6.0.6</version></dependency>

在MySQL中使用的数据表为：

下面写两个类：Department和employee

package jdbc;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;}@Overridepublic String toString() {return "Department [id=" + id + ", name=" + name + "]";}}

package jdbc;public class Employee {private Integer id;private String lastName;private String email;private Integer deptId;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 Integer getDeptId() {return deptId;}public void setDeptId(Integer deptId) {this.deptId = deptId;}@Overridepublic String toString() {return "Employee [id=" + id + ", lastName=" + lastName + ", email="+ email + ", deptId=" + deptId + "]";}}

然后写两个数据访问层的Dao类

package jdbc;import javax.sql.DataSource;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;/** *  * 不推荐使用JdbcDaoSupport，而推荐直接使用JdbcTempate作为Dao类的成员变量 * @author 86538 * */@Repositorypublic class DepartmentDao extends JdbcDaoSupport {@Autowiredpublic void setDataSource2(DataSource dataSource) {setDataSource(dataSource);}public Department get(Integer id) {String sql = "SELECT id, dept_name name FROM department WHERE id = ?";RowMapper<Department> rowMapper = new BeanPropertyRowMapper<>(Department.class);return getJdbcTemplate().queryForObject(sql, rowMapper, id);}}

package jdbc;import java.util.List;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 {@Autowiredprivate 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 employees = jdbcTemplate.queryForObject(sql, rowMapper, id);return employees;}}

在bean的配置文件中：

<?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.xsdhttp://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsdhttp://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd"><context:component-scan base-package="jdbc"></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><!-- 配置Spring的JdbcTemplate --><bean id="jdbcTemplate"class="org.springframework.jdbc.core.JdbcTemplate"><property name="dataSource" ref="dataSource"></property></bean><!-- 配置 NameParameterJdbcTemplate,该对象可以使用具名参数，其没有无参数的构造器，所以必须为其构造器指定参数--><bean id="namedParameterJdbcTemplate"class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate"><constructor-arg ref="dataSource"></constructor-arg></bean></beans>

property配置文件：

jdbc.user=rootjdbc.password=1234jdbc.driverclass=com.mysql.cj.jdbc.Driverjdbc.jdbcurl=jdbc:mysql:///spring-4?serverTimezone=UTC&useSSL=truejdbc.initPoolSize=5jdbc.maxPoolSize=10

然后再弄个junit的test文件测试一下效果：

package jdbc;import static org.junit.Assert.*;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);}/** * 使用具名参数时，可以使用NamedParameterJdbcTemplate.update(String sql, SqlParameterSource paramSource)  * 方法进行更新操作 * 1.SQL语句中的参数名和类的属性名一致 * 2.使用SqlParameterSource的BeanPropertySqlParameterSource实现类作为参数 */@Testpublic void testNamedParameterJdbcTemplate2() {String sql = "INSERT INTO employees(last_name, email, dept_id) VALUES(:lastName,:email,:deptId)";Employee employee = new Employee();employee.setLastName("XYZ");employee.setEmail("[e-mail]");employee.setDeptId(3);SqlParameterSource paramSource = new BeanPropertySqlParameterSource(employee);namedParameterJdbcTemplate.update(sql, paramSource);}/** * 可以为参数起名字，与？相比 * 1.好处：若有多个参数，则不用再去对应位置，直接对应参数名字，如last_name * 2.缺点：较为麻烦  */@Testpublic 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", "[e-mail]");paramMap.put("deptid", 2);namedParameterJdbcTemplate.update(sql, paramMap);}@Testpublic void testDepartmentDao() {System.out.println(departmentDao.get(1));}@Testpublic void testEmployeeDao() {System.out.println(employeeDao.get(1));}/** * 获取单个列的值，或做统计查询 */@Testpublic void testQueryForObject2() {String sql = "SELECT count(id) FROM employees";long count = jdbcTemplate.queryForObject(sql, Long.class);System.out.println(count);}/** * 查到实体类的集合 * 注意不是调用queryForList方法 */@Testpublic 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);}/** * 从数据库中获取一条记录，实际得到对应的对象 * 注意不是调用jdbcTemplate.queryForObject(sql, requiredType, args)方法 * 而是使用queryForObject(sql, rowMapper,1); * 1.其中RowMapper指定如何去映射结果集的行，常用的实现类为BeanPropertyRowMapper * 2.使用SQL中列的别名完成列名和类的属性名的映射，例如last_name lastName * 3.不支持级联属性，JdbcTemplate到底是一个JDBC的小工具，而不是ORM框架 */@Testpublic 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);}/** * 执行批量更新：批量的IUD * 最后一个参数是Object[]的list类型：因为修改一条记录需要一个Object的数组，那么多条就需要一个List */public void testBatchUpdate() {String sql = "INSERT INTO employees(id,last_name, email, dept_id) VALUES(?,?,?,?)";List<Object[]> batchArgs = new ArrayList<>();batchArgs.add(new Object[]{6,"AA", "[e-mail]", 1});batchArgs.add(new Object[]{7,"BB", "[e-mail]", 1});batchArgs.add(new Object[]{8,"CC", "[e-mail]", 1});batchArgs.add(new Object[]{9,"DD", "[e-mail]", 1});batchArgs.add(new Object[]{10,"EE","[e-mail]", 1});jdbcTemplate.batchUpdate(sql, batchArgs);}/* * INSERT,UPDATE,DELETE *///@Testpublic void testUpdata() {String sql = "UPDATE employees SET last_name = ? WHERE id = ?";jdbcTemplate.update(sql, "Jack", 5);}//@Testpublic void testDataSource() throws SQLException {DataSource dataSource = ctx.getBean(DataSource.class);System.out.println(dataSource.getConnection());}

运行之后可以看到程序是可以正常运行的。如果出问题，一般是因为依赖的jar包没有成功加进来或者是加的版本不对，可以在maven里面尝试多几个版本，还有就是MySQL的版本问题，在SQL5中driverClass是com.mysql.jdbc.Driver，jdbcurl是jdbc:mysql:///databaseName，在SQL6中driverClass是com.mysql.cj.jdbc.Driver,jdbcurl需要加上服务器时间域serverTimezone和是否使用加密useSSL等配置信息，出问题的话自己看报错信息一般都能解决。