今天小编分享一个动态sql工具，相信大家很多用的是mybaits自带的sql工具，但是小编的公司用的是自己的一套DB框架，所以并不支持动态sql的编写，有时候需要手动拼接，写起来繁琐可读性也差，所以后来有了一套查询的动态sql工具和更新的动态sql工具。主要功能是支持线程安全，链式操作和自动判du空。断
sqlUpdateTools:
public class SQLUpdateSafe {
private ThreadLocal<SQLUpdate> updateLocal = new ThreadLocal<>();
private static SQLUpdateSafe sqlUpdateSafe = new SQLUpdateSafe();
private SQLUpdateSafe() {
}
public static SQLUpdateSafe instance() {
return sqlUpdateSafe;
}
public SQLUpdate find() {
synchronized (this) {
if (Objects.isNull(updateLocal.get())) {
updateLocal.set(new SQLUpdate());
}
}
return updateLocal.get();
}
public void clean() {
updateLocal.remove();
}
}public class SQLUpdate {
private StringBuilder sql;
private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
SQLUpdate() {
}
public SQLUpdate update(String tableName) {
clean();
sql.append(" update ")
.append(tableName+" set ");
return this;
}
private void clean() {
if (sql == null) {
sql = new StringBuilder();
return;
}
if (sql.length() > 0) {
this.sql = new StringBuilder("");
}
}
public SQLUpdate setColumn(String col, Object value) {
String valueString = convert2SqlString(value);
if (Objects.isNull(valueString)) {
return this;
}
sql.append("`" + col + "` ")
.append("= " + valueString + ",");
return this;
}
public SQLUpdate setJsonColumn(String col, Object value) {
if (Objects.isNull(value)) {
return this;
}
if(!(value instanceof String)){
throw new RuntimeException("JsonColumn类型错误");
}
String finalValue="'" + value + "'";
sql.append("`" + col + "` ")
.append("= " + finalValue + ",");
return this;
}
public String toString() {
return "";
}
public String toSql(){
try {
String s = sql.toString();
boolean b = s.contains("where");
if(!b){
throw new RuntimeException("请添加where条件");
}
int where = sql.lastIndexOf("where");
String substring = sql.substring(where);
//判断where后面是不是只包含1=1
if(!substring.contains("and")){
throw new RuntimeException("请添加and条件");
}
String converSql=sql.replace(where-2,where-1," ").toString();
return converSql;
}finally {
clean();
}
}
private String convert2SqlString(Object value) {
String tmpSql = null;
if (Objects.isNull(value)) {
return tmpSql;
}
if (value instanceof Integer) {
tmpSql = "" + (Integer) value;
} else if (value instanceof Long) {
tmpSql = "" + (Long) value;
} else if (value instanceof Double) {
tmpSql = "" + (Double) value;
} else if (value instanceof String) {
tmpSql = "\"" + (String) value + "\"";
} else if (value instanceof Float) {
tmpSql = "" + (Float) value;
} else if (value instanceof Date) {
Date date = (Date) value;
tmpSql = "\"" + sdf.format(date) + "\"";
} else if (value instanceof BigDecimal) {
tmpSql = "" + (BigDecimal) value;
}
return tmpSql;
}
public SQLUpdate where() {
sql.append(" where 1 = 1 ");
return this;
}
public SQLUpdate and(String col, Object value) {
String valueString = convert2SqlString(value);
if (Objects.isNull(valueString)) {
return this;
}
sql.append(" and `" + col + "` ")
.append("= " + valueString + " ");
return this;
}
public SQLUpdate like(String col, Object value) {
String valueString = convert2SqlString(value);
if (Objects.isNull(valueString)) {
return this;
}
sql.append("and `" + col + "` ")
.append("like " + valueString + " ");
return this;
}
public SQLUpdate in(String column, Collection list) {
if (list == null || list.size() <= 0) {
return this;
}
Object value = list.iterator().next();
if (list.size() == 1) {
return this.and(column, value);
}
Object collect = null;
if (value instanceof String) {
collect = list.stream()
.map(String::valueOf)
.map(s -> "\"" + s + "\"")
.collect(Collectors.joining(",", "(", ")"));
} else if(value instanceof Long){
collect = list.stream()
.map(String::valueOf)
.collect(Collectors.joining(",", "(", ")"));
}else if(value instanceof Integer){
collect = list.stream()
.map(String::valueOf)
.collect(Collectors.joining(",", "(", ")"));
}else {
throw new RuntimeException("当前类型不支持in");
}
sql.append(" and `" + column + "` in " + collect + " ");
return this;
}
public SQLUpdate or(String[] cols, Object[] values, String[] operates) {
if (cols == null || values == null || operates == null
|| cols.length <= 0 || values.length <= 0 || operates.length <= 0) {
return this;
}
sql.append("and (");
Object value = values[0];
for (int index = 0; index < cols.length; index++) {
if (index != 0) {
sql.append("or ");
}
if (value instanceof String) {
sql.append("`" + cols[index] + "` " + operates[index] + " \"" + values[index] + "\" ");
} else {
sql.append("`" + cols[index] + "` " + operates[index] + " " + values[index] + " ");
}
}
sql.append(") ");
return this;
}
public SQLUpdate lt(String col, Object value) {
String valueString = convert2SqlString(value);
if (Objects.isNull(valueString)) {
return this;
}
sql.append("and `" + col + "` ")
.append("< " + valueString + " ");
return this;
}
public SQLUpdate lte(String col, Object value) {
String valueString = convert2SqlString(value);
if (Objects.isNull(valueString)) {
return this;
}
sql.append("and `" + col + "` ")
.append("<= " + valueString + " ");
return this;
}
public SQLUpdate gt(String col, Object value) {
String valueString = convert2SqlString(value);
if (Objects.isNull(valueString)) {
return this;
}
sql.append("and `" + col + "` ")
.append("> " + valueString + " ");
return this;
}
public SQLUpdate gte(String col, Object value) {
String valueString = convert2SqlString(value);
if (Objects.isNull(valueString)) {
return this;
}
sql.append("and `" + col + "` ")
.append(">= " + valueString + " ");
return this;
}
public SQLUpdate between(String col, Object from, Object to) {
if (Objects.isNull(from) || Objects.isNull(to)) {
return this;
}
if (!from.getClass().getTypeName().equals(to.getClass().getTypeName())) {
return this;
}
String fromString = convert2SqlString(from);
String toString = convert2SqlString(to);
if (Objects.isNull(fromString) || Objects.isNull(toString)) {
return this;
}
sql.append("and `" + col + "` ")
.append("between " + fromString + " ")
.append("and " + toString + " ");
return this;
}
public static void main(String[] args) {
String json="{\"buyProds\":[{\"deposit\":\"2000\",\"endTime\":\"2022-06-30\",\"price\":\"10000\",\"prodCount\":\"20\",\"prodName\":\"产品213\",\"remark\":\"123123\",\"startTime\":\"2022-06-01\"}]}";
String s = SQLUpdateSafe.instance().find().update("t_store_compass")
.setColumn("nid", null)
.setColumn("office_count", 10)
.setColumn("name", "select * from table where id=#{id}")
.setColumn("sex", null)
.setColumn("open_time", new Date())
.setColumn("age", null)
.setJsonColumn("json",json)
.setJsonColumn("json1",null)
.where()
.and("id",1)
.in("student",new ArrayList())
.toSql();
System.out.println("case:" + s);
}
}sqlSelectTools:
public class SQLSelectSafe {
private ThreadLocal<SQLSelect> selectLocal = new ThreadLocal<>();
private static SQLSelectSafe sqlSelectSafe = new SQLSelectSafe();
private SQLSelectSafe() {
}
public static SQLSelectSafe instance() {
return sqlSelectSafe;
}
public SQLSelect find() {
synchronized (this) {
if (Objects.isNull(selectLocal.get())) {
selectLocal.set(new SQLSelect());
}
}
return selectLocal.get();
}
public void clean() {
selectLocal.remove();
}
}public class SQLSelect {
private StringBuilder sql;
private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
SQLSelect() {
}
public void clean() {
if (sql == null) {
sql = new StringBuilder();
return;
}
if (sql.length() > 0) {
sql.setLength(0);
}
}
public SQLSelect init() {
if (sql == null) {
sql = new StringBuilder();
return this;
}
sql.setLength(0);
return this;
}
public SQLSelect selectCols(String... colArray) {
if (colArray == null || colArray.length <= 0) {
return this;
}
clean();
String cols = Arrays.stream(colArray).collect(Collectors.joining(","));
sql.append("select ")
.append(cols + " ");
return this;
}
public SQLSelect select() {
clean();
sql.append("select * ");
return this;
}
public SQLSelect selectCnt() {
clean();
sql.append("select count(1) ");
return this;
}
public SQLSelect from(String table) {
sql.append("from ")
.append(table + " ");
return this;
}
public SQLSelect where() {
sql.append(" where 1 = 1 ");
return this;
}
public SQLSelect condition() {
init();
sql.append(" 1 = 1 ");
return this;
}
public SQLSelect and(String col, Object value) {
String valueString = convert2SqlString(value);
if (Objects.isNull(valueString)) {
return this;
}
sql.append(" and `" + col + "` ")
.append("= " + valueString + " ");
return this;
}
public SQLSelect not(String col, Object value) {
String valueString = convert2SqlString(value);
if (Objects.isNull(valueString)) {
return this;
}
sql.append(" and `" + col + "` ")
.append("!= " + valueString + " ");
return this;
}
public SQLSelect like(String col, Object value) {
String valueString = convert2SqlString(value);
if (Objects.isNull(valueString)) {
return this;
}
sql.append("and `" + col + "` ")
.append("like " + valueString + " ");
return this;
}
public SQLSelect like(String col, Object value, boolean leftVague, boolean rightVague) {
if (Objects.isNull(value)) {
return this;
}
if (leftVague) {
value = "%" + value;
}
if (rightVague) {
value = value + "%";
}
String valueString = convert2SqlString(value);
sql.append("and `" + col + "` ")
.append("like " + valueString + " ");
return this;
}
public SQLSelect in(String column, Collection list) {
if (list == null || list.size() <= 0) {
return this;
}
Object value = list.iterator().next();
if (list.size() == 1) {
return this.and(column, value);
}
Object collect = null;
if (value instanceof String) {
collect = list.stream()
.map(String::valueOf)
.map(s -> "\"" + s + "\"")
.collect(Collectors.joining(",", "(", ")"));
} else {
collect = list.stream()
.map(String::valueOf)
.collect(Collectors.joining(",", "(", ")"));
}
sql.append(" and `" + column + "` in " + collect + " ");
return this;
}
public SQLSelect notIn(String column, Collection list) {
if (list == null || list.size() <= 0) {
return this;
}
Object value = list.iterator().next();
if (list.size() == 1) {
return this.not(column, value);
}
Object collect = null;
if (value instanceof String) {
collect = list.stream()
.map(String::valueOf)
.map(s -> "\"" + s + "\"")
.collect(Collectors.joining(",", "(", ")"));
} else {
collect = list.stream()
.map(String::valueOf)
.collect(Collectors.joining(",", "(", ")"));
}
sql.append(" and `" + column + "` not in " + collect + " ");
return this;
}
public SQLSelect or(String[] cols, Object[] values, String[] operates) {
if (cols == null || values == null || operates == null
|| cols.length <= 0 || values.length <= 0 || operates.length <= 0) {
return this;
}
sql.append("and (");
Object value = values[0];
for (int index = 0; index < cols.length; index++) {
if (index != 0) {
sql.append("or ");
}
if (value instanceof String) {
sql.append("`" + cols[index] + "` " + operates[index] + " \"" + values[index] + "\" ");
} else {
sql.append("`" + cols[index] + "` " + operates[index] + " " + values[index] + " ");
}
}
sql.append(") ");
return this;
}
public SQLSelect lt(String col, Object value) {
String valueString = convert2SqlString(value);
if (Objects.isNull(valueString)) {
return this;
}
sql.append("and `" + col + "` ")
.append("< " + valueString + " ");
return this;
}
public SQLSelect lte(String col, Object value) {
String valueString = convert2SqlString(value);
if (Objects.isNull(valueString)) {
return this;
}
sql.append("and `" + col + "` ")
.append("<= " + valueString + " ");
return this;
}
public SQLSelect gt(String col, Object value) {
String valueString = convert2SqlString(value);
if (Objects.isNull(valueString)) {
return this;
}
sql.append("and `" + col + "` ")
.append("> " + valueString + " ");
return this;
}
public SQLSelect gte(String col, Object value) {
String valueString = convert2SqlString(value);
if (Objects.isNull(valueString)) {
return this;
}
sql.append("and `" + col + "` ")
.append(">= " + valueString + " ");
return this;
}
public SQLSelect between(String col, Object from, Object to) {
if (Objects.isNull(from) || Objects.isNull(to)) {
return this;
}
if (!from.getClass().getTypeName().equals(to.getClass().getTypeName())) {
return this;
}
String fromString = convert2SqlString(from);
String toString = convert2SqlString(to);
if (Objects.isNull(fromString) || Objects.isNull(toString)) {
return this;
}
sql.append("and `" + col + "` ")
.append("between " + fromString + " ")
.append("and " + toString + " ");
return this;
}
public SQLSelect group(String groupByCol) {
sql.append(" group by " + groupByCol + " ");
return this;
}
public SQLSelect orderBy(String groupByCol, boolean isDesc) {
String sortBy = isDesc == true ? "desc" : "asc";
if (sql != null && sql.toString().contains("order by")) {
sql.append("," + groupByCol + " " + sortBy + " ");
return this;
}
sql.append("order by ")
.append(groupByCol + " " + sortBy + " ");
return this;
}
public SQLSelect limit(int limit, int offset) {
if (limit <= 0 || offset < 0) {
sql.append("limit 100 offset 0");
return this;
}
if (limit <= 0) {
limit = 100;
} else if (limit > 1000) {
limit = 1000;
}
sql.append(" limit " + limit + " offset " + offset + " ");
return this;
}
public SQLSelect limitByPage(int page, int pageSize) {
if (page <= 0) {
page = 1;
}
if (pageSize <= 0) {
pageSize = 100;
} else if (pageSize > 1000) {
pageSize = 1000;
}
int offset = pageSize * (page - 1);
int limit = pageSize;
sql.append(" limit " + limit + " offset " + offset + " ");
return this;
}
public String toSql() {
String converSql = sql.toString();
clean();
return converSql;
}
private String convert2SqlString(Object value) {
String tmpSql = null;
if (Objects.isNull(value)) {
return tmpSql;
}
if (value instanceof Integer) {
tmpSql = "" + (Integer) value;
} else if (value instanceof Long) {
tmpSql = "" + (Long) value;
} else if (value instanceof Double) {
tmpSql = "" + (Double) value;
} else if (value instanceof String) {
tmpSql = "\"" + (String) value + "\"";
} else if (value instanceof Float) {
tmpSql = "" + (Float) value;
} else if (value instanceof Date) {
Date date = (Date) value;
tmpSql = "\"" + sdf.format(date) + "\"";
} else if (value instanceof BigDecimal) {
tmpSql = "" + (BigDecimal) value;
}
return tmpSql;
}
public static void main(String[] args) {
SQLSelectSafe sqlSelect = SQLSelectSafe.instance();
SQLSelect sql = sqlSelect.find();
String in = sql.condition().and("in", 1).toSql();
System.out.println("normal case -1: " + in);
String toSqlBetween = sql.select()
.from("t_che_info")
.where()
.like("name",1,true,false)
.between("id", null, null)
.between("age", 1, 10)
.between("num", 1, null).toSql();
System.out.println("case between " + toSqlBetween);
// 1. 正常sql
String toSql = sql.select()
.from("t_che_info")
.where()
.and("info_id", 47676958022033L)
.and("error_col_test", null)
.lte("buytime", new Date())
// .limit(1000, 0)
.limitByPage(2, 20)
.toSql();
System.out.println("normal case one: " + toSql);
toSql = sql.select()
.from("t_che_info")
.where()
.lt("id", 11)
.lt("id", "22")
.and("info_id", "47676958022033")
.and("error_col_test", null)
.in("id", Arrays.asList(2312, 231312312))
.in("id", Arrays.asList(1))
.in("id", null)
.in("id", Arrays.asList("2"))
.in("id", Arrays.asList("2312", "231312312"))
.lte("buytime", new Date())
.orderBy("id", false)
.orderBy("buytime", true)
.limit(20, 20)
.toSql();
System.out.println("normal case two: " + toSql);
// 2. group sql
toSql = sql.selectCols("info_id", "count(1) as cnt")
.from("t_che_info")
.where()
.group("info_id")
.limit(100, 0)
.toSql();
System.out.println("group case: " + toSql);
// 3. or sql
String[] cols = new String[]{};
toSql = sql.select()
.from("t_che_info")
.where()
.or(new String[]{"info_id", "user_ip"}, new Object[]{"1001", "111.36.162.20"}, new String[]{"=", "="})
.or(new String[]{"info_id", "user_ip"}, new Object[]{21, 33}, new String[]{"=", "="})
.limit(100, 0)
.toSql();
System.out.println("or case: " + toSql);
toSql = sql.selectCnt()
.from("t_che_info")
.where()
.and("cate_id", 1001)
.and("state", 1)
.and("isadvert", 1)
.and("city_local", 1)
.and("brand", 1)
.and("chexi", 1)
.toSql();
System.out.println("cdb one: " + toSql);
toSql = sql.select()
.from("t_car_infodata")
.where()
.and("model_id", 1001)
.gte("id", 1)
.limit(100, 0)
.toSql();
System.out.println("cdb two: " + toSql);
toSql = sql.select()
.from("t_gate_manager")
.where()
.and("device_code", 1001)
.and("status", 1)
.orderBy("id", true)
.limit(100, 0)
.toSql();
System.out.println("cdb three: " + toSql);
}由于统一判null，入参需要使用包装类。两者第一张图都是线程安全类。
扫一扫
1721
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
