Spring datajpa multi table joint query

It is believed that everyone has used jpa. After jpa inherits the interface of CrudRepository and PagingAndSortingRepository, in simple single table query, jpa can use jpql query quickly, no matter using its own findAll(), saveAll and other methods or using jpa's method naming Specification. However, in the process of complex query, we need to inherit the jpaspecification executor interface to use the Specification for complex query, and also need to define the mapping relationship between tables. Today, we will introduce the multi table query and condition construction of jpa.

1, Jpa configuration and build foundation table

pom

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

To configure

spring:
	jpa:
	    show-sql: true
	    hibernate:
	      # Automatically create, update and verify database table structure
	      ddl-auto: update

Here are three tables: user, role and permission.
A user has a role, and a role can be assigned to multiple users. User to role is many to one. Suppose a role binds 1 permission. Role to permission is one-to-one.
JpaUser

@Data
@Entity
@Table(name = "jpa_user")
@org.hibernate.annotations.Table(appliesTo = "jpa_user", comment = "User table")
public class JpaUser {

    @Id
    @GeneratedValue
    @ApiParam("Primary key ID")
    @Column(name = "id", length = 20)
    private Long id;

    @ApiParam("User name")
    @Column(name = "user_name", columnDefinition = "VARCHAR(255) NOT NULL COMMENT 'User name'")
    private String userName;

    @ApiParam("Nickname?")
    @Column(name = "nick_name", columnDefinition = "VARCHAR(255) NOT NULL COMMENT 'Nickname?'")
    private String nickName;

    @ApiParam("post")
    @Column(name = "position")
    private String position;

    @ApiParam("Age")
    @Column(name = "age")
    private Integer age;

    @Column(name = "longitude", precision = 10, scale = 7)
    private BigDecimal longitude;

    @Column(name = "latitude", precision = 10, scale = 7)
    private BigDecimal latitude;

    /**
     * @ManyToOne User: roles multiple users correspond to one role. When we create a table structure, we should maintain the table relationship on multiple sides. That is to say, @ ManyToOne annotation should be added to the user table and set to lazy loading.
     * @JsonBackReference Exclude this property when generating json
     */
    @ManyToOne
    @JoinColumn(name = "role_id")
    private JpaRole role;
}

JpaRole

@Data
@Entity
@Table(name = "jpa_role")
@org.hibernate.annotations.Table(appliesTo = "jpa_role", comment = "Role table")
public class JpaRole {

    @Id
    @GeneratedValue
    @ApiParam("Primary key ID")
    @Column(name = "id")
    private Long id;

    @ApiParam("Role name")
    @Column(name = "role_name", columnDefinition = "VARCHAR(64) NOT NULL COMMENT 'Role name'")
    private String roleName;

    @JsonBackReference
    @OneToMany(targetEntity = JpaUser.class, mappedBy = "role", cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REFRESH}, fetch = FetchType.LAZY)
    private List<JpaUser> jpaUsers;

    /**
     * One to one relationship is maintained in one of the tables
     */
    @OneToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REFRESH})
    @JoinColumn(name = "permission_id")
    private JpaPermission permission;

}

JpaPermission

@Data
@Entity
@Table(name = "jpa_permission")
@org.hibernate.annotations.Table(appliesTo = "jpa_permission", comment = "Permission table")
public class JpaPermission {

    @Id
    @GeneratedValue
    @ApiParam("Primary key ID")
    @Column(name = "id", length = 20)
    protected Long id;

    @ApiParam("Permission name")
    @Column(name = "permission_name", columnDefinition = "VARCHAR(64) NOT NULL COMMENT 'Permission name'")
    private String permissionName;

}

2, Conditional query builder

Define a condition query dynamic constructor.
JpaUserSpecification

public class JpaUserSpecification implements Specification<JpaUser> {

    private JpaUserQueryVo param;

    public JpaUserSpecification(JpaUserQueryVo jpaUserQueryVo) {
        this.param = jpaUserQueryVo;
    }

    @Override
    public Specification<JpaUser> and(Specification<JpaUser> other) {
        return null;
    }

    @Override
    public Specification<JpaUser> or(Specification<JpaUser> other) {
        return null;
    }

    @Override
    public Predicate toPredicate(Root<JpaUser> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
        List<Predicate> predicates = new ArrayList<Predicate>();
        /**
         * 2 Zhang table
         */
        if (StringUtils.isNotEmpty(param.getRoleName())) {
            Join<JpaRole, JpaUser> join = root.join("role", JoinType.LEFT);
            predicates.add(criteriaBuilder.equal(join.get("roleName"), param.getRoleName()));
        }
        /**
         * Cross table query, 3 tables
         * You need to find out the corresponding user through the permission name, JpaUser.role.permission = param.getPermissionName()
         * jointype.LEFT It mainly refers to the table in which the attribute "role" is located, and here it is in JpaUser. 
         * debug here can find that after the first line is executed, the Model in the join becomes JpaRole. (picture on the back) 
         * Here is to find out the corresponding user through the permission name. So first root.join("role", JoinType.LEFT) gets JpaRole, then join.get("permission") gets jpapapermission, and then matches its property permissionName
         * Here is to get the corresponding attribute until you get the desired attribute. 
         */
        if (StringUtils.isNotEmpty(param.getPermissionName())) {
            Join<JpaPermission, JpaUser> join = root.join("role", JoinType.LEFT);
            predicates.add(criteriaBuilder.equal(join.get("permission").get("permissionName"), param.getPermissionName()));
        }
        /**
         * in query
         */
        if (StringUtils.isNotEmpty(param.getIds())) {
            CriteriaBuilder.In in = criteriaBuilder.in(root.get("id").as(Long.class));
            List<String> ids = Arrays.asList(param.getIds().split(","));
            ids.forEach(s -> in.value(Long.parseLong(s)));
            predicates.add(criteriaBuilder.and(in));
        }
        /**
         * like query
         */
        if (StringUtils.isNotEmpty(param.getUserName())) {
            predicates.add(criteriaBuilder.like(root.get("userName"), "%" + param.getUserName() + "%"));
        }
        /**
         * Full = query
         */
        if (StringUtils.isNotEmpty(param.getNickName())) {
            predicates.add(criteriaBuilder.equal(root.get("nickName"), param.getNickName()));
        }
        /**
         * Greater than less than and so on
         */
        if (param.getAge() != null) {
            predicates.add(criteriaBuilder.le(root.get("age"), param.getAge()));
            predicates.add(criteriaBuilder.gt(root.get("age"), param.getAge() - 10));
        }
        return predicates.isEmpty() ? criteriaBuilder.conjunction() : criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
    }
}

JpaUserQueryVo is the Entity that receives various query parameters. The code will not be posted here.

3, Call query builder

Here, the controller layer code is omitted, only the service layer code.

@Override
public Page<JpaUser> tables(JpaUserQueryVo userQueryVo) {
    PageRequest pageRequest = PageRequest.of(0, 10);
    Specification<JpaUser> specification = new JpaUserSpecification(userQueryVo);
    return jpaJpaUserRepository.findAll(specification, pageRequest);
}

That's it. JpaUserSpecification will automatically build queries based on the parameters in JpaUserQueryVo.
Take a look at the effect.

  • My official account: Coding is a good judge.
  • A Java programmer without a blessing. Occasionally send the dry goods that you have learned recently. Learning route, experience, technology sharing. Exchange and discussion of technical issues.
Published 117 original articles, praised 119, visited 450000+
Private letter follow

Tags: Hibernate Attribute Spring SQL

Posted on Thu, 20 Feb 2020 02:20:04 -0500 by john8675309