Hibernate nextval get id

Question

I use Hibernate with annotations. I need to insert object into the database using a method who needs one parameter that I need to calculate, to do this I use:

SQLQuery sqlQuery = getSession().createSQLQuery(queryString);
sqlQuery.executeUpdate();

The queryString contains:

INSERT INTO TABLE(ID, NUMBER) VALUES (SEC_TABLE.NEXTVAL, 549)

The object is inserted into the database, but when I try to get the id, I get a null value.

What can I do?

When I use getHibernateTemplate().update(obj); I have not problems getting the id, but I need to customize the sql insert.

Thanks

Answer 1

Maybe I didn't get everything but did you consider using raw JDBC either via Session#connection() (might be removed in Hibernate 4) or the new Session#doWork() API? In case you need an example, here is how you'd use it:

session.doWork(new Work() {
   public void execute(Connection conn) {
      // do work with the connection
      ...
   }
});

Answer 2

Your question is a little bit unclear, but for what I understood I think you should first make a prepared statement that reads the next value for the sequence and use that id when creating the insert statement. Also, make sure that you commit the transaction and maybe flush the session.

As a side note, if you are using Hibernate with annotations, when mapping a table to a class you can provide the id generator as a sequence. In this way, you don't have to write jdbc code to insert the entity, just plain hibernate code, i.e. getJdbcTemplate().save().

Answer 3

Here is an example of sequences using annotations:

@Id
@Column(name = "OFFER_ID")
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "OFR_OFFERS_SEQ")
@SequenceGenerator(name = "OFR_OFFERS_SEQ", sequenceName = "OFR_OFFERS_SEQ", allocationSize = 1)
private Long offerId;

Also, the add method should look like this:

public <T> T add(final T obj) {
     return get(obj.getClass(), (Long) getHibernateTemplate().save(obj));
}
public final <T> T get(final Class<T> clazz, final Long id) {
     return (T) getHibernateTemplate().get(clazz, id);
}

The methods are from a class than extends HibernateDaoSupport from Spring, thats how they get the hibernate template.

Hope this works for you.

Answer 4

Now I have this:

public void sql(final String query) {
    getHibernateTemplate().execute(new HibernateCallback<Object>() {
        public Object doInHibernate(Session session) throws HibernateException, SQLException {
            Query q = session.createSQLQuery(query);
            q.executeUpdate();
            return null;
        }
    });
}

The query is:

INSERT INTO TABLE (ID, VALUE) VALUES (SEC.NEXTVAL, crypt(1, paswword))

The method I use:

public void insert(MyClass object) {
    String password = confFile.getProperty("CRYPT");
    String query = createQuery(object, password);
    sql(query);
}

The problem is that when I use the methods of HibernateDaoSupport when I do .persist(object) inmediatly I have the id in the object, but when I use this method (to crypt a field value) I try to get the id but it's null. The object is correctly inserted into the database.