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

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.

Kotlin Koans—Part 23

This portion of the Kotlin Koans tutorial appeared to be a review of the concepts I had been working on throughout the collection section. I had to solve three different problems using the collections API. While doing this, I got to revist the Elivis operator (?:), map, maxBy, sumBy, filter, count, and toSet.

Get Customers Who Ordered Product

This problem focused on filtering.

fun Shop.getCustomersWhoOrderedProduct(product: Product): Set {
    // Return the set of customers who ordered the specified product
    return customers.filter { it.orderedProducts.contains(product) }.toSet()
}

The filter method takes a predicate that returns true or false. In this case, I just used the contains method on orderedProducts. If the product is found in orderedProducts, we get a true, otherwise false. Then there is a toSet() operation to transform the collection to a set.

Get Most Expensive Delived Products

This problem was a little more challenging. I had to go back and review how to use the Elivis operator (TODO: Link).

fun Customer.getMostExpensiveDeliveredProduct(): Product? {
    // Return the most expensive product among all delivered products
    // (use the Order.isDelivered flag)
    return orders.filter { it.isDelivered }.map { it.products.maxBy { it.price } }.maxBy { it?.price ?: 0.0}
}

I started with a filter operation to check if an order was delivered or not since the problem statement required me to find the most expensive delivered product. Then I had to use a map operation which allowed me to traverse all delivered orders. At this point, I could use a maxBy operation and check it.price. This builds up a collection of products that contains the most expensive product on each order.

The next part of the operation is to find the most expensive product of all orders. At this point, I have a collection of products so I just needed another maxBy operation. However it was a little more trickey this time. In this case, there was a possibily that the variable it could be null. It’s nice that Kotlin has compiler checks for this sort of thing because I truthfully didn’t realize that I could be working with null objects here. Thus, I had to use the Elvis operator in this final lambda operation.

Get Number Of Times Product Was Ordered

I had to solve this problem by chaining transformations together again.

fun Shop.getNumberOfTimesProductWasOrdered(product: Product): Int {
    // Return the number of times the given product was ordered.
    // Note: a customer may order the same product for several times.
    return customers.sumBy { it.orders.sumBy { it.products.count { it == product } } }
}

A customer has a one to many relationship with orders, and orders have a one to many relationship with products. I needed two sumBy operations to solve this problem. I began with a sumBy on customers. Inside of the lambda, I did another sumBy operation on orders. Once I was traversing orders, I could do a count operation on products and get a total of how many products matched my predicate.

The it.products.count returns a number that gets fed into it.orders.sumBy. The it.orders.sumBy returns a number that gets fed into customers.sumBy. Once customers.sumBy returns, we have a count of the total number of times the specified product was ordered.

You can click here to see Part 22

Kotlin Spring Security Custom Login

Spring Security provides a custom login page that is functional but not very attractive. In most cases, web developers want a more attractive looking login page. This post demonstrates how to configure Spring Security to use a custom login page. Readers can view this tutorial for a demonstration on how to configure basic Spring Security.

Front End—Write a Custom Page

We are going to start by writing a custom login page. Spring Security is very flexible about the page itself, but there are a few rules that need to be followed.

  • Form requires Username
  • Form requires Password
  • CSRF Token is required

This is a screen shot of the page that will be used for this tutorial.
LoginPage
Followed by the code.
LoginCode
It’s critical to remember to include the CSRF token in the form. Without it, the server will refuse any HTTP POST requests and will respond with code 405. The th:name="${_csrf.parameterName}" th:value="${_csrf.token}" on an input take will do the job of adding the CSRF token.

Backend—Configure Spring

Once the front end code is ready, you need to configure Spring Security to use this page. In order to render the login page, Spring will need some sort of controller class. Developers are free to write their own, but it’s also trivial to make use of the one Spring is happy to provide.

@Configuration
class WebConfig : WebMvcConfigurerAdapter() {
    override fun addViewControllers(registry: ViewControllerRegistry) {
        //This class adds a default controller for the login page.
        //Otherwise you would need to write a custom controller class
        registry.addViewController("/login").setViewName("login")
    }
}

The next job is to configure Spring security.

@Configuration //Make this as a configuration class
@EnableWebSecurity //Turn on Web Security
class SecurityWebInitializer : WebSecurityConfigurerAdapter(){
    override fun configure(http: HttpSecurity) {
        http
                .authorizeRequests()
                    //We need to allow anonymous users to
                    //access the login page (otherwise we get 403)
                    .antMatchers("/login").anonymous()
                    .anyRequest().authenticated()
                .and()
                    //Setup a custom login page
                    .formLogin()
                        .loginPage("/login")
                        .usernameParameter("username")
                        .passwordParameter("password")
                .and()
                    .httpBasic()
    }

    override fun configure(auth: AuthenticationManagerBuilder) {
        //This code sets up a user store in memory. This is
        //useful for debugging and development
        auth
                .inMemoryAuthentication()
                    .withUser("bob")
                    .password("belcher")
                    .roles("USER")
                .and()
                    .withUser("admin")
                    .password("admin")
                    .roles("USER", "ADMIN")
    }
}

It’s important to allow anonymous() access to the login page. Without it, Spring Security will continue to redirect to the login page until the server returns 403 (too many redirects).

Conclusion

Once complete, the site will render a custom login page like what is shown in the video.

You can get the code for the complete project at my GitHub page.