Update a column if row exists, and insert row if it doesn't

Question

I have a table INVENTORY with 2 columns [product(primary key) - quantity]. I want to insert to this table a product with its quantity.

public boolean insertPackage(String product, int quantity) 
            throws SQLException, ClassNotFoundException {

        System.out.println("Insert product to Invetory");
        boolean flag=false;
        sq = "INSERT INTO INVENTORY VALUES (?, ?)";

        try {       
            Class.forName(typeDB);
            c = DriverManager.getConnection(path);            
            stm = c.prepareStatement(sq);

            PreparedStatement stm = c.prepareStatement(sq);

            stm.setString(1, product);
            stm.setInt(2, quantity);
            int rowsAffected = stm.executeUpdate();

        } catch (SQLException e) { 
            //There is already a same product in the Inventory
            flag = true;
            System.out.println(e.getMessage());
        } finally {
        if (stm != null)
                    stm.close();
        if (c != null)
                    c.close();
        }     

        return flag; //if the flag is true, then execute insert.
    }

If it returns true, then I search for this product, retrieve the quantity and then update the table with the new quantity. I am wondering if this way I thought, is a good way to check how to perform the insertion or there is a better one.

Answer 1

This is not a good way to check if a product exists because:

-There are many other things that can go wrong ( a lot of different SQLExceptions, not only PK violation ) and you will end up with a true flag.

-You should not use exceptions for something that is normal to happen.

-Throwing and catching an exception is slow.

Try this:

1) select from INVENTORY by product using count:

select count(*) from INVENTORY where product = ?

2) if the count is equal to 0 then execute the insert

else increment the quantity.

Answer 2

In your particular case the easiest solution would be to use SQLite's INSERT OR REPLACE syntax, like so:

public static void main(String[] args) {
    String connectionURL = "jdbc:sqlite:";  // in-memory database
    try (Connection conn = DriverManager.getConnection(connectionURL)) {
        // set up test data
        try (Statement st = conn.createStatement()) {
            st.execute("CREATE TABLE INVENTORY (product VARCHAR(10) PRIMARY KEY, quantity INT)");
            st.execute("INSERT INTO INVENTORY (product, quantity) VALUES ('one', 123)");
        }
        System.out.println("Initial state:");
        dumpTable(conn);

        // real code starts here
        String sql = "INSERT OR REPLACE INTO INVENTORY (product, quantity) VALUES (?, ?)";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, "two");  // product is new, so it will insert
            ps.setInt(2, 234);
            ps.executeUpdate();
            System.out.println();
            System.out.println("First change:");
            dumpTable(conn);

            ps.setString(1, "one");  // product already exists, so it will replace
            ps.setInt(2, 999);
            ps.executeUpdate();
            System.out.println();
            System.out.println("Second change:");
            dumpTable(conn);
        }
    } catch (Exception e) {
        e.printStackTrace(System.err);
    }       

}

private static void dumpTable(Connection conn) throws SQLException {
    try (
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT product, quantity FROM INVENTORY ORDER BY product")) {
        while (rs.next()) {
            System.out.printf(
                    "product \"%s\" - quantity: %d%n", 
                    rs.getString("product"), 
                    rs.getInt("quantity"));
        }
    }
}

However, INSERT OR REPLACE in SQLite is really just a DELETE followed by INSERT, so another solution would be to try and do the UPDATE first, then do an INSERT if the UPDATE doesn't affect any rows. (That might be more efficient if you tend to be doing more updates than inserts.)

public static void main(String[] args) {
    String connectionURL = "jdbc:sqlite:";  // in-memory database
    try (Connection conn = DriverManager.getConnection(connectionURL)) {
        // set up test data
        try (Statement st = conn.createStatement()) {
            st.execute("CREATE TABLE INVENTORY (product VARCHAR(10) PRIMARY KEY, quantity INT)");
            st.execute("INSERT INTO INVENTORY (product, quantity) VALUES ('one', 123)");
        }
        System.out.println("Initial state:");
        dumpTable(conn);

        // real code starts here
        updateQuantity("two", 234, conn);
        System.out.println();
        System.out.println("First update:");
        dumpTable(conn);

        updateQuantity("one", 999, conn);
        System.out.println();
        System.out.println("Second update:");
        dumpTable(conn);

    } catch (Exception e) {
        e.printStackTrace(System.err);
    }       

}

private static void updateQuantity(String theProduct, int newQuantity, Connection conn) throws SQLException {
    int rowsAffected;
    try (PreparedStatement psUpdate = conn.prepareStatement("UPDATE INVENTORY SET quantity=? WHERE product=?")) {
        psUpdate.setInt(1, newQuantity);
        psUpdate.setString(2, theProduct);
        rowsAffected = psUpdate.executeUpdate();
    }
    if (rowsAffected == 0) {
        try (PreparedStatement psInsert = conn.prepareStatement("INSERT INTO INVENTORY (product, quantity) VALUES (?, ?)")) {
            psInsert.setString(1, theProduct);
            psInsert.setInt(2, newQuantity);
            psInsert.executeUpdate();
        }
    }
}

private static void dumpTable(Connection conn) throws SQLException {
    try (
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT product, quantity FROM INVENTORY ORDER BY product")) {
        while (rs.next()) {
            System.out.printf(
                    "product \"%s\" - quantity: %d%n", 
                    rs.getString("product"), 
                    rs.getInt("quantity"));
        }
    }
}

In both cases we see:

Initial state:
product "one" - quantity: 123

First update:
product "one" - quantity: 123
product "two" - quantity: 234

Second update:
product "one" - quantity: 999
product "two" - quantity: 234