This week's book giveaway is in the Spring forum.
We're giving away four copies of Spring Boot in Practice and have Somnath Musib on-line!
See this thread for details.
Win a copy of Spring Boot in Practice this week in the Spring forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Liutauras Vilda
  • Henry Wong
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Al Hobbs
  • Carey Brown
Bartenders:
  • Piet Souris
  • Mikalai Zaikin
  • Himai Minh

Resultset to flat file.

 
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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);
}
}
}
 
(instanceof Sidekick)
Posts: 8791
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic