view raw
Adrien Brunelat Adrien Brunelat - 11 months ago 35
SQL Question

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


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:
    the objects with the DAO and put them in a (big)
    with a

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

  • step 3: loop on the (n-big)
    to do n simple
    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


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
on a Java
of objects in a database without multiplying the queries?

Simple case

Given this table:

| 0 | 0 | NULL |
| 1 | 1 | NULL |
| 2 | 0 | NULL |
| 3 | 1 | NULL |
| 4 | 1 | NULL |

I have a
and a

I'd like to set
to the
for all objects that have the boolean to

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?


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.