MyBatis Advanced Mapping, dynamic SQL and analytic _java of obtaining self-added primary key

MyBatis is an open source project for Apache Ibatis, the project was migrated from Apache Software Foundation to Google code in 2010 and renamed MyBatis. below to introduce mybatis advanced mapping, dynamic SQL and get the content of the self-added primary key, please refer to this article for details.

One, dynamic SQL

I believe everyone in the use of MyBatis operation database will encounter a problem, if now we have a list of the author Authorlist, need to be based on the authorlist of the author's information in the database to query the corresponding author's blog information. Then the easiest way to think about it is to traverse authorlist and get the appropriate information query database.

for (int i=0;i < authorlist.size (); i++) {
...
Query Database Code
//select * from blog where Author=#{author,jdbctype=varchar}
}

Think about it, if we assume that the length of Authorlist is n, then we need to query n times the database, if this method, the program is not only the cost of the query, as well as from the database connection pool to remove connection instances, establish a database connection, the database instance returned to the database connection pool, Suppose these three actions add up to a total of 0.001 seconds. Then take the traversal method query, will be more time-consuming 0.001N seconds, if you need to query 1000 times, then more than 1 seconds of time, for the program ape, this is intolerable, because this is just a circular query, not counting other business code.

Well, there's no better way, the answer is yes, one of them is dynamic sql:

First code:

<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= ")" >< C3/>#{item}
</foreach>
</select>

The TEM represents the alias of each element in the collection when iterating.

index specifies a name that represents the location of each iteration during the iteration.

Open indicates what the statement starts with,

Separator represents what symbols are used as delimiters between iterations each time,

Close indicates what ends this return value can be accepted with list<bolg>.

However, the foreach statement in dynamic SQL uses most of the actual INSERT statements, and is usually used in the in clause.

Second, advanced mapping

When using MyBatis, you typically use Resulttype = com.blog.author entity classes to accept query results

or use Resulttype = Java.util.map to take the database column name as key, and the record value is returned as value.

This time, however, you need to use Resultmap, which allows you to freely combine the return value of the form to handle more complex queries.

Or the first 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,jdbc Type=integer}
</select>

MyBatis Configuration:

<resultmap id= "Blogs" type= "Com.bloh.Blog" >
<id property= "Authorid" column= "Authorid" >
< Result property= "AuthorName" column= "AuthorName" > <collection property= "postslist"
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 classes

public class Post {
private Integer BlogID;
Private String blogname;
Setter Getter
}

This allows a complex query to be accepted with an entity.

The role of each of the following properties is described below:

Other and ordinary MyBatis query properties and configuration is not in detail,

Resultmap is used instead of Resulttype to indicate the format returned by the query result

The IDs in Resultmap have two main functions:

Similar indexes to improve query performance

Differentiate between different results

So it is best not to omit the ID, if there is no primary key, with a field that can uniquely distinguish between records instead

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

Collection is a collection of lists, maps, etc.

Postslist is the name of the list variable defined in the Blog entity class

OfType is the entity class for objects in the object list.

Third, obtain the self-increasing ID:

When you insert a database record, you want the primary key for the inserted record to follow the business code

The MyBatis also provides appropriate support for this situation (bulk inserts are not supported):

MySQL is the soundtrack ID, assuming the field name of the self-added 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>

Two more properties than normal inserts usegeneratedkeys= "True" indicates that the return ID is turned on

keyproperty= "id" indicates the name that returns the primary key.

The following statements can be used in the business code to receive:

Suppose the entity class is user

User Usernew = Usermapper.insert (user);

Usernew.getid//Is the inserted self-ID

In fact, MySQL's self-added primary key can be used with select last_insert_id ();

So there's another way to do 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}) C11/></insert>

and MySQL's access to the primary key in the opposite way, MySQL is inserted by the table after the allocation of the growth of the value, and Oracle is obtained from the growth of the value of the insert record operation, before the execution of insert SQL must specify a primary key value to the record to insert so to be in the " Before "The time to get the self-added sequence, and then injected into the parameter mapping in a Selectkey way. Suppose the growth or ID

<insert id= "Insert" usegeneratedkeys= "true" keyproperty= "id" parametertype= "xxxx" >
<selectkey resulttype= "int" order= "before" keyproperty= "id" >
SELECT seq_table. Nextval from dual
</selectKey>
inserts into Id,name,age
VALUES
(#{id} #{name}, #{age})
< /insert>

The ID here is the Selectkey ID obtained by the.

Receive in the same way as MySQL, it is best to use entity acceptance when acquiring a self-added primary key.

The above is a small set to introduce the MyBatis Advanced mapping, dynamic SQL and get the primary key, hope to help everyone, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!