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);
}
}
}