ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Chris O'Connell" <oconn...@gorillachicago.com>
Subject How to get the generated key from insert with Oracle
Date Thu, 08 May 2008 22:04:04 GMT
So, I need to insert a User record.  I cannot change the database tables, so
I am stuck with the current behavior of my database.  On insert, there is a
trigger that selects a value from a SEQUENCE and updates the key with that
value.  I would like to return that value from the insert statement to my
java code.  I'm looking at the iBatis in Action book, and trying to figure
out how to do that.  I will include the parameter map I am using and the
<insert> statement below. To explain what you see below, the EMAIL field in
the database is unique, so if I create a new user with the provided email
address, I feel safe selecting that userID back out of the database based on
the email address.

However, this just feels clunky to me.  Is this the right way to do it?  Is
there a better way to get the new ID from the row I just created (given my
current constraint of not being able to change the database)?

Thanks,
Chris

<parameterMap id="UserParameterMap" class="com.gorilla.domain.users.User">
        <parameter property="pass" jdbcType="VARCHAR" />
        <parameter property="firstName" jdbcType="VARCHAR" />
        <parameter property="lastName" jdbcType="VARCHAR" />
        <parameter property="title" jdbcType="VARCHAR" />
        <parameter property="company" jdbcType="VARCHAR" />
        <parameter property="email" jdbcType="VARCHAR" />
        <parameter property="description" jdbcType="VARCHAR" />
        <parameter property="uuid" jdbcType="VARCHAR" />
        <parameter property="screenName" jdbcType="VARCHAR" />
        <parameter property="tradePortalId" jdbcType="VARCHAR" />
    </parameterMap>

<insert id="insertNewUser" parameterMap="UserParameterMap">
        INSERT INTO USER_USERS
            (PASS,
            FIRST_NAME,
            LAST_NAME,
            TITLE,
            COMPANY,
            EMAIL,
            DESCRIPTION,
            LOGIN_DATE,
            USER_UUID,
            MODIFY_DATE,
            SCREEN_NAME,
            TRADE_PORTAL_ID)
        VALUES
            (?,?,?,?,?,?,?,sysdate,?,sysdate,?,?)
        <selectKey resultClass="java.math.BigDecimal">
            select USER_ID from USER_USERS where EMAIL = #email#
        </selectKey>
    </insert> 


Mime
View raw message