How to store images in java derby database and display it on html/jsp page?

Question

I created this HTML page to accept the data from admin of page and store it in the database

And this is how the fetched data will be displayed on html.

Below is the retrieval code in JSP.

And how to Store an image in database directly while filling the form and how to show the image back into html page along with other data.

<%
Class.forName("org.apache.derby.jdbc.ClientDriver");
Connection con=DriverManager.getConnection("jdbc:derby://localhost:1527/SEM6 ","SEM6","SEM6");
Statement st=con.createStatement();
ResultSet rs=st.executeQuery("Select * from products");
%>   


<%     
    while(rs.next())
    {%>
        <div class="productblock">
            <div class="producttable">
            <table>
                <tr><a class="linkfill" href="products/<%= rs.getString("pid") %>.html"></a>
                <td colspan="2"><span class="red"><%= rs.getString("name") %></span></td>
                </tr>
                <tr>
        <td>ID:  <span class="red"><%= rs.getString("pid") %></span></td>
        <td rowspan="3"></td>
                </tr>
        <tr><td>Wattage: <span class="red"><%= rs.getString("watt") %></span></td></tr>
        <tr><td>Price: <span class="red"><%= rs.getString("price") %></span></td></tr>
        <tr><td colspan="2"><%= rs.getString("s_desc") %></td></tr> 
            </table>
            </div>
        </div>
        <%
    }%>

score 1 · Answer 1 · edited May 23 '17 at 12:33

You need to store your image in a binary lob.

This article describes how to create a binary lob in your table.

The servlet/jsp that receives the post of the form will load the posted image in the binary lob.

You need to write also another servlet that reads your image from the database.

In the html page you can use the servlet in the url of the image passing the ID of the blob to load.

For example, the url of the image can be something like:

<img src="/your-app-context/stream-image?id=blogID" >

or you can also use a search engine friendly url like:

<img src="/your-app-context/stream-image/magicmoon-downlight" >

where magicmoon-downlight is a unique name that identifies the table row with the blob.

Of course, stream-image is the servlet path configured in your web.xml.

The servlet will read the ID of the image from the url or the parameter passed, find the row in the table and stream the content of the binary back to the browser.

If you want to know how to stream the image, this post contains a good example (it reads the image from the file system, but it won't be difficult to adapt the code). Use the example suggested by @Jama

Please notice how he is setting the content type and the size of the image. This is important if you want to be compatible with all the browser and search engine friendly.

I have already created the table "products" with an "image" column of type BLOB. But I can't figure out how do I upload it in database like in the above form, how can I add browse image button? or something similar and after pressing submit button it will store all the form data along with the image in products table. — Vipin Yadav, Feb 21 '16 at 16:21
you need to write a servlet that accepts a multipart/form-data. How to do it depends on the version of the servlet container that you are using. You can start from: http://stackoverflow.com/questions/2422468/how-to-upload-files-to-server-using-jsp-servlet — Marco Altieri, Feb 21 '16 at 18:00

score 1 · Answer 2 · answered Feb 27 '16 at 06:01

I've used the FileInputStream to store the image file in Database. Here is what I have done in the additem.jsp file.

try {
String imgpath=request.getParameter("file");
Integer item_id=Integer.parseInt(request.getParameter("item_id"));
Class.forName("org.apache.derby.jdbc.ClientDriver");
Connection con=DriverManager.getConnection("jdbc:derby://localhost:1527/SEM6","SEM6","SEM6");

PreparedStatement psmnt = null;
FileInputStream fis;
File image = new File(imgpath);
psmnt = con.prepareStatement("insert into products(image)"+"values(?)");
fis = new FileInputStream(image); 
psmnt.setString(1, item_name);
psmnt.setBinaryStream(2, (InputStream)fis, (int)(image.length()));
int flag= psmnt.executeUpdate();
if(flag>0)
    {
        %> <jsp:forward page="added.html"></jsp:forward> <%
    }
    else
    {
        out.print("Error");

    }
}

catch (Exception e){
    out.print(e);
}

But how do I retrieve the image from the database and display it on an HTML page?

score 1 · Answer 3 · edited Mar 27 '16 at 09:12

To retrieve the image from the database and display it on an HTML page, I've created one html file with the <img> tag

<img src="getImage.jsp?pid=1001">

Then I created the following getImage.jsp file

<%@page import="java.io.*,java.util.*,java.sql.*;"%>


<%  
int pid =Integer.parseInt(request.getParameter("pid"));


try {

    Class.forName("org.apache.derby.jdbc.ClientDriver");

    Connection conn = DriverManager.getConnection("jdbc:derby://localhost:1527/SEM6","user","pwd");
    Statement stmt = (Statement) conn.createStatement();
ResultSet rs;
    rs = stmt.executeQuery("select image from products where pid ="+pid);

    if (rs.next()) {
        byte[] imgData = rs.getBytes("image");
        System.out.println(imgData);
        response.setContentType("image/jpg");

        OutputStream os = response.getOutputStream();
        os.write(imgData);
        os.flush();
        os.close();

    }


  } catch (SQLException ex) {
            ex.printStackTrace();
} 
%>

When the getImage.jsp is called the jsp returns the output as a Image file. And that concludes the aim of this question.

How to store images in java derby database and display it on html/jsp page?

3 Answers3