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.
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 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.
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:
1 2 3 4 5 6 7 8 9
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
1 2 3 4 5 6 7
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:
1 2 3
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
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:
This way, the model will know to work with column
person_id and not
id as a primary key.
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.
1 2 3 4
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:
1 2 3 4
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:
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 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
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