MyBatis Advanced Mapping, dynamic SQL, and getting a self-augmented primary key

First, 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:

 <SelectId="Dynamicforeachtest" Resulttype="Com.blog.Blog" ParameterType="Java.util.List">    Select *  fromBlogwhereAuthorinch    <foreach Collection="List"Index="IndexThe item="Item"Open="(" Separator=","Close=")">#{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:

<SelectId="Getblogs" Resultmap="Blogs" ParameterType="Map">    SelectA.authorid, A.uthorname, B.blogid, B.blogname fromAuthor A Left JoinBlog b onA. Authorid=B. AuthoridwhereA. Authorid=#{authorid,jdbctype=INTEGER}</Select>              

MyBatis Configuration:

    <ResultmapID= "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

class Bolg {    private  Integer Authorid;     Private String authorname;     Private List<post> postslist;     // Setter Getter }

Post entity class

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">insertinto<include refID = /> (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 =// 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:

<InsertID= "Insert"ParameterType= "User"><SelectkeyResulttype= "int"Order= "after"Keyproperty= "id">SELECT last_insert_id () as ID</Selectkey>INSERT INTO Name,agevalues (#{name}, #{age})</Insert>

and the MySQL the way to get the primary key is just the opposite, MySQL is a Insert after execution, the table is assigned self-growing values, and Oracle is to obtain a self-growing value before inserting a record operation, in the execution Insert SQL You must specify a primary key value for the record you want to insert, so you want to before "The time to get the self-increment sequence, and then use Selectkey into the input parameter map. Assuming the self-growth or ID

<InsertID= "Insert"Usegeneratedkeys= "true"Keyproperty= "id"ParameterType= "xxxx" ><SelectkeyResulttype= "int"Order= "Before"Keyproperty= "id">SELECT seq_table. Nextval from dual</Selectkey>INSERT into 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.

MyBatis Advanced Mapping, dynamic SQL, and getting a self-augmented primary key