Java/JDBC/SQLite-更新BLOB的问题

问题描述

我在更新Blob时遇到问题，问题是pst.executeUpdate没有执行，但是，如果我取出与Blob/Tryinng相关的所有内容以更新Blob，则其他所有内容都会更新，即ID ，名称，地址等.一切正常运行，问题只在于Blob.

I am having an issue with updating the Blob, the issue is that the pst.executeUpdate with not execute, however, if I take out the everything that relates the the Blob/Tryinng to update the Blob everything else will update i.e. ID, Name, Address etc. Everything functions as it should, the issue is just with the 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文件以及进行检索.只是此更新给我一个问题.

I can however, insert and delete blob files as well as retrieve. Just this updating is giving me an issue.

推荐答案

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

Your SQL command text is not valid for a parameterized query. Instead of creating a dynamic SQL command string with imbedded values ...

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+"'";

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

... you should be using a command string with question marks as parameter placeholders ...

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

...，然后使用.setInt，.setString，.setBytes 等设置参数值.

... and then using .setInt, .setString, .setBytes et al to set the parameter values.

(还请注意，使用... WHERE ID = ?时，实际上对于SET"ID"值来说是多余的.)

(Note also that it is actually redundant to SET the "ID" value when you are using ... WHERE ID = ?.)

这篇关于Java/JDBC/SQLite-更新BLOB的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！