2

Time ago my query worked fine for a single row update. Now I have to modify this query to update multiple row. The query is native and use postgresql and postgis.

The old query:

@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);

In the new query I added some parameter in @Modifying as stated here:

@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);

But sadly this changed doesn't have any effect. (If I lunch the query from postgresql it run perfectly). How can I solve?

Edit: I added the query, but it works on postgresql. The only difference is that the old version: WHERE id_dis=?1 target a single row, and the new WHERE id_dis_aree=(select id_dis_aree from dis_area where id_dis=?1) target multiple rows. The couple id_dis_aree and id_dis is a primary key. Two or more records could have same id_dis_aree and different id_dis. So with the second query, I fetch id_dis_aree from id_dis, to affect more rows.

Edit2: I did 2 tests:

  1. substituting the last subselect directly with a fixed wired id value: WHERE id_dis_aree=123456 In this way it works. This could be a solution workaround, fetching id_dis_aree and after calling the query.

  2. substituting the last subselect with this: WHERE id_dis_aree IN (select id_dis_aree from dis_area where id_dis=?1) Doesn't work. (Memo: the subselect return always a single value).

Accollativo
  • 1,537
  • 4
  • 32
  • 56
  • I think you need to share your query. Well if it's too long, break it to small pieces. If it worked in the past and now it stopped working .. something wrong with the query – Happy Coder Nov 27 '19 at 16:39
  • Ok, I will post it tomorrow when I'll go back to work but the query works fine on postgresql (I changed just the where clause to catch more rows) and returns more updated rows. But when it runs on Spring Boot it returns 0 updated rows and no errors. – Accollativo Nov 27 '19 at 20:12
  • 1
    Does `select id_dis_aree from dis_area where id_dis=?1` result in multiple results? If so the where should read `WHERE id_dis_agree IN (select id_dis_aree from dis_area where id_dis=?1)` you should use `IN` instead of `=` then. – M. Deinum Nov 28 '19 at 08:03
  • @M.Deinum no, a single result. – Accollativo Nov 28 '19 at 08:19
  • If it is single then how can it target multiple rows? – M. Deinum Nov 28 '19 at 08:22
  • The couple `id_dis_aree` and `id_dis` is a primary key. Two or more records could have same `id_dis_aree` and different `id_dis`. So with the second query, I fetch `id_dis_aree` from `id_dis`, to affect more rows. – Accollativo Nov 28 '19 at 08:26
  • 1
    @Accollativo - did you try using M. Deinum's suggestion of using IN ? Is the error solved now ? – S B Dec 02 '19 at 03:02
  • @SB No, but I found something. I edited the post. – Accollativo Dec 02 '19 at 10:33
  • There seem two things based on your edit and the statement "Two or more records could have same id_dis_aree and different id_dis" - 1. So, different id_dis could return same id_dis_aree. But you are passing id_dis as a parameter which will result in corresponding id_dis_aree. Potentially, this is why you get single record in the sub-query. 2. You are updating based on selected values, but dis_area is disjointed from the tmp. Could you add dis_area to the joins for tmp and get the values for update ? – S B Dec 02 '19 at 10:53
  • @SB there is no need for join, it's not related – Accollativo Dec 02 '19 at 13:35
  • multi-thread or single-thread ? Try running this method only in a JUnit. If it works, then the flushing etc has side-effects. – Edward Aung Dec 05 '19 at 23:22
  • Also, instead of using a very complex query, try to update multiple records of a 3 rows table. If it works, the problem is something else. – Edward Aung Dec 05 '19 at 23:23
  • It's single thread. As I said in the edit2, I find a workaround and solved in that way, now I'm just wondering if it exists a better solution and why the first versions doesn't work. – Accollativo Dec 06 '19 at 07:40

1 Answers1

0

I didn't find a true solution, but just a workaround:

I fetched the value from the subquery: (select id_dis_aree from dis_area where id_dis=?1) in the @Service by calling disAreeRepository.findByIdIdDis(idDis).getId().getIdDisAree()

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

And then passed it to @Repository native query as third parameter:

@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);
Accollativo
  • 1,537
  • 4
  • 32
  • 56