Can I temporarily suspend auto-generated ID in ORM

Question

I am using Android with ORMLite in a small app I am currently writing. The app aims to have a working Import/Export function, for which I use Simple XML framework. And everything works great, up to a point.

The situation is as follows: Object A contains a foreign key referencing Object B, which references Object C through a foreign key itself. Exporting a database is great. Importing works to, with a small caveat, namely that it works as long as the IDs of all object are sequential and start from 1. But if the database is fragmented, i.e. I deleted a record here and there, after I export the database, I have "holes" in the generated XML structure. Example objects could be:

@DatabaseTable("orders")
public class Order {
    @DatabaseField(generatedId = true)
    private int _id;
    @DatabaseField(columnName="date")
    private Date _date;
    @DatabaseField(columnName="cost")
    private double _cost;
    @DatabaseField(foreign = true, columnName="customer_id")
    private Customer _customer;
    // ... more fields, getters and setters
}

@DatabaseTable("customers")
public class Customer {
    @DatabaseField(generatedId = true);
    private int _id;
    @DatabaseField
    private String _name;
    // ... more fields, getters and setters
}

Let's say I have a database with 2 customers (id 1 and 2), which hold respectively orders from 1 through 5 and 6 through 8. Exporting this and then re-importing in a clean database works great. If however I delete customer 1 and their orders and export it, the exporter will write their id as they are, i.e.

<customer id="2">...</customer>

and

<order id="6">...</order>
<order id="7">...</order>
<order id="8">...</order>
<order id="9">...</order>

etc. When I import the data into a fresh database, I would first save the customer object via

custDao.create((Customer)x);

and then each of their orders via

orderDao.create((Order)o);

The problem is that the create function disregards the provided id (which is not 0) and the newly generated id for customer is 1 (in a fresh, empty database). Same for the orders. But since they reference customer with id=2, the link between them is broken.

So after this somewhat long-winded explanation, here is my question: Is there a way to tell ORMLite to take the provided value for a generatedId field and run with it, instead of overwriting it? I would be OK, if any Exception is generated in case the create function finds row already in the table with the same ID, but would proceed with saving the record otherwise... I have thought of a work-around for this: all the objects should be sortable by ID using the Comparator interface; sort the ArrayList with objects to be imported; for each object - read supposed id into an int, - save to database using dao.create, - if the object's new id is different than supposed id, change it via dao.updateId, - move to the next object in the list... But that seems too cumbersome and error-prone: What if the create method tries to generate an id, which you just re-assigned to a previous object with updateId?

I don't believe my situation is so rare, that no one has encountered this before. I would appreciate a solution!

Best regards, Todor

Answer 1

ORMLite supports a allowGeneratedIdInsert=true option to @DatabaseField annotation that allows inserting of an object with the ID already set into a generated-id table. If the value of the ID field is null or default value (0, ...) then the database will generate the ID. This is not supported by all database types (Derby for example). Here's another discussion about this specific topic.

---

I do think the proper thing to do here is to build your object graph in memory, associating the proper Customer on their Order objects before saving an of them to disk. If you read your Customers into memory, then read in the Order objects and set the real Customerobject on each one. When you then create each Customer object in the database, ORMLite will change the id field to the generated one which would change it on the customer_id field saved in each Order as well.

If you have a ton of data and can't read it all into memory at one (or for some other reason) then you could always build a Map<Integer,Integer> and save the Customer id from the XML mapped to the id you get after you create it in the database. Then when you load in the Order objects you can set the new corrected id on the foreign object.

Hope this helps. Let me know some more details about how you are reading in the objects and I can give a better example of how to build the object graph.