JOOQ Database Patterns
Repository Structure
@Repository
class EnvironmentRepository(
private val dsl: DSLContext
) {
fun findById(id: UUID): Environment? =
dsl.selectFrom(ENVIRONMENT)
.where(ENVIRONMENT.ID.eq(id))
.fetchOne()
?.toEntity()
fun findByName(name: String): Environment? =
dsl.selectFrom(ENVIRONMENT)
.where(ENVIRONMENT.NAME.eq(name))
.fetchOne()
?.toEntity()
fun findAll(): List<Environment> =
dsl.selectFrom(ENVIRONMENT)
.orderBy(ENVIRONMENT.CREATED_AT.desc())
.fetch()
.map { it.toEntity() }
fun save(entity: Environment): Environment =
dsl.insertInto(ENVIRONMENT)
.set(ENVIRONMENT.ID, entity.id)
.set(ENVIRONMENT.NAME, entity.name)
.set(ENVIRONMENT.STATUS, entity.status.name)
.set(ENVIRONMENT.CREATED_AT, entity.createdAt)
.returning()
.fetchOne()!!
.toEntity()
fun update(entity: Environment): Environment =
dsl.update(ENVIRONMENT)
.set(ENVIRONMENT.STATUS, entity.status.name)
.set(ENVIRONMENT.UPDATED_AT, Instant.now())
.where(ENVIRONMENT.ID.eq(entity.id))
.returning()
.fetchOne()!!
.toEntity()
fun delete(id: UUID): Boolean =
dsl.deleteFrom(ENVIRONMENT)
.where(ENVIRONMENT.ID.eq(id))
.execute() > 0
}
Record to Entity Mapping
// Extension function on generated Record
private fun EnvironmentRecord.toEntity() = Environment(
id = id,
name = name,
status = EnvironmentStatus.valueOf(status),
createdAt = createdAt,
updatedAt = updatedAt
)
// For complex mappings with joins
private fun Record.toEnvironmentWithTags() = Environment(
id = get(ENVIRONMENT.ID),
name = get(ENVIRONMENT.NAME),
status = EnvironmentStatus.valueOf(get(ENVIRONMENT.STATUS)),
createdAt = get(ENVIRONMENT.CREATED_AT),
updatedAt = get(ENVIRONMENT.UPDATED_AT),
tags = get("tags", List::class.java) as List<String>
)
Complex Queries
Joins
fun findWithOwner(id: UUID): EnvironmentWithOwner? =
dsl.select(
ENVIRONMENT.asterisk(),
USER.NAME.`as`("owner_name"),
USER.EMAIL.`as`("owner_email")
)
.from(ENVIRONMENT)
.join(USER).on(ENVIRONMENT.OWNER_ID.eq(USER.ID))
.where(ENVIRONMENT.ID.eq(id))
.fetchOne()
?.let { record ->
EnvironmentWithOwner(
environment = record.into(ENVIRONMENT).toEntity(),
ownerName = record.get("owner_name", String::class.java),
ownerEmail = record.get("owner_email", String::class.java)
)
}
Filtering and Pagination
fun findByFilters(
status: EnvironmentStatus?,
search: String?,
page: Int,
size: Int
): Page<Environment> {
val conditions = mutableListOf<Condition>()
status?.let { conditions.add(ENVIRONMENT.STATUS.eq(it.name)) }
search?.let { conditions.add(ENVIRONMENT.NAME.likeIgnoreCase("%$it%")) }
val baseQuery = dsl.selectFrom(ENVIRONMENT)
.where(conditions)
val total = dsl.selectCount()
.from(ENVIRONMENT)
.where(conditions)
.fetchOne(0, Long::class.java) ?: 0L
val items = baseQuery
.orderBy(ENVIRONMENT.CREATED_AT.desc())
.limit(size)
.offset(page * size)
.fetch()
.map { it.toEntity() }
return Page(items, total, page, size)
}
Aggregations
fun countByStatus(): Map<EnvironmentStatus, Long> =
dsl.select(ENVIRONMENT.STATUS, DSL.count())
.from(ENVIRONMENT)
.groupBy(ENVIRONMENT.STATUS)
.fetch()
.associate { record ->
EnvironmentStatus.valueOf(record.value1()) to record.value2().toLong()
}
Batch Operations
fun saveAll(entities: List<Environment>): List<Environment> {
if (entities.isEmpty()) return emptyList()
val records = entities.map { entity ->
dsl.newRecord(ENVIRONMENT).apply {
id = entity.id
name = entity.name
status = entity.status.name
createdAt = entity.createdAt
}
}
dsl.batchInsert(records).execute()
return entities
}
fun updateStatuses(ids: List<UUID>, status: EnvironmentStatus): Int =
dsl.update(ENVIRONMENT)
.set(ENVIRONMENT.STATUS, status.name)
.set(ENVIRONMENT.UPDATED_AT, Instant.now())
.where(ENVIRONMENT.ID.`in`(ids))
.execute()
JSON Fields (PostgreSQL)
// For JSONB columns
fun findByMetadata(key: String, value: String): List<Environment> =
dsl.selectFrom(ENVIRONMENT)
.where(
DSL.field("metadata->>'{0}'", String::class.java, key)
.eq(value)
)
.fetch()
.map { it.toEntity() }
// Store JSON
fun updateMetadata(id: UUID, metadata: Map<String, Any>): Environment =
dsl.update(ENVIRONMENT)
.set(ENVIRONMENT.METADATA, JSONB.jsonb(objectMapper.writeValueAsString(metadata)))
.where(ENVIRONMENT.ID.eq(id))
.returning()
.fetchOne()!!
.toEntity()
Upsert (ON CONFLICT)
fun upsert(entity: Environment): Environment =
dsl.insertInto(ENVIRONMENT)
.set(ENVIRONMENT.ID, entity.id)
.set(ENVIRONMENT.NAME, entity.name)
.set(ENVIRONMENT.STATUS, entity.status.name)
.set(ENVIRONMENT.CREATED_AT, entity.createdAt)
.onConflict(ENVIRONMENT.NAME)
.doUpdate()
.set(ENVIRONMENT.STATUS, entity.status.name)
.set(ENVIRONMENT.UPDATED_AT, Instant.now())
.returning()
.fetchOne()!!
.toEntity()
Transaction Handling
// In service layer - explicit transaction control
@Service
class EnvironmentService(
private val dsl: DSLContext,
private val repository: EnvironmentRepository
) {
fun createWithResources(request: CreateRequest): Environment =
dsl.transactionResult { config ->
val txDsl = DSL.using(config)
// Create environment
val env = repository.save(request.toEnvironment())
// Create related resources in same transaction
request.resources.forEach { resource ->
txDsl.insertInto(RESOURCE)
.set(RESOURCE.ENVIRONMENT_ID, env.id)
.set(RESOURCE.TYPE, resource.type)
.execute()
}
env
}
}
Custom SQL Functions
// Using PostgreSQL functions
fun findNearExpiry(days: Int): List<Environment> =
dsl.selectFrom(ENVIRONMENT)
.where(
ENVIRONMENT.EXPIRES_AT.lessOrEqual(
DSL.currentTimestamp().plus(DSL.interval(days, DatePart.DAY))
)
)
.fetch()
.map { it.toEntity() }
// Array operations
fun findByTags(tags: List<String>): List<Environment> =
dsl.selectFrom(ENVIRONMENT)
.where(
DSL.field("tags").cast(SQLDataType.VARCHAR.array())
.contains(tags.toTypedArray())
)
.fetch()
.map { it.toEntity() }
