Data Persistency Tools (JPA, Hybernate, Mybatis)

Many tools help us do data operations, such as JPA and Mybatis in Spring Boot. In this posting, we will check out some of the Database Persistence tools.

List of Contents

• JPA
  • Dependencies
  • Annotations
  • JPA settings
    • Entities
    • Repositories
    • Services
    • Controllers
    • Using Spring Rest
  • Using H2 DB
• Hibernate
  • Dependencies
  • Annotations
  • hibernate settings
    • Entities
    • DAO
    • CRUD
      • Get List
      • Filter
      • Get One
      • Add
      • Update
      • Delete
      • Completed Codes
    • DB setup
    • Application Properties
  • Test
• Mybatis
  • Dependencies
  • How Dose it Work?
  • Annotations
  • Mapper Interface Queries
  • Dynamic SQL
  • Mybatis Settings
    • Mappers
    • Controllers
    • Entities
    • Application Class Settings
  • MySQL DB Setup
    • Application Properties
  • Query Example
    • Get List
    • Get One
    • Add
    • Update
    • Delete
    • Completed Codes

JPA

JPA (Java Persistence API) contains the definition of a relational database in the Java application and helps to manipulate data to execute operations between the service and the database. It can also be integrated with the Hibernate.

Dependencies

▶ JPA

Creates tables from the entities

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

▶ Spring Web

Runs web server and provides annotations for HTTP requests and responses

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

▶ Spring Rest

Provides JPA CRUD endpoints for free.

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

Annotations

@RestController

Marks a class as a controller that can send data

@RestController

@Autowired

Registers a mapper to the spring bean

@Autowired

JPA settings

Entities

Create a entity package and add a class, add @Entity, @Id annotation (@Entity, @Id, and a constructor without parameters are required)

Repositories

Create a repository package and add a class. Extend the interface shown below and add the entity and its PK type as generic parameters

JpaRepository<<entityName>, <PKType>>

Services

@Service
public class EmployeeService {
    @Autowired
    EmployeeRepository employeeRepository;   
}

package com.example.employee.service;

import com.example.employee.entity.Address;
import com.example.employee.entity.Employee;
import com.example.employee.repository.EmployeeRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;

@Service
public class EmployeeService {
    @Autowired
    EmployeeRepository employeeRepository;

    public List<Employee> getEmployees() {
        return employeeRepository.findAll();
    }

    public Employee getEmployee(int employeeId) {
        return employeeRepository.findById(employeeId).orElseThrow(() -> new RuntimeException("Not found"));
    }

    public void addEmployee(Employee employee) {
        employeeRepository.save(employee);
    }

    public void updateEmployee(Employee employee) {
        employeeRepository.save(employee);
    }

    public void deleteEmployee(int id) {
        employeeRepository.delete(employeeRepository.getReferenceById(id));
    }
}

Controllers

@RestController
public class EmployeeController {
    @Autowired
    EmployeeService employeeService;
}

package com.example.employee.controller;
import com.example.employee.entity.Employee;
import com.example.employee.service.EmployeeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import java.util.List;

@RestController
public class EmployeeController {
    @Autowired
    EmployeeService employeeService;

    @GetMapping("/employees")
    public List<Employee> getEmployees() {
        return employeeService.getEmployees();
    }

    @GetMapping("/employees/{id}")
    public Employee getEmployee(@PathVariable int id) {
        return employeeService.getEmployee(id);
    }

    @PostMapping("/employees")
    public void addEmployee(@RequestBody Employee employee) {
        employeeService.addEmployee(employee);
    }

    @PutMapping("/employees/{id}")
    public void updateEmployee(@PathVariable int id, @RequestBody Employee employee) {
        employeeService.updateEmployee(employee);
    }

    @DeleteMapping("/employees/{id}")
    public void deleteEmployee(@PathVariable int id) {
        employeeService.deleteEmployee(id);
    }
}

Using Spring Rest

Spring REST is a dependency that provides free endpoints for CRUD operations using the entity and the JpaRepository

 

Add the dependency to the pom.xml file

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

Leave the entity and repository (DAO) but remove the controller and service

---

※ By default, Spring creates endpoints by adding 's' to the provided class name in the DAO

To change it use the below annotation

@RepositoryRestResource(path = "<path>")

※ Using URL parameters we can sort and paginate

▶ Paginations

http://localhost:8080/employees?page=<index>

▶ Pagination size setup (application.properties)

spring.data.rest.default-page-size=<val>

▶ Sorting (asc)

http://localhost:8080/employees?sort=<condition>

▶ Sorting (desc)

http://localhost:8080/employees?sort=<condition>,desc

▶ Sorting (multiple conditions)

http://localhost:8080/employees?sort=<condition>,<condition>

---

 

Using H2 DB

Spring Boot API

Hibernate

Hibernate is a JPA specification implementation for mapping the Java class to the database table (ORM).

Dependencies

▶ JPA

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

▶ MySQL

<dependency>
  <groupId>com.mysql</groupId>
  <artifactId>mysql-connector-j</artifactId>
  <scope>runtime</scope>
</dependency>

Annotations

▶ @Entity

Used to create JPA class table

@Entity

▶ @Table

By default, the JPA class table uses the class name as its name. We can change the default name with this annotation

@Table(name="<name>")

▶ @Id

Sets the primary key in the relational database

@Id

▶ @GeneratedValue

An option to automatically create the primary key

@GeneratedValue(strategy = GenerationType.IDENTITY)

▶ @Transaction

Need to insert, update, and delete data in the database

@Transaction

hibernate settings

Entities

Create an entity class with an @Entity annotation

package com.example.cruddemo.entity;

import jakarta.persistence.*;

@Entity
@Table(name = "employee")
public class Employee {

    @Id()
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private int id;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;

    @Column(name = "email")
    private String email;

    public Employee() {
    }

    public Employee(String firstName, String lastName, String email) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.email = email;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", firstName='" + firstName + '\'' +
                ", lastName='" + lastName + '\'' +
                ", email='" + email + '\'' +
                '}';
    }

    public int getId() {
        return id;
    }

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

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

DAO

Add a DAP interface

Implement the interface and inject the JPA EntityManager.  EntityManager is used for saving and retrieving entities

As of this writing, @SpringBootApplication Spring shows an error saying there is no Entity Manager bean found but we can ignore this as it is a false alarm (alternately, use @Configuration, @EnableAutoConfiguration, and @ComponentScan instead of @SpringBootApplication to remove the false alarm).

CRUD

Get List

▶ DAO

List<Employee> findAll();

▶ DAO Imple

@Override
public List<Employee> findAll() {
    TypedQuery<Employee> query = entityManager.createQuery("FROM Employee order by lastName", Employee.class);
    return query.getResultList();
}

Filter

▶ DAO

List<Employee> findByLastName(String lastName);

▶ DAO Imple

@Override
public List<Employee> findByLastName(String lastName) {
    TypedQuery<Employee> query = entityManager.createQuery("FROM Employee WHERE lastName=:name", Employee.class);
    query.setParameter("name", lastName);
    return query.getResultList();
}

Get One

▶ DAO

Employee findById(int id);

▶ DAO Imple

@Override
public Employee findById(int id) {
    return entityManager.find(Employee.class, id);
}

Add

▶ DAO

void save(Employee employee);

▶ DAO Imple

@Override
@Transactional
public void save(Employee student) {
    entityManager.persist(student);
}

Update

▶ DAO

void update(Employee employee);

▶ DAO Imple

@Override
@Transactional
public void update(Employee student) {
    entityManager.merge(student);
}

Delete

▶ DAO

void delete(int id);

▶ DAO Imple

@Override
@Transactional
public void delete(int id) {
    Employee student = entityManager.find(Employee.class, id);
    entityManager.remove(student);
}

Completed Codes

▶ DAO

package com.example.cruddemo.dao;

import com.example.cruddemo.entity.Employee;

import java.util.List;

public interface EmployeeDAO {

    List<Employee> findAll();

    List<Employee> findByLastName(String lastName);

    Employee findById(int id);

    void save(Employee employee);

    void update(Employee employee);

    void delete(int id);

    int deleteAll();
}

▶ DAO Imple

package com.example.cruddemo.dao;

import com.example.cruddemo.entity.Employee;
import jakarta.persistence.EntityManager;
import jakarta.persistence.TypedQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Repository
public class EmployeeDAOImpl implements EmployeeDAO {

    private EntityManager entityManager;

