[Java] Simplify the implementation of data history management with Reladomo

What is Reladomo?

Reladomo is a Java ORM framework published by Goldman Sachs as OSS. I like it because it has a characteristic feature that makes it easy to manage change history in RDBMS.

In Reladomo, Java and DDL can be generated by defining the entity in XML. By leveraging the generated API, you can perform CRUD operations on the corresponding tables.

What to do in this article

This time, I will briefly touch on the basic operations of Reladomo, the types of data models, and so on.

At that time, regarding the bitemporal data model (described later), it is easier to understand by looking at the actual behavior. I would like to take a look using a REST API sample combined with Spring Boot. https://github.com/amtkxa/spring-boot-reladomo

About data history management by Reladomo

In order to be able to track the change history of the data, it is necessary to save the historical data, Time that has some meaning should be saved together.

Transaction Time, Vaild Time

With Reladomo, you can easily manage two times to keep track of your data change history.

• Vaild Time (also known as Business Time)
• Time that occurred in the real world.
• Transaction Time (also known as Processing Time)
• Time when changes were made to the system.

Data model

The following four data models can be considered depending on whether Transaction Time or Vaild Time is used or not. Reladomo also gives you the flexibility to choose a data model for each entity.

Actually use it (transaction time data model)

First, let's work with the transaction-time data model, touching on the basic usage of Reladomo.

Preparing a simple domain model

Since the purpose is to confirm the function of Reladomo, we have prepared a simple domain model that seems to be possible.

Create XML file for Reladomo object

First, create an XML file that defines the objects that correspond to the tables in your database.

Account.xml

<MithraObject xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
              objectType="transactional"
              xsi:noNamespaceSchemaLocation="../config/mithraobject.xsd">
    <PackageName>com.amtkxa.springbootreladomosimple.domain.entity</PackageName>
    <ClassName>Account</ClassName>
    <DefaultTable>ACCOUNT</DefaultTable>

    <AsOfAttribute name="processingDate" fromColumnName="PROCESSING_DATE_FROM" toColumnName="PROCESSING_DATE_TO"
                   toIsInclusive="false"
                   isProcessingDate="true"
                   infinityDate="[com.gs.fw.common.mithra.util.DefaultInfinityTimestamp.getDefaultInfinity()]"
                   defaultIfNotSpecified="[com.gs.fw.common.mithra.util.DefaultInfinityTimestamp.getDefaultInfinity()]"
    />
    <Attribute name="accountId" javaType="int" columnName="ACCOUNT_ID" primaryKey="true"
               primaryKeyGeneratorStrategy="SimulatedSequence">
        <SimulatedSequence sequenceName="Account"
                           sequenceObjectFactoryName="com.amtkxa.springbootreladomosimple.infrastructure.util.ObjectSequenceObjectFactory"
                           hasSourceAttribute="false"
                           batchSize="1"
                           initialValue="1"
                           incrementSize="1"/>
    </Attribute>
    <Attribute name="customerId" javaType="int" columnName="CUSTOMER_ID" nullable="false"/>
    <Attribute name="balance" javaType="double" columnName="BALANCE"/>
</MithraObject>

A brief description of each attribute is as follows.

You can also refer to mithraobject.xsd for more information about what is set here.

Java file generation

From the XML file you just defined, you can generate a Java file for the corresponding object.

Creating a MithraClassList

MithraClassList.xml

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Mithra xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="../config/mithraobject.xsd">
    <MithraObjectResource name="ObjectSequence"/>
    <MithraObjectResource name="Customer"/>
    <MithraObjectResource name="Account"/>
    <MithraObjectResource name="Transaction"/>
</Mithra>

gen-reladomo task definition

Define the gen-reladomo task in pom.xml. This Ant task builds on the MithraClassList.xml file and uses the MithraGenerator to generate a Java file.

pom.xml

<tasks>
    <property name="plugin_classpath" refid="maven.plugin.classpath"/>
    <taskdef name="gen-reladomo" classpath="plugin_classpath"
                classname="com.gs.fw.common.mithra.generator.MithraGenerator"/>
    <gen-reladomo
            xml="${project.basedir}/src/main/resources/reladomo/model/MithraClassList.xml"
            generateGscListMethod="true"
            generatedDir="${project.build.directory}/generated-sources/reladomo"
            nonGeneratedDir="${project.basedir}/src/main/java"
    />
