Cannot retrieve the id of the last inserted row in Hibernate using Oracle

Question

I'm using Hibernate Tools 3.2.1.GA with the Spring version 3.0.2. I'm trying to retrieve the id of the last inserted row into the Oracle(10g) database as follows.

Session session=NewHibernateUtil.getSessionFactory().getCurrentSession();
session.beginTransaction();

Country c=new Country();

c.setCountryId(new BigDecimal(0));
c.setCountryName(request.getParameter("txtCountryName"));
c.setCountryCode(request.getParameter("txtCountryCode"));
Zone z=(Zone) session.get(Zone.class, new BigDecimal(request.getParameter("zoneId")));
c.setZone(z);
session.save(c);

session.flush();    
System.out.println(c.getCountryId());    
session.getTransaction().commit();       

This statement System.out.println(c.getCountryId()); is expected to return the currently inserted id after the data is serialized to the database and before the transaction is committed but it doesn't because of the following line in the preceding code snippet (as it presumably appears to me).

c.setCountryId(new BigDecimal(0));

I'm not sure why this statement is required in my case (while inserting). I saw this statement nowhere. Omission of this line causes the following exception to be thrown.

org.hibernate.id.IdentifierGenerationException: ids for this class must be manually assigned before calling save(): model.Country

Is this statement c.setCountryId(new BigDecimal(0)); really required during insertion? It's a sequence generated primary key in the Oracle database and because of that line, this statement System.out.println(c.getCountryId()); always returns 0 which is actually expected to return the currently inserted id in the current session.

So, how can I get the last generated id in this case? Am I following a wrong way, is there a different way?

---

EDIT:

CREATE TABLE  "COUNTRY" 
(   
    "COUNTRY_ID" NUMBER(35,0) NOT NULL ENABLE, 
    "COUNTRY_CODE" VARCHAR2(10), 
    "COUNTRY_NAME" VARCHAR2(50), 
    "ZONE_ID" NUMBER(35,0), 

    CONSTRAINT "COUNTRY_PK" PRIMARY KEY ("COUNTRY_ID") ENABLE, 
    CONSTRAINT "COUNTRY_FK" FOREIGN KEY ("ZONE_ID")
                            REFERENCES  "ZONE" ("ZONE_ID") ON DELETE CASCADE ENABLE
)
/

CREATE OR REPLACE TRIGGER  "BI_COUNTRY" 
before insert on "COUNTRY"               
for each row  

begin   
    select "COUNTRY_SEQ".nextval into :NEW.COUNTRY_ID from dual; 
end; 

/
ALTER TRIGGER  "BI_COUNTRY" ENABLE
/ 

Answer 1

The exception 'ids for this class must be manually assigned before calling save()' means that you are using the identifier generation strategy of 'Assigned'.

assigned lets the application assign an identifier to the object before save() is called. This is the default strategy if no element is specified.

If you do not define any strategy, hibernate defaults to 'assigned'. 'assigned' strategy implies that hibernate expects that the application supplies it's own ids.

If you want to use a sequence id generator in Oracle, you can do so with the following configuration -

If you are using xml -

   <id name="countryId" type="java.lang.Integer">  
        <column name="Country_Id" />  
        <generator class="sequence">  
            <param name="sequence">Country_Id_Seq</param>               
        </generator>  
    </id>

If you are using annotations -

   @Id
   @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="Country_Id_Seq")
   @SequenceGenerator(name="Country_Id_Seq", sequenceName="Country_Id_Seq"  )
   private Integer sequence;

And your code should look like so -

Country c=new Country();

c.setCountryName(request.getParameter("txtCountryName"));
c.setCountryCode(request.getParameter("txtCountryCode"));
Zone z=(Zone) session.get(Zone.class, new BigDecimal(request.getParameter("zoneId")));
c.setZone(z);
session.save(c);

session.flush();    
System.out.println(c.getCountryId()); 

When 'session.save(c)' executes, hibernate makes the following sql call to Oracle, retrieves the id and sets it in Country object.

select Country_Id_Seq.nextVal from dual;

Problem with trigger

Since you are using a trigger to increment the id when a row is inserted, this will cause a problem with hibernate sequence. Hibernate is using the sequence to generate an id and the database is using the trigger to increment the id. This is resulting in the id being incremented twice.

You have a three options to resolve this.

1. Delete the trigger because it's not necessary.

2. If you still need the trigger because the table could be updated outside the application, you could update the trigger such that the id is generated only if the id is not set in the insert statement HIbernate issue with Oracle Trigger for generating id from a sequence

3. Create a custom id generator that uses the trigger to set the id in the data before it is saved to db. Check out the following link - https://forum.hibernate.org/viewtopic.php?t=973262

Answer 2

If the values into an ID column generated by a sequence, then you should associate that sequence with your ID column in the entity definition so that the attribute is filled in with the ID value by Hibernate during insertion.

Using annotations:

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "CountryIdSequence")
@SequenceGenerator(name = "CountryIdSequence", sequenceName = "COUNTRY_ID_SEQUENCE")
@Column(name = "COUNTRY_ID")
private BigDecimal countryId;

Using hbm:

<id name="countryId" type="big_decimal">
    <column name="COUNTRY_ID" />
    <generator class=""sequence">
        <param name="sequence">COUNTRY_ID_SEQUENCE</param>
    </generator>
</id>

Then, it will be available after the save.

Any changes made to the entity at the database layer are not reflected in the hibernate entity layer until you refresh the object.

session.save(c);
session.flush();    
// Refresh the object for columns modified in the DB by IDENTITY / SEQUENCE / Triggers.
session.refresh(c);
System.out.println(c.getCountryId());