logo标签列表

Java java.sql.SQLException: 准备语句时无效的列索引

javaoraclejdbcsqlexception
20

我的代码通过用户输入查询数据库中一组行。我已经在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开发人员中运行良好。 我正在使用Oracle

编辑：

结果集在同一方法中读入此处。 然后将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
}
你能否发布用于访问结果集的代码？也许你正在尝试选择结果集中不存在的列！ - Avinash Nair
为了更快地获得帮助，请发布一个SSCCE。 - joey rohan
3个回答
35

在查询字符串中，通配符应为?而不是'?'。这样应该就可以解决问题了。

编辑：

另外，您需要将date '?'更改为to_date(?, 'yyyy-mm-dd')。请尝试并让我知道结果。

嗨，感谢回复。我尝试去掉''但是控制台抛出了java.sql.SQLException: ORA-00936: missing expression。 - user1851487
2
你应该在每行末尾加上空格。 - giorashc
1
对于 java.sql.SQLException: ORA-0093，请参考此 Stack Overflow 问题 https://dev59.com/KGPVa4cB1Zd3GeqP7qZb - Avinash Nair
@giorashc：很高兴你发现了那个。 - TechSpellBound
2
啊哈，谢谢你的帮助，可以了。将“to”更改为“to_date(?)”。谢谢。 - user1851487
2
如@TechSpellBound所建议，去掉问号周围的引号。然后在连接字符串的每一行末尾添加一个空格字符。否则，整个查询将被发送为（仅使用其中的一部分作为示例）：.... WHERE bookings.booking_end < date ?OR bookings.booking_start > date ?GROUP BY ....。在查询字符串中需要的地方都要用空格字符将?和OR分开。
据我所知，简写日期转换仅适用于字面字符串，而不适用于参数。此外，他的下一行以空格开头，因此您描述的问题实际上并不存在。 - Mark Rotteveel
我是在参考他的例子，否则他可能会感到困惑。这已经被@TechSpellBound提到了。 - giorashc
1
在 date '?' 中，'?' 是一个具有值为 ? 的字面字符串，而不是参数占位符，因此您的查询没有任何参数。 date 是从（字面）字符串到日期的简写转换。 您需要将 date '?' 替换为 ? 以实际拥有参数。
另外，如果您知道它是日期，则使用 setDate(..) 而不是 setString(..) 来设置参数。
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接