</tasks>

Here, two output destinations are defined by generatedDir and nonGeneratedDir.

• generatedDir
• Recreate each time the gen-reladomo task is executed.
• An abstract class and a utility class will be generated that will contain the code needed to perform CRUD operations and more.
• Classes that should not be modified or versioned.
• nonGeneratedDir
• Even if the gen-reladomo task is executed, it will not be recreated if the class to be generated already exists.
• A concrete class is generated that depends on the class generated by generatedDir. In the initial state, only the minimum required constructor is described, and the implementation of business logic etc. is done here.
• A class that should be versioned.

gen-reladomo task execution

The gen-reladomo task will generate a Java file, such as by running mvn clean install. generatedDir is an abstract class that is recreated every time to follow XML changes, and nonGeneratedDir is a concrete class that allows the user to add their own business logic. The generated classes and dependencies are as follows.

The abstract class generated here contains most of the code needed to perform CRUD operations on the entity.

• AccountAbstract
• Class corresponding to a single record.
• AccountListAbstract
• A class that supports multiple records.
• AccountDatabaseObjectAbstract
• A class that contains information about connections and tables.
• AccountFinder
• A class that includes processing that specifies conditions when searching.

DDL generation

You can generate DDL for databases supported by Reladomo. DDL seems to support tables, indexes and foreign keys.

gen-reladomo-db task definition

Define the gen-reladomo-db task in pom.xml. This Ant task builds on the MithraClassList.xml file and uses MithraDbDefinitionGenerator to generate the DDL.

pom.xml

<tasks>
    <property name="plugin_classpath" refid="maven.plugin.classpath"/>
    <taskdef name="gen-reladomo-db" classpath="plugin_classpath"
                classname="com.gs.fw.common.mithra.generator.dbgenerator.MithraDbDefinitionGenerator"/>
    <gen-reladomo-db
            xml="${project.basedir}/src/main/resources/reladomo/model/MithraClassList.xml"
            generatedDir="${project.build.directory}/generated-resources/db"
            databaseType="postgres"/>
</tasks>

gen-reladomo-db task execution

The gen-reladomo-db task will generate the DDL by doing something like mvn clean install. The DDL generated when postgres was set to databaseType had the following contents.

target/generated-resources/db/ACCOUNT.ddl

drop table if exists ACCOUNT;

create table ACCOUNT
(
    ACCOUNT_ID int not null,
    CUSTOMER_ID int not null,
    BALANCE float8,
    PROCESSING_DATE_FROM timestamp not null,
    PROCESSING_DATE_TO timestamp not null
);

target/generated-resources/db/ACCOUNT.idx

alter table ACCOUNT add constraint ACCOUNT_PK primary key (ACCOUNT_ID, PROCESSING_DATE_TO);

drop index if exists ACCOUNT_IDX0;
create index ACCOUNT_IDX0 on ACCOUNT(CUSTOMER_ID, PROCESSING_DATE_TO);

When managing history using Reladomo, it seems that Foreign Key DDL is not generated from the corresponding XML. By the way, it seems that the DDL generated here is not used as it is, but is supposed to be customized based on this.

The DDLs generated by Reladomo are not meant to be used as is. They are merely meant to be a starting point for further customization. There are two common reasons to customize the generated DDLs. One is to add database specific clauses for specifying table spaces, schemas etc. Another is to add additional indices based on usage patterns seen in the application. Guided Tour Of Reladomo - 2.3. Database DDL Generation

I'm grateful for the DDL generation function.

Reladomo CRUD operations

CRUD (Create)

Single record registration

Add the following constructor to the concrete class generated by Reladomo.

Account.java

public Account(int customerId, double balance) {
    this(com.gs.fw.common.mithra.util.DefaultInfinityTimestamp.getDefaultInfinity());
    this.setCustomerId(customerId);
    this.setBalance(balance);
}

Then try running the code below.

MithraManagerProvider.getMithraManager().executeTransactionalCommand((tx) -> {
    Account account = new Account(1, 100);
    account.insert();
    return null;
});

Then, the record was registered in the ACCOUNT table of the connection destination DB.

testdb=> select * from account;
 account_id | customer_id | balance |  processing_date_from  | processing_date_to
------------+-------------+---------+------------------------+---------------------
          1 |           1 |     100 | 2018-12-23 14:42:14.61 | 9999-12-01 23:59:00

Sequence value numbering

Since the numbered sequence value is set to ʻaccount_id` for which no value is set, it is supplemented there. If you enter the following in the corresponding column of Account.xml defined earlier, you will be able to number the sequence value.

Account.xml

    <Attribute name="accountId" javaType="int" columnName="ACCOUNT_ID" primaryKey="true"
               primaryKeyGeneratorStrategy="SimulatedSequence">
        <SimulatedSequence sequenceName="Account"
                           sequenceObjectFactoryName="com.amtkxa.springbootreladomosimple.infrastructure.util.ObjectSequenceObjectFactory"
                           hasSourceAttribute="false"
                           batchSize="1"
                           initialValue="1"
                           incrementSize="1"/>
    </Attribute>

ʻObjectSequenceObjectFactory specified in ʻAccount.xml is created separately.

ObjectSequenceObjectFactory.java

public class ObjectSequenceObjectFactory implements MithraSequenceObjectFactory {
  public MithraSequence getMithraSequenceObject(String sequenceName, Object sourceAttribute, int initialValue) {
    ObjectSequence objectSequence = ObjectSequenceFinder.findByPrimaryKey(sequenceName);
    if (objectSequence == null) {
      objectSequence = new ObjectSequence();
      objectSequence.setSimulatedSequenceName(sequenceName);
      objectSequence.setNextValue(initialValue);
      objectSequence.insert();
    }
    return objectSequence;
  }
}

The values to be numbered are managed in the OBJECT_SEQUENCE table, which also defines the metadata in XML.

ObjectSequence.xml

<MithraObject xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
              objectType="transactional"
              xsi:noNamespaceSchemaLocation="../config/mithraobject.xsd">

    <PackageName>com.amtkxa.springbootreladomosimple.domain.entity</PackageName>
    <ClassName>ObjectSequence</ClassName>
    <DefaultTable>OBJECT_SEQUENCE</DefaultTable>

    <Attribute name="simulatedSequenceName" javaType="String" columnName="SEQUENCE_NAME" primaryKey="true" maxLength="64"/>
    <Attribute name="nextValue" javaType="long" columnName="NEXT_VALUE"/>

</MithraObject>

testdb=> select * from object_sequence;
 sequence_name | next_value
---------------+------------
 Account       |          2

Registration of multiple records

It is possible to register multiple records by using ʻAccountList` generated by Reladomo.

MithraManagerProvider.getMithraManager().executeTransactionalCommand((tx) -> {
    AccountList accountList = new AccountList();
    accountList.add(new Account(2, 200));
    accountList.add(new Account(3, 300));
    accountList.insertAll();
    return null;
});

When I ran this code, multiple records were registered.

testdb=> select * from account where customer_id in (2, 3);
 account_id | customer_id | balance |  processing_date_from  | processing_date_to
------------+-------------+---------+------------------------+---------------------
          2 |           2 |     200 | 2018-12-23 15:10:37.92 | 9999-12-01 23:59:00
          3 |           3 |     300 | 2018-12-23 15:10:37.92 | 9999-12-01 23:59:00

CRUD (Read)

The search process uses the Finder class generated by Reladomo.

Search condition settings

You can instantiate the ʻOperation class by using the Finder class. The ʻOperation class can set the WHERE clause of the issued query.

Let me give you some examples.

// where NAME = 'tanaka'
Operation op1 = CustomerFinder.name().eq("tanaka");
// where NAME <> 'tanaka'
Operation op2 = CustomerFinder.name().notEq("tanaka");
// where NAME like 'tanaka%'
Operation op3 = CustomerFinder.name().startsWith("tanaka");
// where NAME like '%taro'
Operation op4 = CustomerFinder.name().endsWith("taro");
// where NAME like '%tanaka%'
Operation op5 = CustomerFinder.name().wildCardEq("*tanaka*");
// where CUSTOMER_ID < 3
Operation op6 = CustomerFinder.customerId().lessThan(3);

