Tag: Java

Posts related to Java programming

Antivirus Software

What is antivirus software and what does it do?

Just about every user has heard about computer viruses. According to Norton, a computer virus is computer code that will manipulate how a computer acts and will also try to reproduce itself so that it spreads to other systems. However, it is important to note that a computer virus is only one small category of malware. Malware is a term that is used to describe malicious computer software and includes terms such as viruses, worms, Trojan horses, etc. In today’s computing world, we use antivirus software to detect and remove computer viruses. However, most antivirus (AV) software detects and protects against a large category of malware which includes viruses. This post will explain briefly how AV programs work, how effective they are, and what their limitations are as well.

What is Antivirus Software?

Specifically speaking, AV programs are programs that detect and protect your computing environment from computer viruses. However, this is too narrow of a definition to describe modern AV programs. Instead, we often call such programs as Anti-malware programs since most AV tools protect our systems from more than just viruses. There are AV tools that run on just about every major computing platform, which means that you will find them for Windows, macOS, and Linux variants. It is also important to note that malware exists for non-desktop computing platforms as well, so you will also find AV tools for iOS and Android devices as well.

1502946408

Some platforms are more prone to malware than others. For example, the Windows Operating system has been a malware target for years. Recently, the Android operating system has become a popular target for malware. The platform doesn’t even have to be an operating system. For example, malware has been know to target virtual machine platforms such as the Java Virtual Machine. There are a variety of reasons while some platforms tend to be more targeted than others, but market share is known to be a reason because attackers tend to look for the highest amount of return for their efforts so it makes sense that they would target more highly used platforms. Windows is the most popular desktop environment and Android is the most popular mobile environment so it stands to reason why these environments are attacked more often by malware.

However, it’s a commonly held misconception that other platforms are more secure due to the fact that they have less malware. This is simply wrong. It is true that a person using a variant of desktop Linux is much less likely to be targeted by malware, but this does not mean that the platform is more secure than other platforms. There are are a lot of factors that determine how secure or insecure a platform is and many of those factors have to do with configuration and what permissions are running on the computing environment. This means that you still need an AV program or toolkit running on your platform regardless of what it is.

Antivirus Software Detection Schemes

In order for AV to work, it needs to be able to tell the difference between legitimate computer code and malicious code. Different AV tools use different means to accomplish this task and in some cases will even combine different kinds of identification techniques. Each identification scheme has its benefits and limitations but they can be broken down into the following categories.

Behavior Based Detection

Some AV tools try to identify malware based on what it does. This is known as behavior-based detection. Some kinds of behaviors are known to be malicious. For example, a program that tries to change Windows registry settings or overwrite Unix system log files will generally be considered to be suspicious. The same may hold to be true for programs that attempt to open ports in a firewall or make remote connections to other computers in the background. A behavior-based detection system will attempt to monitor a program for such behaviors and alert a user if there is a match.

One way behavior-based detection works is through sandboxing, where a computer program is loaded into a special virtual machine that is referred to as the sandbox. The AV will consider the program to be safe as long as it is operating inside of the permission boundaries of the sandbox. Any behaviors that attempt to bypass the restrictions of the sandbox are considered to be suspicious.

1536531990

The main drawback of sandbox detection is that it is resource intensive. Running every program in a sandbox can be taxing on the computer’s hardware and may consume an excessive amount of memory, network, disk, and processor resources. The technique is starting to be more common on high-end hardware but sandboxing may not be an option for many users at this time.

Another form of behavior monitoring involves artificial intelligence, where the AV attempts to learn about software’s behavior in order to determine if the software is safe to use. AV based on artificial intelligence is still in its early stages so it is not common to see consumer AV packages based on this technology. Nevertheless, cloud-based AV tools may incorporate machine learning in order to study and analyze programs that are malicious.

Signature Based

Every computer program will produce a unique signature that can be thought of like a fingerprint for the program. A signature-based AV tool simply maintains a registry of allowed and banned program signatures. When you initiate a scan of your system, the AV tool will analyze the signature of all executable code it finds on the system and then checks it against its database. Positive matches of banned fingerprints are quarantined and the user is alerted.

This is with this approach is that it is reactive. Most people who write malware will know to check their program against commonly used AV tools in order to avoid detection and its upon the malware vendor to go out and find malware to study and update their databases of whitelisted and blacklisted programs. Furthermore, the user needs to update their machine and maintain the latest version of the AV tool and related files that the AV tool needs.

1540798457

However, the signature-based approach has noticeable benefits that should not be ignored either. For one thing, there are lots of old malware that still floats around the internet that is still harmful to machines that are not protected. A signature-based AV will know about such malware and protect you accordingly. Also, many commercial and open source AV tool publishers are constantly studying software and looking for malware in order to maintain their tools. Signature-based AV scans your system quickly and does not use a lot of system resources either. Finally, many signatures based AV tools can be purchased at a low cost.

Heuristic Based

Heuristic-based approaches are similar to signature-based approaches, but the difference is that a heuristic based AV tool looks for a family of malware as opposed to a specific fingerprint. This approach tends to use a pattern matching and wildcards in order to prevent a malware writer from padding their code with empty instructions or bytes in order to avoid AV detection. It’s also easy to combine heuristic detection with signature-based detection in order to make a more comprehensive AV tool.

One advantage of heuristic-based AV tools is that they can detect a family of malware. Many malware programs are polymorphic, which means they adapt and change their configurations in order to avoid detection. Worms are one such example since they tend to spread and will morph along the way as they spread. Packagers can also be used to slip malware pass an AV tool as well. By using wildcards and pattern matching, a heuristic based scanner can catch such schemes and isolate malware.

Of course, heuristic-based scanning still requires a current version of the AV tool and known fingerprints to work. While they may not need an exact match of the fingerprints, the heuristic based scanner still needs to know what sort of fingerprints to search for in order to perform fuzzy scanning on computer code. For this reason, it’s still possible for malware to avoid detection even when using a heuristic based AV tool. There is also a possibility for more “false positive” where a legitimate program can be treated as a malicious one in the event that the program’s fingerprint falls within the boundaries of the scanner.

Antivirus Software action upon detection

Once the AV detects malware, it needs to decide what to do with it in order to keep you safe. This behavior will be highly dependent upon the AV tool that you decide to use. However, there are a few different actions that can be taken by the AV once it has determined that you are getting attacked by malware.

Quarantine

At a minimum, the AV tool will quarantine the file that contains the malicious code. It can do this by using permissions or performing manipulations on the file in order to render it inoperable. Generally speaking, the infected file will get moved to a special folder on your hard drive and the AV tool will rename the file so that you don’t double click on it or execute the program. This will keep you from running the file and keep the code from getting executed. Some AV programs will also ask you to send the file to them for further analysis so that the strength of the AV tool gets improved as well.

Block the Action

1535604621

A more advanced AV tool can even interrupt the execution of the malware. For example, if a program attempts to make an unauthorized change to a system file, the AV program may instruct the operating system to kill that process immediately. In other cases, the AV program may flash a confirmation dialog to the user asking if they want to grant permission to program in order to change the protected area of the machine. This can be useful to administrators who are using legitimate programs to perform necessary actions.

Restore the System

Malware, by its very nature, attempts to damage the target system by impacting the confidentiality, integrity, and availability of the target. In some cases, the AV tool can attempt to restore the system after it has been attacked by malware. This can be done by maintaining backups of critical system files in a safe place or it can try and remove the bytes that were adding to a file by the malware.

Restoration is important because it can stop the malware from spreading. For example, a macro-virus (a virus that is stored in an MS-Word document or similar software) may infect a legitimate office document that is going to get shared to other users. In some cases, the AV tool may be able to remove the malicious script from the file so that users can safely open it. This will stop the malware from spreading to additional victims.

Conclusion

No computer system is safe without antivirus software. Antivirus software works by detecting, quarantining, and restoring the system to a safe state. All computing environments are susceptible to malware so no one platform should be considered to be safe to use without some sort of AV tool running on it. You should also keep in mind that mobile devices and IoT devices are also susceptible to malware.

If you use a computer system that does not have an antimalware tool, then you are putting yourself and other people at risk. Even if you believe your system is safe, you can still be used as a conduit to transmit malware to other people. There are lots of different antivirus tools that are available on the market and even for free. Although some antivirus software works better than others, the reality is that you are better off having some degree of protection rather than no protection at all. Antivirus software is a critical component of computer security so you should always make sure that you have it and keep it up to date.

Sources

Antivirus Software, Wikipedia

Symantec Employee, What is a computer virus?

What does Malware Do?, Comodo

Peter M. Mell, Karen Kent, Joseph Nusbau, Guide to Malware Incident Prevention and Handling, NIST

Charlie Osborne, Crisis malware targets virtual machines

 

Kotlin JDBC – RowSet Interface

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

RowSet Sub-Interfavces

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

Example Program

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

pom.xml

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

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

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

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

    </dependencies>

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

</project>

EmployeesRowset.kt

package stonesoupprogramming

import javax.sql.rowset.RowSetProvider

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

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

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

        //Execute the command
        execute()

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

Explanation

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

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

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

Kotlin JDBC – Savepoints

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

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

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

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

connection.rollback(bob)

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

Below is an example program that demonstrates using SavePoints.

pom.xml

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

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

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

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

    </dependencies>

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

</project>

Employees.kt

package stonesoupprogramming

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

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

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

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

                createOrTruncateTable(connection)

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

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

                    val bob = connection.setSavepoint("Bob")

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

                    val linda = connection.setSavepoint("Linda")

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

                    val tina = connection.setSavepoint("Tina")

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

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


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

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

Explanation

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

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

Kotlin JDBC – Rollback Transactions

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

connection.autoCommit = false

createOrTruncateTable(connection)

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

try {
    /* Lines 32-46 omitted */

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

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

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

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

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

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

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

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

Complete Example

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

pom.xml

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

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

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

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

    </dependencies>

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

</project>

Employees.kt

package stonesoupprogramming

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

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

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

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

                createOrTruncateTable(connection)

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

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

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

                        insertRow()
                        moveToInsertRow()

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

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

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

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

                        insertRow()
                    }
                    throw Exception("Simulated")

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

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


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

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

Kotlin JDBC – Transactions

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

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

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

pom.xml

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

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

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

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

    </dependencies>

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

</project>

Employees.kt

package stonesoupprogramming

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

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

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

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

                createOrTruncateTable(connection)

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

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

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

                    insertRow()
                    moveToInsertRow()

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

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

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

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

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

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

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

Kotlin JDBC – Create a Table

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

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

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

Example Program

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

pom.xml

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

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

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

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

    </dependencies>

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

</project>

BurgerMenu.kt

package stonesoupprogramming

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

        //Now insert the row
        insertRow()
    }
}

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

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

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

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

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

Spring Boot Caching with Kotlin

It’s fairly common for applications to continually ask a datastore for the same information repeatedly. Requests to datastores consume application resources and thus have a performance cost even when the requested data is small. The Spring Platform provides a solution allows applications to store information in an in memory caching system that allows applications to check the cache for the required data prior to making a call to the database. This example shows how to use Spring Boot and Kotlin to cache files that we are storing in the database.

Database Entity

We are going to define a database entity that stores files in a database. Since retrieving such data can be an expesive call to the database, we are going to cache this entity.

@Entity
data class PersistedFile(
        @field: Id @field: GeneratedValue var id : Long = 0,
        var fileName : String = "",
        var mime : String = "",
        @field : Lob var bytes : ByteArray? = null)

You will notice that this class has a ByteArray field that is stored as a LOB in the database. In theory, this could be as many bytes as the system allows so ideally we would store this in cache. Other good candidates are entity classes that have complex object graphs and may result in the ORM generated complex SQL to retreive the managed object.

Enable Caching

Spring Boot defines a CachingManager internally for the application. You are free to use your own, but you need to configure your Spring Boot environment first.

Dependencies

You need to have spring-boot-starter-cache in your pom.xml or other dependency manager.


    org.springframework.boot
    spring-boot-starter-web

Annotation

You also need to tell the environment to turn on caching by using the @EnableCaching

@SpringBootApplication
@EnableJpaRepositories
@EnableCaching  //Spring Boot provides a CacheManager our of the box
                //but it only turns on when this annotation is present
class CachingTutorialApplication

Decorate the Caching Methods

At this point, we only need to decorate the methods we want the environment cache. This is done by decorating our methods with the @Cacheable annotation and then providing the annotation with the name of a cache. We can also optionally tell the cache manager what to use for the key. Here is the code for our service class followed by an explanation.

//We are going to use this class to handle caching of our PersistedFile object
//Normally, we would encapsulate our repository, but we are leaving it public to keep the code down
@Service
class PersistedFileService(@Autowired val persistedFileRepository: PersistedFileRepository){

    //This annotation will cause the cache to store a persistedFile in memory
    //so that the program doesn't have to hit the DB each time for the file.
    //This will result in faster page load times. Since we know that managed objects
    //have unique primary keys, we can just use the primary key for the cache key
    @Cacheable(cacheNames = arrayOf("persistedFile"), key="#id")
    fun findOne(id : Long) : PersistedFile = persistedFileRepository.findOne(id)

    //This annotation will cause the cache to store persistedFile ids
    //By storing the ids, we don't need to hit the DB to know if a file exists first
    @Cacheable(cacheNames = arrayOf("persistedIds"))
    fun exists(id: Long?): Boolean = persistedFileRepository.exists(id)
}

