Kotlin Spring JDBC Template

It’s typical for many applications, including web applications, to read and write to a database. JDBC operations are significantly simplified when using Spring JdbcTemplates and Kotlin’s language features. For example, it’s easy to one line read and insert operations into a database. This post goes through a sample web application that inserts a user into a database table and then prints a list of all users stored in the database.

Interacting with the Database

Our first order of business is to create a database schema. This is the SQL script that we will use to generate our database.

DROP TABLE IF EXISTS USERS;

CREATE TABLE USERS (
  id INTEGER IDENTITY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  email VARCHAR(50),
  phone VARCHAR(50),
)

Now we will define a database that maps to the information held in our database. Kotlin’s data classes are ideal for this sort of task.

//Define a data class that maps to both our
//form and database table
data class User(var firstName: String = "",
                var lastName: String = "",
                var email: String = "",
                var phone: String = "")

There isn’t anything special about our User class. It’s only job is to carry information from the view to the database and back from the database to the view. Now that we have a database table and a transfer object, we need to configure our datasource so that Spring can connect our application to our database. We will define a configuration class that will define some Spring beans for us.

@Configuration
class Configuration {

    //First configure a data source that
    //generates an embedded db
    @Bean(name = arrayOf("dataSource"))
    fun dataSource(): DataSource {

        //This will create a new embedded database and run the schema.sql script
        return EmbeddedDatabaseBuilder()
                .setType(EmbeddedDatabaseType.HSQL)
                .addScript("schema.sql")
                .build()
    }

    //Create a JdbcTemplate Bean that connects to our database
    @Bean
    fun jdbcTemplate(@Qualifier("dataSource") dataSource: DataSource): JdbcTemplate {
        return JdbcTemplate(dataSource)
    }
}

The first bean, dataSource, returns an EmbeddedDatabaseBuilder object that does the work of creating an embedded database, setting it’s dialect, and running our schema.sql script to create the database definition. At this point, our database is fully ready when the build() method is called.

The next bean is a JdbcTemplate object. We create a bean definition for it so that we can inject instances of this object into our repository classes later on. The JdbcTemplate requires a DataSource object, which happens to point at our embedded database. Now let’s define a repository class that will actually work with our JdbcTemplate.

@Repository
class IndexRepository(@Autowired var jdbcTemplate: JdbcTemplate) {


    fun addUser(user: User) {
        //We can use SimpleJdbcInsert to insert a value into our table
        //The becomes super concise when combined with Kotlins apply and mapOf functions
        SimpleJdbcInsert(jdbcTemplate).withTableName("USERS").apply {
            setGeneratedKeyName("id")
            execute(
                    mapOf("first_name" to user.firstName,
                            "last_name" to user.lastName,
                            "email" to user.email,
                            "phone" to user.phone))
        }
    }

    //This allows us to query the Users table and return a list of users
    //This is one method call to jdbcTemplate with a lambda expression which makes the code
    //incredibly concise
    fun allUsers(): List = jdbcTemplate.query("SELECT FIRST_NAME, LAST_NAME, EMAIL, PHONE FROM USERS",
            { rs: ResultSet, _: Int ->
                User(rs.getString("first_name"), rs.getString("last_name"), rs.getString("email"), rs.getString("phone"))
            })
}

@Respository is a Spring sterotype annotation that marks our IndexRepository as a class that is intended to interact with the datasource. Spring provides two other stereotype annotations, @Controller and @Service, that are typically used to mark seperations in the application. @Controller is intended to interact with the view, while @Respository works with datasource. @Service should contain business logic. When developers follow this pattern, the application maintains loose coupling which makes it easy to maintain and test code.

Since IndexController is marked with @Repository, it makes sense to inject JdbcTemplate into this class so that it can work with the database. We have two methods in this class: addUser and allUsers. We’ll take each function on its own.

The addUser(user : User) method performs an insert into the database. We create an instance of SimpleJdbcInsert and pass our JdbcTemplate object into this class. The following call to withTable(“USERS”) specifies which table we are inserting a record into. Since our primary key is generated automatically by the database, we can use SimpleJdbcInsert.setGeneratedKeyName(“id”) to assign a primary key. Finally we use the execute() function to actually perform the insertion into the database. The execute() takes a map where the key is the name of the column in the database and the value is what we are inserting into the column.

