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:

Oracle

Postgres

MSSQL

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:

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:

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:

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:

You will then put annotation on the 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:

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

Example:

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.

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

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:

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

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:

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


How to comment

The comment section below is to discuss documentation on this page.

If you have an issue, or discover bug, please follow instructions on the Support page