Solved

Douwload data from database

Posted on 2003-11-27
16
322 Views
Last Modified: 2010-04-01
In my web application I want to give an option to user to download data from a database Oracle to an Excel file( or csv). The amount of data to retrieve from db is big. How's the better way to do that ? I'm working with JSP and Java.
SS
0
Comment
Question by:sandrals
  • 5
  • 4
  • 4
  • +2
16 Comments
 
LVL 15

Expert Comment

by:jimmack
ID: 9830869
If you prepare the data as though you are going to display an HTML table, but set the content type of the response to Excel, the client system should open Excel and read the data into it directly:

<%@ page contentType="application/vnd.ms-excel"%>

<TABLE ...>

</TABLE>
0
 
LVL 6

Expert Comment

by:jarasa
ID: 9830888
Hi Sandra.

You want the data displayed on the browser or to be saved as file?

Javier
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9831322
I prefer to use servlet to generate the csv file. just loop through the resultset and every column to construct the csv file like:

for every row in resultset
  for every column in the row
    out.println( rs.getString(columnNumber)  + "," );

you can find the number of column in rs.getResultSetMetaData()
0
 
LVL 92

Expert Comment

by:objects
ID: 9833787
Use a servlet and return the data in whatever format you require, jsp is intended for returning textual data.
POI can be used to generate Excel file:
http://jakarta.apache.org/poi/
0
 

Author Comment

by:sandrals
ID: 9873600
Thank you, for your help!
I'm using servlet to generate cvs file, but the problem is that I can't fetch all the information retrieved from the resultset. I'm expecting 40.000 rows but the program crash for 10.000 rows.
Do you know what's hapennig.

Sandra
0
 
LVL 6

Expert Comment

by:jarasa
ID: 9873868
Hi Sandra.

Can you post the code where it crash??

How are you sending the result.... etc.

Javier
0
 

Author Comment

by:sandrals
ID: 9874691
Hi Jarasa!

This is an code example.
The problem is when the code executes the while(rs.next()){...} statement, it never finish  resultset fetch, when I want to retrieve a big amount of data.
In a certain point it just stop, and I don't know how to get the exception.
Could it be a memory problem?


...
try
{

      PreparedStatement ps = null;
      ResultSet rs= null;
      ArrayList LIST = new ArrayList();

      String SQL = SELECT ......

      ps = conn.prepareStatement(SQL);
      rs = ps.executeQuery();

      while (rs.next())
      {
            BeanX ITEM = new BeanX();
            ITEM.setY( rs.getInt(1) );
            ITEM.setZ (rs.getString(2));
            ...
            LIST.add(ITEM)
      }                                        

      return LIST;
}
catch (SQLException e){
      e.printStackTrace();
      throw new RuntimeException("error.unexpected");
      }
            finally {
            try {
                    if (ps != null) ps.close();
                    if (rs != null) rs.close();
                }
                catch (SQLException e){
                    e.printStackTrace();
                    throw new RuntimeException("error.unexpected");
                }
}
0
 
LVL 15

Expert Comment

by:jimmack
ID: 9874760
The exception output should be being recorded in your servlet container log (eg. for Tomcat anything to System.out goes to catalina.log by default).  If you're not using Tomcat, please specify which servlet container you're using and one of the other experts should be able to tell you where the output goes.

You shouldn't need to throw the RuntimeExceptions either.

It could be a memory problem, especially with the amount of data you're talking about, but you need to find the exception output to really determine this.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 14

Expert Comment

by:kennethxu
ID: 9874844
I believe the error is because you ran out of memory. For such a big volume data that you are processing, it is strongly recommended not to store the data in server memory, you should process each row and send to client directory instead of store them in a list:

---------- avoid doing this ----
     while (rs.next())
     {
          BeanX ITEM = new BeanX(); // this is very expensive
          ITEM.setY( rs.getInt(1) );
          ITEM.setZ (rs.getString(2));
          ...
          LIST.add(ITEM) // this takes a lot of server memory
     }                                        
---------- try something like this ------------

     BeanX ITEM = new BeanX();
     while (rs.next())
     {
          ITEM.reset( ); // to clear data if needed.
          ITEM.setY( rs.getInt(1) );
          ITEM.setZ (rs.getString(2));
          ...
          out.println( ITEM.getY() + "," + ITEM.getZ() .... );
     }                                        
0
 

Author Comment

by:sandrals
ID: 9904340
I have tried to do something like that, but It doesn't write nothing at all.
How could I set the content type of the response to Excel?
...
javax.servlet.jsp.JspWriter out;
javax.servlet.jsp.PageContext pageContext;
out = pageContext.getOut();
BeanX ITEM = new BeanX();
         
while (rs.next())
     {
          ITEM.setX( rs.getInt(1) );
          ITEM.setY (rs.getString(2));
          ...
          out.println( ITEM.getX() + "," + ITEM.getY() .... );
     }      



Sandra



0
 

Author Comment

by:sandrals
ID: 9904354
I'm working with Tomcat 4.1.
Sandra
0
 
LVL 15

Expert Comment

by:jimmack
ID: 9904428
response.setContentType("application/vnd.ms-excel");

0
 
LVL 15

Expert Comment

by:jimmack
ID: 9904441
If you want to set the content type from within a JSP, see my first comment at the top of this thread.
0
 
LVL 14

Accepted Solution

by:
kennethxu earned 250 total points
ID: 9904520
>>>>
javax.servlet.jsp.JspWriter out;
javax.servlet.jsp.PageContext pageContext;
out = pageContext.getOut();
>>>>
Above doesn't seems to be right. r u doing as servlet or jsp, I don't think you'll be able to get proper csv file using jsp unless you are ok with blank lines in your csv file.

in servlet:
PrintWriter out = new PrintWriter( request.getOutputStream() );

if you want to use jsp, then the out var is alread there and you don't need to do anything.

and jimmack is absolutely right on content type.
0
 

Author Comment

by:sandrals
ID: 9926731
Thanks all of you!
Sandra
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9930936
pleasure :-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Cloud-based technologies and services will continue to grow in popularity in 2017 thanks to the simple, scalable and cost-effective solutions they deliver. Here are three areas where cloud adoption is poised to really take off.
In 2017, ransomware will become so virulent and widespread that if you aren’t a victim yourself, you will know someone who is.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now