### Initialize MySQL Databases and Tables using Shell Script

Source: https://github.com/be-hase/kuery-client/blob/main/examples/README.md

This snippet demonstrates initializing MySQL databases and tables by running a shell script. Similar to running the container, you need to be in the 'examples' directory. This script is responsible for setting up the database schema.

```shell
cd examples
./init_mysql.sh
```

--------------------------------

### Run MySQL Container using Shell Script

Source: https://github.com/be-hase/kuery-client/blob/main/examples/README.md

This snippet shows how to execute a shell script to run a MySQL container. It requires navigating to the 'examples' directory before running the script. No specific inputs or outputs are detailed, but it's assumed to start a database service.

```shell
cd examples
./run_containers.sh
```

--------------------------------

### Install Kuery Client with Gradle (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/getting-started.md

Instructions for adding the Kuery Client to your Gradle project. Supports both Spring Data R2DBC and Spring Data JDBC.

```kotlin
plugins {
    id("dev.hsbrysk.kuery-client") version "{{version}}"
}

implementation("dev.hsbrysk.kuery-client:kuery-client-spring-data-r2dbc:{{version}}")
```

```kotlin
plugins {
    id("dev.hsbrysk.kuery-client") version "{{version}}"
}

implementation("dev.hsbrysk.kuery-client:kuery-client-spring-data-jdbc:{{version}}")
```

--------------------------------

### Run Application with Gradle (Spring Data JDBC)

Source: https://github.com/be-hase/kuery-client/blob/main/examples/README.md

This snippet demonstrates running the application using Gradle, focusing on the Spring Data JDBC module. Ensure you are in the 'examples' directory and have the Gradle wrapper. This command initiates the application's execution.

```shell
cd examples
./gradlew :spring-data-jdbc:bootRun
```

--------------------------------

### Use KueryClient to Query Data (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/getting-started.md

Example of using the built KueryClient to execute a SQL query and retrieve a single result or null. Assumes a User data class and a userId.

```kotlin
val userId = "..."
val user: User = kueryClient
    .sql { +"SELECT * FROM users WHERE user_id = $userId" }
    .singleOrNull()
```

--------------------------------

### Run Application with Gradle (Spring Data R2DBC)

Source: https://github.com/be-hase/kuery-client/blob/main/examples/README.md

This snippet shows how to run the application using Gradle, specifically targeting the Spring Data R2DBC module. It requires the Gradle wrapper to be present and executable in the 'examples' directory. This command compiles and runs the application.

```shell
cd examples
./gradlew :spring-data-r2dbc:bootRun
```

--------------------------------

### Build KueryClient for Spring Data (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/getting-started.md

Demonstrates how to build KueryClient instances for Spring Data R2DBC and Spring Data JDBC. Requires a ConnectionFactory or DataSource.

```kotlin
val connectionFactory: ConnectionFactory = ...

val kueryClient = SpringR2dbcKueryClient.builder()
    .connectionFactory(connectionFactory)
    .build()
```

```kotlin
val dataSource: DataSource = ...

val kueryClient = SpringJdbcKueryClient.builder()
    .dataSource(dataSource)
    .build()
```

--------------------------------

### Installation via Gradle

Source: https://github.com/be-hase/kuery-client/blob/main/README.md

Configuration for adding the Kuery Client plugin and dependencies to a Gradle project. Users can choose between R2DBC or JDBC implementations.

```kotlin
plugins {
    id("dev.hsbrysk.kuery-client") version "{{version}}"
}

implementation("dev.hsbrysk.kuery-client:kuery-client-spring-data-r2dbc:{{version}}")
// or, implementation("dev.hsbrysk.kuery-client:kuery-client-spring-data-jdbc:{{version}}")
```

--------------------------------

### Implement Spring Boot Application with KueryClient

Source: https://context7.com/be-hase/kuery-client/llms.txt

A comprehensive example showing the configuration of a SpringR2dbcKueryClient bean and the implementation of a repository layer for CRUD operations. It utilizes Kotlin coroutines for asynchronous database access and demonstrates dynamic SQL construction.

```kotlin
import dev.hsbrysk.kuery.core.KueryClient
import dev.hsbrysk.kuery.core.list
import dev.hsbrysk.kuery.core.singleOrNull
import dev.hsbrysk.kuery.spring.r2dbc.SpringR2dbcKueryClient
import io.micrometer.observation.ObservationRegistry
import io.r2dbc.spi.ConnectionFactory
import org.springframework.boot.autoconfigure.SpringBootApplication
import org.springframework.boot.runApplication
import org.springframework.context.annotation.Bean
import org.springframework.context.annotation.Configuration
import org.springframework.http.HttpStatus
import org.springframework.stereotype.Repository
import org.springframework.stereotype.Service
import org.springframework.transaction.annotation.Transactional
import org.springframework.web.bind.annotation.*
import org.springframework.web.server.ResponseStatusException

fun main(args: Array<String>) {
    runApplication<Application>(*args)
}

@SpringBootApplication
class Application

@Configuration(proxyBeanMethods = false)
class AppConfiguration {
    @Bean
    fun kueryClient(
        connectionFactory: ConnectionFactory,
        observationRegistry: ObservationRegistry
    ): KueryClient = SpringR2dbcKueryClient.builder()
        .connectionFactory(connectionFactory)
        .observationRegistry(observationRegistry)
        .build()
}

data class User(val userId: Int, val username: String, val email: String)

@Repository
class UserRepository(private val kueryClient: KueryClient) {
    suspend fun findById(id: Int): User? = kueryClient
        .sql { +"SELECT * FROM users WHERE user_id = $id" }
        .singleOrNull()

    suspend fun findAll(): List<User> = kueryClient
        .sql { +"SELECT * FROM users ORDER BY username" }
        .list()

    suspend fun search(username: String?, email: String?): List<User> = kueryClient
        .sql {
            +"SELECT * FROM users WHERE 1=1"
            if (username != null) +"AND username LIKE $username"
            if (email != null) +"AND email = $email"
        }
        .list()

    suspend fun insert(username: String, email: String): Int {
        val result = kueryClient
            .sql { +"INSERT INTO users (username, email) VALUES ($username, $email)" }
            .generatedValues("user_id")
        return (result["user_id"] as Long).toInt()
    }

    suspend fun update(id: Int, username: String, email: String): Long = kueryClient
        .sql { +"UPDATE users SET username = $username, email = $email WHERE user_id = $id" }
        .rowsUpdated()

    suspend fun delete(id: Int): Long = kueryClient
        .sql { +"DELETE FROM users WHERE user_id = $id" }
        .rowsUpdated()
}

@Service
class UserService(private val userRepository: UserRepository) {
    suspend fun getUser(id: Int): User =
        userRepository.findById(id) ?: throw ResponseStatusException(HttpStatus.NOT_FOUND)

    suspend fun getAllUsers(): List<User> = userRepository.findAll()

    @Transactional
    suspend fun createUser(username: String, email: String): Int =
        userRepository.insert(username, email)
}

@RestController
@RequestMapping("/api/users")
class UserController(private val userService: UserService) {
    @GetMapping("/{id}")
    suspend fun getUser(@PathVariable id: Int) = userService.getUser(id)

    @GetMapping
    suspend fun getAllUsers() = userService.getAllUsers()

    @PostMapping
    suspend fun createUser(@RequestBody request: CreateUserRequest) =
        mapOf("userId" to userService.createUser(request.username, request.email))

    data class CreateUserRequest(val username: String, val email: String)
}
```

