# sqlBuilder

**Repository Path**: jslfl/sql-builder

## Basic Information

- **Project Name**: sqlBuilder
- **Description**: 基于真实项目抽离实现的sql语句构造器。特点：
一纯sql字符串处理，生成结果可直接运行，不依赖DAO框架；
二对常用sql函数进行统一封装（并针对各种数据库进行特定实现），可在构造过程中使用函数，而不用考虑数据库类型的差异；
三对sql语句常量值（或查询参数）做了处理，传参可像java代码一样处理（这样可通过编译器进行有限的代码检查）。
- **Primary Language**: Java
- **License**: Apache-2.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 1
- **Created**: 2024-04-10
- **Last Updated**: 2024-04-10

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

【整体介绍】  
========
本工程主要提供了用linq写法来实现构造sql的方法。  
通过本工程构建sql语句，有几个优点：  
1）可对构建的sql语句提供一定的编译期语法检查；  
2）对语句中的常量值，自动做转换；  
3）可针对不同数据库类型，转换正确的sql写法（目前对函数调用还未完全实现）。  

本工程主要有三大类功能：  
一、SqlBuilder下的四个构建器（增删改查）  
在com.southgis.ibase.sql.builder.SqlBuilder下  

二、不同数据库类型常用函数调用处理（类似于Hibernate的Dialect类）  
在com.southgis.ibase.sql.func下，由IExclusiveDatabaseFunc定义统一的调用约定，再由具体类实现调用处理。  
并可借由DatabaseFunc构造使用。  

三、通过spring boot自动配置机制，通过配置扩展新的数据库类型的处理  
在com.southgis.ibase.sql.config下（目前只写了配置类，还未写自动配置加载的spring.factories）。  
并可借由DatabaesUtil辅助使用。  

除此之外，是一些辅助类的封装，可根据实际情况取舍。  

因为此实现是从我们已有项目中抽离出来的，由于时间匆忙，并没有做更完善的处理，也没有整理示例工程，只简单写了如下的几个典型示例。  
如果有大佬看得上的，还请多多提宝贵意见。  


【四种SQL构造器】  
========

# SqlBuilder使用
可使用构造器生成select、update、insert、delete语句，从而防止手工拼sql产生注入问题及不好维护的问题。  
目前仅支持通用性的语句，后续可支持多种不同数据库的sql，比如一些函数调用自动适配等。  
主要用到两个构造器：  
**SqlBuilder**，生成sql语句，同时会涉及到子句项的构造  
**ClauseBuilder**，sql子句构造器，生成计算式、条件、复杂字段等  


## select示例
1、生成的sql语句：  

```sql
select XMMC,XMBH,SSQX from JOB_ST_SBXX where RID=?
```

写法：  

```java
	String sql=SqlBuilder.select()
			.field("XMMC")
			.field("XMBH")
			.field("SSQX")
		.from("JOB_ST_SBXX")
		.whereAs().field("RID").eq("?").toSel() //whereAs的作用是将当前Sql构造器临时转换为条件构造器，并通过toSel返回为sql构造器
		.build();
```

2、生成的sql语句：  

```sql
select s.XMMC,s.XMBH,s.SSQX,b.JDISTRICT from JOB_ST_SBXX s left join JOB_BASE b on b.jid=s.jid where s.RID=?
```

写法：  

```java
	String sql=SqlBuilder.select()
			.from("JOB_ST_SBXX","s")
				.field("XMMC")
				.field("XMBH")
				.field("SSQX")
			.leftJoin("JOB_BASE","b")
				.onAs().field("b", "jid").eq().field("s", "jid").toSel()
				.field("JDISTRICT")  //选择了最近加入表的JDISTRICT字段，这里最新加入的表是b表
			.whereAs().field("s","RID").eq("?").toSel()
			.build();
```

3、生成的sql语句：  

