whats the best way ?

[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 ]

[Vivek Viswanathan]

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]

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.

[Lasse Koskela]

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.