Consuming REST with Spring and Kotlin

Spring 5 came with official support for Kotlin, a JVM language developed by Jetbrains which focuses on code clarity and conciseness. Many web applications today are a mockup of content from other websites, which are usually exposed with a web service. Consuming a web service is really easy when you use Spring’s RestTemplate class. This tutorial is an adaptation of the one found here, which has been modified to use the Kotlin language.

Project Structure

You will want to setup your project with a folder structure that is similar to the one shown in the screenshot below.

consuming_rest

build.gradle

Next you will want to use a dependency management system, either gradle or maven, which will see to the details of downloading your dependencies. We use gradle in this tutorial.

buildscript {
    ext.kotlin_version = '1.2.30'

    repositories {
        mavenCentral()
    }
    dependencies {
        classpath "org.jetbrains.kotlin:kotlin-gradle-plugin:$kotlin_version"
        classpath("org.springframework.boot:spring-boot-gradle-plugin:2.0.1.RELEASE")
    }
}

group 'com.stonesoupprogramming'
version '1.0-SNAPSHOT'

apply plugin: 'kotlin'
apply plugin: 'org.springframework.boot'
apply plugin: 'io.spring.dependency-management'

bootJar {
    baseName = 'gs-consuming-rest'
    version =  '0.1.0'
}

repositories {
    mavenCentral()
}

dependencies {
    compile "org.jetbrains.kotlin:kotlin-stdlib-jdk8:$kotlin_version"
    compile group: 'org.jetbrains.kotlin', name: 'kotlin-reflect', version: '$kotlin_version'
    compile("org.springframework.boot:spring-boot-starter")
    compile("org.springframework:spring-web")
    compile("com.fasterxml.jackson.core:jackson-databind")
}

compileKotlin {
    kotlinOptions.jvmTarget = "1.8"
}
compileTestKotlin {
    kotlinOptions.jvmTarget = "1.8"
}

ConsumingRest.kt

Now we are going to write a simple Kotlin application which will make an HTTP GET request to a server and deserialize the JSON into Kotlin object. We will use two data classes and then write a main function. Here is the code.

package com.stonesoupprogramming

import com.fasterxml.jackson.annotation.JsonIgnoreProperties
import org.springframework.web.client.RestTemplate

/**
 * The following data classes are instantiated by Jackson and converted from JSON to
 * objects. Normally, the class needs to have all of the properties in the JSON, but
 * we can change this by using the ignoreUnknown = true argument
 */
@JsonIgnoreProperties(ignoreUnknown = true)
data class Value(var id: Long = 0, var quote: String = "")

@JsonIgnoreProperties(ignoreUnknown = true)
data class Quote(var type : String = "", var value : Value? = null)

fun main (args : Array){
    //Create a new RestTemplate and use getForObject to make a GET request
    //to the server and return an instance of Quote representing the response
    val quote = RestTemplate().getForObject("http://gturnquist-quoters.cfapps.io/api/random", Quote::class.java)

    //Print the response to the console
    println(quote)
}

Our application begin by declaring two data classes which are Value and Quote. We annotate these classes with @JsonIgnoreProperties which allows the JSON deserializer to skip over JSON properties that are not present in our data classes. Otherwise, we would get a runtime exception. The JSON deserializer uses reflection (java based) to instantiate objects from the data classes so we need some form of a default constructor. We can do this a number of different ways in Kotlin, but I chose to use default values the properties in the data classes.

Next we have a main function. It starts by creating a new RestTemplate object and calling its getForObject method. The getForObject requires a web address and a class of the object to return. Then we call println on the returned Quote object to print the output to the console.

RestTemplate has methods for just about every HTTP verb so while this example only uses GET, you can do POST, PUT, DELETE and other common web requests.

Output

The output will different every time you run the application, but here is what I got when I ran it.

