本机查询IN子句引发SQL错误 [英] Native query IN clause throwing sql error

问题描述

以下查询给我类似Missing IN or OUT parameter at index:: 1的错误 我在IN参数(如... WHERE p.SALES_TYPE IN :types)中不带括号的情况下进行了尝试.但仍然是相同的错误.

Below query gives me error like Missing IN or OUT parameter at index:: 1 I tried it without brackets in the IN parameters like ... WHERE p.SALES_TYPE IN :types. but still the same error.

public List getWithinXDays(ArrayList<String> salesTypes,String sId, String  xdays) {

    Query q  = em.createNativeQuery("SELECT p.ORDER_ID FROM formtable p WHERE p.SALES_TYPE IN (:types) AND p.MCODE=:sid AND TRUNC(SYSDATE - p.creationdate) <=:days");
    q.setParameter("types",salesTypes);
    q.setParameter("sid",sId);
    q.setParameter("days",xdays);
    List salesOrderRow = q.getResultList();
    return salesOrderRow;
}

stacktrace:

stacktrace :

<Jan 10, 2017 4:12:07 PM SGT> <Warning> <EclipseLink> <BEA-2005000> <2017-01-10 16:12:07.551--UnitOfWork(29679273)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Missing IN or OUT parameter at index:: 1
Error Code: 17041

推荐答案

JPA不支持本机查询的array参数.要么重写代码以使用JPQL，要么按照下面的答案中的描述设置IN子句的每个条目:

JPA doesn't support array parameters for native queries. Either rewrite your code to use JPQL, or set each entry for IN clause like it's described in the following answer: JPA passing list to IN clause in named native query

这篇关于本机查询IN子句引发SQL错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！