Spring Boot, JPA and Spring Transaction Tutorial with Examples

Objective of the Post
Prepare a database
Create a Spring Boot project
Configure pom.xml
Configure Datasource
Entity, Model, Form, DAO
Controller
Thymeleaf Template
Run the application

1. Objective of the Post

This document is based on:

Spring Boot 2.x
Eclipse 4.7 (Oxygen)

In this post, I will guide you for creating a Spring Boot project and work with a database (Oracle, MySQL, SQL Server, Postgres,..) using JPA & Spring Transaction. The matters shall be discussed in this post, including:

Declare the libraries necessary to be able to work with a database.
Configure Spring Boot to be able to connect to a database.
Manipulate with a database using the EntityManager of JPA.
Use Spring Transaction and explain the operating principle of Spring Transaction.

2. Prepare a database

MySQL

-- Create table
create table BANK_ACCOUNT
(
  ID        BIGINT not null,
  FULL_NAME VARCHAR(128) not null,
  BALANCE   DOUBLE not null
) ;
--  
alter table BANK_ACCOUNT
  add constraint BANK_ACCOUNT_PK primary key (ID);


Insert into Bank_Account(ID, Full_Name, Balance) values (1, 'Tom', 1000);
Insert into Bank_Account(ID, Full_Name, Balance) values (2, 'Jerry', 2000);
Insert into Bank_Account(ID, Full_Name, Balance) values (3, 'Donald', 3000);

commit;

SQL Server

-- Create table
create table BANK_ACCOUNT
(
  ID        BIGINT not null,
  FULL_NAME VARCHAR(128) not null,
  BALANCE   DOUBLE PRECISION not null
) ;
--  
alter table BANK_ACCOUNT
  add constraint BANK_ACCOUNT_PK primary key (ID);


Insert into Bank_Account(ID, Full_Name, Balance) values (1, 'Tom', 1000);
Insert into Bank_Account(ID, Full_Name, Balance) values (2, 'Jerry', 2000);
Insert into Bank_Account(ID, Full_Name, Balance) values (3, 'Donald', 3000);

Oracle

-- Create table
create table BANK_ACCOUNT
(
  ID        NUMBER(19) not null,
  FULL_NAME VARCHAR2(128) not null,
  BALANCE   NUMBER not null
) ;
--  
alter table BANK_ACCOUNT
  add constraint BANK_ACCOUNT_PK primary key (ID);


Insert into Bank_Account(ID, Full_Name, Balance) values (1, 'Tom', 1000);
Insert into Bank_Account(ID, Full_Name, Balance) values (2, 'Jerry', 2000);
Insert into Bank_Account(ID, Full_Name, Balance) values (3, 'Donald', 3000);

commit;

PostGres

Create table Bank_Account (
   ID Bigint not null,
   Full_Name Varchar(128) not null,
   Balance real not null,
   CONSTRAINT Bank_Account_pk PRIMARY KEY (ID)
);

Insert into Bank_Account(ID, Full_Name, Balance) values (1, 'Tom', 1000);
Insert into Bank_Account(ID, Full_Name, Balance) values (2, 'Jerry', 2000);
Insert into Bank_Account(ID, Full_Name, Balance) values (3, 'Donald', 3000);

3. Create a Spring Boot project

On the Eclipse, create a Spring Boot project

Enter:

Name: SpringBootJPA
Group: org.o7planning
Artifact: SpringBootJPA
Description: Spring Boot + JPA + Spring Transaction
Package: org.o7planning.springbootjpa

Select the technologies and libraries to be used:

JPA
MySQL
PostgrsSQL
SQL Server
Web
Thymeleaf

4. Configure pom.xml

If you work with the Oracle database, you need to declare the following libraries on the pom.xml:

* Oracle *

<dependencies>
    .....

     <dependency>
        <groupId>com.oracle</groupId>
        <artifactId>ojdbc6</artifactId>
        <version>11.2.0.3</version>
    </dependency>
    
    .....
</dependencies>

<repositories>
        ....

    <!-- Repository for ORACLE JDBC Driver -->
    <repository>
        <id>codelds</id>
        <url>https://code.lds.org/nexus/content/groups/main-repo</url>
    </repository>
    
    .....
</repositories>

If you connect to the SQL Service database, you can use either JTDS library orMssql-Jdbc library:

* SQL Server *

