Table of Contents

Order Service - Data Access Layer
- Create your component

Order Service - Data Access Layer

In this chapter we are going to create database schema with initial data. Then we will map it into entities model and provide operations for management of the entities.

Create your component

After you have completed your own devon4j app creation, we are going to create our first app component.

Going back to our example application, Order Service, we need to provide basic functionalities:

increase price of the item with specified name.
remove customer by id.
create order with two positions and owner specified.
find orders from given day with specified status.
find items by name match (LIKE) ordered by name.

To accomplish that we are going to work over three entities: ItemEntity, OrderEntity, CustomerEntity.

Each of the entities will have autogenerated id and modificationCounter which specifies version number of the entity.

The Item will be defined with: name, description and price.

The Customer will be defined with: firstname and lastname. It will have also collection of it’s orders.

The Order will be defined with: order positions, status, creation date, owner and price which is summary of all positions' prices.

In addition, we will have to represent two relationships:

The many to many relation between orders and items.

The one to many relation between order and customer.

The database

In the projects created with the devon4j archetype, we already have a complete data base schema that we can use as a model to create our own. By default we are going to work over the H2 database engine provided in the devon4j applications.

We have the /order-service-core/src/main/resources/db/type/h2/V0001Create_Sequence.sql, V0002Create_RevInfo.sql and V0003Create_BinaryObject.sql these tables are already used by default so don’t delete them. We therefore also have /order-service-core/src/main/resources/db/migration/1.0/V0004Add_blob_data.sql. You can create your own schema table that your required. For e.g. db/type/h2/V0005__Create_OrderServiceSchema.sql.

You can check that your schema is valid running SpringBootApp.java which recreates schema after each run. Created schema can be found in the H2 console.

Item table

Now we can add our first table Item in db/type/h2/V0005__Create_OrderServiceSchema.sql. In the case of OrderService, the items will provide: id, modificationCounter, name, description and price. So we need to represent that data in our table:

CREATE TABLE Item (
  id                  BIGINT NOT NULL AUTO_INCREMENT,
  modificationCounter INTEGER NOT NULL,
  name                VARCHAR(255) NOT NULL,
  description         VARCHAR(255),
  price               DOUBLE NOT NULL,
  PRIMARY KEY (ID),
  CONSTRAINT UC_Item_name UNIQUE(name)
);

id: the id for each item, automatically incremented using sequence HIBERNATE_SEQUENCE.
modificationCounter: used internally by JPA to take care of the optimistic locking for us.
name: the item’s name.
description: the item’s description.
price: the item’s price.

We set also constraints: - primary key for id to take care of it’s uniqueness. - unique key for name of the item.

Customer table

Basing on Item table provide also Customer table with attributes as follows:

id: the id for each customer, automatically incremented using sequence HIBERNATE_SEQUENCE.
modificationCounter: used internally by JPA to take care of the optimistic locking for us.
firstname: the customer’s name.
lastname: the customer’s description.

We set also constraints:

primary key for id to take care of it’s uniqueness.

Please create this table definition basing on types and specific column names that are shown in the diagram.

OrderSummary table

Basing on Item table provide also OrderSummary table with attributes as follows:

id: the id for each order, automatically incremented using sequence HIBERNATE_SEQUENCE.
modificationCounter: used internally by JPA to take care of the optimistic locking for us.
price: price of whole order.
ownerId: the customer’s id which is owner of this order.
creationDate: the order’s creation date.
status: status of the order.

We set also constraints:

primary key for id to take care of it’s uniqueness.
foreign key to the Customer table for column ownerId

CREATE TABLE OrderSummary (
  id                  BIGINT NOT NULL AUTO_INCREMENT,
  modificationCounter INTEGER NOT NULL,
  price DOUBLE,
  ownerId BIGINT NOT NULL,
  creationDate DATE NOT NULL,
  status VARCHAR(255) NOT NULL,
  PRIMARY KEY (ID),
  CONSTRAINT FK_OrderOwner_owner FOREIGN KEY(ownerId) REFERENCES Customer(id)
);

OrderPosition table

OrderPosition is a relational table used for keeping relation between the order and it’s positions. It does not map into entity class.

orderId: id of the OrderSummary entry.
itemId: if of the Item entry.

We set also constraints:

foreign key for orderId to show the connection to OrderSummary table.
foreign key for itemId to show the connection to Item table.

CREATE TABLE OrderPosition (
  orderId   BIGINT NOT NULL,
  itemId	BIGINT NOT NULL,
  CONSTRAINT FK_OrderPosition_order FOREIGN KEY(orderId) REFERENCES OrderSummary(id),
  CONSTRAINT FK_OrderPosition_item FOREIGN KEY(itemId) REFERENCES Item(id)
);

Mock data

Finally we can provide a certain amount of mock data to start our app. Add a new sql script /order-service-core/src/main/resources/db/migration/1.0/V0006__Add_OrderServiceData.sql adding sample data:

