Dev-Mind

Spring in practice : database and JPA

11/10/2022
Java  Spring 

How use a database in your project. We will use Spring Data to do that. Spring Data is an abstraction to help developers. In our examples we will Hibernate as JPA implementation and a H2 Database.

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

  4. choose a database

JDBC

  • common API used by all relational databases

  • each database editor provides its driver (a jar added to your project)

  • you can use a DBMS (DataBase Management System) to define, store, retrieve, and manage the data within a database.

  • a DBMS can be just a cli available on a terminal or a GUI

H2

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

Database and SQL

Go in your FaircorpApplication. We need to add new Spring Boot starters and the H2 driver in the declared dependencies.

implementation 'org.springframework.boot:spring-boot-starter-data-jpa' // libs to use JPA in your project
implementation 'com.h2database:h2' // libs to use a H2 database

Check your build.gradle file and update it. Reload your Gradle project to apply changes

refresh gradle

Spring Boot analyses jars defined in classpath and Spring is able to auto-configure features as the database, the H2 console…​

Add some properties in file src/main/resources/application.properties to configure a in memory H2 database (database will be recreated after each app reload)

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

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

# Spring boot : JPA
spring.jpa.database=h2

You can use the H2 console. To verify that everything is fine, launch your app and open this URL in your browser: http://localhost:8080/console

h2 console0

Use JDBC URL, user, password defined in your `application.properties and click on Connect button. You should access to the console

h2 console

You can create several SQL orders

  • SQL order to create a table (Id is generated by the database and with use 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

To understand the value of Spring and JPA, it is important to see the code that would have to be done if we wanted to directly use the JDBC API which is a low level API requiring a lot of code.

Open a connection

This code open a database connection in Java with JDBC API

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

  • (1) (2) (3) for each step we have to manage exceptions

This operation is slow. If you have thousands connections on your app per second your application will fail.

pool0

We need to use a connection pool with pre-opened connections. Several connections are opened when the pool is launched.

pool1

Execute a request

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 requests run in parallel?

  • What happens if a request is too long?

The solution is to work in a transaction. A database transaction symbolizes a unit of work performed within a database. A transaction generally represents any change in a database. Transactions have two main purposes:

  • to provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system failure, when execution stops (completely or partially) and many operations upon a database remain uncompleted, with unclear status.

  • To provide isolation between concurrent accesses. If this isolation is not provided, data could be erroneous.

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");
                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. autocommit is sometimes the default value. We have to disabled it

  2. If everything is OK a commit persist data

  3. If we have an error everything is cancelled by a rollback.

transaction2

JPA

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.

With Persistence API/Framework, the approach is to :

  • work with Java objects (Java entities) and not with database tables

  • 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

 

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."

JPA Entity

Object relation mapping (ORM) is one of the main feature in the JPA specification. The ORM layer (Hibernate) performs the translation of the app model objects into a relational database. For that we just have to use different JPA annotations on our entity objects.

Let’s take the example of a Java class named Sensor and see how to use JPA to bind it to the SP_SENSOR table of our database.

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) @Entity indicates that this class is an entity managed by Hibernate

  • (2) @Table(name = "SP_SENSOR") you can customize the table name (optional if table name = entity name)

  • (3) @Id 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) @Column 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) @Enumerated(EnumType.STRING) Java enum persisted as a String (choose always EnumType.STRING)

  • (7) an entity 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 (and you should) 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

When your app need to launch a query, it will call an EntityManager to execute it

em

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. When you use Spring, 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 annotation to work in a transaction.

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()

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

Schema generation

Hibernate (JPA implementation) is able to parse yours entities to generate your database schema.It’s very useful when you develop an app.

In Spring you just have to add some properties in application.properties file.

Update the file application.properties and add these keys

# Spring boot : JPA
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.generate-ddl=true
spring.jpa.show_sql=true
spring.jpa.defer-datasource-initialization=true
  • H2Dialect gives information to Hibernate for native SQL

  • generate-ddl allows you to auto generate the schema (tables, constraints) from your Java data model (false if you do not want to do anything)

  • show_sql displays queries in the logs (usefull in development)

JPA and 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.

relationship

An association between JPA entities, can be unidirectional or bidirectional. In this second case, one of the two entities must be the parent (the main entity) and the other the child.

It defines in which direction you can use the association.

@OneToOne

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

relationship 1to1 uni

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

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

    //...
}


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

     // ...
}

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

relationship 1to1 bi

We can' t add a column mayor_id in the City table and a column city_id in the Mayor table because of the cycle.

In Hibernate entity model, we have to use a mappedBy to define the field that owns the relationship. This element is only specified on the inverse (non-owning) side of the association.

For example the mappedBy can be defined on the OneToOne defined in the mayor entity

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

    @OneToOne
    private Mayor mayor;

    // ...
}

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

    @OneToOne(mappedBy = "mayor")
    private City city;

    //...
}

With this code a column mayor_id will be added in the City table and a foreign key will be created. Mayor table won’t have a reference to the city table.

mappedby tells hibernate not to map this field because it’s already mapped by this field [here property mayor in City entity].

If you forget to define a parent and a child in the association (no association with a mappedBy), you will have a mayor_id reference in the City table and a city_id reference in the Mayor table with 2 constraints. In this case, you will not be able to delete a row because you will always have a constraint error.

@OneToMany

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

relationship 1ton uni

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 association: a measurement is linked to a sensor and the sensor does not have the measurement list

relationship nto1 uni

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

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

    // ...
}

@Entity
public class Measurement {

    @Id
    private Long id;

    @ManyToOne(optional = false)
    private Sensor sensor;

    // ...
}

@OneToMany @ManyToOne

Example of bidirectional association: a sensor has n measures and measure knows its sensor

relationship 1ton nto1 bi

@ManyToOne ha nos property mappedBy. So you can’t make a mistake, add this mappedBy property on @OneToMany (which is used to designate the parent)

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

    @ManyToOne
    private Sensor sensor;
}

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

    @OneToMany(mappedBy = "sensor")
    private Set<Measure> measures;
}

If you forget the mapped mappedBy property on the @OneToMany* you will have an unexpected join table betwwen the sensor and the measure.

@ManyToMany

Example of unidirectional association: a musician plays several instruments (instrument does not know who uses it)

relationship nton uni

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 association: a musician plays several instruments (instrument knows that they play musicians)

relationship nton bi

A join table is always present (with 2 foreign keys) but we must define the mappedBy property on one entity

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

    @ManyToMany
    private Set<Musician> musicians;
     // ...
}

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

    @ManyToMany(mappedBy="musicians")
    private Set<Instrument> instruments;

    // ...
}

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 mode 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 context : one or more queries are started to load the missing data

  • You are not attached to a persistence context: 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

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.

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

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 (DAO)

public interface SensorDao extends JpaRepository<Sensor, Long> {
    @Query("select c from Sensor c where c.name=:name")
    Sensor findByName(@Param("name") String name);
}

JpaRepository is a Spring Data interface, which provides common methods such as findOne, save, delete and more. This example will handle Sensors entities, and those are identified by an Id of type Long (generics type used in JpaRepository).

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> {

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

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

    @Modifying // (3)
    @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 called name.

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

If a method 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);
  • You 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);

For more informations read the documentation to know more things

Custom DAO

If you need to create your own queries, you have to create a new interface

public interface SensorCustomDao {
    List<Sensor> findBySiteText(String searchText);
}

You need to update the main interface. In our code we will always inject a SensorDao. This Dao will have all JpaRepository methods and all your custom queries.

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

Implement your SensorCustomDao and use entitiy manager to execute requests

@Repository
public class SensorCustomDaoImpl implements SensorCustomDao {

    @PersistenceContext
    private EntityManager em;

    @Override
    public List<Sensor> findBySiteText(String searchText) {
        return em.createQuery("select c from Sensor c inner join c.site s where lover(s.name) like :searchText",
                              Sensor.class)
                 .setParameter("searchText", "%" + searchText.toLowerCase() + "%")
                 .getResultList();
    }
}

: JPA

It’s time for you to start to build your real application which is able to manage sensors in a building.The management of the sanitary conditions (COVID-19 pandemic, pollution), user comfort, and energy efficiency, require concurrent management of window openings in the École des Mines buildings.

It is necessary to ventilate as much as possible to limit the spread of the cirus and air pollution in general, but as winter approaches it will become important to heat the buildings to ensure user comfort.Windows should be open during and after classes, closed at night or in case of heavy rain or severe cold.Thus the management of the health crisis becomes concurrent with the quest for energy efficiency in the building.

tp

We will now create an application which will able to manage the building windows.
- the building has an outside temperature, and rooms
- each room has zero or more heaters, has zero or more windows, a name, a floor, a current temperature, a target temperature.
- each heater has a name, an on or off status, possibly a power.
- each window has a name, an a status open or closed

Entity creation

Create a Java enum called HeaterStatus in package com.emse.spring.faircorp.model. This enum has 2 values : ON and OFF

public enum HeaterStatus { ON, OFF}

Create another Java enum called WindowStatus in package com.emse.spring.faircorp.model. This enum has 2 values : OPEN and CLOSED

Create an Entity called Window in package com.emse.spring.faircorp.model (an entity is a class). You have to use the JPA annotations seen previously.

You can copy this code. For the moment this entity has no property room because this entity will be created later.

// (1)
// (2)
public class Window {
    // (3)
    private Long id;

    // (4)
    private String name;

    // (5)
    private WindowStatus windowStatus;

    public Window() {
    }

    public Window(String name, WindowStatus status) {
        this.windowStatus = status;
        this.name = name;
    }

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

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

    public String getName() {
        return name;
    }

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

    public WindowStatus getWindowStatus() {
        return windowStatus;
    }

    public void setWindowStatus(WindowStatus windowStatus) {
        this.windowStatus = windowStatus;
    }
}

Use the good annotations to

  • (1) Mark this class as a JPA entity

  • (2) You must give a different name for your table. H2 can’t call a table Window because it is a reserved word. So call it RWINDOW

  • (3) Declare this field as the table ID. This ID must to be auto generated

  • (4) This field must be not nullable

  • (5) WindowStatus is also not nullable, and this field is an enumeration (you have to use @Enumerated). You have to add these informations

Write now

  • the Heater entity with

    • an auto generated id

    • a non nullable String name

    • a nullable Long power

    • a non nullable room

    • a non nullable status (ON, OFF). This field is an enumeration (you have to use @Enumerated).

    • create a constructor with non nullable fields and a default constructor

  • the Room entity

    • an auto generated id

    • a non nullable floor (Integer)

    • a non nullable String name

    • a current temperature (Double)

    • a target temperature (Double)

    • a list of heaters. You have to define a bidirectional association between Room and Heater

    • a list of windows. You have to define a bidirectional association between Room and Window : update the Window entity constructor to always send the room when a room is created, ie add an argument Room in the Window constructor

    • create a constructor with non nullable fields and a default constructor

You can start your application. If you updated your configuration (see this chapter) you should see in your app logs

Hibernate: drop table if exists heater CASCADE
Hibernate: drop table if exists room CASCADE
Hibernate: drop table if exists room_windows CASCADE
Hibernate: drop table if exists rwindow CASCADE
Hibernate: drop sequence if exists hibernate_sequence
Hibernate: create sequence hibernate_sequence start with 1 increment by 1
Hibernate: create table heater (id bigint not null, heater_status varchar(255) not null, name varchar(255) not null, power bigint, room_id bigint not null, primary key (id))
Hibernate: create table room (id bigint not null, current_temperature double, floor integer not null, name varchar(255) not null, target_temperature double, primary key (id))
Hibernate: create table room_windows (room_id bigint not null, windows_id bigint not null, primary key (room_id, windows_id))
Hibernate: create table rwindow (id bigint not null, name varchar(255) not null, window_status varchar(255) not null, room_id bigint not null, primary key (id))
Hibernate: alter table room_windows add constraint UK_ojyqj1kyod1v2m4eoltwj7sfa unique (windows_id)
Hibernate: alter table heater add constraint FKba10hbsubifukw0monybjx3a0 foreign key (room_id) references room
Hibernate: alter table room_windows add constraint FKbpyirwvjc67pkk61hnh5qpn6y foreign key (windows_id) references rwindow
Hibernate: alter table room_windows add constraint FKaihtbjcqqmheg9s19aala347k foreign key (room_id) references room
Hibernate: alter table rwindow add constraint FK621arbu4dtuis4nf2o3xtapfn foreign key (room_id) references room

Open your H2 console you should see

Expected result in H2 console

If you more tables you forgot to declare a bidirectional association…​

Populate data

We’re going to populate our database and insert data in tables. You can execute the script below in your H2 console, but data will be deleted on the next app reload. Fortunately Spring Boot offers a mechanism to populate a database at startup.

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

INSERT INTO ROOM(id, name, floor, current_temperature, target_temperature) VALUES(-10, 'Room1', 1, 22.3, 20.0);
INSERT INTO ROOM(id, name, floor) VALUES(-9, 'Room2', 1);

INSERT INTO HEATER(id, heater_status, name, power, room_id) VALUES(-10, 'ON', 'Heater1', 2000, -10);
INSERT INTO HEATER(id, heater_status, name, power, room_id) VALUES(-9, 'ON', 'Heater2', null, -10);

INSERT INTO RWINDOW(id, window_status, name, room_id) VALUES(-10, 'CLOSED', 'Window 1', -10);
INSERT INTO RWINDOW(id, window_status, name, room_id) VALUES(-9, 'CLOSED', 'Window 2', -10);
INSERT INTO RWINDOW(id, window_status, name, room_id) VALUES(-8, 'OPEN', 'Window 1', -9);
INSERT INTO RWINDOW(id, window_status, name, room_id) VALUES(-7, 'CLOSED', 'Window 2', -9);

Dao creation

Simple DAO

Write now 3 Spring data DAO WindowDao, HeaterDao and RoomDao in package com.emse.spring.faircorp.dao (interface that extends JpaRepository with the good types for entity and its id)

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

To check WindowDao, create a class WindowDaoTest in src/test/java/com.emse.spring.faircorp.dao

import com.emse.spring.faircorp.model.Window;
import com.emse.spring.faircorp.model.WindowStatus;
import org.assertj.core.api.Assertions;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.test.context.junit.jupiter.SpringExtension;

@ExtendWith(SpringExtension.class)
@DataJpaTest
class WindowDaoTest {
    @Autowired
    private WindowDao windowDao;

    @Test
    public void shouldFindAWindow() {
        Window window = windowDao.getReferenceById(-10L);
        Assertions.assertThat(window.getName()).isEqualTo("Window 1");
        Assertions.assertThat(window.getWindowStatus()).isEqualTo(WindowStatus.CLOSED);
    }
}

Execute your test. This test should be green. You can write similar tests to test RoomDao and HeaterDao

Custom DAO

Create your own interface WindowDaoCustom in package com.emse.spring.faircorp.dao

public interface WindowDaoCustom {
    List<Window> findRoomOpenWindows(Long id);
}

Refactor your WindowDao interface : it must extend JpaRepository and WindowDaoCustom

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

public class WindowDaoCustomImpl implements WindowDaoCustom {

    @PersistenceContext
    private EntityManager em;

    @Override
    public List<Window> findRoomOpenWindows(Long id) {
        String jpql = "select w from Window w where w.room.id = :id and w.windowStatus= :status";
        return em.createQuery(jpql, Window.class)
                .setParameter("id", id)
                .setParameter("status", WindowStatus.OPEN)
                .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 update WindowDaoTest test and add these methods

@Test
public void shouldFindRoomOpenWindows() {
    List<Window> result = windowDao.findRoomOpenWindows(-9L);
    Assertions.assertThat(result)
            .hasSize(1)
            .extracting("id", "windowStatus")
            .containsExactly(Tuple.tuple(-8L, WindowStatus.OPEN));
}

@Test
public void shouldNotFindRoomOpenWindows() {
    List<Window> result = windowDao.findRoomOpenWindows(-10L);
    Assertions.assertThat(result).isEmpty();
}

You have to test and develop :

  • a custom DAO with a method to find all windows by room name

  • add a method in WindowDao to delete all windows in a room.

  • add a method in HeaterDao to delete all heaters in a room.

  • you have to develop these methods and their tests

To check that window room are deleted you can add this test method in WindowDaoTest

@Test
public void shouldDeleteWindowsRoom() {
    Room room = roomDao.getById(-10L);
    List<Long> roomIds = room.getWindows().stream().map(Window::getId).collect(Collectors.toList());
    Assertions.assertThat(roomIds.size()).isEqualTo(2);

    windowDao.deleteByRoom(-10L);
    List<Window> result = windowDao.findAllById(roomIds);
    Assertions.assertThat(result).isEmpty();

}

When you will finish other lessons you can go back on this chapter and add a new enty to manage a building.

  • a building has a set of rooms. This relationship is bidirectional

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

  • Adds a unit test to check that everything is OK