Generating Unique Ids for Primary Key values using JDXA ORM

primary-key-img

Using primary keys to uniquely identify persistent objects is a fundamental requirement for all apps. A primary key may consist of only one attribute, or it may consist of multiple attributes (composite primary key). The data type of a primary key attribute is typically “int” or “long” but it could also be “String”. For example, a UUID string may serve as a primary key attribute value. JDXA, an innovative Object Relational Mapping (ORM) product, can handle both single attribute and composite primary keys.

A primary key value may be readily available from the data source from which a persistent object is created in an app. For example, data for an object may be retrieved from a server and that data may also contain the primary key value. If the primary key value for a new object is not available from the data source, it needs to be somehow generated as part of the persistence mechanism.

In this article, we will primarily discuss a few techniques for generating primary key attribute values in which the primary key consists of only one attribute whose data type is “int” or “long”. Incidentally, with the unique sequence generator feature of JDXA (described below), one may also create a “String” value for a primary key attribute by concatenating the “long” value returned by a sequence generator with some other text. The article ends with pros and cons of using these different techniques.

With regard to generating unique primary key values of persistent objects, some parts of the following discussion assume that JDXA ORM is used with a SQLite database. However, these techniques may also work with other databases if the databases provide a similar functionality (e.g., an AUTOINCREMENT column).

Let’s assume the following definition of a persistent class Employee, where the id attribute serves as the primary key:

public class Employee {
    private int id;
    private String name;
    private Date DOB;
    private boolean exempt;
    private float compensation;

    /**
    * Default no-arg constructor needed for JDX
    */
    public Employee() {
    }
    // Other constructors and accessor (setter/getter) methods omitted

CASE A

In this case, we want the SQLite database to generate the primary key (id) value with an AUTOINCREMENT column.

  • Define the mapping specification for the Employee class as follows:
    CLASS Employee
        PRIMARY_KEY id
        SQLMAP FOR id SQLTYPE 'INTEGER PRIMARY KEY AUTOINCREMENT'
        RDBMS_GENERATED id
    ;

    Note: You need to specify the RDBMS_GENERATED statement in order to tell JDXA that the values of the specified attributes (e.g., id) will automatically be generated by the underlying database.

  • Now, when you insert (save) an Employee object whose id attribute has not been initialized before the insert operation, you have two options:
    1. Ask JDXA to initialize the id attribute of the in-memory Employee object with the RDBMS-generated (AUTOINCREMENT) column value (CASE A1).
      You can do this by using a flag value of JDXS.IFLAG_INIT_WITH_RDBMS_GENERATED_PKEY_VALUE when invoking the insert method. For example,
      Employee emp = new Employee("Mark", …);
      jdxHandle.insert(emp,
      JDXS.IFLAG_INIT_WITH_RDBMS_GENERATED_PKEY_VALUE, null);

      Alternatively, you may also invoke a newly provided insert method in the JDXHelper class:

      jdxHelper.insert(emp,
      JDXS.IFLAG_INIT_WITH_RDBMS_GENERATED_PKEY_VALUE);
    2. Ask JDXA to not initialize the id attribute of the in-memory Employee object with the RDBMS-generated (AUTOINCREMENT) column value (CASE A2).
      This is the default behavior if you don’t use the flag JDXS.IFLAG_INIT_WITH_RDBMS_GENERATED_PKEY_VALUE when invoking the insert method. For example,
      Employee emp = new Employee("Mark", …);
      jdxHandle.insert(emp, 0, null);

      Alternatively, you may also invoke a newly provided insert method in the JDXHelper class:

      jdxHelper.insert(emp, 0);

      You may also use the existing insert method of JDXHelper as follows:

      jdxHelper.insert(emp, true);

CASE B

In this case, we don’t want the SQLite database to generate the primary key (id) value with an AUTOINCREMENT column.

You may use the JDXA-provided named sequence generator feature to efficiently generate unique ids, which you can assign to your objects before saving them in the database (CASE B1).

  • Define the mapping specification with a sequence generator as follows:
    CLASS Employee
        PRIMARY_KEY id
    ;
    SEQUENCE EmpIdSequence MAX_INCREMENT 10

    Note: EmpIdSequence is the name of the JDXA sequence generator. You may tweak the MAX_INCREMENT value to a higher or lower number depending on how many unique ids you would need to generate in a typical session of your app and how many unique ids you want to reserve in the database at a time.

