How to go about saving an image in blob format to MySQL in Java

Question

For the purpose of a task I have to store an image into MySQL as a blob format (even though it would have been better and ideal to store the image path in the database and keep the image in a folder in localcopy).

So far I have researched and couldn't find any answer that could help me, this is what I have done so far

Soon as a button click, this will be fired:

empdao.insertImage(fis);

Image is populated on another even listener like this :

static FileInputStream fis = null;
static String path = null;
path = filechooser.getSelectedFile().getAbsolutePath();
File image = new File(path);
fis = new FileInputStream (image);

This code below takes care of adding it into the database.

public void insertImage(FileInputStream fis) throws SQLException {



Connection c = getConnection();     

    String query = "INSERT INTO Picture (picture) VALUES (?)";

    System.out.println(query);

    PreparedStatement pstmt = c.prepareStatement(query);

    pstmt.setBinaryStream(1, fis);

    pstmt.executeUpdate();

    c.close();
}

However the problem is that I needed it to convert it as a blob and I am not sure how to, can someone help me or guide me on how to go about storing the chosen image as a blob field into MySQL.

Currently when it adds it into database I get java.io file input under the pictures column.

Answer 1

Suppose you have a table my_picures in MySQL with id INT PRIMARY KEY, name VARCHAR(255), and photo BLOB.

Then you can use the following Java code to insert a new picture as BLOB:

public class InsertPictureAsBlob {
    public static void main(String[] args) throws Exception, IOException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager
             .getConnection("jdbc:mysql://localhost/databaseName", "username", "password");
        String INSERT_PICTURE = "INSERT INTO my_picures(id, name, photo) VALUES (?, ?, ?)";

        conn.setAutoCommit(false);
        File file = new File("myPhoto.png");
        try (FileInputStream fis = new FileInputStream(file);
                    PreparedStatement ps = conn.prepareStatement(INSERT_PICTURE)) {
            ps.setString(1, "001");
            ps.setString(2, "name");
            ps.setBinaryStream(3, fis, (int) file.length());
            ps.executeUpdate();
            conn.commit();
        }
    }
}

Answer 2

1) First off you are going to want to make sure you have a table created in your MySQL schema with a BLOB column type defined (BLOB, MEDIUMBLOB, LONGBLOB). Review the BLOB column types that are available in MySQL and select the appropriate size.

2) You are going to want to switch from using a Statement to a PreparedStatement.

String query = "INSERT INTO Pictures (Picture) VALUES (?)";
PreparedStatement pstmt = conn.prepareStatement(query);

3) You are currently passing in a FileInputStream to your method so you just need to use the setBinaryStream method on the PreparedStatement.

pstmt.setBinaryStream(1, fis);

4) Then perform the executeUpdate() on the PreparedStatement.

pstmt.executeUpdate();

Utilize the exception handling you have in your original code and perform appropriate cleanup of objects (database connection, prepared statements), similar to what you have in your original code.