[Java知识库]Spring JDBC

• Spring JDBC 与 JdbcTemplate
• 声明式事务配置方式
• 声明式事务七种事务传播方法

Spring JDBC

• Spring JDBC是Spring框架用于处理关系型数据库的模块
• Spring JDBC对JDBC API 进行封装，极大简化开发工作量
• JDBCTemplate是Spring JDBC核心类，提供数据CRUD方法

有MyBatis为什么还需要Spring JDBC?

Spring JDBC 效率高

Spring JDBC 的使用步骤

• Maven 工程引入依赖spring-jdbc
• applicationContext.xml配置datasource数据源
• 在Dao注入JdbcTemplate对象，实现CRUD

JDBCTemplate实现增删改查

package com.imooc.spring.jdbc.dao;

import com.imooc.spring.jdbc.entity.Employee;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

/**
 * todo {类简要说明}
 *
 * @Author wangw
 * @Date 2022/12/12 23:00
 * @Version 1.0
 */
public class EmployeeDao {
    private JdbcTemplate jbJdbcTemplate;

    public Employee findById(Integer eno) {
        String sql = "select * from employee where eno = ?";
        Employee employee = jbJdbcTemplate.queryForObject(sql, new Object[]{eno}, new BeanPropertyRowMapper<Employee>(Employee.class));
        return employee;
    }


    public JdbcTemplate getJbJdbcTemplate() {
        return jbJdbcTemplate;
    }

    public void setJbJdbcTemplate(JdbcTemplate jbJdbcTemplate) {
        this.jbJdbcTemplate = jbJdbcTemplate;
    }
}

package com.imooc.spring.jdbc.entity;

import java.util.Date;

/**
 * todo {类简要说明}
 *
 * @Author wangw
 * @Date 2022/12/12 22:58
 * @Version 1.0
 */

public class Employee {
    private Integer eno;
    private String ename;
    private Float salary;
    private String dName;
    private Date hireDate;

    @Override
    public String toString() {
        return "Employee{" +
                "eno=" + eno +
                ", ename='" + ename + '\'' +
                ", salary=" + salary +
                ", dName='" + dName + '\'' +
                ", hireDate=" + hireDate +
                '}';
    }

    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 getHireDate() {
        return hireDate;
    }

    public void setHireDate(Date hireDate) {
        this.hireDate = hireDate;
    }
}

package com.imooc.spring.jdbc;

import com.imooc.spring.jdbc.dao.EmployeeDao;
import com.imooc.spring.jdbc.entity.Employee;
import javafx.application.Application;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

/**
 * todo {类简要说明}
 *
 * @Author wangw
 * @Date 2022/12/12 23:05
 * @Version 1.0
 */
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(3308);
        System.out.println(employee);

    }
}

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        https://www.springframework.org/schema/beans/spring-beans.xsd">
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/imooc1?useUnicode=true&amp;characterEncoding=utf8&amp;characterSetResults=utf8"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
    </bean>
    <!-- JdbcTemplate 提供了数据CRUD的API-->
    <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">
        <property name="jbJdbcTemplate" ref="jdbcTemplate"/>
    </bean>
</beans>

Spring JDBC 其他API使用

package com.imooc.spring.jdbc.dao;

import com.imooc.spring.jdbc.entity.Employee;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;
import java.util.Map;

/**
 * todo {类简要说明}
 *
 * @Author wangw
 * @Date 2022/12/12 23:00
 * @Version 1.0
 */
public class EmployeeDao {
    private JdbcTemplate jbJdbcTemplate;

    public Employee findById(Integer eno) {
        String sql = "select * from employee where eno = ?";
        Employee employee = jbJdbcTemplate.queryForObject(sql, new Object[]{eno}, new BeanPropertyRowMapper<Employee>(Employee.class));
        return employee;
    }

    public List findByDname(String dname) {
        String sql = "select * from employee where dname = ?";
        List<Employee> employee = jbJdbcTemplate.query(sql, new Object[]{dname}, new BeanPropertyRowMapper<Employee>(Employee.class));
        return employee;
    }

    public List findByDnameMap(String dname) {
        String sql = "select eno as empno,salary as s from employee where dname = ?";
        List<Map<String, Object>> employee = jbJdbcTemplate.queryForList(sql, new Object[]{dname});
        return employee;
    }

    public void insert(Employee employee){
        String sql = "insert into employee(eno,ename,salary,dname,hiredate) values(?,?,?,?,?)";
        jbJdbcTemplate.update(sql,new Object[]{employee.getEno(),employee.getEname(),employee.getSalary(),employee.getdName(),employee.getHireDate()});
    }

    public void update(Employee employee){
        String sql = "update employee set ename =? ,salary =? ,dname =?,hiredate =? where eno =?";
        Integer count =jbJdbcTemplate.update(sql,new Object[]{employee.getEname(),employee.getSalary(),employee.getdName(),employee.getHireDate(),employee.getEno()});
    }

    public void delete (Integer eno){
        String sql = "delete from  employee  where eno =?";
        Integer count =jbJdbcTemplate.update(sql,new Object[]{eno});
    }

    public JdbcTemplate getJbJdbcTemplate() {
        return jbJdbcTemplate;
    }

    public void setJbJdbcTemplate(JdbcTemplate jbJdbcTemplate) {
        this.jbJdbcTemplate = jbJdbcTemplate;
    }
}

import com.imooc.spring.jdbc.dao.EmployeeDao;
import com.imooc.spring.jdbc.entity.Employee;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.annotation.Resource;
import java.util.Date;
import java.util.List;

/**
 * todo {类简要说明}
 *
 * @Author wangw
 * @Date 2022/12/12 23:11
 * @Version 1.0
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:applicationContext.xml")
public class JdbcTemplateTestor {
    @Resource
    private EmployeeDao employeeDao;

    @Test
    public void testFindById(){
        Employee employee = employeeDao.findById(3308);
        System.out.println(employee);
    }

    @Test
    public void testFindByName(){
        List employee = employeeDao.findByDname("研发部");
        System.out.println(employee);
    }

    @Test
    public void testFindByNameMap(){
        List employee = employeeDao.findByDnameMap("研发部");
        System.out.println(employee);
    }
    @Test
    public void testInsert(){
      Employee employee =new Employee(8888,"wangw",66666f,"研发部",new Date());
     employeeDao.insert(employee);
    }
    @Test
    public void testUpdate(){
        Employee employee =new Employee(8888,"wangwei",66666f,"研发部",new Date());
        employeeDao.update(employee);
    }
    @Test
    public void testDelete(){

        employeeDao.delete(8888);
    }

}

编程式事务

什么是事务

• 事务是一种可靠的，一致的方式，访问和操作数据库的程序单元
• 说人话：要么把事情做完，要么什么都不做，不要做一半
• 事务依赖于数据库实现，Mysql通过事务区作为数据缓冲地带

编程式事务

• 编程式事务是指通过代码手动提交回滚事务的事务控制方式
• Spring JDBC 通过TransactionManager事务管理器实现事务控制
• 事务管理器提供commit/rollback方法进行事务提交与回滚

使用Spring之后，会自动集成logback

<!-- 事务管理器-->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>

package com.imooc.spring.jdbc.service;

import com.imooc.spring.jdbc.dao.EmployeeDao;
import com.imooc.spring.jdbc.entity.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Service;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import javax.annotation.Resource;
import java.util.Date;

/**
 * todo {类简要说明}
 *
 * @Author wangw
 * @Date 2022/12/13 22:34
 * @Version 1.0
 */
@Service
public class EmployeeService {
    @Resource
    private EmployeeDao employeeDao;
    @Resource
    private DataSourceTransactionManager transactionManager;
    public void batchImport(){
        // 1.0 默认事务定义 标准配置
        TransactionDefinition definition = new DefaultTransactionDefinition();
        //2.0 开始一个事务    返回事务状态，说明当前事务执行阶段
       TransactionStatus status = transactionManager.getTransaction(definition);
        try {
            for (int i = 0; i < 10; i++) {
                /*if (i==5){
                    throw new RuntimeException("发生异常");
                }*/
                Employee employee =new Employee(8000+i,"员工"+i,4000f,"市场部",new Date());
                employeeDao.insert(employee);
            }
        } catch (Exception e) {
            // 3.0 事务回滚
            transactionManager.rollback(status);
            e.printStackTrace();
        }
        // 4.0 提交事务
        transactionManager.commit(status);
    }
}

@Test
    public void testBatch(){
        employeeService.batchImport();
    }

声明式事务

• 声明式事务是指在不修改源代码的情况下通过配置形式自动实现事务控制，声明式事务本质是AOP环绕通知
• 当目标方法成功时，自动提交事务
• 当目标抛出运行时异常时，自动事务回滚

配置过程

• 配置TransactionManager事务管理器
• 配置事务通知与事务属性
• 为事务通知绑定切点

 <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
            <version>1.9.6</version>
</dependency>     

  <!--1.创建事务管理器 -->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <!--事务通知配置，决定那些方式使用事务 -->
    <tx:advice id="txAdvice" transaction-manager="transactionManager">
        <tx:attributes>
            <!-- 目标方法为batchImport时，启用声明式事务，成功提交，运行异常时回滚-->
            <tx:method name="batchImport" propagation="REQUIRED"/>
            <tx:method name="batch*"propagation="REQUIRED"/>
            <!-- 设置所有查询方法不需要使用事务-->
            <tx:method name="find*" propagation="NOT_SUPPORTED" read-only="true"/>
            <tx:method name="get*" propagation="NOT_SUPPORTED" read-only="true"/>

            <tx:method name="*" 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>