UtilsSql.java
4.1 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
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
package com.taover.repository;
import java.util.HashMap;
import java.util.Map;
import org.springframework.util.StringUtils;
public class UtilsSql {
public static String[] splitCoreSql(String coreSql) throws Exception {
//去除''内的信息
String coreSqlRemoveQuate = replaceSelectAndFromBetweenTopCornerMark(coreSql.toUpperCase());
int fromIndex = calcFromIndex(coreSqlRemoveQuate);
if(fromIndex > -1) {
return new String[] {coreSql.substring(0, fromIndex), coreSql.substring(fromIndex, coreSql.length())};
}else {
throw new Exception("未找到FROM子句");
}
}
private static int calcFromIndex(String coreSqlUpper) {
//计算位置
int selectSubSqlNum = 0;
int currWindowIndex = 0;
for(currWindowIndex=0; (currWindowIndex+6)<coreSqlUpper.length(); ) {
String currSubStr = coreSqlUpper.substring(currWindowIndex, currWindowIndex+6);
if("SELECT".equals(currSubStr)) {
++selectSubSqlNum;
currWindowIndex += 6;
}else if(currSubStr.startsWith("FROM")) {
if(selectSubSqlNum == 1) {
return currWindowIndex;
}
--selectSubSqlNum;
currWindowIndex += 4;
}
++currWindowIndex;
}
return -1;
}
private static String replaceSelectAndFromBetweenTopCornerMark(String sql) {
StringBuffer result = new StringBuffer();
boolean isBetweenMark = false;
int iMark = 0;
int currIndex = 0;
for(currIndex=0; currIndex<sql.length(); ++currIndex) {
char currChar = sql.charAt(currIndex);
if(currChar == '\'') {
if(!isBetweenMark) {
result.append(sql.substring(iMark, currIndex));
isBetweenMark = true;
}else {
result.append(sql.substring(iMark, currIndex).replaceAll("SELECT", "******").replaceAll("FROM", "****"));
isBetweenMark = false;
}
iMark = currIndex;
}else if(currChar == '\\') {
++currIndex;
}
}
if(iMark > -1) {
result.append(sql.substring(iMark, currIndex));
}
return result.toString();
}
/**
* 获取排序字符串
* @param sort
* @param order
* @param columnPreffix
* @return
*/
public static String getSortCondition(String sort, String order, String columnPreffix){
String underScoreSort = UtilsString.underscoreName(sort).toLowerCase();
if(StringUtils.isEmpty(columnPreffix)){
columnPreffix = "";
}
String sortCondition = " ";
if(!StringUtils.isEmpty(underScoreSort)){
sortCondition = " order by ";
String[] sortArr = underScoreSort.split(",");
if(StringUtils.isEmpty(order)){
sortCondition += columnPreffix+sortArr[0]+" DESC ";
for(int i=1; i<sortArr.length; ++i){
sortCondition += ","+columnPreffix+sortArr[i]+" DESC ";
}
}else{
String[] orderArr = order.split(",");
sortCondition += sortArr[0]+" "+orderArr[0]+" ";
for(int i=1; i<sortArr.length; ++i){
if(i < orderArr.length){
sortCondition = ","+columnPreffix+sortArr[i]+" "+orderArr[i]+" ";
}else{
sortCondition = ","+columnPreffix+sortArr[i]+" DESC ";
}
}
}
}
return sortCondition;
}
/**
* 获取分页
* @param sort
* @param order
* @param columnPreffix
* @return
*/
public static String getLimitCondition(int page, int pageSize){
String sql = "";
if(page < -1){
page = 0;
if(pageSize < 1){
pageSize = 0;
}
sql += " limit "+pageSize*(page-1)+","+pageSize;
}else if(page == -1){
sql = "";
}else{
if(pageSize < 1){
pageSize = 0;
}
sql += " limit "+pageSize*(page-1)+","+pageSize;
}
return sql;
}
/**
* 创建分页返回
* @param page
* @param rows
* @param data
* @return
*/
public static Map<String, Object> createPage(int page, int size, int total, Object data){
Map<String, Object> pageData = new HashMap<String, Object>();
pageData.put("page", page);
pageData.put("rows", data);
pageData.put("size", size);
pageData.put("total", total);
return pageData;
}
public static void main(String[] args) {
try {
String[] data = splitCoreSql("select t,(select * as 'fromCActio', dd as 'seelctsele\'ctd' from t) from www where 223");
System.out.println(data[0]);
System.out.println(data[1]);
} catch (Exception e) {
e.printStackTrace();
}
}
}