How to Upsert item with unique index in Android Room?

Question

I have JSON data in an API host like this

   "product": {
      "barcode": "2489752310342",
      "productName": "Trench coat",
      "priceList": [
        {
          "price": 345
        },
        {
          "price": 123
        }
      ]
    }

I'm fetching data in this form and writing to SQLite with Android Room database. I have Product and Price entities and there is a one-to-many relationship between them. I'm storing the data in Product Entity's priceList in Price entity (I know I can store this as a column in Product with string type with the help of Type Converters but my manager wanted me to store it in another Entity).

Product.java

@Entity(tableName = "product", indices = {@Index(value = {"barcode"},
        unique = true)})
public class Product {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    private int id;

    @ColumnInfo(name = "barcode")
    private String barcode;

    @ColumnInfo(name = "product_name")
    private String productName;

    @ColumnInfo(name = "price_list")
    @Ignore
    private ArrayList<Price> priceList;
}

Price.java

@Entity(tableName = "price",
        foreignKeys = @ForeignKey(
                entity = Product.class,
                parentColumns = "id",
                childColumns = "product_id",
                onDelete = ForeignKey.CASCADE,
                onUpdate = ForeignKey.CASCADE
        ))
public class Price {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    private int id;

    @ColumnInfo(name = "product_id")
    private int productId;

    @ColumnInfo(name = "price")
    private int price;
}

Since defining a relationship between these requires a product id, after I fetch data from API I Upsert Product to the database like this

private void upsertProduct(Product product){
    int insertedRowId = (int) productDao.upsertData(product);
    product.setId(insertedRowId);
    addPricesDependOnProductId(product);
}

After I assign an id to Product, I assign this id to Price product id like this

private void addPricesDependOnProductId(Product product) {
    for (Price price : product.getPriceList()) {
        price.setProductId(product.getId());
        priceDao.upsertPrice(price);
    }
}

This works like a charm and I can get Price and Product data using the intermediate data class.

After these things are done my manager wanted me to do delete all the rows in the Price table and insert them again without changing the ids of Product items.

I deleted all the items in the Price table and fetch the data from API again. But I faced "UNIQUE constraint failed: product.barcode (code 2067 SQLITE_CONSTRAINT_UNIQUE)" error. As I understand room throws this exception because I tried to insert an already existing barcode which must be unique.

ProductDao.java

@Dao
public interface ProductDao {
    @Upsert
    long upsertData(Product product);
}

PriceDao.java

@Dao
public interface PriceDao {
    @Query("DELETE FROM price")
    void deleteAllPrices();

    @Upsert
    void upsertPrice(Price price);

}

I'm expecting if a Product item already exists in the Product table I'll get the id of it, assign this id to the Price item's product id and write to DB without increasing the primary key of the Product (Product's product id must be the same as the before deleting all the items in the Price table). Is it possible to do this without increase id of the Product?

SOLUTION:

I added this method in ProductDao.java

@Query("SELECT * FROM product WHERE barcode = :barcode")
Product getProductByBarcode(String barcode);

And updated my method like this

private void upsertProduct(Product product){
        String barcode = product.getBarcode();
        new Thread(() -> {
            Product productInDB = productDao.getProductByBarcode(barcode);
            if (productInDB == null) {
                // If there is no product with given unique barcode in DB insert and get id of it.
                int insertedRowId = (int) productDao.upsertData(product);
                // After inserting the product in DB, assign its id to the product object that I've got from API call.
                product.setId(insertedRowId);
                addPricesDependOnProductId(product);
            } else {
                // If product item already exists in DB fetch from the DB and set product's price list to it.
                productInDB.setPriceList(product.getPriceList());
                addPricesDependOnProductId(productInDB);
            }
        }).start();
}

Answer 1

Is it possible to do this without increase id of Product?

Yes. However:-

Room's @Upsert is a convenience method and only copes with typical/common/basic usage. As such to cater for the more complex nature of multiple UNIQUE constraints then you could have a method in an @Dao that effectively mimics an Upsert.

This would require methods that have bodies and thus an @Dao that is an abstract class as opposed to an interface.

For brevity here's an @Dao abstract class, that embodies all the DAOs (not that the Price Daos are used in the demo):-

@Dao
abstract class AllDAOs {
    @Upsert
    abstract long upsertData(Product product);
    @Query("DELETE FROM price")
    abstract void deleteAllPrices();
    @Upsert
    abstract void upsertPrice(Price price);

    /* Query to return the number of matching rows according to barcode or id.
        should only return 0 (no row) or 1 (matched)
     */
    @Query("SELECT count() FROM product WHERE id=:id OR barcode LIKE :barcode")
    abstract int ifProductExists(int id, String barcode);
    /* just in case the product name is changed then allow updating of the product */
    @Query("UPDATE product SET product_name=:productName WHERE barcode LIKE :barcode")
    abstract int updateProduct(String productName, String barcode);

    /* The equivalent of the Upsert that caters with the two UNIQUE indexes */
    @Transaction
    @Query("")
    long /* always want to return the id */ upsertProduct(Product product) {
        /* If the Product exists then UPDATE the product name of the existing row */
        if (ifProductExists(product.getId(),product.getBarcode()) > 0) {
            updateProduct(product.getProductName(), product.getBarcode());
            return product.getId();
        } else {
            /* If the Product does not exist then insert it using the Upsert (as that exists)
                really it would be more correct to use an `@Insert`
             */
            return upsertData(product);
        }
    }
}

Demo

The following demo uses the upsertProduct method to insert the same Product 3 times, without failing and also after the first also updates the Product's name (it is not specified whether or not the name could change, so just in case this feature was included).

The following is the activity code (note .alloMainThreadQueries has been used for brevity and convenience):-

public class MainActivity extends AppCompatActivity {

    TheDatabase db;
    AllDAOs dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        db = TheDatabase.getInstance(this);
        dao = db.geAllDAOs();

        Product prd1 = new Product();
        prd1.setBarcode("2489752310342");
        prd1.setProductName("Trench coat");
        prd1.setPriceList(new ArrayList<Price>(){});
        logInsertProduct(prd1);
        prd1.setProductName("Updated Trench Coat");
        logInsertProduct(prd1);
        logInsertProduct(prd1);

    }

    void logInsertProduct(Product product) {
        String result = "Successful";
        long rowidOfInsert = dao.upsertProduct(product);
        if (rowidOfInsert < 0) result = "Unsuccessful";
        Log.d("UPSERTPRODUCT","The result of insert product " + product.getProductName() + " was " + result + "rowid returned was " + rowidOfInsert);
    }
}

Demo Results

After running the product table:-

i.e. the id is 1 as per the initial upsertProduct, but the name has changed according to the 2nd and 3rd upsertProduct

And the Log includes:-

2023-05-30 18:13:17.325 D/UPSERTPRODUCT: The result of insert product Trench coat was Successfulrowid returned was 1
2023-05-30 18:13:17.331 D/UPSERTPRODUCT: The result of insert product Updated Trench Coat was Successfulrowid returned was 0
2023-05-30 18:13:17.346 D/UPSERTPRODUCT: The result of insert product Updated Trench Coat was Successfulrowid returned was 0

Another alternative would be to use the barcode as the primary key, but that then is less efficient as even though SQLite will store the value as if it were a number (iirc) as the column would be of type TEXT, it would not be an alias of the more efficient rowid (which can be up to twice as fast).

An even riskier, and more complex alternative could be to drop the index on the barcode, do the upsert and then create the index.