Spring Boot的数据访问

SpringBoot数据库访问方式

最新推荐文章于 2022-11-04 08:20:25 发布

原创最新推荐文章于 2022-11-04 08:20:25 发布 · 1k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#spring boot #spring data jpa #mybatis #mybatis注 #hibernate

spring boot 专栏收录该内容

3 篇文章

订阅专栏

本文详细介绍了SpringBoot中三种常见的数据库访问方式：MyBatis XML、MyBatis注解和Spring Data JPA，对比了各自的优缺点及适用场景。

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

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

项目结构：

entity、repository目录：jpa数据访问

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

test目录：测试类

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

数据库结构：

员工表：employee（从表）

部门表：department（主表）

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

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">
<!--mybatis的配置文件-->
<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不熟的同学来说，也是一大挑战。