How to Get Table’s Generated Ids in Mybatis?

Question

I want to get Table’s Generated Ids to be returned back to user. Here is my code:

1)POJO

public class AuditRecord<K, U> {

  private K id;

  private String context;

  private U contextIdentifier;

  @JsonSerialize(using = JsonJodaDateTimeSerializer.class)
  @JsonDeserialize(using = JsonJodaDateTimeDeSerializer.class)
  private DateTime time;

  private String code;

  private String description;

  private PartyRole<?> creator;

  private Collection<Session<?>> sessions;

  /**
   * Default constructor,reflectively picked by frameworks like jaxb to create and initialise the state of object.
   */
  private AuditRecord() {

  }

  private AuditRecord(final AuditRecordBuilder<K, U> builder) {
    this();
    this.id = builder.id;
    this.context = builder.context;
    this.contextIdentifier = builder.contextIdentifier;
    this.time = builder.time;
    this.description = builder.description;
    this.creator = builder.creator;
    this.sessions = builder.sessions;
    this.code = builder.code;
  }

  public K getId() {
    return id;
  }

  public String getCode() {
    return code;
  }

  public String getDescription() {
    return description;
  }

  public String getContext() {
    return context;
  }

  public U getContextIdentifier() {
    return contextIdentifier;
  }

  public DateTime getTime() {
    return time;
  }

  public Collection<Session<?>> getSessions() {
    return sessions;
  }

  public PartyRole<?> getCreator() {
    return creator;
  }

  /**
   * Builder Class of AuditRecord which populates mandatory fields and allows some of its fields to be populated
   * optionally,
   * making AuditRecord immutable at the same time.
   * @param <K> Identifier for the record.
   * @param <U> Identifier for the context Identifier.
   */
  public static class AuditRecordBuilder<K, U> {

    private K id;

    private String context;

    private U contextIdentifier;

    private DateTime time;

    private String code;

    private String description;

    private PartyRole<?> creator;

    private Collection<Session<?>> sessions;

    /**
     * Builder constructor only receives the required attributes.
     * @param id
     * @param time
     * @param code
     * @param description
     */
    public AuditRecordBuilder(final K id, final DateTime time, final String code, final String description) {
      this.code = code;
      this.id = id;
      this.time = time;
      this.description = description;
    }

    /**
     * Initialise optional attributes context.
     * @param contextForAuditRecord
     * @return
     */
    public AuditRecordBuilder<K, U> setContext(final String contextForAuditRecord) {
      this.context = contextForAuditRecord;
      return this;
    }

    /**
     * Initialise optional attributes sessions.
     * @param sessionsForAuditRecord
     * @return
     */
    public AuditRecordBuilder<K, U> setSessions(final Collection<Session<?>> sessionsForAuditRecord) {
      this.sessions = sessionsForAuditRecord;
      return this;
    }

    /**
     * Initialise optional attributes creator.
     * @param creatorForAuditRecord
     * @return
     */
    public AuditRecordBuilder<K, U> setCreator(final PartyRole<?> creatorForAuditRecord) {
      this.creator = creatorForAuditRecord;
      return this;
    }

    /**
     * Initialise optional attributes context identifier.
     * @param contextIdentifierForAuditRecord
     * @return
     */
    public AuditRecordBuilder<K, U> setCreator(final U contextIdentifierForAuditRecord) {
      this.contextIdentifier = contextIdentifierForAuditRecord;
      return this;
    }

    /**
     * Method finally returns the built instance.
     * @return
     */
    public AuditRecord<K, U> build() {
      return new AuditRecord<K, U>(this);
    }
  }

  @Override
  public int hashCode() {
    return HashCodeBuilder.reflectionHashCode(this);
  }

  @Override
  public boolean equals(final Object obj) {
    return EqualsBuilder.reflectionEquals(this, obj);
  }

  @Override
  public final String toString() {
    return ToString.generateToString(this);
  }

}


2)Mapper as below

@Component
public interface AuthenticationAuditMapper {

  /**
   * Insert the audit record in History table and returned the generated sequence ID.
   * @param auditRecord
   * @return
   */
      void
      createAuditRecord(@Param("auditRecord") AuditRecord<Long, String> auditRecord, @Param("seqNumber") Long seqNumber);
}


3)Mapping xml as 

<mapper namespace="com.avios.domain.repository.audit.support.mybatis.AuthenticationAuditMapper">



  <insert id="createAuditRecord" parameterType="com.avios.domain.model.audit.AuditRecord" useGeneratedKeys="true"
    keyProperty="auditRecord.id" keyColumn="AUTH_HISTORY_ID" >
    INSERT INTO TAUTHHIST
    (USER_SEQ_NO, HISTORY_DESCRIPTION, APP_SESSION_ID, CMS_SESSION_ID,
    CREATED_BY,
    FK_TAUTHHISTYPAUTH_HISTORY_ENT)
    VALUES (
    #{auditRecord.contextIdentifier},
    #{auditRecord.description},
    #{auditRecord.sessions[0].id},
    null, #{auditRecord.creator.id}, #{auditRecord.code}
    )
  </insert>

</mapper>

The problem is that I'm not able to get back my user generated key. However I'm able to get that if I use <selectKey> in my insert statement as follows:

<selectKey keyProperty="auditRecord.id" resultType="java.lang.Long" order="AFTER"> select SEQ_TAUTHHIST.currval from dual </selectKey>

Yes i am using oracle DB.Is builder pattern applied in my POJO creating any problem???? — dhiraj singh, Jan 06 '15 at 16:55

score 0 · Answer 1 · edited May 23 '17 at 12:20

I assume that you are working on an Oracle DB. In Oracle, the classical JDBC way of retrieving auto-generated keys with Statement#getGeneratedKeys() does not work. MyBatis uses this JDBC function as well. See this and that answer of BalusC for a detailed explanation. Actually Oracle's Auto-Generated Id is mostly only a combination of a SEQUENCE and a BEFORE TRIGGER usage.

From MyBatis doc:

First, if your database supports auto-generated key fields (e.g. MySQL and SQL Server), then you can simply set useGeneratedKeys="true" and set the keyProperty to the target property and you're done. [code here] MyBatis has another way to deal with key generation for databases that don't support auto-generated column types, or perhaps don't yet support the JDBC driver support for auto-generated keys.

See also Retrieval of Auto-Generated Keys from Oracle Docs.

score 0 · Answer 2 · answered Jan 08 '15 at 04:23

You can get back auto generated value from insert by changing as below:

User select tag instead of insert tag and return your generated id end of query.
Complete code will be look like as below :

<select id="createAuditRecord" parameterType="com.avios.domain.model.audit.AuditRecord" useGeneratedKeys="true"
    keyProperty="auditRecord.id" keyColumn="AUTH_HISTORY_ID" >
    INSERT INTO TAUTHHIST
    (USER_SEQ_NO, HISTORY_DESCRIPTION, APP_SESSION_ID, CMS_SESSION_ID,
    CREATED_BY,
    FK_TAUTHHISTYPAUTH_HISTORY_ENT)
    VALUES (
    #{auditRecord.contextIdentifier},
    #{auditRecord.description},
    #{auditRecord.sessions[0].id},
    null, #{auditRecord.creator.id}, #{auditRecord.code}
    )RETURNING "auth_history_id" <!-- this is your generated table id column name -->
  </select>

How to Get Table’s Generated Ids in Mybatis?

2 Answers2