In addition to the ones listed here, ʻisNull and contains are available. You can also chain ʻOperations as follows:

// where NAME like '%tanaka%' and CUSTOMER_ID < 3
Operation op = op5.and(op6);

1 search

You can get a single record by using the findOne of the Finder class.

// select t0.ACCOUNT_ID,t0.CUSTOMER_ID,t0.BALANCE,t0.PROCESSING_DATE_FROM,t0.PROCESSING_DATE_TO from ACCOUNT t0 
// where  t0.ACCOUNT_ID = 1 and t0.PROCESSING_DATE_TO = '9999-12-01 23:59:00.000'
Account account = AccountFinder.findOne(AccountFinder.accountId().eq(1));

The condition of the query actually issued is t0.PROCESSING_DATE_TO = '9999-12-01 23: 59: 00.000', which refers to the record corresponding to the latest history.

Multiple search

If you use findMany of the Finder class, you can get multiple records in List.

// select t0.CUSTOMER_ID,t0.NAME,t0.COUNTRY,t0.PROCESSING_DATE_FROM,t0.PROCESSING_DATE_TO from CUSTOMER t0
// where  t0.NAME like 'tanaka%' and t0.PROCESSING_DATE_TO = '9999-12-01 23:59:00.000'
CustomerList customerList = CustomerFinder.findMany(CustomerFinder.name().startsWith("tanaka"));

CRUD (Update)

If you make changes to an object retrieved using the Finder class, the changes will be reflected in the DB table as well. We have prepared the following data to check the behavior.

testdb=> select * from customer where customer_id = 7;
 customer_id |  name   | country |  processing_date_from  | processing_date_to
-------------+---------+---------+------------------------+---------------------
           7 | Pikachu | Japan   | 2018-12-23 19:36:07.06 | 9999-12-01 23:59:00

Pikachu will change to Raichu, so suppose the following code is executed to make the changes.

MithraManagerProvider.getMithraManager().executeTransactionalCommand((tx) -> {
    Customer customer = CustomerFinder.findOne(CustomerFinder.customerId().eq(7));
    customer.setName("Raichu");
    return null;
});

The execution result is as follows.

testdb=> select * from customer where customer_id = 7;
 customer_id |  name   | country |  processing_date_from  |   processing_date_to
-------------+---------+---------+------------------------+------------------------
           7 | Pikachu | Japan   | 2018-12-23 19:36:07.06 | 2018-12-23 19:46:13.91
           7 | Raichu  | Japan   | 2018-12-23 19:46:13.91 | 9999-12-01 23:59:00

The query actually issued is as follows.

--Get the latest history data
select t0.CUSTOMER_ID,t0.NAME,t0.COUNTRY,t0.PROCESSING_DATE_FROM,t0.PROCESSING_DATE_TO from CUSTOMER t0 
where  t0.CUSTOMER_ID = 7 and t0.PROCESSING_DATE_TO = '9999-12-01 23:59:00.000' FOR SHARE OF t0;

--Update the validity period of the latest history data
update CUSTOMER set PROCESSING_DATE_TO = '2018-12-23 19:46:13.910'  
where CUSTOMER_ID = 7 AND PROCESSING_DATE_TO = '9999-12-01 23:59:00.000';

--Register the changed data as the latest history
insert into CUSTOMER(CUSTOMER_ID,NAME,COUNTRY,PROCESSING_DATE_FROM,PROCESSING_DATE_TO) 
values (7,'Raichu','Japan','2018-12-23 19:46:13.910','9999-12-01 23:59:00.000');

CRUD (Delete)

It can be reflected in the table by deleting the object obtained by using the Finder class. In that case, the behavior is different for delete (), terminate (), and purge (), so let's move it easily.

delete()

