Java : Insert query-Exception

Question

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;
}

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

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.

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 18, 2010 at 4:22

BalusC

1.1m377 gold badges3.7k silver badges3.6k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Thorin Oakenshield Over a year ago

@ BalusC : Thank you ,I didnt use any autocommit() and rollback().in try block i used the insert query and using addbatch() for all values and finally executed using executeBatch()

BalusC Over a year ago

Then fix it accordingly. By the way, Vector is really a legacy class. Is this a 15 year old codebase which you have to maintain? Since Java 1.2 the ArrayList was introduced as superior replacement to Vector.

Thorin Oakenshield Over a year ago

@ BalusC : Kindly note the Edited post

Collectives™ on Stack Overflow

Java : Insert query-Exception

1 Answer 1

See also:

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

See also:

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related