Mybatis-Plus中的查(select)和QueryWrapper条件构造器 V1.0

1，selectOne()方法。

        根据 QueryWrapper 的条件 查询返回一条数据，查询出多条数据则报错。

 QueryWrapper<T> queryWrapper = new QueryWrapper<T>();
 User user = userMapper.selectOne(QueryWrapper < T > queryWrapper);

          SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user WHERE (id = ?)
==> Parameters: 13(Integer)
<==      Total: 0

2，selectList()方法。

         根据 QueryWrapper 的条件 查询返回多条数据(List<T> 集合)。

QueryWrapper<T> queryWrapper = new QueryWrapper<T>();
List<User> users = userMapper.selectList(QueryWrapper<T> queryWrapper);

        SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user WHERE (age > ?)
==> Parameters: 12(Integer)
<==    Columns: id, name, sex, age, address
<==        Row: 7, null, 男, 13, kks
<==        Row: 8, null, 男, 13, kks
<==      Total: 2

3，selectCount()方法。

        根据 QueryWrapper 的条件 查询返回总数据的条数。

QueryWrapper<T> queryWrapper = new QueryWrapper<T>();
Integer integer = userMapper.selectCount(QueryWrapper < T > queryWrapper);

         SQL 语句如下：

==>  Preparing: SELECT COUNT( * ) FROM user WHERE (age > ?)
==> Parameters: 12(Integer)
<==    Columns: COUNT( * )
<==        Row: 2
<==      Total: 1

4，selectMaps()方法。

        根据 QueryWrapper 的条件 查询返回多条数据(List<Map<String,Object>> ListMap集合)。        

QueryWrapper<T> queryWrapper = new QueryWrapper<T>();
List<Map<String, Object>> maps = userMapper.selectMaps(QueryWrapper < T > queryWrapper);

         SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user WHERE (age > ?)
==> Parameters: 12(Integer)
<==    Columns: id, name, sex, age, address
<==        Row: 7, null, 男, 13, kks
<==        Row: 8, null, 男, 13, kks
<==      Total: 2

{address=kks, sex=男, id=7, age=13}
{address=kks, sex=男, id=8, age=13}

5，selectByMap()方法。

         根据 columnMap 封装的条件 查询返回多条数据(List<T> 集合)。

Map<String, Object> columnMap = new HashMap<>();
columnMap.put("id", 7);
columnMap.put("sex", "男");
List<User> users = userMapper.selectByMap(Map<String, Object>columnMap);

        SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user WHERE sex = ? AND id = ?
==> Parameters: 男(String), 7(Integer)
<==    Columns: id, name, sex, age, address
<==        Row: 7, null, 男, 13, kks
<==      Total: 1

6，selectById()方法。

        根据传入的 id 进行查询 查询成功返回单条数据，否则返回 null

User user = userMapper.selectById(Serializable id);

        SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user WHERE id=?
==> Parameters: 7(Integer)
<==    Columns: id, name, sex, age, address
<==        Row: 7, null, 男, 13, kks
<==      Total: 1

7，selectBatchIds()方法。

        根据传入的 idList 集合 进行查询返回多条数据(List<T> 集合)。。

List<Integer> idList = new ArrayList<>();
idList.add(5);
idList.add(6);
List<User> users = userMapper.selectBatchIds(Collection<? extends Serializable> idList);

        SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user WHERE id IN ( ? , ? )
==> Parameters: 5(Integer), 6(Integer)
<==      Total: 0

8，selectPage()方法。

9，selectMapsPage()方法。

10，QueryWrapper  的使用。

  10.1， allEq() 方法。

                allEq( Map < String, Object > params ) 

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
// 封装的条件 即便 map 参数的值为 null 仍然查询
Map<String, Object> map = new HashMap<>();
map.put("id", 7);
map.put("age", null);
queryWrapper.allEq(Map < String, Object > params);
List<User> users = userMapper.selectList(queryWrapper);

                SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user WHERE (id = ? AND age IS NULL)
