snLibrary - XML file Upload into DB
I have faced many situations where I wanted to upload an XML file straight into a table. Have you too?
Of course, only select XML tags should be picked up from the XML. In a separate package, called
utils, along with some other utility classes, snLibrary contains few functions for achieving this uploading. It can be used not only in a web-application, but also in a normal java application that could pick up the XML file from the file-system and upload the contents in the specified table in the database.
The method and its usage is given below.
Method:- utils.snUploader :: snReadXMLintoDBwithInsert()
// This method has been used widely for Uploading the XML file received from SAP after calling
// select RFCs (Remote Function Calls) of SAP.
// ...
// ... Some java code goes here..
// ...
snUtilsConstants snUC = new snUtilsConstants();
// snUtilsConstants contains few Constant values that are defined so as to be used as Data Type
// in the following method.
final String[] XML_TAGS = {"VENDCODE","TITLE","VENDNAME","DISTRICT","STATE","AMOUNT","POSTING_DATE" };
// Here we have defined the XML Tags as it appears in the XML file.
final String[] UPLOAD_COLS = {"CARRIER_ID","TITLE","CARRIER_NAME","DISTRICT","STATE","AMOUNT","ACCNG_DATE" };
// Here we have defined the corresponding Columns of the Table in our database.
final Object[] UPLOAD_COL_TYPES = {0L, null, null, null, null, 0.00d,
snUC.DT_TYPE_DMYY }; // null corresponds to String Type
// Here we define the data type of each Column. While null or simply "" could represent a
// String datatype, other datatypes are defined in the snUtilsConstants class.
// snUtilsConstants.DT_TYPE_DMYY denotes Date datatype given in the format dd_mm_yyyy
snUploader snUpd = new snUploader();
// dbPropXMLFileName is an XML file that contains the Connection Parameters
// for connecting to the database server.
// The parameters are : Driver, JDBCurl, login, pass
Connection con = snUpd.createConnection(dbPropXMLFileName);
int insRows = snUpd.snReadXMLintoDBwithInsert(inputXMLFileName, con, dbTablename,
rootSearchItemId, XML_TAGS, UPLOAD_COLS, UPLOAD_COL_TYPES);
// or to use with the Truncate Option,
boolean no_truncate = false;
int insRows = snUpd.snReadXMLintoDBwithInsert(inputXMLFileName, con, no_truncate,
dbTablename, rootSearchItemId, XML_TAGS, UPLOAD_COLS, UPLOAD_COL_TYPES);
con.close();
// ...
// ... Other part of the code
// ...
Few other functions/methods are also available for example,
snReadXMLintoDBwithInsertUpdate() will try an insert for each record and if it fails, will try an update on the same.
snReadXMLintoDBwithUpdateInsert() will try an update first for each record and if it fails, will try an insert on the same.
snReadXMLintoDBwithUpdate() will only try to update if similar record exists.
