Java框架（五）-- Spring 事务（1）--Spring JDBC

第一章、Spring JDBC

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

一、Spring JDBC优点

Spring JDBC对原始JDBC简单封装，执行效率要比高度封装的ORM框架（如MyBatis）要高，更适合较大应用。
Spring JDBC是介于ORM框架和原生JDBC之间折中的选择。

二、Spring JDBC的使用步骤

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

三、Jdbc Template实现单条数据查询

打开IDEA创建新的maven工程，在pom.xml中引入依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.ql.spring</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <repositories>
        <repository>
            <id>aliyun</id>
            <name>aliyun</name>
            <url>https://maven.aliyun.com/repository/public</url>
        </repository>
    </repositories>
    <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>8.0.29</version>
        </dependency>
    </dependencies>
</project>

在com.ql.spring.jdbc.dao包下创建EmployeeDao类

package com.ql.spring.jdbc.dao;

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

public class EmployeeDao {
    private JdbcTemplate jdbcTemplate;

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

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

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

在resources目录下创建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 https://www.springframework.org/schema/context/spring-context.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/ql-spring-jdbc?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="root"/>
    </bean>
    <!--JdbcTemplate提供数据CRUD的API-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <bean id="employeeDao" class="com.ql.spring.jdbc.dao.EmployeeDao">
        <!--为Dao注入JdbcTemplate对象-->
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>
</beans>

最后创建入口类，运行

package com.ql.spring.jdbc;

import com.ql.spring.jdbc.dao.EmployeeDao;
import com.ql.spring.jdbc.entity.Employee;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

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);
    }
}
//Employee{eno=3308, ename='张三', salary=6000.0, dname='研发部', hiredate=2011-05-08 00:00:00.0}

四、Jdbc Template实现集合数据查询

为了测试方便，pom.xml中引入junit和spring-test

<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>

在EmployeeDao新增几条查询方法

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

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

在test/java目录下创建JdbcTemplateTestor测试类，并运行

import com.ql.spring.jdbc.dao.EmployeeDao;
import com.ql.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.List;
import java.util.Map;

@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(){
        List<Employee> employeeList = employeeDao.findByDname("研发部");
        System.out.println(employeeList);
    }
    @Test
    public void testFindMapByDname(){
        List<Map<String, Object>> maps = employeeDao.findMapByDname("研发部");
        System.out.println(maps);
    }
}
/**
[{empno=3308, s=6000}, {empno=3420, s=8700}]
Employee{eno=3308, ename='张三', salary=6000.0, dname='研发部', hiredate=2011-05-08 00:00:00.0}
[Employee{eno=3308, ename='张三', salary=6000.0, dname='研发部', hiredate=2011-05-08 00:00:00.0}, Employee{eno=3420, ename='李四', salary=8700.0, dname='研发部', hiredate=2006-11-11 00:00:00.0}]
**/

五、Jdbc Template实现写入数据操作

在EmployeeDao新增写入操作方法

    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(int eno){
        String sql = "delete from employee where eno = ?";
        return jdbcTemplate.update(sql, new Object[]{eno});
    }

在测试类JdbcTemplateTestor中编写测试方法运行

    @Test
    public void testInsert(){
        Employee employee = new Employee();
        employee.setEno(8888);
        employee.setEname("赵六");
        employee.setSalary(6666f);
        employee.setDname("研发部");
        employee.setHiredate(new Date());
        employeeDao.insert(employee);
    }
    //数据库中新增一条数据
    
    @Test
    public void testUpdate(){
        Employee employee = employeeDao.findById(8888);
        employee.setSalary(employee.getSalary()+3000);
        int count = employeeDao.update(employee);
        System.out.println("本次更新"+count+"条数据");
    }
    //本次更新1条数据
    
    @Test
    public void testDelete(){
        int count = employeeDao.delete(8888);
        System.out.println("本次删除"+count + "条数据");
    }
    //本次删除1条数据