报错报错。。。

Question

try{      //取出所有符合条件的业务明细
      String stSQL="";
      stSQL = "select * from currdetailtoday ";
      stSQL = stSQL + "where ope_type='0' ";
      stSQL = stSQL + "and status='0' ";
      stSQL = stSQL + "and to_char(workdate,'yyyy-mm-dd')='"+workdate+"' ";
      prst = con.prepareStatement(stSQL);
      rs = prst.executeQuery();
      System.out.println(stSQL);      while(rs.next())
      {
        tcraccno=rs.getString("craccno");
        System.out.println(tcraccno);
        tcrflag=rs.getString("crflag").trim();
        tcurrencytype=rs.getString("currencytype").trim();
        tcrbalance=rs.getLong("crbalance");
        tsequenceno=rs.getInt("sequenceno");        stSQL1="select * from currdetail where sequenceno= '"+tsequenceno+"' ";
        stSQL1=stSQL1+" and to_char(workdate,'yyyy-mm-dd')='"+workdate+"' ";
        prst3 = con.prepareStatement(stSQL1);
        rs3 = prst3.executeQuery();
        if (rs3.next())
        {
          request.setAttribute("message","操作失败！");
          request.setAttribute("reason","已入帐");
          request.setAttribute("target","_self");
          request.setAttribute("hyperLink","/rzcl.htm");
          request.getRequestDispatcher("/common/errorPage.jsp").forward(request,response);
          return;
        }

Answer 1

else{
                       //当天活期分户帐是否能录入
        stSQL1="select * from hqfhz where accountno= '"+tcraccno.trim()+"' ";
        stSQL1=stSQL1+"and to_char(begindate,'yyyy-mm-dd')='"+workdate+"' ";
        prst1 = con.prepareStatement(stSQL1);
        rs1 = prst1.executeQuery();        if (rs1.next()) //rs1.next()为真，活期帐今日已经录入，修改
        {
           if (rs1.getString("yesbalance")!=null)
           {
             yesbalance=rs1.getLong("yesbalance");
           }else{
             yesbalance=0;
           }           if (rs1.getString("debitbalance")!=null)
           {
             debitbalance=rs1.getLong("debitbalance");  //借方发生额
           }else{
             debitbalance=0;
           }           if (rs1.getString("creditbalance")!=null)
           {
             creditbalance=rs1.getLong("creditbalance"); //贷方发生额
           }else{
             creditbalance=0;
           }           if (rs1.getString("balance")!=null)
           {
             balance=rs1.getLong("balance");
           }else{
             balance=0;
           }
         
            debitbalance=debitbalance+tcrbalance;
            balance=balance+tcrbalance;
            stSQL="update hqfhz set debitbalance="+debitbalance+",balance="+balance+" where accountno= '"+tcraccno.trim()+"' ";
            stSQL=stSQL+"and to_char(begindate,'yyyy-mm-dd')='"+workdate+"' ";
            prst2=con.prepareStatement(stSQL);
            prst2.execute();
         
        }else{          stSQL="select * from hqfhz where accountno= '"+tcraccno.trim()+"' ";
          stSQL=stSQL+"and to_char(enddate,'yyyy-mm-dd')='2020-01-01' ";
          prst2=con.prepareStatement(stSQL);
          rs2 = prst2.executeQuery();
          if (rs2.next())
          {
            if (rs2.getString("accountno")!=null)
            {
              accountno=rs2.getString("accountno").trim() ;
            }else{
              accountno="";
            }
            if (rs2.getString("accountname")!=null)
            {
              accountname=rs2.getString("accountname").trim() ;
//              accountname = new String(accountname.getBytes("iso-8859-1"));
            }else{
              accountname="";
            }
            if (rs2.getString("cash")!=null)
            {
              cash=rs2.getString("cash").trim() ;
            }else{
              cash="";
            }            currencytype=tcurrencytype;            if (rs2.getString("yesbalance")!=null)
            {
              yesbalance=rs2.getLong("yesbalance");
            }else{
              yesbalance=0;
            }
            if (rs2.getString("debitbalance")!=null)
            {
              debitbalance=rs2.getLong("debitbalance");
            }else{
              debitbalance=0;
            }            if (rs2.getString("creditbalance")!=null)
            {
              creditbalance=rs2.getLong("creditbalance");
            }else{
              creditbalance=0;
            }
            if (rs2.getString("balance")!=null)
            {
              balance=rs2.getLong("balance");
            }else{
              balance=0;
            }            firstdate=rs2.getDate("begindate");
            seconddate=stringdate.stringToDate(workdate);
            day=stringdate.differenceDate(firstdate,seconddate);            if (rs2.getString("accruAmt")!=null)
            {
              accruAmt=rs2.getLong("accruAmt")+balance*day;
            }else{
              accruAmt=0+balance*day;
            }            intcycle="0";
            ratechange="0";
            begindate=workdate;
            enddate="2020-01-01";          if (tcrflag.equals("1"))        // 冲账
          {//update last record
            yesbalance=balance;
            debitbalance=tcrbalance;
            creditbalance=0;
            balance=yesbalance+debitbalance-creditbalance;            try
            {
              stSQL="update hqfhz set enddate=to_date('"+workdate+ "','yyyy-mm-dd')  where accountno= '"+tcraccno.trim()+"' ";
              stSQL=stSQL+"and to_char(enddate,'yyyy-mm-dd')='2020-01-01' ";
              prst2=con.prepareStatement(stSQL);
              prst2.execute();
            }catch(Exception sqlEx){
              System.out.println("Error in class InputVisual " + sqlEx.toString());
            }
            //insert into record
            try
            {
              stSQL="insert into  hqfhz (accountno,accountname,cash,currencytype,"
                   +"yesbalance,debitbalance,creditbalance,balance,accruamt,intcycle,ratechange,"
                   +"yearrate,begindate,enddate)"
                   +" values('"+accountno+"','"+accountname+"','"+cash+"','"+currencytype+"',"
                   +"'"+yesbalance+"','"+debitbalance+"','"+creditbalance+"','"+balance+"','"+accruAmt+"',"
                   +"'"+intcycle+"','"+ratechange+"',"
                   +"'"+yearrate+"',to_date('"+begindate+"','yyyy-mm-dd'),to_date('"+enddate+"','yyyy-mm-dd')) ";              prst2=con.prepareStatement(stSQL);
              prst2.execute();
            }catch(Exception sqlEx){
             System.out.println("Error in class InputVisual " + sqlEx.toString());
            }
          }else if(tcrflag.equals("2"))        //反交易
          {
            yesbalance=balance;
            debitbalance=0;
            creditbalance=tcrbalance;
            balance=yesbalance+debitbalance-creditbalance;
            begindate=workdate;            try
            {
              stSQL="update hqfhz set enddate=to_date('"+workdate+ "','yyyy-mm-dd')  where accountno= '"+ tcraccno.trim() +"' ";
              stSQL=stSQL+" and to_char(enddate,'yyyy-mm-dd')='2020-01-01' ";
              prst2=con.prepareStatement(stSQL);
              prst2.execute();
            }catch(Exception sqlEx){
              System.out.println("Error in class InputVisual " + sqlEx.toString());
            }
            //insert into record
            try
            {
              stSQL="insert into  hqfhz (accountno,accountname,cash,currencytype,"
                   +"yesbalance,debitbalance,creditbalance,balance,accruamt,intcycle,ratechange,"
                   +"yearrate,begindate,enddate)"
                   +" values('"+accountno+"','"+accountname+"','"+cash+"','"+currencytype+"',"
                   +"'"+yesbalance+"','"+debitbalance+"','"+creditbalance+"','"+balance+"','"+accruAmt+"',"
                   +"'"+intcycle+"','"+ratechange+"',"
                   +"'"+yearrate+"',to_date('"+begindate+"','yyyy-mm-dd'),to_date('"+enddate+"','yyyy-mm-dd')) ";              prst2=con.prepareStatement(stSQL);
              prst2.execute();
            }catch(Exception sqlEx){
             System.out.println("Error in class InputVisual " + sqlEx.toString());
            }          }          }
          else{
            System.out.println("no record!");
          }
        }

Answer 2

//insert into detail
        try
        {
          if (rs.getString("crflag")!=null)
          {
            tcrflag=rs.getString("crflag").trim() ;
          }else{
            tcrflag="";
          }
          if (rs.getString("drflag")!=null)
          {
            tdrflag=rs.getString("drflag").trim() ;
          }else{
            tdrflag="";
          }          if (rs.getString("operflag")!=null)
          {
            toperflag=rs.getInt("operflag");
          }else{
            toperflag=1;
          }
          if (rs.getString("opertype")!=null)
          {
            topertype=rs.getString("opertype").trim() ;
          }else{
            topertype="0";
          }
          if (rs.getString("backdate")!=null)
          {
            tbackdate=" to_date('"+rs.getDate("backdate")+"','yyyy-mm-dd')";
          }else{
            tbackdate="";
          }          if (rs.getString("baksequenceno")!=null)
          {
            tbaksequenceno=rs.getInt("baksequenceno");
          }else{
            tbaksequenceno=0;
          }
          if (rs.getString("draccno")!=null)
          {
            tdraccno=rs.getString("draccno").trim();
          }else{
            tdraccno="";
          }          if (rs.getString("craccno")!=null)
          {
            tcraccno=rs.getString("craccno").trim();
          }else{
            tcraccno="";
          }          if (rs.getString("amt")!=null)
          {
            tamt=rs.getLong("amt");
          }else{
            tamt=0;
          }
          if (rs.getString("drbalance")!=null)
          {
            tdrbalance=rs.getLong("drbalance");
          }else{
            tdrbalance=0;
          }
          if (rs.getString("crbalance")!=null)
          {
            tcrbalance=rs.getLong("crbalance");
          }else{
            tcrbalance=0;
          }
          if (rs.getString("currencytype")!=null)
          {
            tcurrencytype=rs.getString("currencytype").trim();
          }else{
            tcurrencytype="";
          }
          if (rs.getString("proofno")!=null)
          {
            tproofno=rs.getString("proofno").trim();
          }else{
            tproofno="";
          }
          if (rs.getString("workdate")!=null)
          {
            tworkdate=" to_date('"+rs.getDate("workdate")+"','yyyy-mm-dd')";
          }else{
            workdate="''";
          }          if (rs.getString("sequenceno")!=null)
          {
            tsequenceno=rs.getInt("sequenceno");
          }else{
            tsequenceno=0;
          }          if (rs.getString("operatorno")!=null)
          {
            toperatorno=rs.getString("operatorno").trim();
          }else{
            toperatorno="";
          }          if (rs.getString("oldoperno")!=null)
          {
            toldoperno=rs.getString("oldoperno").trim();
          }else{
            toldoperno="";
          }          if (rs.getString("re")!=null)
          {
          tre=rs.getString("re").trim();
          }else{
            tre="";
          }          if (rs.getString("ope_type")!=null)
          {
            tope_type=rs.getString("ope_type").trim();
          }else{
            tope_type="";
          }          if (rs.getString("dfzh")!=null)
          {
          tdfzh=rs.getString("dfzh").trim();
          }else{
            tdfzh="";
          }          if (rs.getString("dfhm")!=null)
          {
            tdfhm=rs.getString("dfhm").trim();
          }else{
            tdfhm="";
          }          if (rs.getString("zhaiyao")!=null)
          {
            tzhaiyao=rs.getString("zhaiyao").trim();
          }else{
            tzhaiyao="";
          }           stSQL = "insert into  currdetail(crflag,drflag,operflag,opertype,backdate,baksequenceno,"
                +"draccno,craccno,amt,drbalance,crbalance,currencytype,proofno,workdate,"
                +"sequenceno,operatorno,oldoperno,re,ope_type,dfzh,dfhm,zhaiyao) "
                +" values('"+tcrflag+"','"+tdrflag+"','"+toperflag+"','"+topertype+"','"+tbackdate+"','"+tbaksequenceno+"',"
                +"'"+tdraccno+"','"+tcraccno+"','"+tamt+"','"+tdrbalance+"','"+tcrbalance+"','"+tcurrencytype+"','"+tproofno+"',"
                +""+tworkdate+",'"+tsequenceno+"','"+toperatorno+"','"+toldoperno+"','"+tre+"','"+tope_type+"','"+tdfzh+"','"+tdfhm+"','"+tzhaiyao+"')";         System.out.println(stSQL);
          prst2 = con.prepareStatement(stSQL);
          prst2.execute();
        }catch(Exception sqlEx){           System.out.println("Error in class InputVisual " + sqlEx.toString());
        }
//update today
        try
        {
          stSQL = "update currdetailtoday set status='1' ";
          stSQL = stSQL + "where ope_type='0' ";
          stSQL = stSQL + "and status='0' ";
          stSQL = stSQL + "and sequenceno='"+tsequenceno+"' ";
          stSQL = stSQL + "and to_char(workdate,'yyyy-mm-dd')='"+workdate+"' ";
//          System.out.println(stSQL);
          prst2 = con.prepareStatement(stSQL);
          prst2.execute();
        }catch(Exception sqlEx){
           System.out.println("Error in class InputVisual " + sqlEx.toString());
        }      }      }
      //跳转到操作成功页
      request.setAttribute("message","入帐");
      request.setAttribute("target","_self");
      request.setAttribute("hyperLink","/rzcl.htm");
      request.setAttribute("returnLink","/rzcl.htm");
      request.getRequestDispatcher("/TestBalance").forward(request,response);      //response.sendRedirect("/TestBalance");    }catch(Exception sqlEx){
      //操作失败,打印错误信息
      System.out.println("Error in class InputVisual " + sqlEx.toString());
    }

Answer 3

加一个
finally
    {
        
        try
        {
            if (stmt != null)
            {
            stmt.close();
            }
        }
        catch(Exception e)
        {
        }
    }
}

Answer 4

还应该注意在return之前要关闭记录集和stmt

Answer 5

你把每个逻辑做成一个方法，主方法调用不同的业务逻辑方法，这样就不会出现多个程序段使用一个preparedstatement对象的问题，程序清楚，错误就不会出现了。

Answer 6

to hq1305018(跃强):谢谢大虾
不过我里面好循环呀
能不能给我一个主方法调用不同的业务逻辑方法的例子？/