The first method, findOne, is used to look up a persistedFile object from the database. You will notice that we pass persistedFile as an argument to cacheNames and then use the primary key as the key for this item’s cache. We can use the PK because we know it’s a unique value so we can help make the cache more performant. However, keep in mind that the key is optional.

We can also avoid another call to the database by storing if items exist in the database in the cache. The first time exists() is called, the application will fire a count sql statement to the database. On subsequent calls, the cache will simply return true or false depending on what is stored in the cache.

Putting it all together

I put together a small web application that demonstates the caching working together. I turned on the show sql property in the applications.properties file so that viewers can see when the application is making calls to the database. You will notice that the first time I retreive the persisted file, there is sql generated. However, on the second call to the same object, no sql is generated because the application isn’t making a call to the database.

You can get the complete code from my GitHub page at this link.

Here are some links to posts that are related to concepts used in Spring Boot that we used today.

Spring Boot Kotlin & MongoDB

MongoDB is a NoSQL database that works really well with Kotlin and Spring Boot. MongoDB is incredibly useful in situations where the structure of data isn’t known prior to writing the application. For example, picture a blogging website where users can enter any number of comments or response. Modeling such a data structure would be difficult in a relational database, but it’s much easier with Mongo.

In this example application, we are going to use MongoDB to document Restaurants with any number of employees (of course, a simple example such as this can be done in a relational database, but let’s go with this for simplicity sake). The cool part using Mongo with Spring Boot is that there is zero configuration providing you are using default settings. This let’s us jump right into our code.

Let’s begin by creating a couple of data classes to store in our database.

//Create a document class
//that persists to the DB
@Document
data class Restaurant(
        //Mark this field as the document id
        @field: Id var name : String = "",
        //Unstructured Data Here
        var employees : List = mutableListOf())

//This class embeds directly into Restaurant
//without any annotations
data class Employee(var name : String = "",
                    var position : String = "")

Our Restaurant class is annotated with @Document to mark it as a persistable class. We also annotate the name field with the Id annotation to mark it as the document id. This value has to be unique in the database. The other class is Employee which does not have any annotations at all. It’s used as a property in the Employees database and the persistence provide is able store all of employee objects embedded in Restaurant.

Our next class is a repository class which Spring will generate the implementation for us. Before this can happen, we have to enable mongo repositories. All we need to do is annotate a configuration class to make this happen.

@Configuration
@EnableMongoRepositories //Allow Spring to Generate Mongo Repositories
class Config

Once we have enabled the mongo repositories, we just need to define an interface that extends MongoRespository.

//Spring will implement our interface for us!
interface RestaurantRepository : MongoRepository

Now let’s make a controller class to test our application. See this post for an explanation of Spring MVC.

//Example Controller class for demonstration purposes
@Controller
@RequestMapping("/")
class IndexController(
        //We can inject our RestaurantRepository class, Spring will
        //provide an implementation
        @Autowired private val restaurantRepository: RestaurantRepository){

    @RequestMapping(method = arrayOf(RequestMethod.GET))
    fun doGet(model : Model) : String {
        model.apply {
            addAttribute("restaurant", Restaurant())
            //Query all Restaurants
            addAttribute("allRestaurants", restaurantRepository.findAll())
        }
        return "index"
    }

    @RequestMapping(method = arrayOf(RequestMethod.POST))
    fun doPost(@RequestParam("name") name : String,
               @RequestParam("employees") employees : String,
               model : Model) : String {
        val restaurant = Restaurant(name = name,
                                    employees = parseEmployees(employees))
        //Save the new restaurant
        restaurantRepository.save(restaurant)
        model.apply {
            addAttribute("restaurant", Restaurant())
            //Query all Restaurants
            addAttribute("allRestaurants", restaurantRepository.findAll())
        }
        return "index"
    }

    fun parseEmployees(employees : String) : List {
        val employeeList = mutableListOf()
        val parts = employees.split('\n')

        parts.forEach {
            val subParts = it.split(",")
            employeeList.add(
                    Employee(name = subParts[0],
                            position = subParts[1]))
        }
        return employeeList.toList()
    }
}

Notice that we can directly inject RestaurantRepository into our controller. Spring does the work of providing an implementation for our controller class. In our doPost() method, we call restaurantRepository.save() to save our new document. In both doGet() and doPost(), we call restaurantRepository.findAll() to pull back all of our restaurants stored in the database.

Now we just need an HTML template to provide us with front end code.
indexcode

Conclusion

Here is an example of the application when run.


As you can see, Spring Boot combined with Kotlin makes it really easy to persist data into MongoDB. We only need to define a few data classes and allow Spring to make our Repository classes for us in order to get started.

You can view the code for this project at my GitHub page at this link.