Intercept all Database call in Java

Question

I want to intercept insert query in my database and want to change its insert value

for example, if the user inserts a value name = amitrai in a database, I want to change the value from amitrai to &^&WQWSAKJSJA and then store.

overall I want intercept to execute the query in JDBC connection.

@GauravJeswani can you explain in details how I modify query string using AOP in JDBC — Amit Rai, Nov 19 '19 at 06:23
https://stackoverflow.com/questions/49046265/difference-between-jpas-attributeconverter-and-hibernates-columntransformer-in — Akash Shah, Nov 19 '19 at 06:30
If you want to intercept ALL DB operations for JDBC, you can try [p6spy](https://github.com/p6spy/p6spy) to customize this feature. — LHCHIN, Nov 19 '19 at 07:05
@LHCHIN i am not supposed to change JDBC URL , in my project i have inpertcept without changing anything i have to put hook in database — Amit Rai, Nov 19 '19 at 07:32
@SternK in my job we use oracle, MySQL and SQL-server first I want to start with Oracle — Amit Rai, Nov 20 '19 at 17:15
what do you expect from the bounty? what about the suggested trigger solution? if you want specific trigger give more details on table — Ori Marko, Nov 27 '19 at 11:34

score 3 · Answer 1 · answered Nov 26 '19 at 10:03

Doing it in the database, with a trigger.

In my opinion, the best way to implement this is to use a trigger directly in the database. That's exactly what they have been made for. They will guarantee that every client trying to insert / update / merge the user.name value will pass through this logic. There will be no clever Perl script by some other developer who is not aware of your client side interception.

All the database products you've mentioned in the comments support triggers. The drawback is, of course, you have to repeat your encoding / hashing logic in as many dialects as you want to support.

Doing it in the client, using a JDBC proxy

If you have to implement this logic in the client for some reason, then JDBC is a good layer to do this. You could, of course, implement this on a higher layer (e.g. in Hibernate), but then again, you will risk some Java client side logic bypassing this encoding.

On the JDBC layer, you can at least "reasonably" guarantee that every Java client will execute this logic.

One way to solve this is using something like jOOQ's various connection proxies, including the MockConnection, which allows for intercepting most JDBC calls using a single method (with some limitations), the ParsingConnection, which allows for parsing the SQL string that is being passed to JDBC, and then transform. If you want to produce new SQL logic, you'd be using the VisitListener to transform the SQL to something else. In your case, you're only replacing a bind variable, so you can probably do this more easily.

Disclaimer: I work for the company behind jOOQ. Possibly, there are other solutions out there that support a similar behaviour, the essence being proxying (others have mentioned p6spy for this) and parsing, to make sure you're only replacing the values you actually want to replace. Like jOOQ, such solutions should not force you to actually use jOOQ in your client logic, but to intercept any type of SQL you're executing on the JDBC layer.

A word of caution

While the above is possible to some extent, it may be complicated, and there may still be things you're overlooking (e.g. the MERGE statement, or the INSERT .. ON DUPLICATE KEY UPDATE statement, etc.). A trigger is the best way to solve this problem.

score 1 · Accepted Answer · answered Nov 27 '19 at 13:04

Use Trigger on insert as @Mureinik suggested

for example for table yourtable insert:

CREATE OR REPLACE TRIGGER NameChange
    BEFORE INSERT
    ON yourtable
    FOR EACH ROW
BEGIN
    IF :new.name = 'amitrai'
    THEN
        :new.name := '&^&WQWSAKJSJA';
    END IF;
END;

Intercept all Database call in Java

2 Answers2

Doing it in the database, with a trigger.

Doing it in the client, using a JDBC proxy

A word of caution