Shulou(Shulou.com)06/01 Report--

Editor to share with you Spring Data Jpa complex query example analysis, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to understand it!

Summary of Spring Data Jpa complex queries

It's just a summary, so no more nonsense.

Entity class

@ Entity@Table (name = "t_hotel") @ Datapublic class THotel {@ Id private int id; private String name; private String address; / * Urban id * / private String city;} @ Entity@Table (name = "t_city") @ Datapublic class TCity {@ Id private int id; private String name; private String state; private String country; private String map;}

SpringDataJpa automatically generates table structures in the database when SpringBoot is started.

In order to query the requirements, I casually added some data, as shown in the following figure

Create a new interface

Public interface TCityRepository extends JpaRepository, JpaSpecificationExecutor {}

Unit test class

@ RunWith (SpringRunner.class) @ SpringBootTestpublic class TCityRepositoryTest {@ Autowired private TCityRepository tCityRepository;} 1. Find records with Id less than 3 and names with shanghai / * * find records with Id less than 3 and names with `shanghai`. * * @ param id id * @ param name City name * @ return City list * / List findByIdLessThanAndNameLike (int id, String name)

Unit testing

@ Testpublic void findByIdLessThanAndNameLike () throws Exception {List shanghai = tCityRepository.findByIdLessThanAndNameLike (3, "% shanghai%"); Assert.assertTrue (shanghai.size () > 0);} 2. Query all hotel and city information through hotel name page / * * query hotel and city information through hotel name page * * @ param name hotel name * @ param pageable paging information * @ return Page * / @ Query (value = "select t1.name as cityName" T2.name as hotelName\ n "+" from t_city T1\ n "+" left join t_hotel T2 on t2.city = 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 findCityAndHotel (@ Param ("name") String name, Pageable pageable)

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

Unit testing

@ Testpublic void findCityAndHotel () throws Exception {Page cityAndHotel = tCityRepository.findCityAndHotel ("Hotel", new PageRequest (0,10)); Assert.assertTrue (cityAndHotel.getTotalElements () > 0);}

I already made it clear in my last JPA article about the method of converting List to List, so I won't repeat it again.

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

3 and 2 are actually the same. I won't make a paging query for convenience.

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

/ * HQL queries all hotel and city information by hotel name * * @ return * / @ Query (value = "select new map (T1 left join THotel T2) from TCity T1 left join THotel T2 on t1.id=t2.city where t2.name =: name") List findCityAndHotelByHQL (@ Param ("name") String name)

The test method is similar to 2, so I won't paste it.

There are many ways for Map to convert entity classes. If it is returned directly to the foreground, there is no need to convert it to an object.

4.HQL directly returns the entity class / * Association query * * @ return * / @ Query (value = "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 =: name") List findCityAndHotelByHQLResultObj (@ Param ("name") String name)

In order to facilitate CityHohel, I have only encapsulated two attributes, which are exactly the same as the fields queried by HQL, and must be consistent.

/ * Created by ZhuPengWei on 2018/5/12.*/@Datapublic class CityHohel {private String cityName; private String hotelName; public CityHohel (String cityName, String hotelName) {this.cityName = cityName; this.hotelName = hotelName;}}

Of course, this parameter construction method must be written, otherwise an exception will be thrown to convert the entity.

Unit testing

Testpublic void findCityAndHotelByHQLResultObj () throws Exception {List cityAndHotelByHQLResultObj = tCityRepository.findCityAndHotelByHQLResultObj ("hotel"); Assert.assertTrue (cityAndHotelByHQLResultObj.size () > 0);}

4.HQL directly returns the entity class by paging the hotel name to query all the information about the hotel and the city.

/ * * @ return * / @ Query (value = "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 =: name", countQuery = "select count (*) from TCity T1 left join THotel T2 on t1.id=t2.city where t2.name =: name") Page findCityAndHotelAllSelf (@ Param ("name") String name, Pageable pageable) @ Testpublic void findCityAndHotelAllSelf () throws Exception {Page cityAndHotelAllSelf = tCityRepository.findCityAndHotelAllSelf ("Hotel", new PageRequest (0,10)); Assert.assertTrue (cityAndHotelAllSelf.getTotalElements () > 0);} 5. Dynamically query all the information about hotels and cities and return directly to the entity class.

If it is a dynamic query, of course, we have to construct a sql to query it. Of course, if it is not a custom entity object, I will not write a lot of it on the Internet.

Direct walking test

@ Autowired@PersistenceContextprivate EntityManager entityManager;@Testpublic void testDynamic () throws Exception {String sql = "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'; Query query = entityManager.createQuery (sql); List resultList = query.getResultList (); Assert.assertTrue (resultList.size () > 0);}

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

/ * * Custom query * @ param sql * @ param entityManager * @ return * / default List customQuery (String sql, EntityManager entityManager) {return entityManager.createQuery (sql). GetResultList ();}

Then the test class dynamically splices the SQL statement according to the condition to call the

JPA# complex query # Custom query Writing Custom SQL based on the following information

1. When SpringData JPA generates an implementation for the Repository interface, it looks for a class with "interface name" + "Impl", and if so, merges the methods of this class into the implementation to be generated.

Suppose you want to write a custom sql query for the interface StudentRepository.

To write a custom SQL based on the first information

The following three steps are required:

1. Customize an interface and declare the method StudentCoustomRepository in the interface. This custom interface name is not important.

two。 Let the target interface inherit the custom interface, so that the target interface has the corresponding method

3. Write an implementation class for a custom method, which needs to use "target interface name" + "Impl" as the class name

That is, StudentRepositoryImpl, so that SpringDataJpa will include these methods when generating the implementation for StudentRepository.

Public class StudentRepositoryImpl implements StudentCoustomRepository {/ / very complex SQL can be written here for demonstration purposes, so that private static final String SQL = "select * from t_student where name like: name"; @ PersistenceContext private EntityManager em @ SuppressWarnings ({"rawtypes", "unchecked"}) @ Override public List findByName (String name) {Query query = em.createNativeQuery (SQL) .setParameter ("name", "%" + name+ "%"); query.unwrap (SQLQuery.class) .setResultTransformer (Transformers.ALIAS_TO_ENTITY_MAP); List queryList = query.getResultList () If (queryList.size () = = 0) {System.out.println ("record whose Student name is" + name + "not found"); return null;} List retVal = new ArrayList (); for (Object o: queryList) {Map student = (Map) o; StudentVO vo = new StudentVO () Try {/ / org.apache.commons.beanutils.BeanUtils; / / use the beanutil of apaches to directly convert map to instance BeanUtils.populate (vo, student); retVal.add (vo) } catch (IllegalAccessException | InvocationTargetException e) {System.out.println ("exception occurred while parsing StudentVO bean:" + e.getMessage ());}} return retVal;}} these are all the contents of the article "sample Analysis of complex Spring Data Jpa queries". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.