Spring Data JPA 簡單化存取資料庫的動作。開發者可以省去撰寫很多簡單的查詢 SQL。本章介紹如何用 Spring Data JPA 使用原生 SQL 來建立複雜的查詢 SQL。
Table of Contents
前言
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 看似很麻煩,但其實並不是很複雜。