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等设置参数值。

（还请注意，当您使用...，其中ID=？时，设置“ID”值实际上是多余的。）