在JAVA中将Update语句与MS Access 2010数据库一起使用 [英] Using Update statement with MS Access 2010 database in JAVA

问题描述

您好，我正在为保险领域开发一个小型应用程序.在程序中使用更新语句时出现错误.

Hi I am developing a small application for insurance domain. I am getting an error when I am using the update statement in my program.

错误是net.ucanaccess.jdbc.UcanaccessSQLException:意外令牌:HALF java.lang.NullPointerException

The error is net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: HALF java.lang.NullPointerException

代码是

btnUpdate = new JButton("UPDATE");
btnUpdate.setMnemonic('U');
btnUpdate.setFont(new Font("Times New Roman", Font.BOLD, 11));
GridBagConstraints gbc_btnUpdate = new GridBagConstraints();
gbc_btnUpdate.insets = new Insets(0, 0, 5, 5);
gbc_btnUpdate.gridx = 3;
gbc_btnUpdate.gridy = 3;
contentPane.add(btnUpdate, gbc_btnUpdate);
btnUpdate.setVisible(false);
btnUpdate.addActionListener(new ActionListener() {
    @Override
    public void actionPerformed(ActionEvent ae) {
        Statement stmt = null;
        ResultSet rset = null;
        Calendar currcal = Calendar.getInstance();
        SimpleDateFormat df;
        df = new SimpleDateFormat("dd-MM-yyyy");
        Date getcurrdate = currcal.getTime();
        String currdate = df.format(getcurrdate);       
        System.out.println(getModeID + "," + getModeofPaymentDescription + "," + getModeofPaymentType + "," + currdate);
        try {
            getModeofPaymentDescription=txt_Mode_Of_Payment_Description.getText().toUpperCase();
            getModeofPaymentType=txt_Mode_Of_Payment_Type.getText().toUpperCase();

            stmt = dbcon.DB_Connection("//F://eclipse_Luna_64_Development_Workspace//ProjectJAVA//LIC_AGENCY_TRACKER//DATABASE//LIC_DATA_TRACKER.accdb").createStatement();
            stmt.executeUpdate("update Mode_Of_Payment_Profile set Mode_Of_Payment_Profile_Type='" + getModeofPaymentType + "'"
                            + "',Mode_Of_Payment_Profile_Description='" + getModeofPaymentDescription + "',Mode_Of_Payment_Profile_Creation_Date='" + currdate + "'"
                            + " where Mode_Of_Payment_Profile_ID='" + getModeID + "'");
        } catch (Exception e) {
            //JOptionPane.showMessageDialog(null, "Database Error", "Error Message", JOptionPane.OK_OPTION);
            System.out.println(e);
        }

        txt_Mode_Of_Payment_Description.setText("");
        txt_Mode_Of_Payment_Type.setText("");
        btnAdd.setEnabled(true);
        btnModify.setVisible(true);
        btnUpdate.setVisible(false);
        txt_Mode_Of_Payment_Description.requestFocus();

        try {
            stmt.close();
            rset.close();
            dbcon.DB_Connection("//F://eclipse_Luna_64_Development_Workspace//Project JAVA//LIC_AGENCY_TRACKER//DATABASE//LIC_DATA_TRACKER.accdb").close();
        } catch (Exception e) {
            System.out.println(e);
        }
    }
});

推荐答案

构建SQL字符串的行中有错字.

You have a typo in the lines building your SQL string.

在要插入ModeofPaymentType值的行的末尾有一个单引号:

There's a single quote at the end of the line where you're inserting a value for ModeofPaymentType:

"update Mode_Of_Payment_Profile set Mode_Of_Payment_Profile_Type='"+getModeofPaymentType+"'"

以下行以

+ "',Mode_Of_Payment_Profile_Description='"

导致在modeofPaymentType值之后插入一个额外的单引号.生成的SQL看起来像

resulting in an extra single quote getting inserted after your modeofPaymentType value. The resulting SQL will look like

update Mode_Of_Payment_Profile set Mode_Of_Payment_Profile_Type='mode'',Mode_Of_Payment_Profile_Description='FOO'
,Mode_Of_Payment_Profile_Creation_Date='15-07-2015'
where Mode_Of_Payment_Profile_ID='someid'

两个相邻的单引号被当作转义的单引号，因此解析器认为文字字符串是"mode"，Mode_Of_Payment_Profile_Description =，然后它认为下一个标记是您要为Mode_Of_Payment_Profile_Description传递的任何值.

The two adjacent single-quotes get treated as an escaped single-quote, so the parser thinks the literal string is "mode',Mode_Of_Payment_Profile_Description=", then it thinks the next token is whatever value you're passing in for Mode_Of_Payment_Profile_Description.

如果将java.sql.Statement的使用替换为java.sql.PreparedStatement，则更新如下所示:

If you were to replace use of java.sql.Statement with java.sql.PreparedStatement then your update could look like this:

String connectParams = "//F://eclipse_Luna_64_Development_Workspace//Project JAVA//LIC_AGENCY_TRACKER//DATABASE//LIC_DATA_TRACKER.accdb";
Connection connection = dbcon.DB_Connection(connectParams);
PreparedStatement ps = connection.prepareStatement(
    "update Mode_Of_Payment_Profile set" 
    + " Mode_Of_Payment_Profile_Type=?"
    + ", Mode_Of_Payment_Profile_Description=?"
    + ", Mode_Of_Payment_Profile_Creation_Date=?"
    + " where Mode_Of_Payment_Profile_ID=?");
ps.setString(1, getModeofPaymentType);
ps.setString(2, getModeofPaymentDescription);
ps.setString(3, currdate); 
ps.setString(4, getModeID);

这样，您就不必执行容易出错的事情，例如使用字符串连接插入参数并自行处理引用.它减少了SQL注入的机会(因为PreparedStatement会查找嵌入的转义字符)，并且更具可读性.

This way you don't have to do error-prone things like inserting arguments using string concatenation and handling the quoting yourself. It reduces the opportunities for SQL injection (since the PreparedStatement will look out for embedded escape characters) and is more readable.

这篇关于在JAVA中将Update语句与MS Access 2010数据库一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！