aspose file tools*
The moose likes JDBC and the fly likes best way to extract the data from several tables into a file Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "best way to extract the data from several tables into a file" Watch "best way to extract the data from several tables into a file" New topic
Author

best way to extract the data from several tables into a file

Celinio Fernandes
Ranch Hand

Joined: Jun 28, 2003
Posts: 548

hi,
I have to extract the data from several tables (Oracle is the database)
and insert them into a file, in a specified format.

Each column will be allocated a certain size (filled with empty spaces if size is too big), and I will assign a line for each record extracted from the tables. There wont be any delimiter.
Example of such line:
column1 colum2column2 column4 etc

So i am looking for ways to do it in Java, of course.
Thanks in advance for your suggestions and ideas.


SCJP 1.4, SCWCD 1.4, SCBCD 1.3, SCBCD 5
Visit my blog
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31067
    
232

Max,
First is to figure out your query. Do you want a join of the tables or each table separately or something else?

Once you have your query, loop through the result set and append data to the file. And once you start, you are welcome to post code here if you get stuck.

[edited to fix a typo]
[ August 17, 2005: Message edited by: Jeanne Boyarsky ]

[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Celinio Fernandes
Ranch Hand

Joined: Jun 28, 2003
Posts: 548

thanks, but i would like to find a more elegant way to do it.
I kind of had it with pure SQL statements now,
I was thinking about using Hibernate, but I think the learning curve is too important and I have a short time left to complete my task.

I am still open to new suggestions and ideas
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31067
    
232

Max,
You still need to figure out what the query should be and loop through the result. You would only be able to replace the actual data access step in this way.
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
The hard part is figuring out column widths. I've done all of these at one time or another: derive the size from the column metadata, run through the data once to get the longest value for each column then run through again to report, use an external metadata file to specify column sizes. The external metadata gives you a chance to specify nice headings, too.

Any of those sound good? There are some open source packages that make columnar reports from result sets using only the column metadata. I made a primitive one of those that leaves a column blank if its the same value as the row before, making hierarchical data into an outline appearance.


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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: best way to extract the data from several tables into a file