Java/JDBC/SQLite - 更新 BLOB 问题

Question

我在更新 Blob 时遇到问题，问题是 pst.executeUpdate 未执行，但是，如果我取出与 Blob/尝试更新相关的所有内容Blob 其他所有内容都会更新，即 ID、名称、地址等。一切都按其应有的方式运行，问题仅出在 Blob 上。

 updateEmployee.addActionListener(new ActionListener() {

        @Override
        public void actionPerformed(ActionEvent e) {
            // TODO Auto-generated method stub
            Connection connection = null;
            PreparedStatement pst = null;

            try {

                Class.forName("org.sqlite.JDBC");
                connection = DriverManager.getConnection("jdbc:sqlite:employeeDatabase.sqlite");
                connection.setAutoCommit(false);



                int idVal = Integer.parseInt(idTextField.getText());
                String nameVal= nameTextField.getText();
                String genderVal = genderTextField.getText();
                String dobVal = dobTextField.getText();
                String addressVal = addressTextField.getText();
                String postcodeVal =  postcodeTextField.getText();
                String ninVal =  ninTextField.getText();
                String jobVal =  jobtitleTextField.getText();
                String startDateVal =  startdateTextField.getText();
                String salaryVal = salaryTextField.getText();
                String emailVal =  emailTextField.getText();
                //Icon photoBlob = photoLabel.getIcon();
                InputStream img = new FileInputStream(s);
                String sql = "UPDATE employees set ID= '"+ idVal+"', Name = '"+ nameVal +"', Gender ='"+ genderVal+"', DOB='"+ dobVal+"', Address ='"+ addressVal+"', Postcode ='"+ postcodeVal+"', NIN ='"+ ninVal+"', JobTitle='"+ jobVal+"', StartDate ='"+ startDateVal+"', Salary ='"+ salaryVal+"', Email='"+ emailVal+"', Images='"+ img+" WHERE ID= '"+ idVal+"'";

                pst = connection.prepareStatement(sql);
                pst.setInt(1,Integer.parseInt(idTextField.getText()));
                pst.setString(2, nameTextField.getText());
                pst.setString(3, genderTextField.getText());
                pst.setString(4, dobTextField.getText());
                pst.setString(5, addressTextField.getText());
                pst.setString(6, postcodeTextField.getText());
                pst.setString(7, ninTextField.getText());
                pst.setString(9, startdateTextField.getText());
                pst.setString(10, salaryTextField.getText());
                pst.setString(11, emailTextField.getText());
                pst.setBytes(12, readFile(s));

                pst.executeUpdate();




                System.out.println("Employee Updated");
                JOptionPane.showMessageDialog(null, "Employee has successfully been updated");              


                 connection.commit();
                pst.close();
                connection.close();
            }
            catch ( Exception e1 ) {

                if(idTextField.getText().equals("")){
                    JOptionPane.showMessageDialog(null, "Please Ensure An Employee Has Been Selected");
                }
            }
        }});

编辑 -

但是，我可以插入和删除 blob 文件以及检索。只是这次更新给我带来了一个问题。

Answer 1

您的 SQL 命令文本对于参数化查询无效。而不是创建带有嵌入值的动态 SQL 命令字符串...

String sql = "UPDATE employees set ID= '"+ idVal+"', Name = '"+ nameVal +"', Gender ='"+ genderVal+"', DOB='"+ dobVal+"', Address ='"+ addressVal+"', Postcode ='"+ postcodeVal+"', NIN ='"+ ninVal+"', JobTitle='"+ jobVal+"', StartDate ='"+ startDateVal+"', Salary ='"+ salaryVal+"', Email='"+ emailVal+"', Images='"+ img+" WHERE ID= '"+ idVal+"'";

...您应该使用带有问号的命令字符串作为参数占位符...

String sql = "UPDATE employees set ID = ?, Name = ?, Gender = ?, DOB = ?, Address = ?, Postcode = ?, NIN = ?, JobTitle = ?, StartDate = ?, Salary = ?, Email = ?, Images = ? WHERE ID = ?;

...然后使用 .setInt、.setString、.setBytes et al 设置参数值。

(另请注意，当您使用 ... WHERE ID = ? 时，SET“ID”值实际上是多余的。)