java - Java中的奇怪异常PreparedStatement : Parameter index out of range

Question

我有一个字符串

    final static String OPENTICID="SELECT j.`uid`,j.`key`,j.`status`,j.`group`,j.`createdDate`,j.`updatedDate`,j.`priority`,j.`reporter`,j.`assignee`,`type`,j.`summary` FROM jira_local j "+ 
"                     INNER JOIN "+
"                      ( SELECT MAX(uid) u FROM jira_local GROUP BY `key` ) x "+
"                      ON j.uid=x.u "+
"                     WHERE convert_tz(createdDate,'+05:30','-05:00') > ? "+
"                      AND `status` IN ('acknowledged','In process','Advanced Investigation','Open','Assigned','More investigation','reopened','New','partenered investigation) "+
"                      AND `group` NOT IN ('l3','jira_pms_connect_l3_support','l3_istay2','l3_expedia','jira-ota-webservices','jira_ota_pushstyle_dev','l3_web_3.0','jira_l3_istay2betasupport','l3_ihotelier','jira_ihotelier_l3_support','l3_databridge','l3_istay2betasupport','istay3 team','reservations l3 development team','jira_ihotelier 3.0_dev') "+
"                     AND convert_tz(updatedDate,'+05:30','-05:00') < ? ";

该查询有 2 个 ? 使用PreparedStatement设置

以下是我的代码片段

con = DBConnector.getInstance().getConnection("travelclick");
            PreparedStatement pst=con.prepareStatement(new StringBuilder("select `group` , count(*) cnt from (" +
                                                        OPENTICID +
                                                        ") y group by `group`").toString());
            System.out.println("select `group` , count(*) cnt from (" +
                                                        OPENTICID +
                                                        ") y group by `group`");
            System.out.println(pst);
            pst.setTimestamp(1, new java.sql.Timestamp(octDate.getTimeInMillis()));
            System.out.println(pst);
            pst.setTimestamp(2, new java.sql.Timestamp(end.getTimeInMillis()));

            ResultSet rs=pst.executeQuery();

下面是输出

select `group` , count(*) cnt from (SELECT j.`uid`,j.`key`,j.`status`,j.`group`,j.`createdDate`,j.`updatedDate`,j.`priority`,j.`reporter`,j.`assignee`,`type`,j.`summary` FROM jira_local j                      INNER JOIN                       ( SELECT MAX(uid) u FROM jira_local GROUP BY `key` ) x                       ON j.uid=x.u                      WHERE convert_tz(createdDate,'+05:30','-05:00') > ?                       AND `status` IN ('acknowledged','In process','Advanced Investigation','Open','Assigned','More investigation','reopened','New','partenered investigation)                       AND `group` NOT IN ('l3','jira_pms_connect_l3_support','l3_istay2','l3_expedia','jira-ota-webservices','jira_ota_pushstyle_dev','l3_web_3.0','jira_l3_istay2betasupport','l3_ihotelier','jira_ihotelier_l3_support','l3_databridge','l3_istay2betasupport','istay3 team','reservations l3 development team','jira_ihotelier 3.0_dev')                       AND convert_tz(updatedDate,'+05:30','-05:00') < ? ) y group by `group`
com.mysql.jdbc.PreparedStatement@1484a05: select `group` , count(*) cnt from (SELECT j.`uid`,j.`key`,j.`status`,j.`group`,j.`createdDate`,j.`updatedDate`,j.`priority`,j.`reporter`,j.`assignee`,`type`,j.`summary` FROM jira_local j                      INNER JOIN                       ( SELECT MAX(uid) u FROM jira_local GROUP BY `key` ) x                       ON j.uid=x.u                      WHERE convert_tz(createdDate,'+05:30','-05:00') > ** NOT SPECIFIED **                       AND `status` IN ('acknowledged','In process','Advanced Investigation','Open','Assigned','More investigation','reopened','New','partenered investigation)                       AND `group` NOT IN ('l3','jira_pms_connect_l3_support','l3_istay2','l3_expedia','jira-ota-webservices','jira_ota_pushstyle_dev','l3_web_3.0','jira_l3_istay2betasupport','l3_ihotelier','jira_ihotelier_l3_support','l3_databridge','l3_istay2betasupport','istay3 team','reservations l3 development team','jira_ihotelier 3.0_dev')                       AND convert_tz(updatedDate,'+05:30','-05:00') < ? ) y group by `group`
com.mysql.jdbc.PreparedStatement@1484a05: select `group` , count(*) cnt from (SELECT j.`uid`,j.`key`,j.`status`,j.`group`,j.`createdDate`,j.`updatedDate`,j.`priority`,j.`reporter`,j.`assignee`,`type`,j.`summary` FROM jira_local j                      INNER JOIN                       ( SELECT MAX(uid) u FROM jira_local GROUP BY `key` ) x                       ON j.uid=x.u                      WHERE convert_tz(createdDate,'+05:30','-05:00') > '2012-10-01 12:00:00'                       AND `status` IN ('acknowledged','In process','Advanced Investigation','Open','Assigned','More investigation','reopened','New','partenered investigation)                       AND `group` NOT IN ('l3','jira_pms_connect_l3_support','l3_istay2','l3_expedia','jira-ota-webservices','jira_ota_pushstyle_dev','l3_web_3.0','jira_l3_istay2betasupport','l3_ihotelier','jira_ihotelier_l3_support','l3_databridge','l3_istay2betasupport','istay3 team','reservations l3 development team','jira_ihotelier 3.0_dev')                     AND convert_tz(updatedDate,'+05:30','-05:00') < ? ) y group by `group`
java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1).
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
    at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:2569)
    at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:2600)
    at com.mysql.jdbc.PreparedStatement.setTimestampInternal(PreparedStatement.java:3552)
    at com.mysql.jdbc.PreparedStatement.setTimestamp(PreparedStatement.java:3507)
    at com.cybage.WeeklyStatusReport.OpenTicketsCountReport(WeeklyStatusReport.java:873)
    at com.cybage.WeeklyStatusReport.generateReport(WeeklyStatusReport.java:319)
    at com.cybage.WeeklyStatusReport.main(WeeklyStatusReport.java:1193)

我不知道为什么有一个异常(exception)，我有 2 个 ? 和 2 个 pst.setTimeStamp()

Answer 1

您缺少引号:

'partenered investigation) "+
                        ^

                        |
here --------------------

顺便说一句，这是一个很糟糕的查询 - 作为存储过程可能更容易维护(在代码中很难阅读)。