BeetlSql 单表查询工具（Query）使用说明
UPDATE操作
- 全量更新 update 方法
- 选择更新 updateSelective 方法
DELETE操作
single查询和unique
- single单条查询
- unique单条查询
COUNT查询
GROUP分组查询和Having子句
分页查询
ORDER BY 排序
page分页查询
方法调用顺序

BeetlSql 单表查询工具（Query）使用说明

在实际应用场景中大部分时候是在针对单表进行操作，单独的写一条单表操作的SQL较为繁琐，为了能进行高效、快捷、优雅的进行单表操作，Query查询器诞生了。

Query使用方式和风格介绍

我们以一个 User表为例，查询模糊查询用户名包含 "t" ，并且delete_time 不为空的数据库，按照id 倒序。

//JDK8 强烈推荐使用！！
LambdaQuery<User> query = sqlManager.query(User.class);
query.createLambdaQuery()
  .andLike(User::getName,"%t%")
  .andIsNotNull(User::getDeleteTime)
  .desc(User::getId).select();
//通用方式，可以会逐渐抛弃
Query<User> query = sqlManager.query(User.class);
List<User> list = query.andLike("name", "%t%")
    .andIsNotNull("delete_time")
    .orderBy("id desc").select();

如果你使用的是Spring框架，并且直接在程序里面注入了UserDao可以更加方便的使用。

//定义好的UserDao类
public interface UserDao extends BaseMapper<User> {
}
//注入对象
@Autowired
private UserDao userDao;
//代码中直接使用userDao查询
userDao.createLambdaQuery()
  .andLike(User::getName,"%t%")
  .andIsNotNull(User::getDeleteTime)
  .desc(User::getId).select();

从上面的例子可以看出，Query是使用链式调用，看起来就像一个完整的sql一般，使用方式遵从用户平时SQL编写习惯，所以用户在使用过程中需遵循SQL格式。

所有的条件列完之后，再调用select（要执行的方法：select，insert，update，count 等等）；

这里有的同学可以看出来，直接使用数据库字段，这样不妥啊！要是重构怎么办。虽然大部分时候建立的数据库字段不会重命名，BeetlSql 还是支持列名重构，代码如下：

List<User> list1  = sql.query(User.class)
    .createLambdaQuery()
    .andEq(User::getName, "hi")
    .orderBy(User::getCreateDate)
    .select();

使用LamdbaQuery 必须使用Java8及以上版本。

为了方便，下面的例子都采用数据库字段的形式进行，示例数据库为MySql；

Query主要操作简介

Query接口分为俩类：

一部分是触发查询和更新操作，api分别是

select 触发查询，返回指定的对象列表
single 触发查询，返回一个对象，如果没有，返回null
unique 触发查询，返回一个对象，如果没有，或者有多个，抛出异常
count 对查询结果集求总数
delete 删除符合条件的结果集
update 全部字段更新，包括更新null值
updateSelective 更新选中的结果集（null不更新）
insert 全部字段插入，包括插入null值
insertSelective 有选择的插入，null不插入

另外一部分是各种条件：

标准sql操作符	and操作	or操作
==,!=	andEq,andNotEq	orEq,orNotEq
>,>=	andGreat,andGreatEq	orGreat,orGreatEq
<,<=	andLess,andLessEq	orLess,orLessEq
LIKE,NOT LIKE	andLike,andNotLike	orLike,orNotLike
IS NULL,IS NOT NULL	andIsNull,andIsNotNull	orIsNull,orIsNotNull
IN,NOT IN	andIn ,andNotIn	orIn ,orNotIn
BETWEEN ,NOT BETWEEN	andBetween,andNotBetween	orBetween,orNotBetween
AND ( …..)	and	or

标准sql	Query方法
限制结果结范围，依赖于不同数据库翻页	limit (默认从1开始，会自动处理)
ORDER BY	orderBy, desc,asc
GROUP BY	groupBy
HAVING	having

需要注意的是limit方法默认从1开始，beetlsql已经自动处理了分页情况，比如在mysql中，limit(1,10)默认是从第一条记录开始查询，标准的mysql语句是limit(0,10);

查询器获取

查询器直接通过 sqlManager 获取，多个sqlManager 可以获取各自的Query。获取查询器时，我们泛型一下我们是针对哪个对象（对应的哪张表）进行的操作。

//JDK8推荐使用
LambdaQuery<User> query = sqlManager.lambdaQuery(User.class);
//普通用法
Query<User> query = sqlManager.query(User.class);

如果你的Dao类继承了BaseMapper可以直接使用Dao类创建Query。

