Spring JDBC

原创已于 2022-11-10 00:23:13 修改 · 513 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#spring

于 2022-11-10 00:20:14 首次发布

Spring入门专栏收录该内容

16 篇文章

订阅专栏

本文介绍了Spring JDBC作为Spring框架处理关系型数据库的模块，它对JDBC API进行了封装，简化了开发工作。讨论了在有Mybatis的情况下，为什么仍然需要Spring JDBC，特别是在大型项目中的性能考量。详细阐述了Spring JDBC的使用步骤、配置、JdbcTemplate的数据查询和增删改查操作，并深入探讨了Spring的事务管理，包括事务的概念、编程式事务、声明式事务以及事务的传播行为。最后，提到了注解形式的声明式事务及其应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、什么是Spring JDBC

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

二、有Mybatis为什么还需要SpringJDBC？

Mybatis作为ORM框架，封装程度较高，适合中小企业敏捷开发，让程序员快速的完成与数据库的交互工作
Spring JDBC只是对原始的JDBC API 进行简单封装，在大型项目开发中，如果数据量并发量过高并且使用mybatis，微小的性能差异会导致执行效率变慢

1、Spring JDBC的使用步骤

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

三、Spring JDBC 配置

Spring JDBC 三项依赖
- Spring-context
- Spring-jdbc
- mysql-connection-java
applicationContext.xml配置文件中定义数据源

     <!-- 数据源 -->
        <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/learn?useSSL=false&amp;allowPublicKeyRetrieval=true&amp;serverTimezone=UTC"/>
            <property name="username" value="root"/>
            <property name="password" value="123456"/>
        </bean>

定义jdbcTemplate对象并将设置好的dataSource注入到jdbcTemplate中

  <!-- JdbcTemplate 提供数据CRUD API -->
        <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
            <property name="dataSource" ref="datasource"/>
        </bean>

四、JdbcTemplate数据查询方法

方法	说明
queryForObject()	查询单条数据
query()	查询复合数据
queryForList()	查询结果转换为List

package com.learn.spring.jdbc.dao;

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

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

public class EmployeeDao {
    private JdbcTemplate jdbcTemplate;

    public Employee findById(Integer eno){
        String sql = "select * from employee where eno = ?";
        // queryForObject() 查询单条数据
        Employee employee =  jdbcTemplate.queryForObject(sql,
                new Object[]{eno}, new BeanPropertyRowMapper<>(Employee.class));
        return employee;
    }


    public List<Employee> findByDname(String dname){
        String sql = "select * from employee where dname = ?";
        //查询复合数据
        List<Employee> list = jdbcTemplate.query(sql, new Object[]{dname}, new BeanPropertyRowMapper<Employee>(Employee.class));
        return list;
    }

    public Object findMapDname(String dname){
        String sql = "select eno as empno, salary as s from employee where dname = ?";
        //将查询结果作为Map进行封装
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, new Object[]{dname});
        return maps;
    }

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
}

import com.learn.spring.jdbc.dao.EmployeeDao;
import com.learn.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;

@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 testFindByDname(){
       System.out.println(employeeDao.findByDname("市场部"));
   }

   @Test
    public void testFindMapByDname(){
       System.out.println(employeeDao.findMapDname("研发部"));
   }
}

五、JdbcTemplate实现增删改查

update() 删除、修改、新增

package com.learn.spring.jdbc.dao;

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

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

public class EmployeeDao {
    private JdbcTemplate jdbcTemplate;

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

    public int update(Employee employee){
        String sql = "update employee set ename = ?, salary = ?, dname = ?, hiredate = ? where eno = ?";
        int count = jdbcTemplate.update(sql, new Object[]{
                employee.getEname(),
                employee.getSalary(),
                employee.getDname(),
                employee.getHiredate(),
                employee.getEno()
        });
        return count;
    }

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

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
}
@Test
    public void testInsert(){
       Employee employee = new Employee(8888, "赵六", 6666f, "研发部", new Date());
       employeeDao.insert(employee);
   }

   @Test
    public void testUpdate(){
       Employee employee = employeeDao.findById(8888);
       employee.setSalary(employee.getSalary() + 1000);
       int count = employeeDao.update(employee);
       System.out.println("本次更新" + count + "条数据");
   }

   @Test
    public void testDelete(){
       int count = employeeDao.delete(8888);
       System.out.println("本次删除" + count + "条数据");
   }

六、Spring 事务管理

1.什么是事务

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

2.编程式事务

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

3.Spring两种事务

编程式事务
- 使用程序代码手动控制提交与回滚commit/rollback
声明式事务

4.案例：一次性插入10条完整的员工数据，出现意料之外问题什么也不做

package com.learn.spring.jdbc.service;