  • Now, when you want to insert (save) an Employee object whose primary key (id) attribute has not been initialized before the insert operation, you may invoke the getNextSeq() method to obtain a unique id and assign it the id attribute as follows:
    // Initialize a sequence generator for EmpIdSequence such that it
    // can provide 5 unique sequence id values before going to the
    // database for the next set of 5 unique id values. Here the increment
    // value is 5. By the way, the maximum increment value can be 10 in
    // this case per the mapping definition of EmpIdSequence
    JDXSeqUtil empSeqUtil = new JDXSeqUtil("EmpIdSequence", jdxSetup, 5);

    // Create and save a few Employee objects
    Employee emp = new Employee("Mark", …);
    emp.setId(empSeqUtil.getNextSeq());
    jdxHandle.insert(emp, 0, null);emp = new Employee("Bob", …);
    emp.setId(empSeqUtil.getNextSeq());
    jdxHandle.insert(emp, 0, null);

    Notes:
    The sequence increment value of 5 may be changed depending on the needs of your app per session. The higher the value, the more efficient the sequence generation operation would be because it would require fewer database trips to fetch (reserve) the next set of unique sequences. However, since any unused sequence values in this session will be unavailable (lost) for the next session, you may not want to use a very high increment value.

    You may pass the empSeqUtil.getNextSeq()value directly to a constructor of the Employee class instead of using the setId() method.

Pros and Cons of different techniques for primary key generation:

CASE A1

Using AUTOINCREMENT database column; JDXA initializes the primary key attribute with the database-generated column value.

Pros:
The database-generated primary key attribute value is immediately available for use in the app after the object is inserted (saved).

Cons:
There is a performance penalty as JDXA has to perform an additional database call to obtain the value of the AUTOINCREMENT column after inserting other column values of the object.

CASE A2

Using AUTOINCREMENT database column; JDXA does not initialize the primary key attribute with the database-generated column value.

Pros:
There is a performance benefit as JDXA does not have to perform an additional database call to obtain the value of the AUTOINCREMENT column after inserting other column values of the object.

Cons:
The database-generated primary key attribute value is not immediately available for use in the app after the object is inserted (saved). This may not matter if the app does not need the primary key value right away. Moreover, if the object is subsequently fetched from the database using a query, it would come with its primary key attribute value properly initialized.

CASE B1

Using a JDXA provided named sequence generator; programmatically generating and assigning the unique ids to the primary key attributes.

Pros:
There is a performance benefit as JDXA can efficiently dispense many unique sequence ids before making a database trip for the next set of unique sequence ids.

The primary key attribute values are immediately available for use in the app before the object is inserted (saved). Such values may be used to initialize foreign key attributes in related objects.

Compared to using the AUTOINCREMENT column feature, the JDXA sequence generator feature (with a large increment value) should provide better performance. That’s because, without using the JDXA sequence generator feature, the database has to go through the overhead of generating a unique id for an AUTOINCREMENT column every time a new row (object) is inserted.

Cons:
There is some additional (though very minimal) programming code that must be written to define a sequence generator in the mapping specification and then to use it in the app.

Using primary keys to uniquely identify persistent objects is an important requirement for all apps. JDXA, a simple yet powerful, and non-intrusive ORM for Android, provides many flexible and efficient ways to handle primary keys depending upon the application needs. Learn more about JDXA, including how to get a free trial version, by visiting Software Tree’s website at http://www.softwaretree.com.

About Damodar Periwal

Damodar Periwal is the founder of Software Tree. He has an extensive background in databases, transaction processing, as well as distributed and object-oriented technologies. Damodar has more than 25 years of industry experience, having worked at leading companies like Google, TIBCO, Borland, Ashton-Tate, and Tandem Computers. Damodar has an MS (Computer Science) degree from University of Wisconsin (Madison) and an undergraduate engineering degree from Birla Institute of Technology and Science, Pilani (India).
This entry was posted in Uncategorized and tagged , , , , , , , , . Bookmark the permalink.

2 Responses to Generating Unique Ids for Primary Key values using JDXA ORM

  1. Amy says:

    This is a topic which is close to my heart…
    Best wishes! Where are your contact details though?

Leave a Reply

Your email address will not be published. Required fields are marked *