How to export a database table to a specified file format. This technique uses a servlet business object and a data source object.
You can run this technique code from:
NOTE First make sure that database is running on your localhost SilverStream server | |
|
The export data feature of the DataXpress sample application implements the following objects:
Contains an AgaData called AgaSource, which is bound to a data source object called dsoEditTableData | ||
Instantiates JDBCDataSource, which implements the AgiDataSource object for the DataXpress application | ||
Accesses the data source and populates the AgaData with the rows to be exported This object instantiates several utility objects in the JDBC package; for more information, see Developing a DSO for use by Multiple Clients |
When the user chooses the Submit button, the page gets the data the user entered on the form and stores it in a has table. Then it invokes the servlet URL using the showPage() method, passing the hashtable to the servlet:
Related reading
private void handle_btnSubmit_pageActionPerformed(ActionEvent evt)
throws Exception
{
// Reset the output box
lblResults.setText("");
StringBuffer sbResults =new StringBuffer();
Hashtable htParms = new Hashtable();
try {
// Get the db and table indexes
String sTableName =
chSourceTable.getItem(
chSourceTable.getSelectedIndex());
String sDBName =
chSourceDB.getItem(
chSourceDB.getSelectedIndex());
if (sTableName==null)
{
sbResults.append("You must specify a table to
export from");
return;
}
// Add items needed by the servlet to the hashtable
htParms.put("srcdbname",sDBName);
htParms.put("srctablename",sTableName);
htParms.put("headers",chHeaders.getValue());
sbResults.append("<p>Table:");
sbResults.append(sTableName);
htParms.put("type",chExportType.getValue());
// Call the servlet, passing the hashtable
showPage("../../saveas/"+sTableName,null,null,htParms);
}
catch (Exception e) {
sbResults.append(" Error - " + e.toString());
}
finally
{
// Display the table name on the page
lblResults.setText(sbResults.toString());
}
}
The servlet gets the hashtable parameters and invokes the data source object dsoEditTableData, passing the database and table values. Then it sets the ServletResponse content type according to the file type selected by the user. Finally, it sets the servlet output by calling the local method saveAs():
public void service(javax.servlet.ServletRequest req, javax.servlet.ServletResponse res)
throws javax.servlet.ServletException, java.io.IOException
{
try
{
AgoHttpRequestEvent hRequest = (AgoHttpRequestEvent) req;
HttpServletResponse hResponse = (HttpServletResponse) res;
// Get the values passed in; need to cast them
String sdbname = (String)
((AgoHttpRequestEvent)req).
getParameterAsObject("srcdbname");
String sTableName = (String)
((AgoHttpRequestEvent)req).
getParameterAsObject("srctablename");
String sType = (String)
((AgoHttpRequestEvent)req).
getParameterAsObject("type");
Boolean bHeaders = new Boolean((String)
((AgoHttpRequestEvent)req).
getParameterAsObject("headers"));
// Get the AgaData
AgaData agaSource = hRequest.getAgaData("agaSource");
if (agaSource==null) System.out.println("agaSource is null");
Hashtable htParms = new Hashtable();
// Put db and table values in new hashtable
htParms.put ("DBNAME", sdbname);
htParms.put ("TABLENAME", sTableName);
// Invoke the dso, passing the hashtable
Vector vReturn = (Vector) agaSource.invokeQuery (htParms);
// Set the appropriate http content type
if (sType.equals("HTML"))
{
hResponse.setContentType("text/html; charset=iso-8859-1");
}
else
if (sType.equals("CSV"))
{
hResponse.setContentType("application/octet-stream");
hResponse.setHeader("Content-Disposition", "attachment;
filename=\""+ sTableName +".csv\"");
}
else
if (sType.equals("TAB"))
{
hResponse.setContentType("application/octet-stream");
hResponse.setHeader("Content-Disposition", "attachment;
filename=\""+ sTableName +".tab\"");
}
else
if (sType.equals("EXCEL"))
{
hResponse.setContentType("application/vnd.ms-excel");
}
hResponse.setStatus(hResponse.SC_OK);
// Set the servlet response ...
ServletOutputStream stream = hResponse.getOutputStream();
// Pass parameters to local method to generate the response
// Pass AgSource as an AgiRowCursor object
saveAs(stream,sType,bHeaders.booleanValue(),(AgiRowCursor)
agaSource);
}
catch (Exception e)
{
System.out.println("service -- " + e);
}
}
The saveAs() method reads the file type parameter and calls the appropriate method to generate the file. Then it stores the results of the generate method in a String variable, writes the String to the ServletOutputStream, and returns it to the caller:
public int saveAs(ServletOutputStream fos, String sfileType, boolean headers, AgiRowCursor m_rowcursor)
{
try
{
int rc = 0;
String fileData = null;
// Call one of the generate methods
if (sfileType.equals("CSV"))
fileData =generateCSV(headers,m_rowcursor);
else if (sfileType.equals("TAB"))
fileData = generateTAB(headers,m_rowcursor);
else if (sfileType.equals("EXCEL"))
fileData = generateTAB(headers,m_rowcursor);
else if (sfileType.equals("HTML"))
fileData = generateHTML(headers,m_rowcursor);
if (fileData == null)
rc = -1;
// Write the results to the output stream
fos.write (fileData.getBytes());
return rc;
}
catch (Exception _e)
{
System.out.println ("save as" + _e);
return -1;
}
}
The saveAs() method in the invoked evt calls the method to generate the file. This section describes the generation method for the HTML option.
First the generateHTML() method generates the format for an HTML table, then it creates a StringBuffer to store the row data.
Then, in the second half of the try block, the code loops through the AgiRowCursor object, filling each row of the html table with the row data. Each row is appended to the StringBuffer as a new HTML table row. After the try block, the StringBuffer with all of the rows is returned to the calling method.
public String generateHTML(boolean headers, AgiRowCursor m_agiRowCursor)
{
// Set up html formatting ...
String hdrTD = "<TD VALIGN=TOP BGCOLOR=\"#2C547C\"><FONT SIZE=3
COLOR=\"#FFFFFF\" FACE=\"Helvetica\"><SPAN STYLE=\"font-
size:14;\">";
String hdrTDEnd = "</SPAN></FONT></TD>";
String rowTD = "<TD VALIGN=TOP><FONT SIZE=3
FACE=\"Helvetica\"><SPAN STYLE=\"font-size:14;\">";
String rowTDColor = "<TD VALIGN=TOP BGCOLOR=\"#d2d2d2\">
<FONT SIZE=3 FACE=\"Helvetica\"><SPAN STYLE=\"font-size:14;\">";
String rowTDEnd = "</SPAN></FONT></TD>";
// Use to alternate row shading
boolean toggle = false;
try
{
// Create stringbuffer and generate the file...
// System.out.println ("generating HTML");
StringBuffer sb = new StringBuffer ();
sb.append("<HTML>");
sb.append("<BODY BACKGROUND=\"../SilverStream/
Objectstore/Images/ExamplesBackground.JPG\">");
int colCount = m_agiRowCursor.getPropertyCount();
sb.append ("<TABLE BORDER=1 BGCOLOR=\"#ffffff\"
CELLPADDING=5 CELLSPACING=1 WIDTH=100% HEIGHT=0>\n");
// Include headers from db table?
if (headers)
{
sb.append ("<tr>");
for (int y=0; y<colCount; y++)
{
sb.append (hdrTD + m_agiRowCursor.
getPropertyName (y) + hdrTDEnd);
}
sb.append ("</tr>\n");
}
// System.out.println ("generating HTML2");
// Tell the dso to start feeding the AgiRowCursor...
boolean rc = m_agiRowCursor.gotoFirst();
int rowcount = 0;
while (rc)
{
// Keep count of rows added
rowcount++;
sb.append ("<tr>");
// Get all of the rows from the table
for ( int x = 0; x < colCount; x++)
{
Object obj = m_agiRowCursor.getProperty (x);
if (obj==null)
obj = (String) "null";
else if (obj.equals(""))
obj = "empty";
// Alternate row shading
if (!toggle)
sb.append (rowTD + obj.toString() + rowTDEnd);
else
sb.append (rowTDColor + obj.toString()
+ rowTDEnd);
} // end of for block
// Add html end of row marker
sb.append ("</tr>\n");
toggle = !toggle;
// Get next row from the AgiRowCursor
rc = m_agiRowCursor.gotoNext();
} // End of while block
// Add html end-of-table marker
sb.append ("</table>");
// Add text with row count
sb.append("<p>Rows Retrieved: "+ new Integer(rowcount) );
// Add EOF html marker
sb.append("</BODY></HTML>");
// Return table as string to caller
return sb.toString();
} // End of try block
catch (Exception _e)
{
System.out.println ("gen html:" + _e);
return null;
}
}