I’m confident enough in my ability to create each step independently of each other but how I tie it all together is what I’m unsure about.
My idea is to create a class which connects via ftp to a remote host and downloads the xml file.
Of course, it depends on how large and complicated your XML file is for how long it will take.
I then need to parse the xml file and insert/update into a database depending on a timestamp.
I’m still quite new to coding, so I’m looking for some advice from the more seasoned veterans here on ars.
The part that I’m unsure about is getting the information into the database (mysql).
The googling that I have done so far shows me that there are two main options for parsing the xml DOM and SAX.
For DB2 V9.1 and higher for Linux/UNIX/Windows, the DB2 driver supports the XML data type.
Which JDBC data type the XML data type is mapped to depends on whether the JDBCBehavior and XMLDescribe Type properties are set: and the XMLDescribe Type property is not set, XML data is mapped to SQLXML or CLOB, depending on which JVM your application is using.
If you do need to do it, I use Apache Commons/DBCP which makes JDBC connection pooling pretty simple and easy. The simple case is that you have a single table you will be inserting data into. For each row taken from the XML file, create an instance of My Table, fill in all the fields. When done parsing the XML file, use the collection of My Table objects to insert the data into the database.
That is the assumption I'm making with the rest of this. Assuming you are using JDBC, I recommend using the "execute Batch()" functionality of Prepared Statement to insert many rows at once.
Now close the File Input Stream and fetch the excel file using File Output Stream.