(GO) MyBatis Advanced Mapping, dynamic SQL, and get self-augmented primary keys

Original: http://www.cnblogs.com/edwinchen/p/4105278.html?utm_source=tuicool&utm_medium=referral Dynamic SQL

I believe that everyone in the operation of the database with MyBatis will encounter a problem, if now we have a list authorlist about the author, need to according to the author information in the Authorlist in the database to query the corresponding author's blog information. Then the easiest way to do this is to traverse authorlist and get the corresponding information query database.

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

Consider that if the length of the authorlist is assumed to be n, then we need to query n-times the database, if this method, the program's cost is not only the query, but also from the database connection pool to remove the connection instance, establish a database connection, the database instance back to the database connection pool, Suppose these three actions add up to a total of 0.001 seconds. Then take the method of traversal query, will be more time-consuming 0.001N seconds, if you need to query 1000 times, then will be more than 1 seconds, for the program ape, this is intolerable, because this is only a circular query, not other business code.

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

First on the 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 it is iterated,

index specifies a name that represents the position of each iteration during the iteration,

Open indicates what the statement starts with,

Separator indicates what symbol is used as a delimiter between each iteration.

Close means that the return value can be accepted with list<bolg> by the end.

However, the foreach statement in dynamic SQL uses the most actual insert statement, and is typically used in the in clause.

Second, advanced mapping

When using MyBatis, it is common to use Resulttype = Com.blog.author entity classes to accept query results

Alternatively, use Resulttype = Java.util.map to return the database column name as a key, and the record value as value.

But this time you need to use Resultmap, which allows you to freely combine return values in a form that handles more complex queries.

Or the code first:

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 you to accept a complex query with an entity.

The following describes the functions of each property:

The properties and configuration of other and common MyBatis queries are not discussed,

Resultmap is used instead of Resulttype, which indicates the format returned by the query result

The ID in Resultmap has two main functions:

1. Similar indexes for improved query performance
2. Distinguish between different results

Therefore, the ID is best not to omit, if there is no primary key, with the ability to uniquely distinguish the record field instead of

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

Collection is a collection of lists, maps, etc.

Postslist is the list variable name defined in the blog entity class.

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

third, obtain the self-increment ID:

If, after inserting a database record, you want the primary key to insert the record, use the following business code

The MyBatis also provides support for this scenario (BULK INSERT is not supported):

MySQL is the OST self-increment ID, assuming that the field name of the self-increment primary key is the ID

<insert id= "Insert" usegeneratedkeys= "true" keyproperty= "id" parametertype= "User" >insert into <include refID = "table_name"/> (NAME, age) VALUES (#{name}, #{age}) </insert>

More than normal insert two properties usegeneratedkeys= "true" means turn on return self-increment ID

keyproperty= "id" indicates the name of the primary key returned.

Then the following statements can be received in the business code:

Assume that 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 ();

So there's another way to do this:

<insert id= "Insert" parametertype= "User" ><selectkey resulttype= "int" order= "after" keyproperty= "id" > SELECT last_insert_id () as Id</selectkey>insert into Name,agevalues (#{name}, #{age}) </insert>

and MySQL to get the primary key mode is just the opposite, MySQL is the insert after execution by the table to allocate self-growth value, and Oracle is to obtain the self-growth value after the insert record operation, before executing insert SQL must specify a primary key value to insert the record so you want to be in the " Before "When you get the self-increment sequence, and then inject it into the input parameter map in Selectkey way. Assuming the self-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>insert to Id,name,agevalues (#{id} #{name}, #{age}) </insert>

The ID here is the self-increment ID obtained by Selectkey.

As with MySQL, it is best to use entity ingestion when acquiring a self-increment primary key.

(GO) MyBatis Advanced Mapping, dynamic SQL, and get self-augmented primary keys