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

This article mainly explains "springboot jpa how to achieve custom query return results", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "springboot jpa how to achieve custom query return results" bar!

Jpa returned results Custom query

The custom entities here are entities that are not mapped in the data. You can return the value of the aggregate function.

The first method

Entity class. (I am using idea here)

@ Datapublic class NameOnlyDto implements Serializable {private String firstName; private String lastName; private String sex;}

The repository class (this is not the way in which you inherit from jpa. Instead, use EntityManager). There are detailed comments in the code.

@ Repositorypublic class CustomEntity {/ / entity manager EntityManager is the object responsible for managing Entity. The operations on Entity, including adding, deleting, modifying, and querying, are implemented through the entity manager. @ PersistenceContext private EntityManager entityManager; / / EntityManagerFactory @ Transactional public List listNameOnlyDto () {String sql = "select p.last_name lastName, p.first_name firstName from PERSON p"; / / hibernate 5.2 before / / SQLQuery sqlQuery = entityManager.createNativeQuery (sql) .unwrap (NativeQueryImpl.class) / / unwrap after hibernate 5.2Let Query of JPA return Map object javax.persistence.Query.unwrap / / hibernate or use AliasToBeanResultTransformer custom type conversion ResultTransformer underline to hump SQLQuery sqlQuery = entityManager.createNativeQuery (sql) .unwrap (NativeQueryImpl.class); / * Query query = sqlQuery.setResultTransformer (Transformers.aliasToBean (NameOnlyDto.class)); List list = query.getResultList () / / .list (); * / Query query = sqlQuery.setResultTransformer (Transformers.aliasToBean (NameOnlyDto.class)); List list = query.getResultList (); entityManager.clear (); return list;}}

OK . That's it. Personal testing doesn't seem to work in the oracle database. Maybe the result of sql query is different.

The second method

It can be used on specific occasions.

Custom CLA

Public class NameOnlyDtoT implements Serializable {private String firstName; private String lastName; / / private String address; private String sex; / / Constructor Special attention here returns several fields where the construction parameters of several fields do not feel appropriate public NameOnlyDtoT (String firstName, String lastName) {this.firstName = firstName; this.lastName = lastName } / * public NameOnlyDtoT (String firstName, String lastName, String sex) {this.firstName = firstName; this.lastName = lastName; this.sex = sex;} * /}

Here is the way to write it in repository

/ / remember that you cannot use @ query. If you use @ query, it feels like the same logical List findByLastName (String lastname,Class type) as the method above; use (note here that fields in NameOnlyDtoT can only be assigned to fields contained in person. This is what I feel about the existence of chicken ribs) public List testNameOnlyDtoTS () {List nameOnlyDtoTS= personRepository.findByLastName ("", NameOnlyDtoT.class); return nameOnlyDtoTS;} use jpa two tables to check and return custom entities

In java development, when you use the Jpa framework to query connected tables (you need to return respective parts of the fields of two tables), you feel tricky in the process of returning objects. There has been no good solution, and there are various versions of methods on the Internet. I find the following methods the most convenient to use.

1. Create a blank SpringBoot project and introduce pom dependency

First look at the project structure, in order to simplify, did not introduce the service layer, directly use controller to call the dao layer

Pom.xml configuration

Org.springframework.boot spring-boot-starter-web org.projectlombok lombok 1.18.8 org.springframework.boot spring-boot-starter-data-jpa mysql mysql-connector-java runtime 2 、 Application.yml configuration file server: port: 13333spring: datasource: url: jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=convertToNull username: root password: 12345678 driver-class-name: com.mysql.jdbc.Driver jpa: show-sql: true hibernate: ddl-auto: none3, database (with two tables user/address)

Now we need to jointly check the user table and the address table. The user_id in the address table is associated with the id in the user table.

4. User.java and Address.java

5. UserDaoRepository.java and AddressDaoRepository.java

Attach the code of UserDaoRepository.java

Package com.lss.jpa.dao;import com.lss.jpa.entity.dto.UserAddressDto;import com.lss.jpa.entity.po.User;import org.springframework.data.jpa.repository.JpaRepository;import org.springframework.data.jpa.repository.Query;import java.util.List;import java.util.Map Public interface UserDaoRepository extends JpaRepository {@ Query (value = "select\" title\ "as common, u.id as id, u.name as name, a.id as addressId, a.address as addressName from user u, address a where u.id = a.user_id", nativeQuery = true) public List findAllUserAddress () @ Query (value = "select\" title\ "as common, u.id as id, u.name as name, a.id as addressId, a.address as addressName from user u, address a where u.id = a.user_id and u.id=1", nativeQuery = true) public UserAddressDto findAllUserAddressById () @ Query (value = "select\" title\ "as common, u.id as id, u.name as name, a.id as addressId, a.address as addressName from user u, address a where u.id = a.user_id and u.id=1", nativeQuery = true) public Map findAllUserAddressByMap ();} 6, UserAddressDto.java code package com.lss.jpa.entity.dto;public interface UserAddressDto {Integer getId (); String getName (); String getAddressName (); Integer getAddressId (); String getCommon () }

Here we customize the UserAdressDto to receive the data returned by the two tables. Note: at this time, an interface is created, and the fields in it are received parameters created in the form of get.

7 、 TestController.javapackage com.lss.jpa.web;import com.lss.jpa.dao.UserDaoRepository;import com.lss.jpa.entity.dto.UserAddressDto;import com.lss.jpa.entity.po.User;import lombok.extern.slf4j.Slf4j;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RestController;import java.util.List;import java.util.Map;import java.util.concurrent.locks.Lock Import java.util.concurrent.locks.ReentrantLock;@RestController@Slf4jpublic class TestController {@ Autowired private UserDaoRepository userDaoRepository; @ GetMapping ("test") public String test () {List all = userDaoRepository.findAllUserAddress () All.stream () .forEach (dto-> {log.info ("result: id: {}, name: {}, addressId: {}, addressName: {}, common: {}", dto.getId (), dto.getName (), dto.getAddressId (), dto.getAddressName (), dto.getCommon ();}); UserAddressDto dto = userDaoRepository.findAllUserAddressById () Log.info ("result: id: {}, name: {}, addressId: {}, addressName: {}, common: {}", dto.getId (), dto.getName (), dto.getAddressId (), dto.getAddressName (), dto.getCommon ()); Map map = userDaoRepository.findAllUserAddressByMap (); log.info ("map: {}", map); List userList = userDaoRepository.findAll (); log.info ("userList: {}", userList) Return "ok";}}

Finally, start the project and call the / test interface

Curl http://localhost:13333/test

Look at the printed results in console

Hibernate: select "title" as common, u.id as id, u.name as name, a.id as addressId, a.address as addressName from user u, address a where u.id = a.user_id

2020-02-23 13 INFO 1415 33.293 INFO 2816-[io-13333-exec-3] com.lss.jpa.web.TestController: result: id:1, name:zhangsan, addressId:1, addressName:beijing, common:title

2020-02-23 13 INFO 1415 33.293 INFO 2816-[io-13333-exec-3] com.lss.jpa.web.TestController: result: id:2, name:lisi, addressId:2, addressName:tianjin, common:title

Hibernate: select "title" as common, u.id as id, u.name as name, a.id as addressId, a.address as addressName from user u, address a where u.id = a.user_id and u.id=1

2020-02-23 13 com.lss.jpa.web.TestController 1415 33.296 INFO 2816-[io-13333-exec-3] com.lss.jpa.web.TestController: result: id:1, name:zhangsan, addressId:1, addressName:beijing, common:title

Hibernate: select "title" as common, u.id as id, u.name as name, a.id as addressId, a.address as addressName from user u, address a where u.id = a.user_id and u.id=1

2020-02-23 13 com.lss.jpa.web.TestController 1433. 299 INFO 2816-[io-13333-exec-3] com.lss.jpa.web.TestController: map:org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap@72cce81

Hibernate: select user0_.id as id1_1_, user0_.name as name2_1_ from user user0_

2020-02-23 13 com.lss.jpa.web.TestController 14charge 33.305 INFO 2816-[io-13333-exec-3] com.lss.jpa.web.TestController: userList: [User (id=1, name=zhangsan), User (id=2, name=lisi), User (id=3, name=wangwu), User (id=4, name=zhaoliu)]

We can copy to the output sql and the joint data results, all of which are perfectly received by dto.

In particular, the received dto must be interface, and the parameters must be written as the method body in the form of get, so that jpa will automatically map to interface after querying the data. The method body of calling get is equivalent to calling the parameter value, so the data will be taken out.

Thank you for your reading, the above is the content of "springboot jpa how to achieve a custom query to return results". After the study of this article, I believe you have a deeper understanding of how to achieve a custom query with springboot jpa, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.