Do you remember the first time you used an ORM (object/relational mapping) tool? Do you remember how cool it was to make the object structure match your database design? Do remember how easy it was to retrieve data and make updates using ORM?

Yeah, me too.

In the end, you could have a single object which a bunch of child objects, like this:

public class Account { private long id; ... some more attributes go here ... private List<Name> names; // houses values for doing business as private List<Location> locations; // different account locations private List<Contact> contacts; // contacts at the account private List<Order> orders; // orders for this account private List<Staff> supportStaff; // support structure for the account }





Perhaps, each of the Name , Location , Contact , Order , and Staff values include child lists/sets/maps as well.

The very cool thing was that if I wrote a query to retrieve an Account , I could have access to everything. All by this one simple ORM-based query. If I fetched a list of accounts, I had a majority of my database being returned in a List<Account> from a single request. As an example:

Account account = accountService.getAccountById(id); account.getNames() // allows me to examine Name attributes tied to this account





At the time, the ORM designers implemented a lazy load approach where the database would only retrieve when a particular child object when needed. This worked great in those session-based applications, which allowed the server hosting the ORM technology to know when the requests were made. In those cases, the client session informed the server to "get the contacts for this account" and the results would be provided.

Challenges With RESTful Application Design

With RESTful (REpresentational “State” Transfer) application design, the requests are stateless. There isn't a session on the server that can be listening for that request to provide more information. So, the problem with this approach is that the request will need to fetch the Account object and all of the children objects with the original request. This not only yields a large payload but an expensive hit on the database. For an application servicing tens of thousands of clients, this could become a performance issue as well.

I've gotten in the habit of providing lean DTOs to clients utilizing my RESTful APIs. As much as possible, I try to avoid having objects with child objects — especially when every request does not find value in the child objects. Instead of providing that child object, I return the necessary information (like a key) so that another RESTful URI can be called to retrieve the child information — in only those cases that need it.

Legacy Integration Concerns

At this point, the realization has been made that the Account object, while it was great for stateful applications, doesn't really work well with this new RESTful API we are building. Simple logic would say "then, let’s just remove all of those child objects from the Account object and move on."

This is can be an ideal approach ... except when that model is still required — perhaps by the legacy application that isn't due to be decommissioned anytime soon. Another common challenge is with existing integrations which may also require the existing Account object to remain intact. One other common challenge can be in the way of reporting aspects. Those solutions might be utilizing the legacy Account object and require quite a bit of rework to conform to the new design.

In short, additional work is often required to transform, or flatten, a structure like Account object. This leads to prioritization and or budgeting. In the end, the likelihood of being able to redo the Account object structure is not a likely solution.

Example Use Case

As an example, let's assume for the RESTful API, performance is a concern. In the example provided above, we need the following information for the response from a RESTful API:

Account.id

Account.active — boolean to indicate if the account is in an active state

Account.names.legalName — where Name.type = LEGAL_NAME

Account.contacts.fullName — where Contact.type = SALES

Account.contacts.fullName — where Contact.type = ENGINEERING (can be null)

Account.orders.dateReceived — for the most recent order

Account.orders.shipDate — for the most recent order (can be null)

Account.supportStaff.fullName — where Staff.type = ACCOUNT_EXEC

Account.supportStaff.fullName — where Staff.type = INSIDE_SALES

The expectation is that a request from the Account API for a given account will produce the following JSON response:

{ "id" : 0, "active" : true, "nameLegalId" : 0, "nameLegalName" : "string", "contactSalesId" : 0, "contactSalesName" : "string", "contactEngineeringId" : 0, "contactEngineeringName" : "string", "orderMostRecentId" : 0, "orderMostRecentReceived" : 0, "orderMostRecentShipDate" : 0, "supportAccountExecId" : 0, "supportAccountExecName" : "string", "supportInsideSalesId" : 0, "supportInsideSalesName" : "string" }





In this fictional example, the id and active attributes are members of the Account object and none of the child objects from the List<Location> locations are required. For the remainder of the payload, in addition to the required fields, the id of the child object is included, in order to assist with retrieving additional details or providing PATCH operations of the data.

Introducing the AccountLite Object

In order to provide a performant solution, an AccountLite object is introduced, as follows:

package com.gitlab.johnjvester.kitchensink.entities; @Entity @JsonIgnoreProperties(ignoreUnknown = true) @Data @AllArgsConstructor public class AccountLite { @Id private long id; private boolean active; private long nameLegalId; private String nameLegalName; private long contactSalesId; private String contactSalesName; private Long contactEngineeringId; private String contactEngineeringName; private Long orderMostRecentId; private Calendar orderMostRecentReceived; private Calendar orderMostRecentShipDate; private long supportAccountExecId; private String supportAccountExecName; private long supportInsideSalesId; private String supportInsideSalesName; }





The new object includes the @Entity annotation. Via Lombok, the @Data and @AllArgsConstructor will assist with avoiding any boilerplate coding.

In the DataConfig class or the Spring Boot project, the @EntityScan() basePackages will include com.gitlab.johnjvester.kitchensink.entities so that the AccountLite object is recognized.

Adding Native Query and SQL Result Set Mapping

With JPA, it is easy to utilize a native query using @NamedNativeQuery and a result set converter using @SqlResultSetMapping to avoid having to take this manual-mapping approach:

List<Object[]> results = entityManager.someQueryMethod() // ... your query goes here if (CollectionUtils.isNotEmpty(results)) { List<AccountLite> returnList = new ArrayList<>(); AccountLite accountLite; for (Object[] object : results) { Long id = (Long) object[0]; boolean active = ((int) object[1]) == 1; Long nameLegalId = (Long) object[2]; String nameLegalName = (String) object[3]; // ... continue with this manual mapping exercise ... Long supportInsideSalesId = (Long) object[13]; String supportInsideSalesName = (String) object[14]; accountLite = new AccountLite(id, active, nameLegalId, nameLegalName, ... , supportInsideSalesId, supportInsideSalesName); returnList.add(accountLite); } return returnList; } return null;





In the example above, the generic result set is manually mapped to the expected data values. Each time this pattern is utilized, there is the potential for an error, which might not always be caught during standard testing.

Instead of taking this approach, JPA allows the @NamedNativeQuery to be added to the AccountLite class file:

@NamedNativeQuery(name = "AccountLite.getAccounts", query = "SELECT your query goes here ", resultSetMapping = "GetAccountsResult")





Then, the mapping can be created via a simple annotation, as shown below:

@SqlResultSetMapping( name = "GetAccountsResult", classes = { @ConstructorResult( targetClass = com.gitlab.johnjvester.kitchensink.AccountLite.class, columns = { @ColumnResult(name = "id", type = Long.class), @ColumnResult(name = "active", type = Boolean.class), @ColumnResult(name = "nameLegalId", type = Long.class), @ColumnResult(name = "nameLegalName", type = String.class), // ... continue with the rest of the columns from the result set ... @ColumnResult(name = "supportInsideSalesId", type = Long.class), @ColumnResult(name = "supportInsideSalesName", type = String.class) } ) } )





Keep in mind, the mapping above has been simplified to only demonstrate six of the attributes for the AccountLite object.

Finally, we can introduce an AccountLiteRepository interface, which leverages the JPA Repository:

public interface AccountLiteRepository extends JpaRepository<AccountLite, Long> { List<AccountLite> getAccounts(); }





The AccountLiteRepository interface has only one method, the getAccounts() method, which will automatically call the @NamedNativeQuery (in the AccountLite.class), which will use the @SqlResultSetMapping referenced. The entire process is completed using annotations, which are far easier to create, maintain and understand.

Actual Results

I recently employed this approach on a project for a client. Since we did not have the ability to touch the existing object and performance was a key concern, the time taken to build a Lite object paid off quite nicely for the feature team.

By employing the AccountLite object and avoiding the use of the existing Account object and all of the necessary child objects, we were able to recognize a 590 percent improvement for a URI that is heavily accessed by the tens of thousands of clients accessing the API on nearly a 24 x 7 basis. Where possible, we have continued to utilize this approach, since the supportability of this approach is in line with the superior performance obtained.

Conclusion

This article is certainly not intended to bash any ORM tooling, as I feel they play an important role in application design. Instead, this is intended to provide an alternative approach to retrieve data that is performant and easy to maintain, for those cases where updating the existing object is not possible for a number of different reasons.

Have a really great day!