ORMLite insert or replace

1.1k Views Asked by At

I am using ORMLite in my android app and there are cases where I need sqlite's INSERT OR UPDATE functionality. I am aware that ORMLite has createOrUpdate but it doesn't serve my particular case.

I will explain my case with an example. I have two tables A & B. They have a many-to-many relationship between them which is represented using a separate mapping table M. Tables A & B have manually generated primary keys and M uses generatedId = true. The two columns in M that store the foreign keys to A & B have uniqueCombo = true set on them. Therefore when I get a record from my backend (as JSON) which already exist in M when I attempt to call createOrUpdate it would fail on UNIQUE constraint. The reason for this is

public CreateOrUpdateStatus createOrUpdate(T data) throws SQLException {
    if (data == null) {
        return new CreateOrUpdateStatus(false, false, 0);
    }
    ID id = extractId(data);
    // assume we need to create it if there is no id
    if (id == null || !idExists(id)) {
        int numRows = create(data);
        return new CreateOrUpdateStatus(true, false, numRows);
    } else {
        int numRows = update(data);
        return new CreateOrUpdateStatus(false, true, numRows);
    }
}

As you can see above, if there is no id in the model to be persisted, an update statement is executed which because of the UNIQUE constraint would fail, but since the id column is auto generated, I don't know this (I can query the table with this unique combo & then find the id, but there are a lot many tables like this in my schema and having custom logic to do this for all tables would be a nightmare!!)

To remedy this I am planning to use sqlite's INSERT OR REPLACE which on conflict would delete the existing record and replace it with the new one. From sqlite docs

When a UNIQUE or PRIMARY KEY constraint violation occurs, the REPLACE algorithm deletes
pre-existing rows that are causing the constraint violation prior to inserting or 
updating the current row and the command continues executing normally

This is way I thought of implementing it - let ORMLite prepare an INSERT statement, then replace INSERT with INSERT OR REPLACE and then execute the statement. My question is are there any hooks in ORMLite that would let me do this (get the INSERT statement)?

0

There are 0 best solutions below