java使用JPA进行数据查询和join关联查询

程序员娟娟

已于 2023-11-29 16:25:08 修改

阅读量2k

点赞数 36

分类专栏：数据访问 jpa 文章标签： java 后端

于 2023-11-29 16:24:02 首次发布

本文链接：https://blog.csdn.net/djj1015591892/article/details/134690990

版权

jpa 同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

数据访问

1 篇文章 0 订阅

订阅专栏

一、问题描述

我有【好友entity】和【人entity】。

【好友entity】关联了【人entity】，且他们是1:1的关系。

我想通过查询【好友entity】，

就能直接看见【人entity】的数据，

还能通过【人entity】中的内容进行筛选。

我该如何做呢？

请看下面的代码实现。

二、代码实现

【逻辑实现说明】

在【好友entity】中进行单向1:1关联关系映射，这样就能关联查看【人entity】。

在查看时，通过join的方式，就能通过name和phone筛选【人entity】中的数据了。

2.1 entity

2.1.1【好友entity】

（1）entity接口

public interface CircleFriendEntity2 {
    Long getId();
    void setId(Long id);

    //

    Long getCircleId();
    void setCircleId(Long circleId);

    String getCircleRemark();
    void setCircleRemark(String circleRemark);

    //

    String getNameNick();
    void setNameNick(String nameNick);

    Long getAdminUserId();
    void setAdminUserId(Long adminUserId);

    UserSocialCapitalEntity getSocialCapitalEntity();

    String getAdminSocialCapitalIdUnique();
    void setAdminSocialCapitalIdUnique(String adminSocialCapitalIdUnique);

    String getRecommendReason();
    void setRecommendReason(String recommendReason);

    //

    CircleIdentity getIdentity();
    void setIdentity(CircleIdentity identity);

    String getAdminUnique();
    void setAdminUnique(String adminUnique);

    LocalDateTime getFirstManagerTime();
    void setFirstManagerTime(LocalDateTime firstManagerTime);

    LocalDateTime getLatestManagerTime();
    void setLatestManagerTime(LocalDateTime latestManagerTime);

    //

    CircleJoinMethod getJoinMethod();
    void setJoinMethod(CircleJoinMethod joinMethod);

    Long getInviteCircleFriendId();
    void setInviteCircleFriendId(Long inviteCircleFriendId);

    Long getInviteUserId();
    void setInviteUserId(Long inviteUserId);
    String getInviterName();
    void setInviterName(String inviterName);

    //

    Boolean getTop();
    void setTop(Boolean top);

    LocalDateTime getTopTime();
    void setTopTime(LocalDateTime topTime);

    //

    LocalDateTime getCreateTime();
    void setCreateTime(LocalDateTime createTime);

    LocalDateTime getUpdateTime();
    void setUpdateTime(LocalDateTime updateTime);

    Boolean getDeleted();
    void setDeleted(Boolean deleted);

}

（2）entity实现类（关键点）

字段配置

@OneToOne
@JoinColumn(name = "adminSocialCapitalId")
private UserSocialCapitalEntityImpl socialCapitalEntity;

getter setter方法

@Override
public UserSocialCapitalEntity getSocialCapitalEntity() {
return socialCapitalEntity;
}

public void setSocialCapitalEntity(UserSocialCapitalEntityImpl socialCapitalEntity) {
this.socialCapitalEntity = socialCapitalEntity;
}

@Entity
@Table(name = "circle_friend_entity_two")
@Cacheable
public class CircleFriendEntityImpl2
        implements CircleFriendEntity2, Serializable {

    private static final long serialVersionUID = -7889618615101349957L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id; 

    private Long circleId; 

    
    private String circleRemark; 

    private String nameNick; 

    private Long adminUserId; 

    @OneToOne
    @JoinColumn(name = "adminSocialCapitalId")
    private UserSocialCapitalEntityImpl socialCapitalEntity; 

    private String adminSocialCapitalIdUnique; 


    private String recommendReason; 


    @Enumerated(EnumType.STRING)
    private CircleIdentity identity; 

    
    private String adminUnique; 

    
    private LocalDateTime firstManagerTime; 

    private LocalDateTime latestManagerTime; 


    @Enumerated(EnumType.STRING)
    private CircleJoinMethod joinMethod; 

    private Long inviteCircleFriendId; 

    private Long inviteUserId; 
    private String inviterName; 

    private Boolean top; 

    private LocalDateTime topTime; 


    private LocalDateTime createTime; 

    private LocalDateTime updateTime; 
    
    private Boolean deleted; 

    
    @Override
    public UserSocialCapitalEntity getSocialCapitalEntity() {
        return socialCapitalEntity;
    }

    public void setSocialCapitalEntity(UserSocialCapitalEntityImpl socialCapitalEntity) {
        this.socialCapitalEntity = socialCapitalEntity;
    }

    (……其他常规getter setter方法……)
}

