Spring Data Jpa Complex Query Summary (Multi-table association and custom paging)

Entity class

@Entity @table (name= "T_hotel") @Data Public classThotel {@IdPrivate intID; PrivateString name; PrivateString address; /*** City ID*/    PrivateString City; @Entity @table (Name= "T_city") @Data Public classtcity {@IdPrivate intID; PrivateString name; PrivateString State; PrivateString Country; PrivateString map;}

New interface

 Public Interface extends Jparepository<tcity, Integer>, jpaspecificationexecutor<tcity> {}

Unit Test class

@RunWith (Springrunner.  Class) @SpringBootTestpublicclass  tcityrepositorytest{     @Autowired     Private  tcityrepository tcityrepository;}

1. Find out ID less than 3, and name with shanghaiThe record.

/**@param  ID   @param@return* / List<TCity> findbyidlessthanandnamelike (int ID, String name);

Unit Test

@Test  Public void throws Exception {    List<TCity> shanghai = tcityrepository.findbyidlessthanandnamelike (3, "%shanghai%" );     > 0);}

2. Check all the information of the hotel and the city via the hotel name page

/*** Check the hotel and city information via the hotel Name page * *@paramName Inn *@parampageable Paging Information *@returnpage<object[]>*/@Query (Value= "Select T1.name as cityname,t2.name as hotelname\n" + "from t_city t1\n" + "left join T_hotel T2 on t2.c ity = t1.id\n "+" where T2.name =: Name ", Countquery= "SELECT count (*)" + "from t_city t1 \ n" + "left join T_hotel t2 on t2.city = t1.id\n" + "Where T2.name =: Name", Nativequery=true) Page<Object[]> Findcityandhotel (@Param ("name") String name, pageable pageable);

In order to save time I only queried the name of the city and the name of the hotel, respectively, between select and from. If you want to check all the information, you can change
t1.*,
t2.*

Unit Test

@Test  Public void throws Exception {    Pagenew pagerequest (0, ten));     > 0

3.HQL Find all the information about the hotel and the city through the hotel name

3 and 2 are actually the same, for convenience I will not make paging query

HQL can use the map to accept the returned parameters, as follows:

/**@return*/= "Select New Map (T1,T2) from  tcity T1 left  join Thotel T2 on t1.id=t2.city where T2.name =:name ") List<map<string, object>> findcityandhotelbyhql (@Param ("name") String name);

The test method and 2 are about the same, I'm not pasting.

Map

4.HQL Find all the information about hotels and cities by hotel name directly return to the entity class

/**@return*/= "Select New Pers.zpw.domain.CityHohel (T1.name as CityName, T2.name as Hotelname) from  tcity T1 left  joins Thotel T2 on t1.id=t2.city where T2.name =:name ") List<ci Tyhohel> Findcityandhotelbyhqlresultobj (@Param ("name") String name);

To facilitate Cityhohel I only encapsulated 2 properties, which are exactly the same as the fields of the HQL query and must be consistent.

/** * Created by Zhupengwei on 2018/5/12. */ @Data  Public class Cityhohel {        private  String cityname;         Private String hotelname;          Public Cityhohel (String cityname, String hotelname) {            this. cityname = cityname;              this. hotelname = hotelname;        }}

Of course, this method of constructing the parameter must be written, otherwise the exception of the converted entity will be thrown

Unit Test

@Test  Public void throws Exception {    List<CityHohel> cityandhotelbyhqlresultobj = Tcityrepository.findcityandhotelbyhqlresultobj ("Hotel");     > 0);}

4.HQL Check the hotel name and all the information of the city directly return to the entity class

/*** Related Query * *@return */@Query (Value= "Select New Pers.zpw.domain.CityHohel (T1.name as cityname,t2.name as Hotelname) from Tcity T1 left joins Thotel T2 on t 1.id=t2.city where T2.name =:name ", Countquery= "SELECT count (*) from tcity T1 left join Thotel T2 on t1.id=t2.city where T2.name =:name") Page<CityHohel> findcityandhotelallself (@Param ("name") String name, pageable pageable); @Test Public voidFindcityandhotelallself ()throwsException {Page<CityHohel> cityandhotelallself = tcityrepository.findcityandhotelallself ("Hotel",NewPagerequest (0, 10)); Assert.asserttrue (cityandhotelallself.gettotalelements ()> 0);}

5. Dynamic Search for all information about hotels and cities directly return to the entity class

If it is a dynamic query, of course, we have to construct a SQL to query, of course, if it is not a custom entity object, such a lot of online I will not write.

Go straight to the test

@Autowired @persistencecontext Private Entitymanager Entitymanager; @Test  Public void throws Exception {        = "Select New Pers.zpw.domain.CityHohel (T1.name as cityname,t2.name as Hotelname)  from tcity T1 left  join Thotel T2 on t1.id=t2.city where t2.name = ' hotel ';         = entitymanager.createquery (sql);         = query.getresultlist ();         > 0);}

This test is passed, so you know that in the business layer's approach we can construct the SQL statement dynamically. For example, you can define a method like this in the interface.

/**@param @param @return */default List customquery (String sql, Entitymanager entitymanager) {    return  Entitymanager.createquery (SQL). Getresultlist ();}

And then in the test class dynamically according to the conditions to splice SQL statements to invoke

Reprinted: 80298354

Spring Data Jpa Complex Query Summary (Multi-table association and custom paging)