Adrien Brunelat Adrien Brunelat - 5 months ago 11
SQL Question

What is a proper way to modify a list of DB Objects using Spring MVC?

Question



Using the spring MVC model and without any ORM solution, how am I supposed to treat a large list of database objects without multiplying the database queries?




Research tracks



Track 1




  • Step 1:
    SELECT
    the objects with the DAO and put them in a (big)
    List
    with a
    RowMapper
    .

  • Step 2: deal with my objects within the associated service in Java.

  • step 3: loop on the (n-big)
    List
    to do n simple
    UPDATE
    in the DB



Queries for n objects: 1 + n

Track 2



Write a SQL query in the DAO that directly updates all the corresponding objects without extracting them.

Queries for n objects: 1

Thoughts



As track 2 seems far more efficient, it also seems to straightly go against the Spring MVC model as my services will tend to empty and my DAO will tend to expand.

Maybe there is a generic way to do an
UPDATE
on a Java
List
of objects in a database without multiplying the queries?




Simple case



Given this table:

+----+---------+------+
| MY_OBJECT |
+----+---------+------+
| ID | BOOLEAN | DATE |
+----+---------+------+
| 0 | 0 | NULL |
| 1 | 1 | NULL |
| 2 | 0 | NULL |
| 3 | 1 | NULL |
| 4 | 1 | NULL |
+----+---------+------+


I have a
MyObjectDao
and a
MyObjectService
.

I'd like to set
DATE
to the
SYSDATE()
for all objects that have the boolean to
true
.

Should it be full DAO with a query or should the DAO retrieve the objects, the service edit them and then the DAO update them?

Answer

I did some researches and put a lot of thoughts into this question. What I deduced from my work is that in the majority of cases the process should be:

  • Pull the data from the DB with the DAO in a generic way
  • Edit the objects the way you need it with the service / business layer
  • Transmit the objects to the DAO to do a generic batchUpdate

The key element I was missing is the batchUpdate. Without it, updating a big List of elements would take minutes which is unacceptable. The big difference is not the amount of queries executed but how they are transmitted to the DB, avoiding multiple usages of the network layer in the batch update case.