JPA关联&MyBatis

1. JPA 多表查询

1.1 数据库表及关系

假设我们有两个 User 和 Role 实体类，一个用户可以拥有多个角色，角色也可以属于多个用户，因此这是一个多对多的关系

1.2 多表联接查询

使用JPA，我们可以通过 @Query 注解执行多表联接查询，比如我想获取某个用户的所有角色

public interface UserRepository extends JpaRepository<User, Long> {
    @Query("SELECT u FROM User u JOIN u.roles r WHERE u.username = :username")
    User findByUsernameWithRoles(@Param("username") String username);
}

这个查询语句将返回一个 User 对象，包含了与其关联的 Role 实体

1.3 关联映射

在 User 类中，我们使用 @ManyToMany 注解来定义用户和角色之间的多对多关系

package com.ktjiaoyu.crm.entity;

import lombok.Data;

import javax.persistence.*;
import java.io.Serializable;


@Entity
@Table(name = "sys_user")
public class User implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "usr_id")
    private Long usrId;
    @Column(name = "usr_name")
    private String usrName;
    @Column(name = "usr_password")
    private String usrPassword;
    @Column(name = "usr_role_id")
    private Long usrRoleId;

//    @ManyToOne(targetEntity = Role.class)
//    @JoinColumn(name = "usr_role_id")
//    private Role role;

    @Column(name = "usr_flag")
    private Integer usrFlag;

    public User() {
    }

//    public User(String usrName, String usrPassword, Role role, Integer usrFlag) {
//        this.usrName = usrName;
//        this.usrPassword = usrPassword;
//        this.role = role;
//        this.usrFlag = usrFlag;
//    }


    public User(String usrName, String usrPassword, Long usrRoleId, Integer usrFlag) {
        this.usrName = usrName;
        this.usrPassword = usrPassword;
        this.usrRoleId = usrRoleId;
        this.usrFlag = usrFlag;
    }

    public Long getUsrId() {
        return usrId;
    }

    public void setUsrId(Long usrId) {
        this.usrId = usrId;
    }

    public String getUsrName() {
        return usrName;
    }

    public void setUsrName(String usrName) {
        this.usrName = usrName;
    }

    public String getUsrPassword() {
        return usrPassword;
    }

    public void setUsrPassword(String usrPassword) {
        this.usrPassword = usrPassword;
    }

    public Long getUsrRoleId() {
        return usrRoleId;
    }

    public void setUsrRoleId(Long usrRoleId) {
        this.usrRoleId = usrRoleId;
    }

    //    public Role getRole() {
//        return role;
//    }
//
//    public void setRole(Role role) {
//        this.role = role;
//    }

    public Integer getUsrFlag() {
        return usrFlag;
    }

    public void setUsrFlag(Integer usrFlag) {
        this.usrFlag = usrFlag;
    }
}

在 Role 类中，我们同样需要定义对应的关系：

package com.ktjiaoyu.crm.entity;

import lombok.Data;

import javax.persistence.*;
import java.io.Serializable;
import java.util.HashSet;
import java.util.Set;


@Entity
@Table(name = "sys_role")
public class Role  implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "role_id")
    private Long roleId;
    @Column(name = "role_name")
    private String roleName;
    @Column(name = "role_desc")
    private String roleDesc;
    @Column(name = "role_flag")
    private Integer roleFlag;
//    @OneToMany(targetEntity = User.class,fetch = FetchType.LAZY,cascade = CascadeType.ALL,mappedBy = "role")
//    private Set<User> users = new HashSet<User>();

    public Role() {
    }

    public Role(String roleName, String roleDesc, Integer roleFlag) {
        this.roleName = roleName;
        this.roleDesc = roleDesc;
        this.roleFlag = roleFlag;
    }

    public Long getRoleId() {
        return roleId;
    }

    public void setRoleId(Long roleId) {
        this.roleId = roleId;
    }

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    public String getRoleDesc() {
        return roleDesc;
    }

    public void setRoleDesc(String roleDesc) {
        this.roleDesc = roleDesc;
    }

    public Integer getRoleFlag() {
        return roleFlag;
    }

    public void setRoleFlag(Integer roleFlag) {
        this.roleFlag = roleFlag;
    }

//    public Set<User> getUsers() {
//        return users;
//    }
//
//    public void setUsers(Set<User> users) {
//        this.users = users;
//    }
}

注解属性说明：

