Kotlin JDBC – Insert Row with ResultSet

The ResultSet interface found in the Java JDBC API is capable of inserting records into a database table. The post demonstrates how to insert rows into a database table using JDBC and Kotlin.

Let’s suppose we the following database table called Menu with the following columns.

Column Name Type
ID INT (PK)
ITEM VARCHAR(255)
PRICE FLOAT

Our goal is to insert new rows into this table using ResultSet. Here is the Kotlin code that accomplishes the goal followed by an explanation.

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()
    }
}

The code sample begins by creating a ResultSet object that is able to update the result set. We do this by calling Connection::createStatement() and passing ResultSet.CONCUR_UPDATABLE to the second argument. Once we have a Statement object, we can call executeQuery and passing a SQL query that returns a result set.

Once we have an instance of ResultSet, we can begin adding records to the table. First we need to move the row pointer to insert row by calling ResultSet::moveToInsertRow() (line 9). After we have moved to the insert row, we can populate our database columns using the relevant update methods (lines 12-14). There is one update method for each data type and they are overloaded to use either column indexes or column names.

Once we have finished updating the columns, we can insert the row. We do this by calling ResultSet::insertRow(). The call to insertRow() will attempt to write the record to the datgbase table. The operation will throw a SQLException if the call to insertRow() fails to write the row into the database table.

Example Program

Here is an example Kotlin program that shows how to insert rows into a database table.

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)
            }
}

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 $SCHEMA.$TABLE (
            ID int primary key,
            ITEM varchar(255),
            PRICE float)
        """.trimMargin()

    with(connection) {
        //Get and instance of statement from the connection and use
        //the execute() method to execute the sql
        createStatement().execute(sql)

        //Commit the change to the database
        commit()
    }
}
Advertisements

Kotlin JDBC – Update Rows with ResultSet

The ResultSet interface obtained from Statement::executeQuery(String) provides the ability to update individual rows and columns in a database table. Once we have created an updatable ResultSet, we can use the provided update methods to update the row by specifying the column name and the new value. There is an update method for each type of object, so we have updateDouble, updateString, etc.

Let’s suppose we have the following database table called Menu.

ID PRICE NAME
1 5.95 New Bacon-ings
2 5.95 Chorizo Your Own Adventure Burger

Our goal is to update the PRICE column in all records in the table. Here’s how we would update each PRICE in Kotlin.

val sql = "SELECT * FROM MENU"
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) {
        //Update the value of price
        updateDouble("PRICE", 6.95)
        //Commit the change to the DB
        updateRow()
    }
}

Our first job is the create an updatable ResultSet object. We do this by passing ResultSet.CONCUR_UPDATABLE the second parameter of Connection::createStatement. The first first paramater is also required, but offers flexibility. I chose ResultSet.TYPE_SCROLL_SENSITIVE but there are other ones that are available to the developer.

We will get the following exception if we forget ResultSet.CONCUR_UPDATABLE

Caused by: ERROR XJ083: 'updateDouble' not allowed because the ResultSet is not an updatable ResultSet. 
	at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
	at org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsForTransportAcrossDRDA(Unknown Source)
	... 12 more

Once we create the proper type of Statement and call it’s executeQuery method, we will have an updatable ResultSet object. The ResultSet object has a variety of update methods that accept both the column name or the column index along with the type of data we are updating. In other words, there is an updateDouble(Int, Double) and an updateDouble(String, Double), updateInt(Int, Int) updateInt(String, Int), etc. We use these methods to update each cell in the database table.

Once we have finished updating a value, we must call the udpateRow() method before moving on to the next row in the result set. If we fail to do so, the changes will not get written to the database. Once updateRow() is called, the changes are persisted to the database and we can move onto the next row in the result set.

Example Program

Below is a complete example program that demonstrates how to update rows in a database table.

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)
            }
}

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) {
    val sql = "INSERT INTO $SCHEMA.$TABLE VALUES ($id, $name, $price)"
    with(connection) {
        createStatement().execute(sql)
        commit()
    }
}

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 $SCHEMA.$TABLE (
            ID int primary key,
            ITEM varchar(255),
            PRICE float)
        """.trimMargin()

    with(connection) {
        //Get and instance of statement from the connection and use
        //the execute() method to execute the sql
        createStatement().execute(sql)

        //Commit the change to the database
        commit()
    }
}