14:47:14.091 [main] WARN org.springframework.http.converter.json.Jackson2ObjectMapperBuilder - For Jackson Kotlin classes support please add "com.fasterxml.jackson.module:jackson-module-kotlin" to the classpath
14:47:14.156 [main] DEBUG org.springframework.web.client.RestTemplate - Created GET request for "http://gturnquist-quoters.cfapps.io/api/random"
14:47:14.247 [main] DEBUG org.springframework.web.client.RestTemplate - Setting request Accept header to [application/json, application/*+json]
14:47:14.321 [main] DEBUG org.springframework.web.client.RestTemplate - GET request for "http://gturnquist-quoters.cfapps.io/api/random" resulted in 200 (OK)
14:47:14.322 [main] DEBUG org.springframework.web.client.RestTemplate - Reading [class com.stonesoupprogramming.Quote] as "application/json;charset=UTF-8" using [org.springframework.http.converter.json.MappingJackson2HttpMessageConverter@cc43f62]
Quote(type=success, value=Value(id=1, quote=Working with Spring Boot is like pair-programming with the Spring developers.))

Conclusion

Here is the source document for this tutorial.

https://spring.io/guides/gs/consuming-rest/

You can get the code at my github at this address: https://github.com/archer920/consuming-rest

Advertisements

Kotlin Scheduling Tasks with Spring Boot

Kotlin is fully interoperable with Spring Boot which makes Spring and Kotlin a perfect companion to one another. Spring brings a high level platform that can be used for making just about any enterprise grade application, while Kotlin offers language features that make your code concise and readable. Both Kotlin and Spring do a great job of reducing boilerplate in your code so that you can write an application quickly and get to the point.

This tutorial is based on Scheduling Tasks found on the Spring website is an adapation of the tutorial for Kotlin. We will be using Kotlin, Spring Boot, and Gradle. You can find the code here.

Project Structure

You should setup your project to use this folder structure.

scheduling_tasks

build.gradle

Here is the full code for your gradle.build file. Notice that will bring in both Kotlin and Spring libraries so that we can build the project.

buildscript {
    ext.kotlin_version = '1.2.30'

    repositories {
        mavenCentral()
    }
    dependencies {
        classpath "org.jetbrains.kotlin:kotlin-gradle-plugin:$kotlin_version"
        classpath "org.springframework.boot:spring-boot-gradle-plugin:2.0.1.RELEASE"
    }
}

group 'com.stonesoupprogramming'
version '1.0-SNAPSHOT'

apply plugin: 'kotlin'
apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'idea'
apply plugin: 'org.springframework.boot'
apply plugin: 'io.spring.dependency-management'

repositories {
    mavenCentral()
}

bootJar {
    baseName = 'gs-scheduling-tasks'
    version =  '0.1.0'
}

sourceCompatibility = 1.8
targetCompatibility = 1.8

dependencies {
    compile "org.springframework.boot:spring-boot-starter"
    compile "org.jetbrains.kotlin:kotlin-stdlib-jdk8:$kotlin_version"
    compile group: 'org.jetbrains.kotlin', name: 'kotlin-reflect', version: '1.2.30'
    testCompile "junit:junit"
}

compileKotlin {
    kotlinOptions.jvmTarget = "1.8"
}
compileTestKotlin {
    kotlinOptions.jvmTarget = "1.8"
}

SchedulingTasks.kt

Here is the Kotlin code followed by an explanation.

package com.stonesoupprogramming.schedulingtasks

import org.slf4j.LoggerFactory
import org.springframework.boot.SpringApplication
import org.springframework.boot.autoconfigure.SpringBootApplication
import org.springframework.scheduling.annotation.EnableScheduling
import org.springframework.scheduling.annotation.Scheduled
import org.springframework.stereotype.Component
import java.time.LocalDateTime
import java.time.format.DateTimeFormatter

/**
 * Mark this class an injectable component so that the Spring environment will create
 * an instance of this class when it starts up.
 */
@Component
class ScheduleTasks {

    private val logger = LoggerFactory.getLogger(ScheduleTasks::class.java)

    /**
     * This @Schedule annotation run every 5 seconds in this case. It can also
     * take a cron like syntax.
     * See https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/scheduling/support/CronSequenceGenerator.html
     */
    @Scheduled(fixedRate = 5000)
    fun reportTime(){
        logger.info("The time is now ${DateTimeFormatter.ISO_LOCAL_TIME.format(LocalDateTime.now())}")
    }
}

@SpringBootApplication
//Required to tell Spring to run tasks marked with @Scheduled
@EnableScheduling
open class Application

fun main(args : Array){
    SpringApplication.run(Application::class.java)
}

When run, you will get this output on your console every five seconds.

2018-04-06 18:51:21.868  INFO 20294 --- [pool-1-thread-1] c.s.schedulingtasks.ScheduleTasks        : The time is now 18:51:21.865
2018-04-06 18:51:26.858  INFO 20294 --- [pool-1-thread-1] c.s.schedulingtasks.ScheduleTasks        : The time is now 18:51:26.858

Explanation

So how does the code work? The ScheduleTasks class is annotaded with @Component, which the Spring environment scans for on start up and instantiates the class. At this point, an instance of ScheduleTasks lives in the ApplicationContent. You will notice that the ScheduleTasks::reportTime function is annotated with @Scheduled which defaults to a fix rate or can use a CRON like syntax.

You can’t annotate a method and expect it to run without turning on scheduling. That is why the Application class is annotated with @EnableScheduling. This will tell Spring to scan all container managed classes and look for the @Scheduled annotation. The Spring environment will do the job of making sure that the methods run at the proper time.

Code

You can get the code for this tutorial at my GitHub: https://github.com/archer920/scheduling-tasks

Sources

https://spring.io/guides/gs/scheduling-tasks/
https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/scheduling/support/CronSequenceGenerator.html

Kotlin JDBC – RowSet Interface

The RowSet interface is a sub-interface of ResultSet and is used provide finer grade control over JDBC result sets. The RowSet interface has its own sub-interfaces that provide different features depending on the type of interface.

RowSet Sub-Interfavces

Interface Brief Description
JdbcRowSet A RowSet that is capable of being used as a JavaBeans component. The JdbcRowSet maintains a connection to the underlying database and makes the ResultSet scrollable and updateable.
CachedRowSet Caches rows in memory, allowing for the application to work on the ResultSet without maintaing an active connection to the database.
WebRowSet : CachedRowSet An extension of CachedRowSet, the WebRowSet provides XML capabilities
JoinRowSet : WebRowSet Extends WebRowSet to provide SQL JOIN capabilities.
FilterRowSet : WebRowSet Extends WebRowSet to provide filtering capabilities

Example Program

Below is an example program that demonstrates how to create an instance of RowSet.

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>OCJP-DB</groupId>
    <artifactId>ocjpdb</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <kotlin.version>1.2.10</kotlin.version>
        <main.class>stonesoupprogramming.MainKt</main.class>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.apache.derby</groupId>
            <artifactId>derby</artifactId>
            <version>10.14.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.jetbrains.kotlin</groupId>
            <artifactId>kotlin-stdlib-jre8</artifactId>
            <version>${kotlin.version}</version>
        </dependency>
        <dependency>
            <groupId>org.jetbrains.kotlin</groupId>
            <artifactId>kotlin-test</artifactId>
            <version>${kotlin.version}</version>
            <scope>test</scope>
        </dependency>

    </dependencies>

    <build>
        <sourceDirectory>src/main/kotlin</sourceDirectory>
        <plugins>
            <plugin>
                <groupId>org.jetbrains.kotlin</groupId>
                <artifactId>kotlin-maven-plugin</artifactId>
                <version>${kotlin.version}</version>
                <executions>
                    <execution>
                        <id>compile</id>
                        <phase>compile</phase>
                        <goals>
                            <goal>compile</goal>
                        </goals>
                    </execution>
                    <execution>
                        <id>test-compile</id>
                        <phase>test-compile</phase>
                        <goals>
                            <goal>test-compile</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <jvmTarget>1.8</jvmTarget>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-jar-plugin</artifactId>
                <version>2.6</version>
                <configuration>
                    <archive>
                        <manifest>
                            <addClasspath>true</addClasspath>
                            <mainClass>${main.class}</mainClass>
                        </manifest>
                    </archive>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>exec-maven-plugin</artifactId>
                <version>1.2.1</version>
                <executions>
                    <execution>
                        <phase>test</phase>
                        <goals>
                            <goal>java</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <mainClass>${main.class}</mainClass>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

EmployeesRowset.kt

package stonesoupprogramming

import javax.sql.rowset.RowSetProvider

fun main (args : Array<String>){
    val rowSet  = RowSetProvider.newFactory().createWebRowSet()

    with(rowSet){
        //Connection information
        url = "jdbc:derby:stonesoup;create=true"
        username = "admin"
        password = "pw"

        //Command to execute against the database
        command = "SELECT * FROM BURGERS.EMPLOYEES"

        //Execute the command
        execute()

        //Output XML to standard out
        writeXml(System.out)
    }
}

Explanation

This program queries a table in the database and prints the XML to the standard out. We get an instance of WebRowSet by calling createWebRowSet() on line 6. Note that if we wanted a different kind of RowSet, we would just use the corresponding method on RowSetProvider. For example, if we wanted a JdbcRowSet, we would use createJdbcRowSet() instead of createWebRowSet().

The result is a RowSet object. Once we have a RowSet object, we start by populating its properties to establish a connection to the database. In this case, we pass a JDBC connection string, a username, and a password. Next, we set the command property with a SQL string.

The SQL is executed when call the execute() method. A connection is established to the database and then the RowSet object is populated with the results. Since we are using a WebRowSet, we can write the results to XML. The example program passes System.out as the output stream and the results appear on the console.

Kotlin JDBC – Savepoints

The JDBC connection object has the ability to create SavePoint objects that are used to rollback a transaction to a specific point in time. One possible use case is providing users the ability to have “Undo” options while working in a database client program. Of course, we can also use SavePoints in Exception handlers or other areas of the program as needed.

SavePoints are used when the connection’s autoCommit property is set to false. We create a SavePoint like so

val bob = connection.setSavePoint("Bob") //Name is optional

Later on, we can pass the SavePoint to the rollback() method on the connection object.

connection.rollback(bob)

Once the connection is rollback to a SavePoint, any work performed on the conncetion after the SavePoint is lost.

Below is an example program that demonstrates using SavePoints.

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>OCJP-DB</groupId>
    <artifactId>ocjpdb</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <kotlin.version>1.2.10</kotlin.version>
        <main.class>stonesoupprogramming.MainKt</main.class>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.apache.derby</groupId>
            <artifactId>derby</artifactId>
            <version>10.14.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.jetbrains.kotlin</groupId>
            <artifactId>kotlin-stdlib-jre8</artifactId>
            <version>${kotlin.version}</version>
        </dependency>
        <dependency>
            <groupId>org.jetbrains.kotlin</groupId>
            <artifactId>kotlin-test</artifactId>
            <version>${kotlin.version}</version>
            <scope>test</scope>
        </dependency>

    </dependencies>

    <build>
        <sourceDirectory>src/main/kotlin</sourceDirectory>
        <plugins>
            <plugin>
                <groupId>org.jetbrains.kotlin</groupId>
                <artifactId>kotlin-maven-plugin</artifactId>
                <version>${kotlin.version}</version>
                <executions>
                    <execution>
                        <id>compile</id>
                        <phase>compile</phase>
                        <goals>
                            <goal>compile</goal>
                        </goals>
                    </execution>
                    <execution>
                        <id>test-compile</id>
                        <phase>test-compile</phase>
                        <goals>
                            <goal>test-compile</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <jvmTarget>1.8</jvmTarget>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-jar-plugin</artifactId>
                <version>2.6</version>
                <configuration>
                    <archive>
                        <manifest>
                            <addClasspath>true</addClasspath>
                            <mainClass>${main.class}</mainClass>
                        </manifest>
                    </archive>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>exec-maven-plugin</artifactId>
                <version>1.2.1</version>
                <executions>
                    <execution>
                        <phase>test</phase>
                        <goals>
                            <goal>java</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <mainClass>${main.class}</mainClass>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

Employees.kt

package stonesoupprogramming

import java.sql.Connection
import java.sql.DriverManager
import java.sql.ResultSet
import java.util.*

fun main(args: Array<String>) {
    val properties = Properties()

    //Populate the properties file with user name and password
    with(properties) {
        put("user", "admin")
        put("password", "pw")
    }

    //Open a connection to the database
    DriverManager
            .getConnection("jdbc:derby:stonesoup;create=true", properties)
            .use { connection ->
                //Set autoCommit to false to manually manage transactions
                connection.autoCommit = false

                createOrTruncateTable(connection)

                //Create an updatable result set
                val rs = connection
                        .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)
                        .executeQuery("SELECT * FROM BURGERS.EMPLOYEES")

                //Populate the table with data
                with(rs) {
                    moveToInsertRow()
                    updateInt("ID", 1);
                    updateString("NAME", "Bob")
                    insertRow()

                    val bob = connection.setSavepoint("Bob")

                    moveToInsertRow()
                    updateInt("ID", 2)
                    updateString("NAME", "Linda")
                    insertRow()

                    val linda = connection.setSavepoint("Linda")

                    moveToInsertRow()
                    updateInt("ID", 3)
                    updateString("NAME", "Tina")
                    insertRow()

                    val tina = connection.setSavepoint("Tina")

                    print("Enter Bob, Linda, or Tina => ")
                    val choice = readLine()

                    when (choice) {
                        "Bob" -> connection.rollback(bob)
                        "Linda" -> connection.rollback(linda)
                        "Tina" -> connection.rollback(tina)
                    }
                }
                //Commit the transaction
                connection.commit()


                //Read only queries are still transactions
                val rsq = connection
                        .createStatement()
                        .executeQuery("SELECT * FROM BURGERS.EMPLOYEES")
                with(rsq) {
                    while (next()) {
                        println("${getInt("ID")}\t${getString("NAME")}")
                    }
                }
                //So we need to commit this query also even though it doesn't change anything
                connection.commit()
            }
}

