Mybatis advanced mapping dynamic SQL and getting self enhancing primary key resolution

MyBatis was originally an open source project of apache, iBatis. In 2010, the project moved from apache software foundation to google code and was renamed MyBatis. The following is an introduction to Mybatis advanced mapping, dynamic SQL, and getting a self-incrementing primary key. Please refer to this article for details.

1. The dynamic SQL

It is believed that everyone will encounter a problem when using mybatis to operate the database. If we now have an list authorList about the author, we need to query the corresponding author's blog information in the database according to the author information already in authorList. The easiest way to think about it is to go through authorList and get the information to query the database.

for(int i=0;I < authorList.size();i++) {
 ... 
// Query database code 
//select * from blog where author=#{author,jdbcType=VARCHAR}
}

Want to 1, if the length of the authorList N, so we need to query N database, if using this method, the application of overhead is not only the query, and from the database connection pool connection instance, establish a database connection, will return the database instance to the database connection pool, assuming that the three action added up to a total of 0.001 seconds. If the query needs to be queried 1000 times, then it will take an extra second, which is intolerable for the program ape, because it is only a circular query, not counting other business code.

So, is there a better way? The answer is yes, and one of them is dynamic SQL:

Code first:

<select id="dynamicForeachTest" resultType="com.blog.Blog" parameterType="java.util.List">
select * from blog where author in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>

tem represents the alias for each element in the collection as it iterates,

index specifies a name to indicate the location of each iteration during the iteration,

open means what the statement starts with,

separator represents what symbol is used as the separator between each iteration,

close means what ends so that the return value can be List < Bolg > Accept it.

However, the foreach statement in dynamic SQL is most commonly used in insert statements, and is usually used in in clauses.

2. Advanced mapping

When using mybatis, 1 normally accepts the query results using resultType = com.blog.author entity class

Or you can use resultType = java.util.map to return the database column name as key and the record value as value.

This time, however, you need to use resultMap, which allows you to freely combine return values for more complex queries.

Or first on the code:

SQL:

<select id="getBlogs" resultMap=" blogs " parameterType="map">
Select a.authorID,
a.uthorName,
b.blogID,
b.blogName
from author a left join blog b on a. authorID=b. authorID where a. authorID = #{authorID,jdbcType=INTEGER}
</select>

mybatis configuration:

<resultMap id="blogs" type="com.bloh.Blog">
<id property="authorID" column=" authorID">
<result property="authorName" column=" authorName">
<collection property="postsList" ofType="com.bolg.Post">
<id property="blogID" column=" blogID"/>
<result property="blogName" column="blogName"/>
</collection>
</resultMap>

Blog entity class

Public class Bolg {
private Integer authorID;
private String authorName;
private List<Post> postsList;
//setter getter
}

Post entity class

Public class Post {
private Integer blogID;
private String blogName;
//setter getter
}

This allows one entity to accept one complex query.

Here's what each property does:

The other properties and configurations of normal mybatis queries are not discussed in detail,

resultMap is used instead of resultType to represent the format in which query results are returned

id in resultMap has two main functions:

Similar to indexes to improve query performance

Distinguish between different outcomes

So it is best not to omit id, and if there is no primary key, replace it with a field that can distinguish the record only by 1

result is the variable name defined in the entity class, and column is the column name of the database

collection is a collection of lists, map, and so on

postsList is the list variable name defined in the Blog entity class

ofType is the entity class of the object in the object list.

3. Gain ID:

If you have the following situation, after inserting the database record, you want the primary key of the inserted record to be used in the later business code

Then mybatis also provides support for this situation (batch insertion is not supported):

MySQL is an acoustic autoincrement ID; Assume that the field name of the self-incrementing primary key is ID

<insert id="insert" useGeneratedKeys="true" keyProperty="id" parameterType="User">
insert into <include refid="TABLE_NAME" /> ( NAME, AGE )
values ( #{name}, #{age} )
</insert>

There are two more properties than normal inserts: useGeneratedKeys="true" to enable the return self-increment ID

keyProperty="id" means the name of the return primary key.

In the business code, it can be received with the following statement:

Let's say the entity class is User

User userNew = userMapper.insert(user);

userNew. getID // is the self-increment ID after insertion

In fact, mysql's self-augmented primary key can be used with select LAST_INSERT_ID(); To get,

So, there's another way to write it:

<insert id="insert" parameterType="User">
<selectKey resultType="int" order="AFTER" keyProperty="id">
SELECT LAST_INSERT_ID() AS id
</selectKey>
insert into name,age
values ( #{name}, #{age} )
</insert>

And mysql contrary to obtain primary key way, mysql insert is executed by the table allocated from the value of the growth and oracle is access to the growth of values to insert record again after the operation, the execution insert sql must specify a primary key value before give to insert record so want to at the time of "BEFORE" got on the sequence, then use selectKey way into into the parameter mapping. So let's say that it grows by itself again, id

<insert id=" insert " useGeneratedKeys="true" keyProperty="id" parameterType="xxxx" >
<selectKey resultType="int" order="BEFORE" keyProperty="id">
SELECT SEQ_TABLE.NEXTVAL FROM dual
</selectKey>
INSERT INTO id,name,age
VALUES
(#{id} #{name}, #{age} )
</insert>

id here is the self-increment id obtained by selectKey.

The receive mode is similar to mysql1. It is better to use entity receive when obtaining the self-incrementing primary key.