Java - JDBC/Swing - executeUpdate Not Working with BLOB

Question

I have encountered a problem, as far as I know this whole code works up until pst.executeQuery I just can't see as to why this is the issue. I have an update/insert method and no issues regarding them. I am using a label to display the image, so would it be photoLabel.setIcon();or am I just completely off-track. I have one-button for the file chooser which loads the image into the label, the save button (this function) should just write to the database in the 12 column/field called Images.

Please Note - The System.out.Printlnfor testing purposes, just

System.out.println("Working 5");

Will not show, hence why I know it's something to do with pst.executeQuery(). I've tried searching the web, used different .execute methods, I've tried to commit the connection and so on. Alas no luck.

@Override
public void actionPerformed(ActionEvent e) {
    Connection connection = null;

    Statement statement = null;
    ResultSet rs = null;
//  String s = null;
    try {

        Class.forName("org.sqlite.JDBC");
        connection = DriverManager.getConnection("jdbc:sqlite:employeeDatabase.sqlite");
        //connection.setAutoCommit(false);
        System.out.println("Working 1");
         InputStream is = new FileInputStream(new File(s));
        System.out.println("Working 2");
         String sql = "insert into employees(Images) values(?)";
         PreparedStatement pst = connection.prepareStatement(sql);
            System.out.println("Working 3");
           pst.setBlob(12, is);

            System.out.println("Working 4");


           pst.executeQuery();
           connection.commit();
        System.out.println("Working 5");
           JOptionPane.showMessageDialog(null, "Data Inserted");


}
    }

        catch ( Exception e1 ) {


            JOptionPane.showMessageDialog(null, "Error");

        }
}});

This is the method to select the image via the use of a JFileChooser

uploadImage.addActionListener(new ActionListener() {

           public void actionPerformed(ActionEvent e){
                 JFileChooser fileChooser = new JFileChooser();
                 fileChooser.setCurrentDirectory(new File(System.getProperty("user.home")));
                 FileNameExtensionFilter filter = new FileNameExtensionFilter("*.JPG", "jpg","gif","png");      
                 fileChooser.addChoosableFileFilter(filter);
                fileChooser.addChoosableFileFilter(filter);
                 fileChooser.setMultiSelectionEnabled(false);
                 int result = fileChooser.showSaveDialog(null);
                 if(result == JFileChooser.APPROVE_OPTION){
                     File selectedFile = fileChooser.getSelectedFile();
                     String path = selectedFile.getAbsolutePath();
                     photoLabel.setIcon(ResizeImage(path));
                     s = path;
                      }


                 else if(result == JFileChooser.CANCEL_OPTION){
                     System.out.println("No Data");
                 }
             }
            });

Edit -

By not working I mean, I get no errors, program doesn't break. Just the image will not upload to the database and the code System.out.println("Working 5"); doesn't print to console. So it appears to be stuck/freeze at that point on.

@ScaryWombat I get no errors, program doesn't break. Just will not upload the image to database and the code System.out.println("Working 5"); doesn't print to console. — TheNotoriousCoder
– TheNotoriousCoder, Commented Dec 21, 2016 at 2:59
@ScaryWombat Yeah, forgot to include the catch exception in my original post. I've added it now. It appears to only get up to pst.executeQuery and then got to the catch. — TheNotoriousCoder
– TheNotoriousCoder, Commented Dec 21, 2016 at 3:04

Sully · Accepted Answer · 2016-12-21 03:28:15Z

2

First, double check the table structure for table employees and column Images with correct type

Then, set the correct placeholder for input in the prepared statement, there is only one

pst.setBlob(1, ...

Then, use this approach instead

private byte[] readFile(String file) {
    ByteArrayOutputStream bos = null;
    try {
        File f = new File(file);
        FileInputStream fis = new FileInputStream(f);
        byte[] buffer = new byte[1024];
        bos = new ByteArrayOutputStream();
        for (int len; (len = fis.read(buffer)) != -1;) {
            bos.write(buffer, 0, len);
        }
    } catch (FileNotFoundException e) {
        System.err.println(e.getMessage());
    } catch (IOException e2) {
        System.err.println(e2.getMessage());
    }
    return bos != null ? bos.toByteArray() : null;
}

With

pst.setBytes(1, readFile(s));

Finally, call

pst.executeUpdate();

Source: http://www.sqlitetutorial.net/sqlite-java/jdbc-read-write-blob/

edited Dec 21, 2016 at 3:28

answered Dec 21, 2016 at 3:23

Sully

15k5 gold badges56 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

TheNotoriousCoder Over a year ago

Ok, you lost me with First, set the correct placeholder for input in the prepared statement, there is only one. But thank you for your reply. :)

Sully Over a year ago

Read this docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html it depends on the #of ? in the statement. first one is 1 second is 2 and so on.

TheNotoriousCoder Over a year ago

I've added the code but get an error with the private byte[] readFile(Stringfile) = Syntax error on token "readFile", AnnotationName expected

TheNotoriousCoder Over a year ago

Dude, it worked!!!! Sorry about me being stupid, it's pretty late. 03:36AM.... Enjoying the Christmas Holidays hahah! Thank you, I've accepted your answer. Seriously dude. Thank you so much! <3

Sully Over a year ago

Glad you have it sorted. Happy Holidays.

|

Collectives™ on Stack Overflow

Java - JDBC/Swing - executeUpdate Not Working with BLOB

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related