Java : Insert query-Exception

Question

Dear all,

I have a doubt regarding database operation.I have one insert query that should run for 10 times. the loop starts and inserted 4 or 5 val while inserting 6th, the db connection got failed for a while and again connected. then what will happen, whether it skips that particular val or throws exception or roll back th entire operation?

EDIT : Sample Code

try
{
    String sql_ji_inser="insert into job_input values (?,?)";
    PreparedStatement pst_ji_inser=OPConnect.prepareStatement(sql_ji_inser);

    for(int i=0;i<v_new_data.size();i++)
            {
      Vector row=new Vector();
              row=(Vector)v_new_data.get(i);

              job_id=Integer.parseInt(row.get(0).toString());
              item_no=Integer.parseInt(row.get(1).toString());
              pst_ji_inser.setInt(1,job_id);
              pst_ji_inser.setInt(2,item_no);
              pst_ji_inser.addBatch();
            }
            System.out.println("No of rows inserted"+pst_ji_inser.executeBatch().length);
    }
    catch(Exception ex)
    {
           System.out.println("********Insert Exception*********************");
           ex.printStackTrace();
           return false;
    }

Is this the right way

try 
{
int count=0;// for checking no of inserting values
OPConnect.setAutoCommit(false);
String sql_ji_inser="insert into job_input values (?,?)";
PreparedStatement pst_ji_inser=OPConnect.prepareStatement(sql_ji_inser);
for(int i=0;i<v_new_data.size();i++)
    {
    job_id=Integer.parseInt(row.get(0).toString());
    item_no=Integer.parseInt(row.get(1).toString());
    pst_ji_inser.setInt(1,job_id);
    pst_ji_inser.setInt(2,item_no);
    pst_ji_inser.addBatch();
    count++;
    }
int norowinserted=pst_ji_inser.executeBatch().length;
if(count==norowinserted)
    {   
    OPConnect.commit();
    }
}
catch(Exception ex)
{
System.out.println("********Insert Exception*********************");
OPConnect.rollback();
ex.printStackTrace();
return false;
}

Answer 1

That depends on how you're inserting the rows. If you're inserting them in a single transaction on a connection which has auto-commit turned off by connection.setAutoCommit(false) and you're commiting the connection after completing the insert queries using connection.commit() and you're explicitly calling connection.rollback() inside the catch block, then the entire transaction will be rolled back. Otherwise, you're dependent on environmental factors you have no control over.

See also:

• When to call rollback?

---

Update: here's a rewrite of your code. Note that the connection and statement should be declared before the try, acquired in the try and closed in the finally. This is to prevent resource leaking in case of exceptions.

String sql = "insert into job_input values (?, ?)";
Connection connection = null;
PreparedStatement statement = null;

try {
    connection = database.getConnection();
    connection.setAutoCommit(false);
    statement = connection.prepareStatement(sql);

    for (List row : data) {
        statement.setInt(1, Integer.parseInt(row.get(0).toString()));
        statement.setInt(2, Integer.parseInt(row.get(1).toString()));
        statement.addBatch();
    }

    statement.executeBatch();
    connection.commit();
    return true;
} catch (SQLException e) {
    if (connection != null) try { connection.rollback(); } catch (SQLException logOrIgnore) {}
    e.printStackTrace();
    return false;
} finally {
    if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
    if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
}

I am by the way not a fan of returning a boolean here. I'd just make the method void, let the catch throw e and put the calling code in a try-catch.