SQL语句的另一种写法（1）[单兵作战手册SQL篇]

企业级应用，本质就是对数据的操作，落实到程序上就是对数据库的操作。所以快速便捷的操作数据库是重点，如果能将SQL这种非逻辑话的语句变成对象话的程序，就可以轻松操作数据库了。这里的关键是对SQL语句的字符串拼接。

我们先来看看常见的SQL语句

添删改查（CRUD）
INSERT INTO 表名 (列1, 列2,…) VALUES (值1, 值2,…)
UPDATE 表名 SET 列1 = 值1 WHERE 列2 = 值2
DELETE FROM 表名 WHERE 列2 = 值2
SELECT 列1,… FROM 表名 WHERE 列2 = 值2

我们仔细观察四种语句
INSERT 的格式是 表名、多个列值对 （列值是对应的）。
UPDATE 的格式是 表名、更新的列值对、WHERE子句
DELETE 的格式是 表名、WHERE子句
SELECT 的格式的 表名、显示的列名、WHERE子句
（关于多表联合，以及函数，暂时不考虑）

从这里我们可以看到 WHERE子句 是需要解决的重点
在 WHERE子句 中，我们可以将常见的情况归纳为

public enum CompareType 
{
	GT,// >. 
	LT,// <
	EQ,// =
	UNEQ,// <>
	GTEQ,// >=
	LTEQ,// <=
	LIKE,// like	
	UNLIKE,// not like
	IN,// in
	UNIN,// not in 
	BETWEENAND,// between and
	ORDERBYASC,// order by asc
	ORDERBYDESC,// order by desc
	GROUPBY,// group by
	GROUPBYHAVING// group by having
}

并且我们可以把子句中的规则设定出来

switch (compare) 
{
	case GT:symbol = " ${0} > ${1} ";break;
	case LT: symbol = " ${0} < ${1} ";break;
	case EQ: symbol = " ${0} = ${1} ";break;
	case GTEQ: symbol = " ${0} >= ${1} ";break;
	case LTEQ: symbol = " ${0} <= ${1} ";break;
	case UNEQ: symbol = " ${0} <> ${1} ";break;
	case LIKE: symbol = " ${0} like ${1} ";break;
	case UNLIKE: symbol = " ${0} not like ${1} ";break;
	case IN: symbol = " ${0} in (${1}) ";break;
	case UNIN: symbol = " ${0} not in (${1})";break;
	case BETWEENAND: symbol = " ${0} between ${1} and ${2} ";break;
	case ORDERBYASC: symbol = " order by ${0} asc ";break;
	case ORDERBYDESC: symbol = " order by ${0} desc ";break;
	case GROUPBY: symbol = " group by ${0} ";break;
	case GROUPBYHAVING: symbol = " group by ${0} having ${1}";break;
	default:break;
}

我们还要考虑条件语句之间的逻辑

public enum LogicType 
{
	AND,// and
	OR// or
}

以及对应的规则

switch (logic)
{
	case AND:symbol = " and${0}";break;
	case OR: symbol = " or${0}";break;
}

有了这些基本规则，我们就可以做SQL语句的字符串拼接了

我们将每一个 条件 定义为 一个 SqlCondition

就出现了

public SqlCondition(String column, String value, CompareType compareType)

column 为列名
value 为列值 ORDERBYASC ORDERBYDESC GROUPBY 这三种情况 value 随意
compareType 为 逻辑条件
对应的 分别为：
GT,// >.
LT,// <
EQ,// =
UNEQ,// <>
GTEQ,// >=
LTEQ,// <=
LIKE,// like
UNLIKE,// not like
IN,// in
UNIN,// not in
ORDERBYASC,// order by asc
ORDERBYDESC,// order by desc

public SqlCondition(String column, String[] values, CompareType compareType)

column 为列名
value 为多个列值
compareType 为 逻辑条件
对应的 分别为：
GROUPBY,// group by
BETWEENAND,// between and

public SqlCondition(String column, SqlCondition condition, CompareType compareType)

column 为列名
condition 为 having 后的条件 这里需要嵌套
compareType 为 逻辑条件
对应的 分别为：
GROUPBYHAVING// group by having

public SqlCondition addCondition(LogicType logicType, SqlCondition condition)

logicType 逻辑运算
condition 条件子句
logicType对应的 分别为：
AND,// and
OR// or

剩下的就是做 SQL的拼接了