private fun createOrTruncateTable(connection: Connection) {
    val metaData = connection.metaData
    if (!metaData.getTables(null, "BURGERS", "EMPLOYEES", null).next()) {
        connection
                .createStatement()
                .executeUpdate("CREATE TABLE BURGERS.EMPLOYEES (ID INT PRIMARY KEY, NAME VARCHAR(255))")
    } else {
        connection
                .createStatement()
                .executeUpdate("TRUNCATE TABLE BURGERS.EMPLOYEES")
    }
    connection.commit()
}

Explanation

Our program begins by establishing a connection, preparing a table, and creating an updatable ResultSet. We start inserting into the table beginning on line 32. Line 38 is where we create our first SavePoint, after inserting Bob into the table. Once we have the bob SavePoint established, we move on and insert Linda. Linda also gets a SavePoint (line 45), followed by Tina (line 52).

The user is present with a choice on line 55. When they enter Bob, the connection is rolled back to Bob, meaning that neither Linda or Tina are inserted into the database. When the user picks Linda, the connection is rolled back to the linda SavePoint, meaning that Bob and Linda are inserted into the database, but not Tina. If Tina is picked, then all three employees are inserted into the database. Line 64 commits the transaction and the inserts are performed into the database.

Kotlin JDBC – Rollback Transactions

