Skip to content

Latest commit

 

History

History
155 lines (123 loc) · 6.71 KB

2-dataaccess.asciidoc

File metadata and controls

155 lines (123 loc) · 6.71 KB
Raw
Table of Contents

Data Access Layer

In this chapter we are going to create the dataaccess layer of our app. We create the entities and repositories as well as the corresponding database schema and initial data.

Create Entities

After you have created your own devon4j app creation, we are going to create our first app component. In your core module create the package org.example.app.ordermanagement.dataaccess.api (in src/main/java). Inside this package create the ItemEntity and OrderEntity as illustrated by the following diagramm:

Entities

However, the OrderStatus Enum has to be created in the api module (because it is a datatype that will later be used in our API) in the package org.example.app.ordermanagement.common.api.

Create Repositories

For each entity create a corresponding repository in the package org.example.app.ordermanagement.dataaccess.api.repo.

Create DDL

Create flyway SQL files in core/src/main/resources/db/migration/1.0 to define your DDL as following:

Create file V0005__Create_Order.sql with this content:

CREATE TABLE RESTAURANT_ORDER (
  ID                  BIGINT NOT NULL AUTO_INCREMENT,
  MODIFICATIONCOUNTER INTEGER NOT NULL,
  PRICE               DECIMAL(5, 2) NOT NULL,
  CREATION_DATE       TIMESTAMP WITH TIME ZONE NOT NULL,
  PAYMENT_DATE        TIMESTAMP WITH TIME ZONE,
  STATUS              VARCHAR(10) NOT NULL,
  PRIMARY KEY (ID)
);

Create file V0006__Create_Item.sql with this content:

CREATE TABLE ITEM (
  ID                  BIGINT NOT NULL AUTO_INCREMENT,
  MODIFICATIONCOUNTER INTEGER NOT NULL,
  CREATION_DATE       TIMESTAMP WITH TIME ZONE NOT NULL,
  NAME                VARCHAR(255) NOT NULL,
  PRICE               DECIMAL(5, 2) NOT NULL,
  ORDER_ID            BIGINT NOT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (ORDER_ID) REFERENCES RESTAURANT_ORDER(ID)
);

Please note that you can delete the core/src/main/resources/db/type subfolders except for h2 if you like as we are just using H2 DB for simplicity. In a real project you would use H2 DB only for testing (in JUnits and have flyway SQLs for h2 in core/src/test/resources/db/test/) and use a real DB (SAP Hana, Oracle, etc.) for main usage.

You can check that your schema is valid running SpringBootApp. Surely you will need to add some JPA annotations to make your entities work with this DDL.

Create Test-data

We can easily provide test-data to our app so we do not have to test with an empty DB. This can easily be done with another Flyway SQL script. In a real project, we would create that in core/src/test/resources/db/ but for simplicity to have it also when we start our app regular (not via JUnit), we simply also add it to core/src/main/resources/db/migration/1.0. Create file V0007__Insert_Testdata.sql with this content:

INSERT INTO RESTAURANT_ORDER(ID, MODIFICATIONCOUNTER, CREATION_DATE, STATUS, PRICE) VALUES (1, 0, CURRENT_TIMESTAMP, 'OPEN', 25.50);
INSERT INTO ITEM(ID, MODIFICATIONCOUNTER, CREATION_DATE, NAME, PRICE, ORDER_ID) VALUES (1, 0, CURRENT_TIMESTAMP, 'Spaghetti Bolognese', 12.50, 1);
INSERT INTO ITEM(ID, MODIFICATIONCOUNTER, CREATION_DATE, NAME, PRICE, ORDER_ID) VALUES (2, 0, CURRENT_TIMESTAMP, 'Red Wine: Capezzana, Conti Contini Sangiovese Toscana', 13.00, 1);

During the exercise you can extend this testdata SQL as you like. Run application and check that the data you provided is inserted into the database.

Create JUnits

Now you can create JUnit tests. First, we fix our app/core/pom.xml by replacing devon4j-test dependency with this:

    <dependency>
      <groupId>com.devonfw.java.modules</groupId>
      <artifactId>devon4j-test-jpa</artifactId>
      <scope>test</scope>
    </dependency>

Now, we can create simple JUnits for our repositories like this:

@SpringBootTest(classes = SpringBootApp.class, webEnvironment = WebEnvironment.MOCK)
public class OrderRepositoryTest extends ComponentDbTest {

  @Inject
  private OrderRepository orderRepository;

  @Test
  public void testFindOrder() {

    // given
    Long orderId = Long.valueOf(1L);
    // when
    OrderEntity order = this.orderRepository.find(orderId);
    // then
    assertThat(order).isNotNull();
    assertThat(order.getPrice()).isEqualTo(new BigDecimal("25.50"));
    // ...
  }

}

Setup H2-console

The H2-concole can be helpful for debugging issues with the data. It can be started with the server and accessed by a webbrowser. For this some changes in the code are necessary.

  • Add the following dependencies to the pom.xml in the core package and then run mvn clean install:

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

  • From config/application.properties in the core package copy the value of spring.datasource.url you find here, we will need it later. Also add in this file:

spring.h2.console.enabled=true

  • In the class BaseWebSecurityConfig add at the end of the method configure:

        // Fix for H2-console Frame Sets
        http.headers().frameOptions().sameOrigin();

  • Now start your server and go to http://localhost:8081/h2-console. If this gives you an 403 or 404, you might have set a value for server.servlet.context-path in your application.properties. Than you have to put it in the URL as path prefix.

  • You should now see a login screen for the H2-console. If you are asked to login in our app, just do it as usual, but afterwards you should be routed to the h2-console. In the login screen of the H2-console set the value of JDBC URL to the value of spring.datasource.url from before. Leave the user name as sa and the password empty.

Extra assignment

Extend the OrderRepository with a search for orders by price (lower/greater) and state (equal). Theses values should be variable parameters, so a dynamic query is suggested. Add test data and test for the repositories.