Modified Field Detection in Pojos (Insert vs Update)

47 views
Skip to first unread message

Bernd Huber

unread,
Mar 28, 2024, 6:53:17 AM3/28/24
to jOOQ User Group
Hello guys,

this is not directly jOOQ related, but maybe someone here has already had this problem, and has some input to share.

I posted this on stackoverflow:

It is a bit related to the following jooq blog post:

I needed to write a common `AbstractDTO` class, that all the jooq-generated DTOs and my own DTOs need to extend from, because i needed "modified-fields" detection. I have described it in my stackoverflow post.

We have two cases when we send data to the database:
- Insert: send and validate everything we have in the model
- Update: only send and validate modified/touched stuff in the model.

Java does not seem to have a default mechanism to detect if a field in a pojo has ever been modified or still has its default (never touched).

Its not urgent to me, but if someone reads it and instantly knows a library or tool for something like this, give me a call ;)

Lukas Eder

unread,
Mar 28, 2024, 10:04:48 AM3/28/24
to jooq...@googlegroups.com
What keeps you from just using jOOQ's records?

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/0624283a-e9a5-40d3-8548-c00141fca706n%40googlegroups.com.

Bernd Huber

unread,
Mar 28, 2024, 11:36:48 AM3/28/24
to jOOQ User Group
Hello Lukas,

yes, i would use them, but the jOOQ's records are representing the Database-Layer and each jOOQ record represents a Table. For example:

AuthorRecord represents Table Product
BookRecord represents Table Book

In a SaaS Application we typically need to have an intermediate Layer (DTOs / Domain Transfer Objects) that are containing relationships data,
and this Layer is between the REST-Endpoints and the Database-Layer.

So for example. The Frontend sends a REST POST Request to the backend to insert data. This POST Request contains the data as JSON and
represented by the DTO, so it can contain all relevant data to insert an Author with his Books in one REST-Request instead of many separate REST-Request
and we can also guarantee Transaction-Safety (either the complete REST-Request is inserted into the database or we rollback). This JSON would look like this:

{
  "name": "George Orwell",
  books: [{
    "title": "1984"
  }]
}

Now the REST-Layer will parse this JSON and create the AuthorDTO from it. While Parsing the JSON the Setter-Methods of the AuthorDTO are called.
The AuthorDTO contains relationships to other DTOs as well.

public class AuthorDTO {
 
  private String name; 

  private String birthyear;

  private List<BookDTO> books;
}

When the DTOs are Plain Pojos, we will loose the information which of the fields have really be given by the frontend to the backend in the JSON.
After JSON-Derserialization took place we can not know it anymore.

When we later convert the DTOs to the Jooq-Records, we need to have this information present.

Also it is important to separate DTOs from the Database-Layer, because in the DTOs we can decide which fields should be hidden from the frontend
(password-field in the User-Table for example).

In my implementation i did use the Jooq Generated Pojos and the self-written DTOs extend a common base class (AbstractDTO),
so they both save the modified fields. That way in the DAO-Abstraction i can map the changed-fields correctly from the DTOs to the Jooq-Pojos,
and only send the modified fields to the database in the Update-Case. In the Insert-Case all fields are sent to the database.

That way the backend-developers hopefully do not need to fetch records from the database and send them to the frontend, only for the frontend
to change exactly one field to send the update back to the backend (Roundtrip).

Instead the Frontend can fire-and-forget sent the Update to the backend and the one field in the database is updated, without the Frontend needing
to know the complete model as it exists in the database.

Thats the benefit from having the modified-fields and pushing them trough all the layers (REST, DTO, Database)

Lukas Eder

unread,
Apr 4, 2024, 3:32:50 AM4/4/24
to jooq...@googlegroups.com
Hi Bernd,

Thanks for sharing all of this. Very interesting insight, also for future jOOQ features.

On Thu, Mar 28, 2024 at 4:36 PM 'Bernd Huber' via jOOQ User Group <jooq...@googlegroups.com> wrote:
Hello Lukas,

yes, i would use them, but the jOOQ's records are representing the Database-Layer and each jOOQ record represents a Table. For example:

