preparedstatement_大数据知识库

import java.sql.PreparedStatement;

myQuery = "UPDATE File SET BCN = ?, BLN = ?, BID = ?, 
                  DATE2 = (CASE WHEN parmDate <> 0 AND DATE2 = 0    
                                  THEN parmDate
                                  ELSE DATE2 END) 
          WHERE ERF = " + String.valueOf(erfNr).trim();

db2Stmt = db2Conn.prepareStatement(myQuery);

db2Stmt.setString(1, String.format("%017d", erfNr));
db2Stmt.setInt(2, f3BLin);
db2Stmt.setInt(3, entryBufferId);
db2Stmt.setInt(4, parmDate); // <== ???

db2Stmt.executeUpdate();

我要更新字段 date2 在 file .
我该如何处理案件结构？ parmDate 是一个过度给定的参数。
我得到了db2stmt.setint（8，parmdate）的数据截断；在数据库文件中，字段是十进制8,0。
为什么要截短？

String myQuery = "";
    int erfNr, f3BLin, entryBufferId, parmDate ;

    erfNr = 11183594;
    f3BLin = 9;
    entryBufferId = 999;
    parmDate  = 19700323;

    // Update-Statement
    try {

        myQuery = "UPDATE FILE SET t_BCN = ?, t_BLN = ?, t_BZO = ?, t_BID = ?, t_AND = ?, t_ANT = ?, t_AUS = ?, " +
                  "t_DATE2 = (CASE WHEN ? <> 0 AND t_DATE2 = 0 THEN ? ELSE t_DATE2 END)"    
                + " WHERE t_ERF = ? ";

        SimpleDateFormat sdfTime = new SimpleDateFormat("HHmmss");
        SimpleDateFormat sdfDate = new SimpleDateFormat("yyyyMMdd");
        int t_AnD = Integer.valueOf(sdfDate.format(new Date()));
        int t_AnT = Integer.valueOf(sdfTime.format(new Date()));

        db2Stmt = db2Conn.prepareStatement(myQuery);

        db2Stmt.setString(1, String.format("%017d", erfNr));
        db2Stmt.setInt(2, f3BLin);
        db2Stmt.setString(3, " ");
        db2Stmt.setInt(4, entryBufferId);
        db2Stmt.setInt(5, t_AnD);
        db2Stmt.setInt(6, t_AnT);
        db2Stmt.setString(7, as400CurrentUser.trim() + "§");
        db2Stmt.setInt(8, parmDate );  <== 
        db2Stmt.setInt(9, parmDate );
        db2Stmt.setInt(10, erfNr);
        db2Stmt.executeUpdate();
        // int resultCode = db2Stmt.executeUpdate();
    }

例外

java.sql.DataTruncation: Data truncation
at com.ibm.as400.access.AS400JDBCPreparedStatement.testDataTruncation(AS400JDBCPreparedStatement.java:3265)
at com.ibm.as400.access.AS400JDBCPreparedStatement.setValue(AS400JDBCPreparedStatement.java:3176)
at com.ibm.as400.access.AS400JDBCPreparedStatement.setInt(AS400JDBCPreparedStatement.java:2507)
at com.hji.service.DamageService.TestUpdateFAHRZPP(DamageService.java:3359)
at com.hji.controller.DamageController.checkRestApiTest(DamageController.java:1244)

这部分工作正常：

"t_DATE2 = (CASE WHEN " + String.valueOf(parmDate) + " <> 0 AND t_DATE2 = 0 THEN "+ String.valueOf(parmDate) +" ELSE t_DATE2 END)"

如果您确实需要当前的条件，则必须设置两次参数：

import java.sql.PreparedStatement;

myQuery = "UPDATE File SET BCN = ?, BLN = ?, BID = ?,"
    + " DATE2 = (CASE WHEN ? <> 0 AND DATE2 = 0 THEN ? ELSE DATE2 END)"
    + " WHERE ERF = ?";

db2Stmt = db2Conn.prepareStatement(myQuery);

db2Stmt.setString(1, String.format("%017d", erfNr));
db2Stmt.setInt(2, f3BLin);
db2Stmt.setInt(3, entryBufferId);
db2Stmt.setInt(4, parmDate);
db2Stmt.setInt(5, parmDate);
db2Stmt.setInt(6, erfNr);

db2Stmt.executeUpdate();

如果你能把你的情况简化为 if DATE2 = 0 您可以这样重写代码：

import java.sql.PreparedStatement;

myQuery = "UPDATE File SET BCN = ?, BLN = ?, BID = ?, DATE2 = IF(DATE2 = 0, ?, DATE2) WHERE ERF = ?";

db2Stmt = db2Conn.prepareStatement(myQuery);

db2Stmt.setString(1, String.format("%017d", erfNr));
db2Stmt.setInt(2, f3BLin);
db2Stmt.setInt(3, entryBufferId);
db2Stmt.setInt(4, parmDate);
db2Stmt.setInt(5, erfNr);

db2Stmt.executeUpdate();

另请参见：
在preparedstatement中重用参数？
jdbc中的命名参数

preparedstatement

1条答案

相关问题

热门标签

最新问答