# Essential Setup Guide to Kotlin + Spring Boot + JPA, and Querydsl

### 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.)
    

```kotlin
val springBootVersion by extra { "3.2.5" }
val querydslVersion by extra { "5.1.0" }

buildscript {
    val kotlinVersion = "1.9.23"
    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.23"
    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.4.0")
    // For connecting to Amazon Aurora MySQL remotely
    implementation("software.aws.rds:aws-mysql-jdbc:1.1.14")
    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.7")
    kapt("com.querydsl:querydsl-apt:$querydslVersion: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.
    

```bash
# 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.
    

```kotlin
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.
    

```kotlin
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.
    

```kotlin
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](https://github.com/infobip/infobip-spring-data-querydsl#jpa-module) for detailed usage.
    
* Alternatively, you can write as follows by inheriting `QuerydslRepositorySupport` provided by **Spring Data JPA**. This approach offers wider customization options.
    

```kotlin
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](https://kotlinexpertise.com/hibernate-with-kotlin-spring-boot/)
    
* [Infobip Spring Data Querydsl](https://github.com/infobip/infobip-spring-data-querydsl)
    
* [Vlad Mihalcea - Read-write and read-only transaction routing with Spring](https://vladmihalcea.com/read-write-read-only-transaction-routing-spring/)
    
* [Vlad Mihalcea - The best way to generate a TSID entity identifier with JPA and Hibernate](https://vladmihalcea.com/tsid-identifier-jpa-hibernate/)
    
* [Vlad Mihalcea - The awesome BaseJpaRepository from Hypersistence Utils](https://vladmihalcea.com/basejparepository-hypersistence-utils/)
    
* [Hibernate setAutoCommit 최적화를 통한 성능 튜닝](https://pkgonan.github.io/2019/01/hibrnate-autocommit-tuning)