# code_build_sql

**Repository Path**: WYuHua/code_build_sql

## Basic Information

- **Project Name**: code_build_sql
- **Description**: 使用Java代码构建Sql语句，极致轻量化
- **Primary Language**: Java
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 1
- **Forks**: 0
- **Created**: 2024-04-26
- **Last Updated**: 2025-08-28

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# code_build_sql

#### 介绍
这是我开发的一个极致轻量化的SQL查询SDK,可以通过Java代码的方式快速构建出一个SQL语句，解决目前市面上ORM框架对复杂SQL语句支持不友好的情况。同时由于极致的轻量化，因此也不会对项目造成依赖污染。使用方式非常简便，学习成本低。只专注于生成SQL语句，自由度相当高。目前支持Mysql语法，欢迎有心人士一起完善这个项目

#### 软件架构
annotation - 自定义注解包
enums - 枚举包
inter - 接口包
pojo - 实体属性包
utils - 工具包

#### 安装教程

1. 下载该项目的源文件

2. 用idea打开，执行mvn Install 打包

3. 在其他项目中的pom.xml加入依赖

   ~~~xml
   <dependency>
   	<groupId>org.wuyuhua</groupId>
     	<artifactId>code_build_sql</artifactId>
     	<version>1.0-SNAPSHOT</version>
   </dependency>
   ~~~

#### 使用说明

类定义说明：

```java
package org.wuyuhua.test.pojo;

import lombok.Data;
import org.wuyuhua.annotation.TargetField;

@Data
public class TUserDto {
	// TargerField 注解用于标识该属性对应的数据表中的哪个属性 如下标识对应TUser类中的id属性，Tuser类对应的是t_user表,所以userId对应的就是t_user.id
    @TargetField(entity = TUser.class,name = "id")
    private Long userId;
    @TargetField(entity = TUser.class,name = "userName")
    private String username;
    @TargetField(entity = TUser.class,name = "passWord")
    private String password;
    @TargetField(entity = TRole.class,name = "code")
    private String roleCode;
    private String idNo;
}
```

~~~java
package org.wuyuhua.test.pojo;

import lombok.Data;
import javax.persistence.Table;
import java.util.Date;

@Data
@Table(name = "t_user")
public class TUser {

    private Long id;
    private String userName;
    private String passWord;
    private String idNo;
    private Integer age;
    private String sex;
    private Date birth;
}
~~~

```java
package org.wuyuhua.test.pojo;

import lombok.Data;
import javax.persistence.Table;

@Data
@Table(name = "t_user_role")
public class TUserRole {
    private Long id;
    private Long userId;
    private Long roleId;
}
```

```java
package org.wuyuhua.test.pojo;

import lombok.Data;
import javax.persistence.Table;
import java.util.Date;

@Data
@Table(name = "t_role")
public class TRole {

    private Long id;
    private String name;
    private String code;
    private Date createTime;
}
```