```sql
select t.* from 
(
  select (ROW_NUMBER() OVER(PARTITION BY jss.jid ORDER BY ta.create_time_ DESC)) as row_id,
		ta.name_ as dqjd, jss.jid, jss.rid, jss.xmlx as jtitle,
		(coalesce(jbe.status,'0')) as jhzt,
		(CASE WHEN jss.slsj is null THEN 0 ELSE cast(now() as timestamp) END) as slhys
  from act_ru_task ta
  left join linkdefinitioninfo l on (ta.TASK_DEF_KEY_=l.taskKey) and (ta.PROC_DEF_ID_=l.PROCESSDEFINITIONID)
  inner join job_st_sbxx jss on jss.WFRID=ta.PROC_INST_ID_
  inner join myentrust m on (m.WFRID=jss.BCODE or m.WFRID='#' or m.WFRID is null)
     and (m.DISTRICT=jss.childjdistrict or m.DISTRICT like ('%,' || jss.childjdistrict || ',%'))
     and m.ENTRUSTTYPE='2' and  m.STATUS=1
  where (1=1) and (jbe.status<>99) and (jss.sczt=:sczt)
	and (
		exists (select * from processdefinitioninfo pr where  (pr.RID=ta.PROC_DEF_ID_) and  (pr.AUTOMATICDISPATCH='{"id":5,"exp":"5"}'))
		  or
		exists (select * from linkdefinitioninfo li where  (li.taskKey=ta.TASK_DEF_KEY_) and  (li.PROCESSDEFINITIONID=ta.PROC_DEF_ID_))
	)
) t
where t.row_id=1 order by t.sbsj DESC
```

写法：  

