Retrieve an Image stored as BLOB on a MYSQL DB

Question

I'm trying to create a PDF based on the information that resides on a database. Know I need to retrieve a TIFF image that is stored as a BLOB on a mysql database from Java. And I don't know how to do it. The examples I've found shows how to retrieve it and save it as a File (but on disk) and I needed to reside on memory.

Table name: IMAGENES_REGISTROS

BLOB Field name: IMAGEN

Any Ideas?

see my updated answer - turned out there is an alternative way to do it :) — Bozho, Jan 27 '10 at 23:07

Bozho · Accepted Answer · 2010-01-28T12:11:06.470

21

On your ResultSet call:

Blob imageBlob = resultSet.getBlob(yourBlobColumnIndex);
InputStream binaryStream = imageBlob.getBinaryStream(0, imageBlob.length());

Alternatively, you can call:

byte[] imageBytes = imageBlob.getBytes(1, (int) imageBlob.length());

As BalusC noted in his comment, you'd better use:

InputStream binaryStream = resultSet.getBinaryStream(yourBlobColumnIndex);

And then the code depends on how you are going to read and embed the image.

edited Jan 28 '10 at 12:11

answered Jan 27 '10 at 21:28

Bozho

588,226
146
1,060
1,140

Thanks, just a comment. imageBlob returns a Long. And getBytes expect an Integer. I parse (Integer.ParseInt..) the Long, and it works. I don't know if eventually that'll gonna bring problems. Is there any other way? thanks – Sheldon Jan 27 '10 at 21:56
3

Don't use `ResultSet#getBlob()` or `ResultSet#getBytes()`. Just use `ResultSet#getBinaryStream()`. – BalusC Jan 28 '10 at 11:58
is it just a convenient method, or there is something more to it? – Bozho Jan 28 '10 at 12:00
I guess because it is expected that the data is huge and you'll get an OutOfMemory exception – Bozho Nov 26 '13 at 11:32

score 2 · Answer 2 · edited Apr 27 '18 at 10:41

final String dbURL = "jdbc:mysql://localhost:3306/portfolio";
    final String dbUser = "root";
    final String dbPass = "";

    Connection conn = null;
    Statement stmt = null;

    try {
        //DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        Class.forName("com.mysql.jdbc.Driver");

        conn = DriverManager.getConnection(dbURL, dbUser, dbPass);
        System.out.println("db connected");
        stmt = (Statement) conn.createStatement();

        ResultSet rs1;
        rs1 = stmt.executeQuery("select profileImage from tbl_welcome where id = 1117");

        if (rs1.next()) {
            byte[] imgData = rs1.getBytes("profileImage");//Here....... r1.getBytes() extract byte data from resultSet 
            System.out.println(imgData);
            response.setHeader("expires", "0");
            response.setContentType("image/jpg");

            OutputStream os = response.getOutputStream(); // output with the help of outputStream 
            os.write(imgData);
            os.flush();
            os.close();

        }
    } catch (SQLException ex) {
        // String message = "ERROR: " + ex.getMessage();
        ex.printStackTrace();
    } finally {
        if (conn != null) {
            // closes the database connection
            try {
                conn.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

You should edit your answer to include that information, not add a comment. More explanation is good - code-only answers are discouraged. — Ajean, Oct 05 '15 at 15:41

score 2 · Answer 3 · answered Mar 30 '18 at 07:52

2

imagebytes = rs.getBytes("images");
image=getToolkit().createImage(imageBytes);
Image img = image.getScaledInstance(100,100,Image.SCALE_SMOOTH);
ImageIcon icon=new ImageIcon(img);
jLabel6.setIcon(icon);

Try this code to get adjustable image from blog Mysql in netbeans

answered Mar 30 '18 at 07:52

Ratul Arora

31
4

It's not obvious how this creates a PDF, or matches the database parameters in the original posting. – rwp Mar 30 '18 at 08:28
who is image variable? – gumuruh Oct 08 '20 at 06:04

score 1 · Answer 4 · answered May 20 '16 at 05:56

private void loadFileDataBlobFromDataBase()
             {
            List<Blob> bFile = jdbcTemplate.query(sql, new RowMapper<Blob>() {
                @Override
                public Blob mapRow(ResultSet rs, int rowNum)
                        throws SQLException {
                    return rs.getBlob(1);
                }
            });
            if (bFile != null && bFile.size() > 0) {
                bufReader = new BufferedReader(new InputStreamReader(bFile.get(
                        0).getBinaryStream()));
            }
            if (null != bufReader) {
                dataVO record = null;
                String lineStr = bufReader.readLine();
                record = (dataVO) lineMapper.mapLine(lineStr, 1);               
            }
        }
    }

Retrieve an Image stored as BLOB on a MYSQL DB

4 Answers4

Linked

Related