File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
chaitanya karthikk
Ranch Hand
Posts: 806
Java MySQL Database Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Deepak Bala
Bartender
Posts: 6663
5
Firefox Browser Linux MyEclipse IDE
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
chaitanya karthikk
Ranch Hand
Posts: 806
Java MySQL Database Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 6663
5
Firefox Browser Linux MyEclipse IDE
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 806
Java MySQL Database Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What do you mean by DAO? What I understood is the model should not bother about the insertion decision.
 
chaitanya karthikk
Ranch Hand
Posts: 806
Java MySQL Database Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 6663
5
Firefox Browser Linux MyEclipse IDE
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 806
Java MySQL Database Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is the architecture.

Is this right or wrong?
architecture.png
[Thumbnail for architecture.png]
 
Pawan Kr Gupta
Greenhorn
Posts: 26
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 6663
5
Firefox Browser Linux MyEclipse IDE
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 806
Java MySQL Database Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Bala, what is the correct approach? Is my approach correct?
 
chaitanya karthikk
Ranch Hand
Posts: 806
Java MySQL Database Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Consider Paul's rocket mass heater.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic