[Spring] Exposed

단일 DataSource로 설정하기

dependencies

implementation("org.jetbrains.exposed:exposed-spring-boot-starter:0.49.0")
implementation("org.jetbrains.exposed:exposed-java-time:0.49.0")

Application

@SpringBootApplication(
    exclude = [
        ExposedAutoConfiguration::class, // 자동 설정을 제외하기 위해 추가
    ]
)
class Application

ExposedDatabaseInitializer

class ExposedDatabaseInitializer(
    private val platformTransactionManager: PlatformTransactionManager,
    private val basePackage: String,
) : ApplicationRunner {
    private val log = LoggerFactory.getLogger(javaClass)

    override fun run(args: ApplicationArguments) {
        val tables = findTables()
        log.info("create exposed tables. basePackage: {}, tables: {}", basePackage, tables.map { it.javaClass.simpleName })

        if (tables.isNotEmpty()) {
            transaction {
                SchemaUtils.drop(*tables.toTypedArray())
                SchemaUtils.create(*tables.toTypedArray())
            }
        }
    }

    private fun findTables(): List<Table> {
        val provider = ClassPathScanningCandidateComponentProvider(false)
        provider.addIncludeFilter(AssignableTypeFilter(Table::class.java))
        val components = provider.findCandidateComponents(basePackage)
        return components.map { Class.forName(it.beanClassName).kotlin.objectInstance as Table }
    }

    private fun transaction(block: () -> Unit) {
        val status = platformTransactionManager.getTransaction(DefaultTransactionDefinition())
        try {
            block()
            platformTransactionManager.commit(status)
        } catch (e: Exception) {
            platformTransactionManager.rollback(status)
            throw e
        }
    }
}

application.yml

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:13306/demo
    username: root
    password: 123456

  exposed:
    generate-ddl: true
    
logging:
  level:
    Exposed: debug

ExposedConfig

@Configuration
class ExposedConfig {
    @Bean
    fun transactionManager(dataSource: DataSource): PlatformTransactionManager {
        return SpringTransactionManager(dataSource)
    }

    @Bean
    @ConditionalOnProperty("spring.exposed.generate-ddl", havingValue = "true")
    fun databaseInitializer(transactionManager: PlatformTransactionManager): ExposedDatabaseInitializer {
        return ExposedDatabaseInitializer(transactionManager, "com.example.entity") // com.example.entity 경로에 Table 코드 추가
    }
}

DemoRepository

@Repository
@Transactional
class DemoRepository {
    // do crud
}

다중 DataSource로 설정하기

기본 설정

• 위의 "단일 DataSource로 설정하기"의 "dependencies" ~ "ExposedDatabaseInitializer" 과정까지 동일하게 설정

application.yml

spring:
  datasource:
    hikari:
      primary:
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:13306/primary
        username: root
        password: 123456

      secondary:
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:13306/secondary
        username: root
        password: 123456

  exposed:
    generate-ddl: true
    
logging:
  level:
    Exposed: debug

PrimaryDbConfig

@Configuration
class PrimaryDbConfig {
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.hikari.primary")
    fun primaryHikariConfig(): HikariConfig {
        return HikariConfig()
    }

    @Bean
    fun primaryDataSource(primaryHikariConfig: HikariConfig): DataSource {
        return HikariDataSource(primaryHikariConfig)
    }

    @Bean
    fun primaryTransactionManager(primaryDataSource: DataSource): PlatformTransactionManager {
        return SpringTransactionManager(primaryDataSource)
    }

    @Bean
    @ConditionalOnProperty("spring.exposed.generate-ddl", havingValue = "true")
    fun primaryExposedDatabaseInitializer(primaryTransactionManager: PlatformTransactionManager): ExposedDatabaseInitializer {
        return ExposedDatabaseInitializer(primaryTransactionManager, "com.example.entity.primary")
    }
}

SecondaryDbConfig

@Configuration
class SecondaryDbConfig {
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.hikari.secondary")
    fun secondaryHikariConfig(): HikariConfig {
        return HikariConfig()
    }

    @Bean
    fun secondaryDataSource(secondaryHikariConfig: HikariConfig): DataSource {
        return HikariDataSource(secondaryHikariConfig)
    }

    @Bean
    fun secondaryTransactionManager(secondaryDataSource: DataSource): PlatformTransactionManager {
        return SpringTransactionManager(secondaryDataSource)
    }

    @Bean
    @ConditionalOnProperty("spring.exposed.generate-ddl", havingValue = "true")
    fun secondaryExposedDatabaseInitializer(secondaryTransactionManager: PlatformTransactionManager): ExposedDatabaseInitializer {
        return ExposedDatabaseInitializer(secondaryTransactionManager, "com.example.entity.secondary")
    }
}

