Solved

Douwload data from database

Posted on 2003-11-27
16
333 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
project group option in netbeans equivalent term in eclipse 1 61
Using Tomcat as raspberry pi java app server 13 287
countXY challenge 28 179
instanceof  operator in java 26 114
Invest in your employees with these five simple steps to improve employee engagement and retention.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Suggested Courses

751 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