CODEWITHSUNDEEP
javajdbc
hetmanJIIIS
hetmanJIIIS

Reputation: 104

java JDBC inserting many rows into tables

I have a csv file with 80 000 rows, each rows have: cost;date (123.232;30/12/2008) I have to insert all cost data into tables names as a date in parametr second for example: 123.232 cost will be row in a "30/12/2008" table and i have so many rows like this.. Now my program looks like: Now i have to declare SQL query in for loop because i need "date" parameter,

my question - how to draw a "pStatement = connection.prepareStatement("INSER...." line away from for loop ? ofcourse with getting "date" parameter

Why i need that?- because now addingBatch doesn't work and now add to database only last row. If i will move pStatement.executeBatch() inside for loop- then that will not work like a batch addingbut like normally each adding.

I'm using batch adding because i need fast working my application. All advice will be wanted

    Database database = new Database();
    Connection connection = database.GetConnection();

    PreparedStatement pStatement = null;

    for(int x=0; x<=allRowsInCSVFile.size()-1; x++){
      Rows row = allRows.get(x);


      pStatement = connection.prepareStatement("INSERT INTO \""+ row.getDate() +"\" (cost) VALUES (?);");
      pStatement.setLong(1, row.getCost());
      pStatement.addBatch();

      }
      pStatement.executeBatch();
      connection.close();

Upvotes: 0

Views: 1060

Answers (3)

Priit
Priit

Reputation: 480

You could use just jdbcTemplate.batchUpdate. See an example here: http://www.mkyong.com/spring/spring-jdbctemplate-batchupdate-example/ This allows you to use different sql but still execute in batch.

With preparedstatement you would have to group together all inserts with same date values and prepare different statements for each of those dates.

Upvotes: 0

Srinath Ganesh
Srinath Ganesh

Reputation: 2558

not sure how good this is so ill post as community wiki

    Object obj1 = new Object();
    PreparedStatement pStatement = 
      connection.prepareStatement("insert into " + obj1.toString() );
        while(true)
        {
             obj1.setSomeValue
        }

Upvotes: 0

Random
Random

Reputation: 467

I think you should split the allRowsInCSVFile to multiple lists each for one date then you can draw the prepared statement out of the loop (sort of). It will not be as you exactly want, but it will a batch for each date. I think that will be a compromise that you have to do.

Upvotes: 2

Related Questions