A problem with Hibernate and MySQL query.

[Vishal Srivastav]

Hello friends,

I am creating a small database driven web application using Hibernate and MySQL.

I have a table created with the first column as PRIMARY KEY and AUTO_INCREMENT constraint.

All I want is to get the last updated / inserted records id from the table using SELECT LAST_INSERT_ID().

I was able to do this by simple JDBC coding, but I am not able to think how to do it using Hibernate as I am new to it.

Is there any other way to do it???

Any small hint or suggestion is awaited...

Thanks All.

[Paul Sturrock]

If you need this value after an insert you don't have to do anything, Hibenate will get the value for you when you save the object.

If you need it fomr any other reason, your can use the HQL max function, or you can use a native SQL query.

[Vishal Srivastav]

Ok, I used a service class to insert value into the table... and at the same time tried to retrieve the last id with another class' method.

here are the codes -

public class AddEmployeeService {

    private Integer lastId;
    public Integer storeEmp(Employee e) {
        Session session = null;
        try  {
            //creating configuration and loading the configuraton
            Configuration cfg = new Configuration().configure();

            //creating a session factory for this congfiguration
            SessionFactory sessionFac = cfg.buildSessionFactory();

            //getting a session for the particular user
            session = sessionFac.openSession();

            //Beginning a transaction
            Transaction tx = session.beginTransaction();

            //starting a transaction
            tx.begin();
            session.saveOrUpdate(e);
            lastId = new FindLastIdService().getLastId();
            tx.commit();

        } catch(HibernateException he) {
            System.err.println("Error while inserting Employee details: " + he);
        } finally {
            try {
                session.flush();
                session.close();
            } catch(HibernateException hbe) {
                System.err.println("Error writing session: " + hbe);
            }
        }
        return lastId;
    }

public class FindLastIdService {

    private Integer lastId;

    public Integer getLastId() {
        Session session = null;
        try {
            //creating configuration and loading the configuraton
            Configuration cfg = new Configuration().configure();

            //creating a session factory for this congfiguration
            SessionFactory sessionFac = cfg.buildSessionFactory();

            //getting a session for the particular user
            session = sessionFac.openSession();

            //Beginning a transaction
            Transaction tx = session.beginTransaction();

            //starting a transaction
            tx.begin();
            lastId = (Integer) session.createQuery("Select LAST_INSERT_ID()").uniqueResult();
            tx.commit();

        } catch (HibernateException he) {
            System.err.println("Hibernate error: Error while getting lastId" + he);
        } finally {
            try {
                session.flush();
                session.close();
            } catch (HibernateException hbe) {
                System.err.println("Error writing session: " + hbe);
            }
        }

        return lastId;
    }
}

And all I am getting is this exception....

java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.MethodNode 
 \-[METHOD_CALL] MethodNode: '('
    +-[METHOD_NAME] IdentNode: 'LAST_INSERT_ID' {originalText=LAST_INSERT_ID}
    \-[EXPR_LIST] SqlNode: 'exprList'

Where is the problem??

[Paul Sturrock]

The last_insert_id() function is not value HQL, so trying to use it as an HQL query will not work. If you really want to use this you will need to use a native sql query.

But this is an unecessary step, as is your class FindLastIdService. Like I said, Hibernate will return the value of a generated ID as part of its save method, if you use it instead of the saveOrUpdate method.

[Vishal Srivastav]

Paul Sturrock wrote:The last_insert_id() function is not value HQL, so trying to use it as an HQL query will not work. If you really want to use this you will need to use a native sql query.

But this is an unecessary step, as is your class FindLastIdService. Like I said, Hibernate will return the value of a generated ID as part of its save method, if you use it instead of the saveOrUpdate method.

Thank you sir... Actually I am new to Hibernate and depend a lot on APIs. But now the link Hibernate Api is no more working. It was working 3-4 days earlier.

Can you please tell that that is the API downloadable?? Where can I find it???

[Jeanne Boyarsky]

As that is the #1 hit on Google, I'm thinking they have a system issue. You could use a mirror of the API documentation for now.