JPA编程中自定义SQL语句使用case/when语句实现分页查询和分类排序的示例

一、需求背景

查询我发起的以及被邀请的工单列表，要求分页查询，排序的具体要求是：

• 先按状态排序，未处理的排前面
• 再按处理人排序，被邀请的排前面，自己发起的排后面
• 最后按修改时间倒序

处理状态包括三种：

• 0-未处理；
• 1-已同意；
• 2-已拒绝

邀请表的ddl:

CREATE TABLE `join_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `course_no` varchar(32) NOT NULL COMMENT '课程编号',
  `created_date` datetime DEFAULT current_timestamp() COMMENT '创建时间',
  `creator_id` bigint(20) NOT NULL COMMENT '创建者ID',
  `deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除状态：0-正常;1-已删除',
  `modified_date` datetime DEFAULT current_timestamp() COMMENT '更新时间',
  `receive_id` bigint(20) NOT NULL COMMENT '接受邀请人ID',
  `status` smallint(1) NOT NULL COMMENT '状态，0-未处理；1-已同意；2-已拒绝',
  PRIMARY KEY (`id`),
  KEY `IDX_courseNo` (`course_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• Entity

import lombok.Getter;
import lombok.NoArgsConstructor;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

import javax.persistence.*;
import java.util.Date;


/**
 * 邀请记录表
 *
 * @author xxx
 */
@Getter
@NoArgsConstructor
@Table(name = "join_log", indexes = {
        @Index(name = "IDX_courseNo", columnList = "course_no")
})
@Entity
@EntityListeners(AuditingEntityListener.class)
public class JoinLog {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "course_no", nullable = false, columnDefinition = "varchar(32) COMMENT '课程编号'")
    private String courseNo;

    @Column(name = "creator_id", nullable = false, columnDefinition = "bigint COMMENT '创建者ID'")
    private long creatorId;

    @Column(name = "receive_id", nullable = false, columnDefinition = "bigint COMMENT '接受邀请人ID'")
    private long receiveId;

    @Column(name = "status", nullable = false, columnDefinition = "smallint(1) COMMENT '状态，0-未处理；1-已同意；2-已拒绝'")
    private JoinLogStatus status;

    @CreatedDate
    @Column(name = "created_date", columnDefinition = "datetime DEFAULT NOW() COMMENT '创建时间'")
    private Date createdDate;

    @LastModifiedDate
    @CreatedDate
    @Column(name = "modified_date", columnDefinition = "datetime DEFAULT NOW() COMMENT '更新时间'")
    private Date modifiedDate;

    @Column(name = "deleted", nullable = false, columnDefinition = "tinyint(1) default 0 COMMENT '逻辑删除状态：0-正常;1-已删除'")
    private LogicDeleteEnum deleted;
}

二、目标

手写sql语句，实现数据的排序效果见下：

select jl.*
from join_log jl  where (jl.creator_id = 1192660 or jl.receive_id  = 1192660) and jl.deleted = 0 
order by case when jl.status=0 then 0 else 1 end asc,
 case when jl.creator_id=1192660 then 0 else 1 end desc,  
 jl.modified_date desc 
 limit 0,10;

三、分页查询

jpa已很好地支持分页查询，见类JpaSpecificationExecutor.java，不需要你去计算limit 0,10还是limit 10,20，返回的实体也已是分页类Page。当然，你也不需要去额外编写count()求总记录数的sql语句。

所以，我们的自定义分页查询的出入参仿照着写。

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;

Page<T> query(@Param("pageable") Pageable pageable);

下面是jpa对分页的具体实现：

import lombok.RequiredArgsConstructor;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Component;

@Component
@RequiredArgsConstructor
public class JoinLogService {

    private final JoinLogRepository joinLogRepository;

    public Page<JoinLog> queryJoinLog(Long userId,
                                      int page,
                                      int size) {
        final Pageable pageable = PageRequest.of(page, size);

        return joinLogRepository.queryByCreatorIdOrReceiveIdOrderByStatus(userId, pageable);
    }
}

• 接口JoinLogRepository.java

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

import java.util.List;
import java.util.Set;

/**
 * @author xxx
 */
public interface JoinLogRepository extends JpaRepository<JoinLog, Long>,
        JpaSpecificationExecutor<JoinLog> {
    /**
     * 分页查询邀请列表.
     *
     * @param userId
     * @param pageable
     * @return
     */
    Page<JoinLog> queryByCreatorIdOrReceiveIdOrderByStatus(Long userId, Pageable pageable);
}

• 实现类JpaJoinLogRepository.java

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

/**
 * @author xxx
 */
public interface JpaJoinLogRepository extends JoinLogRepository {

    /**
     * 查询我待处理的和发送的工单.
     * <p>
     * 先按状态排序，未处理的排前面
     * 再按处理人排序，非自己的排前面
     * 最后按修改时间倒序
     * </p>
     *
     * @param userId
     * @param pageable
     * @return
     */
    @Override
    @Query("sql语句")
    Page<JoinLog> queryByCreatorIdOrReceiveIdOrderByStatus(@Param("userId") Long userId,
                                                           @Param("pageable") Pageable pageable);
}

四、复杂排序

要实现上面的复杂排序，本来只需要把sql语句转换为jql语句。

但是，我们的order by 条件在拼接的时候，不支持小括号。

举例说明：
最上面的sql语句也可以这么写：

select jl.*
from join_log jl  where (jl.creator_id = 1192660 or jl.receive_id  = 1192660) and jl.deleted = 0 
order by if(jl.status=0, 0, 1) asc,
 if(jl.creator_id=1192660, 0, 1) desc,  
 jl.modified_date desc 
 limit 0,10;

但是，在你试图转换为jql的时候，则会报错，变成了下面的错误sql语句。

select jl.*
from join_log jl  where (jl.creator_id = 1192660 or jl.receive_id  = 1192660) and jl.deleted = 0 
order by if() asc,
 if() desc,  
 jl.modified_date desc 
 limit 0,10;

所以，我们放弃if语句，换为case when语句。正确的jql语句见下：

@Query("select j from JoinLog j where (j.creatorId=:userId or j.receiveId=:userId) and j.deleted=0 " +
            " order by " +
            " case when j.status=0 then 0 else 1 end asc, " +
            " case when j.creatorId=:userId then 0 else 1 end desc," +
            " j.modifiedDate desc")

五、总结

分页和排序，作为查询的基本需求，本文以一个具体的示例，给你演示了从原生sql到hql的过程，最后使用case/when替换if实现了分类排序。