Basic CRUD Operations (Kotlin)

This example demonstrates the fundamental Create, Read, Update, and Delete operations using Kapper with Kotlin.

📁 Source Code: View the complete runnable examples in the kotlin-example directory.

Setup

The examples use a superhero-themed schema:

data class SuperHero(
    val id: UUID,
    val name: String,
    val email: String? = null,
    val age: Int? = null,
)

data class Villain(
    val id: UUID,
    val name: String,
)

data class SuperHeroBattle(
    val superhero: String,
    val villain: String,
    val date: LocalDateTime,
)

Create (Insert)

// Insert a single superhero
connection.execute(
    """
    INSERT INTO super_heroes(id, name, email, age) 
    VALUES (:id, :name, :email, :age)
    """.trimIndent(),
    "id" to UUID.randomUUID(),
    "name" to "Batman",
    "email" to "batman@dc.com",
    "age" to 85,
)

Read (Select)

// Query all superheroes
val heroes = connection.query<SuperHero>("SELECT * FROM super_heroes")

// Query with parameters
val olderHeroes = connection.query<SuperHero>(
    "SELECT * FROM super_heroes WHERE age > :age",
    "age" to 80,
)

// Query single superhero
val batman = connection.querySingle<SuperHero>(
    "SELECT * FROM super_heroes WHERE name = :name",
    "name" to "Batman",
)

Update

// Update superhero age
connection.execute(
    """
    UPDATE super_heroes
    SET age = 86
    WHERE name = :name
    """.trimIndent(),
    "name" to "Batman",
)

Delete

// Delete superhero
connection.execute(
    """
    DELETE FROM super_heroes
    WHERE name = :name
    """.trimIndent(),
    "name" to "Batman",
)

Custom Mapping

You can use custom mappers for complex result processing:

// Custom mapping to Pair
val heroAges = connection.query<Pair<String, Int>>(
    "SELECT * FROM super_heroes WHERE age > :age",
    { resultSet, fields ->
        Pair(
            resultSet.getString(fields["name"]!!.columnIndex),
            resultSet.getInt(fields["age"]!!.columnIndex),
        )
    },
    "age" to 80,
)

Complete Example

fun main() {
    val dataSource = HikariDataSource().apply {
        jdbcUrl = "jdbc:postgresql://localhost:5432/example"
        username = "user"
        password = "password"
    }
    
    dataSource.connection.use { connection ->
        // Insert superheroes
        connection.execute(
            """
            INSERT INTO super_heroes(id, name, email, age) 
            VALUES (:id, :name, :email, :age)
            """.trimIndent(),
            "id" to UUID.randomUUID(),
            "name" to "Superman",
            "email" to "superman@dc.com",
            "age" to 86,
        )
        
        // Query with parameters
        val olderHeroes = connection.query<SuperHero>(
            "SELECT * FROM super_heroes WHERE age > :age",
            "age" to 80,
        )
        println("Older heroes: $olderHeroes")
        
        // Update
        connection.execute(
            """
            UPDATE super_heroes
            SET age = 87
            WHERE name = :name
            """.trimIndent(),
            "name" to "Superman",
        )
        
        // Query single result
        val superman = connection.querySingle<SuperHero>(
            "SELECT * FROM super_heroes WHERE name = :name",
            "name" to "Superman",
        )
        println("Updated Superman: $superman")
    }
}

Transactions

import net.samyn.kapper.withTransaction

dataSource.withTransaction { connection ->
    // All operations in this block are transactional
    connection.execute(
        "INSERT INTO super_heroes(id, name, email, age) VALUES(:id, :name, :email, :age)",
        "id" to UUID.randomUUID(),
        "name" to "Spider-Man",
        "email" to "spidey@marvel.com",
        "age" to 16
    )
    
    connection.execute(
        "UPDATE super_heroes SET email = :email WHERE name = :name",
        "email" to "peter.parker@marvel.com",
        "name" to "Spider-Man"
    )
}

Next Steps

• Advanced Queries - Complex SQL operations
• Transaction Handling - Managing database transactions
• Coroutines & Flow - Async operations with coroutines

Source Examples

• Complete test examples: Execute.kt
• DTO examples: ExecuteWithDto.kt
• Query examples: Query.kt