java - 如何解决这个 java.sql.SQLException : Statement parameter not set

Question

这是我的 DAO 类:

public Map<String, String> issueOrUpdate(Map<String,String> employeeTempcardMap){
        SlingDAO slingdao = new SlingDAO();
        Map<String, String> issueOrUpdateMap = new HashMap<String, String>();
        StringBuffer issueOrReturnTempcardQuery = new StringBuffer();
        String empname="",functionkey="",tempcardnumber="",dateofissue="",dateofreturn="";
        int empid=0,rowid=0,insertOrUpdateStatus=0;
        Connection connection = slingdao.getConnection();
        PreparedStatement issueOrReturnPS=null;
       if (connection != null) {
           try {
            if(employeeTempcardMap.get("functionkey").toString().equals("issueTempCard")){
                empid =Integer.parseInt(employeeTempcardMap.get("empid").toString()); //empname,tempcardnumber,dateofissue,rowid,dateofreturn
                empname =employeeTempcardMap.get("empname").toString();
                tempcardnumber =employeeTempcardMap.get("tempcardnumber").toString();
                dateofissue =employeeTempcardMap.get("issuedate").toString();
                //System.out.println("11111111111111111111111111111111111");
                //System.out.println("empid : "+empid+" : empname: "+empname+" : tempcardnumber : "+tempcardnumber+": dateofissue "+dateofissue+"Status : "+applicationConstants.ISSUED);

                issueOrReturnTempcardQuery.append(" INSERT INTO acct_tempcardhistory(empid,empname,tempcardnumber,tempcardstatus,issuedate,returndate) ");
                issueOrReturnTempcardQuery.append(" VALUES(?,?,?,?,?,?) ");
            }else{
                dateofreturn = employeeTempcardMap.get("returndate").toString();
                rowid = Integer.parseInt(employeeTempcardMap.get("rowid").toString());
                issueOrReturnTempcardQuery.append(" UPDATE acct_tempcardhistory  SET tempcardstatus=?,returndate=? ");
                issueOrReturnTempcardQuery.append(" WHERE id ="+rowid);
            }

                issueOrReturnPS = connection.prepareStatement(issueOrReturnTempcardQuery.toString());

               if(functionkey.equals("issueTempCard")){
                   //System.out.println("2222222222222222222222");
                   issueOrReturnPS.setInt(1, empid);
                   issueOrReturnPS.setString(2,empname.toString().trim());
                   issueOrReturnPS.setString(3, tempcardnumber.toString().trim());
                   issueOrReturnPS.setString(4, applicationConstants.ISSUED.toString().trim());
                   issueOrReturnPS.setString(5, dateofissue.toString().trim());
                   issueOrReturnPS.setString(6, "".toString().trim());
                   //issueOrUpdateMap.put("message","Temporary Card has been issued to "+empid);

               }else {
                   issueOrReturnPS.setString(1, applicationConstants.RETURNED);
                   issueOrReturnPS.setString(2,dateofreturn);
                   //issueOrUpdateMap.put("message","Temporary Card has been Returned by "+empid);
               }
               insertOrUpdateStatus = issueOrReturnPS.executeUpdate();

               System.out.println(insertOrUpdateStatus + "row(s) affected");

               if(insertOrUpdateStatus >0){
                   issueOrUpdateMap.put("message","Temporary Card has been issued/Returned to "+empid);
               }else{
                   issueOrUpdateMap.put("message","Temporary Card has not been issued/Returned to "+empid);
               }
               connection.close();

              // connection.commit();
           } catch (Exception ex) {
            ex.printStackTrace();
            issueOrUpdateMap.put("message","Temporary Card has not been issued/Returned to "+empid);
           } finally {
               try {
                slingdao.closeConnection(connection, null, issueOrReturnPS);
               } catch (Exception ex) {
                   ex.printStackTrace();
               }
           }
       } else {
           issueOrUpdateMap.put("dbmessage","Connection not Established. Please Contact Vendor");
       }
       return issueOrUpdateMap;
   }
}

更新:

堆栈跟踪:

java.sql.SQLException: Statement parameter 3 not set.
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
        at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1045)
        at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:693)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1404)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1318)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1303)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedS
tatement.java:105)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedS
tatement.java:105)
        at com.slingmedia.notifier.dao.TempCardDAO.issueOrUpdate(TempCardDAO.java:158)
        at com.slingmedia.notifier.servlet.TempCardServlet.doPost(TempCardServlet.java:83)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.j
ava:305)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)

        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:225)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:999
)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.jav
a:565)
        at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:1812)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at java.lang.Thread.run(Thread.java:662)

当我打印时，我看到了所有的值，但它仍然显示

java.sql.SQLException: Statement parameter <number> not set.

更新时同样有效，但插入数据时无效，我还将日期设置为字符串。

请指导我该怎么做或我在哪里弄错了？

问候

Answer 1

您忘记将 functionKey 设置为表达式的结果

employeeTempcardMap.get("functionkey").toString();

永远不要声明，尤其是永远不要初始化您的本地变量预先使用虚拟值。这只是掩盖了这样的编码问题。如果你有 final String functionKey = ...，这种错误在理论上是不可能的。