Meaningless Drivel is fun!*
The moose likes Java in General and the fly likes Resultset to flat file. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Java in General
Bookmark "Resultset to flat file." Watch "Resultset to flat file." New topic
Author

Resultset to flat file.

Richard Miranda
Greenhorn

Joined: Aug 24, 2000
Posts: 14
I'm writing a program to write a database Resultset to a flat file. I figure there must be several people who have written a java program performing the same function. Its time we posted a perfect program to perform this, on this forum.

I've pasted a program written by me and would like to invite criticism, suggestions for improvement of design, performance, etc.

Thanks, Richard.


import java.sql.*;
import java.io.*;
import java.util.*;

/**
* @author Richard Miranda.
* Created on Aug 9, 2004
* Java program to write result set to flat file.
*/
public class RunExtract {

/**
* Generates a flat file from a database table.
* Pass the properties file as a parameter.
* @param args : args[0] The name of the properties file.
*/
public static void main(String[] args) {

java.util.Date timestamp = new java.util.Date();
System.out.println("Program start time : " + timestamp);

Writer out = null;
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;

try {

/** Check if the properties filename is passed as a parameter.*/
if (args.length < 1) {
System.err.println("Usage java <properties file>");
System.exit(0);
}
/**Load properties. */
Properties properties = new Properties();
try {
properties.load(new FileInputStream(args[0]));
} catch (IOException e) {
System.err.println(
"Unable to find properties file..." + args[0]);
System.exit(1);
}

String db_url = properties.getProperty("db_url");
String jdbc_driver = properties.getProperty("jdbc_driver");
String db_userid = properties.getProperty("db_userid");
String db_password = properties.getProperty("db_password");
String output_file = properties.getProperty("output_file");
String error_filename = properties.getProperty("error_filename");
String log_filename = properties.getProperty("log_filename");
String sql_query = properties.getProperty("sql_query");
/** The update_query Query marks the records written to the flat file, so that we dont' write them to the flat file again on the next execution.*/
String update_query = properties.getProperty("update_query");

try {
out = new BufferedWriter(new FileWriter(output_file));
} catch (IOException ioe) {
System.err.println(
"Unable to open output file..." + output_file + "\n" + ioe);
System.exit(1);
}

try {
Class.forName(jdbc_driver);
} catch (ClassNotFoundException cnfe) {
System.err.println(
"Database driver class "
+ jdbc_driver
+ " not found.\n"
+ cnfe);
throw cnfe;
}
try {
connection =
DriverManager.getConnection(db_url, db_userid, db_password);
statement = connection.createStatement();
} catch (SQLException sqle) {
System.err.println(
"Unable to establish connection to database..."
+ db_url
+ "\n"
+ sqle);
throw sqle;
}

try {
resultSet = statement.executeQuery(sql_query);
} catch (SQLException sqle) {
System.err.println(
"Error while executing query on database.\n"
+ sql_query
+ "\n"
+ sqle);
throw sqle;
}

int rowcount = 0;
try {

int colCount = resultSet.getMetaData().getColumnCount();
while (resultSet.next()) {
for (int i = 0; i < colCount; i++) {
if (i > 0) {
//out.write("\t");
Object value = resultSet.getObject(i + 1);
if (value == null || resultSet.wasNull()) {
out.write("NULL");
} else {
out.write(value.toString());
}
}
} //end of column. loop to next column.
out.write("\n");
rowcount++;
} //End of current row. loop to next row.
} catch (SQLException sqle) {
System.err.println("Unable to parse ResultSet.\n" + sqle);
throw sqle;
} catch (IOException ioe) {
System.err.println("Unable to write to data file.\n" + ioe);
throw ioe;
}
System.out.println("Finished writing " + rowcount + " rows to file.");
try {
statement.executeUpdate(update_query);
statement.executeUpdate("COMMIT");
} catch (SQLException sqle) {
System.err.println(
"Error while executing update query on database.\n"
+ update_query
+ "\n"
+ sqle);
throw sqle;
}
}
catch(Throwable t) {
System.err.println("Error while executing program\n " + t);
}
finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException sqle) {
System.err.println("Unable to close ResultSet.\n" + sqle);
}
}

if (statement != null) {
try {
statement.close();
} catch (SQLException sqle) {
System.err.println("Unable to close Statement.\n" + sqle);
}
}

if (connection != null) {
try {
connection.close();
} catch (SQLException sqle) {
System.err.println("Unable to close Connection.\n" + sqle);
}
}

if (out != null) {
try {
out.close();
} catch (IOException ioe) {
System.err.println("Unable to close File Writer.\n" + ioe);
}
}
timestamp = new java.util.Date();
System.out.println("Program end time : " + timestamp);
}
}
}
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
I'll bite ... here are a couple classes I put together for my own use. You'll have to imagine some of the other classes around them. I won't claim they are ideal OO because I still slip into functional decomposition when it's late at night (grin) but I tried to separate concerns. There are really three things going on: 1) Walking through the result set row and column at a time 2) Processing each row and column into a text format and 3) writing the text to the console. Part 3 is a one line method in the second class but it's built to plug in other objects, for instance one that adds lines to a test area on a Swing page.

There was another thread about doing sort breaks that wound up with a pointer to a really nice Reporter class that does all this stuff (much better) for any Collection. The author also had a result set wrapper that implements Collection. Ah, here it is now!

The text formatter writes headings, pads columns out to a consitent width. Working from first column to the last, as long as the value is the same as the prior row it puts an equal sign instead of the value. Makes kind of a "stepped" report.


A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Resultset to flat file.