java - Hibernate无法执行 native 查询SqlExceptionHelper : Invalid value "7" for parameter "parameterIndex"

Question

我有 native SQL 请求，它由两个组成

• 插入 - 选择(带有子请求)
• 选择

参数是三个字符串元素的列表，并传递给:

@Modifying
@Query(value = " "
//close
+ "INSERT INTO status (is_open,advertisement, updated) "
+ "SELECT "
+ " false, to_close.id, NOW() "
+ "FROM( "
+ " SELECT " 
+ "             ad.id as id, " 
+ "             ad.native_id as native_id, " 
+ "             sc_newest.updated as updated, " 
+ "             status.is_open as is_open " 
+ " FROM advertisement AS ad " 
+ " LEFT JOIN " 
+ "         (SELECT "
+ "             MAX(sc.UPDATED) AS updated, "
+ "             sc.ADVERTISEMENT "
+ "         FROM ADVERTISEMENT AS ad "
+ "         LEFT JOIN STATUS AS sc "
+ "             ON  ad.ID = sc.ADVERTISEMENT "
+ "         WHERE   ad.NATIVE_ID NOT IN  :shown_ads "
+ "         GROUP BY sc.advertisement) AS sc_newest "
+ "     ON ad.id = sc_newest.advertisement "
+ " LEFT JOIN status AS status "
+ "     ON ad.id = status.advertisement AND status.updated = sc_newest.updated "
+ " WHERE "
+ "     status.is_open = true " 
+ "     AND ad.native_id NOT IN :shown_ads) AS to_close ; "
+ ""
// get stored previously 
+ " SELECT "
+ " ADVERTISEMENT.NATIVE_ID "
+ "FROM ADVERTISEMENT "
+ " WHERE "
+ " ADVERTISEMENT.NATIVE_ID IN :shown_ads "
        , nativeQuery = true)
public Set<String> closeReopenFindStored(@Param("shown_ads") List<String> shownNaturalIDs);

这里是日志

2017-08-03 07:44:12.353 DEBUG 16162 --- [ main] org.hibernate.SQL :

INSERT INTO status (is_open,advertisement, updated) SELECT false, to_close.id, NOW() FROM( SELECT ad.id as id, ad.native_id as native_id, sc_newest.updated as updated, status.is_open as is_open FROM advertisement AS ad LEFT JOIN (SELECT MAX(sc.UPDATED) AS updated, sc.ADVERTISEMENT FROM ADVERTISEMENT AS ad LEFT JOIN STATUS AS sc ON ad.ID = sc.ADVERTISEMENT WHERE ad.NATIVE_ID NOT IN (?, ?, ?) GROUP BY sc.advertisement) AS sc_newest ON ad.id = sc_newest.advertisement LEFT JOIN status AS status ON ad.id = status.advertisement AND status.updated = sc_newest.updated WHERE status.is_open = true AND ad.native_id NOT IN (?, ?, ?)) AS to_close ; SELECT ADVERTISEMENT.NATIVE_ID FROM ADVERTISEMENT WHERE ADVERTISEMENT.NATIVE_ID IN (?, ?, ?)

2017-08-03 07:44:12.429 TRACE 16162 --- [ main] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [VARCHAR] - [ad1 number] 2017-08-03 07:44:12.430 TRACE 16162 --- [
main] o.h.type.descriptor.sql.BasicBinder : binding parameter [4] as [VARCHAR] - [ad1 number] 2017-08-03 07:44:12.431 TRACE 16162 --- [ main] o.h.type.descriptor.sql.BasicBinder : binding parameter [7] as [VARCHAR] - [ad1 number]

2017-08-03 07:44:12.432 WARN 16162 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 90008, SQLState: 90008 2017-08-03 07:44:12.433 ERROR 16162 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : Invalid value "7" for parameter "parameterIndex" [90008-195]

为什么这不起作用？

更新 - SQL 我从 SQL 客户端运行良好:

INSERT INTO status (is_open,advertisement, updated) 
SELECT 
    false, to_close.id, NOW() 
FROM( 
    SELECT 
                ad.id as id, 
                ad.native_id as native_id, 
                sc_newest.updated as updated, 
                status.is_open as is_open 
    FROM advertisement AS ad 
    LEFT JOIN 
            (SELECT 
                MAX(sc.UPDATED) AS updated, 
                sc.ADVERTISEMENT 
            FROM ADVERTISEMENT AS ad 
            LEFT JOIN STATUS AS sc 
                ON  ad.ID = sc.ADVERTISEMENT 
            WHERE   ad.NATIVE_ID NOT IN  ('ad1 number','ad2 number', 'new ad') 
            GROUP BY sc.advertisement) AS sc_newest 
        ON ad.id = sc_newest.advertisement 
    LEFT JOIN status AS status 
        ON ad.id = status.advertisement AND status.updated = sc_newest.updated 
    WHERE 
        status.is_open = true 
        AND ad.native_id NOT IN ('ad1 number','ad2 number', 'new ad')) AS to_close ; 


SELECT 
    ADVERTISEMENT.ID AS ad_id,
    ADVERTISEMENT.NATIVE_ID
