Search This Blog

Monday, 12 September 2011

Id Generators - 5

I shall cover the select and assigned generators in this article.

select:

This is used when the primary key is to be generated by some database logic. For e.g. the primary key is assigned by a database trigger. As hibernate did not assign the primary key (and it is not a generated value like auto-increment) how does it set the object's id ?

Hibernate needs a unique key so as to  select the row and retrieving the primary key value.
This is illustrated by the Library_Card table
create table LIBRARY_CARD (
        ID varchar(255) not null default 'UNKNOWN',
        PAN_NUMBER varchar(255) not null,
        ISSUE_DATE datetime,
        RENEWAL_DATE datetime,
        primary key (ID),
        unique (PAN_NUMBER)
);
Each card is associated with a pan number that is unique. The Library Card Id is generated based on the Pan Number provided. The java class for the same is as below:
public class LibraryCard {
    private String panNumber;
    private Date issueDate;
    private Date renewalDate;    
    private String id;
    //setter-getters
}
The mapping document is as below:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.model">
    <class name="LibraryCard" table="LIBRARY_CARD">
        <id name="id" type="string">
            <column name="ID" default="UNKNOWN">
            </column>
            <generator class="select">
                <param name="key">panNumber</param>
            </generator>
        </id>
        <natural-id>
                <property name="panNumber" type="string">
                   <column name="PAN_NUMBER"  />
                </property>
        </natural-id>
        <property name="issueDate" type="timestamp">
            <column name="ISSUE_DATE"  />
        </property>
        <property name="renewalDate" type="timestamp">
            <column name="RENEWAL_DATE"  />
        </property>
    </class>
</hibernate-mapping>
The natural id is very important and must be specified.After insert, Hibernate selects the just inserted row to determine the identifier value assigned by the database. The correct row is located using a unique key. This key is identified by the natural-id.
 If the natural-id is not specified then the key param must be used to indicate the unique key that can be used by Hibernate in its select query.
2204 [main] DEBUG org.hibernate.event.def.AbstractSaveEventListener  - saving [c
om.model.LibraryCard#<null>]
2204 [main] DEBUG org.hibernate.event.def.AbstractSaveEventListener  - executing
 insertions
2219 [main] DEBUG org.hibernate.event.def.AbstractSaveEventListener  - executing
 identity-insert immediately
2219 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister  - Inse
rting entity: com.model.LibraryCard (native id)
2219 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - about to open PreparedSt
atement (open PreparedStatements: 0, globally: 0)
2219 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        LIBRARY_CARD
        (PAN_NUMBER, ISSUE_DATE, RENEWAL_DATE) 
    values
        (?, ?, ?)
...
2266 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - about to open PreparedSt
atement (open PreparedStatements: 0, globally: 0)
2266 [main] DEBUG org.hibernate.SQL  - 
    select
        ID 
    from
        LIBRARY_CARD 
    where
        PAN_NUMBER =?
2282 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - preparing statement
2282 [main] DEBUG org.hibernate.type.StringType  - binding '12AKH58' to paramete
r: 1
2282 [main] DEBUG org.hibernate.type.StringType  - returning '#12AKH58' as colum
n: ID
As can be seen, Hibernate did not attempt to set the id as a part of the object creation process. It let the database generate the value, after which it fired a select query to retrieve the newly created id. The database trigger used here for id creation is as below
create trigger generate_lib_card_PK before insert on library_card for each row
set new.id := CONCAT('#', new.pan_number);

assigned:

This generator is used if you want the application to assign identifiers, It uses the identifier value already assigned to the object's id property. This is the default behaviour if you do not specify a <generator> element.
Consider the simple table :
create table SLOGAN_DETAILS  (
  ID int(11) not null,
  SLOGAN varchar(100) not null,
  POPULATION DECIMAL(10,2)
);
alter table SLOGAN_DETAILS add constraint SLOGAN_DETAILS_PK primary key (ID);

The java class and the mapping document are as below:
public class SloganDetails {
    private Long id;
    private BigDecimal population;
    private String slogan;
    //setter-getters
}
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.model">
    <class name="SloganDetails" table="SLOGAN_DETAILS">    
        <id name="id" column="id">
            <generator class="assigned"/> 
        </id>

        <property name="population" type="big_decimal">
        <column name="POPULATION" precision="10" />
    </property>
    <property name="slogan" type="string">
        <column name="SLOGAN" />
    </property>
    </class>   
</hibernate-mapping>

The code requires that the id field is set with appropriate value before record is saved.
Session session = sessionFactory.openSession();
SloganDetails details = new SloganDetails();
details.setId(1L);
details.setPopulation(new BigDecimal("1233"));
details.setSlogan("Garden city");
Transaction transaction = session.beginTransaction();
session.save(details);
transaction.commit();

The logs generated during the save are as below:
2172 [main] DEBUG org.hibernate.jdbc.JDBCContext  - after transaction begin
2172 [main] DEBUG org.hibernate.event.def.DefaultSaveOrUpdateEventListener  - sa
ving transient instance
2172 [main] DEBUG org.hibernate.event.def.AbstractSaveEventListener  - generated
 identifier: 1, using strategy: org.hibernate.id.Assigned
...
2219 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        SLOGAN_DETAILS
        (POPULATION, SLOGAN, id) 
    values
        (?, ?, ?)

3 comments:

  1. Hi

    If I am using Hibernate annotations, how to use Generators so that trigger will be used to generate primary key?

    Any help is highly appreciable.

    ReplyDelete
  2. The JPA Annotations that you use do not support the select option. Unfortunately I could not find any hibernate extension for the same. One option is to define your own custom generator (http://learningviacode.blogspot.in/2011/11/creating-custom-id-generator.html).
    Ill continue to hunt for some solution. Will update you once I have one.

    ReplyDelete
  3. I have a table (schema below) whose primary key is generated by a user defined function (UDF) inside a trigger.

    The table has two columns (Edif and Apto) wich, together, form a unique key.
    How should I write my hbm.xml file using generator class="select"?

    CREATE TABLE Unidade
    (
    Cod CHAR(4) NOT NULL PRIMARY KEY,
    Edif SMALLINT NOT NULL,
    Apto SMALLINT NOT NULL
    );

    CREATE UNIQUE ASC INDEX idxUnidade ON Unidade (Edif, Apto);

    Thank you

    John

    ReplyDelete