SpringBoot JPA, H2 & Liquibase

As a specification, the Jakarta Persistence API (formerly Java Persistence API) is concerned with persistence, which loosely means any mechanism by which Java objects outlive the application process that created them. The JPA specification lets you define which objects should be persisted, and how they are persisted in your Java applications. while JPA was originally intended for use with relational databases, some JPA implementations have been extended for use with NoSQL datastores.

Hibernate

Developed by Gavin King and first released in early 2002, Hibernate is an ORM library for Java. King developed Hibernate as an alternative to entity beans for persistence. The framework was so popular, and so needed at the time, that many of its ideas were adopted and codified in the first JPA specification.

Hibernate is an Object-Relational Mapping (ORM) framework that provides a high-level API for interacting with relational databases. It allows you to map Java objects to database tables and perform database operations using a high-level API, instead of writing low-level SQL code.

Spring Data JPA

Spring Data JPA, part of the larger Spring Data family, makes it easy to easily implement JPA-based repositories. This module deals with enhanced support for JPA-based data access layers. It makes it easier to build Spring-powered applications that use data access technologies.

Features

Sophisticated support to build repositories based on Spring and JPA
Support for Querydsl predicates and thus type-safe JPA queries
Transparent auditing of the domain class
Pagination support, dynamic query execution, ability to integrate custom data access code
Validation of @Query annotated queries at bootstrap time
Support for XML-based entity mapping
JavaConfig-based repository configuration by introducing @EnableJpaRepositories.

QuickStart

We will be creating a TradesMan Application, The application will have the following table structure

Step1: Create Trade Table

@Entity
@Table(name = "trade")
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class TradeEntity extends BaseDataEntityWithAudit {

  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "trade_sequence")
  @SequenceGenerator(
      name = "trade_sequence",
      sequenceName = "trade_sequence",
      allocationSize = 1,
      initialValue = 1)
  private Long id;

  @Column(nullable = false)
  @NotBlank(message = "type is required")
  private String type;

  @JsonBackReference
  @OneToMany(mappedBy = "tradeEntity", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
  private List<TradesmanEntity> tradesmanEntities = new ArrayList<>();

  @Column(nullable = false)
  @NotBlank(message = "Description is required")
  private String description;
}

Step2: Create Person Table

@Entity
@Table(name = "person")
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class PersonEntity extends BaseDataEntityWithAudit {

  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "person_sequence")
  @SequenceGenerator(
      name = "person_sequence",
      sequenceName = "person_sequence",
      allocationSize = 1,
      initialValue = 1)
  private Long id;

  @Column(nullable = false)
  @NotBlank(message = "Name is required")
  private String name;

  @Column(nullable = false)
  @NotBlank(message = "Country is required")
  private String country;

  @Column(nullable = false)
  @NotBlank(message = "state is required")
  private String state;

  @Column(nullable = false)
  @NotBlank(message = "city is required")
  private String city;

  @Column(nullable = false)
  @NotBlank(message = "city is required")
  private String area;

  @Column(nullable = false)
  @NotBlank(message = "contact is required")
  private String contact;

  @JsonBackReference
  @OneToMany(mappedBy = "personEntity", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
  private List<TradesmanEntity> tradesmanEntities = new ArrayList<>();

  @Column(nullable = false)
  @NotBlank(message = "Name is required")
  private boolean active;
}

Step3: Create TradesMan Table

@Entity
@Table(name = "tradesman")
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class TradesmanEntity extends BaseDataEntityWithAudit {

  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "tradesman_sequence")
  @SequenceGenerator(
      name = "tradesman_sequence",
      sequenceName = "tradesman_sequence",
      allocationSize = 1,
      initialValue = 1)
  private Long id;

  @JsonManagedReference
  @ManyToOne(targetEntity = TradeEntity.class)
  @JoinColumn(name = "trade_id", nullable = false)
  @NotNull(message = "Trade Id is required")
  private TradeEntity tradeEntity;

  @JsonManagedReference
  @ManyToOne(targetEntity = PersonEntity.class)
  @JoinColumn(name = "person_id", nullable = false)
  @NotNull(message = "Person Id is required")
  private PersonEntity personEntity;
}

Step4: Create Base Table

@MappedSuperclass
@Getter
@Setter
@EntityListeners(AuditingEntityListener.class)
public class BaseDataEntityWithAudit implements Serializable {

  @Column(name = "created_at", nullable = false, updatable = false)
  @CreatedDate
  private Instant createdAt;

  @Column(name = "created_by", nullable = false, updatable = false)
  @CreatedBy
  private String createdBy;

  @Column(name = "modified_at")
  @LastModifiedDate
  private Instant modifiedAt;

  @Column(name = "modified_by")
  @LastModifiedBy
  private String modifiedBy;
}

Liquibase & H2

Step1: Add Dependency

		<dependency>
			<groupId>org.liquibase</groupId>
			<artifactId>liquibase-core</artifactId>
		</dependency>
		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<scope>runtime</scope>
		</dependency>

Step2: Setup the DB configuration

H2 is a relational database management system written in Java. It can be embedded in Java applications or run in client-server mode. The software is available as open-source software from Mozilla Public. The main features of H2 are:

Very fast, open source, JDBC API
Embedded and server modes; in-memory databases
Browser-based Console application
Small footprint: around 2.5 MB jar file size

Spring Boot comes with auto-configuration for H2, so we don’t need additional configuration. Open the application.properties

logging.level.liquibase = INFO
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:test
spring.datasource.username=sa
spring.datasource.password=sa
spring.h2.console.enabled=true

Step 3: Configure Liquibase

Liquibase provides a way to version control the database schema and manages database changes. In this step, we’ll configure Liquibase for our Spring Boot application. By default, Spring Boot runs Liquibase database migrations automatically on application startup. The change log file is the primary source of configuration for Liquibase. If we don’t specify a change log file location in Spring Boot, the default path for YAML is db/changelog/db.changelog-master.yaml. Create the master change log file changelog-master.xml at default liquibase XML and included change log file by using include with correct sequence.

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd">

    <include file="classpath:db/changelog/changes/changelog-1.1.xml" relativeToChangelogFile="false" />
    <include file="classpath:db/changelog/changes/changelog-1.2.xml" relativeToChangelogFile="false" />
    <include file="classpath:db/changelog/changes/changelog-1.3.xml" relativeToChangelogFile="false" />
    <include file="classpath:db/changelog/changes/changelog-1.4.xml" relativeToChangelogFile="false" />
</databaseChangeLog>

Create Change Log Files

changelog-1.1.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
  http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.4.xsd">
    <changeSet id="1" author="jones">
        <createTable tableName="person">
            <column name="id" type="bigint" >
                <constraints primaryKey="true" nullable="false" />
            </column>
            <column name="created_at" type="TIMESTAMP WITHOUT TIME ZONE">
                <constraints nullable="false"/>
            </column>
            <column name="created_by" type="VARCHAR(255)">
                <constraints nullable="false"/>
            </column>
            <column name="modified_at" type="TIMESTAMP WITHOUT TIME ZONE">
                <constraints nullable="false"/>
            </column>
            <column name="modified_by" type="VARCHAR(255)">
                <constraints nullable="false"/>
            </column>
            <column name="name" type="varchar(255)">
                <constraints nullable="false" />
            </column>
            <column name="country" type="varchar(255)">
                <constraints nullable="false" />
            </column>
            <column name="state" type="varchar(255)">
                <constraints nullable="false" />
            </column>
            <column name="city" type="varchar(255)">
                <constraints nullable="false" />
            </column>
            <column name="area" type="varchar(255)">
                <constraints nullable="false" />
            </column>
            <column name="contact" type="varchar(255)" >
                <constraints nullable="false" />
            </column>
            <column name="active" type="boolean" >
                <constraints nullable="false" />
            </column>
        </createTable>
        <addUniqueConstraint
                columnNames="contact"
                constraintName="contact_constraint"
                tableName="person"
        />
        <createSequence
                sequenceName="person_sequence"
                dataType="BIGINT"
                startValue="1"
        />
    </changeSet>
</databaseChangeLog>

changelog-1.2.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
  http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.4.xsd">
    <changeSet id="1" author="jones">
        <createTable tableName="trade">
            <column name="id" type="bigint">
                <constraints primaryKey="true" nullable="false" />
            </column>
            <column name="created_at" type="TIMESTAMP WITHOUT TIME ZONE">
                <constraints nullable="false"/>
            </column>
            <column name="created_by" type="VARCHAR(255)">
                <constraints nullable="false"/>
            </column>
            <column name="modified_at" type="TIMESTAMP WITHOUT TIME ZONE">
                <constraints nullable="false"/>
            </column>
            <column name="modified_by" type="VARCHAR(255)">
                <constraints nullable="false"/>
            </column>
            <column name="type" type="varchar(255)">
                <constraints nullable="false" />
            </column>
            <column name="description" type="varchar(255)">
                <constraints nullable="false" />
            </column>
        </createTable>
        <addUniqueConstraint
                columnNames="type"
                constraintName="type_data_constraint"
                tableName="trade"
        />
        <createSequence
                sequenceName="trade_sequence"
                dataType="BIGINT"
                startValue="1"
        />
    </changeSet>
</databaseChangeLog>

changelog-1.3.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
  http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.4.xsd">
  <changeSet id="1" author="jones">
        <createTable tableName="tradesman">
            <column name="id" type="bigint">
                <constraints primaryKey="true" nullable="false" primaryKeyName="tradeperson_pkey"  />
            </column>
            <column name="created_at" type="TIMESTAMP WITHOUT TIME ZONE">
                <constraints nullable="false"/>
            </column>
            <column name="created_by" type="VARCHAR(255)">
                <constraints nullable="false"/>
            </column>
            <column name="modified_at" type="TIMESTAMP WITHOUT TIME ZONE">
                <constraints nullable="false"/>
            </column>
            <column name="modified_by" type="VARCHAR(255)">
                <constraints nullable="false"/>
            </column>
            <column name="trade_id" type="BIGINT">
                <constraints nullable="false" foreignKeyName="tradesman_to_trade_fk"
                             references="trade(id)"/>
            </column>
            <column name="person_id" type="BIGINT">
                <constraints nullable="true" foreignKeyName="tradesman_to_person_fk"
                             references="person(id)" />
            </column>
        </createTable>
        <createSequence
                sequenceName="tradesman_sequence"
                dataType="BIGINT"
                startValue="1"
        />
    </changeSet>
</databaseChangeLog>

changelog-1.4.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
  http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.4.xsd">
    <changeSet id="1" author="jones">
        <sqlFile encoding="utf8"
                 path="data/required_trades.sql"
                 relativeToChangelogFile="true"
        />
    </changeSet>
</databaseChangeLog>

required_trades.sql

INSERT INTO TRADE (ID, CREATED_AT, CREATED_BY, MODIFIED_AT, MODIFIED_BY, TYPE, DESCRIPTION) VALUES (1, current_timestamp, 'APP', current_timestamp, 'APP', 'PLUMBER', 'plumbing activities');
INSERT INTO TRADE (ID, CREATED_AT, CREATED_BY, MODIFIED_AT, MODIFIED_BY, TYPE, DESCRIPTION) VALUES (2, current_timestamp, 'APP', current_timestamp, 'APP', 'ELECTRICIAN', 'electrical activities');
INSERT INTO TRADE (ID, CREATED_AT, CREATED_BY, MODIFIED_AT, MODIFIED_BY, TYPE, DESCRIPTION) VALUES (3, current_timestamp, 'APP', current_timestamp, 'APP', 'CARPENTER', 'carpenter activities');
INSERT INTO TRADE (ID, CREATED_AT, CREATED_BY, MODIFIED_AT, MODIFIED_BY, TYPE, DESCRIPTION) VALUES (4, current_timestamp, 'APP', current_timestamp, 'APP', 'WELDOR', 'weldor activities');
INSERT INTO TRADE (ID, CREATED_AT, CREATED_BY, MODIFIED_AT, MODIFIED_BY, TYPE, DESCRIPTION) VALUES (5, current_timestamp, 'APP', current_timestamp, 'APP', 'TECHNICIAN', 'technician activities');
INSERT INTO TRADE (ID, CREATED_AT, CREATED_BY, MODIFIED_AT, MODIFIED_BY, TYPE, DESCRIPTION) VALUES (6, current_timestamp, 'APP', current_timestamp, 'APP', 'LANDSCAPER', 'landscaper activities');

Create Rest API

Step1: Create Rest API for API

@RestController
@AllArgsConstructor
public class TradesmanController {

  private final TradesmanService tradesmanService;

  /**
   * Gets list of Tradesman for the given trade.
   *
   * @param trade filtering tradesman based on trade type
   * @return the response entity
   */
  @GetMapping(value = "/tradesman/{trade}", produces = MediaType.APPLICATION_JSON_VALUE)
  public ResponseEntity<List<TradeResponse>> getTradesman(@Valid @PathVariable String trade) {
    return ResponseEntity.status(HttpStatus.OK).body(tradesmanService.getListOfTrades(trade));
  }
}

Step2: Create Repositories for different classes – Ex TradesmanRepository

@Repository
public interface TradesmanRepository extends JpaRepository<TradesmanEntity, Long> {
  @Query(
      "select new com.jonesjalapat.blog.tradesman.persistence.dto.TradeResponse(te.type, pe.name, pe.country, pe.state, pe.city, pe.contact, pe.active) "
          + " from TradeEntity te "
          + " left join TradesmanEntity tde on tde.tradeEntity.id = te.id "
          + " left join PersonEntity pe on tde.personEntity.id  = pe.id"
          + " where te.type = :trade")
  List<TradeResponse> findTradesmanByTrade(String trade);
}

Run the application

Run mvn spring-boot run command and run the spring boot application. Liquibase creates the DB tables first time, and for every restart, Libuibase does the migration of your DB tables

Open http://localhost:8080/h2-console/ URL in the browser. Login with database properties configured in the application.properties file. Write SELECT * FROM TRADE in textarea and click on Run. Your databases have been reflected in the output.

JPA