title: get primary key of last inserted record with JPA
body: <p>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.</p>
  
  <p>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. </p>
  
  <p>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 <em>I think</em> 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.</p>
  
  <p>My MDB persists 3 entity beans via an entity manager like below. </p>
  
  <p>Any help on how to better do this much appreciated.</p>
  
  <pre><code>public void onMessage(Message msg) {
      Integer agPK = 0;
      Integer scanPK = 0;
      Integer lookPK = 0;
  
      Iterator iter = null;
      List&lt;Ag&gt; agKeys = null;
      List&lt;Scan&gt; 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 &amp;&amp; scanPK.intValue() != 0) {
              look.setAgId(agPK);
              look.setScanId(scanPK);
  
              ilook.addLook(look);
          }
  // ...
  </code></pre>