This week's giveaways are in the JDBC and Relational Databases and A Day in Code forums.
We're giving away four copies each of JDBC Workbook and A Day in Code and have the authors on-line!
See this thread and this one for details.
Win a copy of JDBC Workbook this week in the JDBC and Relational Databases forum
or A Day in Code in the A Day in Code 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • salvin francis
  • fred rosenberger

whats the best way ?

 
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Friends,
I have a problem and a solution. I want to confirm the best solution for the problem.
Problem:
I receive an xml file via ftp. I need to parse that file and store the information in the database. The information in the xml file is scattered across various tables in the database. I should collect information for each table per record and then store in the database.
My solution:
I will have a properties file .
This file
contains the details of the tables that are related to the xml file.
contains the details of the columns of each table.
contains the mapping of database columns to xml elements.
So first i will get the list of tables. then basing on the table name i will get the list of columns. Then basing on the column name i will get the associated element in the xml file i and then i will grab the info related to that column from the xml file and store it in some collection.
I will repeat this for each column and then run the database function.
I know this is a bit confusing....but if any body can suggest me whether i am going the right way or not it wud be great.
Prithvi
[ March 31, 2004: Message edited by: prithvi ]
 
Ranch Hand
Posts: 350
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Originally posted by prithvi:
Hi Friends,
I have a problem and a solution. I want to confirm the best solution for the problem.
Problem:
I receive an xml file via ftp. I need to parse that file and store the information in the database. The information in the xml file is scattered across various tables in the database. I should collect information for each table per record and then store in the database.
My solution:
I will have a properties file .
This file
contains the details of the tables that are related to the xml file.
contains the details of the columns of each table.
contains the mapping of database columns to xml elements.
So first i will get the list of tables. then basing on the table name i will get the list of columns. Then basing on the column name i will get the associated element in the xml file i and then i will grab the info related to that column from the xml file and store it in some collection.
I will repeat this for each column and then run the database function.
I know this is a bit confusing....but if any body can suggest me whether i am going the right way or not it wud be great.
Prithvi
[ March 31, 2004: Message edited by: prithvi ]


Hi
Your soulution seems to be a way of doing it.
Your solution is driven from the database table structure i.e based on the structure of the table ( and all the columns that you want to populate ) you are selecting the corresponding elements from the xml file ( let us call this the xml data file assuming that you are using an xml properties file ). Based on this desing you are going to load the entire xml data in memory (using DOM ??? ) and the select the elements that make up the columns that you want to insert into the database -- Correct me if I am wrong.
Here is what I was thinking
You can use a streaming parser ( SAX ) and read elements and store the elements that form a single table row into a java object that represents a table row, and when all the columns of that row are populated with data insert the data into the database. Using this method you dont have to worry if your xml file is realyl huge since you are using SAX, alternatively you can using batch upadte (JDBC 2.0 ) and improve the database insertion by grouping insertion into batchs.
I think you can also use some XML to DB mapping framework, something like Castor JDO or Hibernate --- I dont have much information about these frameworks but I sure that they can give you what you want and also with less code.
vivek
 
prithvi
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Vivek,
Thanks for the reply. I have actually worked on the flow i wrote and found that the performance is getting effected since for each element i am parsing the whole xml each time.

I also gave a thought in the way u told. What i have in my mind is that i read a block of xml thru a value object then store that value object in a hashmap. So if 100 records 100 value objects in a hashmap. So i only read the whole xml once.

Then at the server i will handle these value objects and work the way u told. I will check that performanace and let u know.
 
author
Posts: 11962
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello "prithvi",
May I ask you to take a look at our naming policy and edit your display name accordingly -- we require both a first name and a last name (initial is ok for a first name).
Thanks.
 
Here. Have a potato. I grew it in my armpit. And from my other armpit, this tiny ad:
Devious Experiments for a Truly Passive Greenhouse!
https://www.kickstarter.com/projects/paulwheaton/greenhouse-1
    Bookmark Topic Watch Topic
  • New Topic