Spring Data JPA 使用原生 SQL 來自定義查詢

  • 4.5K views
  • 3 minute read
Photo by Jonathan Gallegos on Unsplash
Photo by Jonathan Gallegos on Unsplash
Spring Data JPA 簡單化存取資料庫的動作。開發者可以省去撰寫很多簡單的查詢 SQL。本章介紹如何用 Spring Data JPA 使用原生 SQL 來建立複雜的查詢 SQL。
Total
0
Shares
0
0
0
0
Share
0 people shared the story
0
0
0
0

Spring Data JPA 簡單化存取資料庫的動作。開發者可以省去撰寫很多簡單的查詢 SQL。本章介紹如何用 Spring Data JPA 使用原生 SQL 來建立複雜的查詢 SQL。

本章完整的程式碼可以在 下載。

Table of Contents
  1. 前言
  2. 建立 Employee/Employer Entities
  3. 建立查詢
    1. 實作 findAll()
  4. 實作 findAllDetailed()
    1. 建立 EmployeeRepository
  5. 建立 Service 和 Controller
  6. 結語

前言

Spring Data JPA 提供一些方式讓我們可以建立查詢的 methods。最簡單的一種方式是 query creation mechanism。這是藉由 method name 來推導出查詢 SQL。然而，當查詢 SQL 變複雜時，query creation mechanism 可能會無法滿足我們的需求。這時我們可以用 @Query 來撰寫自定義的 SQL。@Query 可以滿足大部分的複雜 SQL，如 JOIN。但是，如果當 SQL 需要根據一些條件來調整 WHERE 裡的條件，那 @Query 可能無法完成這樣的任務。接下來，我們將講解如何用 Spring Data JPA 來完成這樣的任務。

建立 Employee/Employer Entities

在本章的範例中，我們將使用兩個資料庫 entity，分別如下。

import java.util.*
import javax.persistence.Entity
import javax.persistence.GeneratedValue
import javax.persistence.GenerationType
import javax.persistence.Id

@Entity
data class Employee(
    val name: String,
    val employerId: Long,
    val createdAt: Date? = null,
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY) var employeeId: Long? = null,
)
import javax.persistence.Entity
import javax.persistence.GeneratedValue
import javax.persistence.GenerationType
import javax.persistence.Id

@Entity
data class Employer(
    val name: String?,
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY) var employerId: Long? = null,
)

建立查詢

我們想到建立一個 query method 如下。如果參數 name 不是 null 時，我們要在 WHERE 裡加上 name LIKE CONCAT('%', :name, '%')。像這樣的查詢就不好用 @Query 來實現。

fun findAll(name: String?, pageable: Pageable): Page<Employee>

首先，我們先建立 EmployeeRepositoryCustom interface，並且宣告兩個 methods。

一個是 findAll()，它會根據參數 name 來收尋所有的 Employee。如果 name 等於 null，則回傳所有的 Employee。

另一個是 findAllDetailed()，如同 findAll()，它會根據參數 name 來收尋所有的 Employee，並且回傳其 Employer。

import com.waynestalk.jpacustomsql.employer.Employer
import org.springframework.data.domain.Page
import org.springframework.data.domain.Pageable

interface EmployeeRepositoryCustom {
    fun findAll(name: String?, pageable: Pageable): Page<Employee>

    fun findAllDetailed(name: String?, pageable: Pageable): Page<Pair<Employee, Employer>>
}

實作 findAll()

接下來，建立 EmployeeRepositoryCustomImpl 並且實作 ExmployeeRepositoryCustom。以下的程式碼先放上 findAll() 的實作。

PersistenceContext 包含了一堆 entity instances 的集合。我們可以用它來建立 queries。詳細的解說，可以參考 JPA Persistence Context。

在 findAll() 中，只要流程就是拼湊出自定義的 SQL。最後，用 EntityManager 對這自定義的 SQL 建立一個 query，並且執行它。在建立 SQL 時，我們在 WHERE 中加入 :name 的參數。在 Query 建立出來後，我們要對 Query 設定 :name 對應的值，如 query.setParameter("name", name)。

