CODEWITHSUNDEEP
postgresqljpajava-ee-6
Ender
Ender

Reputation: 1778

get primary key of last inserted record with JPA

I've been using JPA to insert entities into a database but I've run up against a problem where I need to do an insert and get the primary key of the record last inserted.

Using PostgreSQL I would use an INSERT RETURNING statement which would return the record id, but with an entity manager doing all this, the only way I know is to use SELECT CURRVAL.

So the problem becomes, I have several data sources sending data into a message driven bean (usually 10-100 messages at once from each source) via OpenMQ and inside this MDB I persists this to PostgreSQL via the entity manager. It's at this point I think there will be a "race condition like" effect of having so many inserts that I won't necessarily get the last record id using SELECT CURRVAL.

My MDB persists 3 entity beans via an entity manager like below.

Any help on how to better do this much appreciated.

public void onMessage(Message msg) {
    Integer agPK = 0;
    Integer scanPK = 0;
    Integer lookPK = 0;

    Iterator iter = null;
    List<Ag> agKeys = null;
    List<Scan> scanKeys = null;

    try {
        iag = (IAgBean) (new InitialContext()).lookup(
                "java:comp/env/ejb/AgBean");

        TextMessage tmsg = (TextMessage) msg;


        // insert this into table only if doesn't exists
        Ag ag = new Ag(msg.getStringProperty("name"));

        agKeys = (List) (iag.getPKs(ag));

        iter = agKeys.iterator();

        if (iter.hasNext()) {
            agPK = ((Ag) iter.next()).getId();
        }
        else {
            // no PK found so not in dbase, insert new 
            iag.addAg(ag);
            agKeys = (List) (iag.getPKs(ag));
            iter = agKeys.iterator();

            if (iter.hasNext()) {
                agPK = ((Ag) iter.next()).getId();
            }
        }


        // insert this into table always
        iscan = (IScanBean) (new InitialContext()).lookup(
                "java:comp/env/ejb/ScanBean");

        Scan scan = new Scan();
        scan.setName(msg.getStringProperty("name"));
        scan.setCode(msg.getIntProperty("code"));

        iscan.addScan(scan);

        scanKeys = (List) iscan.getPKs(scan);

        iter = scanKeys.iterator();

        if (iter.hasNext()) {
            scanPK = ((Scan) iter.next()).getId();
        }


        // insert into this table the two primary keys above

        ilook = (ILookBean) (new InitialContext()).lookup(
                "java:comp/env/ejb/LookBean");

        Look look = new Look();

        if (agPK.intValue() != 0 && scanPK.intValue() != 0) {
            look.setAgId(agPK);
            look.setScanId(scanPK);

            ilook.addLook(look);
        }
// ...

Upvotes: 0

Views: 3348

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324471

The JPA spec requires that after persist, the entity be populated with a valid ID if an ID generation strategy is being used. You don't have to do anything.

Upvotes: 2

Related Questions