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

This article mainly explains "how to understand the projection query of jpql in spring-data-jpa". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to understand the projection query of jpql in spring-data-jpa".

A projection query is to retrieve only part of the fields of the table. Instead of rough SELECT * FROM... Retrieve all column data. For example, when retrieving the user's balance information, there is no need to retrieve the user's profile picture, creation date and other fields. It saves bandwidth transmission and memory footprint, and avoids the possibility of exposing more data to the client.

Let's talk about some points for attention of jpql first.

You operate on object @ Entity, not tables, on object properties, and not on table fields

The default Entity name is an uppercase class name, which can also be modified by modifying the annotation of the @ Entity attribute.

The use of ``symbols and the addition of semicolons at the end are not supported:

Aliases are supported using the AS keyword

Prepare an entity class import javax.persistence.*;import java.io.Serializable;import java.time.LocalDateTime for demonstration / * user * / @ Entity@Table (name = "user", indexes = {@ Index (columnList = "account", unique = true),}) @ org.hibernate.annotations.Table (appliesTo = "user", comment = "user") public class User implements Serializable {/ * / private static final long serialVersionUID =-129586628554861093L @ Id @ Column (columnDefinition = "INT (11) UNSIGNED COMMENT 'id'") @ GeneratedValue (strategy = GenerationType.IDENTITY) private Integer id; / / login account @ Column (columnDefinition = "VARCHAR (20) COMMENT' login account'", nullable = false) private String account / / nickname @ Column (columnDefinition = "VARCHAR (20) COMMENT 'nickname") private String name; / / avatar @ Column (columnDefinition = "VARCHAR (255) COMMENT' avatar") private String avatar / / creation time @ Column (name = "created_date", columnDefinition = "timestamp DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time'", nullable = false) private LocalDateTime createdDate; / / Last modification time @ Column (name = "last_modified_date", columnDefinition = "timestamp NULL DEFAULT NULL COMMENT 'last modification'") private LocalDateTime lastModifiedDate / / record status @ Column (columnDefinition = "TINYINT (1) unsigned COMMENT 'enable'", nullable = false) private Boolean enabled;} retrieve single row and single column data

Single-row, single-column data, relatively simple. The return value of the retrieval method in the Repository interface can be defined as the column data type returned.

Retrieve account field public interface UserRepository extends JpaRepository according to user ID, JpaSpecificationExecutor {/ * retrieve account information based on user ID * @ param id * @ return * / @ Query ("SELECT u.account FROM User AS u WHERE u.id =: id") String accountById (@ Param ("id") Integer id);} Test Code @ Autowiredprivate UserRepository userRepository @ Test@Transactional (readOnly = true) public void test () {String account= userRepository.accountById (1); LOGGER.info ("account= {}", account);} execute log

You can see in the log that the executed SQL meets the requirements, retrieves only one column, and returns the correct results.

Hibernate: select user0_.account as col_0_0_ from user user0_ where user0_.id=? O.h.type.descriptor.sql.BasicBinder: binding parameter [1] as [INTEGER]-[1] i.s.jpa.test.JpaApplicationTest: account=KevinBlandy retrieves single row and multiple columns of data

Encapsulate the result set using the constructor of a custom object

Customize an object that encapsulates the result set

Here you directly inherit the User class, and you need a constructor to define the columns that need to be projected and retrieved.

Import java.time.LocalDateTime;public class UserDTO extends User {/ * / private static final long serialVersionUID = 6393508321723484097L; public UserDTO (String account, String name, LocalDateTime createdDate) {super.setAccount (account); super.setName (name); super.setCreatedDate (createdDate);}}

According to retrieving the user's basic information: account, nickname, creation time, encapsulating the result set as a UserDTO object

Use the NEW keyword in jpql to specify the full path of the custom object, and specify the columns to be retrieved in the constructor, both sql syntax and object-oriented thinking.

