hql 关联查询

一对多标注和hql中外关联的区别

 

1. 一对多标注, 生成一个list结构, 至少执行两条sql语句，可以使用lazy模式加载list

2. hql关联查询,生成多个信息重复的实体类, 只生成一条sql语句执行，甭能lazy模式加载

另见https://blog.csdn.net/asdfsadfasdfsa/article/details/86534215

 

方式一：

hql语句

 @Query("select new OrderItemInfo(s.id,s.barCode,s.itemId,i.uid,i.deliveryType) 
from OrderItemInfo s left join ItemDto i on i.id=s.itemId where s.id in ?1")
    List<OrderItemInfo> getAllBySpecIdIn(Collection<Long> specIds);

两个相关实体类必须有@Table和@Entity注解

@Getter
@Setter
@Table(name = "item_specifications")
@Entity
@NoArgsConstructor
@AllArgsConstructor
public class OrderItemInfo {
    @Id
    @Column(name = "id")
    private long specId;
    private String barCode;
    private long itemId;
    private String itemUid;
    @NotNull
    private DeliveryType deliveryType;
}

 

@Entity
@Table(name = "item")
@Getter
@Setter
public class ItemDto {
    @Id
    private long id;
    private String name;
    @Column(name = "item_describe")
    private String describe;
    private String detail;
    private String pictureUrl;
    @Enumerated
    private SaleStatus saleStatus;
    private long itemClassifyId;
    private String tags;
    @Enumerated
    private DeliveryType deliveryType;
    @Column(name = "item_package")
    @Enumerated
    private PackageType packageType;
    private Integer expectedSuplyAmount;
    @Column(name = "suply_unit")
    private String supplyUnit;
    private boolean soldOut;
    private String uid;
}

 

方式二

 

 

[java] view plain copy

1. public void test(Integer id) {  
2.         String hql = " from TestQuestKnow a left join TestKnowDic b with a.know_id = b.know_id where a.id = :id";  
3.         Query query = createSession().createQuery(hql).setParameter("id", id);  
4.         List<Object> resultList = query.list();  
5.         for (int i = 0; i < resultList.size(); i++) {      
6.             Object[] obj = (Object[])resultList.get(i);      
7.             System.out.println(obj[0]+","+obj[1]);      
8.         }   
9.     }  

 

[java] view plain copy

1. public class TestKnowDic {  
2.   
3.     @Id  
4.     private Integer id;  
5.     private String uid;  
6.     private Integer know_id;  
7. }  

 

[java] view plain copy

1. public class TestQuestKnow {  
2.   
3.     @Id  
4.     private Integer id;  
5.     private Long sid ;  
6.     private Integer know_id;  
7. }  

 

Hibernate: 

    select
        testquestk0_.id as id1_11_0_,
        testknowdi1_.id as id1_9_1_,
        testquestk0_.know_id as know_id2_11_0_,
        testquestk0_.sid as sid3_11_0_,
        testknowdi1_.know_id as know_id2_9_1_,
        testknowdi1_.uid as uid3_9_1_ 
    from
        lftquestdb.quest_know_info testquestk0_ 
    left outer join
        lftquestdb.know_dic testknowdi1_ 
            on (
                testquestk0_.know_id=testknowdi1_.know_id
            ) 
    where
        testquestk0_.id=?
TestQuestKnow(id=6343770, sid=300526385359636340, know_id=190),TestKnowDic(id=123786, uid=300-192-190, know_id=190)
TestQuestKnow(id=6343770, sid=300526385359636340, know_id=190),TestKnowDic(id=275314, uid=300-192-191-190, know_id=190)
TestQuestKnow(id=6343770, sid=300526385359636340, know_id=190),TestKnowDic(id=275766, uid=300-191-190, know_id=190)
TestQuestKnow(id=6343770, sid=300526385359636340, know_id=190),TestKnowDic(id=350474, uid=300-190, know_id=190)