@Entity
@Table(name = "td_user")
public class TdUser extends BaseModel { 
    private static final long serialVersionUID = 8659266017517096998L;
    /**
     * id
     */
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(nullable = false, name = "id", length = 10)
    private Long id;
 
    /**
     * 用戶所屬平臺(tái)
     */
    @Column(nullable = false, name = "partner_id", length = 11)
    private Integer partnerId;
 
    /**
     * 用戶名
     */
    @Column(nullable = false, name = "username", length = 32, unique = true)
    private String username;
 
    /**
     * 用戶昵稱
     */
    @Column(name = "nickname", length = 64)
    private String nickname;
 
    /**
     * 密碼
     */
    @JsonIgnore
    @Column(nullable = false, name = "password", length = 16)
    private String password;
 
    /**
     * id
     *
     * getter  setter方法省略
     */

相對應(yīng)的建立操作接口

@Repository
public interface TdUserRepository extends JpaRepository<TdUser, Long>, JpaSpecificationExecutor<TdUser> {
}

分頁查詢的時(shí)候只要一句話

// Partner partner  外部傳入的分頁信息
Page<TdUser> allPage = TdUserRepository.findAll(pageable);

但是有時(shí)候可能不需要返回所有字段，只要返回一部分而已，經(jīng)過各種嘗試，有一種最簡單的方法

就是把想要返回的字段再構(gòu)建成一個(gè)實(shí)體，實(shí)體的屬性需要和數(shù)據(jù)庫字段進(jìn)行映射，然后單獨(dú)寫一個(gè)Repository就可以了

比如

@Entity
@Table(name = "td_user")
public class UserVO extends BaseModel { 
    private static final long serialVersionUID = 8659266017517096998L;
    /**
     * id
     */
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(nullable = false, name = "id", length = 10)
    private Long id; 
 
    /**
     * 用戶名
     */
    @Column(nullable = false, name = "username", length = 32, unique = true)
    private String username;  

    /**
     * id
     *
     * getter  setter方法省略
     */
@Repository
public interface UserRepository extends JpaRepository<User, Long>, JpaSpecificationExecutor<User> {
}

調(diào)用的時(shí)候

// Partner partner  外部傳入的分頁信息
Page<User> allPage = UserRepository.findAll(pageable);

我一開始也嘗試過寫sql，但是我發(fā)現(xiàn)返回?cái)?shù)據(jù)會(huì)變成Page<Object[]>，經(jīng)過json數(shù)列化以后數(shù)據(jù)會(huì)變成 [ ["1":"string"],...]這種樣子而不是key-value的形式

改了n遍后發(fā)現(xiàn)這樣是最簡單的。。。

SpringBoot JPA實(shí)現(xiàn)自定義語句分頁查詢

例：

1.JPA持久層 InvoiceRepository.java

@Repository
public interface InvoiceRepository extends JpaRepository<Invoice, Integer> { 
    @Query(
      value =
          "SELECT * from invoice_apply where company_id=?1 and IF (?2 is null ,1=1,status = ?2)",
      countQuery =
          "select count(*) from invoice_apply where company_id=?1 and IF (?2 is null ,1=1,status = ?2)",
      nativeQuery = true)
  Page<Map> findInvoice(int companyID, String status, Pageable pageable);
}

2.服務(wù)層

@Override
  public Map findInvoice(int companyID, String status, Integer page, Integer pageSize) {
    Double amount = companyFinanceRepository.findDCompanyFinance(companyID);
    //分頁查詢
    Pageable pageable = PageRequest.of(page, pageSize, Sort.Direction.ASC, "id");
    Page<Map> invoiceList = invoiceRepository.findInvoice(companyID, status, pageable);
    //重組返回結(jié)果
    Map map = new HashMap();
    map.put("invoice_amount", amount);
    map.put("list", invoiceList.getContent());//數(shù)據(jù)列表
    map.put("total", invoiceList.getTotalElements());//記錄總條數(shù)
    map.put("current_page", invoiceList.getNumber());//當(dāng)前頁碼
    return map;
  }

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: