问题描述:

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();

网友答案:

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.

网友答案:

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
        }
网友答案:

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.

相关阅读:
Top