BeetlSql 单表查询工具(Query)使用说明
在实际应用场景中大部分时候是在针对单表进行操作,单独的写一条单表操作的SQL较为繁琐,为了能进行高效、快捷、优雅的进行单表操作,Query查询器诞生了。
Query使用方式和风格介绍
我们以一个 User表为例,查询模糊查询用户名包含 "t" ,并且delete_time 不为空的数据库,按照id 倒序。
Query<User> query = sqlManager.query(User.class);
List<User> list = query.andLike("name", "%t%")
.andIsNotNull("delete_time")
.orderBy("id desc").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,且引入了对jaque库依赖,maven引入下面的包
<dependency>
<groupId>com.trigersoft</groupId>
<artifactId>jaque</artifactId>
<version>2.1.2</version>
<scope>provided</scope>
</dependency>
为了方便,下面的例子都采用数据库字段的形式进行,示例数据库为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
andIn ,andNotIn
orIn ,orNotIn
BETWEEN ,NOT BETWEEN
andBetween,andNotBetween
orBetween,orNotBetween
and ( .....)
and
or
标准sql
Query方法
限制结果结范围,依赖于不同数据库翻页
limit
ORDER BY
orderBY
GROUP BY
groupBy
HAVING
having
查询器获取
查询器直接通过 sqlManager 获取,多个sqlManager 可以获取各自 的Query。 获取查询器时,我们泛型一下我们是针对哪个对象(对应的哪张表)进行的操作。
Query<User> query = sqlManager.query(User.class);
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
直接上代码:
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也提供了定制字段的方法,只要传入你需要的字段名即可:
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'
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
query.andEq("name", "new name")
.orEq("id", 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]
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();
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> {
}