多行的Spring data jpa更新不起作用

Question

前一段时间，我的查询对于单行更新很有效。现在我必须修改这个查询来更新多行。查询是本机的，使用postgresql和postgis。

老问题是：

@Modifying
@Transactional
@Query(value = "WITH tmp AS (SELECT ST_Difference( (SELECT ST_Buffer(ST_Union(ST_Buffer(a.area\\:\\:geometry, 0.002)), -0.002) \n" + 
        "   FROM mydb.city_area a, mydb.dis_city d, mydb.city c \n" + 
        "   where c.id_city=d.id_city and d.id_dis=?1  \n" + 
        "   and c.cod_city=a.cod_city), \n" + 
        "                     (ST_Difference( ST_GeomFromGeoJSON(?2)\\:\\:geometry, (SELECT ST_Union(a.area\\:\\:geometry) \n" + 
        "   FROM mydb.city_area a, mydb.dis_city d, mydb.city c \n" + 
        "   where c.id_city=d.id_city and d.id_dis=?1  \n" + 
        "   and c.cod_city=a.cod_city and c.full_area=true)                          \n" + 
        "                       ))                                      \n" + 
        "                   ) AS final_area)\n" + 
        "UPDATE mydb.dis_area SET new_area=(SELECT final_area FROM tmp), " +
        "id_type=3 " +
        "WHERE id_dis=?1 ",
        nativeQuery = true)
Integer insertShape(Integer id, String shapeGeoJson);

在新的查询中，我在@Modifying中添加了一些参数，如下所述：

@Modifying(flushAutomatically = true, clearAutomatically = true)
@Transactional
@Query(value = "WITH tmp AS (SELECT ST_Difference( (SELECT ST_Buffer(ST_Union(ST_Buffer(a.area\\:\\:geometry, 0.002)), -0.002) \n" + 
        "   FROM mydb.city_area a, mydb.dis_city d, mydb.city c \n" + 
        "   where c.id_city=d.id_city and d.id_dis=?1  \n" + 
        "   and c.cod_city=a.cod_city), \n" + 
        "                     (ST_Difference( ST_GeomFromGeoJSON(?2)\\:\\:geometry, (SELECT ST_Union(a.area\\:\\:geometry) \n" + 
        "   FROM mydb.city_area a, mydb.dis_city d, mydb.city c \n" + 
        "   where c.id_city=d.id_city and d.id_dis=?1  \n" + 
        "   and c.cod_city=a.cod_city and c.full_area=true)                          \n" + 
        "                       ))                                      \n" + 
        "                   ) AS final_area)\n" + 
        "UPDATE mydb.dis_area SET new_area=(SELECT final_area FROM tmp), " +
        "id_type=3 " +
        "WHERE id_dis_aree=(select id_dis_aree from dis_area where id_dis=?1) ",
        nativeQuery = true)
Integer insertShape(Integer id, String shapeGeoJson);

但可悲的是，这种改变没有任何效果。（如果我午餐从postgresql查询它运行完美）。我该怎么解决？

编辑：我添加了查询，但它适用于postgresql。唯一的区别是旧版本：WHEREid_dis=？1目标是一行，新的WHEREid_dis_aree=（选择id_dis_areedis_areaid_dis=？1）以多行为目标。id_dis_aree和id_dis是主键。两个或多个记录可能具有相同的id_dis_aree和不同的id_dis。因此，对于第二个查询，我从id_dis获取id_dis_aree，以影响更多的行。

编辑2：我做了2个测试：

>

将最后一个子选择直接替换为固定的有线id值：其中id_dis_aree=123456，以这种方式工作。这可能是一种解决方法，在调用查询后获取id\u dis\u aree。

将最后一个子选择替换为：WHEREid_dis_areeIN（选择id_dis_aree从dis_areaid_dis=？1）不起作用。（备忘录：子选择总是返回一个值）。

Answer 1

我没有找到真正的解决方案，只是一个变通方法：

@Transactional
public Integer insertDis(Integer idDis, String shapeGeoJson) {
    return disAreeRepository.
            insertShape(
                    idDis,
                    shapeGeoJson,
                    disAreeRepository.findByIdIdDis(idDis).getId().getIdDisAree() 
                    );
}

然后将其作为第三个参数传递给@Repository本机查询：

@Modifying(flushAutomatically = true, clearAutomatically = true)
@Query(value = "WITH tmp AS (SELECT ST_Difference( (SELECT ST_Buffer(ST_Union(ST_Buffer(a.area\\:\\:geometry, 0.002)), -0.002) \n" + 
        "   FROM mydb.city_area a, mydb.dis_city d, mydb.city c \n" + 
        "   where c.id_city=d.id_city and d.id_dis=?1  \n" + 
        "   and c.cod_city=a.cod_city), \n" + 
        "                     (ST_Difference( ST_GeomFromGeoJSON(?2)\\:\\:geometry, (SELECT ST_Union(a.area\\:\\:geometry) \n" + 
        "   FROM mydb.city_area a, mydb.dis_city d, mydb.city c \n" + 
        "   where c.id_city=d.id_city and d.id_dis=?1  \n" + 
        "   and c.cod_city=a.cod_city and c.full_area=true)                          \n" + 
        "                       ))                                      \n" + 
        "                   ) AS final_area)\n" + 
        "UPDATE mydb.dis_area SET new_area=(SELECT final_area FROM tmp), " +
        "id_type=3 " +
        "WHERE id_dis_aree=?3 ",
        nativeQuery = true)
Integer insertShape(Integer id, String shapeGeoJson, Integer idDisAree);