MyBatis Mapper XML File

MyBatis Mapper XML File

Original link: http://www.mybatis.org/mybatis-3/zh/sqlmap-xml.html
One, mapper XML file

The true power of MyBatis lies in its mapping statement and its magic. Because of its exceptionally strong, the mapper's XML file is relatively simple. If you compare it to a JDBC code with the same functionality, you'll immediately find that you've omitted nearly 95% of the code. MyBatis is built for SQL and is better than normal methods.

The SQL mapping file has a few top-level elements (in the order in which they should be defined): cache– the cache configuration for a given namespace. cache-ref– references to other namespace cache configurations. Resultmap– is the most complex and powerful element to describe how to load objects from a database result set. parametermap– is obsolete. Vintage-style parametric mapping. Inline parameters are preferred and this element may be removed in the future and will not be recorded here. sql– A reusable statement block that can be referenced by other statements. insert– Map INSERT Statement update– Map UPDATE statement delete– Map DELETE statement select– map query statement

The next section describes the details of each element from the beginning of the statement itself. 1.1. Select

The query statement is one of the most commonly used elements in MyBatis, and the value of light energy in the database is not very good, if it can be re-extracted to be useful, most applications are also more frequent queries than changes. For each insert, update, or delete operation, more than one query operation is usually appropriate. This is one of the basic principles of MyBatis and the reason for putting focus and effort into query and result mapping. The select element of a simple query is very simple. Like what:

<select id= "Selectperson" parametertype= "int" resulttype= "HashMap" >
  select * from person WHERE id = #{id}
</select>

This statement is called Selectperson, takes an int (or Integer) type parameter, and returns an object of type HashMap, where the key is the column name and the value is the corresponding value in the result row
The Select element has many properties that allow you to configure the details of each statement's function.

<select
  id= "Selectperson"
  parametertype= "int"
  parametermap= "deprecated"
  resulttype= "HashMap "
  resultmap=" Personresultmap "
  flushcache=" false "
  usecache=" true "
  timeout=" 10000 "
  Fetchsize= "statementtype="
  PREPARED "
  resultsettype=" Forward_only ">

1.2, insert,update and delete

Data change statements Insert,update and delete are implemented very closely:

<insert
  id= "Insertauthor"
  parametertype= "Domain.blog.Author"
  flushcache= "true"
  Statementtype= "PREPARED"
  keyproperty= "
  keycolumn=" "
  usegeneratedkeys=" "
  timeout=" >

<update
  id= "Updateauthor"
  parametertype= "Domain.blog.Author"
  flushcache= "true"
  Statementtype= "PREPARED"
  timeout= ">

<delete
  id=" Deleteauthor "
  parametertype=" Domain.blog.Author "
  flushcache=" true "
  statementtype=" PREPARED "
  timeout=" >

Here is an example of the 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 earlier, the configuration rules of the INSERT statement are richer, and in the INSERT statement there are additional attributes and child elements to handle the generation of the primary key, and there are several ways to generate it.

First, if your database supports fields that automatically generate primary keys (such as MySQL and SQL Server), then you can set usegeneratedkeys= "true" and then set Keyproperty to the target property. For example, if the Author table above already uses an automatically generated column type for the ID, the statement can be modified to:

