# search-mybatis-plus

**Repository Path**: ifrog/search-mybatis-plus

## Basic Information

- **Project Name**: search-mybatis-plus
- **Description**: 基于Mybatis-plus的SQL查询工具，支持连表查询
- **Primary Language**: Java
- **License**: Apache-2.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 12
- **Forks**: 1
- **Created**: 2021-10-20
- **Last Updated**: 2026-01-27

## Categories & Tags

**Categories**: Uncategorized

**Tags**: Java, mybatis-plus, SQL

## README

#  search-mybatis-plus 查询辅助工具
  开源不易，点个赞吧！\
  最新版本依赖 mybatis-plus-3.5.16 基于 jdk21,如果想使用jdk8版本的可以切换到jdk8分支
## 项目介绍
search-mybatis-plus 是一款查询辅助工具包，以编程的方式模拟SQL语言进行数据查询/更新/删除，无缝集成Mybatis-plus(支持MySql,Oracle,MariaDB,H2,Postgres)，具有以下功能：
- 模拟sql,以lambda方式进行构建SQL查询/更新/删除,无需再写xml文件
- Select中支持Max,Min,Avg等聚合函数,CaseWhen子句,IFNULL函数,CTMFunction自定义函数
- 支持连表查询(left join/inner join/right join/cross join)
- 连表查询支持自连接查询(left join/inner join/right join),自连接适应于SQLQuery/SQLUpdate/SQLDelete
- 支持复杂嵌套查询(可以用Alias方式)
- 支持where多条件构造查询，支持构建CTMCondition自定义函数,支持choose when构造条件
- 支持groupBy,orderBy,having及分页查询
- 支持update语句,以lambda方式构建,支持update join(left join/inner join/right join) order by limit 等语句构建
- 支持delete语句,以lambda方式构建,支持delete join(left join/inner join/right join) order by limit 等语句构建
- 支持子查询(select子查询,from子查询,exist子查询,in子查询等)
- 支持表达式加/减/乘/除/幂/模运算
- 无缝集成mybatis-plus,mapper只需继承QBaseMapper就可使用，对业务无侵入

## 方法介绍(QBaseMapper)
- fetchQuery(SQLQuery) - 根据查询对象获取数据列表，返回对象列表
- fetchQueryInto(SQLQuery,Class) - 根据查询对象获取数据列表，转成对应对象列表
- countQuery(SQLQuery) - 根据查询对应返回对应数据的总条数
- updateQuery(SQLUpdate update) - 执行构建的SQLUpdate,返回影响行数
- deleteQuery(SQLDelete delete) - 执行构建的SQLDelete,返回影响行数
- QDL 工具类包含条件及其他函数的创建引用,例如eq条件 -> QDL.eq()

版本升级信息请查看：https://gitee.com/ifrog/search-mybatis-plus/blob/master/CHANGELOG.md

