CODEWITHSUNDEEP
sqlitejdbctransactions
yaylitzis
yaylitzis

Reputation: 5534

Transactions with Prepared Statements fails

I try to use transactions with prepared statementsin order to update table INVENTORY where :

CREATE TABLE "INVENTORY" (
    "product" VARCHAR NOT NULL,
    "version" VARCHAR NOT NULL,
    "lot" VARCHAR,
    "barcode" VARCHAR,
    "quantity" INTEGER,

    PRIMARY KEY ("barcode")
);

What I want to do is to insert a new row, but if in my table a row exists with the same primary key then to update it by adding the old value + the new.

I read this and this and I think I followed the instructions right. 

String sqInsert = "INSERT INTO INVENTORY VALUES ('100541026044','01','301610101','10054102604401301610101','5000')";
String sqUpdate = "UPDATE INVENTORY set quantity = quantity + 5000 where barcode='10054102604401301610101'";

// transaction block start   
c.setAutoCommit(false);         

stm = c.prepareStatement(sqInsert);    
System.out.println("try to insert");
result = stm.executeUpdate();

System.out.println("try to update");
stm = c.prepareStatement(sqUpdate);
result = stm.executeUpdate();

c.commit(); //transaction block end
System.out.println("Done!");

In my table a row with barcode=10054102604401301610101 exists, so I want the update to be executed. However, I get

try to insert
Error inserting products :[SQLITE_CONSTRAINT]  Abort due to constraint violation (UNIQUE constraint failed: INVENTORY.barcode)

Note: (I don't set the values from query, but with stm.setString(1, "string");) I just want to be short the code here

Upvotes: 0

Views: 90

Answers (1)

Serg M Ten
Serg M Ten

Reputation: 5606

Of course it will fail due to a duplicated primary key on the bar code. First do the update

stm = c.prepareStatement(sqUpdate);
result = stm.executeUpdate();

Then check the value of result which will be the number of rows affected by the update. If it is zero then the product with given barcode didn't exist so after the update do

if (0==result) {
    stm = c.prepareStatement(sqInsert);    
    stm.executeUpdate();
}

You'll want to put everything under a try {} catch {} to avoid a resource leak or leaving the connection unusable

try {
    c.setAutoCommit(false);
    stm = c.prepareStatement(sqUpdate);
    result = stm.executeUpdate();
    if (0==result) {
        stm.close();
        stm = null;
        stm = c.prepareStatement(sqInsert);    
        stm.executeUpdate();
        stm.close();
        stm = null;
    }
    c.commit();
} catch (SQLException e) {
    if (stm!=null) stm.close();
    c.rollback();
}

Upvotes: 2

Related Questions