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 '%马云%')
完整代码地址:点击这里