I am currently doing a bulk upload in my aplication where i have to upload atleast 30,000 records with 88 data fields in to several tables of my application. The data is uploaded through an excel file and now i need to validate the data for datatype,maxlength,Foreignkey relationship. if had to do this for all the 30,000 records before i insert or update them in to my database,it will take huge amount of time so i thought of inserting the records and show the user what is the error when an sql exceptionoccurs. So is it possible to identitfy which column is causing the SQLEception so that i can alert the user with a meaningful error messgage... Is there is a different to handle this or should i do the validation through my javacode and once all the validation is passed then insert it which is time consuming when record count increases. Please help me in identify a best way to address this issue.
It is not always a good idea to pre-validate Data Model constrains, because there is no 100% guarentee that you will catch them in your app. all before they happen. FOr example, if you check a field is unique then insert data something else may have added data after your check but before your insert.
So is it possible to identitfy which column is causing the SQLEception so that i can alert the user with a meaningful error messgage...
If your indices have understandable names then yes. The SQLException will just return the error message the database generates. So if for example you have a field called name, and it have a unique index called name_unique_index it should be fairly obvious what the error is if the contraint is violated. Of course if you index is called ghg343h4_ux or something this is less likely to be helpful.
If your data model is not immediately obvious to the user, for example if you have an Excel column called "First Name" and this goes in a field called person_n1 you might need to add some sort of mapping layer. You can parse the error message the SQLException includes and use this to provide something more meaningful.
Most databases will specify the column when a data truncation error occurs. If they don't, you will have to pre-validate. This is a safer check, since DDL changes will be far less likely than DML changes.