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 (expecially 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:

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.

These lines are for MySQL connector.

These lines are for JDBI

This is sql script to create the user table.

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

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.

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

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:

Change application.properties to your MySql username and password:

run this in console:

Then run this to start:

You should see something like this:

Open a browswer and try to go to this url:

You should see this:

Try to go to:

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

Then try will this url again:

You should see some data from the database

Try to go to this url:

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:

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