*
The moose likes Servlets and the fly likes Design issue regarding how to validate records in an uploaded excel sheet. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Java » Servlets
Bookmark "Design issue regarding how to validate records in an uploaded excel sheet." Watch "Design issue regarding how to validate records in an uploaded excel sheet." New topic
Author

Design issue regarding how to validate records in an uploaded excel sheet.

chaitanya karthikk
Ranch Hand

Joined: Sep 15, 2009
Posts: 800

Hi everybody, I am Chaitanya, I am working on a web application where one of the requirement is to insert student details in database from an uploaded excel sheet. I have a jsp which uploads the file to the server, a servlet saves the file to the server's drive. Now the actual problem arises. How to validate the rows in the file?

Should I employee a separate class which validates the entire rows in the file all at once and insert only the rows in the database which pass the test.

Or should I pass the file path from the servlet to the model, from there the model reads a record and populates the student bean and send the bean to a validating class then based on the return value the model inserts the record.

I don't know which decision to take and whether these are good approaches. Anyone please shed some light on my problem and guide me in right direction.

Thank you all in advance. Have a good day.


Love all, trust a few, do wrong to none.
Deepak Bala
Bartender

Joined: Feb 24, 2006
Posts: 6661
    
    5

Do you use a ORM ? If you do, then convert to the model, filter it and pass it to the ORM framework.

If you dont and you use something like JDBC, filter the rows, pass it to the JDBC methods and save the data.

On a side note, you might want to consider the information sent back to the user about the rows being discarded.


SCJP 6 articles - SCJP 5/6 mock exams - More SCJP Mocks
chaitanya karthikk
Ranch Hand

Joined: Sep 15, 2009
Posts: 800

Deepak Bala wrote:Do you use a ORM ? If you do, then convert to the model, filter it and pass it to the ORM framework.
No I am not using any frame work.
Deepak Bala wrote:
If you dont and you use something like JDBC, filter the rows, pass it to the JDBC methods and save the data.
Should I filter the rows all at once time or the model filters the row 1 each time and inserts into database.
Deepak Bala wrote: On a side note, you might want to consider the information sent back to the user about the rows being discarded.

Yes the discarded rows are sent back to the user again in web page which has all the rows pre-populated in appropriate input fields.
Deepak Bala
Bartender

Joined: Feb 24, 2006
Posts: 6661
    
    5

Should I filter the rows all at once time or the model filters the row 1 each time and inserts into database.


Your DAOs should not be bothered with choosing which rows to insert. Filter them before sending it to a DAO.
chaitanya karthikk
Ranch Hand

Joined: Sep 15, 2009
Posts: 800

What do you mean by DAO? What I understood is the model should not bother about the insertion decision.
chaitanya karthikk
Ranch Hand

Joined: Sep 15, 2009
Posts: 800

Suppose that I have filtered all the records and found 2/10 records as invalid, now I will store the row number of all valid records in a Set and send the set to the servlet, then the servlet sends the Set and file to a model, from there the model starts inserting the rows.

What about this approach.
Deepak Bala
Bartender

Joined: Feb 24, 2006
Posts: 6661
    
    5

A model represents a design view of a 'business domain model'. A DAO is a component that lets you access / manipulate data to a persistence store.

Your DAO should have a method like so



The method should ideally not decide which Customer needs to be inserted. A service class can process the rows, and then call a DAO which will insert the data.
chaitanya karthikk
Ranch Hand

Joined: Sep 15, 2009
Posts: 800

This is the architecture.

Is this right or wrong?


[Thumbnail for architecture.png]

Pawan Kr Gupta
Greenhorn

Joined: Nov 21, 2010
Posts: 26
You can follow the below approch.

1-First check the data level check in servlet.
2- Create a temp table in database.
3- insert all the records in the Temp table
4- after inserting records in temp table you can check the the records one by one using a PROC and then move the records in main table otherwise rejects the record and insert in Error table.


Deepak Bala
Bartender

Joined: Feb 24, 2006
Posts: 6661
    
    5

Pawan Kr Gupta wrote:You can follow the below approch.

1-First check the data level check in servlet.
2- Create a temp table in database.
3- insert all the records in the Temp table
4- after inserting records in temp table you can check the the records one by one using a PROC and then move the records in main table otherwise rejects the record and insert in Error table.




There are 3 things that are bad about that approach

1. The logic to remove a record is placed in a PROC while it should be running in an app server.
2. There is an unnecessary round trip back to fetch the rejected records.
3. Unnecessary creation of extra tables to execute a simple SQL query
chaitanya karthikk
Ranch Hand

Joined: Sep 15, 2009
Posts: 800

Hi Bala, what is the correct approach? Is my approach correct?
chaitanya karthikk
Ranch Hand

Joined: Sep 15, 2009
Posts: 800

Can anyone tell me whether the approach is correct or not. I don't know anything about DAO. I don't know any frameworks also.
 
jQuery in Action, 2nd edition
 
subject: Design issue regarding how to validate records in an uploaded excel sheet.
 
Similar Threads
How to convert excel sheets exponential cell format to java string
Uploading and downloading data from excel sheet
Files processing on server
Handling SQL Exception
Generating Insert Script from Excel sheet