==> Parameters: 7(Integer)
<==      Total: 0

                 allEq( Map < String, Object > params,boolean null2IsNull )

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
/**
* 封装的条件 map 参数的值为 null 自动忽略
*/
Map<String, Object> map = new HashMap<>();
map.put("id", 7);
map.put("age", null);
//  布尔值为 false 时自动忽略 map 参数值为空  true 时跟上边一样
queryWrapper.allEq(Map < String, Object > params,boolean null2IsNull);
List<User> users = userMapper.selectList(queryWrapper);

                SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user WHERE (id = ?)
==> Parameters: 7(Integer)
<==    Columns: id, name, sex, age, address
<==        Row: 7, null, 男, 13, kks
<==      Total: 1

  10.2， eq() 方法。

                eq( String column,Object val )

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
 /**
 * 多用于 判断单个字段值是否相等
*/
queryWrapper.eq(String column,Object val);
List<User> users = userMapper.selectList(queryWrapper);

                SQL 语句如下： 

==>  Preparing: SELECT id,name,sex,age,address FROM user WHERE (age = ?)
==> Parameters: 13(Integer)
<==    Columns: id, name, sex, age, address
<==        Row: 7, null, 男, 13, kks
<==        Row: 8, null, 男, 13, kks
<==      Total: 2

  10.3， ne() 方法。

                ne( String column,Object val )

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
/**
* 不等于某个值
*/
queryWrapper.ne(String column,Object val);
List<User> users = userMapper.selectList(queryWrapper);

                SQL 语句如下： 

==>  Preparing: SELECT id,name,sex,age,address FROM user WHERE (age <> ?)
==> Parameters: 13(Integer)
<==      Total: 0

  10.4， gt() 方法。

                gt( String column,Object val )

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
/**
* 大于某个值
 */
queryWrapper.gt(String column,Object val);
List<User> users = userMapper.selectList(queryWrapper);

                 SQL 语句如下： 

==>  Preparing: SELECT id,name,sex,age,address FROM user WHERE (age > ?)
==> Parameters: 13(Integer)
<==      Total: 0

  10.5， ge() 方法。

                ge( String column,Object val)

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
/**
* 大于等于某个值
*/
queryWrapper.ge(String column,Object val);
List<User> users = userMapper.selectList(queryWrapper);

                 SQL 语句如下： 

==>  Preparing: SELECT id,name,sex,age,address FROM user WHERE (age >= ?)
==> Parameters: 13(Integer)
<==    Columns: id, name, sex, age, address
<==        Row: 7, null, 男, 13, kks
<==        Row: 8, null, 男, 13, kks
<==      Total: 2

  10.6， lt() 方法。

                lt( String column,Object val )

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
/**
* 小于某个值
*/
queryWrapper.lt(String column,Object val);
List<User> users = userMapper.selectList(queryWrapper);

                 SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user WHERE (age < ?)
==> Parameters: 13(Integer)
<==      Total: 0

  10.7， le() 方法。

                le( String column,Object val )

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
/**
* 小于等于某个值
*/
queryWrapper.le(String column,Object val);
List<User> users = userMapper.selectList(queryWrapper);

                 SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user WHERE (age <= ?)
==> Parameters: 13(Integer)
<==    Columns: id, name, sex, age, address
<==        Row: 7, null, 男, 13, kks
<==        Row: 8, null, 男, 13, kks
<==      Total: 2

  10.8， between() 方法。

                between( String column,Object val1,Object val2 )

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
/**
*  闭区间 包含两头的数字，可以链式使用 多个区间
*/
queryWrapper.between(String column,Object val1,Object val2);
List<User> users = userMapper.selectList(queryWrapper);

                 SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user WHERE (age BETWEEN ? AND ?)
