使用@query和@modifying的Spring data JPA PSQLException

Question

使用spring-boot-starter-data-jpa（版本2.0.4.release）和PostgreSQL（版本9.5.10)，我一直得到psqlexception：查询没有返回任何结果，尽管我确实使用了@modifying（这里很多人建议的）。按照一些人的建议添加@transactional也没有起到什么作用。分析StackTrace可以看到调用了org.postgresql.jdbc.pgPreparedStatement.executeQuery()，而应该调用org.postgresql.jdbc.pgPreparedStatement.executeUpdate()。我能做什么？

@Repository
public interface Rank extends JpaRepository<RankArticle, String> {

@Modifying
@Query(value = "UPDATE rank SET id=calc.id, shop_id=calc.shop_id, " +
                "score=calc.score, rank = calc.rank FROM " +
                    "(SELECT id, shop_id, SUM(sales) AS score, CAST(NULL AS INT) AS rank " +
                    "FROM " +
                        "(SELECT id, sales, shop_id FROM daily_pod " +
                        "WHERE shop_id = :shopId " +
                        "UNION " +
                        "SELECT id, sales, shop_id FROM weekly_pod " +
                        "WHERE shop_id = :shopId " +
                        "UNION " +
                        "SELECT id, sales, shop_id FROM end_pod " +
                        "WHERE shop_id = :shopId) " +
                    "AS pods " +
                    "GROUP BY id, shop_id ORDER BY score DESC limit :limit) as calc;"
        , nativeQuery = true)
void calcScore(@Param("shopId") Integer shopId,
               @Param("limit") Integer limit);

编辑：添加完整堆栈-跟踪：

[ERROR] 2018-09-20 17:49:10,503 [SimpleAsyncTaskExecutor-1] [d.t.p.v.backend.VerkaufsrangConfig] - Konnte AMQP-Nachricht nicht

Answer 1

通过将语句更改为INSERT into...ON CONFLICT（...）,我设法使其工作请更新一个：

@Transactional
public interface Rank extends CrudRepository<RankArticle, Identity> {

    @Modifying
    @Query(value = "INSERT INTO rank" +
                        "(SELECT id, shop_id, SUM(sales) AS score, CAST(NULL AS INT) AS rank " +
                        "FROM " +
                            "(SELECT id, shop_id, sales FROM daily_pod " +
                            "WHERE shop_id = :shopId " +
                            "UNION " +
                            "SELECT id, shop_id, sales FROM weekly_pod " +
                            "WHERE shop_id = :shopId " +
                            "UNION " +
                            "SELECT id, shop_id, sales FROM end_pod " +
                            "WHERE shop_id = :shopId) " +
                        "AS pods " +
                        "GROUP BY id, shop_id ORDER BY score DESC LIMIT :limit) " +
                "ON CONFLICT (id) DO UPDATE " +
                "SET shop_id = EXCLUDED.shop_id, score = EXCLUDED.score, " +
                    "rank_count = EXCLUDED.rank_count;"
        , nativeQuery = true)
void calcScore(@Param("shopId") Integer shopId,
               @Param("limit") Integer limit);

}