Forum:

Fetching information from database in jsp

Greenhorn

Posts: 23

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Good day everyone Please i needs help.This code was used to fetch information from database.
whenever a correct id at BEANS.JSP is entered ,it will fetch every information from that row.
But the problem am having is that it will retrieve every information on that row but the Image cannot
be retrieve instead it will retrieve image link.Please how do i solve this problem using JSP only.

Thanks for help.

below is the code

-----------BEANS.JSP------------

-------------------BEANS.JSP--------------------------

<%@ page import="java.sql.*" %>
<% Class.forName("com.mysql.jdbc.Driver"); %>

<HTML>
    <HEAD>
        <TITLE>Fetching Data From a Database</TITLE>
    </HEAD>

<BODY>

<center>
        <font size="7" color="green">Fetching Data From a Database</font>

</center>

<% 
            Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/money", "root", "root");

Statement statement = connection.createStatement();

String email = request.getParameter("email");

ResultSet resultset = 
                statement.executeQuery("select * from login where id = '" + id + "'") ;

if(!resultset.next()) {
                out.println("SORRY, WE COULD NOT GET YOUR INFORMATIONS. ");
            } else {
           
        %>

<TABLE BORDER="1">
            <TR>
               <TH>YOUR SURNAME:</TH>
               <TH>YOUR FIRSTNAME:</TH>
               <TH>YOUR image:</TH>
                          </TR>
           <TR>
               <TD> <%= resultset.getString(1) %> </TD>
               <TD> <%= resultset.getString(2) %> </TD>
               <TD> <img src="rice.jsp?imgid=?<%=resultset.getBlob(3)%>" width="50" height="50"></TD>

</TR>
       </TABLE>
       <BR>
       <% 
           } 
       %>

<BODY bgcolor="#ffffcc">
<font size="5" color="green"><a href="login.jsp">CLICK HERE TO GO BACK</a></font>

</BODY>
</HTML>

Paul Clapham

Sheriff

Posts: 28401

100

I like...

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I'm not sure if I understand what you are trying to say. But that code up there looks like you're actually getting an image out of the database -- not a URL pointing to the image, but the actual image itself -- and then trying to send it to the client in a place where HTML requires you to put a URL pointing to the image.

Well, you can't do that. You are going to have to produce HTML which contains a URL which points to the image. Obviously the URL can't point to the database column, so it will have to point to a servlet which returns the image. It would have to have a parameter which identifies which image to return, and the servlet would have to set up the response so that the browser knew there was an image being sent, not HTML. Which is why it should be a servlet and not a JSP which returns the image.

Actually most of that code should be in a servlet, not in a JSP. But that's a separate topic. And your JDBC code should be using a PreparedStatement instead of the unreliable method of creating a query by concatenating strings, but that's another separate topic.

Java 8 (verified skill)
Skill verified by Paul Clapham

fredrick esedo

Greenhorn

Posts: 23

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

thanks for your reply i appreciate your help
Okay i develope this servlet code it retrieves all the informations on every row when a correct ID corresponding to that row is entered at
BEAN.JSP. Let assume that i have 2 rows in my database with ID 1,2 etc when i enter ID 1 i will get every information in that row
it works,but if enter ID 2 at BEANS.Jsp, before it will fetch that data at row 2 corresponding to ID 2 i entered it will require me to manually go to RICE.JSP and change the ID values eg to 2 ie id=2 otherwise it will keep on fetching all data at row 1.
Please sir how do i circumvent this problem so that any id i entered it will retrieve that row automatically with requiring me to be edicting the id manually eg id=1,id=2 and so on.

thanks

