Design For a scenario

Question

Hi Experts,

Could someone please suggest me a best design for the following scenario:

• There are many numbers in a file, the file somewhat looks like:

  -1100

  -1101

  -1102

  -1103

  -1104

  -
  -
  -
  -
  -Till 9999
  

• Design and Develop a program in JDBC which will read all numbers from the file and insert into a table at database.

Note: I don't want to read file thousand times and fire insert statement thousand times.

Answer 1

Algorithm:

• Use a BufferedFileReader to read the file line by line.
• Make a prepared statement with the insert you need.
• For each line bind the prepared statement's parameter with the data parser from the file.

• use executeBatch every x lines or so to do the inserts.

• profit !

Answer 2

Can you not use a bulkloader? Here is an example for Sql Server:

BULK

INSERT BulkTest

FROM ‘c:\file.txt’

WITH

(

ROWTERMINATOR = ‘\n’

)

GO

Here is another option, at least with Sql Server 2008, insert multiple lines with 1 statement:

Insert into table(id, country)
Values
(1,‘USA’), –-Row 1
(2,‘UK’), –-Row 2
(3,‘France’) –-Row 3