目录
一、MyBatis标签
1.2 if标签
sql语句表示,如果money不为空或者money不是空字符串并且大于1000输出结果,否则输出查询所有
<select id="selectByMoney" resultType="demo.entity.User">
select * from t_user
<where>
<if test="money != null and money != ''">
and money>1000
</if>
</where>
</select>
1.2 foreach标签
<select id="selectUserByIds" resultType="demo.entity.User">
select * from t_user
<where>
<foreach collection="ids" open="and id in(" item="id" separator="," close=")">
#{id}
</foreach>
</where>
</select>
1.3 sql标签
先生成一个通用语句,全局调用
<sql id="sql_temp">
select id ,user_name, passwd, money from t_user
</sql>
<select id="login" resultType="demo.entity.User">
<include refid="sql_temp"></include> where user_name=#{userName} and passwd=#{passwd}
</select>
1.4 where标签
<select id="findUser1" parameterType="org.mybatis.demo.po.User"
resultType="org.mybatis.demo.po.User">
select * from user
<where>
<if test="id!=null and id!=''">
and id=#{id}
</if>
<if test="username!=null and username!=''">
and username like '%${username}%'
</if>
</where>
</select>
where标签的作用是可以自动处理掉第一个and(可以参考if标ids为QueryVO对象的属性,属性的类型为List<Integer>。
1.5SQL片段的使用
声明SQL片段
<!-- 声明SQL片段 -->
<sql id="query_user_where">
<if test="id!=null and id!=''">
and id=#{id}
</if>
<if test="username!=null and username!=''">
and username like '%${username}%'
</if>
</sql>
引用SQL片段
<!-- 引用sql片段 -->
<select id="findUser2" parameterType="org.mybatis.demo.po.User"
resultType="org.mybatis.demo.po.User">
select * from user
<where>
<include refid="query_user_where"></include>
</where>
</select>
二、条件构造器
2.1QueryWrapper
继承自 AbstractWrapper ,自身的内部属性 entity 也用于生成 where 条件
及 LambdaQueryWrapper, 可以通过 new QueryWrapper().lambda() 方法获取.queryWrapper.lt()——小于
queryWrapper.le()——小于等于
queryWrapper.gt()——大于
queryWrapper.ge()——大于等于
queryWrapper.eq()——等于
queryWrapper.ne()——不等于
queryWrapper.betweeen(“age”,10,20)——age在值10到20之间
queryWrapper.notBetweeen(“age”,10,20)——age不在值10到20之间
queryWrapper.like(“属性”,“值”)——模糊查询匹配值‘%值%’
queryWrapper.notLike(“属性”,“值”)——模糊查询不匹配值‘%值%’
queryWrapper.likeLeft(“属性”,“值”)——模糊查询匹配最后一位值‘%值’
queryWrapper.likeRight(“属性”,“值”)——模糊查询匹配第一位值‘值%’
queryWrapper.isNull()——值为空或null
queryWrapper.isNotNull()——值不为空或null
queryWrapper.in(“属性”,条件,条件 )——符合多个条件的值
queryWrapper.notIn(“属性”,条件,条件 )——不符合多个条件的值
queryWrapper.or()——或者
queryWrapper.and()——和
queryWrapper.orderByAsc(“属性”)——根据属性升序排序
queryWrapper.orderByDesc(“属性”)——根据属性降序排序
queryWrapper.inSql(“sql语句”)——符合sql语句的值
queryWrapper.notSql(“sql语句”)——不符合SQL语句的值
queryWrapper.esists(“SQL语句”)——查询符合SQL语句的值
queryWrapper.notEsists(“SQL语句”)——查询不符合SQL语句的值
2.1.1查询
查询名字等于add,并且钱等于1到100的人
@RequestMapping("tt")
public List<User> select(){
//创建条件构造器对象
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
//select * from t_user Where user_name='11'
queryWrapper.eq("user_name","add");
//select * from t_user where user_name='11' and money between 1300 and 1500
queryWrapper.between("money",1,100);
List<User> list = dao.selectList(queryWrapper);
return list;
}
2.1.2 查询列2
查询所有姓张的人
//查询姓张的人
@RequestMapping("/tt2")
public List<User> tt2(){
//创建条件构造器
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
queryWrapper.likeLeft("user_name","张");
List<User> list = dao.selectList(queryWrapper);
return list;
}
2.1.2 删除
删除钱为null(空) 的人
//删除money为null的人
@RequestMapping("/tt3")
public int tt3(){
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
queryWrapper.isNull("money");
int count = dao.delete(queryWrapper);
return count;
}
2.1.3子查询
根据id查询钱为1300到1500之间的人
@RequestMapping("/tt4")
public List<User> tt4(){
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
queryWrapper.inSql("id","select id from t_user where money between 1300 and 1500");
List<User> list = dao.selectList(queryWrapper);
return list;
}
2.1.4组装查询条件
查询用户名包含a,年龄在20到30之间,并且邮箱不为null的用户信息
@Test
public void test01(){
//查询用户名包含a,年龄在20到30之间,并且邮箱不为null的用户信息
//SELECT id,username AS name,age,email,is_deleted FROM t_user WHEREis_deleted=0 AND (username LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL)
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("username", "a")
.between("age", 20, 30)
.isNotNull("email");
List<User> list = userMapper.selectList(queryWrapper);
list.forEach(System.out::println);
}
2.1.5组装排序条件
按年龄降序查询用户,如果年龄相同则按id升序排列
@Test
public void test02(){
//按年龄降序查询用户,如果年龄相同则按id升序排列
//SELECT id,username AS name,age,email,is_deleted FROM t_user WHEREis_deleted=0 ORDER BY age DESC,id ASC
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper
.orderByDesc("age")
.orderByAsc("id");
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
2.1.6组装删除条件
//删除email为空的用户 //DELETE FROM t_user WHERE (email IS NULL)
@Test
public void test03(){
//删除email为空的用户
//DELETE FROM t_user WHERE (email IS NULL)
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.isNull("email"); //条件构造器也可以构建删除语句的条件
int result = userMapper.delete(queryWrapper);
System.out.println("受影响的行数:" + result);
}
2.1.7条件的优先级
将(年龄大于20并且用户名中包含有a)或邮箱为null的用户信息修改
@Test
public void test04() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>(); //将(年龄大于20并且用户名中包含有a)或邮箱为null的用户信息修改
//UPDATE t_user SET age=?, email=? WHERE (username LIKE ? AND age > ? OR email IS NULL)
queryWrapper
.like("username", "a")
.gt("age", 20) .or()
.isNull("email");
User user = new User();
user.setAge(18); user.setEmail("user@atguigu.com");
int result = userMapper.update(user, queryWrapper);
System.out.println("受影响的行数:" + result);
}
2.1.8 子查询列2
查询id小于等于3的用户信息
@Test
public void test06() {
//查询id小于等于3的用户信息
//SELECT id,username AS name,age,email,is_deleted FROM t_user WHERE (id IN (select id from t_user where id <= 3))
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.inSql("id", "select id from t_user where id <= 3");
List<User> list = userMapper.selectList(queryWrapper);
list.forEach(System.out::println);
}