此外，findAll() 還顯示如何在自定義的 query method 中，實作 Pageable。先執行 SELECT COUNT(1) 取得資料筆數後，再執行資料的查詢。最後，再用 PageImpl() 包裝資料和資料筆數。

import com.waynestalk.jpacustomsql.employer.Employer
import org.springframework.data.domain.Page
import org.springframework.data.domain.PageImpl
import org.springframework.data.domain.Pageable
import javax.persistence.EntityManager
import javax.persistence.PersistenceContext

class EmployeeRepositoryCustomImpl(
    @PersistenceContext private val entityManager: EntityManager,
) : EmployeeRepositoryCustom {
    override fun findAll(name: String?, pageable: Pageable): Page<Employee> {
        val sb = StringBuilder()
        sb.append("FROM Employee e")

        val where = mutableListOf<String>()
        val parameters = mutableMapOf<String, Any>()
        if (!name.isNullOrBlank()) {
            where.add("e.name LIKE CONCAT('%', :name, '%')")
            parameters["name"] = name
        }

        val countQuery = entityManager.createQuery("SELECT COUNT(1) $sb", Long::class.javaObjectType)
        parameters.forEach { countQuery.setParameter(it.key, it.value) }
        val count = countQuery.singleResult

        if (!pageable.sort.isEmpty) {
            val sorts = pageable.sort.map { "e.${it.property} ${if (it.isAscending) "ASC" else "DESC"}" }
            sb.append(" ORDER BY ${sorts.joinToString(", ")}")
        }
        val listQuery = entityManager.createQuery("SELECT e AS employee $sb")
        parameters.forEach { listQuery.setParameter(it.key, it.value) }
        listQuery.maxResults = pageable.pageSize
        listQuery.firstResult = pageable.offset.toInt()
        val list = listQuery.resultList.map {
            return@map it as Employee
        }

        return PageImpl(list, pageable, count)
    }
}

實作 findAllDetailed()

findAllDetailed() 和 findAll() 幾乎一樣。但是，我們加上 findAllDetailed() 主要是想要講解，當 SELECT 子句有多個 tables 時，那要如何處理查詢的結果。

當 SELECT 子句中只有一個 table 時，Query.resultList 中會是 entity 的 array。如在 findAll() 中，listQuery.resultList 是 Array<Employee>。

當 SELECT 子句中多餘一個 tables 時，Query.resultList 中會是 entity array 的 array。如在 findAllDetailed() 中的 SELECT 子句是 SELECT Employee, Employer，那 listQuery.resultList 就會是 Array<Array<Employee, Employer>>。Employee 和 Employer 的順序是根據在 SELECT 字句中的順序。

package com.waynestalk.jpacustomsql.employee

import com.waynestalk.jpacustomsql.employer.Employer
import org.springframework.data.domain.Page
import org.springframework.data.domain.PageImpl
import org.springframework.data.domain.Pageable
import javax.persistence.EntityManager
import javax.persistence.PersistenceContext

class EmployeeRepositoryCustomImpl(
    @PersistenceContext private val entityManager: EntityManager,
) : EmployeeRepositoryCustom {
    override fun findAllDetailed(name: String?, pageable: Pageable): Page<Pair<Employee, Employer>> {
        val sb = StringBuilder()
        sb.append("FROM Employee e")
        sb.append(" LEFT JOIN Employer er ON er.employerId = e.employerId")

        val where = mutableListOf<String>()
        val parameters = mutableMapOf<String, Any>()
        if (!name.isNullOrBlank()) {
            where.add("e.name LIKE CONCAT('%', :name, '%')")
            parameters["name"] = name
        }

        val countQuery = entityManager.createQuery("SELECT COUNT(1) $sb", Long::class.javaObjectType)
        parameters.forEach { countQuery.setParameter(it.key, it.value) }
        val count = countQuery.singleResult

        if (!pageable.sort.isEmpty) {
            val sorts = pageable.sort.map { "e.${it.property} ${if (it.isAscending) "ASC" else "DESC"}" }
            sb.append(" ORDER BY ${sorts.joinToString(", ")}")
        }
        val listQuery = entityManager.createQuery("SELECT e AS employee, er AS employer $sb")
        parameters.forEach { listQuery.setParameter(it.key, it.value) }
        listQuery.maxResults = pageable.pageSize
        listQuery.firstResult = pageable.offset.toInt()
        val list = listQuery.resultList.map {
            val array = it as Array<*>
            return@map Pair(array[0] as Employee, array[1] as Employer)
        }

        return PageImpl(list, pageable, count)
    }
}

