使用Spring MVC修改数据库对象列表的正确方法是什么？(What is a proper way to modify a list of DB Objects using Spring MVC?)

题

使用spring MVC模型并且没有任何ORM解决方案，如何在不增加数据库查询的情况下处理大量数据库对象？

---

研究轨道

轨道1

步骤1：使用DAO SELECT对象并将它们放在带有RowMapper的（大） List 。 第2步：在Java中处理相关服务中的对象。 步骤3：在（n-big） List上循环，在DB中进行n个简单的UPDATE

查询n个对象： 1 + n

轨道2

在DAO中编写一个SQL查询，直接更新所有相应的对象而不提取它们。

查询n个对象： 1

思考

由于第2轨似乎效率更高，它似乎也直接违反了Spring MVC模型，因为我的服务将倾向于空，我的DAO将趋于扩展。

也许有一种通用的方法在数据库中的Java对象List上执行UPDATE而不增加查询？

---

简单的案例

鉴于此表：

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

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?

最满意答案

这通常由ORM层处理。

如果您正在使用Hibernate（其他ORM可用），那么我将委托给它。

如果你有自己的家庭旋转ORM层，你可以做任何你想做的事，但作为你的论点的重量，我会说：

该服务应该只知道它想要的分页。 如何实现这一点应该位于服务之下。

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.