Subscribe to Developer Soapbox

Stay up to date! Get all the latest & greatest posts delivered straight to your inbox

kotlin

Using Spring JdbcTemplate with Kotlin

  • Developer

    Our resident opinionated developer.

    More posts by Developer.

Developer

• 3 min read

What You’ll Need

  1. Java JDK and Kotlin compiler installed
  2. Kotlin IDE.  IntelliJ recommended.

What You’ll Get

A simple CRUD application to manage your favorite beers, written in Kotlin.

1. Generate a Spring Boot Project

Go to http://start.spring.io/ to generate your Spring Boot (Maven) project.  Select Kotlin as the language and add dependencies for JDBC API and H2 and click on Generate Project.  We’ll be using an H2 in-memory database in order to make this example completely stand alone. Extract the zip file and import into your favorite IDE as a Maven project.

2. Database Configuration

Update the src/main/resources/application.properties file with the properties below.  Combined with our H2 dependency, Spring Boot will use this to create an in-memory DB named “test” for us to play with.

spring.datasource.url=jdbc:h2:mem:test
spring.datasource.driverClassName=org.h2.Driver

3. Code

Go into your “@SpringBootApplication” annoted class and add the following.  Since this is a very simple app, we’ll be using a single file for all of our code.  For a larger application, you may want to break out each entity class into its own file, and place your DB logic inside a repository class.

Notice a few awesome things going on here:

  1. Kotlin data classes make creating DB entities SOOOO easy!
  2. Dependency injection of JdbcTemplate through the constructor.  Spring Boot will automagically apply the DB config from application.properties file into our jdbcTemplate, so its ready to use right away.
package com.developersoapbox.kotlinjdbc

import org.springframework.beans.factory.annotation.Autowired
import org.springframework.boot.CommandLineRunner
import org.springframework.boot.autoconfigure.SpringBootApplication
import org.springframework.boot.runApplication
import org.springframework.jdbc.core.JdbcTemplate
import org.springframework.jdbc.core.RowMapper
import java.sql.ResultSet

data class Beer(val id: Int, val name: String, val abv: Double)

@SpringBootApplication
class KotlinJdbcApplication(@Autowired val jdbcTemplate: JdbcTemplate) : CommandLineRunner {
    override fun run(vararg args: String?) {

        //Create table ("IF NOT EXISTS" syntax may not be compatible with some databases):
        jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS favorite_beers(id INT, name VARCHAR(50), abv DOUBLE)")

        //Insert some records:
        jdbcTemplate.execute("INSERT INTO favorite_beers(id, name,abv) VALUES(1, 'Lagunitas IPA', 6.2)")
        jdbcTemplate.execute("INSERT INTO favorite_beers(id, name,abv) VALUES(2, 'Jai Alai', 7.5)")

        //Declare rowmapper to map DB records to collection of Beer entities:
        var rowMapper: RowMapper<Beer> = RowMapper<Beer> { resultSet: ResultSet, rowIndex: Int ->
            Beer(resultSet.getInt("id"), resultSet.getString("name"), resultSet.getDouble("abv"))
        }

        //Query records to print out:
        var results = jdbcTemplate.query("SELECT * FROM favorite_beers", rowMapper)

        println("Original rows:")
        results.forEach { rec -> println(rec) }

        //Insert another record:
        jdbcTemplate.execute("INSERT INTO favorite_beers(id, name,abv) VALUES(3, 'Stella Artois', 5.0)")

        results = jdbcTemplate.query("SELECT * FROM favorite_beers", rowMapper)
        println("After another insert:")
        results.forEach { rec -> println(rec) }

        //Delete record:
        jdbcTemplate.execute("DELETE FROM favorite_beers WHERE ID = 1")

        results = jdbcTemplate.query("SELECT * FROM favorite_beers", rowMapper)
        println("After delete:")
        results.forEach { rec -> println(rec) }
    }
}

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

4. Run

Run the application.  In your IDE’s console, you should see your operation results:

      .   ____          _            __ _ _
     /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
    ( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
     \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
      '  |____| .__|_| |_|_| |_\__, | / / / /
     =========|_|==============|___/=/_/_/_/
     :: Spring Boot ::        (v2.2.2.RELEASE)
    
    Some stuff here..........
    
    Original rows:
    Beer(id=1, name=Lagunitas IPA, abv=6.2)
    Beer(id=2, name=Jai Alai, abv=7.5)
    
    After another insert:
    Beer(id=1, name=Lagunitas IPA, abv=6.2)
    Beer(id=2, name=Jai Alai, abv=7.5)
    Beer(id=3, name=Stella Artois, abv=5.0)
    
    After delete:
    Beer(id=2, name=Jai Alai, abv=7.5)
    Beer(id=3, name=Stella Artois, abv=5.0)
    
    More stuff here..........
Developer Soapbox © 2021