    @Autowired
    public EmployeeDAOImpl(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Override
    @Transactional
    public void save(Employee student) {
        entityManager.persist(student);
    }

    @Override
    public Employee findById(int id) {
        return entityManager.find(Employee.class, id);
    }

    @Override
    public List<Employee> findAll() {
        TypedQuery<Employee> query = entityManager.createQuery("FROM Employee order by lastName", Employee.class);

        return query.getResultList();
    }

    @Override
    public List<Employee> findByLastName(String lastName) {
        TypedQuery<Employee> query = entityManager.createQuery("FROM Employee WHERE lastName=:name", Employee.class);
        query.setParameter("name", lastName);
        return query.getResultList();
    }

    @Override
    @Transactional
    public void update(Employee student) {
        entityManager.merge(student);
    }

    @Override
    @Transactional
    public void delete(int id) {
        Employee student = entityManager.find(Employee.class, id);
        entityManager.remove(student);
    }

    @Override
    @Transactional
    public int deleteAll() {
        int deleted = entityManager.createQuery("DELETE FROM Employee").executeUpdate();
        return deleted;
    }
}

DB setup

Application Properties

Let's create a DB first open MySQL and run the script below

spring.datasource.url=jdbc:mysql://localhost:3306/employee_record
spring.datasource.username=<userName>
spring.datasource.password=<password>
#log for SQL
logging.level.org.hibernate.SQL=debug
#log level for SQL
logging.level.org.hibernate.orm.jdbc.bind=debug
#auto create
spring.jpa.hibernate.ddl-auto=create

---

The 'spring.jpa.hibernate.ddl-auto=create' property automatically creates database. It is equivalent to running the script below to create a DB

CREATE DATABASE  IF NOT EXISTS `employee_record`;
USE `employee_record`;

DROP TABLE IF EXISTS `employee`;

CREATE TABLE `employee` (
  `id` int NOT NULL AUTO_INCREMENT,
  `first_name`varchar(45) DEFAULT NULL,
  `last_name` varchar(45) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

---

Test

Open the application class and add 'CommnadLineRunner' to test

@Bean
public CommandLineRunner commandLineRunner(EmployeeDAO employeeDAO) {
    return runner -> {            
    };
}

▶ Completed Code

package com.example.cruddemo;

import com.example.cruddemo.dao.EmployeeDAO;
import com.example.cruddemo.entity.Employee;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;

import java.util.List;

@SpringBootApplication
public class CrudDemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(CrudDemoApplication.class, args);
    }

    @Bean
    public CommandLineRunner commandLineRunner(EmployeeDAO employeeDAO) {
        return runner -> {
            createEmployee(employeeDAO);
            getEmployee(employeeDAO);
            getEmployees(employeeDAO);
            getEmployeesByLastName(employeeDAO);
            updateEmployee(employeeDAO);
            deleteEmployee(employeeDAO);
            deleteAll(employeeDAO);
        };
    }

    private void deleteAll(EmployeeDAO employeeDAO) {
        int deleted = employeeDAO.deleteAll();
        System.out.println(deleted);
    }

    private void deleteEmployee(EmployeeDAO employeeDAO) {
        int id = 1;
        employeeDAO.delete(1);
    }

    private void updateEmployee(EmployeeDAO employeeDAO) {
        int id = 1;
        Employee employee = employeeDAO.findById(id);
        employee.setFirstName("Stone");
        employeeDAO.update(employee);
        System.out.println(employee);
    }

    private void getEmployeesByLastName(EmployeeDAO employeeDAO) {
        List<Employee> employees = employeeDAO.findByLastName("Pa");
        for (Employee employee : employees) {
            System.out.println(employee);
        }
    }

    private void getEmployees(EmployeeDAO employeeDAO) {
        List<Employee> employees = employeeDAO.findAll();
        for (Employee employee : employees) {
            System.out.println(employee);
        }
    }

    private void getEmployee(EmployeeDAO employeeDAO) {
        System.out.println("employeeDAO.findById(1) = " + employeeDAO.findById(1));
    }

    private void createEmployee(EmployeeDAO employeeDAO) {
        System.out.println("created");
        Employee employee = new Employee("Tim", "Pa", "pa@c.com");

        employeeDAO.save(employee);
    }
}

Mybatis

It was developed in 2002 by Clinton Begin and MyBatis is a renewal version of the original. It provides features such as auto-mapping between the entities and SQL.

 

The biggest difference from other tools is that Mybatis uses SQL (To separate the logic and SQL, mappings are packaged in the XML) rather than customer queries, and its light size makes the framework faster than the others. It can also work with .NET and Ruby on Rails

Dependencies

▶ Spring Web

Runs web server and provides annotations for HTTP requests and responses

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

▶ Mybatis

A dependency for the Mybatis

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>3.0.2</version>
</dependency>

▶ MySQL Driver

A dependency for the MySQL

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

How Dose it Work?

After adding the dependencies, an XML configuration file is created by the SQLSessionFactoryBuilder의 builder() method. In the file, there are data source definition, transaction manager information, and a mapper that defines the relation between entities that creates the SQLSessionFactory instance.

Annotations

@MapperScan

Registers the Mybatis mapper interface. The annotation can be added to the application class or application configuration class. For the value, package name followed by '.mapper' is used

@MapperScan("<packageName>.mapper")

@MappedTypes

Defines the Java type that needs a mapping. The annotation can be added to the application class or application configuration class. When adding multiple entities, simply add multiple values separated by ','

@MappedTypes(<entity>.class)

@Mapper

Marks a class as a mapper interface

@Mapper

@RestController

Marks a class as a controller that can send data

@Autowired

@Autowired

Registers a mapper to the spring bean

@Autowired

Mapper Interface Queries

▶ CRUD

@Insert("Insert into person(name) values (#{name})")
public Integer save(Person person);

@Update("Update Person set name= #{name} where personId=#{personId}")
public void updatePerson(Person person);

@Delete("Delete from Person where personId=#{personId}")
public void deletePersonById(Integer personId);

@Select("SELECT person.personId, person.name FROM person 
  WHERE person.personId = #{personId}")
Person getPerson(Integer personId);

▶ Results

Shows the Java class attribute list for the respective data column

@Select("Select personId, name from Person where personId=#{personId}")
@Results(value = {
  @Result(property = "personId", column = "personId")
    // ...   
})
public Person getPersonById(Integer personId);

▶ Result

An item in the results list

@Results(value = {
  @Result(property = "personId", column = "personId"),
  @Result(property="name", column = "name"),
  @Result(property = "addresses", javaType =List.class) 
    // ... 
})
public Person getPersonById(Integer personId);

▶ Many

Maps one object to many objects

@Results(value ={
  @Result(property = "addresses", javaType = List.class, 
    column = "personId",
    many=@Many(select = "getAddresses"))
})

▶ Mapkey

Converts the returned list to a map

@Select("select * from Person")
@MapKey("personId")
Map<Integer, Person> getAllPerson();

▶ Options

For settings

@Insert("Insert into address (streetAddress, personId) 
  values(#{streetAddress}, #{personId})")
@Options(useGeneratedKeys = false, flushCache=true)
public Integer saveAddress(Address address);

Dynamic SQL

public class MyBatisUtil {
 
    // ...
 
    public String getPersonByName(String name){
        return new SQL() {{
            SELECT("*");
            FROM("person");
            WHERE("name like #{name} || '%'");
        }}.toString();
    }
}

Mybatis Settings

Mappers

It is one of the Java interfaces that maps each method to its responding SQL syntax

Create a mapper package and add an interface then add the @Mapper annotation

Controllers

Create a controller package and add a class. Add necessary annotations (In this example, I am using the @RestController to send data). Then add either a constructor or the @Autowired annotation for the dependency injection

Entities

Create an entity package and add a class. Add a constructor, getters, and setters. With Mybatis, we don't need to add an annotation for mapping, it will be automatically mapped.

Application Class Settings

In the application class, add the @MappedScan and @MapperTypes. For the @MappedScan, add the package name followed by '.mapper' and for the @MapperTypes, add the entity class to use as the value

@MappedTypes(User.class)
@MapperScan("com.example.mybatis.mapper")
@SpringBootApplication
public class MybatisApplication {

    public static void main(String[] args) {
        SpringApplication.run(MybatisApplication.class, args);
    }
}

MySQL DB Setup

Since the connection settings rarely differ between different databases, we will only see how to use MySQL as an example

 

Create an instance in the MySQL. Note the instance path, user name, and password

Create a database and tables in the instance. Add some data for the tests later

CREATE SCHEMA details;
USE details;

DROP TABLE IF EXISTS details.user;
CREATE TABLE  details.user(
   ID int(10) NOT NULL AUTO_INCREMENT,  
   NAME varchar(100) NOT NULL,
   EMAIL varchar(255) NOT NULL,
   PRIMARY KEY ( ID )
);

INSERT INTO user
VALUES(1, "TOM", "t@t.com");

SELECT * FROM user;

Application Properties

Open the application.properties file and add the code shown below

spring.datasource.url=jdbc:mysql://<databasePath>
spring.datasource.username=<userName>
spring.datasource.password=<password>
spring.datasource.tomcat.test-while-idle=true
spring.datasource.tomcat.validation-query=SELECT 1

Query Example

Get All

▶ Mapper

@Select("select * from user")
List<User> findAll();

▶ Controller

@GetMapping("")
public List<User> getAll() {
    return userMapper.findAll();
}

Get One

▶ Mapper

@Select("select * from user where id = #{id}")
User findOne(int id);

▶ Controller

@GetMapping("/{id}")
public User getOne(@PathVariable int id) {
    return userMapper.findOne(id);
}

Add

▶ Mapper

@Insert("insert into user(name, email) values(#{name}, #{email})")
@SelectKey(statement = "select last_insert_id()", keyProperty = "id", before = false, resultType = Integer.class)
void addOne(User user);

▶ Controller

@GetMapping("/add")
public List<User> addOne() {
    User user = new User();
    user.setName("Jack");
    user.setEmail("ja@ck.com");
    userMapper.addOne(user);
    return userMapper.findAll();
}

Update

▶ Mapper

@Update("update user set name = #{name}, email = #{email} where id = #{id}")
void updateOne(User user);

▶ Controller

@GetMapping("/update/{id}")
public List<User> updateOne(@PathVariable int id) {
    User user = new User();
    user.setName("Steve");
    user.setEmail("steve@mail.com");
    user.setId(id);
    userMapper.updateOne(user);
    return userMapper.findAll();
}

Delete

▶ Mapper

@Delete("delete from user where id = #{id}")
void deleteUser(int id);

▶ Controller

@GetMapping("/delete/{id}")
public List<User> deleteUser(@PathVariable int id) {
    userMapper.deleteUser(id);
    return userMapper.findAll();
}

Completed Codes

▶ Mapper

import com.example.mybatis.entity.User;
import org.apache.ibatis.annotations.*;
import java.util.List;

@Mapper
public interface UserMapper {

    @Select("select * from user")
    List<User> findAll();

    @Insert("insert into user(name, email) values(#{name}, #{email})")
    @SelectKey(statement = "select last_insert_id()", keyProperty = "id", before = false, resultType = Integer.class)
    void addOne(User user);

    @Select("select * from user where id = #{id}")
    User findOne(int id);

    @Update("update user set name = #{name}, email = #{email} where id = #{id}")
    void updateOne(User user);

    @Delete("delete from user where id = #{id}")
    void deleteUser(int id);
}

▶ Controller

import com.example.mybatis.entity.User;
import com.example.mybatis.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class UserController {

    @Autowired
    private UserMapper userMapper;

    @GetMapping("")
    public List<User> getAll() {
        return userMapper.findAll();
    }

    @GetMapping("/add")
    public List<User> addOne() {
        User user = new User();
        user.setName("Jack");
        user.setEmail("ja@ck.com");
        userMapper.addOne(user);
        return userMapper.findAll();
    }

    @GetMapping("/{id}")
    public User getOne(@PathVariable int id) {
        return userMapper.findOne(id);
    }

    @GetMapping("/update/{id}")
    public List<User> updateOne(@PathVariable int id) {
        User user = new User();
        user.setName("Steve");
        user.setEmail("steve@mail.com");
        user.setId(id);
        userMapper.updateOne(user);
        return userMapper.findAll();
    }

    @GetMapping("/delete/{id}")
    public List<User> deleteUser(@PathVariable int id) {
        userMapper.deleteUser(id);
        return userMapper.findAll();
    }
}

In this writing, we have seen some of the data persistence tools in the Spring boot

 

Reference

https://stackoverflow.com/questions/26889970/intellij-incorrectly-saying-no-beans-of-type-found-for-autowired-repository

intellij incorrectly saying no beans of type found for autowired repository

▶ Application Properties

Common Application Properties (spring.io)

Common Application Properties