Dev-Mind

Spring in practice : database and JPA (EN)

11/10/2018
Java  Spring 

How use a database in your Spring project with Spring Data JPA and a H2 Database? Hibernate will be the JPA implementation

spring data

Database

An application needs access to data, write data, update these data …​

app data

Today we can access a multitude of data sources …​ and Spring will help us

app data2

How can we persist data in Java?

  1. a low level standard : JDBC (Java Database Connectivity)

  2. an API, JPA (Java Persistence API) and frameworks as Hibernate

  3. an API, JTA (Java Transaction API) to manage transactions

JDBC

  • common API used by all relational databases

  • each DBMS editor (DataBase Management System) provides its driver (a jar added to your project)

  • each DBMS accept SQL code to execute request and execute order on the database

In our tests we will use a database written in Java, the H2 database

h2 logo
  • Open source, JDBC driver

  • embedded database

  • in memory database (perfect for tests)

  • Browser based Console application

  • Small footprint

Lab 3 : Database and SQL

Go in your FaircorpApplication. We need to use these Spring Boot starters

implementation('org.springframework.boot:spring-boot-starter-data-jpa')
implementation('org.springframework.boot:spring-boot-starter-web')
implementation('com.h2database:h2')

Check your build.gradle file and update it

Spring Boot analyses jars defined in classpath and Spring auto-configures features as the database, the H2 console…​

Add some properties in file src/main/resources/application.properties

# Spring boot : datasource
spring.datasource.url=jdbc:h2:mem:faircorp;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.platform=h2
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.sqlScriptEncoding=UTF-8

# Spring boot : H2 datasource
spring.h2.console.enabled=true
spring.h2.console.path=/console

We configure a in memory database

You can use the H2 console. URL will be http://localhost:8080/console

h2 console0

Use JDBC URL, user, password defined in your application.properties

You should access to the console

h2 console

SQL order to create a table
Id is generated by the database (option auto_increment)

CREATE TABLE ROOM(ID BIGINT auto_increment PRIMARY KEY, NAME VARCHAR(255) NOT NULL);

SQL order to insert data in this table
We use a negative id because we don’t use the ID generator in manual inserts

INSERT INTO ROOM(ID, NAME) VALUES(-10, 'Room1');

SQL order to select this data

SELECT * FROM ROOM;

Java and JDBC

Database connection in Java

try {
  Class.forName("org.h2.Drive"); // 1.
}
catch (ClassNotFoundException e) {
  logger.error("Unable to load JDBC Driver", e);
}
try {
  String database_url = "jdbc:h2:mem:bigcorp;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE" // 2.
  Connection connection = DriverManager.getConnection(database_url, username, password); // 3.
}
catch (SQLException e) {
  logger.error("Unable to connect to the database", e);
}
  1. Load JDBC driver (here H2 driver)

  2. We define the URL to access to the database (here we say that we use a H2 database in memory)

  3. Open a connection with username/password

But a database connection is slow. We need to use a connection pool with preopened connections. Several connections are opened when the pool is launched.

pool

When you want to execute a request in Java you can write

For an insert

public void insertSite(Site site) {
    try(Connection conn = dataSource.getConnection()){
        String sql = "insert into SITE (id, name) values (?, ?)";
        try(PreparedStatement stmt = conn.prepareStatement(sql)){
          stmt.setString(1, site.getId());
          stmt.setString(2, site.getName());
          stmt.executeUpdate();
        }
    }
    catch(SQLException e) {
        throw new DatabaseException("Impossible to insert site " +
            site.getName(), e);
    }
}

For a select

public List<Site> findAll() {
    List<Site> sites = new ArrayList<>();
    try(Connection conn = dataSource.getConnection()){
        String sql = "select id, name from SITE";
        try(PreparedStatement stmt = conn.prepareStatement(sql)){
            try (ResultSet resultSet = stmt.executeQuery()) {
                while(resultSet.next()) {
                    Site s = new Site(resultSet.getString("name"));
                    s.setId(resultSet.getString("id"));
                    sites.add(s);
                }
            }
        }
    }
    catch(SQLException e) {
        throw new DatabaseException("Impossible to read sites", e);
    }
    return sites;
}
  • The code is heavy and difficult to read

  • We need to manipulate data types in SQL and in our Java entities

  • We manipulate SQL while we are in an object language

  • We would like to be more productive, simplified relationship management…​

  • What about transactions?

Transaction

transaction1
  • What happens if a query fails, or if an exception occurs?

  • What happens if 2 transactions run in parallel?

  • What happens if a request is too long?

The solution is to work in a transaction

public void insertSite(Site site) {
        try(Connection conn = dataSource.getConnection()){
            conn.setAutoCommit(false); // 1.
            String sql = "insert into SITE (id, name) values (?, ?)";

            try(PreparedStatement stmt = conn.prepareStatement(sql)){
                stmt.setString(1, site.getId());
                stmt.setString(2, "toto&apos;);drop table SITE CASCADE; Select (&apos;1");
                stmt.executeUpdate();
                conn.commit(); // 2.
            }
            catch(SQLException e) {
                conn.rollback(); // 3.
                throw new DatabaseException("Impossible ins&#xE9;rer site " + site.getName(), e);
            }
        }
        catch(SQLException e) {
            throw new DatabaseException("Impossible ins&#xE9;rer site " + site.getName(), e);
        }
    }
  1. We disable autocommit

  2. If everything is OK a commit persit my changes

  3. If we have an error everything is cancelled

transaction2

JPA

With Persistence API/Framework, the approach is to :

  • work with POJO ⇒ Plain Old Java Objects, Java entities

  • add annotations to map entity properties to table columns

  • generate common database request (Create, Update, Delete, Read)

  • fill the SQL imperfections: inheritance, relationships, customs types, validation

Spring provides several sub projects to make database interactions easy

app data3
Note

Do not confuse Spring Data with Spring Data JPA. We can read on in the offical doc that "Spring Data’s mission is to provide a familiar and consistent, Spring-based programming model for data access while still retaining the special traits of the underlying data store. It makes it easy to use data access technologies, relational and non-relational databases, map-reduce frameworks, and cloud-based data services. This is an umbrella project which contains many subprojects that are specific to a given database […​]

Spring Data JPA is part of Spring Data, lets implement JPA based repositories. It makes it easier to build Spring-powered applications that use data access technologies."

The Java Persistence API (JPA) is a Java application programming interface specification that describes the management of relational data in applications using Java Platform, Standard Edition and Java Platform, Enterprise Edition.

Hibernate ORM is the JPA implementation that we’re going to use in this lab.

We’re going to use Spring Data JPA to store and retrieve data in our relational database.

JPA Entity

import javax.persistence.*;

@Entity // 1.
@Table(name = "SP_SENSOR") // 2.
public class Sensor {
    @Id // 3.
    @GeneratedValue
    private Long id;

    @Column(nullable = false, length=255)  // 4.
    private String name;

    private String description;

    @Column(name = "power") // 4.
    private Integer defaultPowerInWatt;

    @Transient // 5.
    private Integer notImportant;

    @Enumerated(EnumType.STRING) // 6.
    private PowerSource powerSource;

    public Sensor() { // 7.
    }

    public Sensor(String name) { // 8.
        this.name = name;
    }

    public Long getId() { // 9.
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Integer getDefaultPowerInWatt() {
        return defaultPowerInWatt;
    }

    public void setDefaultPowerInWatt(Integer defaultPowerInWatt) {
        this.defaultPowerInWatt = defaultPowerInWatt;
    }

    public Integer getNotImportant() {
        return notImportant;
    }

    public void setNotImportant(Integer notImportant) {
        this.notImportant = notImportant;
    }

    public PowerSource getPowerSource() {
        return powerSource;
    }

    public void setPowerSource(PowerSource powerSource) {
        this.powerSource = powerSource;
    }
}
  • (1) indicates that this class is an entity managed by Hibernate

  • (2) you can customize the table name (optional)

  • (3) you have always an id annotated with @javax.persistence.Id (auto generated in this example). This ID is immutable (as the primary key in the database)

  • (4) by default, each property is mapped to a column. You can customize the nullability or the column name.

  • (5) If a property should not be persisted, use @Transient

  • (6) Java enum persisted as a String

  • (7) must have an empty constructor (public or protected).
    An empty constructor is needed to create a new instance via reflection (using Class<T>.newInstance()) by Hibernate which has to instantiate your Entity dynamically. If you don’t provide any additional constructors with arguments for the class, you don’t need to provide an empty constructor because you get one per default. Java always gives you a default invisible empty constructor. If an argument constructor is provided in your class, then jvm will not add the no-argument constructor.

  • (8) you can add a constructor to build an object with all required properties

  • (9) you have to define a getter and a setter for each property

Entity manager

em

Entity lifecycle

The entities managed by Hibernate have a life-cycle associated with them. Either you can create a new object and save it into the database or your can fetch the data from the database.

The Entities go through several stages in the life-cycle.

lifecycle

Transient Objects
Transient objects are non transactional and in fact Hibernate has no knowledge of these objects

Persistent Objects
Persistent entity has a valid database identity associated with.

Removed Object
An object scheduled for deletion either by calling delete or because of orphan deletion of entities.

Detached Object
The object in persistent state go into detached state after the persistent context is closed. Detached objects can be brought into other persistent context by reattachment or merging. Detached object still has a valid primary key attribute but it is no longer managed by Hibernate.

We have different operations to several stages in the life-cycle.

persist()
makes a persistent entity. It will be written in the database at the next commit of the transaction we are in..

remove()
inverse of persist(). It will be erased from the database at the next commit of the transaction we are in.

refresh()
synchronizes the state of an entity to its database state. If the fields of an entity have been updated in the current transaction, these changes will be canceled. This operation only applies to persistent entities (otherwise we have an IllegalArgumentException)

detach()
detaches an entity from entity manager. This entity will not be taken into account during the next commit of the transaction in which we are

merge()
attach an entity to the current entity manager. This is used to associate an entity with another entity manager than the one that was used to create or read it.

Transaction and Spring

We must work in transactions to ensure data integrity.

Transactional policy is managed by Spring with @Transactional annotation. For example

@Service
@Transactional
public class SiteServiceImpl implements SiteService {

    public Site addSite(String name){
        Site site = new Site(name);
        site.addSensor(new Sensor("default", site)
                               .withPowerSource(PowerSource.FIXED)
                               .withDefaultPowerInWatt(1_000_000));
        siteDao.save(site);
        return site;
    }
}

Your services, your components must use a @Transactional

Hibernate stores everything read from the database in a first-level cache. This cache is linked to the current transaction.
If you do twice a find() on the same instance, it will only be loaded once

At the end of the transaction, Hibernate will launch a flush() of this cache

  • Calculation of the modifications of the objects contained in this cache

  • Execution of all requests as a result

  • Launching commit() if everything is OK or rollback()

Association mappings

Association mappings are one of the key features of JPA and Hibernate. They define the relationship between the database tables and the attributes in your Entity.

relation

A relationship between JPA entities. can be unidirectional or bidirectional. In this second case, one of the two entities must be master and the other slave.

It defines in which direction you can use the relationship.

@OneToOne

Example of unidirectional relation: a city has a mayor but the mayor does not know his city

relation1to1

A column mayor_id will be added in the table and a foreign key will be created

@Entity
public class Mayor {
    @Id
    private Long id;
    private String name;

    //...
}


@Entity
public class Town {
     @Id
     private Long id;
     private String name;
     @OneToOne
     private Mayor mayor;

     // ...
}

Example of bidirectional relationship: a city has a mayor and the mayor now knows his city

A column mayor_id will be added in the Town table and a foreign key will be created but we have no town_id in the Mayor table (Master is town)

@Entity
public class Mayor {
    @Id
    private Long id;
    private String name;

    @OneToOne
    private Town town;
    //...
}


@Entity
public class Town {
    @Id
    private Long id;
    private String name;

    @OneToOne(mappedBy="mayor")
    private Mayor mayor;

    // Reste du code
}

mappedBy indicates that the Town table will be the master and will bring the relationship to the database.

@OneToMany

Example of unidirectional relation: a site has one or more sensors (sensor does not link to a site)

relation1ton

A join table is added (with 2 foreign keys)

@Entity
public class Sensor {
    @Id
    private Long id;
    private String name;

     // ...
}


@Entity()
public class Site {
    @Id
    private Long id;
    private String name;

    @OneToMany
    private Set<Sensor> sensors;

    // ...
}

@ManyToOne

Example of unidirectional relation: a measurement is linked to a sensor and the sensor does not have the list of measurements

relationnto1

A sensor_id column will be added to the Measure table and a foreign key will be created

@Entity
public class Sensor {
    @Id
    private Long id;
    private String name;

    // ...
}

@Entity
public class Measure {

    @Id
    private Long id;

    @ManyToOne(optional = false)
    private Sensor sensor;

    // ...
}

@OneToMany @ManyToOne

Example of bidirectional relation: a site has n sensors and sensor knows his site

relation1ton2

Only the @OneToMany annotation has a mappedBy property (which is used to designate the master)

// Slave
@Entity
public class Sensor {
    @Id
    private Long id;
    private String name;

    @ManyToOne
    private Site site;

     // ...
}

// Master
@Entity()
public class Site {
    @Id
    private Long id;
    private String name;

    @OneToMany(mappedBy = "site")
    private Set<Sensor> sensors;

    // ...
}

@ManyToMany

Exemple relation unidirectionnelle : a musician plays several instruments (instrument does not know who uses it)

relationnton

A join table is added (with 2 foreign keys)

@Entity
public class Instrument {
    @Id
    private Long id;
    private String name;

     // ...
}


@Entity()
public class Musician {
    @Id
    private Long id;
    private String name;

    @ManyToMany
    private Set<Instrument> instruments;

    // ...
}

Example bidirectional relation : a musician plays several instruments (instrument knows that they play musicians)

A join table is always present (with 2 foreign keys) but we must define who is master

@Entity
public class Instrument {
    @Id
    private Long id;
    private String name;

    @ManyToMany(mappedBy="instruments")
    private Set<Musician> musicians;
     // ...
}


@Entity()
public class Musician {
    @Id
    private Long id;
    private String name;

    @ManyToMany
    private Set<Instrument> instruments;

    // ...
}

Data Access Object (DAO)

A DAO (Data Access Object) lets you persist your Entities.

The DAO is basically an object or an interface that provides access to an underlying database or any other persistence storage.

That definition from Wikipedia

An example of Spring Data Jpa repository

public interface SensorDao extends JpaRepository<Sensor, Long> {
}

This extends a Spring Data interface, which provides common methods such as findOne, save and more. This repository will handle Sensors entities, and those are identified by an Id of type Long

If you want to create your own methods, you have to create a new interface

public interface SensorCustomDao {
    List<Sensor> findBySiteId(String siteId);
}

You need to update the main interface

public interface SensorDao extends JpaRepository<Sensor, String>, SensorCustomDao {
}

And now you can implement your SensorCustomDao and use entitiy manager to execute requests

@Repository
public class SensorCustomDaoImpl implements SensorCustomDao {

    @PersistenceContext
    private EntityManager em;

    @Override
    public List<Sensor> findBySiteId(String siteId) {
        return em.createQuery("select c from Sensor c inner join c.site s where s.id = :siteId",
                              Sensor.class)
                 .setParameter("siteId", siteId)
                 .getResultList();
    }
}

JPQL

With JPA we don’t write SQL but JPQL (Java Persistence Query Language). We don’t use the column names but we use the JPA entities

In SQL we select a list of columns belonging to one or more tables. In JPQL we select an entity.

// Selection sensors
select c from Sensor c

// Selection sites linked to a sensor
select c.site from Sensor c

You can use implicit join

select c from Sensor c where c.site.id = :siteId

Or you can use explicit join

select c from Sensor c join c.site s where s.id = :siteId
select c from Sensor c left join c.site s where s.id = :siteId

Spring Data JPA

With Spring Data Jpa, if you respect conventions you don’t need to create a DAO implementation

public interface SensorDao extends JpaRepository<Sensor, String>, SensorCustomDao {

    List<Sensor> findBySiteId(String siteId); // 1.

    @Query("select c from Sensor c where c.name=:name")  // 2.
    Sensor findByName(@Param("name") String name);

    @Modifying
    @Query("delete from Sensor c where c.name = ?1")
    void deleteByName(String name);
}
  1. findBy pattern allows to execute a query and return an occurrence or an occurrence list

  2. @Query helps to execute a JPQL query. Here we use a named parameter

  3. Another example with an update. In this case, you must use an @Modifying annotation

A request starts by findBy, findDistinctBy, countBy, …​

You can after add a property

List<Sensor> findByName(String name);

You can add a property of a property

List<Sensor> findBySiteId(String siteId);

You can cumulate criteria

List<Sensor> findByNameAndSiteId(String name, String siteId);
List<Sensor> findByNameOrSiteId(String name, String siteId);

You can ignore upper or lower case

List<Sensor> findByNameIgnoreCase(String name);
List<Sensor> findByNameAndSiteIdAllIgnoreCase(String name, String siteId);

You can sort data

List<Sensor> findByNameOrderByNameAsc(String name);
List<Sensor> findByNameOrderByNameDesc(String name);

Vous can select only one element (if you have for example an unicity constraint). But in this case if several elements are found an exception is thrown

Sensor findByName(String name);

You can read the documentation to know more things

Fetching Strategy

Allows you to specify the loading strategy.

  • LAZY the value is loaded only when it is used (default)

  • EAGER the value is always loaded

@OneToMany(fetch = FetchType.EAGER)
private Set<Sensor> sensors;

By default we are in Lazy because the goal is to load the minimum of things

If you navigate in your object and if relations are set in Lazy mode, you have 2 cases

  • You are attached to a persistence contex one or more queries are started to load the missing data

  • You are not attached to a persistence contex a LazyInitializationException is launched

org.hibernate.LazyInitializationException: could not initialize proxy

If you have to serialize your object or send it to another layer, you have to use DTO. We will see that later

Lab 4 : JPA

It’s time for you to start to build your real application which is able to manage sensors in a building like a light

tp

Create a Java enum called Status in package com.emse.spring.faircorp.model

This enum has 2 values : ON and OFF

Create a Light Entity class in package com.emse.spring.faircorp.model. You have to use the JPA annotations

// 1.
public class Light {

  // 2.
  private Long id;

  // 3.
  private Integer level;

  // 4.
  private Status status;

  public Light() {
  }

  public Light(Integer level, Status status) {
      this.level = level;
      this.status = status;
  }

  public Long getId() {
    return this.id;
  }

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

  public Integer getLevel() {
     return level;
  }

  public void setLevel(Integer level) {
     this.level = level;
  }

  public Status getStatus() {
     return status;
  }

  public void setStatus(Status status) {
     this.status = status;
  }
}

Use the good annotations to

  1. Mark this class as a JPA entity

  2. Declare this field as the table ID. This ID must to be auto generated

  3. This field must be not nullable

  4. status is also not nullable, and this field is an enumeration. You have to add this information

Write now the Room Entity. This entity should have

  • an auto generated id

  • a non nullable name

  • an non nullable integer to indicate the floor

  • a list of lights. You have to define a bidirectional relation between Room and Light : update the Ligth entity constructor to always send the light room when a room is created, ie add an argument Room in the light constructor

  • create a constructor with non nullable fields

Write now 2 DAO LightDao and RoomDao. We will use them in our code to load data

Update the file application.properties and add this keys

# Spring boot : JPA
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect // 1.
spring.jpa.ddl-auto=create-drop // 2.
spring.jpa.show_sql=true // 3.
  1. gives information to Hibernate for native SQL

  2. allows you to auto generate the schema (tables, constraints) from your Java data model (none if you do not want to do anything)

  3. displays queries in the logs (usefull in development)

Try to launch your application If everything is OK you should use the H2 console and see your tables Ligth and Room

console example

We’re going to populate our database and insert data in tables

Create a file data.sql in src/main/resources next to application.properties

INSERT INTO ROOM(ID, NAME, FLOOR) VALUES(-10, &apos;Room1&apos;, 1);
INSERT INTO ROOM(ID, NAME, FLOOR) VALUES(-9, &apos;Room2&apos;, 1);

INSERT INTO LIGHT(ID, LEVEL, STATUS, ROOM_ID) VALUES (-1, 8, &apos;ON&apos;, -10);
INSERT INTO LIGHT(ID, LEVEL, STATUS, ROOM_ID) VALUES (-2, 0, &apos;OFF&apos;, -10);

You’re going to write your own DAO methods (for specific requests), you have to create custom interfaces and implementations with your custom methods.

Create your own interface LightDaoCustom

public interface LightDaoCustom {
    List<Light> findOnLights();
}

Refactor your LightDAO interface : it has to extend LightDaoCustom

Create your own implementation of LightDao with your custom methods and inject the EntityManager (JPA)

public class LightDaoImpl implements LightDaoCustom {
    @PersistenceContext
    private EntityManager em;

    @Override
    public List<Light> findOnLights() {
        String jpql = "select lt from Light lt where lt.status = :value";
        return em.createQuery(jpql, Light.class)
                 .setParameter("value", Status.ON)
                 .getResultList();
    }
}

You have to test your DAO. When Spring context is loaded, the database is populated with the file data.sql and we can test these values. For that create a new test class in the package com.emse.spring.faircorp.repository

@ExtendWith(SpringExtension.class)
@DataJpaTest
class LightDaoCustomImplTest {

    @Autowired
    private LightDao lightDao;

    @Test
    public void shouldFindOnLights() {
        Assertions.assertThat(lightDao.findOnLights())
                .hasSize(1)
                .extracting("id", "status")
                .containsExactly(Tuple.tuple(-1L, Status.ON));
    }
}

You have to test and develop :

  • a custom DAO linked to room with a method to find a room by name

  • a new method to find the room lights when we send the id of the room

  • you have to develop these methods and their tests

  • you can now add a new Entity Building (a building has a set of rooms). This relation is bidirectional

  • Adds a new DAO BuildingDAO and add a new method to find all the building ligths. You send a building ID and your method should return the list of the lights

  • Adds a unit test to check that everything is OK