2.1.2【人entity】

（1）entity接口

public interface UserSocialCapitalEntity {
    Long getId();
    void setId(Long id);

    //

    String getName();
    void setName(String name);

    String getPhone();
    void setPhone(String phone);

}

（2）entity实现类

@Table(name = "user_social_capital_entity")
@Entity
@Cacheable
public class UserSocialCapitalEntityImpl
        implements UserSocialCapitalEntity, Serializable {

    private static final long serialVersionUID = -5517289816387650441L;
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name; 

    private String phone; 

    
    (……常规getter setter方法……)
}

2.2 jpa查询

（1）接口方法

    List<CircleFriendEntity2> listInCircle(Long circleId,
                                           CircleIdentity identity,
                                           String name,
                                           String phone,
                                           int start, int maxResult);

（2）接口实现方法（关键点）

内连接

Join<CircleFriendEntityImpl, UserSocialCapitalEntityImpl> socialCapitalEntity
= root.join("socialCapitalEntity", JoinType.INNER);

if (RenweyUtil.isNotEmpty(name)) {
predicate = criteriaBuilder.and(
predicate,
criteriaBuilder.like(
socialCapitalEntity.get("name"),
RenweyUtil.addMysqlFullTextSearch(name)));
}

if (RenweyUtil.isNotEmpty(phone)) {
predicate = criteriaBuilder.and(
predicate,
criteriaBuilder.equal(socialCapitalEntity.get("phone"), phone));
}

import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Join;
import javax.persistence.criteria.JoinType;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;


    @Override
    public List<CircleFriendEntity2> listInCircle(Long circleId,
                                                  CircleIdentity identity,
                                                  String name,
                                                  String phone,
                                                  int start, int maxResult) {

        CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
        CriteriaQuery<CircleFriendEntityImpl2> criteriaQuery = criteriaBuilder.createQuery(CircleFriendEntityImpl2.class);
        Root<CircleFriendEntityImpl2> root = criteriaQuery.from(CircleFriendEntityImpl2.class);

        Predicate predicate = criteriaBuilder.and(
                criteriaBuilder.equal(root.get("circleId"), circleId),
                criteriaBuilder.isFalse(root.get("deleted")));

        if (RenweyUtil.isNotEmpty(identity)) {
            predicate = criteriaBuilder.and(
                    predicate,
                    criteriaBuilder.equal(root.get("identity"), identity));
        }

        if (RenweyUtil.isNotEmpty(name)
                || RenweyUtil.isNotEmpty(phone)) {

            Join<CircleFriendEntityImpl, UserSocialCapitalEntityImpl> socialCapitalEntity
                    = root.join("socialCapitalEntity", JoinType.INNER);

            if (RenweyUtil.isNotEmpty(name)) {
                predicate = criteriaBuilder.and(
                        predicate,
                        criteriaBuilder.like(
                                socialCapitalEntity.get("name"),
                                RenweyUtil.addMysqlFullTextSearch(name)));
            }

            if (RenweyUtil.isNotEmpty(phone)) {
                predicate = criteriaBuilder.and(
                        predicate,
                        criteriaBuilder.equal(socialCapitalEntity.get("phone"), phone));
            }

        }

        criteriaQuery.where(predicate);

        criteriaQuery.orderBy(
                criteriaBuilder.asc(root.get("identity")),
                criteriaBuilder.desc(root.get("id")));

        TypedQuery<CircleFriendEntityImpl2> typedQuery = getEntityManager().createQuery(criteriaQuery);
        typedQuery.setFirstResult(start).setMaxResults(maxResult);
        return new ArrayList<>(typedQuery.getResultList());
    }