[Querydsl] postgres array column intersect, contains 검색하기

최대한·2023년 6월 20일

Prerequisite

kotlin: 1.82
jdk: 17
Spring boot: 3.2
hibernate-core: 6.2.2

Domain

Student.kt

package io.vitamax.graphqlfunction.domain

import io.hypersistence.utils.hibernate.type.array.ListArrayType
import jakarta.persistence.Column
import jakarta.persistence.Entity
import jakarta.persistence.Id
import jakarta.persistence.Table
import org.hibernate.annotations.Type
import java.util.*

@Entity
@Table(name = "student")
class Student (

    @Id
    val id: UUID = UUID.randomUUID(),

    @Type(ListArrayType::class)
    @Column(columnDefinition = "text[]")
    val subjectNames: List<String> = emptyList(),

)

StudentRepository

package io.vitamax.graphqlfunction.domain

import com.querydsl.core.types.dsl.Expressions
import io.vitamax.graphqlfunction.domain.QStudent.student
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.support.QuerydslRepositorySupport
import java.util.*


interface StudentRepository:
    JpaRepository<Student, UUID>,
    StudentCustomRepository

interface StudentCustomRepository {
    fun findSubjectsIntersect(subjectNames: Array<String>): List<Student>
    fun findBySubjectNamesContaining(subjectName: String): List<Student>
}

class StudentRepositoryImpl:
    QuerydslRepositorySupport(Student::class.java),
    StudentCustomRepository {

    override fun findSubjectsIntersect(subjectNames: Array<String>): List<Student> =
        from(student)
            .where(
                Expressions.booleanTemplate(
                    "ARRAYOVERLAP({0}, {1}) = true",
                    student.subjectNames,
                    subjectNames,
                )
            )
            .fetch()

    override fun findBySubjectNamesContaining(subjectName: String): List<Student> =
        from(student)
            .where(
                Expressions.booleanTemplate(
                    "ARRAY_POSITION({0}, {1}) IS NOT NULL",
                    student.subjectNames,
                    subjectName,
                )
            )
            .fetch()
}

Test

package io.vitamax.graphqlfunction.domain

import io.kotest.matchers.shouldBe
import org.junit.jupiter.api.Test
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.boot.test.context.SpringBootTest
import org.springframework.transaction.annotation.Transactional

@SpringBootTest
internal class StudentRepositoryTest {

    @Autowired
    private lateinit var repo: StudentRepository

    @Test
    @Transactional
    fun `should fetch when any subject name overlaps`() {
        val science = "SCIENCE"
        val math = "MATH"
        val pe = "PE"

        val testSet = listOf(
            listOf(science, math, pe),
            listOf(math, pe),
            listOf(science, pe),
        )
        testSet.forEach { subjectNames ->
            repo.save(Student(
                subjectNames = subjectNames,
            ))
        }
        val studentsTakingPEClass = repo.findSubjectsIntersect(arrayOf(pe))
        studentsTakingPEClass.size shouldBe testSet.size
    }

    @Test
    @Transactional
    fun `should fetch when subjectName contains`() {
        val science = "SCIENCE"
        val math = "MATH"
        val pe = "PE"

        val mathGroupSize = 3
        (1..mathGroupSize).forEach { _ ->
            repo.save(Student(
                subjectNames = listOf(math, science, pe),
            ))
        }

        // another group without math
        repeat(3) {
            repo.save(Student(
                subjectNames = listOf(science, pe),
            ))
        }
        val studentsTakingMathClass = repo.findBySubjectNamesContaining(math)
        studentsTakingMathClass.size shouldBe mathGroupSize
    }
}