Detailed explanation of the example code of Mybatis mapping file-Java Tutorial-php.cn

This article mainly introduces the detailed explanation of Mybatis mapping file examples. Friends who need it can refer to it

1. Input mapping

##parameterType

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

1. Pass simple type

Query user information based on user ID:

Copy after login

2. Pass POJO object

Add user:

 
   
   SELECT LAST_INSERT_ID() 
   
  INSERT INTO USER(username,birthday,sex,address) VALUES(#{username},#{birthday},#{sex},#{address})

Copy after login

3. Pass POJO packaging object

During development, query conditions are passed through pojo. The query conditions are comprehensive query conditions, including not only user query conditions but also other query conditions (such as user purchase product information as query conditions),

This You can use wrapper objects to pass input parameters.

3.1 Requirements

Comprehensive query of user information requires complex query conditions, such as (user information, order information, product information).

3.2 Define the packaging object

Generally, the User.java class should be consistent with the data table fields. It is best not to add other fields here. Reverse engineering in mybatis At this time, the po class will be generated based on the table structure.

If the field is extended in the po class, it will be overwritten at this time.

So for the po class to be extended, we need to create an extension class to inherit it.

public class UserExt extends User{ 
 //这里可以定义user的一些扩展信息 
}

Copy after login

Define POJO wrapper class:

public class UserQueryVO { 
 //用户信息 
 private UserExt userExt; 
 //商品ID集合 
 private List idList; 
 //商品信息 
 public List getIdList() { 
  return idList; 
 } 
 public void setIdList(List idList) { 
  this.idList = idList; 
 } 
 public UserExt getUserExt() { 
  return userExt; 
 } 
 public void setUserExt(UserExt userExt) { 
  this.userExt = userExt; 
 } 
 //订单信息 
}

Copy after login

3.3 Write the Mapper interface

//Use the packaging class to perform complex comprehensive user information query

public List findUserList(UserQueryVO userQueryVO);

3.4 Write mapper mapping file

Copy after login

Note: The type of input parameter becomes UserQueryVO, and the type of result set becomes UserExt, the parameters in #{} become the sex and username sub-attributes of the userExt attribute in the UserQueryVO object.

3.5 Write test code

@Test 
public void findUserListTest() { 
 // 创建SqlSession 
 SqlSession sqlSession = sqlSessionFactory.openSession(); 
 // 通过SqlSession，获取mapper接口的动态代理对象 
 UserMapper userMapper = sqlSession.getMapper(UserMapper.class); 
 //构造userQueryVO对象 
 UserQueryVO userQueryVO = new UserQueryVO(); 
 // 构造UserExt对象 
 UserExt userExt = new UserExt(); 
 userExt.setSex("1"); 
 userExt.setUsername("小明"); 
 userQueryVO.setUserExt(userExt); 
 // 调用mapper对象的方法 
 List list = userMapper.findUserList(userQueryVO); 
 System.out.println(list); 
 // 关闭SqlSession 
 sqlSession.close(); 
}

Copy after login

4. Pass HashMap

The same as passing POJO objects, the key of map is equivalent to the attribute of pojo.

4.1 Mapping file

Copy after login

The id and username marked in red above are hashmap key.

4.2 Test Code

Public void testFindUserByHashmap()throws Exception{ 
  //获取session 
  SqlSession session = sqlSessionFactory.openSession(); 
  //获限mapper接口实例 
  UserMapper userMapper = session.getMapper(UserMapper.class); 
  //构造查询条件Hashmap对象 
  HashMap map = new HashMap(); 
  map.put("id", 1); 
  map.put("username", "管理员"); 
   
  //传递Hashmap对象查询用户列表 
  Listlist = userMapper.findUserByHashmap(map); 
  //关闭session 
  session.close(); 
 }

Copy after login

Exception test:

Pass The key in the map is inconsistent with the key parsed in SQL.

The test result did not report an error, but the value obtained through the key was empty.

2. Output mapping

1. resultType

(1) Usage method

When using resultType for result mapping, the column name must be exactly the same as the mapped pojo attribute name, so that the column can be mapped successfully.

If the queried column name and the mapped pojo attribute name are all inconsistent, then the mapped object will be empty and no pojo object will be created;

If there is one between the queried column name and the mapped pojo attribute name If they are consistent, then the mapped object is not empty and a pojo object will be created, but only the correctly mapped attribute will have a value.

(2) Output simple type

Note that there are also requirements for simple type result mapping. The query column must be one column before it can be mapped to a simple type.

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

2.1 Requirements

Comprehensive query of the total number of users requires complex query conditions, such as (user information, order information, product information).

2.2Mapper mapping file

Copy after login

2.3Mapper interface

//综合查询用户信息总数。学习：resultType输出简单类型 
public int findUsersCount(UserQueryVO vo);

Copy after login

2.4 Test Code

@Test 
public void testFindUsersCount() { 
 // 创建SqlSession 
 SqlSession sqlSession = sqlSessionFactory.openSession(); 
 // 通过SqlSession，获取mapper接口的动态代理对象 
 UserMapper userMapper = sqlSession.getMapper(UserMapper.class); 
 //构造userQueryVO对象 
 UserQueryVO userQueryVO = new UserQueryVO(); 
 // 构造UserExt对象 
 UserExt userExt = new UserExt(); 
 userExt.setSex("1"); 
 userExt.setUsername("小明"); 
 userQueryVO.setUserExt(userExt); 
 int count = mapper.findUsersCount(userQueryVO); 
 System.out.println(count); // 关闭SqlSession 
 sqlSession.close(); 
}

Copy after login

(3) Output POJO single object and list

Note: When outputting a single pojo object and pojo list (holding pojo objects), the type of resultType in the mapper mapping file is Similarly, the method return values of the mapper interface are different.

3.1Mapper mapping file

Copy after login

3.2Mapper interface

1. Output a single pojo object

//根据用户名称来模糊查询用户信息 
 public User findUsersByName(String username);

Copy after login

2. Output a pojo list

//根据用户名称来模糊查询用户信息列表 
 public List findUsersByName(String username);

Copy after login

Summary: When the same mapper mapping file returns a single object and a list of objects, when the mapper interface generates a dynamic proxy,

会根据返回值的类型，决定调用selectOne方法还是selectList方法。

2、resultMap

resultMap可以进行高级结果映射（一对一、一对多映射）。

（1）使用方法

如果查询出来的列名和属性名不一致，通过定义一个resultMap将列名和pojo属性名之间作一个映射关系。

1、定义resultMap

2、使用resultMap作为statement的输出映射类型。

（2）需求

把下面SQL的输出结果集进行映射

SELECT id id_,username username_,sex sex_FROM USER WHERE id = 1

Copy after login

（3）Mapper映射文件

定义resultMap：

Copy after login

定义statement：

Copy after login

（4）Mapper接口定义

 //根据ID查询用户信息（学习resultMap） 
 public User findUserByIdResultMap(int id);

Copy after login

定义Statement使用resultMap映射结果集时，Mapper接口定义方法的返回值类型为mapper映射文件中resultMap的type类型。

（5）测试代码

@Test 
public void findUserByIdResultMapTest() { 
 // 创建SqlSession 
 SqlSession sqlSession = sqlSessionFactory.openSession(); 
 // 通过SqlSession，获取mapper接口的动态代理对象 
 UserMapper userMapper = sqlSession.getMapper(UserMapper.class); 
 // 调用mapper对象的方法 
 User user = userMapper.findUserByIdResultMap(1); 
 System.out.println(user); 
 // 关闭SqlSession 
 sqlSession.close(); 
}

Copy after login

三、动态SQL

1、If和where

Ø If标签：作为判断入参来使用的，如果符合条件，则把if标签体内的SQL拼接上。
注意：用if进行判断是否为空时，不仅要判断null，也要判断空字符串‘'；
Ø Where标签：会去掉条件中的第一个and符号。

（1）需求

用户信息综合查询列表和用户信息综合查询总数这两个statement的定义使用动态SQL。

（2）映射文件

Copy after login

（3）Mapper接口

//通过包装类来进行复杂的用户信息综合查询 
public List findUserList(UserQueryVO userQueryVO); 
//综合查询用户总数 
public int findUsersCount(UserQueryVO userQueryVO);

Copy after login

（4）测试代码

不传用户名：

@Test 
 public void testFindUserList() throws Exception{ 
  // 创建UserMapper对象 
  SqlSession sqlSession = sqlSessionFactory.openSession(); 
  // 由mybatis通过sqlsession来创建代理对象 
  UserMapper mapper = sqlSession.getMapper(UserMapper.class); 
  QueryUserVO vo = new QueryUserVO(); 
  User user = new User(); 
  //此处使用动态SQL，不传username参数 
  user.setSex("1"); 
//  user.setUsername("小明"); 
  vo.setUser(user); 
  List list = mapper.findUserList(vo); 
  System.out.println(user); 
  sqlSession.close(); 
 }

Copy after login

输出的SQL如下（也不包含用户名）：

通过测试可以得知，打印出的SQL语句确实会随着条件的满足情况而不一样。

2、SQL片段

Mybatis提供了SQL片段的功能，可以提高SQL的可重用性。

2.1定义SQL片段

使用sql标签来定义一个SQL片段：

 
 
 
  
   
   AND sex = #{userExt.sex} 
   
   
   AND username LIKE '%${userExt.username}%'

Copy after login

2.2引用SQL片段

使用来引用SQL片段：

Copy after login

3、foreach

向sql传递数组或List时，mybatis使用foreach解析数组里的参数并拼接到SQL中。

（1）传递pojo对象中的list集合

1.1需求

在用户查询列表和查询总数的statement中增加多个id输入查询。

1.2SQL

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

Copy after login

1.3定义pojo中的list属性

package com.itheima.mybatis.po; 
import java.util.List; 
/** 
 * Title: UserQueryVO 
 * Description: TODO(这里用一句话描述这个类的作用) 
 
 */ 
public class UserQueryVO { 
 //用户信息 
 private UserExt userExt; 
 //商品ID集合 
 private List idList; 
 //商品信息 
 public List getIdList() { 
  return idList; 
 } 
 public void setIdList(List idList) { 
  this.idList = idList; 
 } 
 public UserExt getUserExt() { 
  return UserExt; 
 } 
 public void setUserExt(UserExt userExt) { 
  this.UserExt = UserExt; 
 } 
 //订单信息 
}

Copy after login

1.4映射文件

 
 
 
 
 
 
 
 
  #{id}

Copy after login

1.5Mapper接口

//根据用户ID的集合查询用户列表（学习foreach标签之通过POJO对象传ID集合） 
public List findUserList(UserQueryVO vo);

Copy after login

1.6测试代码

@Test 
public void testFindUserList() { 
  // 创建SqlSession 
  SqlSession sqlSession = sqlSessionFactory.openSession(); 
  // 通过SqlSession，获取mapper接口的动态代理对象 
  UserMapper mapper = sqlSession.getMapper(UserMapper.class); 
  // 构造QueryUserVO对象 
  QueryUserVO vo = new QueryUserVO(); 
  // UserExt ext = new UserExt(); 
  // ext.setUsername("小明"); 
  // ext.setSex("1"); 
  // vo.setUserExt(ext); 
  // 创建用户ID集合，然后设置到QueryUserVO对象中 
  List idList = new ArrayList(); 
  idList.add(1); 
  idList.add(10); 
  idList.add(16); 
  vo.setIdList(idList); 
  // 调用mapper代理对象的方法 
  List list = mapper.findUserList(vo); 
  System.out.println(list); 
  // 关闭SqlSession 
  sqlSession.close(); 
}

Copy after login

（2）直接传递List集合

2.1需求

根据用户ID的集合查询用户列表

2.2SQL

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

Copy after login

2.3映射文件

Copy after login

2.4Mapper接口

//根据用户ID的集合查询用户列表（学习foreach标签之直接传ID集合） 
public List findUsersByIdList (List idList);

Copy after login

2.5测试代码

@Test 
public void findUsersByIdListTest() { 
  // 创建SqlSession 
  SqlSession sqlSession = sqlSessionFactory.openSession(); 
  // 通过SqlSession，获取mapper接口的动态代理对象 
  UserMapper userMapper = sqlSession.getMapper(UserMapper.class); 
  // 构造List集合 
  List idList = new ArrayList(); 
  idList.add(1); 
  idList.add(10); 
    idList.add(16); 
  // 调用mapper对象的方法 
  List list = userMapper.findUsersByIdList (idList); 
  System.out.println(list); 
  // 关闭SqlSession 
  sqlSession.close(); 
}

Copy after login

The above is the detailed content of Detailed explanation of the example code of Mybatis mapping file. For more information, please follow other related articles on the PHP Chinese website!