Overview

• This article outlines the basic setup for performing CRUD operations on MySQL in a project based on Kotlin + Spring Boot + JPA. It utilizes Spring Data JPA, Infobip Spring Data Querydsl, and AWS JDBC Driver for MySQL.

build.gradle.kts

• Add the following content to your project's /build.gradle.kts. (This is based on a project initially created with Gradle - Kotlin and only Spring Data JPA dependency added using Spring Initializr. Only additional content has been written here.)

val springBootVersion by extra { "3.2.3" }
val querydslVersion by extra { "5.1.0" }

buildscript {
    val kotlinVersion = "1.9.22"
    dependencies {
        classpath("gradle.plugin.com.ewerk.gradle.plugins:querydsl-plugin:1.0.10")
        classpath("org.jetbrains.kotlin:kotlin-gradle-plugin:$kotlinVersion")
        classpath("org.jetbrains.kotlin:kotlin-allopen:$kotlinVersion")
        classpath("org.jetbrains.kotlin:kotlin-noarg:$kotlinVersion")
    }
}

plugins {
    val kotlinVersion = "1.9.21"
    kotlin("plugin.jpa") version kotlinVersion
    kotlin("kapt") version kotlinVersion
    idea
}

allOpen {
    annotation("jakarta.persistence.Entity")
    annotation("jakarta.persistence.MappedSuperclass")
    annotation("jakarta.persistence.Embeddable")
}

dependencies {
    // For connecting to MySQL locally or remotely
    implementation("com.mysql:mysql-connector-j:8.2.0")
    // For connecting to Amazon Aurora MySQL remotely
    implementation("software.aws.rds:aws-mysql-jdbc:1.1.12")
    implementation("org.springframework.boot:spring-boot-starter-data-jpa:$springBootVersion")
    implementation("com.vladmihalcea:hibernate-types-60:2.21.1")
    implementation("io.hypersistence:hypersistence-utils-hibernate-60:3.7.0")
    implementation("com.infobip:infobip-spring-data-jpa-querydsl-boot-starter:9.0.2")
    kapt("com.querydsl:querydsl-apt:5.0.0:jakarta")
}

idea {
    module {
        val kaptMain = file("build/generated/source/kapt/main")
        sourceDirs.add(kaptMain)
        generatedSourceDirs.add(kaptMain)
    }
}

Environment Variables

• Write the operating system environment variables to be injected into the environment configuration bean as follows.

# When using MySQL locally or remotely
SPRING_DATASOURCE_DRIVER_CLASS_NAME=com.mysql.cj.jdbc.Driver
SPRING_DATASOURCE_URL_READ_WRITE=jdbc:mysql://{read-write-url}:{port}/{database}?useUnicode=true&characterEncoding=utf8&useLegacyDatetimeCode=false&serverTimezone=UTC
SPRING_DATASOURCE_URL_READ_ONLY=jdbc:mysql://{read-only-url}:{port}/{database}?useUnicode=true&characterEncoding=utf8&useLegacyDatetimeCode=false&serverTimezone=UTC

# When using Amazon Aurora MySQL remotely (can also be used for regular MySQL)
SPRING_DATASOURCE_DRIVER_CLASS_NAME=software.aws.rds.jdbc.mysql.Driver
SPRING_DATASOURCE_URL_READ_WRITE=jdbc:mysql:aws://{read-write-url}:{port}/{database}?useUnicode=true&characterEncoding=utf8&useLegacyDatetimeCode=false&serverTimezone=UTC
SPRING_DATASOURCE_URL_READ_ONLY=jdbc:mysql:aws://{read-only-url}:{port}/{database}?useUnicode=true&characterEncoding=utf8&useLegacyDatetimeCode=false&serverTimezone=UTC

SPRING_DATASOURCE_USERNAME={username}
SPRING_DATASOURCE_PASSWORD={password}
SPRING_DATASOURCE_HIKARI_MAXIMUM_POOL_SIZE=20
SPRING_DATASOURCE_HIKARI_PROFILE_SQL=false
SPRING_JPA_PROPERTIES_HIBERNATE_CONNECTION_PROVIDER_DISABLES_AUTOCOMMIT=true
SPRING_JPA_DATASOURCE_PLATFORM=org.hibernate.dialect.MySQL8Dialect

• It is highly recommended to use software.aws.rds.jdbc.mysql.Driver, a custom driver made by AWS, when using Amazon Aurora MySQL as a remote database. Using the standard com.mysql.cj.jdbc.Driver can lead to java.sql.SQLException: Running in read-only mode exceptions due to DNS resolution latency during a Failover, but the AWS custom driver completes the Failover in an average of 2 seconds, maximum 4 seconds, without the application noticing the outage. There is no reason not to use it in production environments for stability (it even works flawlessly with standard MySQL instances).

