[MyBatis] SQL insert 시 주 키 리턴하기

 

■  MySQL

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. For example, if the Author table above had used an auto-generated column type for the id, the statement would be modified as follows:  ( id => auto_increment )

<insert id="insertAuthor" useGeneratedKeys="true" keyProperty="id">
  insert into Author ( username, password, email, bio )
  values ( #{username}, #{password}, #{email}, #{bio} )
</insert>

<insert id="insertAuthor">
  insert into Author ( username, password, email, bio )
  values ( #{username}, #{password}, #{email}, #{bio} )
</insert>

 

■  Oracle DB

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.

Here's a simple (silly) example that would generate a random ID (something you'd likely never do, but this demonstrates the flexibility and how MyBatis really doesn't mind):

<insert id="insertAuthor">
  <selectKey keyProperty="id" order="BEFORE" resultType="int">
    select seq.nextval from dual
  </selectKey>
  insert into Author ( id, username, password, email, bio )
  values ( #{id}, #{username}, #{password}, #{email}, #{bio} )
</insert>

In the example above, the selectKey statement would be run first, the Author id property would be set, and then the insert statement would be called. This gives you a similar behavior to an auto-generated key in your database without complicating your Java code.

<insert id="insertAuthor">
  insert into Author ( id, username, password, email, bio )
  values ( seq.nextval, #{username}, #{password}, #{email}, #{bio} )
</insert>

 

참고 : mybatis.org/mybatis-3/sqlmap-xml.html#insert_update_and_delete

( SQL 문 원문에서 살짝 수정 )