JSP Import CSV into database

Discussion in 'Java' started by Dave, Oct 16, 2006.

  1. Dave

    Dave Guest

    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.
    Dave, Oct 16, 2006
    #1
    1. Advertising

  2. 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
    Manish Pandit, Oct 16, 2006
    #2
    1. Advertising

  3. Dave

    Lew Guest

    Manish Pandit wrote:
    >
    > 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
    Lew, Oct 18, 2006
    #3
  4. Davide Consonni, Oct 18, 2006
    #4
  5. Dave

    Tom Forsmo Guest

    Lew wrote:
    > Manish Pandit wrote:
    >>
    >> 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?


    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
    Tom Forsmo, Oct 23, 2006
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Andrew J Gray
    Replies:
    1
    Views:
    9,131
    Teemu Keiski
    Mar 4, 2004
  2. darrel
    Replies:
    6
    Views:
    13,648
    darrel
    Mar 8, 2006
  3. Replies:
    0
    Views:
    4,353
  4. Replies:
    2
    Views:
    135
  5. John Mcleod
    Replies:
    10
    Views:
    277
    Jonathan Sion
    Sep 16, 2009
Loading...

Share This Page