Spring Boot with Database, Connection, Transaction, JDBI – Example

If you want to see an minimal example of Spring boot with Database (by using JDBI, similar to JDBCTemplate but better) and how to control database connection and transaction by Spring.

This page is for you.

This example is a part of  Spring Boot with all examples

Full Code with Spring Boot 1.5.8.RELEASE:

7_spring-boot-database-jdbi.zip

What you need for run this:

  • Maven 3
  • Java 8
  • Mysql 5.x

What the code will do:

  • Call an url  then the service will create a row in the user table
  • Call an url  then the service will list row from the user table

Warning

We are going to use JDBI to map database schema, not to control database connection nor transaction.

We are going to use Spring to control database connection and transaction.

Do not be confused.

Many people think mapping database schema and controlling database connection/transaction are the same thing.

Many people ,when they hear about using a framework (such as Hibernate), they always mix everything together, which is quite not correct.

Coding with database is not a joke. You should understand how the framework controlling connection and transaction (especially caching in Hibernate). Do not just let the framework does that. This document is just a basic. This page Spring Boot : Try Connection and Transaction will give you more detail about how connection and transaction behave in Spring Boot with runnable examples.

Explain:

This is the file structure:

In pom.xml you need this:

<?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>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.8.RELEASE</version>
    </parent>

    <groupId>com.surasint.example</groupId>
    <artifactId>spring-boot-01</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <properties>
        <maven.compiler.target>1.8</maven.compiler.target>
        <maven.compiler.source>1.8</maven.compiler.source>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- for db annotation, ex @Transactional -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <!-- mysql connector -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.10</version>
        </dependency>
        <!-- jdbi: db table mapper -->
        <dependency>
            <groupId>org.jdbi</groupId>
            <artifactId>jdbi</artifactId>
            <version>2.62</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

Only line 25-40 are for database and related things. The other lines are for basic spring boot

These lines are for database things in Spring Boot , For example @Transactional or DataShourUtils.

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

These lines are for MySQL connector.

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.10</version>
        </dependency>

These lines are for JDBI

        <dependency>
            <groupId>org.jdbi</groupId>
            <artifactId>jdbi</artifactId>
            <version>2.62</version>
        </dependency>

This is sql script to create the user table.

CREATE TABLE users (
  id               INT PRIMARY KEY AUTO_INCREMENT,
  username             VARCHAR(100) NOT NULL
);

This is UserDao.java to map the user table to UserBean.java

package com.surasint.example.db;

import org.skife.jdbi.v2.DBI;
import org.skife.jdbi.v2.Handle;
import org.skife.jdbi.v2.StatementContext;
import org.skife.jdbi.v2.sqlobject.BindBean;
import org.skife.jdbi.v2.sqlobject.GetGeneratedKeys;
import org.skife.jdbi.v2.sqlobject.SqlQuery;
import org.skife.jdbi.v2.sqlobject.SqlUpdate;
import org.skife.jdbi.v2.sqlobject.customizers.RegisterMapper;
import org.skife.jdbi.v2.tweak.ResultSetMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Repository
public class UserDao {

    @Qualifier("dataSource")
    @Autowired
    private DataSource dataSource;

    public List<UserBean> list(){
        Connection conn =  DataSourceUtils.getConnection(dataSource);
        Handle handle = DBI.open(conn);
        UserSQLs userQLs = handle.attach(UserSQLs.class);
        return userQLs.list();
    }

    public Integer insert(UserBean item){
        Connection conn =  DataSourceUtils.getConnection(dataSource);
        Handle handle = DBI.open(conn);
        UserSQLs userSQLs = handle.attach(UserSQLs.class);
        return userSQLs.insert(item);
    }

    @RegisterMapper(UserMapper.class)
    interface UserSQLs {
        @SqlQuery("select * from users")
        List<UserBean> list();

        @SqlUpdate("insert into users (username)" +
                    " values(:username) ")
        @GetGeneratedKeys
        Integer insert(@BindBean UserBean test);
    }

