Spring Data JPA Custom Queries with Native SQL

Spring Data JPA simplifies the operation of accessing the database. Developers can save writing a lot of simple query SQL. This article will introduces how to use Spring Data JPA with a native SQL to build a complex query SQL.

The complete code can be found in .

Preface

Spring Data JPA provides some ways to build query methods. The easiest way is query creation mechanism. This is to deduce the query SQL by the method name. However, when a query SQL becomes complicated, the query creation mechanism may not be able to meet our needs. At this moment, we can use @Query to write a custom SQL. @Query can satisfy most complex SQL, such as JOIN. However, if a SQL needs to adjust the conditions in WHERE according to some conditions, @Query may not be able to complete such tasks. Next, we will explain how to use Spring Data JPA to accomplish such a task.

Creating Employee/Employer Entities

In the example in this chapter, we will use two database entities, as follows.

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,
)

Building Queries

We want to a query method as follows. If the argument name is not null, we want to add name LIKE CONCAT('%', :name, '%') in the WHEREclause. Queries like this are not easy to implement with @Query.

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

First, we first create an EmployeeRepositoryCustom interface and declare two methods.

One is findAll() that it will be based on parameters name to collect all Employee. If name is equal to null, then return all Employee.

The other findAllDetailed(), like findAll(), it will be based on the parameters name to find all find Employee and their 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>>
}

Implementing findAll()

Now, create EmployeeRepositoryCustomImpl and implement ExmployeeRepositoryCustom. The following code first puts the implementation of findAll().

PersistenceContext contains a set of entity instances. We can use it to create queries. For detailed explanation, please refer to JPA Persistence Context.

In findAll(), as long as the process is to create a SQL string. Finally, use EntityManager to build a query with this custom SQL, and execute it. When establishing SQL, we add :name parameter into the WHERE. After having established a Query, we need to set a corresponding value for :name in the Query, like query.setParameter("name", name).

In addition, findAll() also shows how to implement Pageable in a custom query method. First execute SELECT COUNT(1) to obtain the number of results, and then execute the query to obtain the data results. Finally, use PageImpl() to pack the data and the number of data.

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)
    }
}

Implementing findAllDetailed()

findAllDetailed() and findAll() are almost the same. However, we add findAllDetailed()mainly want to explain, when the SELECT clause have multiple tables, how to deal with the results of the query.

When there is only one table in the SELECT clause, Query.resultList will be an array of the entity. As findAll() in listQuery.resultList is Array<Employee>.

When there is more than one table in the SELECT clause, the Query.resultList will be an array of entity arrays. As in the findAllDetailed(), SELECT clause is SELECT Employee, Employer, listQuery.resultList will be Array<Array<Employee, Employer>>. The order of Employee and Employer is based on the order in the SELECT clause.

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)
    }
}

Creating EmployeeRepository

Create EmployeeRepository, and not only inherit JpaRespository, but also inherit EmployeeRepositoryCustom. When JPA generates an instance of EmployeeRepository, when it processes EmployeeRepositoryCustom, it searches for a class with the same name with an Impl suffix. For detailed instructions, please refer to Customizing Individual Repositories.

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

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

Creating Service and Controller

Finally, we create Service and Controller to complete the entire example.

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)
}

Conclusion

Spring Data JPA save us writing simple query SQL, which can accelerate the speed of development. Although it may seem cumbersome to create custom SQL query methods, it is actually not very complicated.