FROM ADVERTISEMENT
WHERE
    ADVERTISEMENT.NATIVE_ID IN ('ad1 number','ad2 number', 'new ad')

Answer 1

我已经检查了你的代码，并找到了你的问题。问题是您无法运行 2 个查询，即 insert and select使用相同的方法进行查询，因为插入查询和选择查询返回类型不同，因此您必须创建两种不同的方法，第一个用于保存数据，第二个用于从数据库中选择数据。您必须像下面这样修改代码

AdvertisementRepository.java

//  @Modifying
    @Query(value = " "
    //close
//  + "INSERT INTO status (is_open,advertisement, updated) "
//  + "SELECT "
//  + " false, to_close.id, NOW() "
//  + "FROM( "
//  + " SELECT " 
//  + "             ad.id as id, " 
//  + "             ad.native_id as native_id, " 
//  + "             sc_newest.updated as updated, " 
//  + "             status.is_open as is_open " 
//  + " FROM advertisement AS ad " 
//  + " LEFT JOIN " 
//  + "         (SELECT "
//  + "             MAX(sc.UPDATED) AS updated, "
//  + "             sc.ADVERTISEMENT "
//  + "         FROM ADVERTISEMENT AS ad "
//  + "         LEFT JOIN STATUS AS sc "
//  + "             ON  ad.ID = sc.ADVERTISEMENT "
//  + "         WHERE   ad.NATIVE_ID NOT IN  :shown_ads "
//  + "         GROUP BY sc.advertisement) AS sc_newest "
//  + "     ON ad.id = sc_newest.advertisement "
//  + " LEFT JOIN status AS status "
//  + "     ON ad.id = status.advertisement AND status.updated = sc_newest.updated "
//  + " WHERE "
//  + "     status.is_open = true " 
//  + "     AND ad.native_id NOT IN :shown_ads) AS to_close ; "
//  + ""
    //reopen
//  + "INSERT INTO status (is_open,advertisement, updated) "
//  + "SELECT "
//  + " true, to_reopen.id, NOW() "
//  + "FROM( "
//  + " SELECT " 
//  + "             ad.id as id, " 
//  + " FROM advertisement AS ad " 
//  + " LEFT JOIN " 
//  + "             (SELECT "
//  + "                 MAX(sc.updated) AS updated, " 
//  + "                 sc.advertisement " 
//  + "             FROM status AS sc "
//  + "             GROUP BY sc.advertisement) AS sc_newest "
//  + "     ON ad.id = sc_newest.advertisement "
//  + " LEFT JOIN status AS status "
//  + "     ON ad.id = status.advertisement AND status.updated = sc_newest.updated "
//  + " WHERE "
//  + "     status.is_open = false " //reopen closed 
//  + "     AND ad.native_id NOT IN :shown_ads) AS to_reopen ; "
//  + ""
    // get stored previously 
    + " SELECT "
    + " advertisement.native_id "
    + "FROM advertisement "
    + " WHERE "
    + " advertisement.native_id IN :shown_ads "
            , nativeQuery = true)

    public List<String> closeReopenFindStored(@Param("shown_ads") List<String> shownNaturalIDs);


    @Modifying
    @Query(value = "INSERT INTO status (is_open,advertisement, updated) "
    + "SELECT "
    + " false, to_close.id, NOW() "
    + "FROM( "
    + " SELECT " 
    + "             ad.id as id, " 
    + "             ad.native_id as native_id, " 
    + "             sc_newest.updated as updated, " 
    + "             status.is_open as is_open " 
    + " FROM advertisement AS ad " 
    + " LEFT JOIN " 
    + "         (SELECT "
    + "             MAX(sc.UPDATED) AS updated, "
    + "             sc.ADVERTISEMENT "
    + "         FROM ADVERTISEMENT AS ad "
    + "         LEFT JOIN STATUS AS sc "
    + "             ON  ad.ID = sc.ADVERTISEMENT "
    + "         WHERE   ad.NATIVE_ID NOT IN  :shown_ads "
    + "         GROUP BY sc.advertisement) AS sc_newest "
    + "     ON ad.id = sc_newest.advertisement "
    + " LEFT JOIN status AS status "
    + "     ON ad.id = status.advertisement AND status.updated = sc_newest.updated "
    + " WHERE "
    + "     status.is_open = true " 
    + "     AND ad.native_id NOT IN :shown_ads) AS to_close ; "
    + "", nativeQuery = true)
    public void saveCloseReopenFindStored(@Param("shown_ads") List<String> shownNaturalIDs);

AdvertisementRepositoryTestService.java

@Transactional
    public List<String> closeReopenFindStored(List<String> shownAdNumbers){
        advertisementRepository.saveCloseReopenFindStored(shownAdNumbers);
        return advertisementRepository.closeReopenFindStored(shownAdNumbers);
    }

AdvertisementRepositoryTest.java

List<String>  storedPreviously =     
                    advertisementRepositoryTestService.closeReopenFindStored(shownAdNaturalIds);
            assertEquals(2, storedPreviously.size());

AdvertisementRepositoryTest.java 没有变化

注意:我已更改您的返回类型 Set<String>至List<String>您可以根据您的要求进行更改。