MyBatis's mapper and common skills

Select

Let's look at a simple case:

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

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. Note the parameter #{id}, similar to the JDBC statement:

String Selectperson = "SELECT * from the person WHERE id=?"; PreparedStatement PS = conn.preparestatement (Selectperson);p s.setint (1,id);

Select Attributes

Property	Describe
Id	A unique identifier in the namespace that can be used to refer to this statement.
ParameterType	The fully qualified name or alias of the parameter class that will pass in the statement. This property is optional because MyBatis can infer the parameters of the specific incoming statement through Typehandler, and the default value is unset.
Parametermap	This is a deprecated method that references an external parametermap. Use inline parameter mapping and ParameterType properties.
Resulttype	The fully qualified name or alias of the class of the expected type returned from this statement. Note that if it is a collection case, it should be the type that the collection can contain, not the collection itself. Use Resulttype or Resultmap, but not at the same time.
Resultmap	A named reference to an external resultmap. The mapping of result sets is the most powerful feature of MyBatis, and a good understanding of it is that many complex mapping scenarios can be solved. Use Resultmap or Resulttype, but not at the same time.
Flushcache	Setting it to true will cause both the local cache and the level two cache to be emptied whenever the statement is invoked, with the default value: False.
UseCache	Setting it to true causes the result of this statement to be cached by level two, the default: True for the SELECT element.
Timeout	This setting is the number of seconds that the driver waits for the database to return the result of the request before throwing an exception. The default value is unset (dependent driver).
Fetchsize	This is an attempt to affect the number of results per batch returned by the driver and the value of this setting is equal. The default value is unset (dependent driver).
StatementType	One of the statement,prepared or callable. This allows MyBatis to use statement,preparedstatement or CallableStatement, respectively, with the default value: PREPARED.
ResultsetType	One of the forward_only,scroll_sensitive or scroll_insensitive, the default value is unset (dependent driver).
databaseid
resultordered	false .
ResultSets	This setting applies only to multi-result sets, which list the result sets returned after the statement executes and each result set to a name, separated by commas.

Fuzzy query for select:

<select id= "Getuserbyname" parametertype= "string" resulttype= "Cn.itheima.mybatis.po.User" > select * from ' User ' WHERE username like '%${value}% ' </select>

The properties of the Insert,update,delete are close:

Insert, Update, Delete ' s Attributes

Properties	Description
Id	A unique identifier in the namespace that can be used to represent the statement.
ParameterType	The fully qualified class name or alias of the parameter that will pass in the statement. This property is optional because MyBatis can infer the parameters of the specific incoming statement through Typehandler, and the default value is unset.
Parametermap	This is a deprecated method that references an external parametermap. Use inline parameter mapping and ParameterType properties.
Flushcache	Setting it to true will cause both the local cache and the level two cache to be emptied whenever the statement is invoked, with the default value: True (corresponding to insert, UPDATE, and DELETE statements).
Timeout	This setting is the number of seconds that the driver waits for the database to return the result of the request before throwing an exception. The default value is unset (dependent driver).
StatementType	One of the statement,prepared or callable. This allows MyBatis to use statement,preparedstatement or CallableStatement, respectively, with the default value: PREPARED.
Usegeneratedkeys	(Only useful for insert and update) This causes MyBatis to use the JDBC Getgeneratedkeys method to remove the primary key generated internally by the database (for example, an auto-increment field for a relational database management system such as MySQL and SQL Server) ), the default value: False.
keyproperty	unset . If you want to get more than one generated column, you can also be a comma-delimited list of property names.
KeyColumn	(Only useful for insert and update) sets the column name in the table by the generated key value, which is required only in certain databases (like PostgreSQL), and when the primary key column is not the first column in the table. If you want to get more than one generated column, you can also be a comma-delimited list of property names.
DatabaseId	If Databaseidprovider,mybatis is configured, all statements without databaseId or matching the current databaseId are loaded, and if a statement with or without is present, it is ignored.

Insert

Usegeneratekeys= "true" and keyproperty= "id" are used together to indicate that the ID is a self-growing field. Note: The parameter is a variable in userinfo, not a field in the database

<insert id= "Insertuser" parametertype= "Com.shadow.foretaste.entity.UserInfo" usegeneratedkeys= "true" keyproperty= "id" > INSERT into User_info (nickname, Phone_num) VALUES (#{nickname}, #{phonenum}) </insert>

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>

Parameters:

The parameter objects of the User type are passed into the statement, and the IDs, username, and password properties are looked up, and their values are passed into the parameters of the preprocessing statement.

<insert id= "Insertuser" parametertype= "User" > INSERT into Users (ID, username, password) VALUES (#{id}, #{username }, #{password}) </insert>

Specify the type of the parameter:

#{property,javatype=int,jdbctype=numeric,typehandler=mytypehandler,mode=out,resultmap=user}

Property: The name of the attribute, which is the variable name passed in by the code.
Javatype: The type of the field in Java, such as Int.
Jdbctype: The type of the field in JDBC, such as numeric.
Typehandler: Type processor
Mode: Parameter type In,out or inout parameter

Resultmap: Results.

Here are a few common parameter types:

List

Public list<area> finduserlistbyidlist (list<long> idlist) {return getsqlsession (). SelectList ("Com.li      Ulanghan.finduserlistbyidlist ", idlist); }

<select id= "finduserlistbyidlist" parametertype= "java.util.ArrayList" resulttype= "User" > select * from User <where> user.id in (<foreach item= "guard" index= "index" collection= "list" Sepa Rator= "," > #{guard} </foreach>) </where> </select>

Array

public list<area> finduserlistbyidlist (int[] ids)  {          return getsqlsession (). SelectList (" Com.liulanghan.findUserListByIdList ",  ids);       } 

<select id= "finduserlistbyidlist" parametertype= "java.util.HashList" resulttype= "User" > select * from User user              <where> user.id in (<foreach item= "guard" index= "index" collection= "array" Separator= "," > #{guard} </foreach>) </where> </select>

Map

public boolean exists (Map<string, object> map) {          object count = getsqlsession (). SelectOne ("Com.liulanghan.exists",  map);           int  totalcount = integer.parseint (Count.tostring ());           return totalCount > 0 ? true : false;       } 

<select id= "exists"  parametertype= "Java.util.HashMap"  resulttype= "Java.lang.Integer" >           select count (*)  FROM USER user           <where>               <if test= "Code != null" >                    and  user. code = #{code}                </if>              <if  test= "Id != null" >                    and user.ID = #{id}                </if>               <if test= "idlist !=null " >                   and user.ID in  (                   < Foreach item= "guard"  index= "index"  collection= "Idlist"                        separator= "," > #{guard} </foreach>                   )                </if>          </where>   </select>

String substitution:

ORDER by ${columnname} replaces the value directly in the SQL statement.

This article is from the "History of Data Mining Engineers" blog, be sure to keep this source http://qianqiansun.blog.51cto.com/13271301/1965493

MyBatis's mapper and common skills