JPA的连表查询

你的名字 2022-09-11 02:29 209阅读 0赞

用jpql写

@Query("select c from Coupon c\n" +
                "join c.categoryList ca\n" +
                "join Activity a on a.id = c.activityId\n" +
                "where ca.id = :cid\n" +
                "and a.startTime < :now\n" +
                "and a.endTime > :now\n")
        List<Coupon> findByCategory(Long cid, Date now);
    
        @Query("select c from Coupon c\n" +
                "join Activity a on c.activityId = a.id\n" +
                "where c.wholeStore = :isWholeStore\n" +
                "and a.startTime < :now\n" +
                "and a.endTime > :now\n")
        List<Coupon> findByWholeStore(Boolean isWholeStore, Date now);

用JPA命名方法写

@Repository
    public interface ActivityRepository extends JpaRepository<Activity, Long> {
    
        Activity findByName(String name);
    
        /**
         * 通过优惠券id查询优惠券对应的活动
         */
        Optional<Activity> findByCouponListId(Long couponId);
    
    }

CouponList是导航属性,相当于是coupon表

@Entity
    @Setter
    @Getter
    @Where(clause = "delete_time is null and online = 1")
    public class Activity extends BaseEntity {
        @Id
        private Long id;
        private String title;
        private String name;
        private String description;
        private Date startTime;
        private Date endTime;
        private Boolean online;
        private String entranceImg;
        private String internalTopImg;
        private String remark;
    
        @OneToMany(fetch = FetchType.LAZY)
        @JoinColumn(name = "activityId")
        private List<Coupon> couponList;
    }

转换成sql

select
            activity0_.id as id1_1_,
            activity0_.create_time as create_t2_1_,
            activity0_.delete_time as delete_t3_1_,
            activity0_.update_time as update_t4_1_,
            activity0_.description as descript5_1_,
            activity0_.end_time as end_time6_1_,
            activity0_.entrance_img as entrance7_1_,
            activity0_.internal_top_img as internal8_1_,
            activity0_.name as name9_1_,
            activity0_.online as online10_1_,
            activity0_.remark as remark11_1_,
            activity0_.start_time as start_t12_1_,
            activity0_.title as title13_1_ 
        from
            activity activity0_ 
        left outer join
            coupon couponlist1_ 
                on activity0_.id=couponlist1_.activity_id 
                and (
                    couponlist1_.delete_time is null
                ) 
        where
            (
                activity0_.delete_time is null 
                and activity0_.online = 1
            ) 
            and couponlist1_.id=?