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();
        }
    }
}