//定义好的UserDao类
public interface UserDao extends BaseMapper<User> {
}
//注入对象
@Autowired
private UserDao userDao;
//代码中直接使用userDao查询
LambdaQuery<User> query = userDao.createLambdaQuery();

SELECT简单的条件查询

我们还是以User为例，我们需要查询这条SQL

SELECT * FROM `user` WHERE `id` BETWEEN 1 AND 1640 AND `name` LIKE '%t%' AND `create_time` IS NOT NULL ORDER BY id desc

直接上代码：

//JDK8 强烈推荐使用！！
LambdaQuery<User> query = sqlManager.lambdaQuery(User.class);
query.createLambdaQuery()
  .andBetween(User::getId,1,1640)
  .andLike(User::getName,"%t%")
  .andIsNotNull(User::getCreateTime)
  .desc(User::getId).select();
//普通用法
Query<User> query = sqlManager.query(User.class);
List<User> list = query.andBetween("id", 1, 1640)
    .andLike("name", "%t%")
    .andIsNotNull("create_time")
    .orderBy("id desc").select();

是不是感觉和写SQL一样爽。

如果我们只要查询其中的几个字段怎么办？比如我只要name和id字段，SQL如下：

SELECT name,id FROM `user`

Query也提供了定制字段的方法，只要传入你需要的字段名即可：

//JDK8 强烈推荐使用！！
LambdaQuery<User> query = sqlManager.lambdaQuery(User.class);
query.createLambdaQuery().select(User::getName,User::getId);
//普通用法
Query<User> query = sqlManager.query(User.class);
List<User> list = query.select("name", "id");

比如时间比较大小：

SELECT name,id FROM `user` WHERE `id` = 1637 AND `create_time` < now() AND `name` = 'test'

//JDK8 强烈推荐使用！！
LambdaQuery<User> query = sqlManager.query(User.class);
query.createLambdaQuery().select(User::getName,User::getId);
//普通用法
Query<User> query = sqlManager.query(User.class);
List<User> list = query.andEq("id", 1637)
    .andLess("create_time", new Date())
    .andEq("name", "test")
    .select("name", "id");

有的同学会说，OR子句怎么用，和AND一样简单：

SELECT * FROM `user` WHERE `name` = 'new name' OR `id` = 1637 limit 0 , 10

LambdaQuery<User> query = sqlManager.query(User.class);
query.createLambdaQuery()
.andEq(User::andName,"new name")
.orEq(User::getId,1637)
.limit(1,10)
.select();

为了兼容其他数据库，这里limit都是统一从1开始哦，后面也会提到。

复杂的条件查询

下面就开始进阶了，要进行一条复杂的条件查询SQL，就要用到 query.condition() 方法，产生一个新的条件，比如我们要查询下面这条SQL

SQL：SELECT * FROM `user` WHERE ( `id` IN( ? , ? , ? ) AND `name` LIKE ? )OR ( `id` = ? )
参数：[1637, 1639, 1640, %t%, 1640]

//建议JDK使用 lambdaQuery 查询
Query<User> query = sqlManager.query(User.class);
List<User> list = query
    .or(query.condition()
    .andIn("id", Arrays.asList(1637, 1639, 1640))
  .andLike("name", "%t%"))
    .or(query.condition().andEq("id", 1640))
    .select();

复杂的条件查询，只需要调用 or() 方法和 and()方法，然后使用 query.condition()生成一个新的条件传入就行；比如下面这条SQL

SQL：SELECT * FROM `user` WHERE ( `id` IN( ? , ? , ? ) AND `name` LIKE ? )AND `id` = ? OR ( `name` = ? )
参数：[1637, 1639, 1640, %t%, 1640, new name2]

Query<User> query = sqlManager.query(User.class);
List<User> list = query
    .and(query.condition()
    .andIn("id", Arrays.asList(1637, 1639, 1640))
    .andLike("name", "%t%"))
    .andEq("id", 1640)
    .or(query.condition().andEq("name","new name2"))
  .select();

查询字段智能处理

健壮的变量

在我们开发中，经常会遇到前端传过来一个搜索条件，后端根据搜索条件进行判断，不为空时加入查询条件。

例如前端一个用户列表，有一个根据用户名进行查询的搜索框，我们一般会这么写。

public User findUser(String userName){
    LambdaQuery<User> query = sqlManager.lambdaQuery(User.class);
    query.andEq(User::getDeleteFlag,0);
    if(StringUtil.isNotEmpty(userName)){
        query.andEq(User::getUserName,userName);
    }
    return query.single();
}

如果有很多个这样的字段查询，这样会显得比较臃肿，beetlsql很好的解决了这个问题。

Query工具中内置了两个过滤值的静态方法，filterEmpty、filterNull，这两个方法返回了一个StrongValue对象。

