jfinal中虽然使用enjoy可以很好的实现动态sql,但是sql模板有一些不太好使用的地方。
1.无法格式化。 因为没有对应的工具插件,所有不能识别enjoy的语法,但是自己又习惯了每次写完就格式化一下,然后就很乱,看起来很费劲。
2.sql模板写多了之后查找麻烦 虽然可以分多文件来区分不同模块的sql模板,但是在看代码或者debug的时候,经常要看着namespace+sql名来找到对应的sql片段。个人觉得不是很方便。因为mybatis有很好的插件,点击就能跳转到对应的sql片段上。此处安利一下一个插件:MyBatisCodeHelper-Pro 很好用的插件。
通过以上两点不方便之处,还是觉得写一个动态生成sql语句的java类来实现此需求。代码如下
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* 动态sql工具类
*/
public class SqlBuilder {
private StringBuilder sql;
private String tableName;
private List<Object> params;
private SqlBuilder() {
}
public static SqlBuilder create(String tableName) {
SqlBuilder sqlBuilder = new SqlBuilder();
sqlBuilder.tableName = tableName;
sqlBuilder.sql = new StringBuilder();
return sqlBuilder;
}
public SqlBuilder select(String... columns) {
this.sql.append("select ");
if (columns == null || columns.length == 0) {
this.sql.append("* ");
} else {
boolean isFirst = true;
for (String column : columns) {
if (isFirst) {
this.sql.append(column);
isFirst = false;
} else {
this.sql.append(",").append(column);
}
}
}
sql.append(" from ").append(this.tableName);
return this;
}
public SqlBuilder where(Map<String, Object> conditions) {
if (conditions == null) {
return this;
}
this.params = new ArrayList<>();
boolean isFirst = true;
for (Map.Entry<String, Object> entry : conditions.entrySet()) {
if (isFirst) {
this.sql.append(" where ");
isFirst = false;
} else {
this.sql.append(" and ");
}
this.sql.append(entry.getKey());
if (entry.getValue() != null) {
Object[] value = (Object[]) entry.getValue();
if (value.length > 1) {
this.sql.append(" in (");
boolean firstVal = true;
for (Object val : value) {
if (firstVal) {
this.sql.append("?");
firstVal = false;
} else {
this.sql.append(",?");
}
this.params.add(val);
}
this.sql.append(")");
} else {
sql.append("=?");
this.params.add(value[0]);
}
}
}
return this;
}
public SqlBuilder orderBy(String... columns) {
if (columns == null || columns.length == 0) {
return this;
}
this.sql.append(" order by ");
boolean isFirst = true;
for (String column : columns) {
if (isFirst) {
this.sql.append(column);
isFirst = false;
} else {
this.sql.append(",").append(column);
}
}
return this;
}
public SqlBuilder desc() {
this.sql.append(" desc");
return this;
}
public SqlBuilder asc() {
this.sql.append(" asc");
return this;
}
public SqlBuilder groupBy(String... groupColumns) {
if (groupColumns == null || groupColumns.length == 0) {
return this;
}
this.sql.append(" group by");
boolean isFirst = true;
for (String groupColumn : groupColumns) {
if (isFirst) {
this.sql.append(groupColumn);
isFirst = false;
} else {
this.sql.append(",").append(groupColumn);
}
}
return this;
}
public String getSql() {
if (this.sql == null || this.sql.length() == 0) {
throw new IllegalStateException("sql不能为空");
}
return sql.toString();
}
public void setSql(String sql) {
this.sql = new StringBuilder(sql);
}
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public Object[] getParams() {
if (params == null || params.size() == 0) {
return new Object[0];
}
return params.toArray();
}
public void setParams(List<Object> params) {
this.params = params;
}
}
用法:
SqlBuilder.create('test').select().where(conditions);