Surrogate primary keys

ActiveJDBC, relies on the surrogate primary keys. Description is found here: http://en.wikipedia.org/wiki/Surrogate_key. A surrogate key is not generated by ActiveJDBC. Unlike Hibernate, it does not (currently) have any generators for the keys and relies fully on DBMS solution to do this. Depending on a DB implementation, you can use various techniques to achieve this goal.

Key name

By convention, the primary key name is id. If your table has a surrogate primary key column with a name id, you do not have to do anything.

Key Type

The key type is a number that would be able to be auto- incremented by the database without the involvement of the framework.

Most JDBC drivers support returning a generated key as part of an insert in a single operation, making this method of auto-generated keys very efficient. ActiveJDBC uses: Statement.html.getGeneratedKeys() under the hood to set the ID of the currently inserted model.

Here are some SQL examples showing how to setup surrogate primary keys:

MySQL:

CREATE TABLE people (id  int(11) NOT NULL auto_increment PRIMARY KEY, name VARCHAR(56));

Oracle

CREATE TABLE people (id  NUMBER NOT NULL, first_name VARCHAR(56) NOT NULL, last_name VARCHAR(56);

ALTER TABLE people ADD CONSTRAINT people_pk PRIMARY KEY ( id );

CREATE SEQUENCE people_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER people_trigger
    BEFORE INSERT ON people REFERENCING
    NEW AS new
    OLD AS old
    FOR ACH ROW
    begin
select coalesce(:new.id, people_seq.nextval) into :new.id from dual;
end;

Postgres

CREATE TABLE people (id serial PRIMARY KEY, first_name VARCHAR(56) NOT NULL, last_name VARCHAR(56));

MSSQL

CREATE TABLE people (id INT IDENTITY PRIMARY KEY, name VARCHAR(56) NOT NULL, last_name VARCHAR(56));

Key Value, insert vs update

When a new object of a model is created, the value of the ID is obviously null. When an object is looked up from a database, the ID value is populated to appropriately as any other attributes.

When a save() method is called, it will generate either an INSERT or UPDATE query, based on a value of the ID attribute. If the id == null, it will assume that this model represents a new record and will generate an INSERT, if the id != null, then it will generate an UPDATE query.

A developer could set an ID manually, but this is not its typical usage.

Override Standard Key Value behavior

Usually you do not set ID of a model. ActiveJDBC will manage that. If you find setting ID of a model, stop and question what you are doing. If you indeed decide to do this, keep in mind that ActiveJDBC uses ID presence to determine if it needs to generate INSERT or UPDATE statements.

However, sometimes a developer knows better than the framework, when to update and when to insert. In such cases, you can call insert directly like so:

Apple apple = new Apple();
apple.set("apple_type", "sweet");
apple.setId(1);
apple.insert();

Apple apple1 = new Apple();
apple1.set("apple_type", "sour");
apple1.setId(2);
apple1.insert();

This way you can fully control the value of the ID and still do either update or insert.

What about composite PKs?

You can have one of two scenarios:

Composite keys and ID column

You can have a composite PK in your table, as long as there is also an ID column ActiveJDBC can watch for inserts and updates. Composite keys are transparent to ActiveJDBC(in a sense it does not see or cares about them). This means that if you set attribute values into your model that violate the integrity of your data, ActiveJDBC will not complain, but the DB will (by throwing an exception save). This follows the same philosophy: ActiveJDBC does not implement what is already implemented by a lower level technology on stack.

Composite keys and no ID column

In some cases you cannot add a new column to an existing table. When this happens, ActibeJDBC stil provides some support. You can use a CompositePK annotation:

@CompositePK({ "first_name", "last_name", "email" })
public class Developer extends Model {
    static {
        validatePresenceOf("first_name", "last_name", "email").message(
                "one or more composite PK's missing!!!");
    }
}

The validator (see above) can also be used to prevent a trip to a database in case you do not have all required columns for a composite PK. Searching with composite keys:

Developer.createIt("first_name", "Johnny", "last_name", "Cash", "email", "j.cash@spam.org", "address", "123 Pine St");
Developer dev = Developer.findByCompositeKeys("Johnny", "WrongName", "j.cash@spam.org");
// ... 

Ensure that the order of values is the same as in the definition of the CompositePK annotation. For more examples, refer to tests: CompositePkTest.java

Override primary key

If your table cannot provide a primary key column named id (for instance due to corporate naming standards), you can override it with @IdName annotation.

Example: let's say you have a table PEOPLE:

CREATE TABLE people (
  person_id int(11) NOT NULL AUTO_INCREMENT,
  first_name              VARCHAR(124),
  last_name               VARCHAR(124),
)

You will then put annotation on the model:

@IdName("person_id")
public class Person extends Model{}

This way, the model will know to work with column person_id and not id as a primary key.

MySQL usage

MySQL probably has the best support for this feature, since it has a direct syntax for them:

CREATE TABLE people (
  id  INT(11) DEFAULT NULL AUTO_INCREMENT PRIMARY KEY, 
  first_name VARCHAR(56), 
  last_name VARCHAR(56), 
  dob DATE, 
  graduation_date DATE, 
  created_at DATETIME, 
  updated_at DATETIME);

This example is taken from ActiveJDBC tests. The surrogate PK id will be properly incremented by MySQL.

Example:

Person p = new Person();
p.getId(); //<<< ===== returns null
p.set("first_name", "Igor").set("last_name", "Polevoy").saveIt();
p.getId(); //<<< ===== returns non-null value, type depends on DBMS driver conversion

Oracle usage with sequences and triggers

In Oracle things a bit more involved. The strategy is to create a sequence to generate numbers, and trigger to enter these numbers into the id column when inserting and not doing this when updating. In other words, if there an insert or update statement without this value, trigger provides one from a sequence, and if one is already provided by the SQL statement, trigger just ignores it.

CREATE TABLE people (
   id  NUMBER NOT NULL, 
   name VARCHAR(56), 
   last_name VARCHAR(56), 
   dob DATE, 
   graduation_date DATE, 
   created_at TIMESTAMP, 
   updated_at TIMESTAMP)

ALTER TABLE people ADD CONSTRAINT people_pk PRIMARY KEY ( id )

CREATE SEQUENCE people_seq START WITH 1 INCREMENT BY 1

CREATE OR REPLACE TRIGGER people_trigger
    BEFORE INSERT ON people REFERENCING
    NEW AS new
    OLD AS old
    FOR EACH ROW
    begin
select coalesce(:new.id, people_seq.nextval) into :new.id from dual;
end;

This SQL creates a DB structure that allows ActiveJDBC behave exactly the same as with MySQL:

Person p = new Person();
p.getId(); //<<< ===== returns null
p.set("first_name", "Igor").set("last_name", "Polevoy").saveIt();
p.getId(); //<<< ===== returns non-null value, type depends on DBMS driver conversion

Oracle usage with sequences and no triggers

In order to reduce amount of SQL to generate your schema, you can adapt a simpler strategy: create only one sequence and use it as a source of generated values for all tables:

CREATE TABLE people (
   id  NUMBER NOT NULL, 
   name VARCHAR(56), 
   last_name VARCHAR(56), 
   dob DATE, 
   graduation_date DATE, 
   created_at TIMESTAMP, 
   updated_at TIMESTAMP)

ALTER TABLE people ADD CONSTRAINT people_pk PRIMARY KEY ( id )

CREATE SEQUENCE main_seq START WITH 1 INCREMENT BY 1

When defining a model, however, you will need to provide this piece of information to all models where you intend to use this sequence:

@IdGenerator("main_seq.nextVal")
public class Person extends Model{}

The usage code, behavior and expectations will be exactly the same with this strategy. As you can see, this seems to be simpler than using triggers, but it has a drawback. Since the same sequence will be used across multiple tables, the number values in a single table could (will) be out of sequence. But.. since this is a surrogate key anyway, it does not matter.

PostgreSQL usage

PostgreSQL has a capability similar to that of MySQL, but different syntax - no sequences are required:

CREATE TABLE people (
   id SERIAL PRIMARY KEY, 
   name VARCHAR(56) NOT NULL, 
   last_name VARCHAR(56), 
   dob DATE, graduation_date DATE, 
   created_at TIMESTAMP, 
   updated_at TIMESTAMP);

The keyword SERIAL in PostgreSQL does the same as MySQL?s AUTO_INCREMENT