Kotlin is able to use JDK’s JDBC APIs to connect to a database. This post provides a brief tutorial to connect to an embedded Apache Derby database using JDBC and Kotlin.

pom.xml

JDBC requires the database drivers to be present on the classpath. We will use Maven to handle our dependencies. Here is the pom.xml used in the project.

<?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>

Kotlin Code

Once we have configured our dependencies, we can write our Kotlin code to connect to the database.

package stonesoupprogramming

import java.sql.DriverManager
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 {
        println("Connected to the DB")
    }

    println("Connection closed")
}

Explanation

According to the Apache Derby documentation, we begin by creating a properties object and providing it with a user name and password. Our example creates and populates a Properties object on lines 7-13 for demonstration purposes, but ideally, we would read our properties from a properties file, command line arguments, or some other source rather than coding user name and passwords directly.

Line 16 creates a database connection by calling DriverManager.getConnection. The getConnection method needs a jdbc connection string, which is specific to each database, and the properties object we created earlier. If the connection is successful, the method will return a connection object that is used to work with the database. Otherwise, it throws an exception.

We make use of the use extension function on line 16 also. Database connections consume system resources so we are required to close them manually. The use function sees to closing the connection when we are finished with it.