Java客户端自定义SqlBuilder

简介

since 1.15.0

Free Sql builder允许用户通过调用API的方式，在Java程序里自己拼接包含任意表，任意语句组合的SQL语句。使用方式类似基于单表的SelectSqlBuilder等类的用法。按照用途分为两个：

FreeSelectSqlBuilder专为处理查询语句构建
FreeUpdateSqlBuilder专为处理更新语句构建

两者共同的父类是AbstractFreeSqlBuilder。下面的例子多以AbstractFreeSqlBuilder作为测试对象。

由于携程目前也开放用户使用mybatis，Free Sql Builder的做法与mybatis等在程序外部维护单独的SQL文件的做法相比其好处是开发人员可以在同一个上下文编辑SQL语句，不用切换环境，需要的代码量极少，并且调试和构建都非常方便。

Free Sql Builder与mybatis相比，具有以下优势：

支持复杂动态SQL
支持智能消除无效查询表达式
基于Java代码而不是XML
支持内置的in语句，无需for循环
支持按照数据库类型自动添加列名分隔符
支持按照数据库类型自动添加表名分隔符
SqlServer支持from表名后面添加WITH (NOLOCK)

Free Sql Builder与Mybatis做法示例对比

下面以一个简单的查询语句为例，简要对比两种做法。让用户有个直观的印象。

Mybatis SQL拼接说明

<select id="selectJiKeUserWhere" resultType="JiKeUser" parameterType="JiKeUser">
select * from jikeuser
<where>
<if test="userName!=null">
and userName like #{userName}
</if>
<if test="id!=null">
and id =#{id}
</if>
</where>
</select>

同样的功能，使用sql builder的代码为：

SQL与参数分开处理。这是最开始Free Sql Builder的做法，我们目前继续支持。

FreeSelectSqlBuilder query = new FreeSelectSqlBuilder(logicDbName); StatementParameters parameters = new StatementParameters();

