MyBatis-Plus常⽤语法使⽤实例汇总（全）
MyBatis-Plus常⽤语法使⽤汇总
总结汇总⼀些MyBatis-Plus常⽤的语法，包括常⽤的增删改查，复杂查询构建器，连接多表查询，⾃定义sql传参查询，构建实体查
询，Lambda查询，函数式查询等等。这⾥不在重复叙述MyBatis-Plus的项⽬搭建过程，详细搭建配置过程可以参考我写的，如果你是⾮SpringBoot项⽬可以参考这篇，也是我写的。
⽂章⽬录
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"))); // 条件嵌套查询
// 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->!Column().equals("age")
&&!Column().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 );
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "/dtd/mybatis-3-mapper.dtd"> <mapper namespace="learnlab.mapper.TbUserMapper">
<select id="selectAll" resultType="ity.TbUser">
select * from tb_user
</select>
<select id="selectUserById" resultType="ity.TbUser">
select * from tb_user where Id=#{id}
</select>
<select id="getUserList" resultType="ity.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>
update语法大全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");
}
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;
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "/dtd/mybatis-3-mapper.dtd"> <mapper namespace="learnlab.mapper.TbUserMapper">
<resultMap id="User" type="del.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.leName as role from tb_user u join tb_role r leID=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!");
}
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论