4

We're currently developing a Spring application that uses jdbcTemplates for the repository.

We have a lot of functions that only require specific combinations of columns in our database. It would be wasteful to always fetch the entire objects. But separating the columns into their own table doesn't make sense semantically, due to other reasons.

The sheer amount of these queries means that to keep everything progressing efficiently, it would be nice to only query for the columns that I actually need. But solutions I find always end up creating a lot of specific objects just for that single purpose, which feels like it could get very high-maintenance keeping track of all those different objects. It also means hand-writing a lot of queries, and exposing the result objects through the service layer.

Using an object-oriented SQL framework doesn't really solve the problem. Writing a query that gets executed by the repository means we're just moving the issue a layer upwards.

My question is: Is there an established way or pattern to call on the repository, say what you need to be returned, read out those details (while still keeping type saftey!) without having to create countless new model classes or delivering partially filled models that could be misused because someone didn't pay attention?

7
  • Consider using a Micro-ORM. Look here for a starting point. Oct 3 '19 at 16:28
  • Have you looked in GraphQL? It allows you to pass something like a JSON schema to get back just the values you care about. graphql.org howtographql.com/graphql-java/1-getting-started
    – Graham
    Oct 3 '19 at 17:06
  • 2
    People way too often ask for "braindead patterns" - that is mostly a reason for getting close votes and downvotes. Why don't you simply ask for a concept or approach or solution (instead of expecting there must be some reusable, named, and often overengineered solution = pattern), that would IMHO be a much better question?
    – Doc Brown
    Oct 3 '19 at 17:23
  • 1
    I wonder if the maintenance cost of having bespoke result classes will be less than you think. Oct 3 '19 at 17:49
  • 1
    I guess this is a Java problem. This is trivially solved by LINQ in .NET returning an anonymous type.
    – Euphoric
    Oct 4 '19 at 7:08
9

It would be wasteful to always fetch the entire objects.

First I would question this. If your objects are well-designed and not too bloated, the performance and memory overhead of fetching them completely instead of getting them partially is often negligible for most real-world cases. The performance of SQL queries over a network are way more dependend on the number of roundtrips they cause than on the number of columns involved (often it does not even make a huge difference if a query returns 1, 10 or 30 columns, and interestingly, this is often true for the number of rows as well). Investing too much thought into getting only the columns required for certain cases is often just a form of premature optimization.

However, if you really know for sure - (and that means: after profiling!) - in your application that getting model objects partially, or querying tables partially will actually bring you a required performance benefit, then you need to design your repository interface in a way it allows to specify the columns to be queried (and construct the SQL query from that spec). Your model objects should have an extra boolean metadata field for each column which indicates if the column was loaded by the last query or not, and the repositories have to maintain this field accordingly. The "getter" method for each column then can throw an exception if another part of the program will try to access an unloaded column later.

If your data objects map to several parent-child tables and your goal is just to use partial loading for avoiding SQL joins, it is also possible to implement this concept of partially loaded objects in coarser granularity, where extra metadata fields only indicates which child tables are actually loaded and which not.

I don't know if there is an ORM or framework for your environment which supports this out-of-the-box (I guess jdbcTemplates does not), but as you asked for a non-ORM solution I guess you have to implement this concept by yourself either.

The alternative to filling your pre-existing model-objects partially is to have individual, dynamic objects generated automatically for each query. This works best if there is programming language support for this:

  • as mentioned by @Euphoric, in C# LINQ can be utilized to generate objects of anonymous types for each query, which allows to create a type-safe solution

  • also in C#, one could utilize the type dynamic for this (see, for example, how the Micro ORM Dapper supports this). This is not type-safe anymore, however.

  • in almost any major language, query results could be (lists of) instances of dictionaries, where the keys are the column names and the values are the column values of each records (either as strings, or as objects encapsulating the basic scalar datatypes). This can be implemented easily in a generic fashion. Of course, this is even less type safe, but it is a solution which is quite common in many scripting languages.

1
  • Not sure if this what you're getting at in your 3rd paragraph, but to state it in a way my brain can understand: Normalize your schema. If your schema is reasonably well normalized, the amount of bloat (in terms of extra columns) should already be pretty minimal.
    – svidgen
    Oct 8 '19 at 17:41
1

In Spring Boot, you may have a look at the the Specification that is very powerful:

My question is: Is there an established way or pattern to call on the repository, say what you need to be returned, read out those details (while still keeping type saftey!) without having to create countless new model classes or delivering partially filled models that could be misused because someone didn't pay attention?

To that specification, you may look at the Spring Rest API Search. Its not possible to get away without writing at least entity objects, but passing it through the layers, is possible with it https://www.baeldung.com/rest-api-search-language-spring-data-specifications

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.