0

We have a linux box into which some third party tool drops 0.5MB of data and we have about 32000 similar files. We need to process those files and insert into Oracle10G DB. some one in our organization already has already created a Java program and it is running as a Daemon thread with static fields to map the data in the file and save data into db and clear the static fields for the next line.

This is a serial processing of file and it seems so slow. I'm planning to make this multithreaded by getting rid of it, Or, run multiple java processes(same jar but each one will be start with java -jar run.jar) for parallel execution. But, I'm concerned about the data locking etc., issues.

Questions is what is the best way to bulk load the data into the DB using Java? Or any other way.

Update: the data that we work on is in the following format, we process the below lines, to make entries into db. 

x.y.1.a.2.c.3.b = 12 // ID 1 of table A onetomany table C 3 ID sequence and its proprty b =12 
x.y.1.a.2.c.3.f = 143 // ID 1 of table A onetomany table C 3 ID sequence and its proprty f =143 
x.y.2.a.1.c.1.d = 12

Update: We have about 15 tables that take this data. Data is in blocks, each block has related data, and related data will be processed at a time. So you are looking at the following figures when inserting one block

Table 1 | Table 2 | Table 3
---------------------------
5 rows  | 8 rows  | 12 rows

etc.,

Zeus
  • 6,386
  • 6
  • 54
  • 89
  • Are you able to use batch inserts with PreparedStatement? A little more info: http://stackoverflow.com/questions/6892105/bulk-insert-in-java-using-prepared-statements-batch-update – tim Aug 02 '14 at 14:24
  • @tim we have about 15 tables, we read a block of data and process the related data and put it in the db. Because we need to maintain the constraints properly when inserting I may not be able to use the prepared statement. – Zeus Aug 02 '14 at 14:29

2 Answers2

3

Take a look at Oracle's SQL*Loader tool. It is a tool that is used to bulk load data into Oracle databases. There is a control file that you can write to describe the data, some basic transforms for the data, skip rows, convert types, etc. I've used it before for a similar process and it worked great. And the only thing I had to maintain was the driver script and the control files. I realize you asked for a Java solution, but this might also meet your needs.

Todd
  • 30,472
  • 11
  • 81
  • 89
  • Thanks for the answer, the file that we read is very custom made, the application need to read a block(ie,. 10-100 lines) to insert data into 15 tables. Will this tool support custom logic? – Zeus Aug 02 '14 at 14:13
  • OP said "Questions is what is the best way to bulk load the data into the DB using Java? **Or any other way**. " – hd1 May 23 '15 at 21:47
1

Ideally, this sounds like a job for SQL Loader rather than Java.

If you do decide to do this job in java, consider using executebatch. An example is here.

Bajal
  • 5,487
  • 3
  • 20
  • 25