There is some Kotlin magic that helps keep the code concise. For one, we are chaining our calls to setGeneratedKeyName() and execute() inside of the apply() function. We can also leverage Kotlin’s mapOf() function to generate a Map on the fly as opposed to creating a map object and populating it with values ahead of time.

The allUsers() function queries the database. In this case, we can call the query method from the jdbcTemplate object. The query() method requires two parameters. The first parameter is the query that is sent to the database. The second method is a an instance of RowMapper, which is a single abstract method (SAM) class. Since RowMapper has only one method, we can use a lambda expression to provide an implementation of RowMapper.

The RowMapper’s job is to transform the results of the database query into a User object. It provides with two objects that help with this job. The first is the good old JDBC ResetSet object and the other object is an Int that represents the row number. We only use the ResultSet in this example. The ResultSet interface has a getString() method that takes the name of the column and outputs the value stored in that column. Using getString(), we can populate each field of a User object and return it. RowMapper will handle the details of building a list and returning the List to the caller.

Web Portion

The remaining part of the application is a Spring MVC application. We aren’t going to spend a lot of time on this portion but are including it for After the @Repository tier (covered above), we have a service class that handles the business logic between the @Controller and the @Repository. In our case, it’s really boring because all our @Service class is doing is acting as a wrapper for our @Repository class, but in the real world, there is generally more application code located in this class.

@Service
class IndexService(
    //Inject IndexRepository here
    @Autowired var indexRepository: IndexRepository) {

    fun addUser(user: User) {
        indexRepository.addUser(user)
    }

    fun allUsers(): List {
        return indexRepository.allUsers()
    }
}

We also have a @Controller class that handles HTTP GET and POST requests.

@Controller
@RequestMapping("/")
class IndexController(@Autowired var indexService: IndexService) {

    @RequestMapping(method = arrayOf(RequestMethod.GET))
    fun doGet(model: Model): String {
        model.addAttribute("user", User())
        model.addAttribute("allUsers", indexService.allUsers())
        return "index"
    }

    @RequestMapping(method = arrayOf(RequestMethod.POST))
    fun doPost(model: Model, user: User): String {
        indexService.addUser(user)

        model.addAttribute("user", User())
        model.addAttribute("allUsers", indexService.allUsers())
        return "index"
    }
}

And finally the view…
View

Conclusion

Kotlin greatly enchances the already excellent JDBC abilities offered by Spring Boot. As was demonstrated in this post, developers can start with definining a data class that holds all of the data in a single row in a database table. When it comes to actually perform inserts or queries from the database, Kotlin’s mapOf(), apply, and to functions cut down on any additional verbosity that might still remain from using JDBC Template. As always, Spring makes it super simple to spring up a web application that interfaces with a database.

You can grab the source for this example from my GitHub page or view the Video tutorial on YouTube.

Advertisements

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 Koans—Part 22

More functional programming on the horizon. This portion of Kotlin Koans demonstrated folding. I personally had never tackled a challenge like this so it took me more time to figure it out than the other problems. My job was to go through all customers and the products they ordered and reduce them down to a single set of objects. Here is the Kotlin code.

fun Shop.getSetOfProductsOrderedByEveryCustomer(): Set {
    // Return the set of products ordered by every customer
    return customers.fold(allOrderedProducts, {
        orderedByAll, customer ->
            orderedByAll.intersect(customer.orderedProducts)
    })
}

As usual, I tried to do the same problem in Java for comparison purposes, but I wasn’t able to figure it out! (If you know the solution, please leave it in the comments section!). I’ll have to admit that I am weak in some of the functional programming areas.

You can click here to see Part 21.

Kotlin Koans—Part 21

The Kotlin Koans tutorial continues with more demonstrations about the extensions on collection classes. This portion of the tutorial was a partitioning problem where I had to return the customers that have not had their orders delivered. Here is the code.

fun Shop.getCustomersWithMoreUndeliveredOrdersThanDelivered(): Set {
    // Return customers who have more undelivered orders than delivered
    return customers.partition { it.orders.all { it.isDelivered } }.second.toSet()
}