• Adding logger=software.aws.rds.jdbc.mysql.shading.com.mysql.cj.log.Slf4JLogger&profileSQL=true to the connection string of AWS JDBC Driver for MySQL enables you to check the executed JDBC queries in the console logs, which is recommended for development environments or debugging.

Creating read-write, read-only auto-routing DataSource Beans

• The setup of read-write, read-only automatically routing DataSource beans is crucial in production environments. With AWS Aurora MySQL, up to 15 replicas can distribute read-only queries, reducing the load on the primary database. The principle works as follows: classes or methods annotated with @Transactional(readOnly = false) will use the read-write connection pool. Conversely, those with @Transactional(readOnly = true) will use the read-only connection pool.

import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource
import org.springframework.beans.factory.annotation.Qualifier
import org.springframework.beans.factory.annotation.Value
import org.springframework.context.annotation.Bean
import org.springframework.context.annotation.Configuration
import org.springframework.context.annotation.Primary
import org.springframework.data.jpa.repository.config.EnableJpaRepositories
import org.springframework.transaction.annotation.EnableTransactionManagement
import javax.sql.DataSource

@Configuration
@EnableJpaRepositories(
    basePackages = ["com.jsonobject.example.repository"],
    repositoryFactoryBeanClass = ExtendedQuerydslJpaRepositoryFactoryBean::class
)
@EnableTransactionManagement
class DatabaseConfig(
    @Value("\${spring.datasource.driver-class-name}") private val DRIVER_CLASS_NAME: String,
    @Value("\${spring.datasource.url.read-write}") private val READ_WRITE_URL: String,
    @Value("\${spring.datasource.url.read-only}") private val READ_ONLY_URL: String,
    @Value("\${spring.datasource.username}") private val USERNAME: String,
    @Value("\${spring.datasource.password}") private val PASSWORD: String,
    @Value("\${spring.datasource.hikari.maximum-pool-size}") private val MAXIMUM_POOL_SIZE: Int,
    @Value("\${spring.datasource.hikari.profile-sql}") private val PROFILE_SQL: Boolean
) {
    @Bean(name = ["readWriteDataSource"])
    fun readWriteDataSource(): DataSource {

        return buildDataSource(
            DRIVER_CLASS_NAME,
            READ_WRITE_URL,
            USERNAME,
            PASSWORD,
            "read-write",
            MAXIMUM_POOL_SIZE,
            PROFILE_SQL
        )
    }

    @Bean(name = ["readOnlyDataSource"])
    fun readOnlyDataSource(): DataSource {

        return buildDataSource(
            DRIVER_CLASS_NAME,
            READ_ONLY_URL,
            USERNAME,
            PASSWORD,
            "read-only",
            MAXIMUM_POOL_SIZE,
            PROFILE_SQL
        )
    }

    @Primary
    @Bean(name = ["dataSource"])
    fun dataSource(
        @Qualifier("readWriteDataSource") readWriteDataSource: DataSource,
        @Qualifier("readOnlyDataSource") readOnlyDataSource: DataSource
    ): DataSource {

        val routingDataSource = TransactionRoutingDataSource()
        val dataSourceMap: MutableMap<Any, Any> = HashMap()
        dataSourceMap[DataSourceType.READ_WRITE] = readWriteDataSource
        dataSourceMap[DataSourceType.READ_ONLY] = readOnlyDataSource
        routingDataSource.setTargetDataSources(dataSourceMap)

        return routingDataSource
    }

    private fun buildDataSource(
        driverClassName: String,
        jdbcUrl: String,
        username: String,
        password: String,
        poolName: String,
        maximumPoolSize: Int,
        profileSql: Boolean
    ): DataSource {

        val config = HikariConfig()
        config.driverClassName = driverClassName
        config.jdbcUrl = jdbcUrl
        config.username = username
        config.password = password
        config.poolName = poolName
        config.maximumPoolSize = maximumPoolSize
        config.addDataSourceProperty("profileSql", profileSql)
        if (DRIVER_CLASS_NAME in arrayOf("com.mysql.cj.jdbc.Driver", "software.aws.rds.jdbc.mysql.Driver")) {
            config.connectionInitSql = "SET NAMES utf8mb4"
            config.addDataSourceProperty("cachePrepStmts", true)
            config.addDataSourceProperty("prepStmtCacheSize", 250)
            config.addDataSourceProperty("prepStmtCacheSqlLimit", 2048)
            config.addDataSourceProperty("useServerPrepStmts", true)
            config.addDataSourceProperty("useLocalSessionState", true)
            config.addDataSourceProperty("rewriteBatchedStatements", true)
            config.addDataSourceProperty("cacheResultSetMetadata", true)
            config.addDataSourceProperty("cacheServerConfiguration", true)
            config.addDataSourceProperty("elideSetAutoCommits", true)
            config.addDataSourceProperty("maintainTimeStats", false)
            config.addDataSourceProperty("rewriteBatchedStatements", true)
        }

        return HikariDataSource(config)
    }
}

