Spring 中使用JDBC事务

我就是我 2024-03-24 13:08 21阅读 0赞

Spring 框架对 JDBC 进行封装，使用 JdbcTemplate 方便实现对数据库操作。

#### 准备环境： ####

1、搭建子模块：spring-jdbc-tx

2、添加依赖

<dependencies>
            
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-jdbc</artifactId>
                <version>6.0.6</version>
            </dependency>
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.32</version>
            </dependency>
            
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.2.16</version>
            </dependency>
        </dependencies>

3、创建jdbc.properties

jdbc.user=root
    jdbc.password=root
    jdbc.url=jdbc:mysql://localhost:3306/spring?characterEncoding=utf8&useSSL=false
    jdbc.driver=com.mysql.cj.jdbc.Driver

4、配置 Spring 的配置文件 beans.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">
    
        
        <context:property-placeholder location="classpath:jdbc.properties" />
    
        
        <bean id="druidDataSource" class="com.alibaba.druid.pool.DruidDataSource">
            <property name="url" value="${jdbc.url}"/>
            <property name="driverClassName" value="${jdbc.driver}"/>
            <property name="username" value="${jdbc.user}"/>
            <property name="password" value="${jdbc.password}"/>
        </bean>
    
        
        <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
            
            <property name="dataSource" ref="druidDataSource"/>
        </bean>
    
    </beans>

5、准备数据库与测试表

CREATE DATABASE `spring`;
    
    use `spring`;
    
    CREATE TABLE `t_emp` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT NULL COMMENT '姓名',
      `age` int(11) DEFAULT NULL COMMENT '年龄',
      `sex` varchar(2) DEFAULT NULL COMMENT '性别',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

6、实现CURD，装配 JdbcTemplate，创建测试类，整合JUnit，注入JdbcTemplate

package com.atguigu.spring6;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.test.context.junit.jupiter.SpringJUnitConfig;
    
    @SpringJUnitConfig(locations = "classpath:beans.xml")
    public class JDBCTemplateTest {
    
        @Autowired
        private JdbcTemplate jdbcTemplate;
        
    }

7、查询数据返回对象

public class Emp {
        private Integer id;
        private String name;
        private Integer age;
        private String sex;
    
    
        public Emp() {
        }
    
        public Emp(Integer id, String name, Integer age, String sex) {
            this.id = id;
            this.name = name;
            this.age = age;
            this.sex = sex;
        }
    
        @Override
        public String toString() {
            return "Emp{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", age=" + age +
                    ", sex='" + sex + '\'' +
                    '}';
        }
    
        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;
        }
    
        public Integer getAge() {
            return age;
        }
    
        public void setAge(Integer age) {
            this.age = age;
        }
    
        public String getSex() {
            return sex;
        }
    
        public void setSex(String sex) {
            this.sex = sex;
        }
    
    }

**最后测试功能**

package com.cj.spring6.jdbc;
    
    import org.junit.jupiter.api.Test;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.BeanPropertyRowMapper;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.test.context.junit.jupiter.SpringJUnitConfig;
    
    import java.util.List;
    
    @SpringJUnitConfig(locations = "classpath:beans.xml")
    public class JDBCTemplateTest {
    
        @Autowired
        private JdbcTemplate jdbcTemplate;
    
        @Test
        public void testUpdate() {
            String sql1 = "insert into t_emp values(null,?,?,?)";
            int result1 = jdbcTemplate.update(sql1, "张三", 23, "男");
            System.out.println(result1);
    
            // 修改功能
            String sql2 = "update t_emp set name=? where id=?";
            int result2 = jdbcTemplate.update(sql2, "张三cj", 1);
            System.out.println(result2);
    
            // 删除功能
            // String sql3 = "delete from t_emp where id=?";
            // int result3 = jdbcTemplate.update(sql3, 1);
            // System.out.println(result3);
        }
    
        /**
         * 查询：返回对象
         */
        @Test
        public void testSelectObject() {
            // 写法一
            // String sql1 = "select * from t_emp where id=?";
            // Emp empData = jdbcTemplate.queryForObject(sql1,
            //         (rs, rowNum) -> {
            //             Emp emp = new Emp();
            //             emp.setName(rs.getString("name"));
            //             emp.setAge(rs.getInt("age"));
            //             emp.setSex(rs.getString("sex"));
            //             return emp;
            //         }, 2);
            // System.out.println(empData);
    
            // 写法二
            String sql = "select * from t_emp where id=?";
            Emp emp = jdbcTemplate.queryForObject(sql,
                    new BeanPropertyRowMapper<>(Emp.class), 2);
    
            System.out.println(emp);
    
        }
    
        /**
         * 查询多条数据为一个list集合
         */
        @Test
        public void testSelectList() {
            String sql = "select * from t_emp";
            List<Emp> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Emp.class));
    
            System.out.println(list);
        }
    
        /**
         * 查询单行单列的值
         */
        @Test
        public void selectCount() {
            String sql = "select count(*) from t_emp";
            Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
            System.out.println(count);
        }
    
    
    }