AuthorRecord represents Table Product
BookRecord represents Table Book

In a SaaS Application we typically need to have an intermediate Layer (DTOs / Domain Transfer Objects) that are containing relationships data,
and this Layer is between the REST-Endpoints and the Database-Layer. [...]

Just curious. You've probably explored and rejected this option. What about using database views for this? In general, I feel like all of what you're describing is exactly what Oracle 23c introduced with their  JSON-Relational Duality Views:

An incredible feature which I plan to support in jOOQ:

At first, jOOQ support would primarily consist in generating Java POJOs for the JSON data, possibly with Jackson annotations to help third parties. And then, also explore how it could be emulated on other RDBMS.

Even without Oracle's feature, views could help with:

- Reducing the visibility of certain columns
- Reusing both jOOQ-generated POJOs and Records (at least, if you can add the constraint information to the views: https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-synthetic-objects/)

I've also thought about dirty tracking in generated POJOs in the past, though rejected it so far because of the substantial design effort if implemented on its own. POJOs are meant to be dependency free (from jOOQ), so how to implement all this logic without too much repetition? Perhaps, a AbstractDirtyTrackingPojo base class that is generated into generated code? Or generate duplicate tracking code nonetheless in each method? But in a bigger context, the feature becomes more re-usable, so let's add a new feature request to at least track the idea for the future:

Comparable requests have been rejected so far in Lombok (despite this being a prime use-case for Lombok!):
 
Also it is important to separate DTOs from the Database-Layer, because in the DTOs we can decide which fields should be hidden from the frontend
(password-field in the User-Table for example).

Again, views could help here. Also, jOOQ 3.20 will finally support hidden fields:

Password columns are exactly a use-case for this.
 
In my implementation i did use the Jooq Generated Pojos and the self-written DTOs extend a common base class (AbstractDTO),
so they both save the modified fields. That way in the DAO-Abstraction i can map the changed-fields correctly from the DTOs to the Jooq-Pojos,
and only send the modified fields to the database in the Update-Case. In the Insert-Case all fields are sent to the database.

Is it possible to share your AbstractDTO? Obviously, you might not be able to do that, but it's worth asking.

Bernd Huber

unread,
Apr 4, 2024, 1:57:20 PM4/4/24
to jOOQ User Group
Hello Lukas,

happy, that the ideas are interesting :)

i only used mariadb and mysql in my developers career.
I will take a look at those capacities of Views and how they could help with this relational datas/tree-structure that i call DTOs here.
They could really often be a good basis for the typical relationships between the database-tables.
Mostly the DTOs are almost always a mapping of the already existing relationships between database tables, and they correspond with what Hibernate does with the Relationshop-Annotations (@OneToMany, @ManyToOne, ...)
So i think your approach to already let the definition be made on the database-level is interesting!
I would find it awesome if such schema-defined JSON-Tree Structures would result in Jooq generated Code for those, that would already contain the relationships!
I wonder if this would support all cases (READ,  INSERT, UPDATE, DELETE) on elements of this JSON, but i guess it would be easy, as the database only needed to save the JSON then.

---

thanks for the other links, i will check them out!

---

sure i can share the AbstractDTO. 

Its like this:
- The Core-Project contains the AbstractDTO
- The Database-Project contains the Jooq-Codegen and depends on the Core-Project only for this AbstractDTO (needs nothing else)
- The Product-Project contains the Business-Logic and depends on the generated Jooq-Code of the Database-Project, and also on the Core-Project for Basic-Stuff (DAO-Abstraction, ...)

Bernd Huber

unread,
Apr 4, 2024, 2:02:31 PM4/4/24
to jOOQ User Group
its also relevant to notice that when writing Unit/Integration Tests on the typical SaaS REST-Endpoints we also should be able to let the DTOs be Serialized to JSON in a way, so that the JSON only contains the dirty data.
That helps to also use the DTOs within Unit-Tests. I currently need a Serializer/Deserializer for this, that i use in my Tests:
Reply all
Reply to author
Forward
0 new messages