Hi guys,
I am writing JSP to retrieve data from database and export to the user as csv or zip file. It has to be binary. I would like to a download dialog to show up and allow users to choose where to save the exported file.
Funcitonally, it works fine althrough I get an error page saying nothing to display. But it always complains that IllegalStateException: getOutputStream() has already been called for this session. I have done much research and testing, but....in vain.
Hope somebody could help me with this. so frustrated that I am about to explode.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="application/octet-stream">
<title>Load Page</title>
</head>
<body >
<% OutputStream outRes = response.getOutputStream( ); %><%@page contentType="application/octet-stream"%><%
%><%@page pageEncoding="UTF-8" import="java.sql.*,java.io.*,java.util.*,java.util.zip.*,java.math.BigDecimal;"%><%
%><%
try {
Class.forName(ResourceBundle.getBundle("map").getString("driver"));
} catch (ClassNotFoundException ex) {
//ex.printStackTrace();
return;
}
String EXPORT_DIR = ResourceBundle.getBundle("map").getString("SUBMITTAL_EXPORT_DIR");
String EXPORT_NAME = ResourceBundle.getBundle("map").getString("SUBMITTAL_NAME");
String EXPORT_ZIP_NAME = ResourceBundle.getBundle("map").getString("SUBMITTAL_ZIP_NAME");
String sqlQuery = "SELECT EMP_ID, EMP_NAME FROM EMP";
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
OutputStream expStream = null;
boolean success = false;
try {
//out.println("<p>Connecting to the source database ...");
// establish database connection
conn = DriverManager.getConnection(
ResourceBundle.getBundle("map").getString("sqlurl"),
ResourceBundle.getBundle("map").getString("ORACLE_DBUSER"),
ResourceBundle.getBundle("map").getString("ORACLE_DBPASSWORD") );
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery(sqlQuery);
//out.println("Done</p>");
// open a file to write
File exportFile = new File(EXPORT_DIR + File.separatorChar + EXPORT_NAME);
if (exportFile.exists()) exportFile.delete();
exportFile.createNewFile();
expStream = new BufferedOutputStream(new FileOutputStream(exportFile));
// iterate all records to save them to the file
//out.print("<p>Exporting the table data ...");
while(rs.next()) {
String recordString = "";
ResultSetMetaData metaData = rs.getMetaData() ;
int colCount = metaData.getColumnCount();
for(int i=1; i<=colCount; i++) {
int colType = metaData.getColumnType(i);
switch(colType) {
case Types.CHAR: {
String sValue = rs.getString(i);
if (rs.wasNull())
recordString += ("'',");
else
recordString += ("'"+ sValue + "',");
break;
}
case Types.VARCHAR: {
String sValue = rs.getString(i);
if (rs.wasNull())
recordString += ("'',");
else
recordString += ("'"+ sValue + "',");
break;
}
case Types.FLOAT: {
float fValue = rs.getFloat(i);
if (rs.wasNull())
recordString += (",");
else
recordString += (fValue + ",");
break;
}
case Types.DOUBLE: {
double dbValue = rs.getDouble(i);
if (rs.wasNull())
recordString += (",");
else
recordString += (dbValue + ",");
break;
}
case Types.INTEGER: {
int iValue = rs.getInt(i);
if (rs.wasNull())
recordString += (",");
else
recordString += (iValue + ",");
break;
}
case Types.NUMERIC: {
BigDecimal bdValue = rs.getBigDecimal(i);
if (rs.wasNull())
recordString += (",");
else
recordString += (bdValue + ",");
break;
}
default:
/*
out.println("<p><font color=#ff0000> Unidentified Column Type "
+ metaData.getColumnTypeName(i) + "</font></p>"); */
success = false;
return;
}
}
recordString = recordString.substring(0, recordString.length()-1);
expStream.write(recordString.getBytes());
expStream.write((new String("\n")).getBytes());
}
expStream.flush();
//out.println("Done</p>");
//out.println("<p>Data have been exported to " + filepath + "</p>");
success = true;
} catch (SQLException ex) {
//out.println(ex.getStackTrace());
} catch (IOException ex) {
//out.println(ex.getStackTrace());
} finally {
if (expStream != null) {
try {
expStream.close();
} catch (IOException ex) {
// out.println(ex.getStackTrace());
}
}
if (rs != null) {
try {
rs.close();
} catch(SQLException ex) {
//out.println(ex.getStackTrace());
}
}
if (stmt != null) {
try {
stmt.close();
} catch(SQLException ex) {
// out.println(ex.getStackTrace());
}
}
if(conn != null) {
try {
conn.close();
} catch(SQLException ex) {
// out.println(ex.getStackTrace());
}
}
}
if (!success) return;
/*
* compress the exported CSV file if necessary
*/
int DATA_BLOCK_SIZE = 1024;
if (request.getParameter("zip")!=null && request.getParameter("zip").equalsIgnoreCase("true")) {
success = false;
BufferedInputStream sourceStream = null;
ZipOutputStream targetStream = null;
try {
File zipFile = new File(EXPORT_DIR + File.separatorChar + EXPORT_ZIP_NAME);
if (zipFile.exists()) zipFile.delete();
zipFile.createNewFile();
FileOutputStream fos = new FileOutputStream ( zipFile );
targetStream = new ZipOutputStream ( fos );
targetStream.setMethod ( ZipOutputStream.DEFLATED );
targetStream.setLevel ( 9 );
/*
* Now that the target zip output stream is created, open the source data file.
*/
FileInputStream fis = new FileInputStream ( EXPORT_DIR + File.separatorChar + EXPORT_NAME );
sourceStream = new BufferedInputStream ( fis );
/* Need to create a zip entry for each data file that is read. */
ZipEntry theEntry = new ZipEntry ( EXPORT_DIR + File.separatorChar + EXPORT_NAME );
/*
* Before writing information to the zip output stream, put the zip entry object
*/
targetStream.putNextEntry ( theEntry );
/* Add comment to zip archive. */
//targetStream.setComment( "comment" );
/* Read the source file and write the data. */
byte[] buf = new byte[DATA_BLOCK_SIZE];
int len;
while ( ( len = sourceStream.read ( buf, 0, DATA_BLOCK_SIZE ) ) >= 0 ) {
targetStream.write ( buf, 0, len );
}
/*
* The ZipEntry object is updated with the compressed file size,
* uncompressed file size and other file related information when closed.
*/
targetStream.closeEntry ();
targetStream.flush ();
success = true;
} catch ( FileNotFoundException e ) {
//e.printStackTrace ();
} catch ( IOException e ) {
//e.printStackTrace ();
} finally {
try {
if (sourceStream != null)
sourceStream.close ();
if (targetStream != null)
targetStream.close ();
} catch(IOException ex) {
//ex.printStackTrace();
}
}
if (!success) return;
}
/*
* prompt the user to download the file
*/
//response.setContentType("text/plain");
//response.setContentType( "application/octet-stream" );
InputStream inStream = null;
//OutputStream outRes = null;
try {
if (request.getParameter("zip")!=null && request.getParameter("zip").equalsIgnoreCase("true")) {
response.setHeader("Content-Disposition", "attachment;filename=" + EXPORT_ZIP_NAME);
inStream = new BufferedInputStream(new FileInputStream(EXPORT_DIR + File.separatorChar + EXPORT_ZIP_NAME));
}
else {
response.setHeader("Content-Disposition", "attachment;filename=" + EXPORT_NAME);
inStream = new BufferedInputStream(new FileInputStream(EXPORT_DIR + File.separatorChar + EXPORT_NAME));
}
out.clearBuffer();
//outRes = response.getOutputStream( );
byte[] buf = new byte[4 * DATA_BLOCK_SIZE]; // 4K buffer
int bytesRead;
while ((bytesRead = inStream.read(buf)) != -1) {
outRes.write(buf, 0, bytesRead);
}
outRes.flush();
/*
int byteBuf;
while ( (byteBuf = inStream.read()) != -1 ) {
out.write(byteBuf);
}
out.flush();
*/
} catch (IOException ex) {
//ex.printStackTrace();
}
finally {
try {
if (inStream != null)
inStream.close();
if (outRes != null)
outRes.close();
} catch(IOException ex) {
//ex.printStackTrace();
}
}
%>
</body>
</html>