# BeetlSql 单表查询工具(Query)使用说明 在实际应用场景中大部分时候是在针对单表进行操作,单独的写一条单表操作的SQL较为繁琐,为了能进行高效、快捷、优雅的进行单表操作,Query查询器诞生了。 ## Query使用方式和风格介绍 我们以一个 User表为例,查询模糊查询用户名包含 "t" ,并且delete_time 不为空的数据库,按照id 倒序。 ```java //JDK8 强烈推荐使用!! LambdaQuery<User> query = sqlManager.lambdaQuery(User.class); query.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`可以更加方便的使用。 ```java //定义好的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.lambdaQuery(User.class) .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。 获取查询器时,我们泛型一下我们是针对哪个对象(对应的哪张表)进行的操作。 ```java //JDK8推荐使用 LambdaQuery<User> query = sqlManager.lambdaQuery(User.class); //普通用法 Query<User> query = sqlManager.query(User.class); ``` 如果你的Dao类继承了`BaseMapper`可以直接使用Dao类创建Query。 ```java //定义好的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 ``` 直接上代码: ```java //JDK8 强烈推荐使用!! LambdaQuery<User> query = sqlManager.lambdaQuery(User.class); query.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也提供了定制字段的方法,只要传入你需要的字段名即可: ```java //JDK8 强烈推荐使用!! LambdaQuery<User> query = sqlManager.lambdaQuery(User.class); query.select(User::getName,User::getId); //普通用法 Query<User> query = sqlManager.query(User.class); List<User> list = query.select("name", "id"); ``` 比如时间比较大小: ```sql SELECT name,id FROM `user` WHERE `id` = 1637 AND `create_time` < now() AND `name` = 'test' ``` ```java //JDK8 强烈推荐使用!! LambdaQuery<User> query = sqlManager.lambdaQuery(User.class); query.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.lambdaQuery(User.class); query.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] ``` ```java //建议JDK8使用 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(); ``` ## 查询字段智能处理 ### 健壮的变量 在我们开发中,经常会遇到前端传过来一个搜索条件,后端根据搜索条件进行判断,不为空时加入查询条件。 例如前端一个用户列表,有一个根据用户名进行查询的搜索框,我们一般会这么写。 ```java 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。 要实现上面的代码,我们只要下面这样写就行了。 ```java 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` 的方法实现。 ```java 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 ```java public static StrongValue filterLikeEmpty(String value) { return new StrongValue() { @Override public boolean isEffective() { return StringUtil.isNotEmpty(value); } @Override public Object getValue() { return "%"+value+"%"; } }; } ``` 查询语句可以变成下面这样: ```java 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之类的哦** 使用方法如下: ```java 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)创建查询对象。 ```java 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等价`)