Jose Yamut, Jr

Fond of +rw but seldom has time 4 both. 😎😋🤖

Programming work work

Simple SQL Query Builder

Did a simple, customized SQL query builder that’s only specific for UPDATE statements some time early last year. Thought I’d just write it down here. It was a smaller part of a bigger application. It wasn’t required for all update cases, but a number of them did. I’ve mostly re-written it from memory. It should be fairly close. It works as how I remembered it. Of course, this one is sort of like only a “demo“.

Here is the link to the code in GitHub.

https://github.com/junyamut/SimpleCustomUpdateSQLQuery

The “update query builder” needs to be explicitly set to have an implementation for those tables you want it to work with. It is sort of reusable, but for each table it has to be defined explicitly in code (such as the primary key, unique keys for example). It is not configured to work for all tables out of the box, on purpose. I also have it using the Java Reflection API.

As I said, it is still nothing complicated. It served its purpose well enough. The goal was for it to be able to generate a basic SQL update query statement. As simple as this one:

UPDATE food_store.food_category SET group_id=2,updated_by='Jose Yamut',is_active='N' WHERE food_category_id=1

JPA’s default method to update somehow always included all fields, even if the value was not set in the object. Which is not the behavior that was desired for the application. The application should only update the fields specified by the user. Don’t let it touch anything else if not required or asked explicitly, like those mandatory and auto-modified fields (i.e., fields with timestamps of last update) which the application should take care of itself.

The initial implementations were to get the data row to be updated from the source, then merge and compare it with the one that is supposed to replace it. It was a messy ordeal. Also, it required 2 calls to the database.

I think I must have searched for solutions to this dilemma for a while. This scenario, I believe, is nothing unique. In fact I think it’s very common. But for some reason I could not find something that fits the bill. Then maybe my research was not exhaustive enough.

Thus this implementation was born. Since the logic is simple-ish, the tables and fields that needs updating are already known… then why not?

Most of the relevant implementation code are located in this package:

package xyz.joseyamut.updatequerybuilder.repository.builders;

The dummy database/table schema is in the resources folder, aptly named schema.sql. Spring should create that table when it does not exist. The configuration is also set to create the database when it is not there too.

So if you folks know anything that makes this any simpler, kindly let me know. Like I don’t have to do something like this anymore, or reinvent the wheel, and is probably more robust and well-maintained solution. Suggestions on how to improve this work are also welcome. 😁

Similar Posts:

Did a simple, customized SQL query builder that’s only specific for UPDATE statements some time early last year. Thought I’d just write it down here. It was a smaller part of a bigger application. It wasn’t required for all update cases, but a number of them did. I’ve mostly re-written it from memory. It should…

Tags: