Spring JDBC
- Spring JDBC是Spring框架用于处理关系型数据库的模块
- Spring JDBC对JDBC API进行封装,极大简化开发工作量
- JdbcTemplate是Spring JDBC核心类,提供数据CRUD方法
MyBatis封装程度较高,适合中小企业进行敏捷开发,让程序员快速完成与数据库交互操作,执行效率低。
Spring JDBC 只是对原始的JDBC的API进行简单封装,轻量级,效率高,因为Spring底层有IOC容器存在,比原生JDBC容易管理。
Spring JDBC的使用步骤
- Maven工程引入依赖spring-jdbc
- applicationContext.xml配置DataSource数据源(用于指向连接哪种数据库、哪台地址、哪台服务器、用户名密码)
- 在Dao注入JdbcTemplate对象,实现数据CRUD(增删改查)
添加依赖
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.2.6.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.6.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>5.2.6.RELEASE</version>
</dependency>
<!--LogBack日志组件,Spring框架默认集成-->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.2.3</version>
</dependency>
</dependencies>
添加配置文件 applicationContext.xml
<?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"
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">
<!--数据源设置 保存JDBC基础信息
DriverManagerDataSource JDBC 连接数据库需要的参数
-->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<!--引入的驱动-->
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url"
value="jdbc:mysql://localhost:3306/数据库名?useSSL=false&ampuseUnicode=true&ampcharacterEncoding=UTF-8&ampserverTimezone=Asia/Shanghai&ampallowPublicKeyRetrieval=true"/>
<property name="username" value="root"/>
<property name="password" value="密码"/>
</bean>
<!--JDBCTemplate提供CRUD(增删改查)的API
在IOC初始化的时候完成jdbcTemplate对象与数据源绑定工作
--
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!--绑定数据源-->
<property name="dataSource" ref="dataSource"/>
</bean>
<bean id="employeeDao" class="com.imooc.spring.jdbc.dao.EmployeeDao">
<!--为DAO注入jdbcTemplate对象-->
<property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>
<bean id="employeeService" class="com.imooc.spring.jdbc.service.EmployeeService">
<property name="employeeDao" ref="employeeDao"/>
<property name="transactionManager" ref="transactionManager"/>
</bean>
<!--事务管理器-->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>
实体类
public class Employee {
private Integer eno;
private String ename;
private Float salary;
private String dname;
private Date hiredata;
public Integer getEno() {
return eno;
}
public void setEno(Integer eno) {
this.eno = eno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public Float getSalary() {
return salary;
}
public void setSalary(Float salary) {
this.salary = salary;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public Date getHiredata() {
return hiredata;
}
public void setHiredata(Date hiredata) {
this.hiredata = hiredata;
}
@Override
public String toString() {
return "Employee{" +
"eno=" + eno +
", ename='" + ename + '\'' +
", salary=" + salary +
", dname='" + dname + '\'' +
", hiredata=" + hiredata +
'}';
}
}
public class EmployeeDao {
// 在IOC容器已经配置了JdbcTemplate 所以在容器启动的时候会自动的初始化
// 后期使用时用配置方式注入到DAO的JdbcTemplate属性中就可以了
private JdbcTemplate jdbcTemplate;
// 按照编号查询员工
public Employee findById(Integer eno) {
String sql = "select * from employee where eno=?";
// BeanPropertyRowMapper 从数据库记录到实体对象转化 含义是:将Bean的属性和每一行的列进行一一对应 实例类的属性命名和顺序和数据库的一样 将每一条记录转换为实体对象
// 用于查询单条数据
return jdbcTemplate.queryForObject(sql, new Object[]{eno},
new BeanPropertyRowMapper<>(Employee.class));
}
}
配置中这样写
<bean id="employeeDao" class="com.imooc.spring.jdbc.dao.EmployeeDao">
<!--为DAO注入jdbcTemplate对象-->
<property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>
public class SpringApplication {
public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext(
"classpath:applicationContext.xml");
EmployeeDao employeeDao = context.getBean("employeeDao", EmployeeDao.class);
Employee employee = employeeDao.findById(100);
System.out.println(employee);
}
}
创建一个测试用例类
// Junit控制权交给Spring
@RunWith(SpringJUnit4ClassRunner.class)
// 加载配置文件
@ContextConfiguration(locations = {"classpath:applicationContext.xml"})
public class JdbcTemplateTestor {
@Resource
private EmployeeDao employeeDao;
@Resource
private EmployeeService employeeService;
@Test
public void testFindById() {
Employee employee = employeeDao.findById(100);
System.out.println(employee);
}
}
EmployeeDao中添加查询部门员工的SQL语句
public List<Employee> findByDName(String dname) {
String sql = "select * from employee where dname=?";
// 用于查询复合数据
// BeanPropertyRowMapper 对象和记录之间的封装和转换
return jdbcTemplate.query(sql, new Object[]{dname}, new BeanPropertyRowMapper<>(Employee.class));
}
@Test
public void testFindByDName() {
System.out.println(employeeDao.findByDName("市场部"));
}
// 模拟无法进行有效的实体属性映射
public List<Map<String, Object>> findMapByDame(String dname) {
// 将查询结果作为Map进行封装
String sql = "select eno as empno,salary as s from employee where dname=?";
// 查询结果作为列表返回,同时默认情况将每一条数据按照map对象进行包裹
// key 原始字段名 value 字段名对应的数值
List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, dname);
return maps;
}
@Test
public void testFindMapByName() {
System.out.println(employeeDao.findMapByDame("研发部"));
}
增删改操作
public void insert(Employee employee) {
String sql = "insert into employee(eno,ename,salary,dname,hiredate) values(?,?,?,?,?)";
jdbcTemplate.update(sql, employee.getEno(), employee.getEname(), employee.getSalary(), employee.getDname(),
employee.getHiredata());
}
public int update(Employee employee) {
String sql = "update employee set ename=?,salary=?,dname=?,hiredate=? where eno=?";
// 几个问好 添加几个数据,位置要对应
return jdbcTemplate.update(sql, employee.getEname(), employee.getSalary(), employee.getDname(),
employee.getHiredata(), employee.getEno());
}
public int delete(Integer eno) {
String sql = "delete from employee where eno=?";
return jdbcTemplate.update(sql, eno);
}
@Test
public void testInsert() {
Employee employee = new Employee();
employee.setEno(101);
employee.setEname("赵六");
employee.setSalary(100000f);
employee.setDname("入职时间");
employee.setHiredata(new Date());
employeeDao.insert(employee);
}
@Test
public void testUpdate() {
Employee employee = employeeDao.findById(101);
employee.setSalary(20000f);
int count = employeeDao.update(employee);
}
@Test
public void delete() {
for (int i = 1; i <= 10; i++) {
int count = employeeDao.delete(8000 + i);
System.out.println("本次删除" + count + "条数据");
}
}
Spring 事务管理
什么是事务
- 事务是以一种可靠的、一致的方式,访问和操作数据库的程序单元
- 说人话:要么把事情做完,要么什么都不做,不要做一半
- 事务依赖于数据库实现,MySQL通过事务区作为数据缓冲地带
编程式事务
- 编程式事务是指通过代码手动提交回滚事务的事务控制方法
- SpringJDBC通过TransactionManager事务管理器实现事务控制
- 事务管理器提供commit/rollback方法进行事务提交与回滚
添加事务管理器,控制事务的提交和回滚
<!--事务管理器-->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<bean id="employeeService" class="com.imooc.spring.jdbc.service.EmployeeService">
<property name="employeeDao" ref="employeeDao"/>
<property name="transactionManager" ref="transactionManager"/>
</bean>
public class EmployeeService {
private EmployeeDao employeeDao;
// 注入事务管理器
private DataSourceTransactionManager transactionManager;
public void batchImport() {
// 定义了事务默认的标准配置
TransactionDefinition transactionDefinition = new DefaultTransactionDefinition();
// 开始一个事务,返回事务状态,事务状态说明当前事务执行阶段
TransactionStatus status = transactionManager.getTransaction(transactionDefinition);
try {
for (int i = 1; i <= 10; i++) {
if (i == 3) {
// throw new RuntimeException("意料之外的异常");
}
Employee employee = new Employee();
employee.setEno(8000 + i);
employee.setEname("员工" + i);
employee.setSalary(4000F + i);
employee.setDname("市场部");
employee.setHiredata(new Date());
employeeDao.insert(employee);
}
// 提交事务
transactionManager.commit(status);
} catch (RuntimeException e) {
// 回滚事务
transactionManager.rollback(status);
throw e;
}
}
public EmployeeDao getEmployeeDao() {
return employeeDao;
}
public void setEmployeeDao(EmployeeDao employeeDao) {
this.employeeDao = employeeDao;
}
public DataSourceTransactionManager getTransactionManager() {
return transactionManager;
}
public void setTransactionManager(DataSourceTransactionManager transactionManager) {
this.transactionManager = transactionManager;
}
}
@Test
public void testBatchImport() {
employeeService.batchImport();
System.out.println("批量导入成功");
}
声明式事务
- 声明式事务指在不修改源码情况下通过配置形式自动实现事务控制,声明式事务本质就是AOP环绕通知
- 当目标方法执行成功时,自动提交事务
- 当目标方法抛出运行时异常时,自动事务回滚
配置过程
- 配置TransactionManager事务管理器
- 配置事务通知与事务属性
- 为事务通知绑定PointCut切点(执行范围)
需要在配置文件中添加一个新的依赖
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.9.5</version>
</dependency>
添加配置 这三者相辅相成,缺一不可
<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"
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/tx
https://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/aop
https://www.springframework.org/schema/aop/spring-aop.xsd">
<!--1.事务管理器,用于创建事务/提交/回滚-->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<!--2.事务通知配置,决定哪些方法使用事务,哪些方法不使用事务-->
<tx:advice id="txAdvice" transaction-manager="transactionManager">
<tx:attributes>
<!--目标方法名为batchImport时,启动声明式事务,成功提交,运行异常回滚-->
<tx:method name="batchImport" propagation="REQUIRED"/>
</tx:attributes>
</tx:advice>
<!--定义声明式事务的作用范围-->
<aop:config>
<aop:pointcut id="pointcut" expression="execution(* com.imooc..*Service.*(..))"/>
<aop:advisor advice-ref="txAdvice" pointcut-ref="pointcut"/>
</aop:config>
</beans>
测试一下
public void batchImport() {
for (int i = 1; i <= 10; i++) {
if (i == 3) {
throw new RuntimeException("意料之外的异常");
}
Employee employee = new Employee();
employee.setEno(8000 + i);
employee.setEname("员工" + i);
employee.setSalary(4000F + i);
employee.setDname("市场部");
employee.setHiredata(new Date());
employeeDao.insert(employee);
}
}
@Test
public void testBatchImport() {
employeeService.batchImport();
System.out.println("批量导入成功");
}
<!--2.事务通知配置,决定哪些方法使用事务,哪些方法不使用事务-->
<tx:advice id="txAdvice" transaction-manager="transactionManager">
<tx:attributes>
<!--目标方法名为batchImport时,启动声明式事务,成功提交,运行异常回滚-->
<tx:method name="batchImport" propagation="REQUIRED"/>
<tx:method name="batch*" propagation="REQUIRED"/>
<!-- NOT_SUPPORTED不支持事务 read-only只读 -->
<!--设置所有findXXX方法不需要使用事务-->
<tx:method name="find*" propagation="NOT_SUPPORTED" read-only="true"/>
<tx:method name="get*" propagation="NOT_SUPPORTED" read-only="true"/>
<!--以上条件都不成立 使用事务/不使用事务自己决定-->
<!-- <tx:method name="*" propagation="NOT_SUPPORTED" read-only="true"/>-->
<tx:method name="*" propagation="REQUIRED" />
</tx:attributes>
</tx:advice>
事务传播行为
- 事务传播行为是指多个拥有事务的方法在嵌套调用时的事务控制方式
- XML:<tx:method name="…" propagation=“REQUIRED”/>
- 注解:@Transactional(propagation=Propagation.REQUIRED)
演示
public class BatchService {
private EmployeeDao employeeDao;
public void importJob1() {
for (int i = 1; i <= 10; i++) {
Employee employee = new Employee();
employee.setEno(8000 + i);
employee.setEname("研发部员工" + i);
employee.setSalary(4000F + i);
employee.setDname("研发部");
employee.setHiredata(new Date());
employeeDao.insert(employee);
}
}
public void importJob2() {
for (int i = 1; i <= 10; i++) {
Employee employee = new Employee();
employee.setEno(9000 + i);
employee.setEname("市场部员工" + i);
employee.setSalary(4500F + i);
employee.setDname("市场部");
employee.setHiredata(new Date());
employeeDao.insert(employee);
}
}
public EmployeeDao getEmployeeDao() {
return employeeDao;
}
public void setEmployeeDao(EmployeeDao employeeDao) {
this.employeeDao = employeeDao;
}
}
<bean id="batchService" class="com.imooc.spring.jdbc.service.BatchService">
<property name="employeeDao" ref="employeeDao"/>
</bean>
public class EmployeeService {
private BatchService batchService;
// 这两个方法之间应该互相不影响
public void startImportJob() {
batchService.importJob1();
if (1 == 1) {
throw new RuntimeException("意料之外的异常");
}
batchService.importJob2();
System.out.println("批量导入成功");
}
}
内侧事务会加入到外侧的事务中
需要让两个方法,每个方法单独使用一个新事务,而不是加入到外侧的事务中。
<!--REQUIRES_NEW每次运行都会产生新的事务-->
<tx:method name="importJob1" propagation="REQUIRES_NEW"/>
<tx:method name="importJob2" propagation="REQUIRES_NEW"/>
注解配置声明式事务
编写配置文件
<?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"
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/tx
https://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/aop
https://www.springframework.org/schema/aop/spring-aop.xsd">
<!--设置扫描的基准包-->
<context:component-scan base-package="com.imooc"/>
<!--数据源-->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url"
value="jdbc:mysql://localhost:3306/imooc?useSSL=false&ampuseUnicode=true&ampcharacterEncoding=UTF-8&ampserverTimezone=Asia/Shanghai&ampallowPublicKeyRetrieval=true"/>
<property name="username" value="root"/>
<property name="password" value="youbenshashi"/>
</bean>
<!--jdbcTemplate-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
<!--事务管理器-->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<!--启用注解形式声明事务-->
<tx:annotation-driven transaction-manager="transactionManager"/>
</beans>
通过IOC完成对象的实例化以及创建的工作
@Repository
public class EmployeeDao {
//配置文件中写过了 对象注入
@Resource
private JdbcTemplate jdbcTemplate;
public Employee findById(Integer eno) {
String sql = "select * from employee where eno=?";
// BeanPropertyRowMapper 从数据库记录到实体对象转化
// 用于查询单条数据
return jdbcTemplate.queryForObject(sql, new Object[]{eno},
new BeanPropertyRowMapper<>(Employee.class));
}
public List<Employee> findByDName(String dname) {
String sql = "select * from employee where dname=?";
// 用于查询复合数据
return jdbcTemplate.query(sql, new Object[]{dname}, new BeanPropertyRowMapper<>(Employee.class));
}
public List<Map<String, Object>> findMapByDame(String dname) {
// 将查询结果作为Map进行封装
String sql = "select eno as empno,salary as s from employee where dname=?";
List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, dname);
return maps;
}
public void insert(Employee employee) {
String sql = "insert into employee(eno,ename,salary,dname,hiredate) values(?,?,?,?,?)";
jdbcTemplate.update(sql, employee.getEno(), employee.getEname(), employee.getSalary(), employee.getDname(),
employee.getHiredata());
}
public int update(Employee employee) {
String sql = "update employee set ename=?,salary=?,dname=?,hiredate=? where eno=?";
return jdbcTemplate.update(sql, employee.getEname(), employee.getSalary(), employee.getDname(),
employee.getHiredata(), employee.getEno());
}
public int delete(Integer eno) {
String sql = "delete from employee where eno=?";
return jdbcTemplate.update(sql, eno);
}
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
}
@Service
@Transactional(propagation = Propagation.NOT_SUPPORTED, readOnly = true)
public class BatchService {
@Resource
private EmployeeDao employeeDao;
@Transactional(propagation = Propagation.REQUIRES_NEW)
public void importJob1() {
for (int i = 1; i <= 10; i++) {
Employee employee = new Employee();
employee.setEno(8000 + i);
employee.setEname("研发部员工" + i);
employee.setSalary(4000F + i);
employee.setDname("研发部");
employee.setHiredata(new Date());
employeeDao.insert(employee);
}
}
@Transactional(propagation = Propagation.REQUIRES_NEW)
public void importJob2() {
for (int i = 1; i <= 10; i++) {
Employee employee = new Employee();
employee.setEno(9000 + i);
employee.setEname("市场部员工" + i);
employee.setSalary(4500F + i);
employee.setDname("市场部");
employee.setHiredata(new Date());
employeeDao.insert(employee);
}
}
public EmployeeDao getEmployeeDao() {
return employeeDao;
}
public void setEmployeeDao(EmployeeDao employeeDao) {
this.employeeDao = employeeDao;
}
}
@Service
// 声明式核心注解
// 放在类上,将声明式事务配置应用于当前类所有方法,默认事务传播方式为 REQUIRED
@Transactional(propagation = Propagation.REQUIRED)
public class EmployeeService {
@Resource
private EmployeeDao employeeDao;
private DataSourceTransactionManager transactionManager;
@Resource
private BatchService batchService;
// 单独设置 非事务
@Transactional(propagation = Propagation.NOT_SUPPORTED ,readOnly = true)
public Employee findById(Integer eno) {
return employeeDao.findById(eno);
}
public void batchImport() {
for (int i = 1; i <= 10; i++) {
if (i == 3) {
throw new RuntimeException("意料之外的异常");
}
Employee employee = new Employee();
employee.setEno(8000 + i);
employee.setEname("员工" + i);
employee.setSalary(4000F + i);
employee.setDname("市场部");
employee.setHiredata(new Date());
employeeDao.insert(employee);
}
}
public void startImportJob() {
batchService.importJob1();
if (1 == 1) {
throw new RuntimeException("意料之外的异常");
}
batchService.importJob2();
System.out.println("批量导入成功");
}
public EmployeeDao getEmployeeDao() {
return employeeDao;
}
public void setEmployeeDao(EmployeeDao employeeDao) {
this.employeeDao = employeeDao;
}
public DataSourceTransactionManager getTransactionManager() {
return transactionManager;
}
public void setTransactionManager(DataSourceTransactionManager transactionManager) {
this.transactionManager = transactionManager;
}
public BatchService getBatchService() {
return batchService;
}
public void setBatchService(BatchService batchService) {
this.batchService = batchService;
}
}