JDBC has the ability rollback transactions. This example shows how to rollback a transaction in the case of an exception. Exception rollbacks are a common pattern because in many cases, committing a transaction after an error can leave the database in an inconsistent state. Let’s take a look at a short example of how to rollback a transaction.

connection.autoCommit = false

createOrTruncateTable(connection)

//Create an updatable result set
val rs = connection
         .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)
         .executeQuery("SELECT * FROM BURGERS.EMPLOYEES")

try {
    /* Lines 32-46 omitted */

    //Commit the previous transaction (lines 32-46)
    connection.commit()

    //Now let's do an insert but have it fail
    with(rs){
        moveToInsertRow()

        updateInt("ID", 3)
        updateString("NAME", "Tina")

        insertRow()
    }
    throw Exception("Simulated")
} catch (e: Exception){
    println("Caught simulated exception. Rolling back...")

    //We can rollback the current transaction. Tina will never
    //get inserted into the database
    connection.rollback()
}

The above code fragement uses an expanded try-catch block. At the start of the code fragement, turn off autoCommit on the connection object and create an updatable ResultSet that let’s us insert rows into the database table. There is an ommitted portion of code that inserts some rows into the database.

Then we commit the first transaction. So far so good. The rows are entered cleanly into the database. Then our example continues by inserting another record into the table. Rather than committing the transaction, we instead throw an Exception to act as if something went wrong with the insertion.

