JPA的连表查询

最新推荐文章于 2024-09-06 15:54:47 发布

阿花落知多少

最新推荐文章于 2024-09-06 15:54:47 发布

阅读量4.2k

点赞数 1

文章标签： python

本文链接：https://blog.csdn.net/qq_39940205/article/details/120235842

版权

用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=?