Writting to file from Database

[ali.alauoubiy]

Hi All..

I am new to Enterprise Java Development and I have question? I am
trying to develop system that been in written in very bad way(i.e. no
Framework, Desigen Patterns). The question is I would like to run SQL
statement from a java class and I would like to store the result set
into a file, however I don't know what how to extract the rows from the
result set (specially don't know what data type to use) to write to a
file.. the code I written so far is:

//creat 4 different files and split the result set into 4
parts and store them into 4 files
FileOutputStream fout1 = new
FileOutputStream("test1.out");
FileOutputStream fout2 = new
FileOutputStream("test2.out");
FileOutputStream fout3 = new
FileOutputStream("test3.out");
FileOutputStream fout4 = new
FileOutputStream("test4.out");


// now to the FileOutputStream into a PrintStream
PrintStream myOutput1 = new PrintStream(fout1);
PrintStream myOutput2 = new PrintStream(fout2);
PrintStream myOutput3 = new PrintStream(fout3);
PrintStream myOutput4 = new PrintStream(fout4);

//Added by Ali
query = "select * from ExportFlatDaily where viewId = '0' ";
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
myOutput1.print(rs.toString());

}

query = "select * from ExportFlatDaily where viewId = '1' ";
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
myOutput2.print(rs.toString());

}

query = "select * from ExportFlatDaily where viewId = '2' ";
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
myOutput3.print(rs.toString());

}

query = "select * from ExportFlatDaily where viewId = '3' ";
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
myOutput1.print(rs.toString());

}

Any Suggestions.. Many thanks...

Ali

 

[Bart Cremers]

You should take a look at the getXXX methods in ResultSet. Probably
getObject(...) will do perfectly for your case.

ResultSet rs = ...
int cols = rs.getMetaData().getColumnCount();

while (rs.next()) {
StringBuffer sb = new StringBuffer();
for (int i = 1; i <= cols; i++) {
sb.append(rs.getObject(i);
}
output.print(sb.toString());
}

Something in that area should work.

Bart

 

[impaler]

Hi All..

I am new to Enterprise Java Development and I have question? I am
trying to develop system that been in written in very bad way(i.e. no
Framework, Desigen Patterns). The question is I would like to run SQL
statement from a java class and I would like to store the result set
into a file, however I don't know what how to extract the rows from the
result set (specially don't know what data type to use) to write to a
file.. the code I written so far is:

Use the getMetaData() method of the resultset. That gives you all the
info you need about the record. Then on each iteration you build a
string and write it to the file. You can add a nice header too at the
beginning.

http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSet.html#getMetaData()
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSetMetaData.html

 

[Arnaud B.]

Hi,

You may use the getColumnType(int columnIndex)

ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2");
ResultSetMetaData rsmd = rs.getMetaData();int columnType =
rsmd.getColumnType(1);switch(columnType){case(java.sql.Types.BOOLEAN):
out.writeBoolean(rs.getBoolean(1)); break;case(...and so forth..)}The
example is provided fo column 0, but ResultSetMetaData provides many
informations about what the resultset contains (column count, column
names...).The out object is of type DataOutputStream in this

 

[James Westby]

Hi All..

I am new to Enterprise Java Development and I have question? I am
trying to develop system that been in written in very bad way(i.e. no
Framework, Desigen Patterns). The question is I would like to run SQL
statement from a java class and I would like to store the result set
into a file, however I don't know what how to extract the rows from the
result set (specially don't know what data type to use) to write to a
file.. the code I written so far is: [snip]

//Added by Ali
query = "select * from ExportFlatDaily where viewId = '0' ";
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
myOutput1.print(rs.toString());

}

[snip]


How well does this work?


query = "select * from ExportFlatDaily where viewId = '0' ";
ResultSet rs = stmt.executeQuery(query);
int numColumns = rs.getMetaData().getColumnCount();

while (rs.next()) {
for (int i = 1; i <= numColumns; i++) { //Note columns indexed from 1
myOutput1.print(rs.getString(i));
}
myOutput.println("");
}

rs.close();


Do you get anything with your orginal code?


James

 

[Arnaud B.]

I hope this one will be more readable...

You may use the getColumnType(int columnIndex)

ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2");

ResultSetMetaData rsmd = rs.getMetaData();int columnType
=rsmd.getColumnType(1);

switch(columnType){

case(java.sql.Types.BOOLEAN):
out.writeBoolean(rs.getBoolean(1));
break;
case(...and so forth..)}

The example is provided fo column 0, but ResultSetMetaData provides many
informations about what the resultset contains (column count, column
names...).

The out object is of type DataOutputStream in this example.

Regards,

Arnaud

 

[James Westby]

James Westby wrote:
[snip]

for (int i = 1; i <= numColumns; i++) { //Note columns
indexed from 1
myOutput1.print(rs.getString(i));
}
myOutput.println("");

[snip]

myOutput1.print(rs.getObject(i));

is probably better.


James

 

[ali.alauoubiy]

Many thanks all for your help.. I will test it on Monday as the
Database is down at the moment... very kind of you.. I think
getMetaData() is what I was looking for ...

_______________________________________

Ali Alauoubiy

IT Analyst, (Common Data Services)

Dresdner Kleinwort Wasserstein, London

 

[ali.alauoubiy]

Hi Everyone again..

This will generate the file correctly, however all the files generated
are empty,, it should be filled with data from the SQL I am running...
any reason!?

Regards
Ali

 

[ali.alauoubiy]

9. (e-mail address removed)
Feb 7, 3:18 pm show options

Newsgroups: comp.lang.java.programmer
From: (e-mail address removed) - Find messages by this author
Date: 7 Feb 2006 07:18:55 -0800
Local: Tues, Feb 7 2006 3:18 pm
Subject: Re: Writting to file from Database
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

Hi Everyone again..


This will generate the file correctly, however all the files generated
are empty,, it should be filled with data from the SQL I am running...
any reason!?


Regards
Ali