BeetlSql 单表查询工具(Query)使用说明

Stella981
• 阅读 1050

本文转自BeetlSQL开发者 Gavin-King的博客

在实际应用场景中大部分时候是在针对单表进行操作,单独的写一条单表操作的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).lamdba()
    .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();
点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
待兔 待兔
4个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Easter79 Easter79
3年前
sql注入
反引号是个比较特别的字符,下面记录下怎么利用0x00SQL注入反引号可利用在分隔符及注释作用,不过使用范围只于表名、数据库名、字段名、起别名这些场景,下面具体说下1)表名payload:select\from\users\whereuser\_id1limit0,1;!(https://o
Stella981 Stella981
3年前
BeetlSql 单表操作工具(Query)使用说明
BeetlSql单表查询工具(Query)使用说明在实际应用场景中大部分时候是在针对单表进行操作,单独的写一条单表操作的SQL较为繁琐,为了能进行高效、快捷、优雅的进行单表操作,Query查询器诞生了。Query使用方式和风格介绍我们以一个User表为例,查询
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Stella981 Stella981
3年前
Hibernate中get()和load()的区别
Hibernate中根据Id单条查询获取对象的方式有两种,分别是get()和load(),来看一下这两种方式的区别。1\.get()使用get()来根据ID进行单条查询:Userusersession.get(User.class,"1");当get()方法被调用的时候就会立即发出SQL语句:Hiberna
Wesley13 Wesley13
3年前
Oracle一张表中实现对一个字段不同值和总值的统计(多个count)
需求:统计WAIT\_ORDER表中的工单总数、未处理工单总数、已完成工单总数、未完成工单总数。表结构:为了举例子方便,WAIT\_ORDER表只有两个字段,分别是ID、STATUS,其中STATUS为工单的状态。1表示未处理,2表示已完成,3表示未完成总数。 SQL:  1.SELECT   2
Wesley13 Wesley13
3年前
ThinkPHP 根据关联数据查询 hasWhere 的使用实例
很多时候,模型关联后需要根据关联的模型做查询。场景:广告表(ad),广告类型表(ad\_type),现在需要筛选出广告类型表中id字段为1且广告表中status为1的列表先看关联的设置部分 publicfunctionadType(){return$thisbelongsTo('A
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究