UtilsSql.java
7.13 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
156
157
158
159
160
161
162
163
164
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 += 5;
}
++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) {
String[] testSql = new String[] {
"select actionFrom t,(select * as 'fromCActio', dd as 'seelctsele\'ctd' from t) from www where 223",
"SELECT wxorder_order.refund_way refundWay,wxorder_order.refund_delivery_sn refundDeliverySn, (SELECT sum(wxorder_compensate.`ware_refund_money`) from `wxorder_compensate` where wxorder_compensate.`order_id` = wxorder_order.id) as wareRefundMoney,(SELECT sum(wxorder_compensate.`refund_money`) from `wxorder_compensate` where wxorder_compensate.`order_id` = wxorder_order.id) as refundMoney, wxorder_order.operate_refund_time applyCompensateTime,wxorder_order.refund_instructions refundInstructions, wxorder_order.channel_id channelId, wxorder_channel.name channelName, wxorder_channel.platform_code platformCode, wxorder_order.pre_control_status as refundPreStatus, wxorder_order.ware_id wareId, wxorder_ware.name wareName, wxorder_order.id, wxorder_order.order_sn orderSn, wxorder_order.upload_sn uploadSn, wxorder_order.money_paid moneyPaid, wxorder_order.consignee, wxorder_order.mobile, wxorder_order.province_name provinceName, wxorder_order.city_name cityName, wxorder_order.district_name districtName, wxorder_order.address, wxorder_order.channel_remark channelRemark, wxorder_order.customer_remark customerRemark, wxorder_order.platform_customer_remark platformCustomerRemark, wxorder_order.sender_name senderName, wxorder_order.sender_mobile senderMobile, date_format(wxorder_order.create_time, '%Y-%m-%d %H:%i:%s') createTime, date_format(wxorder_order.real_delivery_time, '%Y-%m-%d %H:%i:%s') realDeliveryTime, wxorder_order.progress_distribute progressDistribute, wxorder_order.progress_delivery progressDelivery, wxorder_order.control_status controlStatus, wxorder_order.express_name expressName, wxorder_order.express_number expressNumber,wxorder_order.shipping_price shippingPrice, wxorder_order.customer_network_name ,(select count(*) from wxorder_compensate WHERE wxorder_compensate.order_id = wxorder_order.id) as compensateCountAll,(select count(*) from wxorder_compensate WHERE wxorder_compensate.order_id = wxorder_order.id and wxorder_compensate.progress_status = 1) as compensateCountDealed ,(select count(*) from wxorder_channel_refund_payment where wxorder_channel_refund_payment.order_id = wxorder_order.id) as channelRefundPaymentStatus,(select count(*) from wxorder_ware_refund_payment where wxorder_ware_refund_payment.order_id = wxorder_order.id) as wareRefundPaymentStatus FROM wxorder_order wxorder_order INNER JOIN wxorder_channel wxorder_channel ON wxorder_order.channel_id=wxorder_channel.id and wxorder_channel.tenant_id=54 INNER JOIN wxorder_ware wxorder_ware ON wxorder_order.ware_id=wxorder_ware.id and wxorder_ware.tenant_id=54 INNER JOIN wxorder_order_goods wxorder_order_goods ON wxorder_order.id=wxorder_order_goods.order_id and wxorder_order_goods.tenant_id=54 WHERE 1=1 AND wxorder_order.tenant_id='54' AND wxorder_order.control_status='4' GROUP BY wxorder_order.id",
"select wdd, fromType from www where 223",
};
try {
for(int i=0; i<testSql.length; ++i) {
String item = testSql[i];
System.out.println("================testSql["+i+"]===============");
String[] data = splitCoreSql(item);
System.out.println(data[0]);
System.out.println(data[1]);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}