```java
	
	//把上面复杂的语句，从内到外按表拆分，逐步构造，就不容易出错了
	//准备查询表
	Table tblTask=SqlBuilder.createTable("act_ru_task", "ta");
	Table tblLinkInfo=SqlBuilder.createTable("linkdefinitioninfo", "l");
	Table tblSbxx=SqlBuilder.createTable("job_st_sbxx", "jss");
	Table tblExchange=SqlBuilder.createTable("JOB_BUS_EXCHANGE", "jbe");
	Table tblMyEntrust=SqlBuilder.createTable("myentrust", "m");
	Table tblTaskInfo=SqlBuilder.createTable("taskinfo", "tk");
	
	//取当前时间的函数
	ClauseItem funcNow=SqlBuilder.clause().function("now").build();
	
	//判断是否存在流程定义的sql条件
	Table tblProcInfo=SqlBuilder.createTable("processdefinitioninfo", "pr");
	SelectSqlBuilder wProc=SqlBuilder.select().from(tblProcInfo)
			.whereAs().field(tblProcInfo,"RID").eq().field(tblTask,"PROC_DEF_ID_")
					.and().field(tblProcInfo,"AUTOMATICDISPATCH").eq("{\"id\":5,\"exp\":\"5\"}")
					.toSel();
	//判断是否存在环节定义的sql条件
	Table tblLink2=SqlBuilder.createTable("linkdefinitioninfo", "li");
	SelectSqlBuilder wLink=SqlBuilder.select().from(tblLink2)
			.whereAs().field(tblLink2,"taskKey").eq().field(tblTask,"TASK_DEF_KEY_")
					.and().field(tblLink2,"PROCESSDEFINITIONID").eq().field(tblTask,"PROC_DEF_ID_")
					.toSel();
	//构造主要查询
	SelectSqlBuilder s1=SqlBuilder.select().from(tblTask)
			//构建返回行号的字段
			.fieldAs("row_id")
				.rownum(new TableColumn(tblSbxx, "jid", null),
						new TableColumn(tblTask, "create_time_", null), false)
				.toSel()
			.field("name_","dqjd")
			//target切换当前表，并选择其字段
			.target(tblSbxx).field("jid").field("rid").field("xmlx","jtitle")
			//构建如果为null返回指定值的字段
			.fieldAs("jhzt")
					.ifnull(new TableColumn(tblExchange, "status", null), "0")
					.toSel()
			//构建case转换字段
			.fieldAs("slhys")
					.caseBegin().field(tblSbxx,"slsj").isnull().to_cwhen().cthen(0)
						.cast(funcNow, java.sql.Types.TIMESTAMP).to_celse().caseEnd()
					.toSel()
			//连接表
			.leftJoin(tblLinkInfo).onAs()
					.field(tblTask,"TASK_DEF_KEY_").eq(tblLinkInfo,"taskKey")
					.and()
					.field(tblTask,"PROC_DEF_ID_").eq(tblLinkInfo,"PROCESSDEFINITIONID").toSel()
			.innerJoin(tblSbxx).onAs()
					.field(tblSbxx,"WFRID").eq(tblTask,"PROC_INST_ID_").toSel()
			.innerJoin(tblMyEntrust).onAs()
					.groupBegin() //表示到groupEnd之间的构造合并为一组（相当于括号），用group的方式虽然写的有点多余，但结构会更清晰
						.field(tblMyEntrust,"WFRID").eq(tblSbxx,"BCODE")
						.or()
						.field(tblMyEntrust,"WFRID").eq("#")
						.or()
						.field(tblMyEntrust,"WFRID").isnull()
					.groupEnd()
					.and()
					.groupBegin()
						.field(tblMyEntrust,"DISTRICT").eq(tblSbxx,"childjdistrict")
						.or()
						.field(tblMyEntrust,"DISTRICT").like()
							.funcBegin("concat").farg("%,").field(tblSbxx,"childjdistrict").to_farg().farg(",%").funcEnd()
					.groupEnd()
					.and().field(tblMyEntrust,"ENTRUSTTYPE").eq("2")
					.and().field(tblMyEntrust,"STATUS").eq(1).toSel()
			//查询条件
			.whereAs().c(1).eq(1)
				.and()
					.field(tblExchange,"status").ne(99)
				.and()
					.field(tblSbxx,"sczt").eq(":sczt")
				.and()
					.groupBegin()
						.exists(wProc)
					.or()
						.exists(wLink)
					.groupEnd()
			.toSel()
			;
	//将查询做为另一个查询的来源（即子查询）
	Table sqlTable=SqlBuilder.createTable(s1, "t");
	//基于子查询构造最终查询
	SelectSqlBuilder s2=SqlBuilder.select().from(sqlTable).fieldAll()
			.whereAs().field(sqlTable, "row_id").eq(1).toSel()
			.orderBy("sbsj",true);
	
	//要转换到指定类型的sql语句
	//注：在运行环节，如果生成语句给当前库使用，不需要自己构建DatabaseFunc，会默认使用当前主库构造
	//DatabaseFunc func=DatabaseFunc.createInstanceByTypeName(DataSourceType.POSTGRESQL);
	//s2.databaseFunc(func);
	String sql=s2.build();
```

## update示例
1、生成的sql语句：  
```sql
update JOB_TEST_DY set azone='10''22',zt=2,a=extract(year from now())
   where rid='22'
```

写法：  

```java
	//注：在运行环节，如果生成语句给当前库使用，不需要自己构建DatabaseFunc，会默认使用当前主库构造
	//DatabaseFunc func=DatabaseFunc.createInstanceByTypeName(DataSourceType.POSTGRESQL);
	String sql=SqlBuilder.update("JOB_TEST_DY")
				.set("azone","10'22")
				.set("zt",2)
				.setAs("a").funcBegin("year").function("now").to_farg().funcEnd().toUpd()
				.whereAs().field("rid").eq("22").toUpd()
				//.databaseFunc(func)
				.build();
```

2、生成的sql语句（用查询参数）：  

```sql
update JOB_TEST_DY set azone=:a,zt=:b where rid=:id
```

写法：  

```java
	String sql=SqlBuilder.update("JOB_TEST_DY").set("azone",":a").set("zt",":b")
				.whereAs().field("rid").eq(":id").toUpd()
				.build();
```

3、生成的sql语句（在where中添加条件）：  

```sql
update JOB_TEST_DY a set azone=:a,zt=:b where a.rid='22'
```

写法：  