Kotlin JDBC Create, Insert, Query, and Truncate Tables

Kotlin works with JDK’s JDBC API. Once we obtain a connection to the database, we can get an instance of Statement. The Statement interface lets us work directly with the database and more importantly, allows us to send queries to the database. This post demonstrates how to use Statement to create a table, insert rows into it, query the table, and truncate it.

Create a Table

Our first operation is to create a table.

fun createTable(connection: Connection, scheme : String, table : String) {
    //SQL statement to create a table
    val sql = """
         CREATE TABLE $schema.$table (
            ID int primary key,
            ITEM varchar(255),
            PRICE float)
        """.trimMargin()

    with(connection) {
        //Get and instance of statement from the connection and use
        //the execute() method to execute the sql
        createStatement().execute(sql)

        //Commit the change to the database
        commit()
    }
}

Kotlin’s triple quoted strings come in handy when writing SQL code. In this example, we use Kotlin’s string template feature to insert the schema and table name. The rest of the SQL defines the columns and the data types used in each column. Next, we obtain an instance of Statement by calling connection.createStatement(). The Statement object has an execute() method which takes a SQL string. Finally, we call commit() on the connection to commit the changes to the DB.

Truncate Table

We can also use the Statement interface to truncate a table.

 
fun truncateTable(connection: Connection, schema : String, table : String) {
    val sql = "TRUNCATE TABLE $schema.$table"
    with (connection) {
        createStatement().execute(sql)
        commit()
    }
}

The workflow for truncating a table is the same as creating one. We define a string of SQL. The we obtain an instance of Statement using createStatement() and then pass the SQL to the execute() method on Statement. Then we call commit() on the connection.

Insert Rows

The Statement interface may also be used to insert rows into a table.

fun insertRow(connection: Connection, schema : String, table : String, id: Int, name: String, price: Double) {
    val sql = "INSERT INTO $schema.$table VALUES ($id, $name, $price)"
    with(connection) {
        createStatement().execute(sql)
        commit()
    }
}

As in all other cases, we can easily use Kotlin’s String templating features to easily build a SQL string. Once again, we use Statement’s execute() method and then commit the changes when the method returns.

Query a Table

Our final example involves using the Statement object to query a table.

fun queryRows(connection: Connection, schema : String, table : String) {
    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")}")
    }
}

In this case, we need to use the executeQuery() method found on Statement. The executeQuery() returns a ResultSet object, which is used to navigate the records returned from the query. Here are a few of the more useful methods used for navigation.

beforeFirst() Move the cursor to the first row in the result set
afterLat() Navigate to tend of the result set
absolute(rowNumber : Int) : Boolean Move to the absolute position of the result set specified by index
relative(rowNumber : Int) : Boolean Move the cursor relatively
next() : Boolean Move to the next row
previous() : Boolean Move to the previous row

Once we have moved our result set ot the proper row, we can use it to retreive the data from the columns. We have methods such as getString(Int), getInt(Int), etc. that returns values as their correct data types specified by column index number (1 based). All of these methods also have overloaded methods that allow using the column name rather than the index of the column.

It’s worth noting that in the example above, we use the next() method on result set to setup a while loop that terminates when next() returns false. This allows use to navigate through the entire result set one row at a time.

Putting it Together

