Mybatis mapping file example in detail

1. Input mapping

parameterType

Specify the Java type of the input parameter, using either an alias or the fully qualified name of the class. It can receive simple types, POJO, HashMap.

1. Pass simple types

Query user information according to user ID:

<select id="findUserById" parameterType="int" resultType="com.itheima.mybatis.po.User"> 
  SELECT * FROM USER WHERE id =#{id} 
</select> 

2. Pass the POJO object

Add user:

<insert id="insertUser" parameterType="com.itheima.mybatis.po.User"> 
  <selectKey keyProperty="id" resultType="int" order="AFTER"> 
   SELECT LAST_INSERT_ID() 
  </selectKey> 
  INSERT INTO USER(username,birthday,sex,address) VALUES(#{username},#{birthday},#{sex},#{address}) 
</insert> 

3. Pass the POJO wrapper object

During development, pojo is used to transmit the query condition, which is a comprehensive query condition, including not only the user's query condition but also other query conditions (such as taking the product information purchased by the user as the query condition).
You can then use the wrapper object to pass input parameters.

3.1 requirements

Comprehensive query user information, need to pass complex query conditions, such as (user information, order information, commodity information).

3.2 Define the wrapper object

1 Generally, ES36en. java class should be related to the data table table field 1. It is better not to add other fields in it.

If you extend the field in the po class, it will be overwritten at this point.

So for the po class we want to extend, we need to create an extension class to inherit from it.

public class UserExt extends User{ 
 // You can define it here user the 1 Some extended information  
} 

Define the POJO wrapper class:

public class UserQueryVO { 
 // The user information  
 private UserExt userExt; 
 // goods ID A collection of  
 private List<Integer> idList; 
 // Commodity information  
 public List<Integer> getIdList() { 
  return idList; 
 } 
 public void setIdList(List<Integer> idList) { 
  this.idList = idList; 
 } 
 public UserExt getUserExt() { 
  return userExt; 
 } 
 public void setUserExt(UserExt userExt) { 
  this.userExt = userExt; 
 } 
 // The order information  
} 

3.3 Write Mapper interface

// Through the packaging of classes to conduct complex user information integrated query
public List < UserExt > findUserList(UserQueryVO userQueryVO);

3.4 Write mapper mapping file

<!--  Through wrapping the class to carry on the complex user information synthesis inquiry  --> 
<select id="findUserList" parameterType="userQueryVO" resultType="userExt"> 
  SELECT * FROM USER WHERE sex=#{userExt.sex} AND username LIKE '%${userExt.username}%' 
</select> 

Note: incoming arguments become UserQueryVO, result sets become UserExt, and arguments in #{} become sex and username subproperties of the userExt attribute in the UserQueryVO object.

3.5 Write test code

@Test 
public void findUserListTest() { 
 //  create SqlSession 
 SqlSession sqlSession = sqlSessionFactory.openSession(); 
 //  through SqlSession To obtain mapper A dynamic proxy object for an interface  
 UserMapper userMapper = sqlSession.getMapper(UserMapper.class); 
 // structure userQueryVO object  
 UserQueryVO userQueryVO = new UserQueryVO(); 
 //  structure UserExt object  
 UserExt userExt = new UserExt(); 
 userExt.setSex("1"); 
 userExt.setUsername(" Xiao Ming "); 
 userQueryVO.setUserExt(userExt); 
 //  call mapper Method of an object  
 List<UserExt> list = userMapper.findUserList(userQueryVO); 
 System.out.println(list); 
 //  Shut down SqlSession 
 sqlSession.close(); 
} 

4. Pass on HashMap

As with passing POJO object 1, key of map is equivalent to the property of pojo.

4.1 Mapping file

<!--  pass hashmap Comprehensive query user information  --> 
 <select id="findUserByHashmap" parameterType="hashmap" resultType="user"> 
  select * from user where id=#{<span style="color:#ff0000;">id</span>} and username like '%${<span style="color:#ff0000;">username</span>}%' 
 </select> 

id and username, shown in red above, are key of hashmap.

4.2 Test Code

Public void testFindUserByHashmap()throws Exception{ 
  // To obtain session 
  SqlSession session = sqlSessionFactory.openSession(); 
  // A limit mapper Interface instance  
  UserMapper userMapper = session.getMapper(UserMapper.class); 
  // Construct query conditions Hashmap object  
  HashMap<String, Object> map = new HashMap<String, Object>(); 
  map.put("id", 1); 
  map.put("username", " The administrator "); 
   
  // pass Hashmap The object queries the list of users  
  List<User>list = userMapper.findUserByHashmap(map); 
  // Shut down session 
  session.close(); 
 } 

Exception test:

The key in the passed map is not the same as the key resolved in sql.

The test results did not report an error, except that the value obtained through key was null.

2. Output mapping

1, resultType

(1) Method of use

When using resultType for result mapping, the column name of the query and the pojo attribute name of the map are exactly 1 before the column can be mapped successfully.

If both the query column name and the mapped pojo attribute name are not 1, then the mapped object is empty and the pojo object is not created.

If the query's column name and the mapped pojo attribute name have a 1, the mapped object is not empty and the pojo object is created, but only the correctly mapped attribute has a value.

(2) Output simple types

Note that the result mapping for simple types is also required; the columns of the query must be 1 column in order to be mapped to simple types.

When the output result is only 1 column, you can use ResultType to specify a simple type as the output result type.

2.1 requirements

Comprehensive query user total, need to pass in complex query conditions, such as (user information, order information, commodity information).

2.2Mapper mapping file

<!--  Comprehensive query user information total, need to pass in complex query conditions, such as (user information, order information, commodity information)  --> 
<select id="findUsersCount" parameterType="UserQueryVO" 
  resultType="int"> 
  SELECT count(1) FROM USER WHERE sex = #{userExt.sex} AND username LIKE '%${userExt.username}%' 
</select> 

2.3 Mapper interface

// Comprehensive query the total number of user information. Study: resultType Output simple type  
public int findUsersCount(UserQueryVO vo); 

2.4 Test Code

<insert id="insertUser" parameterType="com.itheima.mybatis.po.User"> 
  <selectKey keyProperty="id" resultType="int" order="AFTER"> 
   SELECT LAST_INSERT_ID() 
  </selectKey> 
  INSERT INTO USER(username,birthday,sex,address) VALUES(#{username},#{birthday},#{sex},#{address}) 
</insert> 

0

(3) Output POJO single object and list

Note: When printing a single pojo object and an pojo list (containing pojo objects), the resultType in the mapper mapping file is of the same type, and the method return value of the mapper interface is different.

3.1Mapper mapping file

<select id="findUsersByName" parameterType="java.lang.String" resultType="cn.itcast.mybatis.po.User"> 
  SELECT * FROM USER WHERE username LIKE '%${value}%' 
</select> 

3.2 Mapper interface

1. Output a single pojo object

// Query user information by user name  
 public User findUsersByName(String username); 

2. Output pojo list

<insert id="insertUser" parameterType="com.itheima.mybatis.po.User"> 
  <selectKey keyProperty="id" resultType="int" order="AFTER"> 
   SELECT LAST_INSERT_ID() 
  </selectKey> 
  INSERT INTO USER(username,birthday,sex,address) VALUES(#{username},#{birthday},#{sex},#{address}) 
</insert> 

3

Summary: The same mapper mapping file returns a single object and a list of objects, while the mapper interface generates dynamic proxies,

The selectOne method or selectList method is called depending on the type of the return value.

2, resultMap

resultMap can do advanced result mapping (one-to-one 1.1 multi-mapping).

(1) Method of use

If the column name and the attribute name are not 1, define 1 resultMap to map between the column name and the pojo attribute name.

1. Define resultMap

2. Use resultMap as the output mapping type of statement.

(2) Demand

Map the output result set of SQL below

<insert id="insertUser" parameterType="com.itheima.mybatis.po.User"> 
  <selectKey keyProperty="id" resultType="int" order="AFTER"> 
   SELECT LAST_INSERT_ID() 
  </selectKey> 
  INSERT INTO USER(username,birthday,sex,address) VALUES(#{username},#{birthday},#{sex},#{address}) 
</insert> 

4

(3) Mapper mapping file

Define resultMap:

<insert id="insertUser" parameterType="com.itheima.mybatis.po.User"> 
  <selectKey keyProperty="id" resultType="int" order="AFTER"> 
   SELECT LAST_INSERT_ID() 
  </selectKey> 
  INSERT INTO USER(username,birthday,sex,address) VALUES(#{username},#{birthday},#{sex},#{address}) 
</insert> 

5

Define statement:

<insert id="insertUser" parameterType="com.itheima.mybatis.po.User"> 
  <selectKey keyProperty="id" resultType="int" order="AFTER"> 
   SELECT LAST_INSERT_ID() 
  </selectKey> 
  INSERT INTO USER(username,birthday,sex,address) VALUES(#{username},#{birthday},#{sex},#{address}) 
</insert> 

6

(4) Mapper interface definition

 // According to the ID Query user information (learn resultMap )  
 public User findUserByIdResultMap(int id);<strong> 
</strong> 

When defining Statement to use resultMap mapping result sets, the Mapper interface defines a method with a return value type of type of resultMap in the mapper mapping file.

(5) Test code

@Test 
public void findUserByIdResultMapTest() { 
 //  create SqlSession 
 SqlSession sqlSession = sqlSessionFactory.openSession(); 
 //  through SqlSession To obtain mapper A dynamic proxy object for an interface  
 UserMapper userMapper = sqlSession.getMapper(UserMapper.class); 
 //  call mapper Method of an object  
 User user = userMapper.findUserByIdResultMap(1); 
 System.out.println(user); 
 //  Shut down SqlSession 
 sqlSession.close(); 
} 

(3) dynamic SQL

1. If and where

& Oslash; If tag: to be used as a reference. If conditions are met, the SQL in the if tag is spliced.
Note: when if is used to judge whether it is empty or not, it is necessary to judge not only null, but also the empty string ' ';
& Oslash; Where tag: Removes the first and symbol in the condition.

(1) Demand

The user information comprehensive query list and the user information comprehensive query total are defined using dynamic SQL.

(2) Mapping file

<insert id="insertUser" parameterType="com.itheima.mybatis.po.User"> 
  <selectKey keyProperty="id" resultType="int" order="AFTER"> 
   SELECT LAST_INSERT_ID() 
  </selectKey> 
  INSERT INTO USER(username,birthday,sex,address) VALUES(#{username},#{birthday},#{sex},#{address}) 
</insert> 

9

(3) Mapper interface

// Through wrapping the class to carry on the complex user information synthesis inquiry  
public List<UserExt> findUserList(UserQueryVO userQueryVO); 
// Query the total number of users  
public int findUsersCount(UserQueryVO userQueryVO); 

(4) Test code

No user name:

@Test 
 public void testFindUserList() throws Exception{ 
  //  create UserMapper object  
  SqlSession sqlSession = sqlSessionFactory.openSession(); 
  //  by mybatis through sqlsession To create the proxy object  
  UserMapper mapper = sqlSession.getMapper(UserMapper.class); 
  QueryUserVO vo = new QueryUserVO(); 
  User user = new User(); 
  // Use dynamics here SQL , don't spread username parameter  
  user.setSex("1"); 
//  user.setUsername(" Xiao Ming "); 
  vo.setUser(user); 
  List<User> list = mapper.findUserList(vo); 
  System.out.println(user); 
  sqlSession.close(); 
 } 

The output of SQL is as follows (also without the user name) :

The tests show that the printed SQL statement does vary as the condition is satisfied.

2. Segment SQL

Mybatis provides the functionality of SQL fragments to improve the reusability of SQL.

2.1 Define the SQL fragment

Use the sql tag to define an SQL fragment:

<!--  define SQL fragment  --> 
<!-- 
 [sql The label ] Definition: 1 a SQL fragment  
 [id] : SQL Fragments of wei 1 logo  
  Advice:  
  1 , SQL The content in the fragment is best defined as a single table  
  2 , if it is a query field, do not write SELECT 
  3 If it is a conditional statement, do not write WHERE 
 --> 
<sql id="select_user_where"> 
 <if test="userExt != null"> 
  <if test="userExt.sex != null and userExt.sex != ''"> 
   AND sex = #{userExt.sex} 
  </if> 
  <if test="userExt.username != null and userExt.username != ''"> 
   AND username LIKE '%${userExt.username}%' 
  </if> 
 </if> 
</sql> 

2.2 References to SQL fragment

use < includerefid='' / > To quote the SQL snippet:

<!--  According to the user id To query user information (use SQL Fragment)  --> 
<!-- 
 [include The label ] : The reference is already defined SQL fragment  
 [refid] : the reference SQL fragment id 
--> 
<select id="findUserList" parameterType="userQueryVO" resultType="userExt"> 
 SELECT * FROM USER 
<where> 
  <include refid="select_user_where"/> 
 </where> 
</select> 
<!--  Comprehensive query user information total, need to pass in complex query conditions, such as (user information, order information, commodity information)  --> 
<select id="findUsersCount" parameterType="QueryUserVO" 
  resultType="int"> 
 SELECT count(1) FROM USER 
 <where> 
  <include refid="select_user_where"/> 
 </where> 
</select> 

3, foreach

When passing an array or List to sql, mybatis USES foreach to parse the parameters in the array and concatenate them into SQL.

(1) Pass the list collection in the pojo object

1.1 requirements

Add multiple id input queries to statement of the user query list and total number of queries.

1.2SQL

SELECT * FROM user WHERE id IN (1,10,16)

1.3 Define the list attribute in pojo

package com.itheima.mybatis.po; 
import java.util.List; 
/** 
 * <p>Title: UserQueryVO</p> 
 * <p>Description: TODO( Here with 1 A sentence describes what this class does ) <p> 
 */ 
public class UserQueryVO { 
 // The user information  
 private UserExt userExt; 
 // goods ID A collection of  
 private List<Integer> idList; 
 // Commodity information  
 public List<Integer> getIdList() { 
  return idList; 
 } 
 public void setIdList(List<Integer> idList) { 
  this.idList = idList; 
 } 
 public UserExt getUserExt() { 
  return UserExt; 
 } 
 public void setUserExt(UserExt userExt) { 
  this.UserExt = UserExt; 
 } 
 // The order information  
} 

1.4 Mapping file

<!-- [foreach The label ] Said: 1 a foreach cycle  --> 
<!-- [collection] : The name of the collection parameter. If the collection parameter is passed directly, the parameter name there can only be filled in [list] .  --> 
<!-- [item] : The number of objects that are traversed each time  --> 
<!-- [open] : Starts a string that traverses the collation  --> 
<!-- [close] : Ends the traversal splice of a string  --> 
<!-- [separator] : Characters that need to be spliced between each object traversed  --> 
<if test="idList != null and idList.size > 0"> 
<foreach collection="idList" item="id" open="AND id IN (" close=")" separator=","> 
  #{id} 
</foreach> 
</if> 

1.5 Mapper interface

// According to the user ID The collection query user list (learn foreach Passage of labels POJO The object travels ID Collection)  
public List<UserExt> findUserList(UserQueryVO vo); 

1.6 Test Code

@Test 
public void testFindUserList() { 
  //  create SqlSession 
  SqlSession sqlSession = sqlSessionFactory.openSession(); 
  //  through SqlSession To obtain mapper A dynamic proxy object for an interface  
  UserMapper mapper = sqlSession.getMapper(UserMapper.class); 
  //  structure QueryUserVO object  
  QueryUserVO vo = new QueryUserVO(); 
  // UserExt ext = new UserExt(); 
  // ext.setUsername(" Xiao Ming "); 
  // ext.setSex("1"); 
  // vo.setUserExt(ext); 
  //  Create a user ID Set to QueryUserVO In the object  
  List<Integer> idList = new ArrayList<Integer>(); 
  idList.add(1); 
  idList.add(10); 
  idList.add(16); 
  vo.setIdList(idList); 
  //  call mapper Methods of a proxy object  
  List<UserExt> list = mapper.findUserList(vo); 
  System.out.println(list); 
  //  Shut down SqlSession 
  sqlSession.close(); 
} 

(2) Directly pass List set

2.1 requirements

Query the list of users against the collection of user ID

2.2SQL

SELECT * FROM user WHERE id IN (1,10,16)

2.3 Mapping file

<!--  According to the user ID The collection query user list (learn foreach Direct transmission of labels ID Collection)  --> 
<!--  
  [foreach The label ] Said: 1 a foreach cycle  
  [collection] : The name of the collection parameter. If the collection parameter is passed directly, the parameter name there can only be filled in [list] .  
  [item] : Defines the name of the parameter after traversal of the collection  
  [open] : Need to be spliced before starting traversal SQL string  
  [close] : After the end of the traversal needs to be spliced SQL string  
  [separator] : Characters that need to be spliced between each object traversed  
 --> 
<select id="findUsersByIdList" parameterType="java.util.List" resultType="user"> 
  SELECT * FROM USER 
  <where> 
    <if test="list != null and list.size > 0"> 
      <foreach collection="list" item="id" open="AND id IN (" close=")" separator=","> 
        #{id} 
      </foreach> 
    </if> 
  </where> 
</select> 

2.4 Mapper interface

// According to the user ID The collection query user list (learn foreach Direct transmission of labels ID Collection)  
public List<User> findUsersByIdList (List<Integer> idList); 

2.5 Test code

@Test 
public void findUsersByIdListTest() { 
  //  create SqlSession 
  SqlSession sqlSession = sqlSessionFactory.openSession(); 
  //  through SqlSession To obtain mapper A dynamic proxy object for an interface  
  UserMapper userMapper = sqlSession.getMapper(UserMapper.class); 
  //  structure List<Integer> A collection of  
  List<Integer> idList = new ArrayList<Integer>(); 
  idList.add(1); 
  idList.add(10); 
    idList.add(16); 
  //  call mapper Method of an object  
  List<User> list = userMapper.findUsersByIdList (idList); 
  System.out.println(list); 
  //  Shut down SqlSession 
  sqlSession.close(); 
}