==> Parameters: 10(Integer), 13(Integer)
<==    Columns: id, name, sex, age, address
<==        Row: 7, null, 男, 13, kks
<==        Row: 8, null, 男, 13, kks
<==      Total: 2

  10.9， notBetween 方法。

                notBetween( String column,Object val1,Object val2 )

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
/**
*  闭区间 包含两头的数字，可以链式使用 多个区间
*/
queryWrapper.notBetween(String column,Object val1,Object val2);
List<User> users = userMapper.selectList(queryWrapper);

                 SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user WHERE (age NOT BETWEEN ? AND ?)
==> Parameters: 13(Integer), 55(Integer)
<==      Total: 0

  10.10， like() 方法。

                 like( String column,Object val )

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
/**
*  模糊查询 相当于 %XX%
*/
queryWrapper.like(String column,Object val);
List<User> users = userMapper.selectList(queryWrapper);

                 SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user WHERE (address LIKE ?)
==> Parameters: %k%(String)
<==    Columns: id, name, sex, age, address
<==        Row: 7, null, 男, 13, kks
<==        Row: 8, null, 男, 13, kks
<==      Total: 2

  10.11， notLike() 方法。

                notLike( String column,Object val )

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
/**
*  模糊查询 查询没有某个值
*/
queryWrapper.notLike(String column,Object val);
List<User> users = userMapper.selectList(queryWrapper);

                 SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user WHERE (address NOT LIKE ?)
==> Parameters: %k%(String)
<==      Total: 0

  10.12，likeLeft() 方法。

                likeLeft( String column,Object val )

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
/**
*  左模糊查询   相当于 %XX
*/
queryWrapper.likeLeft(String column,Object val);
List<User> users = userMapper.selectList(queryWrapper);

                  SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user WHERE (address LIKE ?)
==> Parameters: %k(String)
<==      Total: 0

  10.13， likeRight() 方法。

                likeRight( String column,Object val )

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
/**
*  右模糊查询   相当于 XX%
*/
queryWrapper.likeRight(String column,Object val);
List<User> users = userMapper.selectList(queryWrapper);

                 SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user WHERE (address LIKE ?)
==> Parameters: k%(String)
<==    Columns: id, name, sex, age, address
<==        Row: 7, null, 男, 13, kks
<==        Row: 8, null, 男, 13, kks
<==      Total: 2

  10.14， isNull() 方法。

                isNull( String column )

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
 /**
*  查询 字段中值为 null 的数据
*/
queryWrapper.isNull(String column);
List<User> users = userMapper.selectList(queryWrapper);

                  SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user WHERE (age IS NULL)
==> Parameters: 
<==    Columns: id, name, sex, age, address
<==        Row: 3, null, 女, null, null
<==      Total: 1

  10.15， isNotNull() 方法。

                isNotNull( String column )

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
/**
*  查询 字段中值不是 null 的数据
*/
queryWrapper.isNotNull(String column);
List<User> users = userMapper.selectList(queryWrapper);

                 SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user WHERE (age IS NOT NULL)
==> Parameters: 
<==    Columns: id, name, sex, age, address
<==        Row: 7, null, 男, 13, kks
<==        Row: 8, null, 男, 13, kks
<==      Total: 2

  10.16， in() 方法。

                in( String column,Object... values )

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.in(String column,Object... values);
List<User> users = userMapper.selectList(queryWrapper);

                in( String column,Collection<?> coll ) 

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
/**
* 第一个参数为 字段名  第二个参数为 集合
*/
queryWrapper.in(String column,Collection<?> coll)
List<User> users = userMapper.selectList(queryWrapper);

                 SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user WHERE (id IN (?,?,?))
==> Parameters: 6(Integer), 7(Integer), 8(Integer)
<==    Columns: id, name, sex, age, address
<==        Row: 7, null, 男, 13, kks
<==        Row: 8, null, 男, 13, kks
<==      Total: 2

  10.17， notIn() 方法。

               notIn( String column,Collection<?> coll ) 

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
/**
* 第一个参数为 字段名  第二个参数为 集合
*/
queryWrapper.notIn(String column,Collection<?> coll)
List<User> users = userMapper.selectList(queryWrapper);

                 notIn( String column,Object... values )

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.notIn(String column,Object... values);
List<User> users = userMapper.selectList(queryWrapper);

                 SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user WHERE (age IN (?,?))
