Developing Spring Boot Application with SAP HANA to store Meter Usage

In the AI on mobile: Powering your field force – Part 1, Vriddhi Shetty has set the context of the project and explained what we are trying to achieve with the blog post series.

In next section, I will walk you through the steps to create the Meter Reading Service. This part gives a high-level overview on basic design principles followed to develop a cloud application using SAP Cloud Programming model (CAP) and Spring Boot for storing Meter Usage data. The CAP runtimes provides many generic implementations for recurring tasks and best practices. This helps in quick development and minimised boilerplate code. Spring Boot is used as a run time container which initialises the CAP Java runtime automatically.

Pre-requisites
Please follow tutorial Build a Business Application Using CAP for Java which lists down all the steps to Build and Deploy a cloud application using SAP Cloud Application Programming Model (CAP) for Java

Requirements
The Cloud Application needs to support CRUD operations for storing Buildings, Apartments, Users, Meter Usage along with Model data. It should also support storing of clicked images of the meter data which can be later retrieved and verified. The current month’s bill can be calculated by retrieving last month’s meter usage and current month’s meter usage. Support to store ML-model which can be used for predicting image data will be stored in Model table. We have used SAP Business Application Studio for development of this application. Instructions are detailed out in the link specified in the Pre-requisites section.

Following are the steps:

Step1: Create application skeleton project
The first step is to create skeleton project for the application, It can be achieved by running the below maven command.

mvn -B archetype:generate 
> -DarchetypeArtifactId=cds-services-archetype 
> -DarchetypeGroupId=com.sap.cds 
> -DarchetypeVersion=RELEASE 
> -DgroupId=com.sap.cap 
> -DartifactId=smartmeter-service 
> -Dpackage=com.sap.cap.smartmeterservice

The above command initializes the application. It creates following folders by default:

• db – This folder stores the entity model definitions.
• srv – This folder defines the service layer for the application.

Step2: Define the domain model
1. Go to your db folder and create a file named schema.cds
2. Below code defines model definitions for the application

namespace sap.capire.models;

using { Currency, cuid, managed, sap.common.CodeList } from '@sap/cds/common';

entity User : cuid {
   firstname : String(50);
   lastname : String(50);
   usertype:  String(20); //user type can be system or customer
   userid : String(50);
   password : String(50);
   email :  String(50);
}

entity Building : cuid {
    apartments    : Composition of many BuildingApartment on apartments.building = $self;
    buildingname : String(50);
    street1 : String(50);
    street2 : String(50);
    city : String(50);
    state : String(50);
    country : String(50);
    zipcode : String(50);
    geolocation : String(50);
    latitude : Decimal(10,8);
    longitude : Decimal(10,8);
    hasapartments: Boolean;
    owner : Association to User;
}

entity BuildingApartment : cuid {
    apartmentnumber : String(50);
    building : Association to Building;
    owner : Association to User;
}

entity MeterUsage : cuid {
    building : Association to Building not null;
    apartment : Association to BuildingApartment;
    previousreading : Decimal(9,2);
    currentreading : Decimal(9,2);
    units : Decimal(9,2);
    billamount    : Decimal(9,2);
    currency : Currency;
    billdate : Date;
    duedate : Date;
    type : String(20); //meter type can be electricity, water etc.;

    @Core.MediaType: mediaType
    content : LargeBinary ; //handling media

    @Core.IsMediaType: true
    mediaType : String;
} 

entity Model {
   key model_id : Integer;
   model : LargeBinary ;
   time_stamp :  Timestamp;
}

This defines entities for storing Users, Building, BuildingApartment, MeterUsage and Model data. MeterUsage stores the clicked image as a BLOB in MeterUsage table. ML-model is stored in Model table as a BLOB. The domain models are defined using CDS entity definitions.

Step3: Define a Service
This step describes meter service. It expose projections on entities defined in the data model.
1. In srv folder root, create a file, named as meter-service.cds
2. Add following service definition to the file.

using { sap.capire.models as db } from '../db/schema';

// Define MeterService Service
service MeterService {
    entity Building as projection   on db.Building;
    entity BuildingApartment as projection on db.BuildingApartment;
    entity MeterUsage as projection on db.MeterUsage;
    entity User as projection on db.User;
    entity Model as projection on db.Model;
}

This completes the bare-bone skeleton application which exposes the basic CRUD operations for the above entities.

Step4: Run the application
Execute the following command to compile and execute the application

$ mvn spring-boot:run

This starts the application on port 8080 and open the link in web browser http://machine-name:8080. It will lists the OData end points exposed by this application.

Step5: Configure SQLite Data Base usage

1. In the root folder, issue command “npm install --save-dev sqlite3” to install sqlite node packages.
2. To initialize the smart meter service database with the defined domain model, issue command “cds deploy --to sqlite”.

The above steps create a file sqlite.db and initializes DB with the required model definitions.

To use the sqlite.db created in the above step, update file srv/src/main/resources/application.yaml with following

