I have this method :

public void ExecuteSQL(String SQL)
{
Koneksi getCn = new Koneksi();
Connection cnExecute = null;
try {
cnExecute = getCn.getConnection();
int rowNgefek = 0;
Statement stExecute = cnExecute.createStatement();
cnExecute.setAutoCommit(true);
stExecute.executeUpdate(SQL, rowNgefek);
cnExecute.commit();
closingConnection (cnExecute,stExecute);
}
catch (SQLException e)
{
System.out.println(e.toString());
}
}

If insert/delete/update raise error, I want to rollback all transactions.
It means I have to use cnExecute.rollback();
but I dont know where I should put that code..
In catch(SQLException e) does not recognize cnExecute.rollback();

Thanks,

Kusno

Edited 3 Years Ago by happygeek: fixed formatting

I have found the answer. Thanks anyway.

public class Data
{
Connection cnExecute = null;
int rowNgefek = 0;
Statement stExecute;


public void ExecuteSQL(ArrayList<String>  SQLArray)
{
Koneksi getCn = new Koneksi();
String SQL;
rowNgefek = 0;
try {
cnExecute = getCn.getConnection();
stExecute = cnExecute.createStatement();
cnExecute.setAutoCommit(false);
for (int i=0;i <= SQLArray.size()-1; i++)
{
SQL = SQLArray.get(i).toString().trim();
rowNgefek = stExecute.executeUpdate(SQL);
}
}
catch (SQLException e)
{
rowNgefek = 0;
JOptionPane.showMessageDialog(null, e.toString(),"Warning",JOptionPane.OK_OPTION);
}
finally
{
if(rowNgefek<=0)
{
try
{
cnExecute.rollback();
}
catch(SQLException e)
{


}
}
else
{
try
{
cnExecute.commit();
}
catch(SQLException e)
{
System.out.println(e.toString());
}
}
closingConnection (cnExecute,stExecute);
}



}

Edited 3 Years Ago by happygeek: fixed formatting

I can be wrong here, but I think that rollback only works for a current transaction. If you use commit, you close that transaction.

So you should catch your error before the commit, and roll back then too. If all is fine you can proceed to commit.

Also, autocommit should be set to false I think. Else your program is going to commit as soon as you release your query.

Edit: Ack, excuse me. Sort of scrolled down as soon as I saw the problem and thought of a possible answer. I should watch before I type :p. Glad you found a solution though.

public static boolean INSERT(Payment argPayment, Connection con) {
        boolean result = false;
        Connection mastercon = MyConnection.getMasterConnection();

        try {
//            String selectStatement = "SELECT SUM(AMOUNT) AS TOTALAMOUNT, SUM(PAID) AS TOTALPAID,OUTSTANDING FROM PAYMENT WHERE SUBID=? AND OFFICEID=? GROUP BY SUBID";
            String selectStatement = "SELECT PAID AS TOTALPAID,OUTSTANDING FROM PAYMENT WHERE SUBID=? AND OFFICEID=? AND ID = (SELECT MAX(ID) FROM PAYMENT WHERE SUBID=? AND OFFICEID=?) GROUP BY SUBID";
            PreparedStatement stmt = con.prepareStatement(selectStatement);
            stmt.setInt(1, argPayment.getSubid());
            stmt.setInt(2, argPayment.getOfficeID());
            stmt.setInt(3, argPayment.getSubid());
            stmt.setInt(4, argPayment.getOfficeID());
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                argPayment.setSumPaid(rs.getFloat("TOTALPAID"));
                argPayment.setOutstanding(rs.getFloat("OUTSTANDING"));
//                argPayment.setSumAmount(rs.getFloat("TOTALAMOUNT"));
            }

            String insertStatement = "INSERT INTO PAYMENT(SUBID,AMOUNT,PAID,OUTSTANDING,ENTRYDATE,REMARK,COURIERCHARGE,LESSPAYMENT,OFFICEID) VALUES (?,?,?,?,?,?,?,?,?)";
            PreparedStatement stat = con.prepareStatement(insertStatement);
            stat.setInt(1, argPayment.getSubid());
            stat.setFloat(2, argPayment.getAmount());
            stat.setFloat(3, argPayment.getAmount() + argPayment.getSumPaid());
            stat.setFloat(4, argPayment.getOutstanding() - argPayment.getAmount());

            stat.setDate(5, argPayment.getEntryDate());
            stat.setString(6, argPayment.getRemark());
            stat.setFloat(7, argPayment.getCourierCharge());
            stat.setFloat(8, argPayment.getLessPayment());
            stat.setInt(9, argPayment.getOfficeID());

            con.setAutoCommit(false);
            if (stat.executeUpdate() > 0) {
                int subid = argPayment.getSubid();
                Purchase purchase = DBPurchase.retrive(subid, argPayment.getOfficeID(), con);
                int partyid = purchase.getParty().getPartyID();
                Account partyAccount = DBAccount.retrieveAccountByIDandType(partyid, "PARTY", mastercon);
                Account officeAccount = DBAccount.retrieveAccountByIDandType(argPayment.getOfficeID(), "OFFICE", mastercon);
                AccountTrack partyAccountTrack = new AccountTrack();
                partyAccountTrack.setId(partyAccount.getId());
                partyAccountTrack.setCredit(true);
                partyAccountTrack.setDate(argPayment.getEntryDate());
                partyAccountTrack.setAmount(argPayment.getAmount());
                partyAccountTrack.setDescription("Payment for payment id-" + argPayment.getId());
                if (DBAccountTrack.insert(partyAccountTrack)) {
                    AccountTrack officeAccountTrack = new AccountTrack();
                    officeAccountTrack.setId(officeAccount.getId());
                    officeAccountTrack.setCredit(false);
                    officeAccountTrack.setDate(argPayment.getEntryDate());
                    officeAccountTrack.setAmount(argPayment.getAmount());
                    officeAccountTrack.setDescription("Payment for payment id-" + argPayment.getId());

                    if (DBAccountTrack.insert(officeAccountTrack)) {
                        AccountTrack pAccountTrack = new AccountTrack();
                        pAccountTrack.setId(partyAccount.getId());
                        pAccountTrack.setCredit(false);
                        pAccountTrack.setDate(argPayment.getEntryDate());
                        float amount = argPayment.getLessPayment() + argPayment.getCourierCharge();
                        pAccountTrack.setAmount(amount);
                        pAccountTrack.setDescription("Less Payment and Courier Charge-" + argPayment.getId());

                        if (DBAccountTrack.insert(pAccountTrack)) {
                            AccountTrack oAccountTrack = new AccountTrack();
                            oAccountTrack.setId(officeAccount.getId());
                            oAccountTrack.setCredit(true);
                            oAccountTrack.setDate(argPayment.getEntryDate());
                            float officeAmount = argPayment.getLessPayment() + argPayment.getCourierCharge();
                            oAccountTrack.setAmount(amount);
                            oAccountTrack.setDescription("Less Payment and Courier Charge-" + argPayment.getId());

                            result = DBAccountTrack.insert(oAccountTrack);
                            con.commit();                            
                        }

                    }
                }
            }

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);
        }finally{
            try{
                con.setAutoCommit(true);
                mastercon.close();
            }catch(Exception e){
            }
        }
        return result;
    }

Edited 3 Years Ago by pyTony: fixed formatting

Comments
This code is absolutely useless as a response to the question.
194 Posts and still no code tags
This article has been dead for over six months. Start a new discussion instead.