<dependencies>
       .....

    <dependency>
        <groupId>com.microsoft.sqlserver</groupId>
        <artifactId>mssql-jdbc</artifactId>
        <scope>runtime</scope>
    </dependency>
    
    <dependency>
        <groupId>net.sourceforge.jtds</groupId>
        <artifactId>jtds</artifactId>
        <scope>runtime</scope>
    </dependency>

     .....
</dependencies>

The full content of the pom.xml file:

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>org.o7planning</groupId>
    <artifactId>SpringBootJPA</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>SpringBootJPA</name>
    <description>Spring Boot + JPA + Spring Transaction</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.0.RELEASE</version>
        <relativePath /> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>42.1.4</scope>
        </dependency>
        
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>net.sourceforge.jtds</groupId>
            <artifactId>jtds</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
        </dependency>
        
        <!-- https://mvnrepository.com/artifact/org.threeten/threetenbp -->
        <dependency>
            <groupId>org.threeten</groupId>
            <artifactId>threetenbp</artifactId>
            <version>1.3.6</version>
        </dependency>
        

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <repositories>

        <!-- Repository for ORACLE JDBC Driver -->
        <repository>
            <id>codelds</id>
            <url>https://code.lds.org/nexus/content/groups/main-repo</url>
        </repository>     
        
    </repositories>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
    
</project>

5. Configure Datasource

For Spring can connect to a Database, you need to configure necessary parameters in the application.properties file.

application.properties (MySQL)

# ===============================
# DATABASE
# ===============================

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mydatabase
spring.datasource.username=root
spring.datasource.password=12345

# ===============================
# JPA / HIBERNATE
# ===============================

spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=none
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

application.properites (Sql Server + Mssql-Jdbc)

# ===============================
# DATABASE
# ===============================

spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.url=jdbc:sqlserver://tran-vmware-pc\\SQLEXPRESS:1433;databaseName=bank
spring.datasource.username=sa
spring.datasource.password=12345


# ===============================
# JPA / HIBERNATE
# ===============================

spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=none
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect

application.properites (Sql Server + JTDS)

# ===============================
# DATABASE
# ===============================

spring.datasource.driver-class-name=net.sourceforge.jtds.jdbc.Driver
spring.datasource.url=jdbc:jtds:sqlserver://tran-vmware-pc:1433/bank;instance=SQLEXPRESS
spring.datasource.username=sa
spring.datasource.password=12345


# ===============================
# JPA / HIBERNATE
# ===============================

spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=none
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect

application.properties (Oracle)

# ===============================
# DATABASE
# ===============================

spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver

spring.datasource.url=jdbc:oracle:thin:@localhost:1521:db12c
spring.datasource.username=Test
spring.datasource.password=test001


# ===============================
# JPA / HIBERNATE
# ===============================

spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=none
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle10gDialect

application.properties (PostGres)

# ===============================
# DATABASE CONNECTION
# ===============================

spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://tran-vmware-pc:5432/bank
spring.datasource.username=postgres
spring.datasource.password=12345

# ===============================
# JPA / HIBERNATE
# ===============================

spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=none
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect


# Fix Postgres JPA Error:
# Method org.postgresql.jdbc.PgConnection.createClob() is not yet implemented.
spring.jpa.properties.hibernate.temp.use_jdbc_metadata_defaults= false

6. Entity, Model, Form, DAO

In JPA (or Hibernate), Entity is a representive class (corresponding to) a table in a database. The fields of this class will correspond to the columns of the table.
We will create a BankAccount class to represent for the BANK_ACCOUNT in the database. JPA Annotations will be used to annotate on the fields to describe mappings between the fields and the columns of the table. These mappings is 1-1. Each field corresponds to 1 column of the table.

BankAccount.java

package org.o7planning.springbootjpa.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "Bank_Account" )
public class BankAccount {

    @Id
    @GeneratedValue
    @Column(name = "id", nullable = false)
    private Long id;

    @Column(name = "Full_Name", length = 128, nullable = false)
    private String fullName;

    @Column(name = "Balance", nullable = false)
    private double balance;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getFullName() {
        return fullName;
    }

    public void setFullName(String fullName) {
        this.fullName = fullName;
    }

    public double getBalance() {
        return balance;
    }

    public void setBalance(double balance) {
        this.balance = balance;
    }
}

While an Entity class represents for the data of a record of a table, a Model class represents for the data of one record of a query statement (joined from one or many tables). You use the Model class when you are interested in some columns of one or many tables.

BankAccountInfo.java

package org.o7planning.springbootjpa.model;

public class BankAccountInfo {

   private Long id;
   private String fullName;
   private double balance;

   public BankAccountInfo() {

   }

