Retrieve multiple images from MySQL

Question

How to retrieve multiple blob images from MySQL database in JSP page?

Answer 1

I suggest you address that as two problems. There are several questions and answer related to both.

1. How to load blob from MySQL

   See for instance Retrieve image stored as blob

2. How to display image dynamically

   See for instance Show thumbnail dynamically

Answer 2

Let's see in steps what should happen:

• To display an image in HTML, you need the HTML <img> element.
• To let it locate an image, you need to specify its src attribute.
• The src attribute needs to point to a valid URL and specify the image as request pathinfo (e.g. /images/foo.gif) or as request parameter (e.g. /images?id=1).
• In JSP/Servlet world, you can let a Servlet listen on a certain url-pattern so that you can just execute some Java code on specific URL's.
• Images are binary data and are to be obtained as an InputStream from the DB, the JDBC API offers the ResultSet#getBinaryStream() for this.
• In the Servlet you can just write this InputStream to the OutputStream of the response the usual Java IO way.
• The client side needs to be instructed that the data should be handled as an image, thus at least the Content-Type response headers needs to be set as well.

That should be it. It almost writes code itself. Let's start with HTML (in JSP):

<img src="images/foo.gif">
<img src="images/bar.gif">
<img src="images/baz.gif">

Oh, you can if necessary also dynamically generate links with EL:

<c:forEach items="${imagenames}" var="imagename">
    <img src="images/${imagename}">
</c:forEach>

Then define/create a servlet which listens on /images/:

<servlet>
    <servlet-name>imageServlet</servlet-name>
    <servlet-class>com.example.ImageServlet</servlet-class>
</servlet>
<servlet-mapping>
    <servlet-name>imageServlet</servlet-name>
    <url-pattern>/images/*</url-pattern>
</servlet-mapping>

"Plain vanilla" requests (such as image links) are obviously GET, thus just do the thing in doGet():

// content=blob, contenttype=varchar(255), contentlength=integer, name=varchar(255).
private static final String SQL_FIND = "SELECT content, contenttype, contentlength FROM image WHERE name = ?";

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    // Prepare.
    String name = request.getPathInfo().substring(1); // Returns "foo.gif". You may want to do nullchecks here to avoid NPE's.
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;

    try {
        // Query DB.
        connection = database.getConnection();
        statement = connection.prepareStatement(SQL_FIND);
        statement.setString(1, name);
        resultSet = statement.executeQuery();

        if (resultSet.next()) {
            // Image found, prepare response and send it.
            response.setContentType(resultSet.getString("contenttype"));
            response.setContentLength(resultSet.getInt("contentlength"));
            response.setHeader("Content-Disposition", "inline;filename=\"" + name + "\"");
            BufferedInputStream input = null;
            BufferedOutputStream output = null;
            try {
                input = new BufferedInputStream(resultSet.getBinaryStream("content"));
                output = new BufferedOutputStream(response.getOutputStream());
                for (int data; (data = input.read()) > -1;) output.write(data);
            } finally {
                if (output != null) try { output.close(); } catch (IOException logOrIgnore) {}
                if (input != null) try { input.close(); } catch (IOException logOrIgnore) {}
            }
        } else {
            // No image found, send HTTP 404.
            response.sendError(HttpServletResponse.SC_NOT_FOUND);
        }
    } catch (SQLException e) {
        throw new ServletException("Something failed at SQL/DB level.", e);
    } finally {
        if (resultSet != null) try { resultSet.close(); } catch (SQLException logOrIgnore) {}
        if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
        if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
    }
}

That's it. Another example and more hints can be found in this article.