The catch block found at the end of the code fragement shows what to do when an exception is thrown in the middle of a transaction. In our example, we notify the user that we are rolling back the changes. Then we call rollback() on the connection object. Rollback() resets the transaction and the program can act as if the last transaction never happened.

Complete Example

Below is a complete Kotlin program that shows the demonstration code in its entirety.

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>OCJP-DB</groupId>
    <artifactId>ocjpdb</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <kotlin.version>1.2.10</kotlin.version>
        <main.class>stonesoupprogramming.MainKt</main.class>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.apache.derby</groupId>
            <artifactId>derby</artifactId>
            <version>10.14.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.jetbrains.kotlin</groupId>
            <artifactId>kotlin-stdlib-jre8</artifactId>
            <version>${kotlin.version}</version>
        </dependency>
        <dependency>
            <groupId>org.jetbrains.kotlin</groupId>
            <artifactId>kotlin-test</artifactId>
            <version>${kotlin.version}</version>
            <scope>test</scope>
        </dependency>

    </dependencies>

    <build>
        <sourceDirectory>src/main/kotlin</sourceDirectory>
        <plugins>
            <plugin>
                <groupId>org.jetbrains.kotlin</groupId>
                <artifactId>kotlin-maven-plugin</artifactId>
                <version>${kotlin.version}</version>
                <executions>
                    <execution>
                        <id>compile</id>
                        <phase>compile</phase>
                        <goals>
                            <goal>compile</goal>
                        </goals>
                    </execution>
                    <execution>
                        <id>test-compile</id>
                        <phase>test-compile</phase>
                        <goals>
                            <goal>test-compile</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <jvmTarget>1.8</jvmTarget>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-jar-plugin</artifactId>
                <version>2.6</version>
                <configuration>
                    <archive>
                        <manifest>
                            <addClasspath>true</addClasspath>
                            <mainClass>${main.class}</mainClass>
                        </manifest>
                    </archive>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>exec-maven-plugin</artifactId>
                <version>1.2.1</version>
                <executions>
                    <execution>
                        <phase>test</phase>
                        <goals>
                            <goal>java</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <mainClass>${main.class}</mainClass>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

Employees.kt

package stonesoupprogramming

import java.sql.Connection
import java.sql.DriverManager
import java.sql.ResultSet
import java.util.*