INSERT INTO Item(id, ModificationCounter, name, description, price) VALUES (21, 0, 'spaghetti bolognese', 'Italy', 250);

INSERT INTO Customer(id, ModificationCounter, firstname, lastname) VALUES (31, 0, 'John', 'Travolta');

INSERT INTO OrderSummary(id, ModificationCounter, price, ownerId, creationDate, status) VALUES (41, 0, 671.10, 31, '2019-03-15', 'SERVED');

INSERT INTO OrderPosition(orderId, itemId) VALUES (41, 21);

You can provide your own data or use script which can be found here.

Run application and check that the data you provided is inserted into the database.

Entities

Now that we have defined the data base for our entities is the moment to start creating the code of the related components.

We are going to create entities with it’s interfaces in new package orderservice which will contain all objects specific for our application.

In order-service-core component create package orderservice.dataaccess.api under com.devonfw.app.java.order where we will place our entities.

ItemEntity

Create new class ItemEntity.

Create there private attributes basing on the schema. For now, skip id and modificationCounter. For each of them generate getters and setters (right click in ItemEntity → Source → Generate Getters and Setters).

Mark table with @Entity annotation and set also table name which should be mapped to this object.

@Entity(name="Item")
public class ItemEntity {

Generate interface and class hierarchy for ItemEntity using CobiGen.

Choose Entity Infrastructure and click finish.

@Entity(name="Item")
public class ItemEntity extends ApplicationPersistenceEntity implements Item {

ItemEntity extends now ApplicationPersistenceEntity which has attributes id and modificationCounter needed for all our entities. Each of the entities should inherit from this class.

Check implementation of ApplicationPersistenceEntity,

@Override
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
public Long getId() {

  return this.id;
}

@Override
@Version
public int getModificationCounter() {

  return this.modificationCounter;
}

@Id shows that this attribute will be the identifier of the entity. It is used by JPA to identify entity.
@GeneratedValue shows that values for id will be automatically generated
@Version defines modificationCounter attribute as version counter.

In component order-service-api com.devonfw.app.java.order.orderservice.common.api.Item, there is Item interface generated which contains method which have to be implemented by the Entity and later by transfer objects.

You can run the application and it should start correctly.

CustomerEntity

Next to the ItemEntity create CustomerEntity which should inherit from ApplicationPersistenceEntity. Prepare attributes as shown in the schema (skip orders attribute until we create OrderEntity). Generate getters and setters. Generate Customer interface for the entity same way we did it for ItemEntity.

OrderEntity

In component order-service-api com.devonfw.app.java.order.orderservice.common.api create OrderStatus enum which will be used as status in OrderEntity.

public enum OrderStatus {
	NEW, PREPARING, PREPARED, SERVED, PAID, CANCELLED
}

Next to other entities create OrderEntity. It should also inherit from ApplicationPersistenceEntity. Prepare attributes as shown in the schema. Generate getters and setters. Generate Order interface for the entity.

Above getters add needed annotations:

As default, enum values are saved as Integer in the database. To have it’s value as String, change is needed:
```
@Enumerated(EnumType.STRING)
public OrderStatus getStatus() {
  return status;
}
```

Add mapping for relation to CustomerEntity:

@ManyToOne
@JoinColumn(name = "ownerId")
public CustomerEntity getOwner() {
  return owner;
}

Check method getOwnerId, it is marked with @Transient annotation to not map owner twice (since we have relation specified also by getOwner() method):

@Transient
	public Long getOwnerId() {
		if (this.getOwner() != null)
			return this.getOwner().getId();
		return null;
	}

	public void setOwnerId(Long ownerId) {
		CustomerEntity e = new CustomerEntity();
		e.setId(ownerId);
		this.setOwner(e);
	}

Add mapping for relation to ItemEntity:

@ManyToMany
@JoinTable(name = "OrderPosition", joinColumns = @JoinColumn(name = "orderId", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "itemId", referencedColumnName = "id"))
public Set<ItemEntity> getOrderPositions() {
  return orderPositions;
}

Adjust CustomerEntity and add there relation to OrderEntity with annotation showing that this relation is mapped only in OrderSummary table.

@OneToMany(mappedBy = "owner")
public Set<OrderEntity> getOrders() {
  return orders;
}

Run application and check that schema from database maps correctly into entities model.

Repositories

To perform operations on our entities we need to create repositories for each of them. They will contain operations specific for each of the objects.

ItemRepository

In component order-service-core, create package com.devonfw.app.java.order.orderservice.dataaccess.api.repo. We will place there all our repositories.

Create interface ItemRepository. It should extend DefaultRepository from JPA which contains generic operations for entities.

public interface ItemRepository extends DefaultRepository<ItemEntity> {

ItemRepositoryTest

With such repository we can already perform basic CRUD operations, f.e. save, deleteById, findById.

We will test the behaviour of Repository methods using unit tests.

In source folder src/test/java of component order-service-core create same package as for interface.

Create there class ItemRepositoryTest

import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;

@SpringBootTest( webEnvironment = WebEnvironment.DEFINED_PORT)
public class ItemRepositoryTest extends ComponentTest {

DEFINED_PORT is used to connect to H2 console always on the specified port. We need to adjust application.properties of src/test/resources/config.

Enable there h2 console
Adjust spring.datasource.url to be same as for our application
Define server.port as different than for application.
Look at spring.flyway.locations. It calls migration of data for each of test runs. We leave it for now to start with the filled database.

server.port=8085
spring.h2.console.enabled=true

Functionality of ComponentTest will be described later.

FindAll test

Let’s write our first test - we would like to find all items. We will use for it JpaRepository.findAll() method which is available in ItemRepository thanks to inheritance of DefaultRepository.

inject ItemRepository to test class.
prepare public method annotated with @Test
call there ItemRepository.findAll() method
check that number of found elements is same as number of elements created in your migration.

import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;

@SpringBootTest( webEnvironment = WebEnvironment.DEFINED_PORT)
public class ItemRepositoryTest extends ComponentTest {

  @Inject
  private ItemRepository itemRepository;

  @Test
  public void shouldFindAllItems() {
    // when
    List<ItemEntity> foundItems = itemRepository.findAll();

    // then
    assertThat(foundItems).hasSize(1);
  }

Run test

To check that items from migrations are really in the tested database, create a breakpoint in the test. It will pause the

Congratulations, you wrote your first unit test!

JPAQuery

QueryDSL allows us to write query which can be suitable for many use cases.

Create search criteria object which will contain all arguments used in this query. Place it on order-service-api in package com.devonfw.app.java.order.orderservice.logic.api.to ItemSearchCriteriaTo has to contain all business attributes of ItemEntity. Additionally we add there StringSearchConfigTo for each of the string attributes to be able to create also LIKE expressions.

import org.springframework.data.domain.Pageable;

public class ItemSearchCriteriaTo extends AbstractTo {

  private String name;
  private String description;
  private Double price;
  private StringSearchConfigTo nameOption;
  private StringSearchConfigTo descriptionOption;
  private Pageable pageable;

Implement getters and setters for all attributes.

Go back to the ItemRepository. Implement there findByCriteria method using JPAQuery.

import static com.querydsl.core.alias.Alias.$

default Page<ItemEntity> findByCriteria(ItemSearchCriteriaTo criteria) {

  ItemEntity alias = newDslAlias();
  JPAQuery<ItemEntity> query = newDslQuery(alias);

  String name = criteria.getName();
  if (name != null && !name.isEmpty()) {
    QueryUtil.get().whereString(query, $(alias.getName()), name, criteria.getNameOption());
  }

  // TODO: implement also expression for description and price

  // TODO: implement also sorting using addOrderBy

  // TODO: return found items using QueryUtil
}

Test the solution you created.

Hints

Sort sort = Sort.by("name");
Pageable pageable = PageRequest.of(0, 20, sort);

Repositories with CobiGen

For OrderEntity and CustomerEntity we will generate SeachCriteria and Repositories using CobiGen. Right click on each of the entities, generate with CobiGen. Mark there TO’s ans SpringRepository. Click Customize (right down corner of objects generated) and untick TO object (CustomerTo).

Compare generated repositories to your own.

Business functionality

Implement given queries together with unit tests.

Find item entities where name is like given argument (case insensitive). Sort result by name ascending - use search criteria query.
Find orders from given day with specific status - create SpringData query.
Remove Customer by id.
Create Order with two order positions and owner set.
Update item with given name changing it’s price.

Remark

During testing ItemEntity we used our migration to create testdata. Instead of it, we should create testdata for each unit test separately to have them independent. To do so, after each of the tests, we will clear database schema using doTearDown() method.

@Override
protected void doTearDown() {
  super.doTearDown();
  // TODO: call here delete for all entities related to this test class
  this.itemRepository.deleteAll();
}

We won’t load data from our migration file. Please adjust application.properties file from test directory and remove there migration location from flyway locations.

spring.flyway.locations=classpath:db/type/h2

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Order Service - Data Access Layer

Create your component

The database

Item table

Customer table

OrderSummary table

OrderPosition table

Mock data

Entities

ItemEntity

CustomerEntity

OrderEntity

Repositories

ItemRepository

ItemRepositoryTest

FindAll test

JPAQuery

Hints

Repositories with CobiGen

Business functionality

Remark

Next chapter: Order Service - Business Logic Layer

FilesExpand file tree

order-service-dataaccess-layer.asciidoc

Latest commit

History

order-service-dataaccess-layer.asciidoc

File metadata and controls

Order Service - Data Access Layer

Create your component

The database

Item table

Customer table

OrderSummary table

OrderPosition table

Mock data

Entities

ItemEntity

CustomerEntity

OrderEntity

Repositories

ItemRepository

ItemRepositoryTest

FindAll test

JPAQuery

Hints

Repositories with CobiGen

Business functionality

Remark

Next chapter: Order Service - Business Logic Layer