Some uncommon but useful tips for hql (personal summary)

Recently has been using SPRING-DATA-JPA this thing, feel the method of annotation HQL statement is a very common method,

There are some experiences on HQL to share:

I. Join of the HQL

The advantage of HQL is the direct correlation, but there are some places to be aware of when a join query is made through HQL statements:

HQL join cannot join two tables directly like a SQL-like join, you need to maintain the association relationship before join:

@Entity @table (name = "Mq_mark") public class Markmodel implements Serializable {/** *  */private static final long seria Lversionuid = -5169585172209348371l; @Id @generatedvalue (strategy = generationtype.auto) public Long Id; @ManyToOne ( Fetch = Fetchtype.eager) @JoinColumn (name = "show_id", nullable = False) @Fetch (fetchmode.join) public ShowImage ShowImage ;}

Then in the HQL join query

@Query ("Select R from Markmodel t join T.showimage R where t.type=? 1 and T.datamid =? 2  ")    List<showi Mage> findmarkimages (Integer marktype, Long datamid, pageable pageable);

A LEFT JOIN statement is generated

Second: The HQL of the statement

Using SQL to de-weight is generally using distinc, but this approach can affect efficiency.

HQL recommended to use GROUP by

@Query ("Select T.showimage from Markmodel t where t.type=? 1 and T.datamid =? 2  GROUP by t.showimage.id")    List <ShowImage> findmarkimages (Integer marktype, Long datamid, pageable pageable);

Take a look at the statements it generates:

SelectShowimage1_.id asId1_25_, Showimage1_.create_time asCreate_t5_25_, Showimage1_.image_url asImage_ur8_25_, Showimage1_.position asPositio14_25_

 fromXXX markmodel0_Inner JoinMq_showpic showimage1_ onmarkmodel0_.show_id=Showimage1_.id

 Cross JoinMq_showpic showimage2_

wheremarkmodel0_.show_id=Showimage2_.id andMarkmodel0_.type=? andmarkmodel0_.datam_id=?

Group  bymarkmodel0_.show_idOrder  byShowimage2_.create_timedescLimit?

Here we see a cross join, which shows that the cross join is about to produce a Cartesian product, which can have a very large effect on efficiency,

After troubleshooting, it was found that only the order by was useful to showimage2_, so guess is the order by question, see how to generate the order by

New Pagerequest (page, rows, Direction.desc, "Showimage.createtime");//Note here the conditions of the list this. Markdao.findmarkimages (Marktype, Datamid, pageable);

This uses the join table for sorting, so the cross join appears

Change this to sort by the createtime of the main table

New Pagerequest (page, rows, Direction.desc, "createtime");

The generated SQL changes immediately:

SelectShowimage1_.id asId1_25_, Showimage1_.create_time asCreate_t5_25_, Showimage1_.image_url asImage_ur8_25_, Showimage1_.position asPositio14_25_

 fromMq_mark markmodel0_Inner JoinMq_showpic showimage1_ onmarkmodel0_.show_id=Showimage1_.id

whereMarkmodel0_.type=? andmarkmodel0_.datam_id=?

Group  byShowimage1_.idOrder  byMarkmodel0_.create_timedescLimit?

Not a cross join.

Some uncommon but useful tips for hql (personal summary)