by Razvan Popian, Horațiu Dan

Abstract

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 – SQL generation, lazy loading etc.

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

asentinel-orm has been successfully used in several Tangoe products since 2015 and has been continually improved as required by the business needs. In the summer of 2024, it has officially become 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 in order to outline several ORM key features:

• simple configuration
• straight forward domain entity modelling 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 OrmOperationsis required.

As stated in the JavaDoc, thisis 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)); } 

OrmOperationshas 2 super interfaces:

• SqlBuilderFactory – creates SqlBuilderinstances 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, by 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 NewEntityDetectorexists, which is used for determining whether an entity is a new one. By default, the SimpleNewEntityDetectoris used.

All queries generated by the ORM are executed using an SqlQueryTemplateinstance, which further needs a Spring JdbcOperations / JdbcTemplateto work. Eventually, all queries reach the good old JdbcTemplate through which they are executed while participating in Spring transactions, just as any JdbcTemplatedirect 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 a 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 andcar 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, then
• the entities are loaded back described by both the initial and the runtime defined attributes

The initial entities are mapped over the below database tables.

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 in order 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
• the @Child annotated members are configured to be lazily loaded
• the type table column is 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 classstores 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 DynamicColumnEntityinterface.

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 DynamicColumnmaps runtime defined attributes to their corresponding columns in a similar manner the @Columnannotation 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 CarManufacturerentity (for simplicity, only int and varchartypes are supported).

For each such name – type pair, a DML command is executed in order for the new columns to be added to the CarManufacturerdatabase 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 DynamicColumnreference implementation.

Once all attributes are defined, two car manufacturers are added in 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 OrmOperationsupdate() 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 are worth doing regarding the method defined above.

The OrmOperationsnewSqlBuilder()method creates a SqlBuilder instance and as the name suggests, this can be used to generate SQL queries. The SqlBuilderselect()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 EntityDescriptorNodeCallbackinstances (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 DynamicColumnsEntityNodeCallbackneeds to be passed. Together with it, an AutoEagerLoaderone is provided, so that the ORM understands to eagerly load the list of CarModelsassociated 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.

Conclusions

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 which are generated directly by the ORM.

It wasn’t rare when we had the chance to discuss in “the community” about the asentinel-orm, about the reasons we had to develop such a tool. Usually, at the 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 proves to be the right way, the applications are running smoothly in production, the customers are happy with the speed and the achieved performance, the developers are comfortable and creative with the intuitive API.

As the project is open-source now, 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

1. The open-source ORM project is here.
2. The source code of the sample application is here.
3. The picture was taken in a hand-made decorations shop, in Skiathos, Greece, in the summer of 2024.