import java.sql.Blob;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Connection;
import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.sql.DriverManager;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class DisplayBlobExample extends HttpServlet{
  public void doGet(HttpServletRequest request, HttpServletResponse response) 
    throws IOException,ServletException {
    Blob image = null;
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;

String id = request.getParameter("id");  
    ServletOutputStream out = response.getOutputStream();
    try {
      Class.forName("com.mysql.jdbc.Driver");
      con = DriverManager.getConnection("jdbc:mysql://localhost/love?user=root&password=root");
      stmt = con.createStatement();
      rs = stmt.executeQuery("select * from login where id = '" + id + "'");
      if (rs.next()) {
        image = rs.getBlob(1);
      } else {
        response.setContentType("text/html");
        out.println("<html><head><title>Display Blob Example</title></head>");
        out.println("<body><h4><font color='red'>image not found for given id</font></h4></body></html>"); 
        return;
      }
      response.setContentType("image/gif");
      InputStream in = image.getBinaryStream();
      int length = (int) image.length();
      int bufferSize = 1024;
      byte[] buffer = new byte[bufferSize];
      while ((length = in.read(buffer)) != -1) {
        out.write(buffer, 0, length);
      }
      in.close();
      out.flush();
    } catch (Exception e) {
      response.setContentType("text/html");
      out.println("<html><head><title>Unable To Display image</title></head>");
      out.println("<body><h4><font color='red'>Image Display Error=" + e.getMessage() +
       "</font></h4></body></html>");
      return;
    } finally {
      try {
        rs.close();
        stmt.close();
        con.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}

----------------rice.jsp--------------------

<%@ page import="java.sql.*" %>
<% Class.forName("com.mysql.jdbc.Driver"); %>

<HTML>
    <HEAD>
        <TITLE>Fetching Data From a Database</TITLE>
    </HEAD>

<BODY>

<center>
        <font size="7" color="green">Fetching Data From a Database</font>

</center>

<% 
            Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/rose", "root", "root");

Statement statement = connection.createStatement();

String id = request.getParameter("id");

ResultSet resultset = 
                statement.executeQuery("select * from fast where id = '" + id + "'");

if(!resultset.next()) {
                out.println("SORRY, WE COULD NOT GET YOUR INFORMATIONS. ");
            } else {
           
        %>

<TABLE BORDER="1">
            <TR>
               <TH>YOUR PICTURE:</TH>
               <TH>YOUR ID:</TH>
                          </TR>
           <TR>
               <TD>
               <img src="http://localhost:8080/peter/DisplayBlobExample?id=1<%=resultset.getBlob(1)%>" width="100" height="100">
</TD>          
               </TD>

<BODY bgcolor="#ffffcc">
<font size="5" color="green"><a href="login.jsp">CLICK HERE TO GO BACK</a></font>

</BODY>
</HTML>

-------------------BEANS.JSP----------------------

Mark E Hansen

Ranch Hand

Posts: 650

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I think you're completely missing how to serve an image to a web application. Have a look at the ServletsFaq - there is an article on creating an Image Servlet which should help you which you can see here: http://faq.javaranch.com/java/ImageServlet

fredrick esedo

Greenhorn

Posts: 23

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Good day everyone, please when i run this code it keeps on telling me the error below whereas i have commons-upload.jar and common-io.jar file in my both directory TOMCAT/lib and FREA/WEB-INF/lib.
but if run servlet it will run and no such error will be displayed.Is there any other dependecy jar file i need to install so that this error below will stop occuring
thanks and have a wonderful day

<%@ page import="java.io.*,java.sql.*,java.util.*,java.text.*,java.text.SimpleDateFormat" %>
<html>
<%
int val =0;
String contentType = request.getContentType();
if ((contentType != null) && (contentType.indexOf("multipart/form-data") >= 0)) {
DataInputStream in = new DataInputStream(request.getInputStream());
int formDataLength = request.getContentLength();
byte dataBytes[] = new byte[formDataLength];
int byteRead = 0;
int totalBytesRead = 0;

while (totalBytesRead < formDataLength) {
byteRead = in.read(dataBytes, totalBytesRead, formDataLength);
totalBytesRead += byteRead;
}
String file = new String(dataBytes);
String saveFile = file.substring(file.indexOf("filename=\"") + 10);
System.out.println("saveFile=" + saveFile);
saveFile = saveFile.substring(saveFile.lastIndexOf("\\")+ 1,saveFile.indexOf("\""));
System.out.println("saveFile" + saveFile);
saveFile = file.substring(file.indexOf("filename=\"") + 10);
saveFile = saveFile.substring(0, saveFile.indexOf("\n"));
saveFile = saveFile.substring(saveFile.lastIndexOf("\\")+ 1,saveFile.indexOf("\""));
int lastIndex = contentType.lastIndexOf("=");
String boundary = contentType.substring(lastIndex + 1,contentType.length());
int pos;

pos = file.indexOf("filename=\"");
pos = file.indexOf("\n", pos) + 1;
pos = file.indexOf("\n", pos) + 1;
pos = file.indexOf("\n", pos) + 1;
int boundaryLocation = file.indexOf(boundary, pos) - 4;
int startPos = ((file.substring(0, pos)).getBytes()).length;
int endPos = ((file.substring(0, boundaryLocation)).getBytes()).length;

FileOutputStream fileOut = new FileOutputStream(saveFile);
fileOut.write(dataBytes, startPos, (endPos - startPos));
%>

<%
Connection con=null;
PreparedStatement pstatement = null;
String line = null;
String value=null;
String url = "jdbc:mysql://localhost:3306/";
String dbName = "esedo";
String driver = "com.mysql.jdbc.Driver";
String userName = "root"; 
String password = "root";
try{
StringBuilder contents = new StringBuilder();
BufferedReader input = new BufferedReader(new FileReader(saveFile));
while (( line = input.readLine()) != null){
contents.append(line);
}
value = contents.toString();
System.out.println("Value:"+value);
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(url+dbName,userName,password);
java.util.Date now = new java.util.Date();
String DATE_FORMAT = "yyyy-MM-dd hh:mm:ss";
SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
String strDateNew = sdf.format(now) ;

String queryString = "INSERT INTO file_tbl set file_data='"+value+"',file_date='"+strDateNew+"'";

//out.println(queryString);

pstatement=con.prepareStatement(queryString);

val = pstatement.executeUpdate();

if(val>0)
{
%>
<br><br>
<b>File <% out.println(saveFile); %> has been uploaded and inserted into Database at <%=strDateNew%>.</b>
<%
}

}
catch(Exception e)
{}
}
%>
</html>

Ayoma Wijethunga

Ranch Hand

Posts: 38

I like...

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

fredrick esedo wrote:Is there any other dependecy jar file i need to install so that this error below will stop occuring

No.. This has nothing to do with dependencies. The error is generated from here:

So do a print as mentioned below and check weather you have produced the name correctly after all those String manipulations.

Regards,

AyomaOnline - CodeGreed

Mark E Hansen

Ranch Hand

Posts: 650

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

fredrick esedo wrote: java.io.FileNotFoundException: at java.io.FileOutputStream.open(Native Method) at java.io.FileOutputStream.<init>(FileOutputStream.java:179) at java.io.FileOutputStream.<init>(FileOutputStream.java:70) at org.apache.jsp.upload_005fpage_jsp._jspService(upload_005fpage_jsp.java:94) at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70) at javax.servlet.http.HttpServlet.service(HttpServlet.java:803) at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)

It seems you're not trying very hard to debug this problem. In looking at this error, it's telling you that you have a FileNotFoundException when trying to open a FileOutputStream from within a JSP page. Doesn't that give you any clue at all about where the problem might be? Does your JSP page attempt to open a FileOutputStream anywhere? If so, what is the file name you're passing to it? Is the file name valid?

You should try harder to ShowSomeEffort.

By the way, it seemed originally you were trying to extract an image from the database and display it in the browser, but now you seem to be attempting to upload a file to the server. What's the deal? Has the problem changed? Perhaps you should create a new topic for the new problem.

If you're trying to upload files to the server, have a look at the FileUpload library from Apache Commons: http://commons.apache.org/fileupload/using.html

Finally, you really, really shouldn't be doing any Java programming in the JSP page itself. The JSP page should be used to gather user input and display results. The application logic should be in (or behind) a Servlet. Have your JSP page just gather the user input and POST to a Servlet, where you can do all your application logic. If you continue to try to execute your application logic in the JSP page, you're going to have a lot of difficulty.

fredrick esedo

Greenhorn

Posts: 23

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Thanks very much, the problem has being resolved

Mark E Hansen

Ranch Hand

Posts: 650

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Perhaps you could share the solution with everyone?