batch insert takes only one query and fails to take all the query in java

Question

i have a method which has to insert into two tables, i have used batch insert for that. my problem is it takes the second query and inserts in to that table and fails to insert into first table that is fails to take first query.

this is what is my code :

public String saveVillageDetails(VillagesViewModel viewmodel, String functionType) { int[] saveOrupdateStatus = null; StringBuffer disctrictQuery = new StringBuffer(); String saveOrupdateStatusMessage = ""; Connection connection = getConnection(); PreparedStatement districtPS = null; ResultSet rs = null; if (connection != null) { try { connection.setAutoCommit(false); if(functionType.equals("add")){ // to insert in villages table disctrictQuery.append(" INSERT INTO m_villages(districtid,village,creation_date,last_created_by) "); disctrictQuery.append(" VALUES (?, ?, ?,?) "); districtPS = connection.prepareStatement(disctrictQuery.toString()); districtPS.setInt(1, viewmodel.getDistrictid()); districtPS.setString(2, viewmodel.getVillage()); districtPS.setTimestamp(3, getCurrentDate()); districtPS.setInt(4,viewmodel.getUserid()); districtPS.addBatch(); // to insert in regions table disctrictQuery=new StringBuffer(); disctrictQuery.append(" INSERT INTO m_regions(villageid,creation_date,last_created_by) "); disctrictQuery.append(" VALUES (?, ?, ?) "); districtPS = connection.prepareStatement(disctrictQuery.toString()); districtPS.setInt(1, getLatestVilalgeID()); districtPS.setTimestamp(2, getCurrentDate()); districtPS.setInt(3,viewmodel.getUserid()); districtPS.addBatch(); }else if(functionType.equals("edit")){ // to update villages table disctrictQuery.append("UPDATE m_villages SET districtid=? ,village=? ,updation_date=? ,last_updated_by=? "); disctrictQuery.append(" WHERE villageid=? "); districtPS = connection.prepareStatement(disctrictQuery.toString()); districtPS.setInt(1, viewmodel.getDistrictid()); districtPS.setString(2, viewmodel.getVillage()); districtPS.setTimestamp(3, getCurrentDate()); districtPS.setInt(4,viewmodel.getUserid()); districtPS.setInt(5,viewmodel.getVillageid()); districtPS.addBatch(); disctrictQuery=new StringBuffer(); // to update regiions table disctrictQuery.append("UPDATE m_regions SET villageid=?,updation_date=? ,last_updated_by=? "); disctrictQuery.append(" WHERE regionid=? "); districtPS = connection.prepareStatement(disctrictQuery.toString()); districtPS.setInt(1, viewmodel.getVillageid()); districtPS.setTimestamp(2, getCurrentDate()); districtPS.setInt(3,viewmodel.getUserid()); districtPS.setInt(4,getRegionID(viewmodel.getVillageid())); districtPS.addBatch(); } saveOrupdateStatus = districtPS.executeBatch(); if (saveOrupdateStatus.length > 0) { if(functionType.equals("add")){ saveOrupdateStatusMessage = "Village Details Added successfully"; }else if(functionType.equals("edit")){ saveOrupdateStatusMessage = "Village Details Modified successfully"; } connection.commit(); connection.setAutoCommit(true); } else { if(functionType.equals("add")){ saveOrupdateStatusMessage = "Failed to Add Village Details"; }else if(functionType.equals("edit")){ saveOrupdateStatusMessage = "Failded to Modify Village Details"; } } } catch (Exception ex) { ex.printStackTrace(); //use log to print the exception ex.printStackTrace(); } finally { try { closeConnection(connection, rs, districtPS); } catch (Exception ex) { ex.printStackTrace(); //use logger here } } } return saveOrupdateStatusMessage; }

when i printed the statement i get like this :

com.mysql.jdbc.ServerPreparedStatement[2] - INSERT INTO m_regions(villageid,creation_date,last_created_by) VALUES (18, '2012-11-06 17:41:25', 1)

and the first query is missing in the batch.

what could be the problem.

Please help

Regards

jdevelop · Accepted Answer · 2012-11-06 13:07:46Z

Please refactor your code in the way as below:

 if(functionType.equals("add")){ // to insert in villages table districtPS = connection.prepareStatement("INSERT INTO m_villages(districtid,village,creation_date,last_created_by) VALUES (?, ?, ?,?) ", Statement.RETURN_GENERATED_KEYS); districtPS.setInt(1, viewmodel.getDistrictid()); districtPS.setString(2, viewmodel.getVillage()); districtPS.setTimestamp(3, getCurrentDate()); districtPS.setInt(4,viewmodel.getUserid()); districtPS.executeUpdate(); ResultSet keySet = districtPS.getGeneratedKeys(); int villageId = 0; if (keySet.next()) { villageId = keySet.getInt(1); } // to insert in regions table districtPS = connection.prepareStatement(" INSERT INTO m_regions(villageid,creation_date,last_created_by) VALUES (?, ?, ?) ", Statement.RETURN_GENERATED_KEYS); districtPS.setInt(1, villageId); districtPS.setTimestamp(2, getCurrentDate()); districtPS.setInt(3,viewmodel.getUserid()); districtPS.executeUpdate(); }

thanks for the comment. do you mean this way PreparedStatement villageandRegionPS = null; PreparedStatement regionPS = null; PreparedStatement villagePS = null;
Thanks, it is not home work, once the record is inserted into first table, i have to take id from the first table and insert the same into second table. I am sorry since i am new to this, i am struggling and running out of time also. Please help
I am sorry to disturb you again and again but i have no other go other than asking you, Please
still, you need to close districtsPS right before creation of new one, just to avoid resource leaks.

Collectives™ on Stack Overflow

batch insert takes only one query and fails to take all the query in java

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related