--------------------------------

### Install Kuery Client Gradle Plugin and Dependencies

Source: https://context7.com/be-hase/kuery-client/llms.txt

Add the Kuery Client Gradle plugin and the appropriate implementation dependency for either R2DBC (reactive) or JDBC (blocking) database access to your project.

```kotlin
// build.gradle.kts for R2DBC (reactive)
plugins {
    id("dev.hsbrysk.kuery-client") version "{{version}}"
}

dependencies {
    implementation("dev.hsbrysk.kuery-client:kuery-client-spring-data-r2dbc:{{version}}")
}

// OR for JDBC (blocking)
plugins {
    id("dev.hsbrysk.kuery-client") version "{{version}}"
}

dependencies {
    implementation("dev.hsbrysk.kuery-client:kuery-client-spring-data-jdbc:{{version}}")
}
```

--------------------------------

### Declarative and Programmatic Transactions with JDBC

Source: https://context7.com/be-hase/kuery-client/llms.txt

Illustrates transaction management for JDBC using Spring's @Transactional annotation for declarative control and TransactionTemplate for programmatic control. Includes examples for user creation and credit transfers.

```kotlin
import org.springframework.transaction.support.TransactionTemplate
import org.springframework.transaction.annotation.Transactional
import java.math.BigInteger

@Service
class UserService(
    private val userRepository: UserRepository,
    private val transaction: TransactionTemplate
) {
    // Declarative transaction with @Transactional
    @Transactional
    fun createUserWithProfile(username: String, email: String): Int {
        val userId = userRepository.insert(username, email)
        userRepository.createProfile(userId)
        return userId
    }

    // Programmatic transaction with TransactionTemplate
    fun transferCredits(fromId: Int, toId: Int, amount: Int): Boolean {
        return transaction.execute {
            userRepository.deductCredits(fromId, amount)
            userRepository.addCredits(toId, amount)
            true
        }!!
    }
}

@Repository
class UserRepository(private val kueryClient: KueryBlockingClient) {
    fun insert(username: String, email: String): Int {
        val result = kueryClient
            .sql { +
                "INSERT INTO users (username, email) VALUES ($username, $email)"
            }
            .generatedValues("user_id")
        return (result["GENERATED_KEY"] as BigInteger).toInt()
    }

    fun createProfile(userId: Int): Long = kueryClient
        .sql { +
            "INSERT INTO profiles (user_id) VALUES ($userId)"
        }
        .rowsUpdated()
}
```

--------------------------------

### Define Repository with KueryClient (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/observation.md

Shows a basic example of a Spring Data R2DBC repository in Kotlin that utilizes the injected KueryClient to perform database queries. This demonstrates the typical usage within a service layer.

```kotlin
package com.example.spring.data.r2dbc

// ...

@Repository
class UserRepository(private val kueryClient: KueryClient) {
    suspend fun selectByUserId(userId: Int): User? = kueryClient
        .sql {
            +"SELECT * FROM users WHERE user_id = $userId"
        }
        .singleOrNull()
}
```

--------------------------------

### Declarative and Programmatic Transactions with R2DBC

Source: https://context7.com/be-hase/kuery-client/llms.txt

Demonstrates transaction management for R2DBC using Spring's @Transactional annotation for declarative control and TransactionalOperator for programmatic control. It includes examples of user creation and credit transfers.

```kotlin
import org.springframework.transaction.reactive.TransactionalOperator
import org.springframework.transaction.reactive.executeAndAwait
import org.springframework.transaction.annotation.Transactional

@Service
class UserService(
    private val userRepository: UserRepository,
    private val transaction: TransactionalOperator
) {
    // Declarative transaction with @Transactional
    @Transactional
    suspend fun createUserWithProfile(username: String, email: String): Int {
        val userId = userRepository.insert(username, email)
        userRepository.createProfile(userId)
        return userId
    }

    // Programmatic transaction with TransactionalOperator
    suspend fun transferCredits(fromId: Int, toId: Int, amount: Int): Boolean {
        return transaction.executeAndAwait {
            userRepository.deductCredits(fromId, amount)
            userRepository.addCredits(toId, amount)
            true
        }
    }
}

@Repository
class UserRepository(private val kueryClient: KueryClient) {
    suspend fun insert(username: String, email: String): Int {
        val result = kueryClient
            .sql { +
                "INSERT INTO users (username, email) VALUES ($username, $email)"
            }
            .generatedValues("user_id")
        return (result["user_id"] as Long).toInt()
    }

    suspend fun createProfile(userId: Int): Long = kueryClient
        .sql { +
            "INSERT INTO profiles (user_id) VALUES ($userId)"
        }
        .rowsUpdated()

    suspend fun deductCredits(userId: Int, amount: Int): Long = kueryClient
        .sql { +
            "UPDATE users SET credits = credits - $amount WHERE user_id = $userId"
        }
        .rowsUpdated()

    suspend fun addCredits(userId: Int, amount: Int): Long = kueryClient
        .sql { +
            "UPDATE users SET credits = credits + $amount WHERE user_id = $userId"
        }
        .rowsUpdated()
}
```

--------------------------------

### Initializing KueryClient

Source: https://github.com/be-hase/kuery-client/blob/main/README.md

Shows how to instantiate the KueryClient using either a ConnectionFactory for R2DBC or a DataSource for JDBC.

```kotlin
// for kuery-client-spring-data-r2dbc
val connectionFactory: ConnectionFactory = ...
val kueryClient = SpringR2dbcKueryClient.builder()
    .connectionFactory(connectionFactory)
    .build()

// for kuery-client-spring-data-jdbc
val dataSource: DataSource = ...
val kueryClient = SpringJdbcKueryClient.builder()
    .dataSource(dataSource)
    .build()
```

--------------------------------

### Dynamic SQL Construction and Execution

Source: https://github.com/be-hase/kuery-client/blob/main/README.md

Demonstrates how to use the KueryClient DSL to build dynamic SQL queries with parameter binding and execute them. It supports conditional logic using standard Kotlin syntax and provides helper functions for batch operations.

```kotlin
data class User(...)

class UserRepository(private val kueryClient: KueryClient) {
    suspend fun findById(userId: Int): User? = kueryClient
        .sql { +"SELECT * FROM users WHERE user_id = $userId" }
        .singleOrNull()

    suspend fun search(status: String, vip: Boolean?): List<User> = kueryClient
        .sql {
            +"""
            SELECT * FROM users
            WHERE
            status = $status
            """
            if (vip != null) {
                +"AND vip = $vip"
            }
        }
        .list()

    suspend fun insertMany(users: List<User>): Long = kueryClient
        .sql {
            +"INSERT INTO users (username, email)"
            values(users) { listOf(it.username, it.email) }
        }
        .rowsUpdated()
}
```

--------------------------------

### Use Kotlin Logic (if, for) in SQL Construction (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/basics.md

Demonstrates embedding Kotlin's control flow logic, such as `if` statements, directly within the SQL construction block. This allows for dynamic query building based on conditions.

```kotlin
kueryClient
    .sql {
        +"SELECT * FROM users"
        +"WHERE"
        +"status = $status"
        if (vip != null) {
            +"AND vip = $vip"
        }
    }
```

--------------------------------

### Initialize KueryClient with ObservationRegistry (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/observation.md

Demonstrates how to create a KueryClient instance in Kotlin, specifying the ObservationRegistry for metrics collection. This is essential for enabling observation features.

```kotlin
val kueryClient = SpringR2dbcKueryClient.builder()
    .connectionFactory(connectionFactory)
    .observationRegistry(...)
.build()
```

--------------------------------

### Bind Parameters using String Interpolation (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/basics.md

Illustrates how to bind parameters to SQL queries using Kotlin's string interpolation. This is a secure way to include dynamic values in your SQL.

```kotlin
val userId = "..."
kueryClient
    .sql {
        +"""
        SELECT * FROM users
        WHERE user_id = $userId
        """
    }
```

--------------------------------

### Fetch Multiple Results as List of Maps (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/basics.md

Demonstrates fetching multiple query results as a `List<Map<String, Any?>>`. This is suitable for retrieving collections of records where each record is a map of column names to values.

```kotlin
val result: List<Map<String, Any?>> = kueyClient
    .sql { +
"SELECT * FROM users WHERE user_id = 1" }
    .listMap()
```

--------------------------------

### Fetch Single Result as Map (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/basics.md

Shows how to fetch a single query result as a `Map<String, Any?>`. This is useful when expecting a single row with named columns.

```kotlin
val map: Map<String, Any?> = kueyClient
    .sql { +
"SELECT * FROM users WHERE user_id = 1" }
    .singleMap()
```

--------------------------------

### Integrate Micrometer Observation for Metrics and Tracing

Source: https://context7.com/be-hase/kuery-client/llms.txt

Configures the KueryClient to report metrics and traces using Micrometer's ObservationRegistry. This enables monitoring of SQL execution performance through systems like Prometheus.

```kotlin
import io.micrometer.observation.ObservationRegistry

@Configuration(proxyBeanMethods = false)
class KueryClientConfiguration {
    @Bean
    fun kueryClient(connectionFactory: ConnectionFactory, observationRegistry: ObservationRegistry): KueryClient = 
        SpringR2dbcKueryClient.builder()
            .connectionFactory(connectionFactory)
            .observationRegistry(observationRegistry)
            .build()
}
```

--------------------------------

### Build and Execute SQL Queries with Kuery Client (JDBC)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/introduction.md

Demonstrates how to use Kuery Client with Spring Data JDBC to perform various database operations like finding a user by ID, searching users with dynamic criteria, and inserting multiple users. It utilizes Kotlin's string interpolation for parameter binding and extension functions for dynamic query building.

```kotlin
data class User(...)

class UserRepository(private val kueryClient: KueryBlockingClient) {
    fun findById(userId: Int): User? = kueryClient
        .sql { +
"SELECT * FROM users WHERE user_id = $userId" }
        .singleOrNull()

    fun search(status: String, vip: Boolean?): List<User> = kueryClient
        .sql {
            +"""
            SELECT * FROM users
            WHERE
            status = $status
            """
            if (vip != null) {
                +"AND vip = $vip"
            }
        }
        .list()

    fun insertMany(users: List<User>): Long = kueryClient
        .sql {
            +"INSERT INTO users (username, email)"
            // useful helper function
            values(users) { listOf(it.username, it.email) }
        }
        .rowsUpdated()
}
```