query.selectAll().from(TABLE_NAME").where(like("userName").ignoreNull(userName), AND, equal("id").ignoreNull(id));

assertEquals("SELECT * FROM [jikeuser] WITH (NOLOCK) WHERE [id] = ?", query.build());

query.mapWith(JikeUser.class); parameters.set(1, 1);

DalQueryDao dao = new DalQueryDao(logicDbName);
List l = dao.query(query, parameters, new DalHints());
SQL与参数同时处理。这是现在支持的做法，可以让参数设置和拼SQL同时进行。代码更简洁

FreeSelectSqlBuilder query = new FreeSelectSqlBuilder(); StatementParameters parameters = new StatementParameters(); query.with(parameters); query.selectAll().from("jikeuser").where(like("userName", Types.VARCHAR, userName).ignoreNull(), AND, equal("id", Types.INTEGER, id).ignoreNull()); query.mapWith(JikeUser.class);

DalQueryDao dao = new DalQueryDao(logicDbName);
List l = dao.query(query, parameters, new DalHints());

当然两种做法可以在同一个builder实例上混用，只要保证调用顺序和参数顺序一致即可

API介绍

Builder提供大量常用方法以适应各种需求。并提供方便的特性自动适应带条件判断的拼接情况。

基本的拼接SQL片段的方法

public AbstractFreeSqlBuilder append(Object template)：添加字符串
public AbstractFreeSqlBuilder append(Object... templates) ：添加多个字符串或者语句
public AbstractFreeSqlBuilder appendWhen(boolean condition, Object template)：当condition满足时添加字符串或者语句
public AbstractFreeSqlBuilder appendWhen(boolean condition, Object template, Object elseTemplate)：当condition满足时添加template代表的字符串或者语句，否则添加elseTemplate
public AbstractFreeSqlBuilder appendColumn(String columnName)：添加列名
public AbstractFreeSqlBuilder appendColumn(String columnName, String alias)：添加列名，并指定别名
public AbstractFreeSqlBuilder appendTable(String tableName)：添加表名
public AbstractFreeSqlBuilder appendTable(String tableName, String alias)：添加表名，并指定别名
public AbstractFreeSqlBuilder appendExpression(String expression)：添加表达式
public AbstractFreeSqlBuilder appendExpressions(Object...expressions)：以表达式的形式添加多个字符串，或者语句

private static final String template = "template";
private static final String wrappedTemplate = "[template]";
private static final String expression = "count()";
private static final String elseTemplate = "elseTemplate";
private static final String EMPTY = "";
private static final String logicDbName = "dao_test_sqlsvr_tableShard";
private static final String tableName = "dal_client_test";
 
@Test
public void testAppend() {
    AbstractFreeSqlBuilder test = create();
    test.append(template);
    assertEquals(template, test.build());
}
 
@Test
public void testAppendCondition() {
    AbstractFreeSqlBuilder test = create();
    test.appendWhen(true, template);
    assertEquals(template, test.build());
     
    test = create();
    test.appendWhen(false, template);
    assertEquals(EMPTY, test.build());
}
 
@Test
public void testAppendConditionWithElse() {
    AbstractFreeSqlBuilder test = create();
    test.appendWhen(true, template, elseTemplate);
    assertEquals(template, test.build());
     
    test = create();
    test.appendWhen(false, template, elseTemplate);
    assertEquals(elseTemplate, test.build());
}
 
@Test
public void testAppendClause() {
    AbstractFreeSqlBuilder test = create();
    test.append(new Text(template));
    assertEquals(template, test.build());
}
 
@Test
public void testAppendClauseCondition() {
    AbstractFreeSqlBuilder test = create();
    test.appendWhen(true, new Text(template));
    assertEquals(template, test.build());
     
    test = create();
    test.appendWhen(false, new Text(template));
    assertEquals(EMPTY, test.build());
}
 
@Test
public void testAppendClauseConditionWithElse() {
    AbstractFreeSqlBuilder test = create();
    test.appendWhen(true, new Text(template), new Text(elseTemplate));
    assertEquals(template, test.build());
     
    test = create();
    test.appendWhen(false, new Text(template), new Text(elseTemplate));
    assertEquals(elseTemplate, test.build());
}
 
@Test
public void testAppendColumn() {
    AbstractFreeSqlBuilder test = create();
    test.appendColumn(template);
    test.setLogicDbName(logicDbName);
    assertEquals("[" + template + "]", test.build());
     
    test = create();
    test.appendColumn(template, template);
    test.setLogicDbName(logicDbName);
    assertEquals("[" + template + "] AS " + template, test.build());
 
    test = create();
    test.append(column(template).as(template));
    test.setLogicDbName(logicDbName);
    assertEquals("[" + template + "] AS " + template, test.build());
}
 
@Test
public void testAppendTable() {
    String noShardTable = "noShard";
     
    AbstractFreeSqlBuilder test = create();
    test.appendTable(noShardTable);
    test.setLogicDbName(logicDbName);
    test.setHints(new DalHints());
    assertEquals("[" + noShardTable + "]", test.build());
     
    test = create();
    test.appendTable(tableName);
    test.setLogicDbName(logicDbName);
    test.setHints(new DalHints().inTableShard(1));
    assertEquals("[" + tableName + "_1]", test.build());
     
    test = create();
    test.appendTable(tableName, template);
    test.setLogicDbName(logicDbName);
    test.setHints(new DalHints().inTableShard(1));
    assertEquals("[" + tableName + "_1] AS " + template, test.build());
 
    test = create();
    test.append(table(tableName).as(template));
    test.setLogicDbName(logicDbName);
    test.setHints(new DalHints().inTableShard(1));
    assertEquals("[" + tableName + "_1] AS " + template, test.build());
}
 
@Test
public void testBuildMeltdownAtBegining() throws SQLException {
    FreeSelectSqlBuilder test = createTest();
 
    test = createTest();
    test.where(template).ignoreNull(null).and().appendExpression(template).or().appendExpression(template).ignoreNull(null).groupBy(template);
    assertEquals("WHERE template GROUP BY [template]", test.build());
}
 
@Test
public void testAutoMeltdown() throws SQLException {
    AbstractFreeSqlBuilder test = new AbstractFreeSqlBuilder();
    test.appendExpressions(AND).bracket(AND, OR, AND);
    assertEquals("", test.build());
     
    test = new AbstractFreeSqlBuilder();
    test.appendExpressions(template, AND).bracket(AND, OR, AND);
    assertEquals(template, test.build());
     
    test = new AbstractFreeSqlBuilder();
    test.setLogicDbName(logicDbName);
    test.appendExpressions(template, AND).bracket(AND, OR, AND).appendColumn(template);
    assertEquals(template + " " +wrappedTemplate, test.build());
     
    test = new AbstractFreeSqlBuilder();
    test.setLogicDbName(logicDbName);
    test.appendExpressions(template, AND).bracket(AND, OR, AND).appendTable(template);
    assertEquals(template + " " + wrappedTemplate, test.build());
     
    test = new AbstractFreeSqlBuilder();
    test.setLogicDbName(logicDbName);
    test.appendExpressions(template).ignoreNull(null).append(AND).bracket(AND, OR, AND).appendTable(template);
    assertEquals(wrappedTemplate, test.build());
     
    test = new AbstractFreeSqlBuilder();
    test.setLogicDbName(logicDbName);
    test.appendExpressions(template, AND).bracket(AND, OR, AND).appendTable(template).append(AND).append(expression(template)).ignoreNull(null);
    assertEquals(template+ " " + wrappedTemplate, test.build());
     
    test = new AbstractFreeSqlBuilder();
    test.setLogicDbName(logicDbName);
    test.appendExpressions(template, AND).bracket(AND, OR, AND, template).appendTable(template).append(AND).append(expression(template)).ignoreNull(null);
    assertEquals("template AND (template) [template]", test.build());
}

拼接查询相关SQL片段的方法

public AbstractFreeSqlBuilder select(Object... columnNames)：选择多个列名
public AbstractFreeSqlBuilder selectAll()：SELECT *
public AbstractFreeSqlBuilder from(String table)：指定from的表名，如果是Sqlserver会添加”WITH (NOLOCK)“
public AbstractFreeSqlBuilder from(Table table)：指定from的表名，如果是Sqlserver会添加”WITH (NOLOCK)“
public AbstractFreeSqlBuilder where(Object...expressions)：指定where条件，字符串包装为表达式
public AbstractFreeSqlBuilder orderBy(String columnName, boolean ascending)：指定排序字段和排序方式
public AbstractFreeSqlBuilder groupBy(String columnName)：指定group by的字段
public AbstractFreeSqlBuilder groupBy(Clause condition)：指定group by的语句
public AbstractFreeSqlBuilder having(String condition)：指定having条件

特殊方法：

public static Object[] includeAll()：返回 1=1 AND。可用于WHERE后面第一个条件，在其他条件都消除的情况下缺省返回所有记录。
public static Object[] excludeAll()：返回 1<>1 OR。可用于WHERE后面第一个条件，在其他条件都消除的情况下缺省不返回任何记录。

拼接表达式相关SQL片段的方法

public AbstractFreeSqlBuilder leftBracket()：添加左括号
public AbstractFreeSqlBuilder rightBracket()：添加右括号
public AbstractFreeSqlBuilder bracket(Object... expressions)：添加（多个表达式）
public AbstractFreeSqlBuilder and()：添加and
public AbstractFreeSqlBuilder or()：添加or
public AbstractFreeSqlBuilder not()：添加not
public AbstractFreeSqlBuilder and(Object... expressions)：添加多个表达式或语句，之间用and连接
public AbstractFreeSqlBuilder or(Object... expressions)：添加多个表达式或语句，之间用or连接
public AbstractFreeSqlBuilder equal(String columnName)：添加columnName = ？
public AbstractFreeSqlBuilder equal(String columnName, int sqlType, Object value)：添加columnName = ？并指定参数类型和值
public AbstractFreeSqlBuilder notEqual(String columnName)：添加columnName <> ？
public AbstractFreeSqlBuilder notEqual(String columnName, int sqlType, Object value)：添加columnName <> ？并指定参数类型和值
greaterThan，greaterThanEquals，lessThan，lessThanEquals，between，like，notLike，in，notIn，isNull，isNotNull：各种基于列名的表达式

从equal开始的所有表达式即支持只包含名字，拼SQL的形式，也支持同时提供参数名，类型和参数值的形式来简化操作

创建特殊语句

AbstractFreeSqlBuilder里面的

text：创建简单字符串语句
column：创建列语句，在构建时会添加数据库特定的引用分隔符
table：创建表名语句，在构建时会添加数据库特定的引用分隔符，并自动计算表的shard。支持分表情况下，实际表的定位操作

表达式有效性标记

一般来说，只有参数满足一定条件，比如不是null，或者数值在某个正常范围内，该参数相关的SQL片段表达式才能够加入到最终的SQL里面。为了方便，避免写if-else，下列两个方法可以方便的标记当前最后一个添加表达式是否有效。

public AbstractFreeSqlBuilder ignoreNull()：指定当前添加的最后一个表达式是否要包含在最终的拼接结果里。如果表达式里面的value为null，则忽略
public AbstractFreeSqlBuilder ignoreNull(Object value)：指定当前添加的最后一个表达式是否要包含在最终的拼接结果里。如果指定的value为null，则忽略。这种做法的目的是允许判断条件不是表达式本身的参数值
public AbstractFreeSqlBuilder when(Boolean condition指定当前添加的最后一个表达式是否要包含在最终的拼接结果里。如果condition为true，则添加，反之则忽略

AbstractFreeSqlBuilder 的这些方法其实调用的是Expression里面同样的方法。

设置参数

从为了代码可读性和效率方面考虑，用户可以一次append大段的包括多个参数占位符“？”的SQL语句，再为语句中包含的每个参数设置实际值。提供的方法有：

public AbstractFreeSqlBuilder set(String name, Object value, int sqlType)：添加指定名字和类型的参数
public AbstractFreeSqlBuilder setNullable(String name, Object value, int sqlType)：在参数值不为空的情况下添加指定名字和类型的参数
public AbstractFreeSqlBuilder set(boolean condition, String name, Object value, int sqlType)：在condition为true的情况下添加指定名字和类型的参数
public AbstractFreeSqlBuilder setIn(String name, List<?> values, int sqlType)：添加指定名字和类型的in参数
public AbstractFreeSqlBuilder setInNullable(String name, List<?> values, int sqlType)：在参数值不为空的情况下添加指定名字和类型的in参数
public AbstractFreeSqlBuilder setIn(boolean condition, String name, List<?> values, int sqlType)：在condition为true的情况下添加指定名字和类型的in参数

扩展API

为了更加灵活的构建SQL，DAL提供Expressions类，运行直接创建表达式。

public static Expression createColumnExpression(String template, String columnName)：添加自定义的保护列名的表达式
public static Expression expression(boolean condition, String template) ：满足条件时创建表达式，否则返回NULL表达式
public static Clause expression(boolean condition, String template, String elseTemplate)：根据条件返回不同的表达式
leftBracket，rightBracket：左右括号快速引用
public static Clause bracket(Clause... clauses)：批量添加语句并括起来
AND，OR，NOT：操作符引用
equal，notEqual，greaterThan，greaterThanEquals，lessThan，lessThanEquals，between，like，notLike，in，notIn，isNull，isNotNull：各种基于列名的表达式，支持包含和不包含参数值两种做法
NULL：缺省的NULL表达式

SQL拼接说明

自动填充空格

DAL拼接最后的SQL语句的时候，会自动在每个添加的语句间增加空格” “。这样用户无需在提供语句模板的时候为语句间的分隔担心。为了美观和符合惯用法，DAL添加空格时还符合下面的规则：

如果当前语句为括号，无论左右括号，则不自动添加后继空格
如果下一个语句是括号或者逗号–COMMA，则不自动添加后继空格

如果用户还是想绕过这个规则，可以在添加括号或者逗号的前后添加AbstractFreeSqlBuilder .EMPTY。通过为EMPTY增加空格，最终的效果会是绕过上述规则

支持表名，列名

所有参数是列名或表名的方法都会把字符串参数添加为列名和表名。在构建SQL的时候，列名和表名会使用数据库相关的引用符号来包裹原始值。例如：

在SqlServer，表名或列名为someName，则最终构建的为[someName]。

在MySql，表名或列名为someName，则最终构建的为someName。

支持分表

如果逻辑数据库配置了分表策略，并通过支持表名的方法添加表名，则在构建的时候会首先判断对应表名是否属于分表表名，如果是，则根据hints和parameters自动判断分表对应的分片值并构造实际表名。

@Test
public void testAppendTable() {
    String noShardTable = "noShard";
     
    AbstractFreeSqlBuilder test = createDisabled();
    test.appendTable(noShardTable);
    test.setLogicDbName(logicDbName);
    test.setHints(new DalHints());
    assertEquals("[" + noShardTable + "]", test.build());
     
    test = createDisabled();
    test.appendTable(tableName);
    test.setLogicDbName(logicDbName);
    test.setHints(new DalHints().inTableShard(1));
    assertEquals("[" + tableName + "_1]", test.build());
     
    test = createDisabled();
    test.appendTable(tableName, template);
    test.setLogicDbName(logicDbName);
    test.setHints(new DalHints().inTableShard(1));
    assertEquals("[" + tableName + "_1] AS " + template, test.build());
 
    test = createDisabled();
    test.append(table(tableName).as(template));
    test.setLogicDbName(logicDbName);
    test.setHints(new DalHints().inTableShard(1));
    assertEquals("[" + tableName + "_1] AS " + template, test.build());
}

参数自动消除

如果拼接的某些表达式为null，则在构建的时候会按照一定规则消除。这种做法可以避免写繁琐的if-else判断。有助于顺序的描述SQL拼接过程。

支持的方法有上面介绍的ignoreNull和when方法

@Test
public void testAutoMeltdown() throws SQLException {
    AbstractFreeSqlBuilder test = new AbstractFreeSqlBuilder();
    test.appendExpressions(AND).bracket(AND, OR, AND);
    assertEquals("", test.build());
     
    test = new AbstractFreeSqlBuilder();
    test.appendExpressions(template, AND).bracket(AND, OR, AND);
    assertEquals(template, test.build());
     
    test = new AbstractFreeSqlBuilder();
    test.setLogicDbName(logicDbName);
    test.appendExpressions(template, AND).bracket(AND, OR, AND).appendColumn(template);
    assertEquals(template + " " +wrappedTemplate, test.build());
     
    test = new AbstractFreeSqlBuilder();
    test.setLogicDbName(logicDbName);
    test.appendExpressions(template, AND).bracket(AND, OR, AND).appendTable(template);
    assertEquals(template + " " + wrappedTemplate, test.build());
     
    test = new AbstractFreeSqlBuilder();
    test.setLogicDbName(logicDbName);
    test.appendExpressions(template).ignoreNull(null).append(AND).bracket(AND, OR, AND).appendTable(template);
    assertEquals(wrappedTemplate, test.build());
     
    test = new AbstractFreeSqlBuilder();
    test.setLogicDbName(logicDbName);
    test.appendExpressions(template, AND).bracket(AND, OR, AND).appendTable(template).append(AND).append(expression(template)).ignoreNull(null);
    assertEquals(template+ " " + wrappedTemplate, test.build());
     
    test = new AbstractFreeSqlBuilder();
    test.setLogicDbName(logicDbName);
    test.appendExpressions(template, AND).bracket(AND, OR, AND, template).appendTable(template).append(AND).append(expression(template)).ignoreNull(null);


assertEquals("template AND(template)[template]", test.build());

}

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Java客户端自定义SqlBuilder

简介

Free Sql Builder与Mybatis做法示例对比

API介绍

基本的拼接SQL片段的方法

拼接查询相关SQL片段的方法

拼接表达式相关SQL片段的方法

创建特殊语句

表达式有效性标记

设置参数

扩展API

SQL拼接说明

支持表名，列名

支持分表

参数自动消除

Clone this wiki locally

Java客户端 自定义SqlBuilder

简介

Free Sql Builder与Mybatis做法示例对比

API介绍

基本的拼接SQL片段的方法

拼接查询相关SQL片段的方法

拼接表达式相关SQL片段的方法

创建特殊语句

表达式有效性标记

设置参数

扩展API

SQL拼接说明

支持表名，列名

支持分表

参数自动消除

Clone this wiki locally

Java客户端自定义SqlBuilder