DemoRepository

@Repository
@Transactional("primaryTransactionManager")
class DemoRepository {
    // do crud
}

Table 코드 추가하기

Users

/*
    CREATE TABLE IF NOT EXISTS `User`
    (
        userId    BIGINT AUTO_INCREMENT PRIMARY KEY,
        userName    VARCHAR(50) NOT NULL,
        cityCode  CHAR(4)     NULL,
        createdAt DATETIME(6) NOT NULL
    )
 */
object Users : Table("User") {
    val userId = long("userId").autoIncrement()
    val userName = varchar("userName", length = 50)
    val cityCode = char("cityCode", length = 4).nullable()
    val createdAt = datetime("createdAt").clientDefault { LocalDateTime.now() }
    override val primaryKey = PrimaryKey(userId)
}

Cities

/*
    CREATE TABLE IF NOT EXISTS City
    (
        cityCode  CHAR(4) PRIMARY KEY,
        cityName  VARCHAR(50) NOT NULL,
        createdAt DATETIME(6) NOT NULL
    )
 */
object Cities : Table("City") {
    val cityCode = char("cityCode", length = 4)
    val cityName = varchar("cityName", length = 50)
    val createdAt = datetime("createdAt").clientDefault { LocalDateTime.now() }
    override val primaryKey = PrimaryKey(cityCode)
}

DSL 방식으로 CRUD하기

Insert

// INSERT INTO `User` (cityCode, createdAt, userName) VALUES ('AA01', '2024-04-11 08:19:56.921221', 'tyler.choi')
val userId = Users.insert {
    it[userName] = "tyler.choi"
    it[cityCode] = "AA01"
} get Users.userId

Update

// UPDATE `User` SET cityCode='AA02' WHERE `User`.userId = 1
val count = Users.update({ Users.userId eq 1 }) {
    it[cityCode] = "AA02"
}

Delete

// DELETE FROM `User` WHERE `User`.userName LIKE 'tyler.%'
val count = Users.deleteWhere { Users.userName like "tyler.%" }

Select - basic

/*
    SELECT `User`.userId, `User`.userName, `User`.cityCode, `User`.createdAt
    FROM `User`
    WHERE `User`.cityCode = 'AA01'
 */
val row = Users.selectAll().where { Users.cityCode eq "AA01" }.single()
log.info("userId: {}, userName: {}", row[Users.userId], row[Users.userName])

Select - groupBy

/*
    SELECT COUNT(`User`.userId)
    FROM `User`
    GROUP BY `User`.userName
 */
val userCount = Users.select(Users.userId.count())
    .groupBy(Users.userName)
    .single()[Users.userId.count()]

Select - join

/*
    SELECT `User`.userName, City.cityName
    FROM `User`
    INNER JOIN City ON (`User`.cityCode = City.cityCode)
*/
val list = Users.join(Cities, JoinType.INNER, additionalConstraint = { Users.cityCode eq Cities.cityCode })
    .select(Users.userName, Cities.cityName)
    .map {
        mapOf(
            "userName" to it[Users.userName],
            "cityName" to it[Cities.cityName],
        )
    }

Select - where

/*
    SELECT `User`.userId, `User`.userName, `User`.cityCode, `User`.createdAt
    FROM `User`
    WHERE (`User`.cityCode = 'AA01')
    AND (`User`.userName LIKE 'tyler.%')
*/
val users = Users.selectAll()
    .where {
        Users.cityCode.eq("AA01") and Users.userName.like("tyler.%")
    }
    .map {
        User(
            userId = it[Users.userId],
            userName = it[Users.userName],
            cityCode = it[Users.cityCode],
        )
    }

select - dynamic query

/*
    SELECT `User`.userId, `User`.userName, `User`.cityCode, `User`.createdAt
    FROM `User`
    WHERE `User`.userName LIKE '%tyler%'
*/
val cityCode: String? = null
val userName: String = "tyler"
val users = Users.selectAll()
    .apply {
        if (cityCode != null) {
            andWhere { Users.cityCode eq cityCode }
        }

        if (userName.isNotBlank()) {
            andWhere { Users.userName like "%${userName}%" }
        }
    }
    .map {
        User(
            userId = it[Users.userId],
            userName = it[Users.userName],
            cityCode = it[Users.cityCode],
        )
    }

참고

• https://github.com/JetBrains/Exposed
• https://jetbrains.github.io/Exposed/getting-started-with-exposed.html#getting-started
• https://azeke.tistory.com/entry/Spring-Jetbrains-Exposed-Multiple-Datasource