JPA关联&mybatis

JAP多表查询

多表查询在Spring Data JPA 中有两种实现方式,第一种是创建结果集的接口来接收多表联接查询后的结果,第二种是利用JPA的关联映射来实现。

• 数据库表及关系
  crm数据库,包含sys_user用户表和sys_role角色表

CREATE TABLE `sys_role`  (
  `role_id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `role_name` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '角色名称',
  `role_desc` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '角色描述',
  `role_flag` int(0) NULL DEFAULT NULL COMMENT '状态',
  PRIMARY KEY (`role_id`) USING BTREE,
  UNIQUE INDEX `role_id`(`role_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 32 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Compact;

CREATE TABLE `sys_user`  (
  `usr_id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `usr_name` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `usr_password` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '密码',
  `usr_role_id` bigint(0) NULL DEFAULT NULL COMMENT '角色编号',
  `usr_flag` int(0) NULL DEFAULT NULL COMMENT '状态',
  PRIMARY KEY (`usr_id`) USING BTREE,
  INDEX `usr_role_id`(`usr_role_id`) USING BTREE,
  CONSTRAINT `sys_user_ibfk_1` FOREIGN KEY (`usr_role_id`) REFERENCES `sys_role` (`role_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 83 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Compact;

存在主外键关系

多表联接查询

首先创建一个 包pojo实体类Role

package com.bdqn.springboot.pojo;

import lombok.Data;

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

@Table(name = "sys_role")
@Entity
@Data
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;
}

然后在Vo包下创建一个接口UserInfo,用于提供getter方法,包含用户数据和角色名称(roleName)

package com.bdqn.springbootmybatis.vo;

public interface UserInfo {
    Long getUsrId();

    String getUsrName();

    String getUsrPassword();

    Long getUsrRoleId();

    Integer getUsrFlag();

    //角色名称
    String getRoleName();
}

• 在运行中Spring会给接口UsrInfo自动生产一个代理类来接收返回的结果,代替了代码中的get**形式来获取
• 在UserRepository中添加查询方法,返回类型是UserInfo中添加查询方法,返回类型设置为UserInfo:

@@Query("select u.usrId as usrId, u.usrName as usrName, u.usrPassword as usrPassword, " +
            "u.usrRoleId as usrRoleId, u.usrFlag as usrFlag, r.roleName as roleName from User u," +
            "Role r where u.usrRoleId=r.roleId and u.usrId=?1")
    UserInfo getUserInfo(Long usrId);

测试验证:

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

关系映射

• 在软件开发中,类鱼类之间的关系就是关联关系,而且是有方向(目的性)的,我们User(用户表)与角色表(Role)之间存在主外键的关联关系,而一个用户只能属于一个角色,一个角色能有多个用户。
• 可能说的有点不懂,通俗的来说如:
• User 和 Role 之间,介绍映射多对一单向关联映射
• User 和 Role 之间,介绍映射一对多单向关联映射

单向多对一关联

通过主外键的映射关系,建立多对一的单向关联关系

首先创建一个User实体

package com.bdqn.springboot.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

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

//@Data
@NoArgsConstructor
@AllArgsConstructor
//@ToString
@Entity
@Table(name = "sys_user")
@NamedQueries(@NamedQuery(name = "User.findUsersByName", query = "select u from User u where u.usrName= ?1"))
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(String usrName, String usrPassword, Long usrRoleId, Integer usrFlag) {
//        this.usrName = usrName;
//        this.usrPassword = usrPassword;
//        this.usrRoleId = usrRoleId;
//        this.usrFlag = usrFlag;
//    }

    public User(String usrName, String usrPassword, Role role, Integer usrFlag) {
        this.usrName = usrName;
        this.usrPassword = usrPassword;
        this.role = role;
        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;
    }

    @Override
    public String toString() {
        return "User{" +
                "usrId=" + usrId +
                ", usrName='" + usrName + '\'' +
                ", usrPassword='" + usrPassword + '\'' +
                ", role.roleName=" + role.getRoleName() +
                ", usrFlag=" + usrFlag +
                '}';
    }


//    @Override
//    public String toString() {
//        return "User{" +
//                "usrId=" + usrId +
//                ", usrName='" + usrName + '\'' +
//                ", usrPassword='" + usrPassword + '\'' +
//                ", usrRoleId=" + usrRoleId +
//                ", usrFlag=" + usrFlag +
//                '}';
//    }
}

• @ManyToOne:注解映射多对一关联关系,targetEntity 属性标识关联实体类型。
• @JoinColumn:注解映射关联的外键字段,则生成一张新表维护两个对象之间的关系。

创建UserRepository

public interface UserRepository extends JpaRepository<User,Long>, JpaSpecificationExecutor {
}

测试验证:

@Test
    public void testGet(){
        //User user = userRepository.getOne(2L);//不是使用该方法
        User user = userRepository.findById(65L).get();
        System.out.println("usrName："+user.getUsrName());
        System.out.println("roleName："+user.getRole().getRoleName());
    }

• 从输出的SQL语句可以发现,执行查询User,关联了Role数据,并将Role相关数据自自动封装在User对象的role属性中。

双向一对多关联

我们通过Role(用户类)类到User(用户类)类的双向一对多关联。

首先修改Role实体类,添加关联的User对象集合。

package com.bdqn.springboot.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

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

@Entity // JPA实体类
@Table(name = "sys_role") // 对应表
//@Data // Lombok注解，自动生成Getter/Setter
@NoArgsConstructor // 生成无参构造方法
@AllArgsConstructor // 生成全参构造方法
//@ToString // 生成toString方法
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;
    // PERSIST联级新增，REMOVE联级删除
    @OneToMany(targetEntity = User.class,fetch = FetchType.EAGER,cascade = CascadeType.PERSIST,mappedBy = "role")
    private Set<User> users = new HashSet<>();

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

    @Override
    public String toString() {
        return "Role{" +
                "roleId=" + roleId +
                ", roleName='" + roleName + '\'' +
                ", roleDesc='" + roleDesc + '\'' +
                ", roleFlag=" + roleFlag +
                ", users.size=" + users.size() +
                "}\t_\n"+ users;
    }
}

