Java java sql SQLException Invalid column index on preparing statement

Question

my code below query the database for a set of rows based on user input  I have tried and tested the query inside of SQL Developer and it works perfectly fine returning the correct rows  And example of an input is   2013-01-22  But for some reason inside of java i am getting this error   java sql SQLException  Invalid column index   The console indicates it is firing at this line here   preparedStatement setString 1  to     The full connection code   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  lt  gt  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  lt  date                              OR bookings booking start  gt  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   The full stack trace looks like this   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    Im not quite sure why the error would be thrown as the query works fine is SQL developer  I am using Oracle  EDIT   The result set is read into this within the same method  The homes object is then passed back   HomeList homes   new HomeList    homes   new HomeList rs     The constructor looks like this   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

User · Answer

As  TechSpellBound suggested remove the quotes around the   signs  Then add a space character at the end of each row in your concatenated string  Otherwise the entire query will be sent as    using only part of it as an example         WHERE bookings booking end  lt  date  OR bookings booking start  gt  date  GROUP BY       The   and the OR needs to be seperated by a space character  Do it wherever needed in the query string

User · Answer

Everywhere inside the query string  the wildcard should be   instead of      That should solve the problem   EDIT    To add to that  you need to change date     to to date     yyyy-mm-dd    Please try that and let me know

User · Answer

In date      the     is a literal string with value    not a parameter placeholder  so your query does not have any parameters  The date is a shorthand cast from  literal  string to date  You need to replace date     with   to actually have a parameter   Also if you know it is a date  then use setDate     and not setString     to set the parameter

[java] Java java.sql.SQLException: Invalid column index on preparing statement

Examples related to java

Examples related to oracle

Examples related to jdbc

Examples related to sqlexception