Spring Boot的数据访问

蔚落 2022-05-04 08:57 175阅读 0赞

## **前言：spring boot对关系型数据库访问一般采用以下三种方式方式访问：mybatis的xml、mybatis的注解版、spring data jpa。** ##

**环境准备：**IntelliJ使用spring initializr创建项目，并添加相应的依赖：mybatis、jpa、mysql

![70][]

**项目结构：**

![70 1][]

entity、repository目录：jpa数据访问

pojo、mapper(包括resources下)目录：mybatis数据访问

test目录：测试类

application.properties和application.yml文件：均可作为spring boot的配置文件，两者作用一样

**数据库结构：**

员工表：employee（从表）

部门表：department（主表）

员工表和部门表存在一对多的关系（这里演示的是双向关系，实际开发要根据需求确定单向还是双向关系）

![70 2][]

![70 3][]![70 4][]

### **1、mybatis的xml方式** ###

以application.yml为例：配置数据源和mybatis的sql映射文件

config-location：mybatis的配置文件位置

mapper-locations：mybatis的映射文件，\*.xml为该目录下所有的xml的文件

spring:
      datasource:
        username: root
        password: 12345
        url: jdbc:mysql://localhost:3306/jpa_mybatis
        driver-class-name: com.mysql.jdbc.Driver
    
    mybatis:
      config-location: classpath:mybatis/mybatis-config.xml
      mapper-locations: classpath:mybatis/mapper/*.xml

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE configuration
            PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-config.dtd">
    
    <configuration>
    
    </configuration>

department表的pojo类

public class Department {
        private Integer id;
        private String departmentName;
        //关联查询，一个部门有多个员工
        private List<Employee> employees;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getDepartmentName() {
            return departmentName;
        }
    
        public void setDepartmentName(String departmentName) {
            this.departmentName = departmentName;
        }
    
    
        public List<Employee> getEmployees() {
            return employees;
        }
    
        public void setEmployees(List<Employee> employees) {
            this.employees = employees;
        }
    
        @Override
        public String toString() {
            return "Department{" +
                    "id=" + id +
                    ", departmentName='" + departmentName + '\'' +
                    ", employees=" + employees +
                    '}';
        }
    }

employee表的pojo

public class Employee {
        private Integer id;
        private String name;
        private String phone;
        private String email;
        //department表，外键
        private Integer dId;
        //关联查询，一个员工属于一个部门
        private Department department;
    
        public Integer getdId() {
            return dId;
        }
    
        public void setdId(Integer dId) {
            this.dId = dId;
        }
    
        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 String getPhone() {
            return phone;
        }
    
        public void setPhone(String phone) {
            this.phone = phone;
        }
    
        public String getEmail() {
            return email;
        }
    
        public void setEmail(String email) {
            this.email = email;
        }
    
        public Department getDepartment() {
            return department;
        }
    
        public void setDepartment(Department department) {
            this.department = department;
        }
    
        @Override
        public String toString() {
            return "Employee{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", phone='" + phone + '\'' +
                    ", email='" + email + '\'' +
                    ", dId=" + dId +
                    ", department=" + department +
                    '}';
        }
    }

DepartmentMapper接口

public interface DepartmentMapper {
        //根据id查询某部门
        public Department findDepById(Integer id);
        //插入
        public int insertDep(Department department);
        //更新
        public int updateDep(Department department);
        //删除
        public int deleteDep(Integer id);
        //根据某部门名称查询部门的所有的员工,关联查询
        public Department findAllEmployeesByDepName(String departmentName);
    
    }

DepartmentMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="cn.zdxh.mapper.DepartmentMapper">
        <select id="findDepById" resultType="cn.zdxh.pojo.Department" parameterType="integer">
            SELECT * FROM department WHERE id=#{id}
        </select>
        <insert id="insertDep" parameterType="cn.zdxh.pojo.Department" useGeneratedKeys="true">
            INSERT INTO department(departmentName) VALUES (#{departmentName})
        </insert>
        <update id="updateDep" parameterType="cn.zdxh.pojo.Department">
            UPDATE department SET departmentName=#{departmentName} where id=#{id}
        </update>
        <delete id="deleteDep" parameterType="integer">
            DELETE FROM department WHERE id=#{id}
        </delete>
    
    
        <resultMap id="findAllEmp" type="cn.zdxh.pojo.Department">
            <id property="id" column="id"/>
            <result property="departmentName" column="departmentName"/>
            <collection property="employees" ofType="cn.zdxh.pojo.Employee">
                <id property="id" column="eId"/>
                <result property="name" column="name"/>
                <result property="phone" column="phone"/>
                <result property="email" column="email"/>
                <result property="dId" column="dId"/>
            </collection>
        </resultMap>
        <select id="findAllEmployeesByDepName" resultMap="findAllEmp" parameterType="string">
            SELECT
              department.id,
              department.departmentName,
              employee.id eId,
              employee.name,
              employee.phone,
              employee.email,
              employee.dId
            FROM department
            LEFT JOIN employee
            ON department.id=employee.dId
            WHERE department.departmentName=#{departmentName}
        </select>
    </mapper>

以上的内容几乎和ssm的开发几乎一致，唯一的不同的就是不用配置那么多的配置文件，只需要简单配置一下数据源和映射文件的位置即可。使用spring boot开发相当快捷快速。这也是推荐的数据访问方式。

### **2、mybatis的注解方式** ###

EmployeeMapper接口

public interface EmployeeMapper {
        //根据员工id查询员工信息
        @Select("select * from employee where id=#{id}")
        public Employee findEmpById(Integer id);
    
        //插入
        @Insert("insert into employee(name,phone,email,dId) values(#{name},#{phone},#{email},#{dId})")
        public int insertEmp(Employee employee);
    
    
        //更改
        @Update("update employee set name=#{name},phone=#{phone},email=#{email},dId=#{dId} where id=#{id}")
        public int updateEmp(Employee employee);
    
        //删除
        @Delete("delete from employee where id=#{id}")
        public  int deleteEmp(Integer id);
    
        //根据某员工姓名查询员工信息以及部门信息
        @Select("select * from employee left join department on employee.dId=department.id where employee.name=#{name}")
        @Results({@Result(property = "id",column = "id"),
                @Result(property = "name",column = "name"),
                @Result(property = "phone",column = "phone"),
                @Result(property = "email",column = "email"),
                @Result(property = "dId",column = "dId"),
                @Result(property = "department",column = "dId",
                one = @One(select = "cn.zdxh.mapper.DepartmentMapper.findDepById"))
        })
        public Employee findOneEmployeeAndDepartmentByName(String name);
    }

@Select:查询

@Insert:插入

@Update:更新

@Delete:删除

根据删除、插入、更新还是删除，并且在里面加上相应的sql语句，就可完成简单的增删改查

如果涉及联表查询，@Results就相当于xml中的ResultMap标签，里面的@Result就相当于id和result标签

one:多对一或一对一用到的属性

one=@One(select="根据department的id查询department的方法全路径")：先通过查出的外键dId，再根据这个外键去二次查询

many:一对多，同理

mybatis两种方式的测试类

MapperTest类

@RunWith(SpringRunner.class)
    @SpringBootTest
    public class MapperTest {
        @Autowired
        EmployeeMapper employeeMapper;
    
        @Autowired
        DepartmentMapper departmentMapper;
    
    
        /**
         *  配置文件的测试类
         */
    
        //根据id查询员工
        @Test
        public void testEmplyeeFindById(){
            Employee employee=employeeMapper.findEmpById(3);
            System.out.println(employee);
        }
        
       //添加
        @Test
        public void testEmployeeAdd(){
            Employee employee=new Employee();
            employee.setName("ning");
            employee.setEmail("664650000@qq.com");
            employee.setPhone("1318940000");
            employee.setdId(1);
            employeeMapper.insertEmp(employee);
        }
    
        //更新
        @Test
        public void testEmplyeeUpdate(){
            Employee employee=new Employee();
            employee.setId(3);
            employee.setName("xiao");
            employee.setEmail("664650000@qq.com");
            employee.setPhone("1318940000");
            employee.setdId(1);
            employeeMapper.updateEmp(employee);
        }
    
        //删除
        @Test
        public void testEmplyeeDel(){
            employeeMapper.deleteEmp(4);
        }
    
        //联表查询，一对多
        @Test
        public void testFindAllEmployeesByDepName(){
            Department department = departmentMapper.findAllEmployeesByDepName("AA");
            System.out.println(department);
        }
    
    
    
        /**
         *  注解方式的测试方法
         */
    
        //根据id查询部门
        @Test
        public void testDepartmentFindById(){
            Department department=departmentMapper.findDepById(1);
            System.out.println(department);
        }
    
        //添加
        @Test
        public void testDepartmentAdd(){
           Department department=new Department();
           department.setDepartmentName("BB");
            departmentMapper.insertDep(department);
        }
    
        //更新
        @Test
        public void testDepartmentUpdate(){
            Department department=new Department();
            department.setId(2);
            department.setDepartmentName("BB-BB");
            departmentMapper.updateDep(department);
        }
    
        //删除
        @Test
        public void testDepartmentDel(){
           departmentMapper.deleteDep(3);
        }
        
        //联表查询，多对一
        @Test
        public void testFindOneEmployeeAndDepartmentByName(){
            Employee employee = employeeMapper.findOneEmployeeAndDepartmentByName("xiao");
            System.out.println(employee);
        }
        
    }

### **3、spring data jpa方式** ###

jpa面向接口，hibernate面向实现。jpa的底层就是hibernate

有hibernate基础的同学对jpa相信也会感觉很熟悉

首先在application.yml中添加如下设置

spring:
       jpa:
          hibernate:
             ddl-auto: update  #自动建表
          show-sql: true  #显示sql语句

employee的entity类

@Entity
    @Table(name = "employee")//不写name的话就默认类名小写
    public class Employee {
        @Id//标志这是一个主键
        @GeneratedValue(strategy = GenerationType.IDENTITY)//标明这是自增主键
        private Integer id;
        @Column(name = "name")//不写也默认
        private String name;
        @Column(name = "phone")
        private String phone;
        @Column(name = "email")
        private String email;
        @Column(name = "dId")
        private Integer dId;
    
        private Department department;
    
        public Department getDepartment() {
            return department;
        }
    
        public void setDepartment(Department department) {
            this.department = department;
        }
    
        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 String getPhone() {
            return phone;
        }
    
        public void setPhone(String phone) {
            this.phone = phone;
        }
    
        public String getEmail() {
            return email;
        }
    
        public void setEmail(String email) {
            this.email = email;
        }
    
        public Integer getdId() {
            return dId;
        }
    
        public void setdId(Integer dId) {
            this.dId = dId;
        }
    
        @Override
        public String toString() {
            return "Employee{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", phone='" + phone + '\'' +
                    ", email='" + email + '\'' +
                    ", dId=" + dId +
                    '}';
        }
    }

department的entity类

@Entity
    @Table
    public class Department {
    
        @Id//设为自动增长
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Integer id;
        @Column
        private String departmentName;
    
        private Set<Employee> employees;
    
        public Set<Employee> getEmployees() {
            return employees;
        }
    
        public void setEmployees(Set<Employee> employees) {
            this.employees = employees;
        }
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getDepartmentName() {
            return departmentName;
        }
    
        public void setDepartmentName(String departmentName) {
            this.departmentName = departmentName;
        }
    
        @Override
        public String toString() {
            return "Department{" +
                    "id=" + id +
                    ", departmentName='" + departmentName + '\'' +
                    '}';
        }
    }

类上：

@Entity:标志该类为entity类

@Table(name="")：指定表名，不写则默认类名小写

属性上：

@Id：该属性为一个主键

@GeneratedValue(strategy=GenerationTpye.IDENTITY):设定主键为自动增长

@Column(name=""):该属性为普通属性

上面的这些映射信息和hibernate的映射信息几乎一样

注意:这里的jpa没有做联表查询操作，需要深入了解的同学可以参考@OneToMany、@ManyToOne、@OneToOne等注解，和hibernate配置的many-to-one等标签相似度也很高。

DepartmentRepository接口

/**
     * 只需要继承JpaRepository即可进行简单的crud
     * 参数一：实体类类型
     * 参数二：实体类的id类型
     */
    public interface DepartmentRepository extends JpaRepository<Department,Integer> {
    }

EmployeeRepository接口

/**
     * 只需要继承JpaRepository即可进行简单的crud，如果需要进行复杂的查询则需要另继承JpaSpecificationExecutor
     * JpaRepository 参数一：实体类类型  参数二：实体类的id类型
     * JpaSpecificationExecutor 参数：实体类类型
     */
    public interface EmployeeRepository extends JpaRepository<Employee,Integer>,JpaSpecificationExecutor<Employee> {
    }

继承JpaRepository接口就可以进行简单的增删改查和分页功能

泛型参数一：实体类类型

泛型参数二：实体类中的id的类型

再继承JpaSpecificationExecutor接口，一些复杂的条件类型查询就可以实现

泛型参数：实体类类型

DepartmentRepositoryTest类

@RunWith(SpringRunner.class)
    @SpringBootTest
    public class DepartmentRepositoryTest {
        @Autowired
        DepartmentRepository departmentRepository;
    
        @Autowired
        DataSource dataSource;
        @Test
        public void testDepartmentFindById(){
            Department department=new Department();
            department.setId(1);
            Example example= Example.of(department);
            Optional<Employee> one = departmentRepository.findOne(example);
            if (one.isPresent()){
                System.out.println(one.get());
            } else {
                System.out.println("查询的值不存在！");
            }
        }
    }

注意：这里是以spring boot2.00以上版本作为测试实例，Optional类是jdk1.8的新特性，专门解决空指针异常的，给里面传一个Example类作为参数。和spring boot1.00版本的findOne方法会有所不同，直接传入id就行。

EmployeeRepositoryTest类

@RunWith(SpringRunner.class)
    @SpringBootTest
    public class EmployeeRepositoryTest {
        @Autowired
        EmployeeRepository employeeRepository;
    
        @Test
        public void testEmployeeAdd(){
            Employee employee=new Employee();
            employee.setName("jian");
            employee.setEmail("666666666@qq.com");
            employee.setPhone("13189999999");
            //employee.setdId(1);
            employeeRepository.save(employee);
        }
    
        @Test
        public void testEmplyeeUpdate(){
            Employee employee=new Employee();
            employee.setId(5);
            employee.setName("xiao");
            employee.setEmail("666666666@qq.com");
            employee.setPhone("13189999999");
            //employee.setdId(1);
            employeeRepository.save(employee);
        }
    
        @Test
        public void testEmplyeeDel(){
           employeeRepository.deleteById(5);
        }
    
        @Test
        public void testEmplyeeFindById(){
            Employee employee=new Employee();
            employee.setId(5);
            Example example= Example.of(employee);
            Optional<Employee> one = employeeRepository.findOne(example);
            if (one.isPresent()){
                System.out.println(one.get());
            } else {
                System.out.println("查询的值不存在！");
            }
        }
    
        @Test
        public void testEmployeeComplexQuery(){
            //构造查询条件
            Specification<Employee> specification = new Specification<Employee>() {
                @Nullable
                @Override
                public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                    Path<Object> pathName = root.get("name");
                    Path<Object> pathPhone = root.get("phone");
                    Predicate predicatePhone = criteriaBuilder.equal(pathPhone, "1318999999");
                    Predicate predicateName = criteriaBuilder.equal(pathName, "ning");
                    return criteriaBuilder.and(predicateName,predicatePhone);
                }
            };
            Optional<Employee> optional = employeeRepository.findOne(specification);
            Employee employee = optional.get();
            System.out.println(employee);
        }
    }

其他方法大同小异，只是testEmplyeeComplexQuery方法用来做复杂的条件查询（主要）

Spectification接口传入一个实体类构造查询类

主要实现toPredicate方法

参数Root：从中获得要查询的类型

参数CriteriaQuery：添加查询条件，例如OrderBy等等

参数CriteriaBuilder：构建perdicate,例如and、or等等，作为返回的值

还有两种方法可以拓展查询条件：（了解）

（1）、规范方法名，spring data会根据里面的关键词构造查询条件

（2）、@Query注解直接写查询条件

总结：虽然spring boot给我们提供那么多的访问数据的方式，但是实际开发当中不会全部的用到，要学会有取有舍，用自己最擅长的方式，也可以混合使用，当用到联表查询的时候可以直接用mybatis的xml，方便快捷又便于维护，如果说只是对单表的操作，用jpa的方式也不错，但是jpa的联表查询有一些复杂，对hibernate不熟的同学来说，也是一大挑战。

[70]: /images/20220504/b0e4cd25f9404be688e6e27f3489ab04.png
[70 1]: /images/20220504/ed97c432c3244dc2998b98947b224074.png
[70 2]: /images/20220504/77d32f05dbb6466ab30ed6db7b1dc965.png
[70 3]: https://img-blog.csdn.net/20181022202318919?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODgwMjA2MQ==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[70 4]: /images/20220504/3c840851bcaf4109b4f4821393c013ad.png