   // Used in JPA query.
   public BankAccountInfo(Long id, String fullName, double balance) {
      this.id = id;
      this.fullName = fullName;
      this.balance = balance;
   }

   public Long getId() {
      return id;
   }

   public void setId(Long id) {
      this.id = id;
   }

   public String getFullName() {
      return fullName;
   }

   public void setFullName(String fullName) {
      this.fullName = fullName;
   }

   public double getBalance() {
      return balance;
   }

   public void setBalance(double balance) {
      this.balance = balance;
   }
}

To work with a traditional JPA, the first thing you need to do is creating an EntityManager object. With the Spring Boot, everything is really easy. It is automatically configured to and creates EntityManager and manages it as a Spring BEAN. And in the application you can use the EntityManager object anywhere you want.

BankAccountDAO.java

package org.o7planning.springbootjpa.dao;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.o7planning.springbootjpa.entity.BankAccount;
import org.o7planning.springbootjpa.exception.BankTransactionException;
import org.o7planning.springbootjpa.model.BankAccountInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

@Repository
public class BankAccountDAO {

	@Autowired
	private EntityManager entityManager;
	 

	public BankAccountDAO() {
	}

	public BankAccount findById(Long id) { 
		return this.entityManager.find(BankAccount.class, id);
	}

	public List<BankAccountInfo> listBankAccountInfo() {
		String sql = "Select new " + BankAccountInfo.class.getName() //
				+ "(e.id,e.fullName,e.balance) " //
				+ " from " + BankAccount.class.getName() + " e ";
		Query query = entityManager.createQuery(sql, BankAccountInfo.class);
		return query.getResultList();
	}

	// MANDATORY: Transaction must be created before.
	@Transactional(propagation = Propagation.MANDATORY )
	public void addAmount(Long id, double amount) throws BankTransactionException {
		BankAccount account = this.findById(id);
		if (account == null) {
			throw new BankTransactionException("Account not found " + id);
		}
		double newBalance = account.getBalance() + amount;
		if (account.getBalance() + amount < 0) {
			throw new BankTransactionException(
					"The money in the account '" + id + "' is not enough (" + account.getBalance() + ")");
		}
		account.setBalance(newBalance);
	}

	// Do not catch BankTransactionException in this method.
	@Transactional(propagation = Propagation.REQUIRES_NEW, 
			            rollbackFor = BankTransactionException.class)
	public void sendMoney(Long fromAccountId, Long toAccountId, double amount) throws BankTransactionException {

		addAmount(toAccountId, amount);
		addAmount(fromAccountId, -amount);
	}

}

BankTransactionException.java

package org.o7planning.springbootjpa.exception;

public class BankTransactionException extends Exception {
 
    private static final long serialVersionUID = -3128681006635769411L;
    
    public BankTransactionException(String message) {
        super(message);
    }

}

SendMoneyForm.java

package org.o7planning.springbootjpa.form;

public class SendMoneyForm {

    private Long fromAccountId;
    private Long toAccountId;
    private Double amount;

    public SendMoneyForm() {

    }

    public SendMoneyForm(Long fromAccountId, Long toAccountId, Double amount) {
        this.fromAccountId = fromAccountId;
        this.toAccountId = toAccountId;
        this.amount = amount;
    }

    public Long getFromAccountId() {
        return fromAccountId;
    }

    public void setFromAccountId(Long fromAccountId) {
        this.fromAccountId = fromAccountId;
    }

    public Long getToAccountId() {
        return toAccountId;
    }

    public void setToAccountId(Long toAccountId) {
        this.toAccountId = toAccountId;
    }

    public Double getAmount() {
        return amount;
    }

    public void setAmount(Double amount) {
        this.amount = amount;
    }
}

Explain the operation mechanism of Spring Transaction:

In this example, I simulate a bank transaction. A account sends to B account an amount of 700$. Thus, two actions will be created in the database:

Add 700$ to B account.
Subtract 700$ from A account.

If the first action succeeds (add $ 700 to B account), but the second action fails for a reason, the bank will suffer a damage.
Therefore, it needs to manage the transaction to ensure that if an action fails, the data will rollback the original state (before transaction). The transaction is considered successful when all actions are successful.

Usethe @Transactional(rollbackFor = BankTransactionException.class) to annotate on a method to tell the "Spring Transaction" "let's apply the AOP to this method".

@Transactional(propagation = Propagation.REQUIRES_NEW,
                         rollbackFor = BankTransactionException.class)
