MyBatis-Plus常用语法使用实例汇总(全)

MyBatis-Plus常用语法使用汇总

总结汇总一些MyBatis-Plus常用的语法，包括常用的增删改查，复杂查询构建器，连接多表查询，自定义sql传参查询，构建实体查询，Lambda查询，函数式查询等等。这里不在重复叙述MyBatis-Plus的项目搭建过程，详细搭建配置过程可以参考我写的《springboot+mybatis-plus+mysql项目完整搭建(十分详尽！)》，如果你是非SpringBoot项目可以参考这篇《用maven-archetype-quickstart原型快速搭建mybatis-plus应用》，也是我写的。

1. 数据库表结构

先说明一下数据库的表结构，非常简单的两个表，用户表和角色表。

2. 实例化sql操作

2.1 单表插入示例

同单表删除操作

// 插入操作
@Test
public void InsertRole(){
	TbRole roleOne = new TbRole();
	roleOne.setRoleName("小组长");
	roleOne.setComment("小组长");
	System.out.println(roleMapper.insert(roleOne));

	List<TbRole> users = roleMapper.selectList(null);
	users.forEach(System.out::println);
	System.out.println("Test Finished!");
}

2.2 单表查询全部

@Test
public void RoleSelectAllTest(){
	List<TbRole> users = roleMapper.selectList(null);
	users.forEach(System.out::println);
	System.out.println("Test Finished!");
}

2.3 单表复杂查询

QueryWrapper<TbUser> queryWrapper =  new QueryWrapper<>();
// 嵌套子查询
queryWrapper.apply("gender=1").inSql("roleId","SELECT id from tb_role");
// and or 查询
queryWrapper.likeRight("name","王").and(wq-> wq.lt("age",20).or().isNotNull("email"));
// between and 查询
queryWrapper.likeRight("name", "王").and(wq -> wq.between("age", 10, 40).and(wqq -> wqq.isNotNull("email")));
// 条件嵌套查询
queryWrapper.nested(wq->wq.lt("age",19).or().isNotNull("email")).likeRight("name","王");
// in 查询
queryWrapper.in("age", Arrays.asList(16,18,20));
// last返回最后一个
queryWrapper.in("age", Arrays.asList(30,31,34,35)).last("limit 1");
// 筛选指定列
queryWrapper.select("name","age").likeRight("name","王");
// 排除不包含的列
queryWrapper.select(TbUser.class,info->!info.getColumn().equals("age")
		&&!info.getColumn().equals("gender"));

3. 构造实体查询

		// 构造实体查询
		TbUser user = new TbUser();
		user.setName("王丽丽");
		user.setAge(18);
		QueryWrapper<TbUser> queryWrapper2 = new QueryWrapper<>(user);

		List<TbUser> users = userMapper.selectList(queryWrapper);
		users.forEach(System.out::println);
		System.out.println("Test Finished!");

4. Lambda条件构造查询

// 构建Lambda查询的3种方式
LambdaQueryWrapper<TbUser> lambdaQueryWrapper = new QueryWrapper<TbUser>().lambda();
LambdaQueryWrapper<TbUser> lambdaQueryWrapper1 = new LambdaQueryWrapper<>();
LambdaQueryWrapper<TbUser> lambdaQueryWrapper2 = Wrappers.lambdaQuery();

lambdaQueryWrapper.like(TbUser::getName,"明").lt(TbUser::getAge,16);
List<TbUser> userList = userMapper.selectList(lambdaQueryWrapper);
userList.forEach(System.out::println);

// 函数式编程风格
List<TbUser> userList2 = new LambdaQueryChainWrapper<>(userMapper)
		.like(TbUser::getName, "明")
		.ge(TbUser::getAge, 20).list();
		
userList2.forEach(System.out::println);

5. 自定义xml构造sql查询

• Mapper中定义代码

public interface TbUserMapper extends BaseMapper<TbUser> {
    /**
     * 查询所有用户信息
     * @return list
     */
    List<TbUser> selectAll();
    
    /**
     * 根据id查询用户信息
     * @return TbUser
     */
    List<TbUser> selectUserById(@Param("id") Integer id);
    
    /**
     * 动态sql查询举例
     * @return List<TbUser>
     */
	List<TbUser> getUserList(@Param("userName ") String userName, 
							@Param("userRole ") int userRole );
} 

• mapper.xml中定义的代码

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.zhousonglin.mylearnlab.mapper.TbUserMapper">
    <select id="selectAll" resultType="cn.zhousonglin.mylearnlab.entity.TbUser">
        select * from tb_user
    </select>
    <select id="selectUserById" resultType="cn.zhousonglin.mylearnlab.entity.TbUser">
      	select * from tb_user where Id=#{id}
    </select>
    <select id="getUserList" resultType="cn.zhousonglin.mylearnlab.entity.TbUser">
 		select * from tb_user 
	    <where>
		      <if test="userName != null and userName != ''">
		        and u.userName like concat('%', #{userName}, '%')
		      </if>
		      <if test="userRole != null">
		        and u.userRole = #{userRole}
		      </if>
	    </where>
	</select>
</mapper>

• application.yml中配置文件定义

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations: mapper/*.xml

• 使用代码

@Test
public void UserSelfXmlSqlSelect()
{
	List<TbUser> userList = userMapper.selectAll();
	userList.forEach(System.out::println);
}

6. 单表更新语句

// 更新用户数据
@Test
public void UserUpdate()
{
	// 第一种
	UpdateWrapper<TbUser> updateWrapper = new UpdateWrapper<>();
	updateWrapper.eq("name","李艺伟").eq("age",26);
	TbUser user = new TbUser();
	user.setEmail("update2@email");
	int rows = userMapper.update(user, updateWrapper);
	System.out.println(rows);
	
	// 第二种
	UpdateWrapper<TbUser> updateWrapper2 = new UpdateWrapper<>();
	updateWrapper2.eq("name","李艺伟").eq("age",26).set("email","update3@email.com");
}

7. Lambda单表更新数据

// Lambda更新数据
@Test
public void UserUpdateByLambda(){
	LambdaUpdateWrapper<TbUser> lambdaUpdateWrapper = Wrappers.lambdaUpdate();
	lambdaUpdateWrapper.eq(TbUser::getName,"李艺伟").eq(TbUser::getAge,26).set(TbUser::getAge,27);
	userMapper.update(null,lambdaUpdateWrapper);
}

8. Active Record 模式

• entity实体代码

@Data
@TableName("tb_role")
public class TbRole extends Model<TbRole> {
    @TableId("id")
    private int id;
    @TableField("roleName")
    private String roleName;
    @TableField("comment")
    private String comment;
}

• 实体操作代码

@Test
public void RoleSelectAllActiveRecord(){
	TbRole role = new TbRole();
	role.selectAll().forEach(System.out::println);
}

9. Join内联接查表

• model代码

@Data
public class User {
    private int id;
    private String name;
    private int age;
    private int gender;
    private String email;
    private String roleName;
}

• mapper.xml配置

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.zhousonglin.mylearnlab.mapper.TbUserMapper">

    <resultMap id="User" type="cn.zhousonglin.mylearnlab.entity.model.User">
        <result column="id" property="id" />
        <result column="name" property="name" />
        <result column="age" property="age" />
        <result column="gender" property="gender" />
        <result column="email" property="email" />
        <result column="role" property="roleName" />
    </resultMap >

    <select id="selectUserAndRoleAll" resultMap="User">
        select u.id,u.name,u.age,u.gender,u.email,r.roleName as role from tb_user u join tb_role r on u.roleID=r.id
        <where>
            ${ew.sqlSegment}
        </where>
    </select>
</mapper>

• mapper层代码

public interface TbUserMapper extends BaseMapper<TbUser> {
    List<User> selectUserAndRoleAll(@Param("ew") Wrapper<User> wrapper);
}

• 使用代码

@Test
public void UserJoinRoleSelectAll(){
	QueryWrapper<User> queryWrapper =  new QueryWrapper<>();
	queryWrapper.like("name","明");
	List<User> users = userMapper.selectUserAndRoleAll(queryWrapper);
	users.forEach(System.out::println);
	System.out.println("Test Finished!");
}