==> Parameters: 7(Integer), 8(Integer)
<==      Total: 0

  10.18， inSql() 方法。

                helpGuanWang

  10.19， notInSql() 方法。

                helpGuanWang

  10.20， groupBy() 方法。

                groupBy( String... columns )

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
/**
*  分组函数 多和 having 一同使用
*/
queryWrapper.groupBy(String... columns);
List<User> users = userMapper.selectList(queryWrapper);

                 SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user GROUP BY age,sex
==> Parameters: 
<==    Columns: id, name, sex, age, address
<==        Row: 3, null, 女, null, null
<==        Row: 7, null, 男, 13, kks
<==      Total: 2

  10.21， orderByAsc() 方法。

                orderbyAsc( String columns )

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
/**
*   升序排序根据  字段的顺序优先级
*/
queryWrapper.orderByAsc(String columns);
List<User> users = userMapper.selectList(queryWrapper);

                 SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user ORDER BY id ASC,age ASC
==> Parameters: 
<==    Columns: id, name, sex, age, address
<==        Row: 3, null, 女, null, null
<==        Row: 7, null, 男, 13, kks
<==        Row: 8, null, 男, 13, kks
<==      Total: 3

  10.22， orderByDesc() 方法。

                orderByDesc( String columns )

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
/**
*   降序排序根据  字段的顺序优先级
*/
queryWrapper.orderByDesc(String columns);
List<User> users = userMapper.selectList(queryWrapper);

                  SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user ORDER BY id DESC,age DESC
==> Parameters: 
<==    Columns: id, name, sex, age, address
<==        Row: 8, null, 男, 13, kks
<==        Row: 7, null, 男, 13, kks
<==        Row: 3, null, 女, null, null
<==      Total: 3

  10.23， orderBy() 方法。

                orderBy( boolean condition,boolean isAsc,Object... columns )

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
/**
*  参数一： 必须为 true 不然不用使用这个方法
*  参数二： true 表示按照 asc 排序 false 表示按照 desc排序
*  参数三： 排序的字段
*/
queryWrapper.orderBy(boolean condition,boolean isAsc,Object... columns);
queryWrapper.orderBy(true, true, "age", "id");
List<User> users = userMapper.selectList(queryWrapper);

                 SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user ORDER BY age ASC,id ASC
==> Parameters: 
<==    Columns: id, name, sex, age, address
<==        Row: 3, null, 女, null, null
<==        Row: 7, null, 男, 13, kks
<==        Row: 8, null, 男, 13, kks
<==      Total: 3

  10.24， having() 方法。

                having( String sqlHaving,Object... params )

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
/**
*  分组函数 多和 having 一同使用  having 第一个参数为 sql 语句 可以用 {0}{1}指定占位符，在
*  后续参数中设置值
*/
queryWrapper.groupBy("age","sex")
     .having("age > {0} and sex = {1}",12,"女");
//  参数
queryWrapper.having(String sqlHaving,Object... params);
List<User> users = userMapper.selectList(queryWrapper);

                 SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user GROUP BY age,sex HAVING age > ? and sex = ?
==> Parameters: 12(Integer), 女(String)
<==      Total: 0

  10.25， or() 方法。

                helpGuanWang

  10.26， last() 方法。

                last( String lastSql )

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
/**
* 只能调用一次,多次调用以最后一次为准 有sql注入的风险,请谨慎使用
*  limit 从下标 0 开始 取 一条值
* */
queryWrapper.last(String lastSql);
queryWrapper.last("limit 0,1");

                 SQL 语句如下：

==>  Preparing: SELECT id,name,sex,age,address FROM user limit 0,1
==> Parameters: 
<==    Columns: id, name, sex, age, address
<==        Row: 3, null, 女, null, null
<==      Total: 1

  10.27， helpGuanWang 。