filterEmpty方法主要作用是，当一个字段为空时不把他加入查询条件，当字段不为空时才加入查询条件。

为空的判断标准：

当字段为String类型，会判断空字符串以及NULL。
当字段为Collection类型以及其子类时，会调用isEmpty方法判断，以及NULL。
当字段为其他对象时，仅仅会判断NULL。

要实现上面的代码，我们只要下面这样写就行了。

public User findUser(String userName){
    LambdaQuery<User> query = sqlManager.lambdaQuery(User.class);
    return query.andEq(User::getDeleteFlag,0)
    .andEq(User::getUserName,Query.filterEmpty(userName))
    .single();
}

如果userName有值的情况下，例如等于"myName"，将会生成下面的语句。

select * from user where delete_flag = 0 and user_name = "myName" limit 0,1

当userName为空字符串或者NULL的时候，user_name 将不会参与条件查询。

select * from user where delete_flag = 0 limit 0,1

filterNull方法的作用也是类似的，但是此方法只会判断对象是否等于NULL

自定义实现

但是业务场景往往是复杂的，BeetSql也提供了非常好的拓展性，我们看下filterNull 的方法实现。

    public static StrongValue filterNull(Object value) {
        return new StrongValue() {
            @Override
            public boolean isEffective() {
                return value != null;
            }
            @Override
            public Object getValue() {
                return value;
            }
        };
    }

这个方法返回了一个StrongValue接口，实现了isEffective和getValue方法，如果isEffective方法返回true的时候表示将 value 加入查询条件,否则不加查询条件。

假如还是上面的场景，但是userName变成了模糊查询，我们可以自定义StrongValue

    public static StrongValue filterLikeEmpty(String value) {
        return new StrongValue() {
            @Override
            public boolean isEffective() {
                return StringUtil.isNotEmpty(value);
            }
            @Override
            public Object getValue() {
                return "%"+value+"%";
            }
        };
    }

查询语句可以变成下面这样：

public User findUser(String userName){
    LambdaQuery<User> query = sqlManager.lambdaQuery(User.class);
    return query.andEq(User::getDeleteFlag,0)
    .andLike(User::getUserName,Query.filterLikeEmpty(userName))
    .single();
}

这样当userName为空时isEffective 为false，userName不加入查询，反之加入查询条件。

生成的查询语句为：

select * from user where delete_flag = 0 and user_name like "%myName%" limit 0,1

INSERT操作

学会条件查询之后，其他操作就简单了，我们看下insert。

全量插入insert 方法

SQL：insert into `user` (`name`,`department_id`,`create_time`) VALUES (?,?,?)
参数：[new name, null, null]

    User record = new User();
    record.setName("new name");
    Query<User> query = sqlManager.query(User.class);
    int count = query.insert(record);

全量插入，会对所有的值进行插入，即使这个值是NULL；返回影响的行数；

选择插入insertSelective方法

SQL： insert into `user` ( `name`,`create_time` ) VALUES ( ?,? )
参数：[new name2, now()]

User record = new User();
record.setName("new name2");
record.setCreateTime(new Date());
Query<User> query = sqlManager.query(User.class);
int count = query.insertSelective(record);

insertSelective方法，对user进行了一次有选择性的插入。NULL值的字段不插入；返回影响的行数；

UPDATE操作

update和insert类似,有全量更新和选择更新的方法；

全量更新 update 方法

SQL：update `user` set `name`=?,`department_id`=?,`create_time`=? WHERE `id` = ? AND `create_time` < ? AND `name` = ? 
参数：[new name, null, null, 1637, now(), test]

User record = new User();
record.setName("new name");
Query<User> query = sqlManager.query(User.class);
int count = query.andEq("id", 1637)
    .andLess("create_time", new Date())
    .andEq("name", "test")
    .update(record);

全量更新，会对所有的值进行更新，即使这个值是NULL；返回影响的行数；

选择更新 updateSelective 方法

SQL：update `user` set `name`=? WHERE `id` = ? AND `create_time` < ? AND `name` = ? 
参数：[new name, 1637, now(), test]

User record = new User();
record.setName("new name");
Query<User> query = sqlManager.query(User.class);
int count = query.andEq("id", 1637)
    .andLess("create_time", new Date())
    .andEq("name", "test")
    .updateSelective(record);

updateSelective方法，对user进行了一次有选择性的更新。不是null的值都更新，NULL值不更新；返回影响的行数；

DELETE操作

delete操作非常简单，拼接好条件，调用delete方法即可；返回影响的行数。

DELETE FROM `user` WHERE `id` = ?