private String getSingleSqlString()
{
	String sql = "";
	
	String symbol = this.getCompareSymbol(this.compare);
	String value0 = SqlStringEx.toSqlStringByColumnName(column);
	String value1 = values == null?"":(values.size() == 0 ?"":values.get(0));
	String value2 = values == null?"":(values.size() == 1 ?"":values.get(1));
	
	if(CompareType.LIKE == this.compare || CompareType.UNLIKE == this.compare)
	{
		value1 = SqlStringEx.toSqlLikeString(value1);
		value2 = SqlStringEx.toSqlLikeString(value2);
	}
	else if(CompareType.IN == this.compare || CompareType.UNIN == this.compare)
	{
		value1 = SqlStringEx.toSqlStringByMulti(value1);
		value2 = SqlStringEx.toSqlStringByMulti(value2);
	}
	else if(CompareType.EQ == this.compare || CompareType.UNEQ == this.compare)
	{
		value1 = SqlStringEx.toSqlString(value1);
		value2 = SqlStringEx.toSqlString(value2);
	}
	else if(CompareType.BETWEENAND == this.compare)
	{
		if(!StringEx.isEmpty(value1) && StringEx.isEmpty(value2)) value2 = value1;
		value1 = SqlStringEx.toSqlString(value1);
		value2 = SqlStringEx.toSqlString(value2);
	}
	else if(CompareType.GROUPBY == this.compare)
	{
		value1 = SqlStringEx.toSqlStringByColumnName(value1);
		value2 = SqlStringEx.toSqlStringByColumnName(value2);

		if(!StringEx.isEmpty(value1)) value0 += "," + value1;
		if(!StringEx.isEmpty(value2)) value0 +=  "," + value2;
	}
	else if(CompareType.GROUPBYHAVING == this.compare)
	{
		value1 = value1;
	}
	else
	{
		value1 = SqlStringEx.toSqlString(value1);
		value2 = SqlStringEx.toSqlString(value2);
	}
	
	sql = symbol;
	sql = sql.replace("${0}",value0);
	sql = sql.replace("${1}",value1);
	sql = sql.replace("${2}",value2);
	
	return sql;
}	

/**
 * 转换成列名格式
 * 
 * @param columnname
 * @return `文本`
 */
public static String toSqlStringByColumnName(String columnname) 
{
	if(StringEx.isEmpty(columnname)) return "";
	if(columnname.contains("`"))return columnname;

	String[] columeNameA = columnname.split("\\.");
	if(columeNameA.length == 2) 
		columnname = columeNameA[0].trim() + "." + "`" + columeNameA[1].trim() + "`";
	else
		columnname = "`" + columnname.trim() + "`";
	return columnname;
}

/**
* String加引号加%
 * 
 * @param obj
 * @return %文本%
 */
public static String toSqlLikeString(String obj) 
{
	obj = obj.replace("'", "\"");
	obj = obj.replace(" ", "%");
	return "'%" + obj.trim() + "%'";
}

有了主体代码，我们就可以让我们的 WHERE子句 对象化了
我们来看看运行的结果

String[] times = new String[2];
times[0] = "createtime";
times[1] = "expiretime";

SqlCondition condition = new SqlCondition();

SqlCondition condition1 = new SqlCondition("id", "^id1,id2",CompareType.IN);
SqlCondition condition2 = new SqlCondition("id2","", CompareType.ORDERBYDESC);
SqlCondition condition3 = new SqlCondition("id1","", CompareType.ORDERBYASC);
SqlCondition condition4 = new SqlCondition("name","!马云", CompareType.UNLIKE);
SqlCondition condition5 = new SqlCondition("expiretime", CalendarUtil.getLongString(), CompareType.GT);


condition.addCondition(LogicType.AND, condition1)
		 .addCondition(LogicType.AND, condition2)
		 .addCondition(LogicType.AND, condition3)
		 .addCondition(LogicType.AND, condition4)
		.addCondition(LogicType.AND, condition5);
System.out.println(condition.toSqlString());

结果：
( `expiretime` > '2021-04-04 21:24:30' and`name` like '%马云%' and`id` not in ('id1','id2') )  order by `id1` asc  ,`id2` desc  

SqlCondition condition6 = new SqlCondition("name",new String[]{"name1","name2"}, CompareType.GROUPBY);
		SqlCondition condition7 = new SqlCondition("id","id1", CompareType.GROUPBY);
		SqlCondition condition8 = new SqlCondition("name",new SqlCondition("name","^马云", CompareType.UNLIKE), CompareType.GROUPBYHAVING);


System.out.println(condition6.toSqlString());
System.out.println(condition7.toSqlString());
System.out.println(condition8.toSqlString());

结果：
 group by `name`,`name1`,`name2` 
 group by `id`,`id1` 
 group by `name` having (`name` like '%马云%')

完整代码地址:点击这里