Java/JDBC/SQLite

本文介绍了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"); connectionmit(); 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 = ?.)