I use ORMLite in my Android app, and there are times when I need sqlite INSERT OR UPDATE functionality. I know that ORMLite has createOrUpdate , but this is not appropriate for my specific case.
I will explain my case with an example. I have two tables A and B They have a many-to-many relationship between them, which is represented using a separate M mapping table. Tables A and B have manually created primary keys, and M uses generatedId = true . Two columns in M that store foreign keys up to A and B have uniqueCombo = true installed on them. Therefore, when I get an entry from my backend (as JSON) that already exists in M , when I try to call createOrUpdate , it will fail with the 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 the id does not exist in the model, the update instruction is executed, which, due to the UNIQUE restriction, will fail, but since the id column will be generated automatically, I do not know this (I can request a table with this unique combo, and then find id , but in my schema there are a lot of such tables, and for this table for custom logic circuits it will be a nightmare !!)
To fix this, I plan to use sqlite INSERT OR REPLACE , which in case of conflict will delete the existing record and replace it with a 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 the way I thought about implementing it — let ORMLite prepare the INSERT , then replace INSERT with INSERT OR REPLACE , and then execute the statement. My question is: are there any hooks in ORMLite that would allow me to do this (get the INSERT )?