JSP Import CSV into database

[Dave]

I'm looking to implement a feature into a JSP web app which will allow users
to import a CSV file into a database. My thoughts are that the process would
involve the following steps:

1) Transfer the CSV file from client to web server.

2) Have a simple Java application check folder location intermittently for
new files in upload folder on website

3) If a new file has been uploaded then parse the CSV into a collection of
JavaBeans

4) Use JDBC to import each JavaBean into table


My questions are the following:

- Is this the best way to achieve this (bearing in mind each row will need
to be validated)?

- What is the most appropriate method of tranferring a file to a JSP web
server?

- How should I transfer the JavaBean data to the SQL database - import each
bean one-by-one?


Any advice would be appreciated.

 

[Manish Pandit]

Hi Dave,

- Is this the best way to achieve this (bearing in mind each row will need
to be validated)?

No. You should not be polling, but the process should be per-request.
In short, when your servlet receives the csv, it should process,
validate and write to the database. Any errors can then be reported to
the submitter synchronously (as a response to this request).

- What is the most appropriate method of tranferring a file to a JSP web
server?

Use struts. If not, write a servlet that accepts the request, override
the doPost() method, extract the file from the multipart request, and
process the records. Use the HttpServletResponse to write any messages
regarding error/success. You can use apache fileupload API to extract
the file from the request. Or if you want to do it
quick-and-not-so-dirty, post to another JSP that has the scriptlet java
code to process the request + write to the database.

- How should I transfer the JavaBean data to the SQL database - import each
bean one-by-one?

Yes, you will need to update/insert records one by one as you read from
the file, or read everything and then process the collection one by
one.

-cheers,
Manish

 

[Lew]

Use struts. If not, write a servlet that accepts the request, override
the doPost() method, extract the file from the multipart request, and
process the records. Use the HttpServletResponse to write any messages
regarding error/success. You can use apache fileupload API to extract
the file from the request. Or if you want to do it
quick-and-not-so-dirty, post to another JSP that has the scriptlet java
code to process the request + write to the database.

Since you recommend Struts, instead of a monolithic servlet as the
alternative, how about a hand-constructed MVC app?

Have the servlet extract the request parameters and pass them on to a logic
class (a la Action handlers in Struts). Have the logic class parse the posted
data and hand it off to a data access object (DAO) that encapsulates the
database logic. Return the database results (success, failure, other) to the
logic class, then back to the servlet, and have the servlet forward to an
appropriate JSP to report the outcome.

Voila.

- Lew

 

[Davide Consonni]

Any advice would be appreciated.

this can be useful ? http://csvtosql.sourceforge.net

 

[Tom Forsmo]

Since you recommend Struts, instead of a monolithic servlet as the
alternative, how about a hand-constructed MVC app?

This is exactly why there are frameworks so you don't have to hand code
and reinvent the wheel every time.

FYI, Struts is old school by now, try Spring Framework, you only need 10
lines of code, a couple of spring config lines and the spring libraries
and you are ready to start processing the csv. The DB stuff can be done
by the Spring DB layer, which hides all JDBC mechanics, all you need
to do is to create a dao that provides the final sql statement and
spring will take of the rest.

tom