import com.learn.spring.jdbc.dao.EmployeeDao;
import com.learn.spring.jdbc.entity.Employee;
import com.sun.org.apache.bcel.internal.generic.IF_ACMPEQ;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import java.util.Date;

public class EmployeeService {
    private EmployeeDao employeeDao;
    private DataSourceTransactionManager transactionManager;

    public void batchImport(){
        //定义了事务默认的标准配置
        TransactionDefinition definition = new DefaultTransactionDefinition();
        //开始一个事务,返回事务状态，事务状态说明当前事务的执行阶段
        TransactionStatus status = transactionManager.getTransaction(definition);
        try {
            for (int i = 1; i <= 10; i++) {
            
                Employee employee = new Employee();
                employee.setEno(8000 + i);
                employee.setEname("员工");
                employee.setSalary(4000f);
                employee.setDname("市场部");
                employee.setHiredate(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;
    }
}

5.声明式事务

声明式事务指在不修改源码的情况下通过配置的形式自动实现事务控制，声明式事务本质就是AOP环绕通知
声明式事务触发机制
- 当目标方法执行成功时，自动提交事务
- 当目标方法抛出运行时异常时，自动事务回滚
声明式事务配置过程
- 创建TransactionManager事务管理器
- 配置事务通知与事务属性
- 为事务通知绑定PointCut点确定作用范围

   <!-- 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"/>
                <!-- 设置所有batch*使用事务 -->
                <tx:method name="batch*" propagation="REQUIRED"/>
                <!-- 设置所有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"/>
            </tx:attributes>
        </tx:advice>

    <!-- 定义声明式事务的范围 -->
    <aop:config>
        <aop:pointcut id="pointcut" expression="execution(* com.learn..*Service.*(..))"/>
        <aop:advisor advice-ref="txAdvice" pointcut-ref="pointcut"/>
    </aop:config>

6.事务传播行为

事务传播行为是指多个拥有事务的方法嵌套调用时的事务控制方式
事务传播行为设置：
- XML:<tx:method name=“…” propagation=“REQUIRED”>
- 注解：@Transactional(propagation=Propagation.REQUIRED)
事务传播行为的七种类型

事务传播类型
PROPAGATION_REQUIRED(默认)	如果当前没有事务，就新建一个事务，如果已经存在一个事务，加入到这个事务中，这是常见的选择
PROPAGATION_SUPPORTS	支持当前事务，如果当前没有事务，就以非事务方式执行
PROPAGATION_MANDATORY	使用当前的事务，如果当前没有事务，就抛出异常
PROPAGATION_REQUIRES_NEW	新建事务，如果当前存在事务，就当前事务挂起
PROPAGATION_NOT_SUPPORTED	以非事务方式执行操作，如果当前存在事务，就把当前事务挂起
PROPAGATION_NEVER	以非事务方式执行，如果当前存在事务，则抛出异常
PROPAGATION_NESTED	如果当前存在事务，则在嵌套事务内执行，如果当前没有事务，则执行PROPAGATION_REQUIRED类似的操作

代码实例：

	<!-- 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"/>
                <!-- 设置所有batch*使用事务 -->
                <tx:method name="batch*" propagation="REQUIRED"/>
                <!-- 设置所有findxxx方法不需要使用事务-->
                <tx:method name="find*" propagation="NOT_SUPPORTED" read-only="true"/>
                <tx:method name="get*" propagation="NOT_SUPPORTED" read-only="true"/>
                <!-- 所有方法都支持事务-->
                <tx:method name="importJob1" propagation="REQUIRES_NEW"/>
                <tx:method name="importJob2" propagation="REQUIRES_NEW"/>
                <tx:method name="*" propagation="REQUIRED"/>
            </tx:attributes>
        </tx:advice>

    <!-- 定义声明式事务的范围 -->
    <aop:config>
        <aop:pointcut id="pointcut" expression="execution(* com.learn..*Service.*(..))"/>
        <aop:advisor advice-ref="txAdvice" pointcut-ref="pointcut"/>
    </aop:config>

package com.learn.spring.jdbc.dao;

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

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

public class EmployeeDao {
    private JdbcTemplate jdbcTemplate;

    public Employee findById(Integer eno){
        String sql = "select * from employee where eno = ?";
        // queryForObject() 查询单条数据
        Employee employee =  jdbcTemplate.queryForObject(sql,
                new Object[]{eno}, new BeanPropertyRowMapper<>(Employee.class));
        return employee;
    }


    public List<Employee> findByDname(String dname){
        String sql = "select * from employee where dname = ?";
        //查询复合数据
        List<Employee> list = jdbcTemplate.query(sql, new Object[]{dname}, new BeanPropertyRowMapper<Employee>(Employee.class));
        return list;
    }

    public Object findMapDname(String dname){
        String sql = "select eno as empno, salary as s from employee where dname = ?";
        //将查询结果作为Map进行封装
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, new Object[]{dname});
        return maps;
    }

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

    public int update(Employee employee){
        String sql = "update employee set ename = ?, salary = ?, dname = ?, hiredate = ? where eno = ?";
        int count = jdbcTemplate.update(sql, new Object[]{
                employee.getEname(),
                employee.getSalary(),
                employee.getDname(),
                employee.getHiredate(),
                employee.getEno()
        });
        return count;
    }

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

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
}

package com.learn.spring.jdbc.service;

import com.learn.spring.jdbc.dao.EmployeeDao;
import com.learn.spring.jdbc.entity.Employee;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import java.util.Date;

public class EmployeeService {
    private EmployeeDao employeeDao;
    private DataSourceTransactionManager transactionManager;
    private BatchService batchService;

    public void batchImport(){
        //定义了事务默认的标准配置
        TransactionDefinition definition = new DefaultTransactionDefinition();
        //开始一个事务,返回事务状态，事务状态说明当前事务的执行阶段
        TransactionStatus status = transactionManager.getTransaction(definition);
        try {
            for (int i = 1; i <= 10; i++) {
               /* if (i == 3) {
                    throw new RuntimeException("意料之外的异常");
                }*/
                Employee employee = new Employee();
                employee.setEno(8000 + i);
                employee.setEname("员工");
                employee.setSalary(4000f);
                employee.setDname("市场部");
                employee.setHiredate(new Date());
                employeeDao.insert(employee);
            }
            // 提交事务
            transactionManager.commit(status);
        } catch (RuntimeException e){
            // 回滚事务
            transactionManager.rollback(status);
            throw e;
        }
    }

    public void startImportJob(){
        batchService.importJob1();
        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;
    }
}

七、注解形式声明式事务

@Transactional(propagation=“”, readOnly=“”)
- 声明式事务的核心注解
- 放在类上，将声明式事务配置应用于当前类所有方法，默认事务传播为REQUIRED

package com.learn.spring.jdbc.service;

import com.learn.spring.jdbc.dao.EmployeeDao;
import com.learn.spring.jdbc.entity.Employee;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

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

@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++) {
             /*   if (i == 3) {
                    throw new RuntimeException("意料之外的异常");
                }*/
            Employee employee = new Employee();
            employee.setEno(8000 + i);
            employee.setEname("研发部员工");
            employee.setSalary(4000f);
            employee.setDname("研发部");
            employee.setHiredate(new Date());
            employeeDao.insert(employee);
        }
    }

    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void importJob2() {
        for (int i = 1; i <= 10; i++) {
                if (i == 3) {
                    throw new RuntimeException("意料之外的异常");
                }
            Employee employee = new Employee();
            employee.setEno(9000 + i);
            employee.setEname("市场部员工");
            employee.setSalary(4500f);
            employee.setDname("市场部");
            employee.setHiredate(new Date());
            employeeDao.insert(employee);
        }
    }



    public EmployeeDao getEmployeeDao() {
        return employeeDao;
    }

    public void setEmployeeDao(EmployeeDao employeeDao) {
        this.employeeDao = employeeDao;
    }
}