• 添加了Setusers属性,使用Set集合可避免重复对象的问题,用于表示一对多关联,在该属性上我们添加了@OneToMany注解,映射一对多关联关系。
• targetEntity:属性表示关联的实体类型
• fetch:属性表示加载,FetchType值有:LAZY、EAGER,LAZY表示懒加载俗称延迟加载,EAGER表示立即加载,马上加载,该@OneToMany注解默认是懒加载,LAZY。
• cascade:表示级联操作,取值有PERSIST、REMOVE、ALL等等属性,PERSIST:表示添加、修改操作 ,REMOVE:表示删除或移除的操作,ALL:表示全部的级联操作,不建议使用!
• mappedBy:表示外键的意思,用于属性来设置对象之间的关系维护方(关系维护通过操作外键完成),如果没有指定该mappedBy属性,则默认对象均由自己维护关系(外键),若@OneToMany注解中指定mappedBy属性,那么属性值为多方对象中关联的一方属性名,并且此时一方实体中不能添加@JoinColumn主键。

创建RoleRepository

package com.bdqn.springboot.repository;

import com.bdqn.springboot.entity.Role;
import org.springframework.data.jpa.repository.JpaRepository;

public interface RoleRepository extends JpaRepository<Role,Long> {
}

测试验证:

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

• 测试级联操作:级联新增

@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);
        role.getUsers().add(user1);
        role.getUsers().add(user2);
        roleRepository.save(role);
        System.out.println("新增成功");
    }

• 测试级联删除:

 @Test
    public void testDelete(){
        Role role = roleRepository.getOne(33L);
        roleRepository.delete(role);
        System.out.println("删除成功");
    }

MyBatis

MyBatis是一款标准的ORM框架,被广泛的应用于各企业开发中,MyBatis最早是Apache的一个开源项目iBatis,2010年这个项目由Apache Software Foundation迁移到了Google Code,并改名为MyBatis,2013年11月又迁移到Github,MyBatis支持普通SQL语句。

优点:

• SQL被统一提取出来,便于统一管理和优化
• SQL和代码解耦,将业务逻辑和数据访问逻辑分离,使系统的设计更清晰、更易维护、更易单元测试
• 提供映射标签,支持对象与数据库的ORM字段关系映射
• 提供对象关系映射标签,支持对象关系组件维护
• 灵活书写动态SQL,支持各种条件来动态生成不同的SQL

缺点:

• 编写SQL语句时工作量很大,尤其是字段多、关联表多时,更是如此
• SQL语句依赖于数据库,导致数据库移植性差