delete () can be used for objects that Reladomo does not manage history (determined by whether XML has ʻAsOfAttribute`). Doing this physically deletes the record from the table.

MithraManagerProvider.getMithraManager().executeTransactionalCommand((tx) -> {
    Transaction transaction = TransactionFinder.findOne(TransactionFinder.transactionId().eq(4));
    transaction.delete();
    return null;
});

The actual query executed is as follows.

select t0.LOG_ID,t0.ACCOUNT_ID,t0.TRANSACTION_TYPE,t0.AMOUNT,t0.TRANSACTION_DATE from TRANSACTION t0 
where  t0.LOG_ID = 4 FOR SHARE OF t0;

delete from TRANSACTION where LOG_ID = 4;

terminate()

terminate () terminates the history of objects that are managed by Reladomo. (Record as history remains) The following data was prepared to check the behavior.

testdb=> select * from customer where customer_id = 7;
 customer_id |  name   | country |  processing_date_from  |   processing_date_to
-------------+---------+---------+------------------------+------------------------
           7 | Pikachu | Japan   | 2018-12-23 19:36:07.06 | 2018-12-23 19:46:13.91
           7 | Raichu  | Japan   | 2018-12-23 19:46:13.91 | 9999-12-01 23:59:00

In contrast, run this code.

MithraManagerProvider.getMithraManager().executeTransactionalCommand((tx) -> {
    Customer customer = CustomerFinder.findOne(CustomerFinder.customerId().eq(7));
    customer.terminate();
    return null;
});

Then, the part registered as 9999-12-01 23:59:00 in processing_date_to will be updated.

testdb=> select * from customer where customer_id = 7;
 customer_id |  name   | country |  processing_date_from  |   processing_date_to
-------------+---------+---------+------------------------+------------------------
           7 | Pikachu | Japan   | 2018-12-23 19:36:07.06 | 2018-12-23 19:46:13.91
           7 | Raichu  | Japan   | 2018-12-23 19:46:13.91 | 2018-12-23 22:10:36.68

The actual query executed is as follows.

select t0.CUSTOMER_ID,t0.NAME,t0.COUNTRY,t0.PROCESSING_DATE_FROM,t0.PROCESSING_DATE_TO from CUSTOMER t0 
where  t0.CUSTOMER_ID = 7 and t0.PROCESSING_DATE_TO = '9999-12-01 23:59:00.000' FOR SHARE OF t0;

update CUSTOMER set PROCESSING_DATE_TO = '2018-12-23 22:10:36.680'  
where CUSTOMER_ID = 7 AND PROCESSING_DATE_TO = '9999-12-01 23:59:00.000';

purge()

purge () deletes the history of the object that is managed by Reladomo.

MithraManagerProvider.getMithraManager().executeTransactionalCommand((tx) -> {
    Customer customer = CustomerFinder.findOne(CustomerFinder.customerId().eq(6));
    customer.purge();
    return null;
});

The actual query executed is as follows.

select t0.CUSTOMER_ID,t0.NAME,t0.COUNTRY,t0.PROCESSING_DATE_FROM,t0.PROCESSING_DATE_TO from CUSTOMER t0
where  t0.CUSTOMER_ID = 6 and t0.PROCESSING_DATE_TO = '9999-12-01 23:59:00.000' FOR SHARE OF t0;

delete from CUSTOMER where CUSTOMER_ID = 6;

Actually use it (bitemporal data model)

Use a sample REST API that works with a bitemporal data model in combination with Spring Boot. https://github.com/amtkxa/spring-boot-reladomo

Transaction Time and Vaild Time handled in the bitemporal data model often have the same timing, but here we will trace the case where you can see the merit of managing the two at the same time.

• This case is almost the same as the one covered in Guided Tour Of Reladomo.
• In processing_date, the history is registered at the time that I operated and reflected in the system.

I mentioned the basic usage of Reladomo earlier, so I will introduce the parts that are not covered much.

January 1-Open an account with an initial deposit of 10,000 yen

The very first history record has been registered.

sampledb=> select * from account where account_id = 10;
 account_id | customer_id | balance | business_date_from  |  business_date_to   |  processing_date_from  | processing_date_to
------------+-------------+---------+---------------------+---------------------+------------------------+---------------------
         12 |           1 |   10000 | 2019-01-01 00:00:00 | 9999-12-01 23:59:00 | 2018-12-22 16:17:17.49 | 9999-12-01 23:59:00

January 17-5000 yen deposit

Suppose that you couldn't make a deposit due to some trouble. Therefore, there is no change in the table contents.

January 20-20000 yen deposit

The history has been cut due to the deposit of 20000 yen.

sampledb=> select * from account where account_id = 12 order by 4, 6;
 account_id | customer_id | balance | business_date_from  |  business_date_to   |  processing_date_from  |   processing_date_to
------------+-------------+---------+---------------------+---------------------+------------------------+------------------------
         12 |           1 |   10000 | 2019-01-01 00:00:00 | 9999-12-01 23:59:00 | 2018-12-22 16:33:09.54 | 2018-12-22 16:38:27.48
         12 |           1 |   10000 | 2019-01-01 00:00:00 | 2019-01-20 00:00:00 | 2018-12-22 16:38:27.48 | 9999-12-01 23:59:00
         12 |           1 |   30000 | 2019-01-20 00:00:00 | 9999-12-01 23:59:00 | 2018-12-22 16:38:27.48 | 9999-12-01 23:59:00

• If the implementation uses ʻincrementBalance () of the ʻAccount class generated by Reladomo, the corresponding table will be updated so that the deposit amount of 20000 yen will be added to the balance.
• The record with processing_date_to andprocessing_date_tois 9999-12-01 23:59:00 is the latest history.

January 25-Fixed 5000 yen that could not be deposited

Suppose an amendment to the amount deposited on January 17th occurs on January 25th. In this case, if you use ʻincrementBalance ()` to update the object obtained by specifying January 17 for business_date, the corresponding table will be updated to the following contents.

sampledb=> select * from account where account_id = 12 order by 6, 4;
 account_id | customer_id | balance | business_date_from  |  business_date_to   |  processing_date_from  |   processing_date_to
------------+-------------+---------+---------------------+---------------------+------------------------+------------------------
         12 |           1 |   10000 | 2019-01-01 00:00:00 | 9999-12-01 23:59:00 | 2018-12-22 16:33:09.54 | 2018-12-22 16:38:27.48
         12 |           1 |   10000 | 2019-01-01 00:00:00 | 2019-01-20 00:00:00 | 2018-12-22 16:38:27.48 | 2018-12-22 16:45:31.94
         12 |           1 |   30000 | 2019-01-20 00:00:00 | 9999-12-01 23:59:00 | 2018-12-22 16:38:27.48 | 2018-12-22 16:45:31.94
         12 |           1 |   10000 | 2019-01-01 00:00:00 | 2019-01-17 00:00:00 | 2018-12-22 16:45:31.94 | 9999-12-01 23:59:00
         12 |           1 |   15000 | 2019-01-17 00:00:00 | 2019-01-20 00:00:00 | 2018-12-22 16:45:31.94 | 9999-12-01 23:59:00
         12 |           1 |   35000 | 2019-01-20 00:00:00 | 9999-12-01 23:59:00 | 2018-12-22 16:45:31.94 | 9999-12-01 23:59:00

We were able to confirm that the balance of the historical data that needs to be corrected could be updated while retaining the historical data before the correction.

Finally

After actually using Reladomo, I realized that data history management can be easily realized. Especially in cases where you want to track the change history later for auditing etc., it seems that you can expect improvement in development efficiency. It may be useful in many situations when developing with the JVM language.

Also, cache strategy selection for each entity, Multi-Threaded matcher Loader (MTLoader), etc. I'm particularly interested in the features provided by Reladomo for high performance, so I would like to investigate that as well at another time.

Materials that I used as a reference

When I looked it up this time, I felt that there were many English documents, but all of them were of high quality.

• Reladomo Documentation
• Guided Tour Of Reladomo
• Introducing Reladomo - Enterprise Open Source Java ORM, Batteries Included!
• Introducing Reladomo - Enterprise Open Source Java ORM, Batteries Included! (Part 2)

Also, there are very easy-to-understand Japanese materials, which was really helpful.

• Introduction of temporal data model and Reladomo for data history management # jjug_ccc # ccc_g3
• Introduction to Reladomo JJUG Night Seminar #jjug
• Blog I don't understand-Introduction to BiTemporal Data Model
• A blog I don't understand-I touched an ORM called Reladomo