Kotlin adds a partition method to it’s collection classes. The partition method takes a lambda expression that returns a boolean. Inside of this lambda, I used the all (#TODO Link to All) method on the orders collection. Once again, I am returning a boolean value.

Now for the coolest part. Kotlin has a pair class that has a first and second method. Since I need the orders that are not delievered, I use the second property on the Pair class. At this point, second is holding a collection of Customers whose orders are not delivered. Finally, I can use the toSet (#TODO Link) method to transform the collection into a set.

Like the last few portions of this tutorial, I decided to compare the Kotlin code to the Java 8 code. Here is what I came up with.

public static Set getCustomersWithMoreUndeliveredOrdersThanDelivered(Shop shop){
    return new HashSet(shop.getCustomers()
            .stream()
            .collect(Collectors.partitioningBy((Customer c) -> c.getOrders().stream().allMatch(Order::isDelivered)))
            .get(false));
}

You can click here to see Part 20.

Kotlin Koans—Part 20

Grouping objects by a property and storing them into a map is a challenge that all developers have faced at some point in time. For example, you may have a collection of Customers and you wish to find out which Customers live in each city. Basically, you want a map where City is the key and a Collection of Customers associated with that City is the value.

This was exactly the problem that Kotlin Koans tutorial had me do.

fun Shop.groupCustomersByCity(): Map {
    // Return a map of the customers living in each city
    return customers.groupBy { it.city }
}

I was able to arrange all of the Customers by city with just one line of Kotlin code. The related Java code wasn’t that difficult either, but I did have to search for the solution since it wasn’t quite as clear as the Kotlin approach.

public static Map groupCustomersByCity(Shop shop){
    return shop.getCustomers().stream().collect(Collectors.groupingBy(Customer::getCity));
}

What helped me with the Kotlin approach was that since the groupBy method was direclty on the Collection object, my IDE was able to supply me with the groupBy method. That’s not the case with the Java approach since it’s using a static method on the Collectors class. It also didn’t occur to me to use the collect method on the Stream object either. I was looking for something that said group in it.

You can click here to see Part 19.

Kotlin Koans—Part 19

This section of the Kotlin Koans tutorial continued onward with Kotlin’s collection API enhancement. The challenge in this section use to total the price of all products a customer purchased. Here is the code

fun Customer.getTotalOrderPrice(): Double {
    // Return the sum of prices of all products that a customer has ordered.
    // Note: a customer may order the same product for several times.
    return orders.sumByDouble { it.products.sumByDouble { it.price } }
}

The collection API in Kotlin has a sumByDouble method, which takes a lambda expression. The lambda let’s developers chain function calls. In this case, each Customer had a collection of Products in each Order. To get the price of all Products ordered, I needed the sum of the price of all products in a order. This was easy enough to do because I just made a nested call to sumByDouble on it.products and then told it to sum on it.price.

Here is Java code that solves the same problem.

public static double getTotalOrderPrice(Customer customer){
    return customer
            .getOrders()
            .stream()
            .mapToDouble(
                    order -> order.getProducts()
                                    .stream()
                                    .mapToDouble(Product::getPrice)
                            .sum())
            .sum();
}

You can click here to see Part 18

Kotlin Koans—Part 18

This portion of the Kotlin Koans tutorial deals with sorting a collection functionally. As usual with Kotlin, this wasn’t a very difficult problem. As a matter of fact, it’s only one line of code.

fun Shop.getCustomersSortedByNumberOfOrders(): List {
    return customers.sortedBy { it.orders.size }
}

We basically just call the sortedBy method and place the property we want to sort by inside of the curly braces { }. In this case, I wanted to sort by the number of orders my hypothetical customer placed, so I used it.orders.size.

Just for kicks, I decided to try this in Java 8 to see the difference.

public static List getCustomersSortedByNumberOfOrders(Shop shop){
    return shop.getCustomers()
            .stream()
            .sorted(Comparator.comparingInt(customer -> customer.getOrders().size()))
            .collect(Collectors.toList());
}

Once again, we can do the same thing in Java that we can do in Kotlin at the expense of more verbosity. Also, I think in this case, the Java approach is more difficult to learn and more prone to errors since we are chaining a bunch of functions together to achieve the same result that we can do in one line of Kotlin code.

You can click here to see Part 17