fun main(args: Array<String>) {
    val properties = Properties()

    //Populate the properties file with user name and password
    with(properties) {
        put("user", "admin")
        put("password", "pw")
    }

    //Open a connection to the database
    DriverManager
            .getConnection("jdbc:derby:stonesoup;create=true", properties)
            .use { connection ->
                //Set autoCommit to false to manually manage transactions
                connection.autoCommit = false

                createOrTruncateTable(connection)

                //Create an updatable result set
                val rs = connection
                        .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)
                        .executeQuery("SELECT * FROM BURGERS.EMPLOYEES")

                try{
                    //Populate the table with data
                    with(rs) {
                        moveToInsertRow()

                        updateInt("ID", 1);
                        updateString("NAME", "Bob")

                        insertRow()
                        moveToInsertRow()

                        updateInt("ID", 2)
                        updateString("NAME", "Linda")

                        insertRow()
                    }
                    //Commit the transaction
                    connection.commit()

                    //Now let's do an insert but have it fail
                    with(rs){
                        moveToInsertRow()

                        updateInt("ID", 3)
                        updateString("NAME", "Tina")

                        insertRow()
                    }
                    throw Exception("Simulated")

                } catch (e: Exception){
                    println("Caught simulated exception. Rolling back...")

                    //We can rollback the current transaction. Tina will never
                    //get inserted into the database
                    connection.rollback()
                }


                //Read only queries are still transactions
                val rsq = connection
                        .createStatement()
                        .executeQuery("SELECT * FROM BURGERS.EMPLOYEES")
                with(rsq) {
                    while (next()) {
                        println("${getInt("ID")}\t${getString("NAME")}")
                    }
                }
                //So we need to commit this query also even though it doesn't change anything
                connection.commit()
            }
}

private fun createOrTruncateTable(connection: Connection) {
    val metaData = connection.metaData
    if (!metaData.getTables(null, "BURGERS", "EMPLOYEES", null).next()) {
        connection
                .createStatement()
                .executeUpdate("CREATE TABLE BURGERS.EMPLOYEES (ID INT PRIMARY KEY, NAME VARCHAR(255))")
    } else {
        connection
                .createStatement()
                .executeUpdate("TRUNCATE TABLE BURGERS.EMPLOYEES")
    }
    connection.commit()
}

Kotlin JDBC – Transactions

There are plenty of situations where databases need to execute a series of SQL statements together to maintain the integrity of the data. In such situations, either all of the statements must succeed or none of them must succeed. Bank accounts are a good example.

In many cases, a customer may have a savings account and a checking account. If a customer moves money from the savings account into the checking account, then two updates are required on two tables. If the update only succeeds on the savings account but fails on the checking account, then the customer’s money will disappear. That is less than ideal for the customer, so either both the checking and savings account tables must update, or the entire operation must fail.

Grouping SQL statements together is known as a transaction. We can manually manage our transactions in JDBC by setting the autoCommit property to false on the connection object. After we set autoCommit to false, we have to make sure to call commit() on the connection object after each transaction. Below is a simple Kotlin program that demonstrates transactions.

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>OCJP-DB</groupId>
    <artifactId>ocjpdb</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <kotlin.version>1.2.10</kotlin.version>
        <main.class>stonesoupprogramming.MainKt</main.class>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.apache.derby</groupId>
            <artifactId>derby</artifactId>
            <version>10.14.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.jetbrains.kotlin</groupId>
            <artifactId>kotlin-stdlib-jre8</artifactId>
            <version>${kotlin.version}</version>
        </dependency>
        <dependency>
            <groupId>org.jetbrains.kotlin</groupId>
            <artifactId>kotlin-test</artifactId>
            <version>${kotlin.version}</version>
            <scope>test</scope>
        </dependency>

    </dependencies>

    <build>
        <sourceDirectory>src/main/kotlin</sourceDirectory>
        <plugins>
            <plugin>
                <groupId>org.jetbrains.kotlin</groupId>
                <artifactId>kotlin-maven-plugin</artifactId>
                <version>${kotlin.version}</version>
                <executions>
                    <execution>
                        <id>compile</id>
                        <phase>compile</phase>
                        <goals>
                            <goal>compile</goal>
                        </goals>
                    </execution>
                    <execution>
                        <id>test-compile</id>
                        <phase>test-compile</phase>
                        <goals>
                            <goal>test-compile</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <jvmTarget>1.8</jvmTarget>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-jar-plugin</artifactId>
                <version>2.6</version>
                <configuration>
                    <archive>
                        <manifest>
                            <addClasspath>true</addClasspath>
                            <mainClass>${main.class}</mainClass>
                        </manifest>
                    </archive>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>exec-maven-plugin</artifactId>
                <version>1.2.1</version>
                <executions>
                    <execution>
                        <phase>test</phase>
                        <goals>
                            <goal>java</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <mainClass>${main.class}</mainClass>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

Employees.kt

package stonesoupprogramming

import java.sql.Connection
import java.sql.DriverManager
import java.sql.ResultSet
import java.util.*