// Creating a DataSource bean with automatic routing between read-write and read-only
class TransactionRoutingDataSource : AbstractRoutingDataSource() {

    override fun determineCurrentLookupKey(): DataSourceType {

        return if (TransactionSynchronizationManager.isCurrentTransactionReadOnly()) {
            DataSourceType.READ_ONLY
        } else {
            DataSourceType.READ_WRITE
        }
    }
}

enum class DataSourceType {
    READ_WRITE,
    READ_ONLY
}

Design JPA Entity

• This is an example of designing an entity, the starting point of JPA design.

import io.hypersistence.utils.hibernate.id.Tsid
import jakarta.persistence.Column
import jakarta.persistence.Entity
import jakarta.persistence.Id
import jakarta.persistence.Table
import org.hibernate.proxy.HibernateProxy
import java.io.Serializable

@Entity
@Table(name = "foo")
class Foo : Serializable {

    @Id
    @Tsid
    @Column
    var id: Long? = null

    @Column
    var bar: String? = null

    // equals()
    // hashCode()
    // toString()
}

• By specifying @Tsid on the Long type field that acts as PK, the application level generates a TSID value and stores it at the execution time of FooRepository.save(). It's space-efficient among similar types of UUID, consuming 8 bytes for Long type and 13 bytes for String type, making it suitable for a database primary key. The advantage over the more commonly used @GeneratedValue(strategy = GenerationType.IDENTITY) is the ability to perform Bulk Insert at the JPA level.

• All entities should implement equals(), hashCode(), and toString() methods after basic field design. If you're a paid subscriber of IntelliJ IDEA, you can easily autogenerate these using the JPA Buddy plugin.

Design JPA Repository

• Now it's time to design the JPA repository bean that will execute CRUD on the actual physical table.

import com.infobip.spring.data.jpa.ExtendedQuerydslJpaRepository

interface FooRepository : ExtendedQuerydslJpaRepository<Foo, Long>

• Using Infobip Spring Data Querydsl, one can easily create a repository bean with just one line by inheriting the ExtendedQuerydslJpaRepository<T, ID> interface. Refer to here for detailed usage.

• Alternatively, you can write as follows by inheriting QuerydslRepositorySupport provided by Spring Data JPA. This approach offers wider customization options.

import com.querydsl.jpa.impl.JPAQuery
import com.querydsl.jpa.impl.JPAQueryFactory
import jakarta.annotation.Resource
import jakarta.persistence.EntityManager
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.stereotype.Repository
import org.springframework.transaction.annotation.Isolation
import org.springframework.transaction.annotation.Transactional
import com.jsonobject.example.entity.QFoo.foo

@Repository
@Transactional(readOnly = true, isolation = Isolation.READ_COMMITTED)
class FooRepositorySupport(
    @Autowired
    @Resource(name = "jpaQueryFactory")
    private val query: JPAQueryFactory,
    private val em: EntityManager
) : QuerydslRepositorySupport(Foo::class.java) {

    fun fetchById(id: Long?): Foo? {

        id ?: return null

        return query
           .selectFrom(foo)
           .where(
               foo.id.eq(id)
           )
           .fetch()
           .firstOrNull()
    }

    @Transactional(readOnly = false)
    fun updateBarById(id: Long?, bar: String?): Long {

        id ?: return 0

        return query
            .update(foo)
            .set(foo.bar, bar)
            .where(foo.id.eq(id))
            .execute()
            // Reflecting changes to the entity in JPA's first-level cache
            .also { em.refresh(em.getReference(Foo::class.java, id)) }
    }
}

Reference Articles

• Hibernate with Kotlin - powered by Spring Boot

• Infobip Spring Data Querydsl

• Vlad Mihalcea - Read-write and read-only transaction routing with Spring

• Vlad Mihalcea - The best way to generate a TSID entity identifier with JPA and Hibernate

• Vlad Mihalcea - The awesome BaseJpaRepository from Hypersistence Utils

• Hibernate setAutoCommit 최적화를 통한 성능 튜닝