[Project]Kotlin+SpringBoot+Querydslを使用してアドレスAPIを検索

きっかけ

Stack

プロセス

1.Querydslバインド

build.gradle.kts

import org.jetbrains.kotlin.gradle.tasks.KotlinCompile

plugins {
	id("org.springframework.boot") version "2.6.6"
	id("io.spring.dependency-management") version "1.0.11.RELEASE"
	kotlin("jvm") version "1.6.10"
	kotlin("plugin.spring") version "1.6.10"
	kotlin("plugin.jpa") version "1.6.10"
	kotlin("kapt")  version "1.6.10" // kapt 등록

}

group = "com.example"
version = "0.0.1-SNAPSHOT"
java.sourceCompatibility = JavaVersion.VERSION_11

// Q파일 생성 경로
sourceSets["main"].withConvention(org.jetbrains.kotlin.gradle.plugin.KotlinSourceSet::class) {
	kotlin.srcDir("$buildDir/generated/source/kapt/main")
}

repositories {
	mavenCentral()
}

dependencies {
	implementation("org.springframework.boot:spring-boot-starter-data-jpa")
	implementation("org.springframework.boot:spring-boot-starter-web")
	implementation("com.fasterxml.jackson.module:jackson-module-kotlin")
	implementation("org.jetbrains.kotlin:kotlin-reflect")
	implementation("org.jetbrains.kotlin:kotlin-stdlib-jdk8")
	runtimeOnly("com.h2database:h2")
	testImplementation("org.springframework.boot:spring-boot-starter-test")

	runtimeOnly("mysql:mysql-connector-java")

	// querydsl
	api("com.querydsl:querydsl-jpa:")
	kapt(group = "com.querydsl", name = "querydsl-apt", classifier = "jpa") 
}

tasks.withType<KotlinCompile> {
	kotlinOptions {
		freeCompilerArgs = listOf("-Xjsr305=strict")
		jvmTarget = "11"
	}
}

tasks.withType<Test> {
	useJUnitPlatform()
}

application.yaml

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/[dbname]?serverTimezone=UTC&characterEncoding=UTF-8
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: [userid]
    password: [userpassword]

  jpa:
    hibernate:
#      ddl-auto: create
    properties:
      hibernate:
        dialect: com.example.practice_qdsl.Dialect.CustomDialect # 커스텀한 Dialect 경로
#        show_sql: true
        format_sql: true

logging:
  level:
    org:
      hibernate:
        SQL: debug
        type:
          descriptor:
            sql: trace

querydslConfig

package com.example.practice_qdsl.Configuration
import com.querydsl.jpa.impl.JPAQueryFactory
import org.springframework.context.annotation.Bean
import org.springframework.context.annotation.Configuration
import javax.persistence.EntityManager
import javax.persistence.PersistenceContext

@Configuration
class QuerydslConfig(
  @PersistenceContext
  private val entityManager: EntityManager
) {

  @Bean
  fun jpaQueryFactory(): JPAQueryFactory {
    return JPAQueryFactory(this.entityManager)
  }
}

2.JPA Dialectを使用してマッチング用の機能を登録する

だからJPAで何らかのDBMSを使う方法がDialectを設定する方法になります.JPAにDialectを構成できる抽象的なDialectクラスを提供し、設定された方言で各DBMSに対応するインプリメンテーションを提供します.

package com.example.practice_qdsl.Dialect

import org.hibernate.dialect.MySQL57Dialect
import org.hibernate.dialect.function.SQLFunctionTemplate
import org.hibernate.type.StandardBasicTypes


class CustomDialect: MySQL57Dialect() {

  init{
    registerFunction("match", SQLFunctionTemplate(StandardBasicTypes.INTEGER, "match(?1) against (?2 in boolean mode)"))
  }
}

3.エンティティとレポート

Entity

package com.example.practice_qdsl.Entity

import javax.persistence.*

@Entity(name = "ADDRESS_INFO")
data class AddressInfo(
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "id")
  var id: Long = 0,

  @Column(name = "address")
  val address: String,
)

AddressInfoRepositoryCustom

package com.example.practice_qdsl.Repository

import com.example.practice_qdsl.Entity.AddressInfo

interface AddressInfoRepositoryCustom {
  fun getAddressList(keyword : String) :List<AddressInfo>
  fun getLists():List<AddressInfo>
}

AddressInfoRepositoryCustomImpl

package com.example.practice_qdsl.Repository.Impl

import com.example.practice_qdsl.Entity.AddressInfo
import com.example.practice_qdsl.Entity.QAddressInfo
import com.example.practice_qdsl.Repository.AddressInfoRepositoryCustom
import com.querydsl.core.types.dsl.Expressions
import com.querydsl.core.types.dsl.NumberTemplate
import com.querydsl.jpa.impl.JPAQueryFactory
import org.springframework.stereotype.Repository



@Repository
class AddressInfoRepositoryCustomImpl(
  val jpaQueryFactory: JPAQueryFactory,
) : AddressInfoRepositoryCustom {

  override fun getAddressList(keyword: String): List<AddressInfo> {
    val booleanTemplate: NumberTemplate<*> = Expressions.numberTemplate(Integer::class.java,
      "function('match',{0},{1})",
        QAddressInfo.addressInfo.address, keyword)


    return jpaQueryFactory.select(QAddressInfo.addressInfo).from(QAddressInfo.addressInfo).where(booleanTemplate.gt(0)).fetch()
  }

  override fun getLists(): List<AddressInfo> {
    return jpaQueryFactory.selectFrom(QAddressInfo.addressInfo).fetch()
  }
}

4.性能比較

1) %Like%

2) match-against

注意:https://idlecomputer.tistory.com/337