点击编辑/删除按钮时Derby数据库报INTEGER与CHAR类型不兼容错误

Java程序连接Derby数据库时，点击编辑或删除按钮触发以下错误：

java.sql.SQLSyntaxErrorException: Comparisons between 'INTEGER' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')

编辑按钮代码 #

private void UpdateBtnMouseClicked(java.awt.event.MouseEvent evt) {
    if (SerialNumber.getText().isEmpty() ||
        ProductName.getText().isEmpty()  ||
        DateAcquired.getText().isEmpty() ||
        Quantity.getText().isEmpty()     ||
        Description.getText().isEmpty()) {
        JOptionPane.showMessageDialog(this, "Missing Information");
    }
    else {
        try {
            Con = DriverManager.getConnection("jdbc:derby://localhost:1527/Inventorydb",
                                              "User1",
                                              "1220");
            String UpdateQuery = "Update User1.PRODUCTTBL " +
                                    "set ProductName='"+ProductName.getText()+"'"+
                                       ",DateAcquired='"+DateAcquired.getText()+"'"+
                                       ",Quantity='"+Quantity.getText()+"'"+
                                       ",Description='"+Description.getText()+"'"+
                                 " where SerialNumber="+"'"+SerialNumber.getText()+"'";
            Statement Add = Con.createStatement();
            Add.executeUpdate(UpdateQuery);
            JOptionPane.showMessageDialog(this, "Product Updated Successfully");
            SelectProd();
        }
        catch(Exception e) {
            e.printStackTrace();
        }
    }
}

删除按钮代码 #

private void DeleteBtnMouseClicked(java.awt.event.MouseEvent evt) {
    if(SerialNumber.getText().isEmpty()) {
        JOptionPane.showMessageDialog(this, "Enter the SerialNumber to be Deleted");
    }
    else {
        try {
            Con = DriverManager.getConnection("jdbc:derby://localhost:1527/Inventorydb","User1", "1220");
            String ID = SerialNumber.getText();
            String Query = "Delete from User1.PRODUCTTBL where SERIALNUMBER="+"'"+ID+"'";
            Statement Add = Con.createStatement();
            Add.executeUpdate(Query);
            SelectProd ();
            JOptionPane.showMessageDialog(this, "Product Deleted Successfully");
        }
        catch(SQLException e) {
            e.printStackTrace();
        }
    }
}

错误根源是数据库中SerialNumber字段类型为INTEGER，但你的SQL语句里给该字段的值加了单引号，把它当成字符串处理，导致Derby尝试对整数和字符串做比较，类型不匹配触发报错。

直接修复：去掉单引号 #

在Update和Delete的SQL语句中，删除SerialNumber值两侧的单引号，直接使用数字格式：

• 编辑按钮修改后的SQL：

String UpdateQuery = "Update User1.PRODUCTTBL " +
                        "set ProductName='"+ProductName.getText()+"'"+
                           ",DateAcquired='"+DateAcquired.getText()+"'"+
                           ",Quantity='"+Quantity.getText()+"'"+
                           ",Description='"+Description.getText()+"'"+
                     " where SerialNumber="+SerialNumber.getText();

• 删除按钮修改后的SQL：

String Query = "Delete from User1.PRODUCTTBL where SERIALNUMBER="+ID;

进阶优化：使用PreparedStatement #

直接拼接字符串不仅易出类型错误，还存在SQL注入风险，建议改用PreparedStatement，同时明确类型转换：
以编辑按钮为例：

// 替换原Statement相关代码
String UpdateQuery = "Update User1.PRODUCTTBL " +
                        "set ProductName=?, DateAcquired=?, Quantity=?, Description=? " +
                     "where SerialNumber=?";
PreparedStatement pstmt = Con.prepareStatement(UpdateQuery);
pstmt.setString(1, ProductName.getText());
pstmt.setString(2, DateAcquired.getText());
pstmt.setString(3, Quantity.getText());
pstmt.setString(4, Description.getText());
// 将字符串转成整数类型，匹配字段类型
pstmt.setInt(5, Integer.parseInt(SerialNumber.getText()));
pstmt.executeUpdate();

额外校验：避免格式错误 #

因为SerialNumber.getText()返回字符串，转整数时可能出现格式错误，建议添加校验逻辑：

try {
    int serialNum = Integer.parseInt(SerialNumber.getText());
    // 后续SQL操作
} catch (NumberFormatException e) {
    JOptionPane.showMessageDialog(this, "请输入有效的数字序列号");
    return;
}

内容的提问来源于stack exchange，提问作者Gumban Nikki