Play the projection query to JPQL in Spring-data-JPA

A projected query is one that retrieves only part of a table’s fields. SELECT * FROM… Retrieve all column data. For example, when retrieving the user 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 also avoids exposing more data to clients.

Here are some of JPQL’s caveats

• You’re manipulating @entity, not a table, and you’re manipulating object properties, not table fields
• The default Entity name is the class name capitalized and can be changed by modifying the annotation of the @entity attribute
• The use of ‘ ‘symbols is not supported. Semicolons at the end are not supported.
• Alias using the AS keyword is supported

Prepare a demo entity class

import javax.persistence.*;

import java.io.Serializable;
import java.time.LocalDateTime;

/**
 * 用户
 */
@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 '", nullable = false)
	private String account;
	
	/ / nickname
	@Column(columnDefinition = "VARCHAR(20) COMMENT 'iD '")
	private String name;

	/ / avatar
	@Column(columnDefinition = "VARCHAR(255) COMMENT 'avatar '")
	private String avatar;

	// Create time
	@Column(name = "created_date", columnDefinition = "Timestamp DEFAULT CURRENT_TIMESTAMP COMMENT 'create time '", nullable = false)
	private LocalDateTime createdDate;

	// Change the last time
	@Column(name = "last_modified_date", columnDefinition = "Timestamp NULL DEFAULT NULL COMMENT 'last modified timestamp '")
	private LocalDateTime lastModifiedDate;

	// Record the status
	@Column(columnDefinition = "TINYINT(1) unsigned COMMENT 'whether enabled '", nullable = false)
	private Boolean enabled;
}

Copy the code

Retrieves single-row, single-column data

Single row, single column, that’s easier. The return value of the Repository interface retrieval method defines whatever column data type is returned.

Retrieve the account field based on the user ID

public interface UserRepository extends JpaRepository<User.Integer>, JpaSpecificationExecutor <User> {
	
	/** * 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);
}

Copy the code

The test code

@Autowired
private UserRepository userRepository;

@Test
@Transactional(readOnly = true)
public void test (a) {
	String account = userRepository.accountById(1);
	LOGGER.info("account={}", account);
}
Copy the code

Perform log

You can see in the log that the SQL executed meets the requirements, retrieving only one column and returning 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
Copy the code

Retrieves single-row, multi-column data

Encapsulate the result set with the constructor of the custom object

Customize an object that encapsulates a result set

You inherit directly from the User class, and you need a constructor to define the columns to be retrieved by projection

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); }}Copy the code

Based on the retrieval of the user’s basic information: account, nickname, creation time, encapsulate the result set as a UserDTO object

Using the NEW keyword in JPQL to specify the full path of a custom object, and specifying the column to retrieve in the constructor, has both SQL syntax and object-oriented thinking.

/** * Encapsulates the result set as the UserDTO object * based on the user's basic information: account, nickname, creation time@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);
Copy the code

test

@Autowired
private UserRepository userRepository;

@Test
@Transactional(readOnly = true)
public void test (a) {
	UserDTO userDTO = userRepository.querySimpleInfoById(1);
	LOGGER.info("user={}", userDTO);
}
Copy the code

Perform 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]
Copy the code

Encapsulate the result set with Oject[]

Encapsulating the result set as a collection of arrays does not require redefining objects, but the disadvantage is that you need to remember what the data under each array subscript represents.

According to the retrieval of the user’s basic information: account, nickname, creation time, encapsulation result set as an Object array

/** * Retrieve the user's basic information: account, nickname, creation time, encapsulation result set as "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);
Copy the code

Note that the return value is defined as Object, rather than Object[] as expected, and JPA encapsulates the result set as a two-dimensional array suitable for multiple rows and columns.

This is an example of a search by ID, which can only be single row, multiple columns. So I’ll call it Object, and I’ll just do it myself.

test

@Autowired
private UserRepository userRepository;

@Test
@Transactional(readOnly = true)
public void test (a) {
	// cast to Object[]
	Object[] results = (Object[]) userRepository.querySimpleInfoById(1);
	for (Object result : results) {
		LOGGER.info("result={}", result);	// Iterate over the even result of the number group}}Copy the code

Perform 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=KevinBlandy
i.s.jpa.test.JpaApplicationTest          : result=Vin
i.s.jpa.test.JpaApplicationTest          : result=2020-07-16T10:39:02
Copy the code

Encapsulate the result set with Map<String, Object>

The advantage of using a Map as a result set is that you do not need to redefine objects. And you can find your own field according to the key. I think the easiest way to do this is to use Spring’s BeanUtils to convert maps into objects.

According to the retrieved user’s basic information: account, nickname, creation time, encapsulation result set as “Map<String, Object>”

/** * Retrieve the user's basic information: account, nickname, creation time, encapsulate result set "Map<String, Object>" *@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<String, Object> querySimpleInfoById(@Param("id") Integer id);
Copy the code

Alias the retrieved column with AS AS the name of the key. If the AS alias is not used, key = null for single row and key = serial number for multiple rows (starting from 0).

{
	"0":"Vin"."1": "KevinBlandy}"Copy the code

The test code

@Autowired
private UserRepository userRepository;

@Test
@Transactional(readOnly = true)
public void test (a) {
	Map<String, Object> result = userRepository.querySimpleInfoById(1);
	LOGGER.info("result={}", result);	
}
Copy the code

Perform 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}
Copy the code

Retrieve multiple columns N rows

For record encapsulation with multiple lines, this is simple. Based on the above, change the return value result to array or Collection subclass.

• Separate multiple columnsList<String>
• Multi-line columnList<Map<String, Object>>
• Multi-line queueList<Object[]>
• Multi-line queueList<UseDTO>
• Multi-line queueObject[]Each Object element is an array…

Original text: springboot. IO/topic / 223 / t…