SqlBuilderUtils.java
4.46 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
package com.lhcredit.common.utils;
import com.alibaba.fastjson.JSONArray;
import javax.xml.crypto.Data;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;
public class SqlBuilderUtils {
private String from;
private String limit;
private String orderBy;
private boolean countSql=false;
private List<String> where=new ArrayList<>();
private List<String> fields=new ArrayList<>();
public static SqlBuilderUtils of(String from){
SqlBuilderUtils sqlBuilderUtils = new SqlBuilderUtils();
sqlBuilderUtils.from=from;
return sqlBuilderUtils;
}
public static SqlBuilderUtils of(String db,String tb){
SqlBuilderUtils sqlBuilderUtils = new SqlBuilderUtils();
sqlBuilderUtils.from=db+"."+tb;
return sqlBuilderUtils;
}
public SqlBuilderUtils and(String where){
if (StringUtils.isNotEmpty( where)){
this.where.add( where);
}
return this;
}
public SqlBuilderUtils and(String key,String condition,Object data,boolean when){
if (!when){
return this;
}
return and(key,condition,data);
}
public SqlBuilderUtils and(String key,String condition,Object data){
if (StringUtils.isEmpty(key)||StringUtils.isEmpty(condition)||StringUtils.isEmpty( data))return this;
String where = key+" "+condition+" "+(
(data instanceof String)?setStr(data.toString()):
(data instanceof Data ||data instanceof LocalDateTime)?setDate(data.toString()):
data.toString()
);
if (StringUtils.isNotEmpty( where)){
this.where.add( where);
}
return this;
}
public SqlBuilderUtils and(String where,boolean when){
if (StringUtils.isNotEmpty( where)&&when){
this.where.add( where);
}
return this;
}
public SqlBuilderUtils limit(Integer pageNum,Integer pageSize){
this.limit=" limit "+(pageNum-1)*pageSize+","+pageSize;
return this;
}
public static String setStr(String string){
return "'"+string+"'";
}
public static String setDate(Object object){
if (object instanceof Date){
Date date= (Date)object;
return "'"+ DateUtils.parseDateToStr(DateUtils.YYYY_MM_DD_HH_MM_SS,date) +"'";
} else if (object instanceof LocalDateTime) {
DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(DateUtils.YYYY_MM_DD_HH_MM_SS);
LocalDateTime date= (LocalDateTime)object;
String format = date.format(dateTimeFormatter);
return setStr( format);
}else{
return null;
}
}
public SqlBuilderUtils field(String... fields){
if (null!= fields&&fields.length>0){
this.fields.addAll(Arrays.stream(fields).collect(Collectors.toList()));
}
return this;
}
public SqlBuilderUtils orderBy(String orderBy){
this.orderBy=orderBy;
return this;
}
public String sql() {
StringBuilder builder=new StringBuilder();
builder.append("select ");
if (countSql){
builder.append(" count(1) as num ");
}else{
if (fields.size()>0){
builder.append(fields.stream().collect(Collectors.joining(",")));
}else{
builder.append(" * ");
}
}
builder.append(" from ").append(from);
if (where.size()>0){
builder.append(" where ")
.append(where.stream().collect(Collectors.joining(" and ")));
}
if (StringUtils.isNotEmpty(orderBy)){
builder.append(" order by ").append(orderBy);
}
if (StringUtils.isNotEmpty(limit)){
builder.append(limit);
}
return builder.toString();
}
public SqlBuilderUtils where_In(String field,List<String> eid) {
where.add(field+" in ("+eid.stream().map(m->"'"+m+"'").collect(Collectors.joining(","))+")");
return this;
} public SqlBuilderUtils where_In(String field, JSONArray eid) {
where.add(field+" in ("+eid.stream().map(m->"'"+m.toString()+"'").collect(Collectors.joining(","))+")");
return this;
}
public SqlBuilderUtils count(boolean b) {
this.countSql=b;
return this;
}
}