public void sendMoney(Long fromAccountId, Long toAccountId,
                       double amount) throws BankTransactionException {

    addAmount(toAccountId, amount);
    addAmount(fromAccountId, -amount);
}

The Spring Transaction applies the Spring AOP to your method, which is like changing code of the method, add code to catch the exception and call Rollback transaction when the exception occurs, then it rethrows the exception out of the method. All are the same as the following illustration:

7. Controller

MainController.java

package org.o7planning.springbootjpa.controller;

import java.util.List;

import org.o7planning.springbootjpa.dao.BankAccountDAO;
import org.o7planning.springbootjpa.exception.BankTransactionException;
import org.o7planning.springbootjpa.form.SendMoneyForm;
import org.o7planning.springbootjpa.model.BankAccountInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;  
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

@Controller
public class MainController {

    @Autowired
    private BankAccountDAO bankAccountDAO;

    @RequestMapping(value = "/", method = RequestMethod.GET)
    public String showBankAccounts(Model model) {
        List<BankAccountInfo> list = bankAccountDAO.listBankAccountInfo();

        model.addAttribute("accountInfos", list);

        return "accountsPage";
    }

    @RequestMapping(value = "/sendMoney", method = RequestMethod.GET)
    public String viewSendMoneyPage(Model model) {

        SendMoneyForm form = new SendMoneyForm(1L, 2L, 700d);

        model.addAttribute("sendMoneyForm", form);

        return "sendMoneyPage";
    }

 
    @RequestMapping(value = "/sendMoney", method = RequestMethod.POST)
    public String processSendMoney(Model model, SendMoneyForm sendMoneyForm) {

        System.out.println("Send Money: " + sendMoneyForm.getAmount());

        try {
            bankAccountDAO.sendMoney(sendMoneyForm.getFromAccountId(), //
                    sendMoneyForm.getToAccountId(), //
                    sendMoneyForm.getAmount());
        } catch (BankTransactionException e) {
            model.addAttribute("errorMessage", "Error: " + e.getMessage());
            return "/sendMoneyPage";
        }
        return "redirect:/";
    }

}

8. Thymeleaf Template

_menu.html

<div xmlns:th="http://www.thymeleaf.org"
     style="border: 1px solid #ccc;padding:5px;margin-bottom:20px;">

  <a th:href="@{/}">Accounts</a>

     | &nbsp;

   <a th:href="@{/sendMoney}">Send Money</a>  
  

</div>

accountsPage.html

<!DOCTYPE HTML>
<html xmlns:th="http://www.thymeleaf.org">
<head>
<title>Bank</title>

<style>
th, td {
    padding: 5px;
}
</style>

</head>

<body>
    <!-- Include _menu.html -->
    <th:block th:include="/_menu"></th:block>

    <h2>Accounts</h2>

    <table border="1">
        <tr>
            <th>ID</th>
            <th>Full Name</th>
            <th>Balance</th>
        </tr>
        <tr th:each="accountInfo : ${accountInfos}">
            <td th:utext="${accountInfo.id}">..</td>
            <td th:utext="${accountInfo.fullName}">..</td>
            <td th:utext="${accountInfo.balance}">..</td>
        </tr>

    </table>
</body>
</html>

sendMoneyPage.html

<!DOCTYPE HTML>
<html xmlns:th="http://www.thymeleaf.org">
   <head>
      <title>Bank</title>
   </head>
   
   <body>      
      <!-- Include _menu.html -->
      <th:block th:include="/_menu"></th:block>
      
      <h2>Send Money</h2>
      <ul>
         <li>1 - Tom</li>
         <li>2 - Jerry</li>
         <li>3 - Donald</li>
      </ul>
      
      <div th:if="${errorMessage!=null}"
           style="color:red;font-style:italic" th:utext="${errorMessage}">..</div>
      
      <form th:action="@{/sendMoney}" th:object="${sendMoneyForm}" method="POST">
         <table>

           <tr>
              <td>From Bank Account Id</td>
              <td><input type="text" th:field="*{fromAccountId}"/></td>
           </tr>
           <tr>
              <td>To Bank Account Id</td>
              <td><input type="text" th:field="*{toAccountId}"/></td>
           </tr>
            <tr>
              <td>Amount</td>
              <td><input type="text" th:field="*{amount}" /></td>
           </tr>           
           <tr>
              <td>&nbsp;</td>
              <td><input type="submit" value="Send"/></td>
           </tr>       
         </table>      
      </form>
      
   </body>
</html>

9. Run the application

On Eclipse, run your application.

http://localhost:8080