零SQL实现CRUD你还没学会？这最后一次啦-SQL查询

书接上篇零SQL实现CRUD你还没学会？这最后一次啦-SQL查询

模糊查询-like/notLike跟likeLeft/likeRight

用法：模糊查询，sql：

        like： where 列 like  "%值%"

        notLike：where 列 like  "%值%"

        likeLeft：where lie like "%值"

        likeRight：where lie like "值%"

需求：查询名字中含有 “ye” 字样的员工信息   like 

需求：查询名字中不含有 “ye” 字样的员工信息   notLikie

需求：查询名字中以 “ye” 字样结尾员工信息   likeLeft

需求：查询名字中以 “ye” 字样开头员工信息   likeRight

@Test
public void testQuery6() {
    QueryWrapper<Employee> wrapper = new QueryWrapper<>();
    wrapper.like("name", "ye");  // like "%ye%"
    //wrapper.notLike("name", "ye");  // not like "%ye%"
    //wrapper.likeLeft("name", "ye");  // like "%ye"
    //wrapper.likeRight("name", "ye");  // like "fei%"
    List<Employee> list = employeeMapper.selectList(wrapper);
}

执行后SQL

 SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name LIKE ?)

-- "%ye%"  -- like
-- "%ye"   -- likeLeft
-- "ye%"   -- likeRight

逻辑运算符-and

用法：逻辑与， sql： where 条件1 and 条件2

需求：查询年龄介于18~30岁的员工信息

@Test
public void testQuery8() {
    QueryWrapper<Employee> wrapper = new QueryWrapper<>();
    //多个条件默认使用and
    wrapper.ge("age", 18).le("age", 30);
    List<Employee> list = employeeMapper.selectList(wrapper);
}

执行后SQL

SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (age >= ? AND age <= ?)

逻辑运算符-or

用法：逻辑或， sql： where 条件1 or 条件2

需求：查询年龄小于18或大于30岁的员工信息

@Test
public void testQuery8() {
    QueryWrapper<Employee> wrapper = new QueryWrapper<>();
    //多个条件使用or
    wrapper.lt("age", 18)
            .or()
            .gt("age", 30);
    List<Employee> list = employeeMapper.selectList(wrapper);
}

执行后SQL

SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (age < ? OR age > ?)

多个条件连接默认使用and拼接，如果是or操作需要明确调用or方法。

逻辑条件嵌套

需求：查询名字带有"ye"字样，或者年龄介于18~30岁的员工信息

@Test
public void testQuery10() {
    QueryWrapper<Employee> wrapper = new QueryWrapper<>();
    wrapper.like("name", "ye")
            .or(wr -> wr.le("age", 30).ge("age", 18));
    List<Employee> list = employeeMapper.selectList(wrapper);
}

 执行后SQL

SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name LIKE ? OR (age <= ? AND age >= ?))

需求：查询名字带有"ye"字样，并且年龄小于18或大于30岁的员工信息

@Test
public void testQuery11() {
    QueryWrapper<Employee> wrapper = new QueryWrapper<>();
    wrapper.like("name", "ye")
            .and(wr -> wr.lt("age", 18).or().gt("age", 30));
    List<Employee> list = employeeMapper.selectList(wrapper);
}

执行后SQL

SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name LIKE ? AND (age < ? OR age > ?))

分组查询-group by 跟 having

用法：分组查询， sql： group  by 列   having  条件

需求：查询每个部门员工个数

@Test
public void testQuery12() {
    QueryWrapper<Employee> wrapper = new QueryWrapper<>();
    wrapper.select("dept_id", "count(id) count");
    wrapper.groupBy("dept_id");
    List<Map<String, Object>> mapList = employeeMapper.selectMaps(wrapper);
    mapList.forEach(System.out::println);
}

 执行后SQL

SELECT dept_id,count(id) count FROM employee GROUP BY dept_id

需求：查询每个部门员工个数，筛选出个数大于3的部门

@Test
public void testQuery13() {
    QueryWrapper<Employee> wrapper = new QueryWrapper<>();
    wrapper.select("dept_id", "count(id) count");
    wrapper.groupBy("dept_id");
    //wrapper.having("count > 3");
    wrapper.having("count > {0}", 3);
    List<Map<String, Object>> mapList = employeeMapper.selectMaps(wrapper);
    mapList.forEach(System.out::println);
}

 执行后SQL

-- count > 3
SELECT dept_id,count(id) count FROM employee GROUP BY dept_id HAVING count > 3

-- count > {0}
SELECT dept_id,count(id) count FROM employee GROUP BY dept_id HAVING count > ?

自定义SQL

上面的wrapper查询，针对简单sql场景非常简便，但是如果业务相对复杂，需要要求更灵活的SQL时(比如多表关联查询，动态sql等)，wrapper有点力不从心了，此时可以使用自定义sql方式。这种方式不是啥新鲜货，其实就是还原之前的Mybatis的XxxxMapper.xml写法。

Mapper接口

public interface EmployeeMapper extends BaseMapper<Employee> {
    List<Employee> listByXmlSingle();  //自定义方法
}

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.wolfcode.mp.mapper.EmployeeMapper" >

    <resultMap id="BaseResultMap" type="cn.wolfcode.mp.domain.Employee" >
        <id column="id" jdbcType="BIGINT" property="id" />
        <result column="name" jdbcType="VARCHAR" property="name" />
        <result column="password" jdbcType="VARCHAR" property="password" />
        <result column="email" jdbcType="VARCHAR" property="email" />
        <result column="age" jdbcType="INTEGER" property="age" />
        <result column="admin" jdbcType="BIT" property="admin" />
        <result column="dept_id" property="deptId" />
    </resultMap>


    <select id="listByXmlSingle" resultMap="BaseResultMap">
        select id, name, password, email, age, admin, dept_id
        from employee
    </select>

</mapper>

到这，本篇就结束啦