Getting autoincrement id on insert in SAP Hana

Question

I have a table with an autoincrement column, as described here

This is how it's defined in Java/Hibernate:

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private int ID;

And this is how the column is defined in the CREATE TABLE (note, no sequence):

"ID" INTEGER CS_INT GENERATED BY DEFAULT AS IDENTITY

Now, I want to insert a row in the table and retrieve the resulting id. The problem is that the driver doesn't support Statement.RETURN_GENERATED_KEYS to retrieve the id with a fetch statement.

On the other hand, since I don't have a sequence created I cannot use nextval. Any ideas how to get the autoincremented id?

Note: JDBC has similar problem, it's not Hibernate specific.

UPDATE

Sample code (in Java/Hibernate):

        val c = new MyJpaClass
        c.code = 1
        c.name = "abc"
        c.version = 0
        entityManger.getTransaction.begin
        entityManger.persist(c)
        entityManger.getTransaction.commit

        // c.id should be populated here with the assigned autoincremented id

Can you share some code to show how you save your POJO? I think that would help us understand the context better. Thanks — Abdel Dadouche, Dec 17 '18 at 11:48
Is this code supposed to be Java code? Using Local-Variable Type Inference? — Abdel Dadouche, Dec 20 '18 at 14:16
Yes, it's Java/Hibernate, but if you have a solution for Java/JDBC that will also work — ps0604, Dec 20 '18 at 15:29
I'm no expert in LVT, but have you tried switching from val to var for your MyJpaClass? — Abdel Dadouche, Dec 24 '18 at 09:55

aggredi · Accepted Answer · 2018-12-25T08:34:43.473

When you persist entity Hibernate syncronize it with database when transaction is commit. So, you can simply do c.getId();

UPDATE Here example of unit test. All files is here

import static org.junit.Assert.assertNotNull;

import java.io.FileNotFoundException;
import java.sql.SQLException;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

public class PersistTest {
    Logger LOG = LogManager.getLogger(PersistTest.class);

protected static EntityManagerFactory emf;
protected static EntityManager em;

@BeforeClass
public static void init() throws FileNotFoundException, SQLException {
    emf = Persistence.createEntityManagerFactory("HelloWorldPU");
    em = emf.createEntityManager();
}

@AfterClass
public static void tearDown(){
    em.clear();
    em.close();
    emf.close();
}

@Test
public void testPersist_success() {
    em.getTransaction().begin();
    MyJpaClass o = new MyJpaClass();
    o.setMessage("text");
    em.persist(o);
    em.getTransaction().commit();

    assertNotNull(o.getId());
    LOG.debug(o.getId());
}

}

can you share your persistence.xml? I was able to make it work on HANA — Abdel Dadouche, Jan 03 '19 at 10:23

Getting autoincrement id on insert in SAP Hana

1 Answers1