select

The select statement is one of the most popular elements that you'll use in MyBatis. Putting data in a database isn't terribly valuable until you get it back out,so most applications query far more than they modify the data. For every insert,update or delete,there is probably many selects. This is one of the founding principles of MyBatis,and is the reason so much focus and effort was placed on querying and result mapping. The select element is quite simple for simple cases. For example:

<select id="selectPerson" parameterType="int" resultType="hashmap">
  SELECT * FROM PERSON WHERE ID = #{id}
</select>

This statement is called selectPerson,takes a parameter of type int (or Integer),and returns a HashMap keyed by column names mapped to row values.

Notice the parameter notation:

#{id}

This tells MyBatis to create a PreparedStatement parameter. With JDBC,such a parameter would be identified by a "?" in sql passed to a new PreparedStatement,something like this:

// Similar JDBC code,NOT MyBatis…
String selectPerson = "SELECT * FROM PERSON WHERE ID=?";
PreparedStatement ps = conn.prepareStatement(selectPerson);
ps.setInt(1,id);

Of course,there's a lot more code required by JDBC alone to extract the results and map them to an instance of an object,which is what MyBatis saves you from having to do. There's a lot more to know about parameter and result mapping. Those details warrant their own section,which follows later in this section.

The select element has more attributes that allow you to configure the details of how each statement should behave.

<select
  id="selectPerson"
  parameterType="int"
  parameterMap="deprecated"
  resultType="hashmap"
  resultMap="personResultMap"
  flushCache="false"
  useCache="true"
  timeout="10000"
  fetchSize="256"
  statementType="PREPARED"
  resultSetType="FORWARD_ONLY">

insert,update and delete

The data modification statements insert,update and delete are very similar in their implementation:

<insert
  id="insertAuthor"
  parameterType="domain.blog.Author"
  flushCache="true"
  statementType="PREPARED"
  keyProperty=""
  keyColumn=""
  useGeneratedKeys=""
  timeout="20">

<update
  id="insertAuthor"
  parameterType="domain.blog.Author"
  flushCache="true"
  statementType="PREPARED"
  timeout="20">

<delete
  id="insertAuthor"
  parameterType="domain.blog.Author"
  flushCache="true"
  statementType="PREPARED"
  timeout="20">

The following are some examples of insert,update and delete statements.

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

<update id="updateAuthor">
  update Author set
    username = #{username},password = #{password},email = #{email},bio = #{bio}
  where id = #{id}
</update>

<delete id="deleteAuthor">
  delete from Author where id = #{id}
</delete>

As mentioned,insert is a little bit more rich in that it has a few extra attributes and sub-elements that allow it to deal with key generation in a number of ways.

First,if your database supports auto-generated key fields (e.g. MysqL and sql Server),then you can simply setuseGeneratedKeys="true"and set thekeyPropertyto the target property and you're done. For example,if theAuthortable above had used an auto-generated column type for the id,the statement would be modified as follows:

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

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" resultType="int" order="BEFORE">
    select CAST(RANDOM()*1000000 as INTEGER) a from SYSIBM.SYSDUMMY1
  </selectKey>
  insert into Author
    (id,bio,favourite_section)
  values
    (#{id},#{bio},#{favouriteSection,jdbcType=VARCHAR})
</insert>

In the example above,the selectKey statement would be run first,theAuthorid 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.

The selectKey element is described as follows:

<selectKey
  keyProperty="id"
  resultType="int"
  order="BEFORE"
  statementType="PREPARED">

sql

This element can be used to define a reusable fragment of sql code that can be included in other statements. For example:

<sql id="userColumns"> id,password </sql>

The sql fragment can then be included in another statement,for example:

<select id="selectUsers" resultType="map">
  select <include refid="userColumns"/>
  from some_table
  where id = #{id}
</select>

Parameters

In all of the past statements,you've seen examples of simple parameters. Parameters are very powerful elements in MyBatis. For simple situations,probably 90% of the cases,there's not much too them,245)"><select id="selectUsers" resultType="User"> select id,password from users where id = #{id} </select>