建立 EmployeeRepository

建立 EmployeeRepository，除了繼承 JpaRespository，還要繼承 EmployeeRepositoryCustom。當 JPA 在產生 EmployeeRepository 的 instance 時，當它處理到 EmployeeRepositoryCustom 時，它收尋同名且有 Impl 後綴的 class。詳細說明可參考 Customizing Individual Repositories。

import org.springframework.data.jpa.repository.JpaRepository

interface EmployeeRepository: JpaRepository<Employee, Long>, EmployeeRepositoryCustom {
    fun findByEmployeeId(employeeId: Long): Employee?
}

建立 Service 和 Controller

最後，我們建立 Service 和 Controller 來完善整個範例。

import com.waynestalk.jpacustomsql.employee.EmployeeRepository
import org.springframework.data.domain.Pageable
import org.springframework.stereotype.Service

@Service
class EmployeeService(private val employeeRepository: EmployeeRepository) {
    fun findAll(name: String?, pageable: Pageable) = employeeRepository.findAll(name, pageable)

    fun findAllDetail(name: String?, pageable: Pageable) = employeeRepository.findAllDetailed(name, pageable)
}
import com.waynestalk.jpacustomsql.employee.Employee
import com.waynestalk.jpacustomsql.employer.Employer
import org.springframework.data.domain.Page
import org.springframework.data.domain.Pageable
import org.springframework.data.domain.Sort
import org.springframework.data.web.SortDefault
import org.springframework.web.bind.annotation.GetMapping
import org.springframework.web.bind.annotation.RequestMapping
import org.springframework.web.bind.annotation.RequestParam
import org.springframework.web.bind.annotation.RestController

@RestController
@RequestMapping("/employees")
class EmployeeController(private val employeeService: EmployeeService) {
    @GetMapping
    fun list(
        @RequestParam(required = false) name: String?,
        @SortDefault(sort = ["createdAt"], direction = Sort.Direction.DESC) pageable: Pageable,
    ): Page<Employee> = employeeService.findAll(name, pageable)

    @GetMapping("/detail")
    fun listDetail(
        @RequestParam(required = false) name: String?,
        @SortDefault(sort = ["createdAt"], direction = Sort.Direction.DESC) pageable: Pageable,
    ): Page<Pair<Employee, Employer>> = employeeService.findAllDetail(name, pageable)
}

結語

Spring Data JPA 讓我們可以省去撰寫簡單的查詢 SQL，可以加速我們開發專案的速度。雖然，建立自定義 SQL 的 query methods 看似很麻煩，但其實並不是很複雜。

Total
0
Shares
Share 0
Tweet 0
Share 0
Share 0
Related Tags

我是 Wayne，從事全端軟體開發有15年以上的經驗。

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

You May Also Like
Photo by Charles Jackson on Unsplash
Read More
  • 6.1K views
  • 4 minute read

Springdoc-OpenAPI 教學

Springdoc 是一個整合 OpenAPI Specification 和 Spring Boot 的套件。和 SpringFox 套件一樣，它產出 Swagger 文件。兩者不同在於，Springdoc 是用 Swagger 3，而 SpringFox 是用 Swagger 2。
Read More
0
0
0
0