spring:
  config.activate.on-profile: default
  datasource:
    url: "jdbc:sqlite: <Root-folder>/smartmeter-service/sqlite.db"
    driver-class-name: org.sqlite.JDBC
    hikari:
      maximum-pool-size: 1
  sql:
    init:
      mode: never

sql.init.mode is set to ‘never’ since DB has already been initialized.

Step6: Configure SAP HANA Data Base usage
1. In the root folder, issue command “npm install –save-dev @sap/hdi-deploy” to install HANA node packages.
2. Create a new file “.cdsrc.json” in the root folder of the project and copy the content “{ “hana” : { “deploy-format”: “hdbtable” }}”
3. To initialize the smart meter service database with the defined domain model, issue command “cds deploy –to hana:smartmeterstore-hana”. This initializes the Database with the required model definitions and also creates “default-env.json” in the root folder. This file contains credentials which will be used by the application to connect to the Database.
4. The credentials will be injected into the application as Service Bindings. Please add following dependency in pom.xml available in “srv” folder in order for this to work.

<dependency>
  <groupId>com.sap.cds</groupId>
  <artifactId>cds-feature-hana</artifactId>
</dependency>

5. Build the application and run it using following command.

$ mvn spring-boot:run -Dspring-boot.run.profiles=cloud

This completes the basic Meter Reading application which exposes the entities defined in Service Layer via OData APIs. It can handle all CRUD events (create, read, update, and delete) triggered by OData requests out of the box.

For deployment to SAP Business Technology Platform, please follow the tutorial mentioned in Pre-requisites section.

Step7: Event Handlers
Although CAP provides generic event handlers to serve most CRUD requests out-of-the-box, it’s possible to add business logic through Custom Event Handlers.

This section describes how to register event handlers on services. In CAP everything that happens at runtime is an event that is sent to a service. With event handlers the processing of these events can be extended or overridden. Event handlers can be used to handle CRUD events, implement actions and functions and to handle asynchronous events from a messaging service.
1. Create a folder handlers in the folder srv/src/main/java/com/sap/cap/smartmeterservice.
2. Create Java file SmartMeterService.java in the above folder.

This file will hold the logic to calculate the bill when a new MeterUsage is submitted. It will get last submitted MeterUsage and copy the currentreading from it to the previousreading of the newly submitted meter usage. Using that, required Bill amount can be calculated.

@Before(event = CdsService.EVENT_CREATE, entity = "MeterService.MeterUsage")
public void validateMeterUsageAndCalculateBill(MeterUsage meterUsage) {
  String buildingId = meterUsage.getBuildingId();
  String apartmentId = meterUsage.getApartmentId();
  CqnSelect sel;

  //get apartments last meter usage
  if(apartmentId != null) {
    sel = Select.from(MeterUsage_.class).where(b -> b.building_ID().eq(buildingId)
                                      .and(b.apartment_ID().eq(apartmentId))).orderBy(b -> b.billdate().desc());
  } else {
    //get buildings last meter usage
    sel = Select.from(MeterUsage_.class).where(b -> b.building_ID().eq(buildingId)
                                      .and(b.apartment_ID().isNull())).orderBy(b -> b.billdate().desc());
  }

  //retrieve last meter usage
  Optional<MeterUsage> lastOptionalUsage = db.run(sel).first(MeterUsage.class);
  if(lastOptionalUsage.isPresent()) {
    MeterUsage lastMeterUsage = lastOptionalUsage.get();
    //set previous reading to the current meter usage
    meterUsage.setPreviousreading(lastMeterUsage.getCurrentreading());
  }

  BigDecimal previousReading = BigDecimal.ZERO;
  if(meterUsage.getPreviousreading() != null) {
    previousReading = meterUsage.getPreviousreading();
  }
  //calculate bill amount
  meterUsage.setUnits(meterUsage.getCurrentreading().subtract(previousReading));
  meterUsage.setBillamount(meterUsage.getUnits().multiply(METER_RATE));
  meterUsage.setCurrencyCode(CURRENCY_CODE);
}

This logic will be triggered on create event of meter usage. It will calculate various attributes and will persist in DB.

This blog post covers a high-level overview of the application built using Spring boot and SAP Cloud Programming model (CAP).

In the AI on mobile: Powering your field force – Part 3 of 5, Sumin Lee will explain how we train the ML-model and convert to tflite and store it in SAP HANA to be consumed by the front end application.

If you have any questions or would like to discuss this in more detail, please write in to any one of us or can leave a comment here.

Please follow below references for further detailed information.

References:

• Build a Business Application Using CAP for Java – https://developers.sap.com/mission.cap-java-app.html
• SAP Cloud Application Programming Model(CAP) – https://cap.cloud.sap/docs/java/application-services
• Building CDS QL Statements – https://cap.cloud.sap/docs/java/query-api
• Serving Media Data – https://cap.cloud.sap/docs/guides/media-data#using-external-resources