```java
	Table tblUpdate=SqlBuilder.createTable("JOB_TEST_DY","a");
	String sql=SqlBuilder.update(tblUpdate)
				.set("azone",":a").set("zt",":b")
				//直接传入单独构造的子句（与whereAs效果一样，只是写法不同）
				.where(SqlBuilder.clause().field(tblUpdate,"rid").eq("22").build())
				.build();
```


## insert示例
1、生成的sql语句：  

```sql
insert into JOB_TEST_DY(azone,zt) values ('10''22',2)
```

写法：  

```java
	String sql=SqlBuilder.insert().into("JOB_TEST_DY")
				.set("azone","10'22").set("zt",2) //字段赋值与update builder写法一致
				.build();
```

2、生成的sql语句：  

```sql
insert into JOB_TEST_DY(azone,zt) values (:a,:b)
```

写法：  

```java
	String sql=SqlBuilder.insert().into("JOB_TEST_DY")
				.set("azone",":a").set("zt",":b")
				.build();
```

3、生成的sql语句：  

```sql
insert into JOB_TEST_DY(azone,zt) values (:a,:b)
```

写法：  

```java
	String sql=SqlBuilder.insert().into("JOB_TEST_DY")
				.fields("azone","zt").values(":a",":b") //另一种字段赋值的写法
				.build();
```

## delete示例
1、生成的sql语句：  

```sql
delete from JOB_TEST_DY where azone in ('1022','1023','1025') and xzqdm like '53%'
```

写法：  

```java
	Table tbl=SqlBuilder.createTable("JOB_TEST_DY", null);
	String sql=SqlBuilder.delete().from(tbl)
		.where(ClauseBuilder.produce()
			.field("azone").in().collection(new String[] {"1022","1023","1025"})
			.and()
			.field("xzqdm").like("53%")
			.build())
		.build();
```

2、生成的sql语句：  

```sql
delete from JOB_TEST_DY where azone=? and xzqdm<>?
```

写法：  

```java
	String sql=SqlBuilder.delete().from(tbl)
				.whereAs()
						.field("azone").eq().param()
						.and()
						.field("xzqdm").ne("?")
						.toDel()
				.build();
```


# 后续改进  
1、支持更多的通用函数调用  
目前只支持FuncDefine中定义的有限几个通用函数（按通用格式调用，输出sql时会转为正确数据库类型的调用格式）。  
后续根据需要逐步添加更多sql函数支持。处理方式如下：  
1.1 在IExclusiveDatabaseFunc中定义通用调用方式  
1.2 在具体实现类中，按自己数据库要求改为正确的调用格式  
1.3 在FuncDefine中添加实现的函数（方便构建使用）  
1.4 在ClauseBuilder中通过function 或 funcBegin/farg/to_farg/funcEnd使用，也可直接实现一个具体方法名的调用（类似cast、ifnull方法）  
直接封装方法调用，对使用者更方便。如：ClauseBuilder.produce().cast(TableColumn.of("fld"), java.sql.Types.DATE).build();  
转换为postgersql的sql串时，会输出：cast(fld as date)  

用func..调用，直接传方法名，会更灵活，甚至不需要考虑兼容性，直接写当前运行环境数据库的特有sql方法。  
function 是一个方法完成调用；funcBegin开始方法参数单独构造模式，最后以funcEnd收尾。如：  
ClauseBuilder.produce().funcBegin("lower").farg("PUBLIC").funcEnd().build();  
转换为postgersql的sql串时，会输出：lower('PUBLIC')  
或：  
ClauseBuilder.produce().funcBegin("lower").field("fld").to_farg().funcEnd().build();  
转换为postgersql的sql串时，会输出：lower(fld)  


2、在语句构造过程中内嵌子句构造的处理不是很好，要实现更优雅的方式  
目前通过xxxAs()的方式开启子句构造（如：whereAs、onAs、havingAs等），最后通过toSel()/toUpd/toIns()/toDel()返回到对应的sql构造器上。  
这样会增加调用者的记忆负担。如果能实现类似：whereAs()....continueBuilder()来返回到当前sql构造器上，就好很多。