fun main(args: Array<String>) {
    val properties = Properties()

    //Populate the properties file with user name and password
    with(properties) {
        put("user", "admin")
        put("password", "pw")
    }

    //Open a connection to the database
    DriverManager
            .getConnection("jdbc:derby:stonesoup;create=true", properties)
            .use { connection ->
                //Set autoCommit to false to manually manage transactions
                connection.autoCommit = false

                createOrTruncateTable(connection)

                //Create an updatable result set
                val rs = connection
                        .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)
                        .executeQuery("SELECT * FROM BURGERS.EMPLOYEES")

                //Populate the table with data
                with(rs) {
                    moveToInsertRow()

                    updateInt("ID", 1);
                    updateString("NAME", "Bob")

                    insertRow()
                    moveToInsertRow()

                    updateInt("ID", 2)
                    updateString("NAME", "Linda")

                    insertRow()
                }
                //Commit the transation
                connection.commit()

                //Read only queries are still transactions
                val rsq = connection
                        .createStatement()
                        .executeQuery("SELECT * FROM BURGERS.EMPLOYEES")
                with(rsq) {
                    while (next()) {
                        println("${getInt("ID")}\t${getString("NAME")}")
                    }
                }
                //So we need to commit this query also even though it doesn't change anything
                connection.commit()
            }
}

private fun createOrTruncateTable(connection: Connection) {
    val metaData = connection.metaData
    if (!metaData.getTables(null, "BURGERS", "EMPLOYEES", null).next()) {
        connection
                .createStatement()
                .executeUpdate("CREATE TABLE BURGERS.EMPLOYEES (ID INT PRIMARY KEY, NAME VARCHAR(255))")
    } else {
        connection
                .createStatement()
                .executeUpdate("TRUNCATE TABLE BURGERS.EMPLOYEES")
    }
    connection.commit()
}

Let’s begin with the explanation. We start by connecting to the database (lines 12-20), then we set the connection’s autoCommit property to false (line 22). From this point on, we are responsible for managing our database transactions. Lines 63-75 create a table for us to work on in the database. It will either make a brand new table, or truncate an existing one.

We populate our table with data on lines 27-45 by creating an updatable ResultSet object and using it’s update methods. The ResultSet will prepare SQL insert statements. However, none of the rows get inserted into the database until line 47 when we call commit() on the connection object.

It’s worth noting that we have to call commit() on read only transactions also. So on lines 50-57, we read from the database to verify the inserts were performed. However, notice that on line 59, we still call commit() even though we haven’t actually changed anything. This is because the underlying database still considers a read only statement to be a transaction.

Kotlin JDBC – Create a Table

We can use JDKs JDBC API to create a table in a database. We create tables by using the Statements::executeUpdate method and pass the proper SQL command along to the database. Here is an example of how to do this in Kotlin.

private fun createTable(connection: Connection) {
    //SQL statement to create a table
    val sql = """
         CREATE TABLE BURGERS.MENU (
            ID int primary key,
            ITEM varchar(255),
            PRICE float)
        """.trimMargin()
    connection.createStatement().executeUpdate(sql)
}

The first thing to do is to prepare a SQL String. Kotlin’s triple quoted strings “”” are very useful for making such Strings. Next, we enter a with() function call on the connection object and call createStatement() to get an instance of Statement. The Statement has an executeUpdate(String) method that accepts our SQL string. Once the executeUpdate() method returns, our database has a new table.

Example Program

Below is a Kotlin program that includes creating a table in the database.

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>OCJP-DB</groupId>
    <artifactId>ocjpdb</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <kotlin.version>1.2.0</kotlin.version>
        <main.class>stonesoupprogramming.MainKt</main.class>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.apache.derby</groupId>
            <artifactId>derby</artifactId>
            <version>10.14.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.jetbrains.kotlin</groupId>
            <artifactId>kotlin-stdlib-jre8</artifactId>
            <version>${kotlin.version}</version>
        </dependency>
        <dependency>
            <groupId>org.jetbrains.kotlin</groupId>
            <artifactId>kotlin-test</artifactId>
            <version>${kotlin.version}</version>
            <scope>test</scope>
        </dependency>

    </dependencies>

    <build>
        <sourceDirectory>src/main/kotlin</sourceDirectory>
        <plugins>
            <plugin>
                <groupId>org.jetbrains.kotlin</groupId>
                <artifactId>kotlin-maven-plugin</artifactId>
                <version>${kotlin.version}</version>
                <executions>
                    <execution>
                        <id>compile</id>
                        <phase>compile</phase>
                        <goals>
                            <goal>compile</goal>
                        </goals>
                    </execution>
                    <execution>
                        <id>test-compile</id>
                        <phase>test-compile</phase>
                        <goals>
                            <goal>test-compile</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <jvmTarget>1.8</jvmTarget>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-jar-plugin</artifactId>
                <version>2.6</version>
                <configuration>
                    <archive>
                        <manifest>
                            <addClasspath>true</addClasspath>
                            <mainClass>${main.class}</mainClass>
                        </manifest>
                    </archive>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>exec-maven-plugin</artifactId>
                <version>1.2.1</version>
                <executions>
                    <execution>
                        <phase>test</phase>
                        <goals>
                            <goal>java</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <mainClass>${main.class}</mainClass>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