--------------------------------

### Build and Execute SQL Queries with Kuery Client (R2DBC)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/introduction.md

Demonstrates how to use Kuery Client with Spring Data R2DBC to perform various database operations like finding a user by ID, searching users with dynamic criteria, and inserting multiple users. It utilizes Kotlin's string interpolation for parameter binding and extension functions for dynamic query building.

```kotlin
data class User(...)

class UserRepository(private val kueryClient: KueryClient) {
    suspend fun findById(userId: Int): User? = kueryClient
        .sql { +
"SELECT * FROM users WHERE user_id = $userId" }
        .singleOrNull()

    suspend fun search(status: String, vip: Boolean?): List<User> = kueryClient
        .sql {
            +"""
            SELECT * FROM users
            WHERE
            status = $status
            """
            if (vip != null) {
                +"AND vip = $vip"
            }
        }
        .list()

    suspend fun insertMany(users: List<User>): Long = kueryClient
        .sql {
            +"INSERT INTO users (username, email)"
            // useful helper function
            values(users) { listOf(it.username, it.email) }
        }
        .rowsUpdated()
}
```

--------------------------------

### Execute Detekt Analysis

Source: https://github.com/be-hase/kuery-client/blob/main/docs/detekt.md

Run the detektMain Gradle task to perform static analysis with type resolution enabled.

```shell
./gradlew detektMain
```

--------------------------------

### Add SQL String with add() Function (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/basics.md

Shows how to add SQL strings using the `add(sql: String)` function, which is an alias for the unary plus operator. This function provides IDE syntax assistance for SQL due to annotation.

```kotlin
kueryClient
    .sql {
        add("SELECT * FROM users")
        add("WHERE user_id = 1")
    }
```

--------------------------------

### Fetch Multiple Results as List of Specific Type (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/basics.md

Illustrates fetching multiple query results and converting them into a `List<T>`, where `T` is a specified Kotlin type. Requires a `KClass<T>` for type conversion.

```kotlin
val users: List<User> = kueyClient
    .sql { +
"SELECT * FROM users WHERE user_id = 1" }
    .list()
```

--------------------------------

### Fetch Multiple Results as Flow of Maps (R2DBC Only) (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/basics.md

Shows how to receive multiple query results as a `Flow<Map<String, Any?>>` using the `kuery-client-spring-data-r2dbc` module. This provides reactive streaming of results.

```kotlin
val result: Flow<Map<String, Any?>> = kueyClient
    .sql { +
"SELECT * FROM users WHERE user_id = 1" }
    .flowMap()
```

--------------------------------

### Implement Custom SQL Helper Functions

Source: https://github.com/be-hase/kuery-client/blob/main/docs/helpers.md

Provides the implementation logic for the values helper function. It demonstrates how to extend SqlBuilder to handle batch data binding and safe SQL string construction.

```kotlin
fun SqlBuilder.values(input: List<List<Any?>>) {
    require(input.isNotEmpty()) { "inputted list is empty" }
    val firstSize = input.first().size
    require(input.all { it.size == firstSize }) { "All inputted child lists must have the same size." }
    require(firstSize > 0) { "inputted child list is empty" }

    val placeholders = input.joinToString(", ") { list ->
        list.joinToString(separator = ", ", prefix = "(", postfix = ")") {
            bind(it)
        }
    }
    addUnsafe("VALUES $placeholders")
}

fun <T> SqlBuilder.values(
    input: List<T>,
    transformer: (T) -> List<Any?>,
) {
    values(input.map { transformer(it) })
}
```

--------------------------------

### Build KueryClient Instance (R2DBC and JDBC)

Source: https://context7.com/be-hase/kuery-client/llms.txt

Create a KueryClient instance using the builder pattern, providing either a ConnectionFactory for R2DBC (reactive) or a DataSource for JDBC (blocking).

```kotlin
// R2DBC (reactive) - SpringR2dbcKueryClient
import dev.hsbrysk.kuery.spring.r2dbc.SpringR2dbcKueryClient
import io.r2dbc.spi.ConnectionFactory

val connectionFactory: ConnectionFactory = // ... your connection factory
val kueryClient = SpringR2dbcKueryClient.builder()
    .connectionFactory(connectionFactory)
    .build()

// JDBC (blocking) - SpringJdbcKueryClient
import dev.hsbrysk.kuery.spring.jdbc.SpringJdbcKueryClient
import javax.sql.DataSource

val dataSource: DataSource = // ... your data source
val kueryClient = SpringJdbcKueryClient.builder()
    .dataSource(dataSource)
    .build()
```

--------------------------------

### Configure Custom SQL IDs for Observability

Source: https://context7.com/be-hase/kuery-client/llms.txt

Shows how to assign custom identifiers to SQL queries to improve tracing and metrics. This is particularly useful when multiple queries are executed within a single method.

```kotlin
@Repository
class UserRepository(private val kueryClient: KueryClient) {

    // Multiple queries in same method - specify custom SQL IDs
    suspend fun getUserWithDetails(userId: Int): UserAndDetail {
        val user: User = kueryClient
            .sql("user_repository.get_user") {
                +"SELECT * FROM users WHERE user_id = $userId"
            }
            .single()

        val details: UserDetail = kueryClient
            .sql("user_repository.get_user_details") {
                +"SELECT * FROM user_details WHERE user_id = $userId"
            }
            .single()

        return UserAndDetail(user, details)
    }
}
```

--------------------------------

### Customize KueryClient Observation with Convention (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/observation.md

Shows how to further customize the observation process by providing an ObservationConvention to the KueryClient builder. This allows for fine-grained control over metric names and settings.

```kotlin
val kueryClient = SpringR2dbcKueryClient.builder()
    .connectionFactory(connectionFactory)
    .observationRegistry(...)
    .observationConvention(...)
.build()
```

