Uploading data from excel file to database using java

What oracle version do you use? If you need just a web application for that use case, maybe APEX can make it being your friend. Starting from Oracle 11g it will be preinstalled along with the database, from 9i you can install it by yourself. It brings a very good integration of excel along thus it will be easy, even for beginners, to create an application in a few days.

As regards the first question, i think that the best solution is to convert excel file in .csv format (comma-separated-value) that is very simple to be parsed. I'm not sure that can help, but I found this link.

For exception handling, have a look to @ExceptionHandler annotation that maybe can help you.

Write to disk and do a bulk import from oracle. Parse the errors and return them to the user.

You can use jExcelAPI or Apache POI which are free or you can pay for Aspose cells which is not that expensive if you're working for a company.

Each of these can be very useful. For example, you can use the library to look for errors, then convert to CSV, and finally use JDBC to insert into the database.

If your excel sheets are large like you say, be careful to not load everything into memory at the same time otherwise you might run out of heap space. Do your garbage collecting throughout the process.

How many columns of each record in your data sheet and how big is the data in each cell? You can figure out the memory consumption to see if it fits your heap after converting to Java objects using Apache POI.

Then you could start a database transaction trying to insert the data. In case of any error, the transaction will be rolled back and you can file the wrong data in the sheet for user to correct.

Wat exactly you should do is ,

1)Use Apache POI for Microsoft excel document conversion to XML. It gets you office openXML format. Once you get it . Store it in the file system. 2)Secondly provide a link to the user for importing 3)once he click import , you start a quartz job. Since you are using spring http://static.springsource.org/spring/docs/1.2.x/reference/scheduling.html

4)In you job class start your XML extraction and then start a transcation(Hibernate). After each

Starting new transaction in spring .

DefaultTransactionDefinition def = new DefaultTransactionDefinition();
def.setName("ImportFileTransaction");
def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);

TransactionStatus status = txManager.getTransaction(def);

after each commit

txManager.commit(status);
status = txManager.getTransaction(def);

get the transactions status and cntinue inserting

5)For notifying the user maintain error xml so that the row tht has an error is inserted in that xml.

Enjoy!!!!

I have also done the same thing what you have asked. I did the following steps

1. saved file @ server .
2. Used POI jar because it support maximum xls features
3. Created a java class as per table structure
4. Applied the validation in the setter method , that will check value during setting data into collection of type (class what is created),thorw the exception with relative code to track handle the exception and for customized message(To show user end).
5. once all record are validated then hit data base to validate 2nd level e.g get id for any given name. handle exception here again by using error code.
6. make an insert query template to insert data into database try to use prepared Statement because it is little bit safe for SQL injection.
7. good way is to use hibernate or any other persistence service because it safe and transaction management , OR for JDBC use batch query to commit finally if all thing fine else simply rollback the transaction

if record is very large (15000 is not that large), then divide you job in different batch and control it by thread.