一、Spring 和 junit 整合
1、所需 jar 包
commons-logging-1.1.3.jar
hamcrest-core-1.3.jar
junit-4.12.jar
spring-aop-4.0.0.RELEASE.jar
spring-beans-4.0.0.RELEASE.jar
spring-context-4.0.0.RELEASE.jar
spring-core-4.0.0.RELEASE.jar
spring-expression-4.0.0.RELEASE.jar
spring-test-4.0.0.RELEASE.jar
2、创建 Spring 配置文件
applicationContext.xml
1
| <context:component-scan base-package="com.atguigu.jdbc.component"/>
|
3、创建 empService
1 2 3 4 5
| package com.atguigu.jdbc.component.service;
@Service public class empService { }
|
4、创建 junit 测试类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
| @RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(value = {"classpath:applicationContext.xml"}) public class JDBCTest {
@Autowired private EmpService empService;
@Test public void testSpringJunit() { System.out.println("empService = " + empService); } }
|
二、JdbcTemplate
1、定位
为了学习声明式事务才用一下 JdbcTemplate,以后 Spring 整合 Mybatis 就不再使用 JdbcTemplate 了。
2、介绍
JdbcTemplate 是 Spring 封装的一套访问数据库的简易操作模板。使用的感觉很像 DBUtils。
3、搭建环境
① 导入 jar 包
使用 JdbcTemplate 需要额外导入 3 个 jar 包:
commons-logging-1.1.3.jar
druid-1.1.9.jar:连接数据库
hamcrest-core-1.3.jar
junit-4.12.jar
mysql-connector-java-5.1.37-bin.jar:连接数据库
spring-aop-4.0.0.RELEASE.jar
spring-beans-4.0.0.RELEASE.jar
spring-context-4.0.0.RELEASE.jar
spring-core-4.0.0.RELEASE.jar
spring-expression-4.0.0.RELEASE.jar
spring-jdbc-4.0.0.RELEASE.jar
spring-orm-4.0.0.RELEASE.jar
spring-test-4.0.0.RELEASE.jar
spring-tx-4.0.0.RELEASE.jar
② 准备外部属性文件 jdbc.properties
1 2 3 4
| wechat.dev.driver=com.mysql.jdbc.Driver wechat.dev.url=jdbc:mysql://localhost:3306/mybatis0223 wechat.dev.username=root wechat.dev.password=root
|
③ 配置 Spring 配置文件
applicationContext.xml
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
| <context:component-scan base-package="com.atguigu.jdbc.component"/>
<context:property-placeholder location="classpath:jdbc.properties"/>
<bean class="com.alibaba.druid.pool.DruidDataSource" id="druidDataSource"> <property name="url" value="${wechat.dev.url}"/> <property name="driverClassName" value="${wechat.dev.driver}"/> <property name="username" value="${wechat.dev.username}"/> <property name="password" value="${wechat.dev.password}"/> </bean>
<bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplate"> <property name="dataSource" ref="druidDataSource"/> </bean>
|
④junit 测试类
1 2 3 4 5 6 7 8 9 10 11 12 13 14
|
@Autowired private JdbcTemplate jdbcTemplate;
@Autowired private DataSource dataSource;
@Test public void testConnection() throws SQLException { Connection connection = dataSource.getConnection(); System.out.println("connection = " + connection); }
|
4、访问数据库
① 查询单个对象
先建立一个实体类
1 2 3 4 5 6 7 8 9 10 11
| package com.atguigu.jdbc.entity;
public class Employee {
private Integer empId;
private String empName;
private Double empSalary;
...
|
junit 测试类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
| @Test public void testQueryOne() {
String sql = "select emp_id,emp_name,emp_salary from t_emp where emp_id=?";
Integer empId = 8;
BeanPropertyRowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, empId);
System.out.println("employee = " + employee); }
|
② 查询 List
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
| @Test public void testQueryList() {
String sql = "select emp_id,emp_name,emp_salary from t_emp where emp_salary>?";
Integer empSalary = 3000;
BeanPropertyRowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
List<Employee> employeeList = jdbcTemplate.query(sql, rowMapper, empSalary);
for (Employee employee : employeeList) { System.out.println("employee = " + employee); } }
|
③ 查询单个值
1 2 3 4 5 6 7 8 9 10 11 12
| @Test public void testQuerySingleValue() { String sql = "select max(emp_salary) from t_emp";
Double maxSalary = jdbcTemplate.queryForObject(sql, Double.class);
System.out.println("maxSalary = " + maxSalary); }
|
④ 增删改
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
| @Test public void testUpdate() {
String sql = "insert into t_emp(emp_name,emp_salary) values(?,?)";
String empName = "maomi"; Double empSalary = 6000.00;
int rowNunber = jdbcTemplate.update(sql, empName, empSalary);
System.out.println("rowNunber = " + rowNunber);
}
|
⑤ 批量操作
一次性执行很多条 SQL 语句。为了提高效率,我们不是把 SQL 语句一条一条的发生给数据库,而是一次性发送很多条,让数据库批量执行。因为批量执行,能够节约网络传输所消耗的时间。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
| @Test public void batchUpdate() {
String sql = "insert into t_emp(emp_name,emp_salary) values(?,?)";
List<Object[]> batchSQLParam = new ArrayList<>();
for (int i = 0; i < 10; i++) {
Object[] paramArr = new Object[2];
paramArr[0] = "empName" + i; paramArr[1] = (i + 1) * 1000.00;
batchSQLParam.add(paramArr); }
int[] rowCountArr = jdbcTemplate.batchUpdate(sql, batchSQLParam);
for (int count : rowCountArr) { System.out.println("count = " + count); } }
|
5、封装 Dao
封装 DAO 类的目的,是为了屏蔽调用 jdbcTemplate 方法的细节,让持久化层和业务逻辑层能够彼此分开,实现解耦。
最终实现的效果:业务逻辑层只需要提供要操作数据库的数据,而不必关心这些数据是如何通过调用 jdbcTemplate 的方法来操作数据库的。让业务逻辑层可以保持代码的纯正——仅包含也业务功能相关的代码。
为了让代码能够更好的实现解耦合,我们声明一个 DAO 的接口,按照面向接口编程的思路封装 DAO 类。
首先声明一个 DAO 的接口:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
| package com.atguigu.jdbc.component.dao.api;
public interface IEmpDao {
Employee selectEmpById(Integer empId);
List<Employee> selectAll();
Integer selectEmpCount();
Integer updateEmployee(Employee emp);
Integer deleteEmployeeById(Integer empId);
Integer insertEmployee(Employee emp);
}
|
下面是接口的实现类:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71
| package com.atguigu.jdbc.component.dao.impl;
@Repository public class EmpDaoImp implements IEmpDao {
@Autowired private JdbcTemplate jdbcTemplate;
@Override public Employee selectEmpById(Integer empId) {
String sql = "select emp_id,emp_name,emp_salary from t_emp where emp_id=?";
BeanPropertyRowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, empId);
return employee; }
@Override public List<Employee> selectAll() {
String sql = "select emp_id,emp_name,emp_salary from t_emp";
List<Employee> employeeList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Employee.class));
return employeeList; }
@Override public Integer selectEmpCount() {
String sql = "select count(*) from t_emp";
return jdbcTemplate.queryForObject(sql, Integer.class); }
@Override public Integer updateEmployee(Employee emp) {
String sql = "update t_emp set emp_name=?,emp_salary=? where emp_id=?";
return jdbcTemplate.update(sql, emp.getEmpName(), emp.getEmpSalary(), emp.getEmpId()); }
@Override public Integer deleteEmployeeById(Integer empId) {
String sql = "delete from t_emp where emp_id=?";
return jdbcTemplate.update(sql, empId); }
@Override public Integer insertEmployee(Employee emp) {
String sql = "insert into t_emp(emp_name,emp_salary) values(?,?)";
int rowNunber = jdbcTemplate.update(sql, emp.getEmpName(), emp.getEmpSalary());
return rowNunber; } }
|
实现自动装配
1 2
| @Autowired private IEmpDao empDao;
|
junit 测试类
1 2 3 4 5 6 7 8 9
| @Test public void testEmpDao() {
Integer count = empDao.deleteEmployeeById(6);
System.out.println("count = " + count);
}
|