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.

http://stackoverflow.com/a/41235395/267540 – e4c5 Dec 28 '16 at 23:47 — e4c5, Dec 28 '16 at 23:47

score 5 · Accepted Answer · answered Dec 28 '16 at 20:29

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

I have made the changes you suggessted but it still doesn't add the image into the database even with using the PreparedStatement — dark_illusion_909099, Dec 28 '16 at 21:30

score 3 · Answer 2 · answered Dec 28 '16 at 20:38

3

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.

answered Dec 28 '16 at 20:38

M. Rizzo

1,611
12
24

Is there anyway I can do with using the regular Statement instead of PreparedStatement since its a task and I have to follow the spec ??? – dark_illusion_909099 Dec 28 '16 at 20:49
@jomin_george94 The code above is how you are supposed to do it, your current code is, and I can't put any other way, unsafe garbage and not how good database access code is written in Java. I'm not sure what you mean with "have to follow the spec", but specifications usually describe what needs to be done, not how it should be implemented. – Mark Rotteveel Dec 28 '16 at 20:57
When i do the way you suggested I get NullPointerException and fails at this line : – dark_illusion_909099 Dec 28 '16 at 21:01
pstmt.setBinaryStream(1, fis); – dark_illusion_909099 Dec 28 '16 at 21:01
@jomin_george94 There is a way you could use a regular Statement with MySQL, but I certainly wouldn't recommend it. You would need to include the SQL Binary Literal as the value in the statement. If you are currently have a NPE, re-post the structure of your code now. – M. Rizzo Dec 28 '16 at 21:11
I have made the changes you suggessted but it still doesn't add the image into the database even with using the PreparedStatement – dark_illusion_909099 Dec 28 '16 at 21:30
Is your table named Picture or Pictures? Before you were listing it as Pictures. There must be something off in the code somewhere. – M. Rizzo Dec 28 '16 at 21:38

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

2 Answers2

Linked