Solved

Douwload data from database

Posted on 2003-11-27
16
321 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
Comment Utility
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
Comment Utility
Hi Sandra.

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

Javier
0
 
LVL 14

Expert Comment

by:kennethxu
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Hi Sandra.

Can you post the code where it crash??

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

Javier
0
 

Author Comment

by:sandrals
Comment Utility
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
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 14

Expert Comment

by:kennethxu
Comment Utility
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
Comment Utility
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
Comment Utility
I'm working with Tomcat 4.1.
Sandra
0
 
LVL 15

Expert Comment

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

0
 
LVL 15

Expert Comment

by:jimmack
Comment Utility
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
Comment Utility
>>>>
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
Comment Utility
Thanks all of you!
Sandra
0
 
LVL 14

Expert Comment

by:kennethxu
Comment Utility
pleasure :-)
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article will show you how to create an ISO CD-ROM/DVD-ROM image (*.iso), and MD5 checksum signature, for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5). It's a good idea to compare checksums, because many installations fail because of a corr…
Veeam Backup & Replication has added a new integration – Veeam Backup for Microsoft Office 365.  In this blog, we will discuss how you can benefit from Office 365 email backup with the Veeam’s new product and try to shed some light on the needs and …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 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

8 Experts available now in Live!

Get 1:1 Help Now