java SQLException: Invalid parameter index 1 only with PreparedStatement
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40301768/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLException: Invalid parameter index 1 only with PreparedStatement
提问by robyp7
I have got a webapp(JSP/Servlet) with Tomcat8 + SQL Server2012 JDBC Driver Type 4: JTDS old version 1.2.5 (http://jtds.sourceforge.net/)
我有一个带有 Tomcat8 + SQL Server2012 JDBC Driver Type 4: JTDS old version 1.2.5 ( http://jtds.sourceforge.net/)的 webapp(JSP/Servlet )
I change this kind of query, adding Prepared Statement (server pagining)
我改变了这种查询，添加了准备好的语句（服务器分页）
Sting DDXsql = "SELECT '?' *, ( DDX_RECORD_COUNT / '?' + 1 ) AS DDX_PAGE_COUNT
FROM
( SELECT '?' *
FROM ( SELECT '?' *,
(SELECT COUNT(*) " + "FROM "
+ session.getAttribute("DatabaseName") + ".G1_grid "
+ sqlFrom
+ sqlWhere + " "
+ " ) AS DDX_RECORD_COUNT "
+ "FROM " + session.getAttribute("DatabaseName") + ".G1_grid "
+ sqlFrom
+ sqlWhere + " "
+ " ORDER BY '?' '?' , '?' '?' ) AS TMP1 ORDER
BY '?' '?', '?' '?') AS r ORDER BY '?' '?', '?' '?'";
Parameters:
参数：
String top1 = DBManager.getTOP(request, "TOP " + Integer.valueOf((String)ResourceManager.findData("pageSize", request)));
Integer pagesizeInt = Integer.valueOf((String)ResourceManager.findData("pageSize", request));
String top2 = DBManager.getTOP(request, "TOP " + Integer.valueOf((String)ResourceManager.findData("ddxrecordcount", request)));
String top3= DBManager.getTOP(request, "TOP " + Integer.valueOf((String)ResourceManager.findData("toRange", request)));
String notSortStr = (String)ResourceManager.findData("notSort", request);
Object[] values = new Object[] {
top1,
pagesizeInt,
top2,
top3,
SortKey,
Sort,
TotalSortKey,
Sort,
SortKey,
notSortStr,
TotalSortKey ,
notSortStr,
SortKey,
Sort,
TotalSortKey,
Sort
};
Before, I didint use PreparedStatement I have this kind of query (replace "?" with the Object array values, without StringEscapeUtils):
之前，我没有使用 PreparedStatement 我有这种查询（用对象数组值替换“？”，没有 StringEscapeUtils）：
String DDXsql = "SELECT " +
DBManager.getTOP(request, "TOP "
+ Integer.valueOf(StringEscapeUtils.escapeSql((String)ResourceManager.findData("pageSize", request)))) + " *,
( DDX_RECORD_COUNT / " + Integer.valueOf(StringEscapeUtils.escapeSql((String)ResourceManager.findData("pageSize", request))) + " + 1 ) AS DDX_PAGE_COUNT FROM
( SELECT "
+ DBManager.getTOP(request, "TOP "
+ Integer.valueOf(StringEscapeUtils.escapeSql((String)ResourceManager.findData("ddxrecordcount", request))))
+ " * FROM ( SELECT " + DBManager.getTOP(request, "TOP " + Integer.valueOf(StringEscapeUtils.escapeSql((String)ResourceManager.findData("toRange", request))))
+ " *, (SELECT COUNT(*) "
+ "FROM " + session.getAttribute("DatabaseName") + ".G1_grid " + sqlFrom + sqlWhere + " " + " ) AS DDX_RECORD_COUNT "
+ "FROM " + session.getAttribute("DatabaseName")
+ ".G1_grid " + sqlFrom + sqlWhere + " " + " ORDER BY "
+ StringEscapeUtils.escapeSql(SortKey) + " " + StringEscapeUtils.escapeSql(Sort) + ", "
+ StringEscapeUtils.escapeSql(TotalSortKey) + " "
+ StringEscapeUtils.escapeSql(Sort) + ") AS TMP1 ORDER BY "
+ StringEscapeUtils.escapeSql(SortKey) + " "
+ StringEscapeUtils.escapeSql((String)ResourceManager.findData("notSort", request))
+ ", " + StringEscapeUtils.escapeSql(TotalSortKey) + " "
+ StringEscapeUtils.escapeSql((String)ResourceManager.findData("notSort", request)) + " ) AS r ORDER BY "
+ StringEscapeUtils.escapeSql(SortKey) + " "
+ StringEscapeUtils.escapeSql(Sort) + ", "
+ StringEscapeUtils.escapeSql(TotalSortKey)
+ " " + StringEscapeUtils.escapeSql(Sort) + " ";
The last query runs without error, System.out of this query give this for example:
最后一个查询运行没有错误，这个查询的 System.out 例如：
SELECT TOP 20 *, ( DDX_RECORD_COUNT / 20 + 1 ) AS DDX_PAGE_COUNT
FROM
( SELECT TOP 20 * FROM
( SELECT TOP 20 *,
(SELECT COUNT(*)
FROM SuiteMA_DIP.dbo.G1_grid
WHERE 1 = 1 ) AS DDX_RECORD_COUNT
FROM SuiteMA_DIP.dbo.G1_grid WHERE 1 = 0 ORDER BY DATA_ISCRIZIONE_ORDER DESC, SOGGETTO_RILEVANTE_PAID DESC) AS TMP1 ORDER BY DATA_ISCRIZIONE_ORDER ASC, SOGGETTO_RILEVANTE_PAID ASC ) AS r ORDER BY DATA_ISCRIZIONE_ORDER DESC, SOGGETTO_RILEVANTE_PAID DESC
But when i run sql with preparedStatement:
但是当我用preparedStatement运行sql时：
java.sql.SQLException: Invalid parameter index 1.
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.getParameter(JtdsPreparedStatement.java:340)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setParameter(JtdsPreparedStatement.java:409)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setObjectBase(JtdsPreparedStatement.java:395)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setObject(JtdsPreparedStatement.java:667)
at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:188)
at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:188)
at it.netbureau.jfx.db.SQLDBManager.execSQL(SQLDBManager.java:57)
at it.netbureau.jfx.db.SQLDBManager.execSQL(SQLDBManager.java:78)
at org.apache.jsp.G1.select_jsp._jspService(select_jsp.java:691)
The java method execute the query :
java方法执行查询：
class jfx.db.SQLDBManager.execSQL:
jfx.db.SQLDBManager.execSQL 类：
public Object execSQL(PreparedStatement stmt, Object values[], String xmlId)
throws SQLException
{
Object result = null;
if(stmt == null)
return null;
try
{
for(int i = 0; i < values.length; i++)
if(values[i] == null)
stmt.setNull(i + 1, 4);
else
stmt.setObject(i + 1, values[i]); <--this give exception!
if(stmt.execute()) result = transform(stmt.getResultSet(), xmlId);
}
catch(SQLException ex)
{
rollback();
throw ex;
}
return result;
}
What's wrong?
怎么了？
Thank you very much
非常感谢你
roby
罗比
回答by Mark Rotteveel
Your query does not contain any parameters, a '?'is just a literal string with a question mark in it, it is not a parameter.
您的查询不包含任何参数， a'?'只是一个带有问号的文字字符串，它不是参数。
You also can't parameterize object names like column names and clauses (like a TOP 20), so even if you'd change it to - for example - order by ?, ...it wouldn't work, as you'd be sorting by the string value (which would be the same for all rows, so effectively you wouldn't be sorting at all).
您也不能参数化对象名称，如列名和子句（如 a TOP 20），因此即使您将其更改为 - 例如 -order by ?, ...它也不起作用，因为您将按字符串值排序（这将所有行都相同，因此实际上您根本不会进行排序）。
To do what you want to do you will need to concatenate the column names (and other clauses) into the query string. This also means that you might open yourself up to SQL injection: be sure to check the values carefully (for example against a whitelist of allowed values).
要执行您想要执行的操作，您需要将列名（和其他子句）连接到查询字符串中。这也意味着您可能会对 SQL 注入敞开心扉：一定要仔细检查这些值（例如，对照允许值的白名单）。