Query<User> query = sqlManager.query(User.class);
int count = query.andEq("id", 1642).delete();

single查询和unique

在beetlSql中还提供了两个用来查询单条数据的方法，single和unique；

single单条查询

single查询，查询出一条，如果没有，返回null；

SELECT * FROM `user` WHERE `id` = 1642 limit 0 , 1

Query<User> query = sqlManager.query(User.class);
User user = query.andEq("id", 1642).single();

unique单条查询

unique查询和single稍微不同，他是查询一条，如果没有或者有多条，抛异常；

SELECT * FROM `user` WHERE `id` = 1642 limit 0 , 2

Query<User> query = sqlManager.query(User.class);
User user = query.andEq("id", 1642).unique();

如果存在多条，或者没有则抛出异常：

org.beetl.sql.core.BeetlSQLException: unique查询，但数据库未找到结果集

COUNT查询

count查询主要用于统计行数，如下面的SQL：

SQL：     SELECT COUNT(1) FROM `user` WHERE `name` = ? OR `id` = ? limit 0 , 10
参数：     [new name, 1637]

Query<User> query = sqlManager.query(User.class);
long count = query.andEq("name", "new name")
             .orEq("id", 1637).limit(1, 10)
             .count();

拼接条件，调用count方法，返回总行数。

GROUP分组查询和Having子句

有时候我们要进行分组查询，如以下SQL：

SELECT * FROM `user` WHERE `id` IN(1637, 1639, 1640 ) GROUP BY name

在BeetlSql中直接拼条件调用group方法，传入字段即可：

Query<User> query = sqlManager.query(User.class);
List<User> list = query
    .andIn("id", Arrays.asList(1637, 1639, 1640))
     .groupBy("name")
    .select();

在分组查询之后，我们可能还要进行having筛选，只需要在后面调用having方法，传入条件即可。

SELECT * FROM `user` WHERE `id` IN( 1637, 1639, 1640 ) GROUP BY name HAVING `create_time` IS NOT NULL

Query<User> query = sqlManager.query(User.class);
List<User> list = query
    .andIn("id", Arrays.asList(1637, 1639, 1640))
    .groupBy("name")
    .having(query.condition().andIsNotNull("create_time"))
    .select();

分页查询

分页查询是我们经常要使用的功能，beetlSql支持多数据，会自动适配当前数据库生成分页语句，在beeltSql中调用limit方法进行分页。如下面的SQL：

SQL： SELECT * FROM `user` WHERE `name` = ? OR `id` = ? limit 0 , 10
参数： [new name, 1637]

User record = new User();
record.setName("new name");
Query<User> query = sqlManager.query(User.class);
long count = query.andEq("name", "new name")
    .orEq("id", 1637)
    .limit(1, 10)
    .select();

这里需要注意，limit方法传入的参数是开始行，和查询的行数。（开始行从1开始计数），beetlSql会根据不同的数据生成相应的SQL语句。

ORDER BY 排序

进行排序查询时，只要调用orderBy方法，传入要排序的字段以及排序方式即可。

SQL： SELECT * FROM `user` WHERE `id` BETWEEN ? AND ? AND `name` LIKE ? AND `create_time` IS NOT NULL ORDER BY id desc 
参数： [1, 1640, %t%]

Query<User> query = sqlManager.query(User.class);
List<User> list = query.andBetween("id", 1, 1640)
    .andLike("name", "%t%")
    .andIsNotNull("create_time")
    .orderBy("id desc").select();

page分页查询

如果你想直接使用分页查询同时获得总行数，可以在最后调用page方法，返回一个PageQuery对象。注意page，与select一样，放在末尾调用，不能重复调用select，page，update，delete之类的哦使用方法如下：

blogDao.createLambdaQuery().page(pageNumber, pageSize);
//如下面的完整示例
public PageQuery<Blog> pageBlog(long pageNumber, long pageSize) {
        LambdaQuery<Blog> query = blogDao.createLambdaQuery()
                .andEq(Blog::getDeleteFlag, false);
        return query.page(pageNumber, pageSize);
}

上面的blogDao是集成BaseDao的接口,也可以使用sqlManager.lambdaQuery(Blog.class)创建查询对象。

public interface BlogDao extends BaseDao<Blog> {
}

方法调用顺序

在使用Query工具类时，基本与SQL的使用习惯保持一致，只是select, update, delete, insert作为执行操作将会最后一个方法执行。

方法调用顺序应保持为 and ,or(大于、小于、等于、LIKE、BETWEEN、IN、NOT IN等) —> groupBy —> having—> orderBy(desc,asc 等价，只能用一种 ) —> limit(page等价，只能用一种) —> select(update, delete, insert，page等价)