--------------------------------

### Add Dependencies for Spring Boot Actuator and Prometheus (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/observation.md

Provides the necessary Gradle dependencies for integrating Spring Boot Actuator and Micrometer Prometheus registry. These are required for exposing and collecting metrics.

```kotlin
// ...
// other dependencies
// ...
implementation("org.springframework.boot:spring-boot-starter-actuator:{{version}}")
implementation("io.micrometer:micrometer-registry-prometheus:{{version}}")
```

--------------------------------

### Fetch Single Result as Map or Null (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/basics.md

Demonstrates fetching a single query result as a `Map<String, Any?>?`, returning null if no rows are found. This handles cases where a record might not exist.

```kotlin
val map: Map<String, Any?>? = kueyClient
    .sql { +
"SELECT * FROM users WHERE user_id = 1" }
    .singleMapOrNull()
```

--------------------------------

### Register Custom Type Converters for Domain Objects

Source: https://context7.com/be-hase/kuery-client/llms.txt

Demonstrates how to implement Spring's Converter interface to map custom domain objects to database types. Requires registering the converters during the KueryClient builder initialization.

```kotlin
import org.springframework.core.convert.converter.Converter
import org.springframework.data.convert.ReadingConverter
import org.springframework.data.convert.WritingConverter

data class Email(val value: String)

@WritingConverter
class EmailToStringConverter : Converter<Email, String> {
    override fun convert(source: Email): String = source.value
}

@ReadingConverter
class StringToEmailConverter : Converter<String, Email> {
    override fun convert(source: String): Email = Email(source)
}

val kueryClient = SpringR2dbcKueryClient.builder()
    .connectionFactory(connectionFactory)
    .converters(listOf(EmailToStringConverter(), StringToEmailConverter()))
    .build()
```

--------------------------------

### Perform Batch Inserts with values() Helper

Source: https://context7.com/be-hase/kuery-client/llms.txt

Utilizes the values() helper function to execute efficient multi-row inserts. It supports both object-based transformations and raw list-of-lists input for flexible data handling.

```kotlin
data class UserParam(val username: String, val email: String?, val age: Int)

// Batch insert with transformer function
suspend fun insertUsers(users: List<UserParam>): Long = kueryClient
    .sql {
        +"INSERT INTO users (username, email, age)"
        values(users) { listOf(it.username, it.email, it.age) }
    }
    .rowsUpdated()

// Alternative: using raw list of lists
suspend fun insertRawValues(data: List<List<Any?>>): Long = kueryClient
    .sql {
        +"INSERT INTO users (username, email, age)"
        values(data)
    }
    .rowsUpdated()
```

--------------------------------

### Fetch Generated Values (e.g., Auto-Increment IDs) (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/basics.md

Illustrates how to retrieve database-generated values, such as auto-increment primary keys, after an INSERT operation. Returns a map of column names to their generated values.

```kotlin
val result: Map<String, Any> = kueyClient
    .sql {+"INSERT INTO users (username, email) VALUES ('username1', 'email1')"}
    .generatedValues("user_id")
```

--------------------------------

### Perform Multi-row Inserts with values Helper

Source: https://github.com/be-hase/kuery-client/blob/main/docs/helpers.md

Demonstrates how to use the values helper function to perform batch inserts into a database table. It maps a list of data objects to SQL parameters using a transformer function.

```kotlin
@Test
fun test() = runTest {
        data class UserParam(val username: String, val email: String?, val age: Int)

        val input = listOf(
            UserParam("user1", "user1@example.com", 1),
            UserParam("user2", null, 2),
            UserParam("user3", "user3@example.com", 3),
        )

        kueryClient.sql {
            +"INSERT INTO users (username, email, age)"
            values(input) { listOf(it.username, it.email, it.age) }
        }.rowsUpdated()
    }
```

--------------------------------

### Fetch Single Result as Specific Type (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/basics.md

Illustrates fetching a single query result and converting it to a specified Kotlin type `T`. This requires a corresponding `KClass<T>` to be provided.

```kotlin
val user: User = kueyClient
    .sql { +
"SELECT * FROM users WHERE user_id = 1" }
    .single()

```

--------------------------------

### Configure Detekt Plugin Dependency

Source: https://github.com/be-hase/kuery-client/blob/main/docs/detekt.md

Add the kuery-client Detekt plugin to your project's build dependencies to enable custom rule analysis.

```kotlin
dependencies {
    detektPlugins("dev.hsbrysk.kuery-client:kuery-client-detekt:{{version}}")
}
```

--------------------------------

### SQL Building with `+` Operator and String Interpolation

Source: https://context7.com/be-hase/kuery-client/llms.txt

Construct SQL queries by concatenating strings using the `+` operator. Parameter binding is automatically handled via Kotlin string interpolation.

```kotlin
// Simple single-line query
val userId = 123
val user: User? = kueryClient
    .sql { +
SELECT * FROM users WHERE user_id = $userId" }
    .singleOrNull()

// Multi-line query with string concatenation
val status = "active"
val users: List<User> = kueryClient
    .sql {
        +"SELECT * FROM users"
        +"WHERE status = $status"
        +"ORDER BY created_at DESC"
    }
    .list()

// Multi-line SQL using triple-quoted strings
val user: User? = kueryClient
    .sql {
        +"""
        SELECT u.*, p.name as profile_name
        FROM users u
        LEFT JOIN profiles p ON u.user_id = p.user_id
        WHERE u.user_id = $userId
        """
    }
    .singleOrNull()
```

--------------------------------

### Extend SqlBuilder with Custom Helper Functions

Source: https://context7.com/be-hase/kuery-client/llms.txt

Uses Kotlin extension functions to create reusable SQL fragments for pagination, ordering, and complex conditional clauses. This promotes cleaner and more maintainable query code.