1. **QueryEntity.class**

   功能介绍: 这是一条SQL语句的抽象对象，通过Java构建一个QueryEntity对象完成SQL语句的创建

   - 重要方法1: **Select**

     Select方法主要用于构建查询的结果列，组成sql语句中的**select** 部分。方法定义如下：

     ~~~java
     /**
     * 根据对象来构建select
     *
     * @param clazz 对象
     * @return this
     */
     public QueryEntity select(Class<?> clazz);
     
     // example 推荐使用
     public class TestMain {
     
         public static void main(String[] args) {
             QueryEntity queryUser = new QueryEntity()
                     .select(TUserDto.class);
             System.out.println(queryUser.print());
         }
     }
     ~~~

     ```sql
     -- output
     select t_user.id userId,t_user.user_name username,t_user.pass_word password,t_role.code roleCode
     ```

     

     ~~~java
     /**
     * 构建select
     *
     * @param functions 函数列表
     * @param <T>       泛型
     * @return this
     */
     @SafeVarargs
     public final <T> QueryEntity select(CustomFunction<T, ?>... functions);
     
     // example
     public class TestMain {
     
         public static void main(String[] args) {
             QueryEntity queryUser = new QueryEntity()
                     .select(TUser::getId,TUser::getUserName,TUser::getPassWord)
                     .select(TUserDto::getRoleCode);
             System.out.println(queryUser.print());
         }
     }
     ~~~

     ```sql
     -- output
     select t_user.id,t_user.userName,t_user.passWord,t_role.code roleCode
     ```

     

     ~~~java
     /**
     * 通过字符串构建select (由于还没想好函数调用场景如果设计,目前只使用这个字符串方法完成各种各样的需求)
     * @param columns 字符串
     * @return QueryEntity
     */
     public QueryEntity select(String... columns);
     
     // example
     public class TestMain {
         public static void main(String[] args) {
             QueryEntity queryUser = new QueryEntity()
                     .select(TUser.class).select("max(id) as userId");
             System.out.println(queryUser.print());
         }
     }
     ~~~

     ```sql
     -- output
     select t_user.id,t_user.userName,t_user.passWord,t_user.idNo,t_user.age,t_user.sex,t_user.birth,max(id) as userId
     ```

     

     

     ```java
     // 其他可行不推荐的使用案例
     public class TestMain {
     
         public static void main(String[] args) {
             QueryEntity queryUser = new QueryEntity()
                     .select(TUser.class)
                     .select(TRole.class);
             System.out.println(queryUser.print());
         }
     }
     ```

     ```sql
     -- output
     select t_user.id,t_user.userName,t_user.passWord,t_user.idNo,t_user.age,t_user.sex,t_user.birth,t_role.id,t_role.name,t_role.code,t_role.createTime,t_role.code roleCode
     ```

     

     推荐使用@TargetField注解构建一个专用的响应结果返回DTO类，可以灵活选择各个表的各个属性。后续做结果映射也十分方便。

   - 重要方法2：**from**

     From方法用于构建查询的数据表，组成sql语句中的from部分，方法定义如下：

     ~~~java
     /**
     * 使用类型构建查询表 类定义必须有@Table注解或@TableName注解标识绑定的是数据库中的那个表
     * @param clazz 类对象
     * @return QueryEntity
     * @param <T> 泛型
     */
     public <T> QueryEntity from(Class<T> clazz);
     
     //example 1
     public class TestMain {
     
         public static void main(String[] args) {
             QueryEntity queryUser = new QueryEntity()
                     .select(TUser.class)
                     .from(TUser.class);
             System.out.println(queryUser.print());
         }
     }
     
     //example 2
     public class TestMain {
     
         public static void main(String[] args) {
             QueryEntity queryUser = new QueryEntity()
                     .select(TUser.class)
                     .from(TUser.class)
                     .from(TRole.class);
             System.out.println(queryUser.print());
         }
     }
     ~~~

     ```sql
     -- output 1
     select t_user.id,t_user.userName,t_user.passWord,t_user.idNo,t_user.age,t_user.sex,t_user.birth
     from t_use
        
     -- output2
     select t_user.id,t_user.userName,t_user.passWord,t_user.idNo,t_user.age,t_user.sex,t_user.birth
     from t_user,t_role
     ```

     

     from方法还有它的增强方法,leftJoin,rightJoin,用于处理关联查询的场景

     ~~~java
     // leftJoin example
     public class TestMain {
         public static void main(String[] args) {
             QueryEntity queryUser = new QueryEntity()
                     .select(TUser.class)
                     .from(TUser.class)
                     .leftJoin().from(TUserRole.class).on(TUser::getId,TUserRole::getUserId)
                     .leftJoin().from(TRole.class).on(TUserRole::getRoleId,TRole::getId);
             System.out.println(queryUser.print());
         }
     }
     ~~~

     ```sql
     -- output
     select t_user.id,t_user.userName,t_user.passWord,t_user.idNo,t_user.age,t_user.sex,t_user.birth
     from t_user left join t_user_role on (t_user.id != t_user_role.user_id) left join t_role on (t_user_role.role_id != t_role.id)
     ```

     

     ```java
     //rightJoin example
     public class TestMain {
         public static void main(String[] args) {
             QueryEntity queryUser = new QueryEntity()
                     .select(TUser.class)
                     .from(TUser.class)
                     .rightJoin().from(TUserRole.class).on(TUser::getId,TUserRole::getUserId)
                     .rightJoin().from(TRole.class).on(TUserRole::getRoleId,TRole::getId);
             System.out.println(queryUser.print());
         }
     }
     ```

     ```sql
     select t_user.id,t_user.userName,t_user.passWord,t_user.idNo,t_user.age,t_user.sex,t_user.birth
     from t_user right join t_user_role on (t_user.id != t_user_role.user_id) right join t_role on (t_user_role.role_id != t_role.id)
     ```

     

     ```java
     //innerJoin example
     public class TestMain {
         public static void main(String[] args) {
             QueryEntity queryUser = new QueryEntity()
                     .select(TUser.class)
                     .from(TUser.class)
                     .innerJoin().from(TUserRole.class).on(TUser::getId,TUserRole::getUserId)
                     .innerJoin().from(TRole.class).on(TUserRole::getRoleId,TRole::getId);
             System.out.println(queryUser.print());
         }
     }
     ```

     ```sql
     -- output
     select t_user.id,t_user.userName,t_user.passWord,t_user.idNo,t_user.age,t_user.sex,t_user.birth
     from t_user inner join t_user_role on (t_user.id != t_user_role.user_id) inner join t_role on (t_user_role.role_id != t_role.id)
     ```

     

   - 重要方法3： **where**

     Where用来构建sql语句的查询条件，组成sql语句中的where部分，方法定义如下：

     ```java
     /**
     * 通过条件实体构建where
     * @param condition 条件实体
     * @return QueryEntity
     */
     public QueryEntity where(Condition condition);
     
     //example 
     public class TestMain {
         public static void main(String[] args) {
             QueryEntity queryUser = new QueryEntity()
                     .select(TUser.class)
                     .from(TUser.class)
                     .rightJoin().from(TUserRole.class).on(TUser::getId,TUserRole::getUserId)
                     .rightJoin().from(TRole.class).on(TUserRole::getRoleId,TRole::getId)
                     .where(new Condition().eq(TUser::getId,1000));
             System.out.println(queryUser.print());
         }
     }
     ```

     ```sql
     -- output
     select t_user.id,t_user.userName,t_user.passWord,t_user.idNo,t_user.age,t_user.sex,t_user.birth
     from t_user right join t_user_role on (t_user.id != t_user_role.user_id) right join t_role on (t_user_role.role_id != t_role.id)
     where (t_user.id = 1000)
     ```

     > 关于 **Condition**对象的介绍请看下文,此处不作讲解

   - 其他方法：

     groupBy:用于分组条件

     having: 用于分组后的筛选

     orderBy:用于排序

     limit: 用于分页

     ~~~java
     /**
     * 通过函数表达式构建分组
     * @param columns 列名的函数表达式参数
     * @return QueryEntity
     * @param <T> 泛型
     */
     @SafeVarargs
     public final <T> QueryEntity group(CustomFunction<T,?>... columns);
     
     /**
     * 查询对象构建having
     * @param condition 查询对象
     * @return QueryEntity
     * @param <T> 泛型
     */
     public <T> QueryEntity having(Condition condition);
     
     /**
     * 构建 desc 排序
     * @param columns 待排序的列
     * @return QueryEntity
     * @param <T> 泛型
     */
     @SafeVarargs
     public final <T> QueryEntity orderByDesc(CustomFunction<T, ?>... columns);
     
     /**
     * 构建 aes 排序
     * @param columns 待排序的列
     * @return QueryEntity
     * @param <T> 泛型
     */
     @SafeVarargs
     public final <T> QueryEntity orderByAes(CustomFunction<T, ?>... columns);
     
     /**
     * 构建排序
     * @param type 类型 1.aes 2.desc
     * @param columns 待排序的列
     * @return QueryEntity
     * @param <T> 泛型
     */
     @SafeVarargs
     public final <T> QueryEntity orderBy(int type, CustomFunction<T, ?>... columns);
     
     //example
     public class TestMain {
         public static void main(String[] args) {
             QueryEntity queryUser = new QueryEntity()
                     .select(TUser.class)
                     .from(TUser.class)
                     .rightJoin().from(TUserRole.class).on(TUser::getId,TUserRole::getUserId)
                     .rightJoin().from(TRole.class).on(TUserRole::getRoleId,TRole::getId)
                     .where(new Condition().eq(TUser::getId,1000))
                     .group(TUser::getId)
                     .having(new Condition().condition(QueryEnum.EQUALS,"id",10))
                     .orderByDesc(TUser::getId)
                     .limit(1,10);
             System.out.println(queryUser.print());
         }
     }
     ~~~

     ```sql
     -- output
     select t_user.id,t_user.userName,t_user.passWord,t_user.idNo,t_user.age,t_user.sex,t_user.birth
     from t_user right join t_user_role on (t_user.id != t_user_role.user_id) right join t_role on (t_user_role.role_id != t_role.id)
     where (t_user.id = 1000)
     group by t_user.id
     having (id = 10)
     order by t_user.id desc 
     limit 1 10
     ```

   QueryEntity 采用建造者模式，在实际的使用场景中，可以先构建可以复用的QueryEntity对象，再通过预制好的对象构建查询条件满足不同的条件需求。