• @ManyToMany: 定义多对多关系。
• @JoinTable: 定义关联表，name 指定表名，joinColumns 和 inverseJoinColumns 指定关联的外键列。
• @JoinColumn: 定义表中的外键列。

2. Spring Boot 集成 MyBatis

2.1 回顾 MyBatis

MyBatis 是一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。在 Spring Boot 项目中，集成 MyBatis 通常是为了利用其灵活的 SQL 操作能力

2.2 SpringBoot 集成 MyBatis

首先，我们需要在 pom.xml 中添加 MyBatis 依赖：

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.2.0</version>
</dependency>

接着，创建 Mapper 接口，例如 UserMapper 来进行数据库操作：

package com.ktjiaoyu.crm.mapper;

import com.ktjiaoyu.crm.entity.User;
import org.apache.ibatis.annotations.*;

import java.util.List;


public interface UserMapper {
    @Insert("insert into sys_user(usr_name,usr_password,usr_role_id,usr_flag) values (#{usrName},#{usrPassword},#{usrRoleId},#{usrFlag})")
    void insert(User user);
    @Delete("delete from sys_user where usr_id=#{id}")
    void delete(Long id);
    @Update("<script>"+"update sys_user"+"<set>"
            +"<if test=\"usrName !=null\">usr_name=#{usrName},</if>"
            +"<if test=\"usrPassword !=null\">usr_password=#{usrPassword},</if>"
            +"<if test=\"usrRoleId !=null\">usr_role_id=#{usrRoleId},</if>"
            +"<if test=\"usrFlag !=null\">usr_flag=#{usrFlag},</if>"+
            "</set>" +
            "where usr_id =#{usrId}"+"</script>")
    void update(User user);
    @Select("select usr_id,usr_name,usr_password,usr_role_id,usr_flag from sys_user where usr_id=#{id}")
    @Results({
            @Result(column = "usr_id",property = "usrId"),
            @Result(column = "usr_name",property = "usrName"),
            @Result(column = "usr_password",property = "usrPassword"),
            @Result(column = "usr_role_id",property = "usrRoleId"),
            @Result(column = "usr_flag",property = "usrFlag")

    })
    User get(Long id);
    @Select("select usr_id,usr_name,usr_password,usr_role_id,usr_flag from sys_user")
    @Results({
            @Result(column = "usr_id",property = "usrId"),
            @Result(column = "usr_name",property = "usrName"),
            @Result(column = "usr_password",property = "usrPassword"),
            @Result(column = "usr_role_id",property = "usrRoleId"),
            @Result(column = "usr_flag",property = "usrFlag")

    })
    List<User> findAll();
}

在 Spring Boot 应用中，我们可以通过自动注入 UserMapper 来进行用户的想要的CRUD操作：

package com.ktjiaoyu.crm;

import com.ktjiaoyu.crm.entity.Role;
import com.ktjiaoyu.crm.entity.User;
import com.ktjiaoyu.crm.repository.RoleRepository;
import com.ktjiaoyu.crm.repository.UserRepository;
import com.ktjiaoyu.crm.vo.UserInfo;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;


@SpringBootTest
class Ch03ApplicationTests {
    @Resource
    private UserRepository userRepository;
    @Resource
    private RoleRepository roleRepository;

    @Test
    void contextLoads() {
    }

    @Test
    public void testGetUserInfo(){
//        UserInfo userInfo = userRepository.getUserInfo(2L);
//        System.out.println("usrName："+userInfo.getUsrName());
//        System.out.println("roleName："+userInfo.getRoleName());
    }

    @Test
    public void testUserGet(){
//        User user = userRepository.findById(2L).get();
//        System.out.println("usrName："+user.getUsrName());
//        System.out.println("roleName："+user.getRole().getRoleName());
    }

    @Test
    public void testRoleGet(){
//        Role role = roleRepository.findById(1L).get();
//        System.out.println("roleName："+role.getRoleName());
//        System.out.println("users.size："+role.getUsers().size());
    }

    @Test
    public void testAdd(){
//        Role role = new Role("测试角色","演示级联新增角色和用户",1);
//        User user1 = new User("测试用户1","123456",role,1);
//        User user2 = new User("测试用|户2","123456",role,1);
//
//        //将User添加到Role的users集合中，进行级联新增
//        role.getUsers().add(user1);
//        role.getUsers().add(user2);
//        roleRepository.save(role);
    }

    @Test
    public void testDelete(){
        //先使用getOne方法获取或到Role的引用，然后调用delete方法进行删除
        Role role = roleRepository.getOne(10L);
        roleRepository.delete(role);
    }

}