```kotlin
import dev.hsbrysk.kuery.core.SqlBuilder

fun SqlBuilder.paginate(page: Int, pageSize: Int) {
    val offset = (page - 1) * pageSize
    +"LIMIT $pageSize OFFSET $offset"
}

fun SqlBuilder.orderBy(column: String, ascending: Boolean = true) {
    val direction = if (ascending) "ASC" else "DESC"
    +"ORDER BY $column $direction"
}
```

--------------------------------

### Perform Database Operations with Custom Types

Source: https://github.com/be-hase/kuery-client/blob/main/docs/type-conversion.md

Demonstrates using the custom type in SQL queries and data mapping within the KueryClient, leveraging the registered converters for automatic type handling.

```kotlin
suspend fun write(str: StringWrapper): Long = kueryClient
    .sql {
        +"INSERT INTO test_table (text) VALUES ($str)"
    }
    .rowsUpdated()

data class Record(
    val text: StringWrapper,
)

suspend fun read(): List<Record> = kueryClient
    .sql {
        +"SELECT * FROM test_table"
    }
    .list()
```

--------------------------------

### Fetch Multiple Results as Flow of Specific Type (R2DBC Only) (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/basics.md

Demonstrates receiving multiple query results as a `Flow<T>`, where `T` is a specified Kotlin type, using the `kuery-client-spring-data-r2dbc` module. Enables reactive streaming of typed results.

```kotlin
val users: Flow<User> = kueyClient
    .sql { +
"SELECT * FROM users WHERE user_id = 1" }
    .flow()
```

--------------------------------

### Register KueryClient Bean with ObservationRegistry (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/observation.md

Illustrates how to configure a KueryClient as a Spring Bean in a Kotlin configuration class, injecting the necessary ConnectionFactory and ObservationRegistry. This is a common pattern in Spring Boot applications.

```kotlin
@Configuration(proxyBeanMethods = false)
class ExampleConfiguration {
    @Bean
    fun kueryClient(connectionFactory: ConnectionFactory, observationRegistry: ObservationRegistry): KueryClient {
        return SpringR2dbcKueryClient.builder()
            .connectionFactory(connectionFactory)
            .observationRegistry(observationRegistry)
            .build()
    }
}
```

--------------------------------

### Dynamic SQL with Kotlin Control Flow

Source: https://context7.com/be-hase/kuery-client/llms.txt

Build dynamic SQL queries by embedding native Kotlin control flow statements like `if` and loops directly within the SQL builder lambda. This eliminates the need for custom template languages.

```kotlin
data class SearchParams(
    val status: String?,
    val vip: Boolean?,
    val minAge: Int?,
    val roles: List<String>?
)

suspend fun searchUsers(params: SearchParams): List<User> = kueryClient
    .sql {
        +"SELECT * FROM users WHERE 1=1"

        // Conditional clauses with if statements
        if (params.status != null) {
            +"AND status = ${params.status}"
        }
        if (params.vip != null) {
            +"AND vip = ${params.vip}"
        }
        if (params.minAge != null) {
            +"AND age >= ${params.minAge}"
        }

        // IN clause with list parameters
        if (!params.roles.isNullOrEmpty()) {
            +"AND role IN (${params.roles})"
        }

        +"ORDER BY created_at DESC"
    }
    .list()
```

--------------------------------

### Concatenate SQL Strings with + Operator (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/basics.md

Demonstrates concatenating SQL strings using the unary plus (+) operator in Kotlin. This is useful for building multi-line SQL queries.

```kotlin
kueryClient
    .sql {
        +"SELECT * FROM users"
        +"WHERE user_id = 1"
    }
```

```kotlin
kueryClient
    .sql {
        +"""
        SELECT * FROM users
        WHERE user_id = 1
        """
    }
```

--------------------------------

### UseStringLiteralRule Compliance

Source: https://github.com/be-hase/kuery-client/blob/main/docs/detekt.md

Demonstrates noncompliant and compliant patterns for using string interpolation within SqlBuilder to prevent SQL injection.

```kotlin
// Noncompliant
kueryClient.sql {
    val sql = "SELECT * FROM user WHERE id = $id"
    +sql
}

// Compliant
kueryClient.sql {
    +"SELECT * FROM user WHERE id = $id"
}
```

--------------------------------

### AOP Transaction Management with JDBC (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/transaction.md

Demonstrates AOP-based transaction management using the @Transactional annotation for JDBC. This declarative approach simplifies transaction handling by automatically managing transaction boundaries around annotated methods.

```kotlin
import org.springframework.stereotype.Service
import org.springframework.stereotype.Repository
import org.springframework.transaction.annotation.Transactional
import io.kuery.KueryClient

@Service
class UserService(
    private val userRepository: UserRepository,
) {
    // Apply transactions using AOP
    @Transactional
    fun addUser(
        username: String,
        email: Email,
    ): Int {
        return userRepository.insert(username, email)
    }
}

@Repository
class UserRepository(private val kueryClient: KueryClient) {
    fun insert(
        username: String,
        email: Email,
    ): Int {
        // ...
        return 0
    }
}
```

--------------------------------

### Implement Spring Type Converters

Source: https://github.com/be-hase/kuery-client/blob/main/docs/type-conversion.md

Creates custom converter classes for reading and writing operations by implementing the Spring Converter interface. These classes handle the transformation between the custom type and the database-compatible string format.

```kotlin
@WritingConverter
class StringWrapperToStringConverter : Converter<StringWrapper, String> {
    override fun convert(source: StringWrapper): String {
        return source.value
    }
}

@ReadingConverter
class StringToStringWrapperConverter : Converter<String, StringWrapper> {
    override fun convert(source: String): StringWrapper {
        return StringWrapper(source)
    }
}
```

--------------------------------

### Fetch Multiple Rows with FetchSpec.list()

Source: https://context7.com/be-hase/kuery-client/llms.txt

Retrieves multiple rows from the database as a list, automatically mapped to the specified data class. Supports R2DBC and JDBC implementations, including complex join queries.

```kotlin
suspend fun getAllUsers(): List<User> = kueryClient
    .sql { +
"SELECT * FROM users ORDER BY username"
}
    .list()

fun getUsersByUsernames(usernames: List<String>): List<User> {
    if (usernames.isEmpty()) return emptyList()
    return kueryClient
        .sql { +
"SELECT * FROM users WHERE username IN ($usernames)"
}
        .list()
}

data class UserOrder(
    val username: String,
    val orderId: Int,
    val orderDate: LocalDate,
    val amount: BigDecimal
)

suspend fun getUserOrders(userId: Int): List<UserOrder> = kueryClient
    .sql {
        +"""
        SELECT users.username, orders.order_id, orders.order_date, orders.amount
        FROM users
        JOIN orders ON users.user_id = orders.user_id
        WHERE users.user_id = $userId
        ORDER BY orders.order_date DESC
        """
    }
    .list()
```

--------------------------------

### Specify Custom SQL ID for KueryClient Queries (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/observation.md

Demonstrates how to manually specify a custom `sql_id` when making multiple KueryClient SQL calls within the same repository method. This is crucial for accurate metric identification when a single method performs multiple distinct queries.

```kotlin
@Repository
class UserRepository(private val kueryClient: KueryClient) {
    suspend fun selectByUserId(userId: Int): UserAndDetail {
        val user: User = kueryClient
            .sql("my_sql_id_1") {
                +"SELECT * FROM users WHERE user_id = $userId"
            }
            .single()
        val userDetail: UserDetail = kueryClient
            .sql("my_sql_id_2") {
                +"SELECT * FROM user_details WHERE user_id = $userId"
            }
            .single()
        return UserAndDetail(user, userDetail)
    }
}
```

--------------------------------

### Map-Based Results with FetchSpec.singleMap() / listMap()

Source: https://context7.com/be-hase/kuery-client/llms.txt

Retrieves results as maps instead of typed objects, useful for dynamic queries or when full object mapping is not needed. Supports fetching a single row as a map or a list of maps.

```kotlin
suspend fun getUserAsMap(userId: Int): Map<String, Any?> = kueryClient
    .sql { +
"SELECT * FROM users WHERE user_id = $userId"
}
    .singleMap()

suspend fun findUserAsMap(userId: Int): Map<String, Any?>? = kueryClient
    .sql { +
"SELECT * FROM users WHERE user_id = $userId"
}
    .singleMapOrNull()

suspend fun getAllUsersAsMap(): List<Map<String, Any?>> = kueryClient
    .sql { +
"SELECT user_id, username FROM users"
}
    .listMap()

suspend fun getUserInfo(userId: Int) {
    val userMap = kueryClient
        .sql { +
"SELECT * FROM users WHERE user_id = $userId"
}
        .singleMapOrNull()

    userMap?.let {
        println("Username: ${it["username"]}")
        println("Email: ${it["email"]}")
    }
}
```

--------------------------------

### Fetch Single Result as Specific Type or Null (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/basics.md

Shows how to fetch a single query result as a specified Kotlin type `T?`, returning null if no rows are found. This is useful for optional single record retrieval.

```kotlin
val user: User? = kueyClient
    .sql { +
"SELECT * FROM users WHERE user_id = 1" }
    .singleOrNull()
```

--------------------------------

### Fetch Single Required Result with `single()`

Source: https://context7.com/be-hase/kuery-client/llms.txt

Retrieve exactly one row from the database using the `single()` method. This method throws an exception if zero or multiple rows are returned. Results are automatically mapped to the specified data class.

```kotlin
data class User(
    val userId: Int,
    val username: String,
    val email: String
)

// R2DBC (suspend function)
suspend fun getUser(userId: Int): User = kueryClient
    .sql { +
SELECT * FROM users WHERE user_id = $userId" }
    .single()

// JDBC (blocking function)
fun getUser(userId: Int): User = kueryClient
    .sql { +
SELECT * FROM users WHERE user_id = $userId" }
    .single()
```

--------------------------------

### Enable Custom Rules in Detekt Configuration

Source: https://github.com/be-hase/kuery-client/blob/main/docs/detekt.md

Custom rules must be explicitly enabled in the detekt configuration YAML file to be active during analysis.

```yaml
kuery-client:
  UseStringLiteral:
    active: true
```

--------------------------------

### AOP Transaction Management with R2DBC (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/transaction.md

Illustrates AOP-based transaction management using the @Transactional annotation for R2DBC. This method simplifies transaction handling by automatically managing transaction boundaries around annotated methods. It's a declarative approach to transaction management.

```kotlin
import org.springframework.stereotype.Service
import org.springframework.stereotype.Repository
import org.springframework.transaction.annotation.Transactional
import io.kuery.KueryClient

@Service
class UserService(
    private val userRepository: UserRepository,
) {
    // Apply transactions using AOP
    @Transactional
    suspend fun addUser(
        username: String,
        email: Email,
    ): Int {
        return userRepository.insert(username, email)
    }
}

@Repository
class UserRepository(private val kueryClient: KueryClient) {
    suspend fun insert(
        username: String,
        email: Email,
    ): Int {
        // ...
        return 0
    }
}
```

--------------------------------

### Register Converters in KueryClient

Source: https://github.com/be-hase/kuery-client/blob/main/docs/type-conversion.md

Configures the KueryClient instance by passing the custom converters to the builder during initialization.

```kotlin
val kueryClient = SpringR2dbcKueryClient.builder()
    .connectionFactory(connectionFactory)
    .converters(
        listOf(
            StringWrapperToStringConverter(),
            StringToStringWrapperConverter(),
        )
    )
    .build()
```

--------------------------------

### Fetch Optional Single Result with FetchSpec.singleOrNull()

Source: https://context7.com/be-hase/kuery-client/llms.txt

Retrieves zero or one row from the database, returning null if no row is found. Throws an exception if multiple rows are returned. Supports both R2DBC (suspend) and JDBC (blocking) functions.

```kotlin
suspend fun findUserByEmail(email: String): User? = kueryClient
    .sql { +
"SELECT * FROM users WHERE email = $email"
}
    .singleOrNull()

fun findUserByEmail(email: String): User? = kueryClient
    .sql { +
"SELECT * FROM users WHERE email = $email"
}
    .singleOrNull()

suspend fun getUserOrCreate(email: String): User {
    val existing = kueryClient
        .sql { +
"SELECT * FROM users WHERE email = $email"
}
        .singleOrNull<User>()

    return existing ?: createUser(email)
}
```

--------------------------------

### Fetch Number of Rows Updated (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/basics.md

Shows how to retrieve the number of rows affected by an SQL statement (e.g., INSERT, UPDATE, DELETE) as a `Long`. This is useful for confirming the impact of data modification operations.

```kotlin
val result: Long = kueyClient
    .sql {+"INSERT INTO users (username, email) VALUES ('username1', 'email1')"}
    .rowsUpdated()
```

--------------------------------

### Programmatic Transaction Management with JDBC (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/transaction.md

Shows programmatic transaction management using TransactionTemplate in Kotlin for JDBC. This allows for explicit control over transaction boundaries. It assumes TransactionTemplate is registered as a bean, which is typical in Spring Boot applications.

```kotlin
import org.springframework.stereotype.Service
import org.springframework.stereotype.Repository
import org.springframework.transaction.support.TransactionTemplate
import io.kuery.KueryClient

@Service
class UserService(
    private val userRepository: UserRepository,
    private val transaction: TransactionTemplate, // registered as a bean
) {
    fun addUser(
        username: String,
        email: Email,
    ): Int {
        // Programmatically apply transactions
        return transaction.execute {
            userRepository.insert(username, email)
        }!!
    }
}

@Repository
class UserRepository(private val kueryClient: KueryClient) {
    fun insert(
        username: String,
        email: Email,
    ): Int {
        // ...
        return 0
    }
}
```

--------------------------------

### Prevent SQL Injection with Detekt Custom Rules

Source: https://context7.com/be-hase/kuery-client/llms.txt

Configures Detekt to enforce safe SQL string interpolation. It highlights the difference between unsafe variable assignment and compliant direct string literal usage.

```kotlin
// build.gradle.kts
dependencies {
    detektPlugins("dev.hsbrysk.kuery-client:kuery-client-detekt:{{version}}")
}

// COMPLIANT usage
kueryClient.sql {
    +"SELECT * FROM users WHERE id = $id"
}
```

--------------------------------

### Retrieve Auto-Generated Keys with Kuery-Client

Source: https://context7.com/be-hase/kuery-client/llms.txt

Demonstrates how to extract database-generated values like auto-increment IDs after an INSERT operation. Supports both R2DBC and JDBC drivers with specific handling for column identifiers.

```kotlin
// R2DBC - get generated user_id
suspend fun createUser(username: String, email: String): Int {
    val generatedValues = kueryClient
        .sql { +"INSERT INTO users (username, email) VALUES ($username, $email)" }
        .generatedValues("user_id")

    return (generatedValues["user_id"] as Long).toInt()
}

// JDBC - get generated key (column name may vary by database)
fun createUser(username: String, email: String): Int {
    val generatedValues = kueryClient
        .sql { +"INSERT INTO users (username, email) VALUES ($username, $email)" }
        .generatedValues("user_id")

    // MySQL returns "GENERATED_KEY"
    return (generatedValues["GENERATED_KEY"] as BigInteger).toInt()
}

// Get multiple generated values
suspend fun createOrder(userId: Int, amount: BigDecimal): Map<String, Any> = kueryClient
    .sql { +"INSERT INTO orders (user_id, amount, order_date) VALUES ($userId, $amount, CURRENT_DATE)" }
    .generatedValues("order_id", "order_date")
```

--------------------------------

### Programmatic Transaction Management with R2DBC (Kotlin)

Source: https://github.com/be-hase/kuery-client/blob/main/docs/transaction.md

Demonstrates programmatic transaction management using TransactionalOperator in Kotlin for R2DBC. This approach allows for explicit control over transaction boundaries within your code. It assumes TransactionalOperator is registered as a bean, which is default in Spring Boot.

```kotlin
import org.springframework.stereotype.Service
import org.springframework.stereotype.Repository
import io.kuery.KueryClient
import io.kuery.transaction.TransactionalOperator

@Service
class UserService(
    private val userRepository: UserRepository,
    private val transaction: TransactionalOperator, // registered as a bean
) {
    suspend fun addUser(
        username: String,
        email: Email,
    ): Int {
        // Programmatically apply transactions
        return transaction.executeAndAwait {
            userRepository.insert(username, email)
        }
    }
}

@Repository
class UserRepository(private val kueryClient: KueryClient) {
    suspend fun insert(
        username: String,
        email: Email,
    ): Int {
        // ...
        return 0
    }
}
```

--------------------------------

### Streaming Results with FetchSpec.flow() (R2DBC Only)

Source: https://context7.com/be-hase/kuery-client/llms.txt

Streams results as a Kotlin Flow for memory-efficient processing of large result sets. Only available with the R2DBC implementation. Can stream typed objects or maps.

```kotlin
import kotlinx.coroutines.flow.Flow
import kotlinx.coroutines.flow.collect

fun streamAllUsers(): Flow<User> = kueryClient
    .sql { +
"SELECT * FROM users"
}
    .flow()

suspend fun processLargeDataset() {
    kueryClient
        .sql { +
"SELECT * FROM large_table"
}
        .flow<LargeRecord>()
        .collect {
            processRecord(it)
        }
}

fun streamAsMap(): Flow<Map<String, Any?>> = kueryClient
    .sql { +
"SELECT * FROM users"
}
    .flowMap()
```

--------------------------------

### Define Custom Data Type

Source: https://github.com/be-hase/kuery-client/blob/main/docs/type-conversion.md

Defines a simple data class wrapper to be used as a custom type within the application.

```kotlin
data class StringWrapper(val value: String)
```