Below is an entire Kotlin program that demonstrates the above concepts on an embedded derby 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.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)
            }
}

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) {
    val sql = "INSERT INTO $SCHEMA.$TABLE VALUES ($id, $name, $price)"
    with(connection) {
        createStatement().execute(sql)
        commit()
    }
}

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 $SCHEMA.$TABLE (
            ID int primary key,
            ITEM varchar(255),
            PRICE float)
        """.trimMargin()

    with(connection) {
        //Get and instance of statement from the connection and use
        //the execute() method to execute the sql
        createStatement().execute(sql)

        //Commit the change to the database
        commit()
    }
}

Kotlin JDBC Connection

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.

Kotlin Watch Service

The java.nio.file package has a WatchService class that is used to watch for changes in a folder. This is a Kotlin program that demonstrates how to create a watch service that monitors a folder for changes and reports the changes.

package ch9.files

import java.nio.file.Path
import java.nio.file.Paths
import java.nio.file.StandardWatchEventKinds
import java.nio.file.WatchService

private fun prompt(msg : String) : String {
    print("$msg => ")
    return readLine() ?: ""
}

private fun Path.watch() : WatchService {
    //Create a watch service
    val watchService = this.fileSystem.newWatchService()

    //Register the service, specifying which events to watch
    register(watchService, StandardWatchEventKinds.ENTRY_CREATE, StandardWatchEventKinds.ENTRY_MODIFY, StandardWatchEventKinds.OVERFLOW, StandardWatchEventKinds.ENTRY_DELETE)

    //Return the watch service
    return watchService
}

fun main(args : Array<String>){
    val folder = prompt("Enter a folder to watch")
    val path = Paths.get(folder)

    val watcher = path.watch()
    println("Press ctrl+c to exit")

    while(true){
        //The watcher blocks until an event is available
        val key = watcher.take()

        //Now go through each event on the folder
        key.pollEvents().forEach { it ->
            //Print output according to the event
            when(it.kind().name()){
                "ENTRY_CREATE" -> println("${it.context()} was created")
                "ENTRY_MODIFY" -> println("${it.context()} was modified")
                "OVERFLOW" -> println("${it.context()} overflow")
                "ENTRY_DELETE" -> println("${it.context()} was deleted")
            }
        }
        //Call reset() on the key to watch for future events
        key.reset()
    }
}

Here is what it looked like when run on my machine.

Enter a folder to watch => /users/stonesoup/downloads
Press ctrl+c to exit
bob.json was created
bob.json was deleted

While the program was running, I created a bob.json file in my Downloads folder and then deleted it.

Explanation

The first task is to register the Watch Service. The example program has an Path.watch() extension function that encapsulates creating a watch service, registering it, and then returning it to the caller. The Watch Service is obtained from Path.fileSystem.newWatchService() method (line 15). The next step is to register the Watch Service using the Path.register() method (line 18). When registering the Watch Service, we can pass in number of StandWatchEventKinds to tell the Watch Service what to watch.

The main method collects a path from the user (line 25), creates a Path object from the input (line 26), and then registers the Watch Service (line 28). At this point, we enter into an infinite loop and watch the target folder for changes.

The first action in the loop is watcher.take() (line 33). The take() method blocks the thread until an event happens. When a monitored watch event takes place, the take() method will return a WatchKey(). The WatchKey() holds any number of Watch Events that have happened since the last watch cycle.

The example program calls WatchKey.pollEvents().forEach and goes through each watch event (line 36). It uses the WatchEvent.kind().name property (line 38-43) to print output according to each event. Notice how the program combines a when() function to react to each kind of watch event (lines 38-43). When we are done processing all events, we call reset() on the WatchKey() so that the program can wait for the next event. We can also end the WatchService by calling cancel() on the WatchKey.

References

https://docs.oracle.com/javase/8/docs/api/?java/io/File.html

Kotlin Glob

Glob is a pattern that is used to match files to a pattern. For example, suppose we wish to match all Kotlin files on our file system, we would use the syntax “glob:*.kt”. The following demo program walks through a user-supplied start path and matches all files according to the user-supplied glob pattern.

package ch9.files

import java.nio.file.FileSystems
import java.nio.file.Files
import java.nio.file.Path
import java.nio.file.Paths
import java.util.stream.Collectors.toList

private fun prompt(msg : String) : String {
    print("$msg => ")
    return readLine() ?: ""
}

fun main(args : Array<String>){
    val start = prompt("Enter a start path")
    val glob = prompt("Enter a glob pattern")

    //Object a matcher object from the supplied Glob pattern
    val matcher = FileSystems.getDefault().getPathMatcher(glob)

    val path = Paths.get(start)
    //Walk the file system
    Files.walk(path)
            //Filter out anything that doesn't match the glob
            .filter { it : Path? -> it?.let { matcher.matches(it.fileName) } ?: false }
            //Collect to a list
            .collect(toList())
            //Print to the console
            .forEach({ it -> println("Found ${it.fileName}") })
}

Here is an example run of the program.

Enter a start path => /users/stonesoup
Enter a glob pattern => glob:*.kt
Found CachingTutorialApplicationTests.kt
Found CachingTutorialApplication.kt
Found ExposedTransactionManagerTest.kt
Found SpringTransactionManager.kt
Found SamplesDao.kt
Found SamplesSQL.kt
...continued

Detailed Explanation

The program asks the user for a start path (line 15) and a glob syntax (line 16). The program supports the glob patterns in the table below.

Pattern Description
* Matches anything
** Matches anything even accross directories
? The ? mark matches any single character
[xyz] Matches any character inside of [ ]. In this example, it’s x, y, or z
[0-5], [a-z] Matches a range. In this case, it’s 0-5 or the letters a-z
{xyz, abc} Matches one of the two patterns. In this case, either xyz or abc

Once the user has supplied a valid path and glob pattern, the program calls Files.walk to walk through the file system. Using Java 8’s Streaming API, we filter all items that do not match the pattern (line 25) using the matcher object that was returned on line 19. The results are collected into a list and printed to the console.

References

https://docs.oracle.com/javase/8/docs/api/?java/io/File.html
https://docs.oracle.com/javase/8/docs/api/?java/io/File.html

Kotlin Walk a File Tree

The java.nio.file.Files class has a walk method that returns a Stream used to walk a file tree. The example program lists out the 5 largest files given a starting path and demonstrates how to easily walk through a file system in Kotlin.

package ch9.files

import java.nio.file.Files
import java.nio.file.Path
import java.nio.file.Paths
import java.util.stream.Collectors.toList

private fun Path.size() : Long {
    return try {
        Files.size(this)
    } catch (e : Exception){
        -1
    }
}

fun main(args: Array<String>){
    if(args.isNotEmpty()){
        val path = Paths.get(args[0])

        //Open a Stream object
        Files.walk(path)
                //Sort by size
                .sorted { lhs : Path?, rhs : Path? -> compareValues(lhs?.size() ?: -1, rhs?.size() ?: -1)}
                //Collect the result into a list
                .collect(toList())
                //Now reverse the list so that the largest file is first
                .reversed()
                .stream()
                //Open another stream and collect up to 5 files
                .limit(5)
                //Now print the results
                .forEach({it -> println("${it.fileName} \t ${it.size()}") })
    } else {
        println("Usage: start path")
    }
}

Detailed Explanation

The program parses the command line arguments and returns a Path object (line 18). The Path object is passed to the Files.walk() method on line 21. The walk() method returns a Stream object that opens up all of the operations found on a Java 8 Stream. In our case, we wish to sort all files by their size (using the Path.size() extension function found on lines 8-14) on line 23. The result is collected into a list on line 25.

By default, our files are sorted smallest to largest. We can either rework the comparator used on line 23 to reverse sort or just call the reversed() method on the list object. The former idea is most likely more performant but later is very readable. Finally, since we are interested in the five largest files, we open another Stream on the list and limit it to 5 elements. The final operation is to call forEach on the list and print the file name and its size.

References

https://docs.oracle.com/javase/8/docs/api/?java/io/File.html