0

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 by happygeek: fixed formatting

4
Contributors
4
Replies
5
Views
9 Years
Discussion Span
Last Post by stephen84s
0

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 by happygeek: fixed formatting

0

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.

-2
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 by pyTony: fixed formatting

Votes + Comments
This code is absolutely useless as a response to the question.
194 Posts and still no code tags
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.