Spring Data JPA uses JPQL and native SQL to query

1. Query using JPQL statement

JPQL language (Java Persistence Query Language) is an intermediate and objective query language very similar to SQL, which will eventually be compiled into SQL language for different underlying databases, thus shielding the differences between different databases.

JPQL language is executed through Query interface, and Query interface encapsulates the related methods of executing database query. The query object can be obtained by calling Query, NamedQuery and NativeQuery methods of EntityManager, and then the related methods of Query interface can be called to execute the query operation.

JPQL is an object-oriented query language, and UPDATE and DELETE operations can be completed through custom JPQL. JPQL does not support the use of INSERT. For UPDATE or DELETE operations, you must decorate them with the annotation @ Modifying.

[Example] Query using JPQL language

package com.pjb.jpauserdemo.dao; 
import com.pjb.jpauserdemo.entity.UserInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
 
/**
 *  User information database access interface 
 *  Use JPQL Language 
 * @author pan_junbiao
 **/
@Repository
public interface UserJpqlDao extends JpaRepository<UserInfo,Integer>
{
    /**
     *  Query user information according to user name 
     */
    @Query("SELECT u FROM UserInfo u WHERE u.userName = ?1")
    public UserInfo getUserInfoByName(String name);
 
    /**
     *  Fuzzy query of user list based on user name 
     */
    @Query("SELECT u FROM UserInfo u WHERE u.userName like %:name%")
    public List<UserInfo> getUserListByName(String name);
 
    /**
     *  Modify user name 
     */
    @Modifying
    @Query("UPDATE UserInfo u SET u.userName = :name WHERE u.userId = :id")
    public int updateUserName(@Param("id")int userId, @Param("name")String userName);
}

2. Query using the native SQL statement

The @ Query annotation is also used when using native SQL queries. At this point, the nativeQuery parameter needs to be set to true.

[Example] Query using native SQL statement

package com.pjb.jpauserdemo.dao;
import com.pjb.jpauserdemo.entity.UserInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
 
/**
 *  User Information Database Access Interface Test Class 
 *  Use native SQL Language 
 * @author pan_junbiao
 **/
@Repository
public interface UserSqlDao extends JpaRepository<UserInfo,Integer>
{
    /**
     *  According to the user ID Obtain user information 
     */
    @Query(value = "SELECT * FROM tb_user WHERE user_id = :id",nativeQuery = true)
    public UserInfo getUserById(@Param("id")int userId);
 
    /**
     *  Fuzzy query of user list based on user name 
     */
    @Query(value = "SELECT * FROM tb_user WHERE user_name LIKE %:userName%",nativeQuery = true)
    public List<UserInfo> getUserListByName(@Param("userName")String userName);
 
    /**
     *  Modify user name 
     */
    @Modifying
    @Query(value = "UPDATE tb_user SET user_name = :name WHERE user_id = :id",nativeQuery = true)
    public int updateUserName(@Param("id")int userId, @Param("name")String userName);
}

As you can see, the @ Query and @ Modifying annotations are declarations that define personalized update actions.

Spring data jpa @ query Use native SQl, need to pay attention to the pit

Explain according to the code:

@Query(value = "select bill.id_ as id, bill.created_date as date, bill.no, lawyer_case .case_no as caseNo, " +
            "lawyer_case .case_name as caseName, customer.no as customerNo, customer.cn_name as customerName, " +
            "bill.total_expense_after_tax, bill.collected_money, bill.book_ticket_amount, bill.version " +
            "e1.name as creator, bill.status" +
            "from bill " +
            "left join lawyer_case on lawyer_case .case_no=bill.case_no " +
            "left join customer on customer.no=bill.customer_no " +
            "left join employee e1 on e1.id_=bill.creator " +
            "where IF (?1!='', customer_no=?1, 1=1) " +
            "and   IF (?2!='', case_no=?2, 1=1) " +
            "and   IF (?3!='', status=?3, 1=1) " +
            "and   IF (?4!='', creator'%',?4,'%')), 1=1) " +
            "and   create_by=?5 " +
            "ORDER BY ?#{#pageable} ",
            countQuery = "select count(*) " +
                    "from bill " +
                    "left join lawyer_case on lawyer_case .case_no=bill.case_no " +
                    "left join customer on customer.no=bill.customer_no " +
                    "left join employee e1 on e1.id_=bill.creator " +
                    "where IF (?1!='', customer_no=?1, 1=1) " +
                    "and   IF (?2!='', case_no=?2, 1=1) " +
                    "and   IF (?3!='', status=?3, 1=1) " +
                    "and   IF (?4!='', creator'%',?4,'%')), 1=1) " +
                    "and   create_by=?5 "+
                    "ORDER BY ?#{#pageable} ",
            nativeQuery = true)
    Page<Object[]> findAllBill(String customerNo, String caseNo, Integer status, String creator,
                               String createBy, Pageable pageable);

There are several methods to pay attention to:

1. From does not support renaming.

2. 1 page is returned < Object[] > The array only stores data, and there is no corresponding key. It can only be injected into DTO in turn according to the order of returning data.

3. To use paging, you need: "ORDER BY? # {# pageable}", which can directly pass in an pageable object and will be parsed automatically.

4. Pay attention to the format problem. Most of the time, there is no space when wrapping lines.

5. Carefully correspond to the table fields in the database. Most of the time, a field cannot be found because the field name is incorrectly written and does not correspond to the database.

6. This is to solve the problem of using micro-services, and a large amount of data needs to be called remotely, which will reduce the performance of programs.

7. When using Pageabel as a parameter, paging is carried out. At the beginning, I think it is still a feasible method, but I have to pay attention to it. When I need to sort, I can't add sort field. Will 1 directly report error left*.

8. According to the solution of 7, the data query of native SQL and countQuery are divided into two query methods. Get count, and then judge. If it is equal to 0, it will directly return to the empty set; On the contrary, the obtained data is taken. You need to do your own paging calculation and pass in the correct pageNumber and pageSize. Most systems sort in descending order according to modification time. Therefore, order by can be written to death. Then pageNumber and pageSize are dynamically passed in. The algorithm of pageNumber = (pageNumber-1) * pageSize, provided that PageNumber starts from 1, and if 0, pageNumber = pageNumber * PageSize; In this way, the data can be guaranteed to be correct.

/**
* pageInfos:  Data after conversion. 
* pageable Incoming pageable.
* totalPage:  No. 1 1 Article SQL Calculated return value. 
*  In this way, it can be unified 1 Returns various kinds of pageDTO . 
*/
private Page<T> convertForPage(List<T> pageInfos, Pageable pageable, Integer totalPage) {
        return new PageImpl<>(pageInfos, pageable, totalPage);
    }