2. **Condition.class**

   功能介绍: 这是一个查询条件的抽象对象，使用它来构建各种各样的查询条件。它的方法定义如下：

   ~~~java
   public <T> Condition eq(CustomFunction<T,?> left,Object right);
   public <T,R> Condition eq(CustomFunction<T,?> left,CustomFunction<R, ?> right);
   public <T> Condition neq(CustomFunction<T,?> left,Object right);
   public <T,R> Condition neq(CustomFunction<T,?> left,CustomFunction<R, ?> right);
   public <T> Condition lt(CustomFunction<T,?> left,Object right);
   public <T,R> Condition lt(CustomFunction<T,?> left,CustomFunction<R, ?> right);
   public <T> Condition gt(CustomFunction<T,?> left,Object right);
   public <T,R> Condition gt(CustomFunction<T,?> left,CustomFunction<R, ?> right);
   public <T> Condition lte(CustomFunction<T,?> left,Object right);
   public <T,R> Condition lte(CustomFunction<T,?> left,CustomFunction<R, ?> right);
   public <T> Condition gte(CustomFunction<T,?> left,Object right);
   public <T,R> Condition gte(CustomFunction<T,?> left,CustomFunction<R, ?> right);
   public <T> Condition like(CustomFunction<T,?> left,Object right);
   public <T,R> Condition like(CustomFunction<T,?> left,CustomFunction<R, ?> right);
   public <T> Condition rightLike(CustomFunction<T,?> left,Object right);
   public <T,R> Condition rightLike(CustomFunction<T,?> left,CustomFunction<R, ?> right);
   public <T> Condition leftLike(CustomFunction<T,?> left,Object right);
   public <T,R> Condition leftLike(CustomFunction<T,?> left,CustomFunction<R, ?> right);
   public <T> Condition allLike(CustomFunction<T,?> left,Object right);
   public <T,R> Condition allLike(CustomFunction<T,?> left,CustomFunction<R, ?> right);
   public <T> Condition nLike(CustomFunction<T,?> left,Object right);
   public <T,R> Condition nLike(CustomFunction<T,?> left,CustomFunction<R, ?> right);
   public <T> Condition nRightLike(CustomFunction<T,?> left,Object right);
   public <T,R> Condition nRightLike(CustomFunction<T,?> left,CustomFunction<R, ?> right);
   public <T> Condition nLeftLike(CustomFunction<T,?> left,Object right);
   public <T,R> Condition nLeftLike(CustomFunction<T,?> left,CustomFunction<R, ?> right);
   public <T> Condition nAllLike(CustomFunction<T,?> left,Object right);
   public <T,R> Condition nAllLike(CustomFunction<T,?> left,CustomFunction<R, ?> right);
   public <T> Condition isNull(CustomFunction<T,?> left,Object right);
   public <T,R> Condition isNull(CustomFunction<T,?> left,CustomFunction<R, ?> right);
   public <T> Condition notNull(CustomFunction<T,?> left,Object right);
   public <T,R> Condition notNull(CustomFunction<T,?> left,CustomFunction<R, ?> right);
   public <T> Condition isBlank(CustomFunction<T,?> left);
   public <T> Condition notBlank(CustomFunction<T,?> left);
   public <T> Condition betWeen(CustomFunction<T,?> left,Object right1,Object right2);
   @SafeVarargs
   public final <T,R> Condition betWeen(CustomFunction<T, ?> left, CustomFunction<R, ?>... right);
   public <T> Condition notBetWeen(CustomFunction<T,?> left,Object right1,Object right2);
   @SafeVarargs
   public final <T,R> Condition notBetWeen(CustomFunction<T, ?> left, CustomFunction<R, ?>... right)
   public <T> Condition in(CustomFunction<T,?> left,Object... right);
   public <T,E> Condition in(CustomFunction<T,?> left,Collection<E> rights);
   @SafeVarargs
   public final <T,R> Condition in(CustomFunction<T, ?> left, CustomFunction<R, ?>... right)
   public <T> Condition notIn(CustomFunction<T,?> left,Object... right);
   public <T> Condition notIn(CustomFunction<T,?> left,Collection<?> rights);
   @SafeVarargs
   public final <T,R> Condition notIn(CustomFunction<T, ?> left, CustomFunction<R, ?>... right);
   public <T> Condition condition(QueryEnum queryEnum, CustomFunction<T,?> left);
   public <T> Condition condition(QueryEnum queryEnum, String left);
   public <T> Condition condition(QueryEnum queryEnum, String left,Object right);
   public <R> Condition condition(QueryEnum queryEnum,String left,CustomFunction<R, ?> right);
   public Condition condition(QueryEnum queryEnum,String left,Object... right);
   @SafeVarargs
   public final <R> Condition condition(QueryEnum queryEnum, String left, CustomFunction<R, ?>... right);
   public <E> Condition condition(QueryEnum queryEnum, String left, Collection<E> right);
   public <T> Condition condition(QueryEnum queryEnum, CustomFunction<T,?> left, Object right);
   public <T,R> Condition condition(QueryEnum queryEnum,CustomFunction<T,?> left,CustomFunction<R, ?> right);
   public <T> Condition condition(QueryEnum queryEnum,CustomFunction<T,?> left,Object... right);
   @SafeVarargs
   public final <T,R> Condition condition(QueryEnum queryEnum, CustomFunction<T, ?> left, CustomFunction<R, ?>... right);
   public <T,E> Condition condition(QueryEnum queryEnum, CustomFunction<T,?> left, Collection<E> right);
   public Condition child(Condition child);
   public Condition and();
   public Condition or();
   public void next(ConditionNode node);
   
   //example_1 子查询使用示例
   public class TestMain {
       public static void main(String[] args) {
           QueryEntity queryUser = new QueryEntity()
                   .select(TUserDto.class)
                   .from(TUser.class)
                   .where(new Condition().eq(TUser::getId,1));
           Condition condition = new Condition().eq(TUser::getId,queryUser);
           QueryEntity queryEntity = new QueryEntity()
                   .select(TUserDto.class)
                   .from(TUser.class)
                   .leftJoin(TUserRole.class).on(TUser::getId,TUserRole::getUserId)
                   .leftJoin(TRole.class).on(TUserRole::getRoleId,TRole::getId)
                   .where(condition);
           System.out.println(queryEntity.print());
       }
   }
   
   // example_2 多层嵌套
   public class TestMain {
       public static void main(String[] args) {
           Condition condition = new Condition()
                   .eq(TUser::getSex,"男")
                   .child(new Condition().eq(TUser::getUserName,"张三").eq(TUser::getAge,18))
                   .or()
                   .child(new Condition().eq(TUser::getUserName,"李四").eq(TUser::getAge,19).or().neq(TUser::getAge,17));
           QueryEntity queryEntity = new QueryEntity()
                   .select(TUserDto.class)
                   .from(TUser.class)
                   .leftJoin(TUserRole.class).on(TUser::getId,TUserRole::getUserId)
                   .leftJoin(TRole.class).on(TUserRole::getRoleId,TRole::getId)
                   .where(condition);
           System.out.println(queryEntity.print());
       }
   }
   ~~~

   ```sql
   -- output_1 子查询使用示例
   SELECT
   	t_user.id userId,
   	t_user.user_name username,
   	t_user.pass_word PASSWORD,
   	t_role.CODE roleCode 
   FROM
   	t_user
   	LEFT JOIN t_user_role ON ( t_user.id != t_user_role.user_id )
   	LEFT JOIN t_role ON ( t_user_role.role_id != t_role.id ) 
   WHERE
   	(
   		t_user.id = (
   		SELECT
   			t_user.id userId,
   			t_user.user_name username,
   			t_user.pass_word PASSWORD,
   			t_role.CODE roleCode 
   		FROM
   			t_user 
   		WHERE
   			( t_user.id = 1 )
   		)
   	)
   ```

   ```sql
   -- output_2 多层嵌套 
   SELECT
   	t_user.id userId,
   	t_user.user_name username,
   	t_user.pass_word PASSWORD,
   	t_role.CODE roleCode 
   FROM
   	t_user
   	LEFT JOIN t_user_role ON ( t_user.id != t_user_role.user_id )
   	LEFT JOIN t_role ON ( t_user_role.role_id != t_role.id ) 
   WHERE
   	( t_user.sex = "男" ) 
   	AND (
   		(t_user.user_name = "张三" ) AND ( t_user.age = 18 )
   		) 
   	OR  (
   	    (t_user.user_name = "李四" ) AND ( t_user.age = 19 ) OR ( t_user.age != 17)
   	    )
   ```

   > Condition 由 ConditionNode 组成，它有两个属性 定义如下：
   >
   > ```java
   > @Data
   > public class Condition {
   >     private ConditionNode root = null;
   >     private ConditionNode next = null;
   > }
   > ```
   >
   > 实际上这里采用了链表的数据结构，root表示为链表的头节点，next表示当前链表的尾部，链表插入元素时通过尾插法进行插入。
   >
   > ConditionNode定义如下：
   >
   > ```
   > @Data
   > public class ConditionNode {
   > 
   >     private String left = "";
   >     private QueryEnum join = QueryEnum.NULL;
   >     private Collection<Object> right = null;
   >     private ConditionNode child;
   >     private ConditionNode next;
   >     private Boolean isFunction = false;
   >     private int type;
   > }
   > ```
   >
   > left : 表示条件的左边
   >
   > join：表示 连接符号，详情请查看QueryEnum枚举类
   >
   > right: 表示条件的右边，由于可能存在多个元素，因此这里采用的是集合的类型
   >
   > child: 表示当前条件结点的子节点
   >
   > next: 下一个结点
   >
   > isFunction: 用于表示right是否是函数，控制最后的输出时是否需要带引号
   >
   > type: 与下一节点的连接类型 0.and 1.or
   >
   > 举例说明1： (a = b)
   >
   > >left: a
   > >
   > >join: =
   > >
   > >right: b
   > >
   > >isFunction: true
   > >
   > >type: 0
   >
   > 举例说明2： (a = "b") or (a = b)
   >
   > > left: a
   > >
   > > join: =
   > >
   > > right: 'b'
   > >
   > > next: (a = b)
   > >
   > > isFunction: false
   > >
   > > type: 1
   >
   > 举例说明3：((a = "b"))
   >
   > > child: (a = "b")
   > >
   > > type: 0
   >
   > 举例说明4：((a = "b")  or  (a = b))
   >
   > > child: (a = "b")
   > >
   > > child.type : 1
   > >
   > > child.next : (a = b)