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.

Advertisements

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

Kotlin JDBC – Use ResultSet to Delete Rows

The ResultSet interface found in JDK’s JDBC API may be used to delete rows in a database individually. Here is a Kotlin example that deletes rows from a Burgers.Menu table in the database that match the supplied primary keys.

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

The crux of the operation is found on line 9 with the call to ResultSet::deleteRow(). The method call will simply delete the row from the database. However, it’s important to have the proper ResultSet object prior to calling deleteRow(). On lines 3-5, we create a Statement object (Connection::createStatment) and pass in the ResultSet.CONCUR_UPDATABLE constant to the second paramter of createStaement. If we fail to do so, we will get a SQLException.

It should be noted that while this example shows how to delete records from the database, it should not be a preferred route. The reason is that we are duplicating logic the database already provides. A properly formed SQL statement will perform a bulk delete operation much more efficiently than using ResultSet::deleteRow. ResultSet::deleteRow is much better suited in application where a user is browsing through records and delete items one at a time.

Example Program

Here is a complete Kotlin program that demonstrates the topic discussed in this post.

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

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