<insert id= "Insertauthor" usegeneratedkeys= "true"
    keyproperty= "id" >
  INSERT INTO Author (username, Password,email,bio)
  values (#{username},#{password},#{email},#{bio})
</insert>

If your database also supports multirow insertion, you can also pass in an authors array or collection and return the auto-generated primary key

<insert id= "Insertauthor" usegeneratedkeys= "true"
    keyproperty= "id" >
  INSERT INTO Author (username, Password, email, bio) values
  <foreach item= "item" collection= "List" separator= "," >
    (#{item.username}, # {Item.password}, #{item.email}, #{item.bio})
  </foreach>
</insert>

1.3. SQL

This element can be used to define reusable SQL code snippets that can be included in other statements. It can be parameterized statically (in load parameters). Different attribute values are changed by the included instance. Like what:

<sql id= "Usercolumns" > ${alias}.id,${alias}.username,${alias}.password </sql

This SQL fragment can be included in other statements, for example:

<select id= "selectusers" resulttype= "map" >
  select
    <include refid= "Usercolumns" ><property Name= "Alias" value= "T1"/></INCLUDE> <include
    refid= "usercolumns" ><property name= "Alias" Value= "T2"/></include> from
  some_table T1 cross
    join some_table T2
</select>

A property value can be used for a property value contained in a refID property or a contained sentence, for example:

<sql id= "sometable" >
  ${prefix}table
</sql>

<sql id= "Someinclude" >
    from <include refid= "${include_target}"/>
</sql>

<select id= "select" resulttype= "Map" >
  Select
    field1, Field2, field3
  <include refid= "Someinclude" >
    <property name= "prefix" Value= "Some"/>
    <property name= "Include_target" value= "sometable"/>
  </include>
< /select>

1.4, Resultmap

The Resultmap element is the most important and powerful element in the MyBatis. It is the thing that keeps you away from 90% of the JDBC code that needs to take the data out of the result set, and in some cases allows you to do something that JDBC does not support. In fact, writing the equivalent code similar to the complex statement map may span thousands of lines of code. The design of RESULTMAP is that simple statements do not require explicit result mappings, and many complex statements do need to describe their relationships.

One way to resolve column name mismatches:

<resultmap id= "Userresultmap" type= "User" >
  <id property= "id" column= "user_id"/>
  <result property= "username" column= "user_name"/>
  <result property= "password" column= "Hashed_password"/>
</resultMap>

The statement referencing it uses the Resultmap property (note that we have removed the Resulttype property). Like what:

<select id= "Selectusers" resultmap= "Userresultmap" >
  select user_id, user_name, Hashed_password
  from Some_table
  where id = #{id}
</select>

Advanced Result Mapping Resultmap constructor-class is used to inject results into the constructor Idarg-id parameters when instantiating, and marking results as IDs can help improve overall performance arg-a common result injected into the construction method ID – an ID result ; Marking results as IDs can help improve overall performance result– injection into a field or JavaBean property of a common result association– a complex type association; many results will be wrapped into this type of embedded result mapping – the association of the result mapping itself, or a reference to a Colle ction– Set embedding result mappings for complex types – the set of result mappings themselves, or reference to a discriminator– using result values to determine which result map to use case– embedded result mappings based on certain values-the result also maps itself, so it can contain many phases The same element, or it can refer to an external result map. Associate

<association property= "Author" column= "blog_author_id" javatype= "author" >
  <id property= "id" column= " author_id "/>
  <result property=" username "column=" author_username "/>
</association>

The correlation element handles the "have one" type of relationship. For example, in our example, a blog has a user. Correlation mapping works on this result. You specify the target property to get the column of the value, the Java type of the property (in many cases MyBatis can calculate it yourself), and if necessary, the JDBC type, if you want to overwrite or get the result value also need type controller.

The difference in association is that you need to tell MyBatis how to load the association. MyBatis There are two different ways to do this: nested queries: Return the expected complex type by executing another SQL mapping statement. Nested results: Use nested result mappings to handle a subset of duplicate union results. First, let's look at the attributes of this element. All you will see, it and ordinary only by select and

The result mapping for the Resultmap property is different.

<resultmap id= "Blogresult" type= "Blog" >
  <association property= "author" column= "author_id" javatype= " Author "select=" Selectauthor "/>
</resultMap>

<select id=" Selectblog "resultmap=" Blogresult " >
  SELECT * from BLOG WHERE ID = #{id}
</select>

<select id= "Selectauthor" resulttype= "Author" >
  SELECT * from AUTHOR WHERE ID = #{id}
</select

We have two query statements: one to load the blog, the other to load the author, and the result map of the blog describes what the "Selectauthor" statement should be used to load its Author property.

All other properties will be loaded automatically, assuming their columns and property names match. Collection (one-to-many mappings)

<collection property= "Posts" oftype= "Domain.blog.Post" >
  <id property= "id" column= "post_id"/>
  <result property= "Subject" column= "Post_subject"/>
  <result property= "Body" column= "Post_body"/>
</collection>

The function of the collection element is almost identical to the association. In fact, they are very similar, and the similarities and differences of documents are superfluous. So we focus more on their differences.

Let's go on to the example above, a blog with only one author. But blogs have lots of articles. In the blog class, this can be represented by the following notation:

Private list<post> posts;

To map the nested result collection to the list, we use the collection element. Just like associative elements, we can use nested queries from joins, or nest results.
nested queries for collectionsFirst, let's look at using nested queries to load articles for blogs

<resultmap id= "Blogresult" type= "Blog" >
  <collection property= "Posts" javatype= "ArrayList" column= "id" Oftype= "Post" select= "Selectpostsforblog"/>
</resultMap>

<select id= "Selectblog" resultmap= " Blogresult ">
  select * from BLOG WHERE ID = #{id}
</select>

<select id=" Selectpostsforblog " Resulttype= "POST" >
  select * from Post WHERE blog_id = #{id}
</select>

Here you should pay attention to a lot of things, but most of the code is very similar to the above related elements. First, you should be aware that we are using a collection element. Then pay attention to the new "OfType" attribute. This property is important for distinguishing between javabean (or field) attribute types and the types that the collection contains. So you can read the following map:

<collection property= "Posts" javatype= "ArrayList" column= "id" oftype= "Post" select= "Selectpostsforblog"/>

Read as: "The collection of posts in the ArrayList of the Post type. ”

The Javatype property is not required because MyBatis will be counted for you in many cases. So you can shorten the wording:

<collection property= "Posts" column= "id" oftype= "Post" select= "Selectpostsforblog"/>

nested results of the collection

<select id= "Selectblog" resultmap= "Blogresult" >
  select
  b.id as blog_id,
  B.title as Blog_title,
  b.author_id as blog_author_id,
  P.id as post_id,
  P.subject as Post_subject,
  P.body as Post_body, from
  Blog B left
  outer join post P on B.I D = p.blog_id
  where b.id = #{id}
</select>

When mapping a blog with an article mapping collection, you can simply write:

<resultmap id= "Blogresult" type= "Blog" >
  <id property= "id" column= "blog_id"/>
  <result property= "title" column= "Blog_title"/>
  <collection property= "Posts" oftype= "Post" >
    <id property= "id" column= "post_id"/>
    <result property= "Subject" column= "Post_subject"/>
    <result property= "Body" column= "post_body"/>
  </collection>
</resultMap>

Again, remember the importance of the ID element, and if you don't remember it, read the related section above.

Similarly, if you refer to a longer form that allows for more reuse of your result mappings, you can use this alternative mapping:

<resultmap id= "Blogresult" type= "Blog" >
  <id property= "id" column= "blog_id"/>
  <result property= "title" column= "Blog_title"/>
  <collection property= "Posts" oftype= "Post" resultmap= " Blogpostresult "columnprefix=" Post_ "/>
</resultMap>

<resultmap id=" Blogpostresult "type=" post ">
  <id property=" id "column=" id "/>
  <result property=" subject "column=" Subject "/>
  < Result property= "Body" column= "body"/>
</resultMap>