Example of custom SQL statements using case/when statements to implement paging queries and classification sorting in JPA programming

1. Requirement background

Query the list of work orders initiated by me and invited by me. It requires paging query. The specific requirements for sorting are:

• Sort by status first, with unprocessed items at the front
• Then sort by handler, those who were invited are ranked first, and those who initiated themselves are ranked last.
• Last in descending order of modification time

There are three processing statuses:

• 0-not processed;
• 1-Agreed;
• 2-Rejected

DDL of invitation form:

CREATE TABLE `join_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `course_no` varchar(32) NOT NULL COMMENT 'course number',
  `created_date` datetime DEFAULT current_timestamp() COMMENT 'created time',
  `creator_id` bigint(20) NOT NULL COMMENT 'Creator ID',
  `deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Logical deletion status: 0-normal; 1-deleted',
  `modified_date` datetime DEFAULT current_timestamp() COMMENT 'update time',
  `receive_id` bigint(20) NOT NULL COMMENT 'ID of the person who accepted the invitation',
  `status` smallint(1) NOT NULL COMMENT 'Status, 0-unprocessed; 1-agreed; 2-rejected',
  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;


/**
 * Invitation record form
 *
 *@authorxxx
 */
@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 'course number'")
    private String courseNo;

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

    @Column(name = "receive_id", nullable = false, columnDefinition = "bigint COMMENT 'Accept the invitation ID'")
    private long receiveId;

    @Column(name = "status", nullable = false, columnDefinition = "smallint(1) COMMENT 'Status, 0-unprocessed; 1-agreed; 2-rejected'")
    private JoinLogStatus status;

    @CreatedDate
    @Column(name = "created_date", columnDefinition = "datetime DEFAULT NOW() COMMENT 'created time'")
    private Date createdDate;

    @LastModifiedDate
    @CreatedDate
    @Column(name = "modified_date", columnDefinition = "datetime DEFAULT NOW() COMMENT 'Update time'")
    private Date modifiedDate;

    @Column(name = "deleted", nullable = false, columnDefinition = "tinyint(1) default 0 COMMENT 'Tombstone status: 0-normal; 1-deleted'")
    private LogicDeleteEnum deleted;
}

2. Goals

The handwritten sql statement to achieve data sorting effect is as follows:

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;

3. Paging query

JPA already supports paging queries very well. See the class JpaSpecificationExecutor.java. You don’t need to calculate limit 0, 10 or limit 10, 20. The returned entity is also a paging class Page. Of course, you don’t need to write additional SQL statements for count() to find the total number of records.

Therefore, the incoming and outgoing parameters of our custom paging query are written in the same way.

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

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

The following is the specific implementation of paging in 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);
    }
}

• InterfaceJoinLogRepository.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;

/**
 *@authorxxx
 */
public interface JoinLogRepository extends JpaRepository<JoinLog, Long>,
        JpaSpecificationExecutor<JoinLog> {<!-- -->
    /**
     * Query the invitation list in paging.
     *
     * @param userId
     * @param pageable
     * @return
     */
    Page<JoinLog> queryByCreatorIdOrReceiveIdOrderByStatus(Long userId, Pageable pageable);
}

• Implement class 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;

/**
 *@authorxxx
 */
public interface JpaJoinLogRepository extends JoinLogRepository {<!-- -->

    /**
     * Check my pending and sent work orders.
     * <p>
     * Sort by status first, with unprocessed items at the front
     * Sort by handler again, with those who are not your own ranked first
     *Last in reverse order of modification time
     *</p>
     *
     * @param userId
     * @param pageable
     * @return
     */
    @Override
    @Query("sql statement")
    Page<JoinLog> queryByCreatorIdOrReceiveIdOrderByStatus(@Param("userId") Long userId,
                                                           @Param("pageable") Pageable pageable);
}

4. Complex sorting

To achieve the above complex sorting, you only need to convert the sql statement into a jql statement.

However, our order by condition does not support parentheses when splicing.

for example:
The top sql statement can also be written like this:

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;

However, when you try to convert to jql, an error will be reported and become the following error sql statement.

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;

Therefore, we abandon the if statement and replace it with a case when statement. The correct jql statement is as follows:

@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")

5. Summary

Paging and sorting are basic requirements for querying. This article uses a specific example to demonstrate the process from native SQL to hql. Finally, case/when is used to replace if to achieve classification sorting.