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