package com.learn.spring.jdbc.service;

import com.learn.spring.jdbc.dao.EmployeeDao;
import com.learn.spring.jdbc.entity.Employee;
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.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.support.DefaultTransactionDefinition;

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

@Service
// 声明式事务的核心注解
// 放在类上，将声明式事务配置应用于当前类所有方法，默认事务传播为REQUIRED
@Transactional
public class EmployeeService {
    @Resource
    private EmployeeDao employeeDao;
    @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("员工");
                employee.setSalary(4000f);
                employee.setDname("市场部");
                employee.setHiredate(new Date());
                employeeDao.insert(employee);
            }
    }  
    
    public void startImportJob(){
        batchService.importJob1();
        batchService.importJob2();
        System.out.println("批量导入成功");
    }


    public EmployeeDao getEmployeeDao() {
        return employeeDao;
    }

    public void setEmployeeDao(EmployeeDao employeeDao) {
        this.employeeDao = employeeDao;
    }


    public BatchService getBatchService() {
        return batchService;
    }

    public void setBatchService(BatchService batchService) {
        this.batchService = batchService;
    }
}

<?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" xmlns:bean="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        https://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        https://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/tx
        http://www.springframework.org/schema/tx/spring-tx.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd">
    <bean:component-scan base-package="com.learn"/>
        <!-- 数据源 -->
        <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/learn?useSSL=false&amp;
            useUnicode=true&amp;
            characterEncoding=UTF-8&amp;
            serverTimezone=Asia/Shanghai&amp;
            allowPublicKeyRetrieval=true"/>
            <property name="username" value="root"/>
            <property name="password" value="123456"/>
        </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>