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

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

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

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

前言

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.resultListArray<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 看似很麻煩,但其實並不是很複雜。

發佈留言

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

You May Also Like
Photo by Charles Jackson on Unsplash
Read More

Springdoc-OpenAPI 教學

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