BurgerMenu.kt

package stonesoupprogramming

import java.sql.Connection
import java.sql.DriverManager
import java.sql.ResultSet
import java.util.*

private const val SCHEMA = "BURGERS"
private const val TABLE = "MENU"

fun main(args: Array<String>) {
    val properties = Properties()

    //Populate the properties file with user name and password
    with(properties) {
        put("user", "admin")
        put("password", "pw")
    }

    //Open a connection to the database
    DriverManager
            .getConnection("jdbc:derby:stonesoup;create=true", properties)
            .use { connection ->
                    prepareTable(connection)
                    insertItems(connection)
                    queryRows(connection)
                    updatePrice(connection, 6.95)

                    println("\nAfter update")
                    queryRows(connection)

                    println("\nAfter delete")
                    deleteRows(connection, listOf(1, 3, 5, 7))
                    queryRows(connection)
            }
}

fun deleteRows(connection: Connection, pks: List<Int>) {
    //Create an updatable Statement (using ResultSet.CONCUR_UPDATABLE)
    val rs = connection
            .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)
            .executeQuery("SELECT * FROM BURGERS.MENU WHERE ID IN (${pks.joinToString(", ")})")

    while(rs.next()){
        //Delete each row in the ResultSet
        rs.deleteRow()
    }
}

fun updatePrice(connection: Connection, price: Double) {
    val sql = "SELECT * FROM $SCHEMA.$TABLE"
    val rs = connection.createStatement(
            ResultSet.TYPE_SCROLL_SENSITIVE,
            ResultSet.CONCUR_UPDATABLE) //This is required to make the ResultSet updatable
            .executeQuery(sql)
    while(rs.next()){
        with(rs) {
            updateDouble("PRICE", price)
            updateRow()
        }
    }
}

private fun queryRows(connection: Connection) {
    val sql = "SELECT * FROM $SCHEMA.$TABLE"
    val rs = connection.createStatement().executeQuery(sql)
    while (rs.next()) {
        println("ID: ${rs.getInt("ID")}\t" +
                "PRICE: $${rs.getDouble("PRICE")}\t" +
                "NAME: ${rs.getString("ITEM")}")
    }
}

private fun insertItems(connection: Connection) {
    insertRow(connection, 1, "'New Bacon-ings'", 5.95)
    insertRow(connection, 2, "'Chorizo Your Own Adventure Burger'", 5.95)
    insertRow(connection, 3, "'Not If I Can Kelp It Burger'", 5.95)
    insertRow(connection, 4, "'The Longest Chard Burger'", 5.95)
    insertRow(connection, 5, "'Peas and Thank You Burger'", 5.95)
    insertRow(connection, 6, "'Cole came, cole slaw, cole conquered burger'", 5.95)
    insertRow(connection, 7, "'Chili Wonka Burger'", 5.95)
    insertRow(connection, 8, "'The Clear and Present Ginger Burger'", 5.95)
}

private fun insertRow(connection: Connection, id: Int, name: String, price: Double) {
    //Obtain an updatable ResultSet object (ResultSet.CONCUR_UPDATABLE)
    val resultSet = connection
            .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)
            .executeQuery("SELECT * FROM BURGERS.MENU")

    with(resultSet){
        //Move the ResultSet to the insert row
        moveToInsertRow()

        //Populate each column using the relevant update method
        updateInt("ID", id)
        updateString("ITEM", name)
        updateDouble("PRICE", price)

        //Now insert the row
        insertRow()
    }
}

private fun prepareTable(connection: Connection) {
    val metaData = connection.metaData
    val rs = metaData.getTables(null, SCHEMA, TABLE, null)

    if (!rs.next()) {
        createTable(connection)
    } else {
        truncateTable(connection)
    }
}

private fun truncateTable(connection: Connection) {
    val sql = "TRUNCATE TABLE $SCHEMA.$TABLE"
    with (connection) {
        createStatement().execute(sql)
        commit()
    }
}

private fun createTable(connection: Connection) {
    //SQL statement to create a table
    val sql = """
         CREATE TABLE BURGERS.MENU (
            ID int primary key,
            ITEM varchar(255),
            PRICE float)
        """.trimMargin()
    connection.createStatement().executeUpdate(sql)
}