Java java.sql.SQLException：准备语句时的列索引无效
2022-09-02 09:25:26
我下面的代码根据用户输入在数据库中查询一组行。我已经尝试并测试了SQL Developer内部的查询，它工作得很好，返回正确的行。输入的例子是 ： 2013-01-22
但是由于Java内部的某种原因，我得到了这个错误：
java.sql.SQLException: Invalid column index
控制台指示它在此处的此行触发：
preparedStatement.setString(1, to);
完整的连接代码：
ResultSet rs = null;
PreparedStatement preparedStatement = null;
try {
String strQuery =
"SELECT homes.home_id, homes.title, homes.description, homes.living_room_count, homes.bedroom_count, homes.bathroom_count, homes.price, homes.sqft,"
+ " listagg(features.feature_name, '\n') WITHIN GROUP(ORDER BY features.feature_name) features, home_type.type_name"
+ " FROM homes"
+ " INNER JOIN bookings ON bookings.home_id <> homes.home_id"
+ " INNER JOIN home_feature ON homes.home_id = home_feature.home_id"
+ " INNER JOIN home_type ON home_type.type_code = homes.type_code"
+ " INNER JOIN features ON home_feature.feature_id = features.feature_id"
+ " WHERE bookings.booking_end < date '?'"
+ " OR bookings.booking_start > date '?'"
+ " GROUP BY homes.home_id, homes.title, homes.description, homes.living_room_count, homes.bedroom_count, homes.bathroom_count, homes.price, homes.sqft, home_type.type_name";
preparedStatement = conn.prepareStatement(strQuery);//prepare the statement
preparedStatement.setString(1, to);//insert
preparedStatement.setString(2, from);//insert
rs = preparedStatement.executeQuery();//execute query
完整的堆栈跟踪如下所示：
SEVERE: java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5317)
at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:5305)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:248)
at DB.DatabaseConnector.getPropertiesSearch(DatabaseConnector.java:258)
at DB.SearchServlet.doPost(SearchServlet.java:47)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:688)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:770)
at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1550)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:281)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:161)
at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:331)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:231)
at com.sun.enterprise.v3.services.impl.ContainerMapper$AdapterCallable.call(ContainerMapper.java:317)
at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:195)
at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:860)
at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:757)
at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1056)
at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:229)
at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137)
at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)
at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)
at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)
at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)
at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)
at com.sun.grizzly.ContextTask.run(ContextTask.java:71)
at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532)
at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513)
at java.lang.Thread.run(Thread.java:722)
SEVERE: at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5317)
SEVERE: at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:5305)
SEVERE: at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:248)
SEVERE: at DB.DatabaseConnector.getPropertiesSearch(DatabaseConnector.java:258)
SEVERE: at DB.SearchServlet.doPost(SearchServlet.java:47)
SEVERE: at javax.servlet.http.HttpServlet.service(HttpServlet.java:688)
SEVERE: at javax.servlet.http.HttpServlet.service(HttpServlet.java:770)
SEVERE: at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1550)
SEVERE: at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:281)
SEVERE: at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
SEVERE: at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655)
SEVERE: at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595)
SEVERE: at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:161)
SEVERE: at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:331)
SEVERE: at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:231)
SEVERE: at com.sun.enterprise.v3.services.impl.ContainerMapper$AdapterCallable.call(ContainerMapper.java:317)
SEVERE: at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:195)
SEVERE: at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:860)
SEVERE: at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:757)
SEVERE: at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1056)
SEVERE: at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:229)
SEVERE: at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137)
SEVERE: at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)
SEVERE: at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)
SEVERE: at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)
SEVERE: at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)
SEVERE: at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)
SEVERE: at com.sun.grizzly.ContextTask.run(ContextTask.java:71)
SEVERE: at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532)
SEVERE: at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513)
SEVERE: at java.lang.Thread.run(Thread.java:722)
我不太确定为什么会抛出错误，因为查询工作正常是SQL开发人员。我正在使用甲骨文
编辑：
结果集将在同一方法中读入其中。然后，将 homes 对象传回：
HomeList homes = new HomeList();
homes = new HomeList(rs);
构造函数如下所示：
public HomeList(ResultSet rs) {
this();
try {
while (rs.next()) {
list.add(new Home(rs.getInt(1),
rs.getString(2),
rs.getString(3),
rs.getInt(4),
rs.getInt(5),
rs.getInt(6),
rs.getInt(7),
rs.getInt(8),
rs.getString(9),
rs.getString(10)
));
}//end while
} catch (SQLException e) {
e.printStackTrace();
}//end try
}