## 使用
 
 添加 Maven(如果找不到可以用https://repo1.maven.org/maven2/) 依赖或者下载项目执行 "mvn clean install"
 
 ```java
	<groupId>com.baomibing</groupId>
	<artifactId>search-mybatis-plus</artifactId>
	<version>1.0.7</version>
 ```
 * Mapper继承QBaseMapper(必须)
 * 服务实现类集成QDL(推荐),在服务实现类可以调用条件构造
 * 构建SQLQuery
 * 调用QBaseMapper中方法查询数据

<B>具体例子请参考：https://gitee.com/ifrog/search-mybatis-plus-example </B>

### 查询样例1
```java
SQLQuery query = new SQLQuery();
query.selectFrom(SysButton.class).limitOffset(1, 10);
List<ButtonDto> list = buttonMapper.fetchQueryInto(query, ButtonDto.class)
int count = buttonMapper.countQuery(query);
```
对应SQL为
```sql
SELECT * FROM sys_button LIMIT 10, OFFSET 0;
SELECT count(1) FROM sys_button
```
### 查询样例2
```java
SQLQuery q = new SQLQuery();
q.selectDistinct(
    SysMenu::getId,
    SysMenu::getRedirect,
    SysMenu::getBeHidden,
    SysMenu::getMenuName,
    SysMenu::getPriority,
    SysMenu::getIcon,
    SysMenu::getParentId,
    SysResourceApi::getReqUrl)
.from(SysMenu.class)
.leftJoin(SysRoleResource.class, eq(SysRoleResource::getResourceId,SysMenu::getId), eq(SysRoleResource::getResourceType,"MENU"))
.leftJoin(SysRole.class, eq(SysRole::getId,SysRoleResource::getRoleId))
.innerJoin(SysResourceApi.class,eq(SysResourceApi::getResourceId,SysMenu::getId), eq(SysResourceApi::getResourceType,"MENU"))
.where(eq(SysMenu::getMenuType,"BUSINESS"))
.orderByasc(SysMenu::getPriority,SysMenu::getParentId)
.limitOffset(1,10);
List<SysMenu> list = qbaseMapper.fetchQuery(q);
```
生成的SQL为
```sql
SELECT DISTINCT
    sys_menu.id,
    sys_menu.redirect,
    sys_menu.be_hidden,
    sys_menu.menu_name,
    sys_menu.priority,
    sys_menu.icon,
    sys_menu.parent_id,
    sys_resource_api.req_url
FROM
    sys_menu f
    LEFT JOIN sys_role_resource ON (sys_role_resource.resource_id = sys_menu.id AND sys_role_resource.resource_type = 'MENU')
    LEFT JOIN sys_role ON sys_role.id = sys_role_resource.role_id
    INNER JOIN sys_resource_api ON (sys_resource_api.resource_id = sys_menu.id AND sys_resource_api.resource_type = 'MENU')
WHERE
    sys_menu.menu_type = 'BUSINESS'
ORDER BY
    sys_menu.priority,
    sys_menu.parent_id ASC
LIMIT 10 OFFSET 0
```
### 查询样例3
```java
Alias alias = alias(
  sqlQuery().select(
      StudentCourse::getCourseId,
      as(max(StudentCourse::getScore),"max_score"),
      as(min(StudentCourse::getScore),"min_score"))
  .from(StudentCourse.class)
  .groupBy(StudentCourse::getCourseId), "t");

SQLQuery query = sqlQuery();
query.select(
    Course::getName,
    field("t.max_score"), 
    field("t.min_score"), 
    as(alias.Fields(StudentCourse::getCourseId),"id"))
.from(Course.class)
.innerJoin(alias, eq(Course::getId, alias.Fields(StudentCourse::getCourseId)));

List<Course> courses = courseMapper.fetchQueryInto(query, Course.class);
```
生成的SQL为
```sql
SELECT 
    course.`name`,
    t.max_score,
    t.min_score,
    t.`course_id` AS id
FROM course
INNER JOIN 
  (SELECT 
    student_course.`course_id`,
    MAX(student_course.`score`) AS max_score,
    MIN(student_course.`score`) AS min_score
    FROM student_course
    GROUP BY  student_course.`course_id`) AS t
ON course.`id` = t.`course_id`
```
### 自连表查询样例
```java
SQLQuery query = new SQLQuery();
Alias alias1 = new Alias(SysGroup.class, "g1");
Alias alias2 = new Alias(SysGroup.class, "g2");
query.selectDistinct(alias2.Fields(SysGroup::getId, SysGroup::getGroupLevel, SysGroup::getGroupName))
    .from(alias1)
    .leftJoin(alias2, like(alias1.Fields(SysGroup::getParentId), alias2.Fields(SysGroup::getId)), eq(alias2.Fields(SysGroup::getGroupLevel), 1))
    .where(eq(alias1.Fields(SysGroup::getId), "R0101"));
```
生成的SQL为
```sql
SELECT DISTINCT
    g2.`id`,
    g2.`group_level`,
    g2.`group_name`
FROM 
    sys_group AS g1
    LEFT JOIN sys_group AS g2 ON (g1.`parent_id` LIKE CONCAT('%', g2.`id`, '%') AND g2.`group_level` = 1)
WHERE
    g1.`id` = 'R0101'
```
### 更新样例
```java
SQLUpdate sqlUpdate = new SQLUpdate(SysButton.class);
sqlUpdate
    .set(eq(SysButton::getButtonName, "test"), eq(SysButton::getMenuId, "test_menu"))
    .where(eq(SysButton::getId, "1442023718292295681"));
Integer updateCount = buttonMapper.updateQuery(sqlUpdate);
```
生成的SQL为
```sql
UPDATE sys_button 
    SET sys_button.`button_name` = 'test', sys_button.`menu_id` = 'test_menu' 
WHERE sys_button.`id` = '1442023718292295681'
```
### 删除样例
```java
SQLDelete sqlDelete = new SQLDelete();
sqlDelete.from(SysButton.class).where(eq(SysButton::getId, "1442023718292295681"));
Integer deleteCount = buttonMapper.deleteQuery(sqlDelete);
```
生成的SQL为
```sql
DELETE FROM sys_button WHERE sys_button.`id` = '1442023718292295681'
```