/ * * based on the basic information of the user retrieved: account, nickname, creation time, encapsulated result set as UserDTO object * @ param id * @ return * / @ Query ("SELECT NEW io.springboot.jpa.entity.UserDTO (u.account, u.name, u.createdDate) FROM User AS u WHERE u.id =: id") UserDTO querySimpleInfoById (@ Param ("id") Integer id); test @ Autowiredprivate UserRepository userRepository @ Test@Transactional (readOnly = true) public void test () {UserDTO userDTO = userRepository.querySimpleInfoById (1); LOGGER.info ("user= {}", userDTO) } execute log Hibernate: select user0_.account as col_0_0_, user0_.name as col_1_0_, user0_.created_date as col_2_0_ from user user0_ where user0_.id=?o.h.type.descriptor.sql.BasicBinder: binding parameter [1] as [INTEGER]-[1] i.s.jpa.test.JpaApplicationTest: user=User [id=null, account=KevinBlandy, name=Vin Avatar=null, createdDate=2020-07-16T10:39:02, lastModifiedDate=null, enabled=null] encapsulates the result set using Oject []

Encapsulating the result set as an array collection does not require redefining the object, but the drawback is that you need to remember what the data in each array subscript represents.

According to the basic information of the user retrieved: account, nickname, creation time, encapsulated result set as Object array

/ * * based on the basic information of the user retrieved: account, nickname, creation time, the encapsulated result set is "Object []" * @ param id * @ return * / @ Query ("SELECT u.account, u.name, u.createdDate FROM User AS u WHERE u.id =: id") Object querySimpleInfoById (@ Param ("id") Integer id)

Note that the return value here defines Object instead of the imagined Object []. If the return value is defined as Object [], jpa will encapsulate the result set into a two-dimensional array, which is suitable for multi-row and multi-column situations.

What is demonstrated here is retrieval based on id, which can only be single-row and multi-column. So define it as Object and make a strong conversion by yourself.

Test @ Autowiredprivate UserRepository userRepository;@Test@Transactional (readOnly = true) public void test () {/ / cast to Object [] Object [] results = (Object []) userRepository.querySimpleInfoById (1); for (Object result: results) {LOGGER.info ("result= {}", result) / / traversing the occasional results of the array}} execute log Hibernate: select user0_.account as col_0_0_, user0_.name as col_1_0_ User0_.created_date as col_2_0_ from user user0_ where user0_.id=?o.h.type.descriptor.sql.BasicBinder: binding parameter [1] as [INTEGER]-[1] i.s.jpa.test.JpaApplicationTest: result=KevinBlandyi.s.jpa.test.JpaApplicationTest: result=Vini.s.jpa.test.JpaApplicationTest: result=2020-07-16T10:39:02 encapsulates the result set using Map

The advantage of using map as the result set is that there is no need to redefine the object. And you can find the field you want according to key. I think the most convenient way, with the help of spring, BeanUtils, you can convert Map into an object.

According to the basic information of the user retrieved: account, nickname, creation time, the encapsulated result set is "Map"

/ * * based on the basic information of the user retrieved: account, nickname, creation time, the encapsulated result set is "Map" * @ param id * @ return * / @ Query ("SELECT NEW MAP (u.account AS account, u.name AS name, u.createdDate AS createdDate) FROM User AS u WHERE u.id =: id") Map querySimpleInfoById (@ Param ("id") Integer id)

You need to alias the retrieved column using AS as the name of key. If you do not use AS alias, in the case of single row and single column, key = null, and in the case of multiple rows, key = serial number (starting with 0)

{"0": "Vin", "1": "KevinBlandy"} Test Code @ Autowiredprivate UserRepository userRepository;@Test@Transactional (readOnly = true) public void test () {Map result= userRepository.querySimpleInfoById (1); LOGGER.info ("result= {}", result) } execute log Hibernate: select user0_.account as col_0_0_, user0_.name as col_1_0_, user0_.created_date as col_2_0_ from user user0_ where user0_.id=?o.h.type.descriptor.sql.BasicBinder: binding parameter [1] as [INTEGER]-[1] i.s.jpa.test.JpaApplicationTest: result= {name=Vin Account=KevinBlandy, createdDate=2020-07-16T10:39:02} retrieve multiple columns and N rows

For record encapsulation with multiple lines, it is very simple. On the basis of the above, the result of the return value can be changed to an array or a Collective subclass.

Single-row multi-column List

Multiple rows and columns List

Multiline queue List

Multiline queue List

Multiline queue Object [], each Object element is an array

...

Thank you for your reading, the above is the content of "how to understand the projection query of jpql in spring-data-jpa". After the study of this article, I believe you have a deeper understanding of how to understand the projection query of jpql in spring-data-jpa. 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.