Runtime-Defined Columns With asentinel-orm

Asentinel-orm is a lightweight ORM tool built on top of Spring JDBC, particularly JdbcTemplate.  Thus, it possesses most of the features one would expect from a basic ORM, such as SQL generation, lazy loading, etc.

By leveraging the JdbcTemplate, it means it allows participation in Spring-managed transactions, and it can be easily integrated into any project that already uses JdbcTemplate as a means to interact with the database.

Since 2015, asentinel-orm has been successfully used in several applications and continually improved as required by business needs. In the summer of 2024, it officially became an open-source project, which we consider will accelerate its evolution and increase the number of contributors.

In this article, a sample application is built to outline several ORM key features:

• Simple configuration
• Straightforward domain entity modeling via custom annotations
• Easy writing and secure execution of plain SQL statements
• Automatic SQL statements’ generation
• Dynamic schema (entities are enriched with additional runtime attributes, persisted, and read without code changes)

Application

Setup

• Java 21
• Spring Boot 3.4.0
• asentinel-orm 1.70.0
• H2 database

Configuration

In order to interact with the asentinel-orm and leverage its functionalities, an instance of OrmOperations is required.

As stated in the JavaDoc, this is the central interface for performing ORM operations, and it is neither intended nor required to be specifically implemented in the client code.

The sample application includes the configuration code to create a bean of this type.

@Bean
public OrmOperations orm(SqlBuilderFactory sqlBuilderFactory,
                             JdbcFlavor jdbcFlavor, SqlQuery sqlQuery) {
    return new OrmTemplate(sqlBuilderFactory, new SimpleUpdater(jdbcFlavor, sqlQuery));
}

OrmOperations has two super interfaces:

• SqlBuilderFactory – creates SqlBuilder instances that can be further used to create SQL queries. SqlBuilder is able to auto-generate parts of the query, for instance, the one that selects the columns. The where clause, the order by clause, other conditions, and the actual columns can be added using methods from the SqlBuilder class as well. In the next part of this section, a SqlBuilder generated query example is shown.
• Updater – used for saving entities to their respective database tables. It can perform inserts or updates depending on whether the entity is newly created or already existing. A strategy interface called NewEntityDetector exists, which is used for determining whether an entity is a new one. By default, the SimpleNewEntityDetector is used.

All queries generated by the ORM are executed using an SqlQueryTemplate instance, which further needs a Spring JdbcOperations/JdbcTemplate to work. Eventually, all queries reach the good old JdbcTemplate through which they are executed while participating in Spring transactions, just as any JdbcTemplate direct execution.

Database specific SQL constructs and logic are provided via implementations of the JdbcFlavor interface, further injected into most of the beans mentioned above. In this article, as an H2 database is used, a H2JdbcFlavor implementation is configured.

The complete configuration of the ORM as part of the sample application is OrmConfig.

Implementation

The experimental domain model exposed by the sample application is straightforward and consists of two entities – car manufacturers and car models. Representing exactly what their names denote, the relationship between them is obvious: one car manufacturer may own multiple car models. 

In addition to its name, the car manufacturer is enriched with attributes (columns) that are input by the application user dynamically at runtime. The exemplified use-case is straight-forward:

• The user is requested to provide the aimed names and types for the dynamic attributes
• A couple of car manufacturers are created with concrete values for previously added dynamic attributes, and then
• The entities are loaded back described by both the initial and the runtime-defined attributes

The initial entities are mapped using the database tables below:

CREATE TABLE CarManufacturers (
    ID INT auto_increment PRIMARY KEY,
    NAME VARCHAR(255)
);
 
CREATE TABLE CarModels(
    ID INT auto_increment PRIMARY KEY,
    CarManufacturer int,
    NAME VARCHAR(255),
    TYPE VARCHAR(15),
    foreign key (CarManufacturer) references CarManufacturers(id)
);

The corresponding domain classes are decorated with ORM-specific annotations to configure the mappings to the above database tables. 

@Table("CarManufacturers")
public class CarManufacturer {
     
    @PkColumn("id")
    private int id;
     
    @Column("name")
    private String name;
     
    @Child(parentRelationType = RelationType.MANY_TO_ONE, 
            fkName = CarModel.COL_CAR_MANUFACTURER, 
            fetchType = FetchType.LAZY)
    private List<CarModel> models = Collections.emptyList();
 
    ... 
}

@Table("CarModels")
public class CarModel {
     
    public static final String COL_CAR_MANUFACTURER = "CarManufacturer";
     
    @PkColumn("id")
    private int id;
     
    @Column("name")
    private String name;
     
    @Column("type")
    private CarType type;
     
    @Child(fkName = COL_CAR_MANUFACTURER, fetchType = FetchType.LAZY)
    private CarManufacturer carManufacturer;
 
    ...
}
 
public enum CarType {
    CAR, SUV, TRUCK
}

A few considerations:

• @Table – maps (associates) the class to a database table
• @PkColumn – maps the id (unique identifier) to the table primary key
• @Column – maps a class member to a table column 
• @Child – defines the relationship with another entity
• @Child annotated members – configured to be lazily loaded
• type table column – mapped to an enum field – CarType

In order for the CarManufacturer class to support runtime-defined attributes (mapped to runtime-defined table columns), a subclass as the one below is defined:

public class CustomFieldsCarManufacturer extends CarManufacturer 
            implements DynamicColumnsEntity<DynamicColumn> {
     
    private final Map<DynamicColumn, Object> customFields = new HashMap<>();
   	...
 
    @Override
    public void setValue(DynamicColumn column, Object value) {
        customFields.put(column, value);
         
    }
 
    @Override
    public Object getValue(DynamicColumn column) {
        return customFields.get(column);
    }
    ...
}

This class stores the runtime-defined attributes (fields) in a Map. The interaction between the runtime field values and the ORM is fulfilled via the implementation of the DynamicColumnEntity interface. 

public interface DynamicColumnsEntity<T extends DynamicColumn> {
  
    void setValue(T column, Object value);
  
    Object getValue(T column);
}

• setValue() – is used to set the value of the runtime-defined column when this is read from the table
• getValue() – is used to retrieve the value of a runtime-defined column when this is saved to the table

The DynamicColumn maps runtime-defined attributes to their corresponding columns in a similar manner to the @Column annotation maps compile time known members.

When running the application, the CfRunner is executed. The user is asked to input names and types for the desired dynamic custom attributes that enrich the CarManufacturer entity (for simplicity, only int and varchar types are supported). 

For each name–type pair, a DML command is executed so that the new columns can be added to the CarManufacturer database table. The following method (declared in CarService) performs the operation.

@Transactional
public void addManufacturerField(String name, String type) {
	orm.getSqlQuery()
           .update("alter table CarManufacturers add column " +  name + " " + type);
}

Each input attribute is recorded as a DefaultDynamicColumn, a DynamicColumn reference implementation.

Once all attributes are defined, two car manufacturers are added to the database, as the user provides values for each such attribute.

Map<DynamicColumn, Object> dynamicColumnsValues = new HashMap<>();
for (DynamicColumn dynamicColumn : dynamicColumns) {
    // read values for each dynamic attribute
    ...
}
         
CustomFieldsCarManufacturer mazda = new CustomFieldsCarManufacturer("Mazda", dynamicColumnsValues);
 
carService.createManufacturer(mazda, dynamicColumns);

The below method (declared in CarService) actually creates the entity via the ORM. 

@Transactional
public void createManufacturer(CustomFieldsCarManufacturer manufacturer, List<DynamicColumn> attributes) {
    orm.update(manufacturer, new UpdateSettings<>(attributes, null));
}

The 2 parameter version of the OrmOperations update() method is called, which allows passing an UpdateSettings instance and communicating to the ORM upon execution that there are runtime-defined whose values shall be persisted. 

Lastly, two car models are created, corresponding to one of the previously added car manufacturers.

CarModel mx5 = new CarModel("MX5", CarType.CAR, mazda);
CarModel cx60 = new CarModel("CX60", CarType.SUV, mazda);
carService.createModels(mx5, cx60);

The below method (declared in CarService) actually creates the entities via the ORM, this time using OrmOperations update() method for persisting entities without dynamic attributes. For convenience, multiple entities are created in one call. 

@Transactional
public void createModels(CarModel... models) {
    orm.update(models);
}

As a last step, one of the created manufacturers is loaded back by its name using an ORM-generated query. 

CarManufacturer mazda1 = carService.findManufacturerByName("Mazda", dynamicColumns);
 
@Transactional(readOnly = true)
public CarManufacturer findManufacturerByName(String name, List<DynamicColumn> attributes) {
    return orm.newSqlBuilder(CustomFieldsCarManufacturer.class)
            .select(
                    AutoEagerLoader.forPath(CarManufacturer.class, CarModel.class),
                    new DynamicColumnsEntityNodeCallback<>(
                            new DefaultObjectFactory<>(CustomFieldsCarManufacturer.class),
                            attributes
                    )
            )
            .where().column("name").eq(name)
        .execForEntity();
}

A few explanations regarding the method defined above are worth doing.

The OrmOperations newSqlBuilder() method creates a SqlBuilder instance, and as the name suggests, this can be used to generate SQL queries. The SqlBuilder select() method generates the select from table part of the query, while the rest (where, order by) must be added. The query select part can be customized by passing EntityDescriptorNodeCallback instances (details on EntityDescriptorNodeCallback may be the subject of a future article). 

In order to let the ORM know that the plan is to select and map runtime-defined columns, a DynamicColumnsEntityNodeCallback needs to be passed. Together with it, an AutoEagerLoader one is provided so that the ORM understands to eagerly load the list of CarModels associated with the manufacturer. Nevertheless, this has nothing to do with the runtime-defined attributes, but it demonstrates how a child member can be eagerly loaded.

Conclusion

While there are probably other ways of working with runtime-defined columns when data is stored in relational databases, the approach presented in this article has the advantage of using standard database columns that are read/written using standard SQL queries generated directly by the ORM.

It wasn’t rare when we had the chance to discuss in “the community” the asentinel-orm, the reasons we had to develop such a tool. Usually, at first glance, developers proved to be reluctant and reserved when it came to a custom-made ORM, asking why not using Hibernate or other JPA implementations. 

In our case, the main driver was the need for a fast, flexible, and easy way of working with sometimes quite a big number of runtime-defined attributes (columns) for entities that are part of the business domain. For us, it proved to be the right way. The applications are running smoothly in production, the customers are happy with the speed and the achieved performance, and the developers are comfortable and creative with the intuitive API.

As the project is now open-source, it is very easy for anyone interested to have a look, form an objective opinion about it, and, why not, fork it, open a PR, and contribute.

Resources

• The open-source ORM project is here.
• The source code of the sample application is here.