    public static class UserMapper implements ResultSetMapper<UserBean> {
        @Override
        public UserBean map(int i, ResultSet r, StatementContext statementContext) throws SQLException {
            UserBean bean = new UserBean();
            bean.setId((Integer) r.getObject("id"));
            bean.setUsername(r.getString("username"));
            return bean;
        }
    }

}

The keys here are:

  • Line 26:  @Qualifier(“dataSource”) : This is to tell Spring to inject the data source.
  • Line 31: DataSourceUtils.getConnection(dataSource); : This is to get database connection from data source.
  • Line 44 and 46 @RegisterMapper and @SqlQuery are from JDBI. We will not explain here.

Then we create a service, UserService.java, to user UserDao.java and control database transaction.

package com.surasint.example.service;

import com.surasint.example.db.UserBean;
import com.surasint.example.db.UserDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.List;

@Service
public class UserService {

    @Autowired
    private UserDao userDao;

    @Transactional
    public List<UserBean> list(){
        System.out.println(userDao.list());
        return userDao.list();
    }

    @Transactional
    public Integer insertOK(){
        UserBean test = new UserBean();
        test.setUsername("username"+new Date().getTime());
        return userDao.insert(test);
    }

    @Transactional
    public void insertAndFail(){
        UserBean test = new UserBean();
        test.setUsername("username"+new Date().getTime());
        userDao.insert(test);

        throw new RuntimeException("Hello this is an error message");
    }

}

The keys here are:

  • Line 18,24,31: @Transactional: This is how to control database transaction in Spring

Note that insertAndFail() will insert to the user table and then throw a runtime exception. So, from this method, we expect that it will rollback.

We will execute methods in UserService via REST calls. So, we create TestRestController.java

package com.surasint.example.web.api;

import com.surasint.example.db.UserBean;
import com.surasint.example.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class TestRestController {

    private static int i;

    @Autowired
    UserService userService;
    @GetMapping("/api/user/list")
    public List<UserBean> list() {
        return userService.list();
    }

    @GetMapping("/api/user/insertOK")
    public Integer insertOK() {
        return userService.insertOK();
    }

    @GetMapping("/api/user/insertNotOK")
    public String insertNotOK() {
        userService.insertAndFail();
        return "ok";
    }

}

It is just a normal REST mapping class.

  • http://localhost:8080/api/user/list  will return list of user in Json format
  • http://localhost:8080/api/user/insertOK will insert a row in the user table
  • http://localhost:8080/api/user/insertNotOK will insert a row in the user table and throw an exception and the row will be rollbacked

Try:

create a database named “testdb” and run dbscript.sql to the database in MySql:

CREATE TABLE users (
  id               INT PRIMARY KEY AUTO_INCREMENT,
  username             VARCHAR(100) NOT NULL
);

Change application.properties to your MySql username and password:

#Database
spring.datasource.url=jdbc:mysql://localhost/testdb
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

run this in console:

mvn clean package

Then run this to start:

mvn clean spring-boot:run

You should see something like this:

 .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v1.5.8.RELEASE)

.....
.....
2017-12-12 22:03:04.243  INFO 260 --- [           main] s.w.s.m.m.a.RequestMappingHandlerAdapter : Looking for @ControllerAdvice: org.springframework.boot.context.embedded.Annotation
ConfigEmbeddedWebApplicationContext@7595f8d7: startup date [Tue Dec 12 22:03:01 CET 2017]; root of context hierarchy

Open a browswer and try to go to this url:

http://localhost:8080/api/user/list

You should see this:

Try to go to:

http://localhost:8080/api/user/insertOK

The system should return the id of inserted row, like this:

Then try will this url again:

http://localhost:8080/api/user/list

You should see some data from the database

Try to go to this url:

http://localhost:8080/api/user/insertNotOK

Then you should see this error page.

Note that “Hello this is an error message” is our text in UserService.java.

Try to refresh it multiple times.

Then try will this url again:

http://localhost:8080/api/user/list

You should see the same rows. It should not have more rows.