001/**
002 * Copyright (c) 2015-2022, Michael Yang 杨福海 (fuhai999@gmail.com).
003 * <p>
004 * Licensed under the Apache License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 * <p>
008 * http://www.apache.org/licenses/LICENSE-2.0
009 * <p>
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 */
016package io.jboot.db.model;
017
018import com.jfinal.kit.LogKit;
019import io.jboot.utils.ArrayUtil;
020import io.jboot.utils.StrUtil;
021
022import java.util.List;
023import java.util.regex.Pattern;
024
025/**
026 * @author Michael Yang 杨福海 （fuhai999@gmail.com）
027 * @version V1.0
028 */
029public class SqlBuilder {
030
031    private static final String OR = " OR ";
032    private static final String AND = " AND ";
033
034    public static void buildMysqlWhereSql(StringBuilder sqlBuilder, List<Column> columns) {
035        buildWhereSql(sqlBuilder, columns, '`');
036    }
037
038    public static String forDeleteByColumns(String alias, List<Join> joins, String table, List<Column> columns, char separator) {
039        StringBuilder sqlBuilder = new StringBuilder(45);
040        sqlBuilder.append("DELETE FROM ");
041
042        appendTextWithSeparator(sqlBuilder, table, separator);
043
044        buildAlias(sqlBuilder, alias);
045        buildJoinSql(sqlBuilder, joins, separator);
046        buildWhereSql(sqlBuilder, columns, separator);
047
048        return sqlBuilder.toString();
049    }
050
051
052    public static void buildWhereSql(StringBuilder sqlBuilder, List<Column> columns, char separator) {
053        buildWhereSql(sqlBuilder, columns, separator, true);
054    }
055
056
057    public static void buildWhereSql(StringBuilder sqlBuilder, List<Column> columns, char separator, boolean appendWhereKeyword) {
058        if (ArrayUtil.isNullOrEmpty(columns)) {
059            return;
060        }
061
062        StringBuilder whereSqlBuilder = new StringBuilder();
063        buildByColumns(whereSqlBuilder, columns, separator);
064
065        if (whereSqlBuilder.length() > 0) {
066            if (appendWhereKeyword && !isAllGroupByColumns(columns)) {
067                sqlBuilder.append(" WHERE ");
068            }
069            sqlBuilder.append(whereSqlBuilder);
070        }
071    }
072
073    //fixed: https://gitee.com/JbootProjects/jboot/issues/I3TP7J
074    private static boolean isAllGroupByColumns(List<Column> columns) {
075        for (Column column : columns) {
076            if (!(column instanceof GroupBy)) {
077                return false;
078            }
079        }
080        return true;
081    }
082
083
084    private static void buildByColumns(StringBuilder sqlBuilder, List<Column> columns, char separator) {
085        for (int i = 0; i < columns.size(); i++) {
086
087            Column before = i > 0 ? columns.get(i - 1) : null;
088            Column current = columns.get(i);
089
090            if (current instanceof Or) {
091                continue;
092            }
093            // sqlPart
094            else if (current instanceof SqlPart) {
095                appendSqlPartLogic(sqlBuilder, before, (SqlPart) current, separator);
096            }
097            // group
098            else if (current instanceof Group) {
099                appendGroupLogic(sqlBuilder, before, (Group) current, separator);
100            }
101            // in logic
102            else if (Column.LOGIC_IN.equals(current.getLogic()) || Column.LOGIC_NOT_IN.equals(current.getLogic())) {
103                appendLinkString(sqlBuilder, before);
104                appendInLogic(sqlBuilder, current, separator);
105            }
106            // between logic
107            else if (Column.LOGIC_BETWEEN.equals(current.getLogic()) || Column.LOGIC_NOT_BETWEEN.equals(current.getLogic())) {
108                appendLinkString(sqlBuilder, before);
109                appendBetweenLogic(sqlBuilder, current, separator);
110            }
111            // others
112            else {
113                appendLinkString(sqlBuilder, before);
114                appendColumnName(sqlBuilder, current, separator);
115
116                if (current.hasPara()) {
117                    sqlBuilder.append('?');
118                }
119            }
120        }
121    }
122
123
124    private static void appendSqlPartLogic(StringBuilder sqlBuilder, Column before, SqlPart sqlPart, char separator) {
125        if (!sqlPart.isWithoutLink()) {
126            appendLinkString(sqlBuilder, before);
127        }
128        sqlPart.build(separator);
129        sqlBuilder.append(' ').append(sqlPart.getSql()).append(' ');
130    }
131
132
133    private static void appendColumnName(StringBuilder sqlBuilder, Column column, char separator) {
134        appendTextWithSeparator(sqlBuilder, column.getName(), separator);
135        sqlBuilder.append(' ')
136                .append(column.getLogic())
137                .append(' ');
138    }
139
140
141    private static void appendLinkString(StringBuilder sqlBuilder, Column before) {
142        if (sqlBuilder.length() == 0 || before == null) {
143            return;
144        } else {
145            sqlBuilder.append(before instanceof Or ? OR : AND);
146        }
147    }
148
149
150    public static void appendGroupLogic(StringBuilder sqlBuilder, Column before, Group group, char separator) {
151        List<Column> columns = group.getColumns().getList();
152        if (ArrayUtil.isNullOrEmpty(columns)) {
153            return;
154        }
155
156        StringBuilder groupSqlBuilder = new StringBuilder();
157        buildByColumns(groupSqlBuilder, columns, separator);
158
159        String groupSql = groupSqlBuilder.toString();
160        if (StrUtil.isNotBlank(groupSql)) {
161            appendLinkString(sqlBuilder, before);
162            sqlBuilder.append('(');
163            sqlBuilder.append(groupSql);
164            sqlBuilder.append(')');
165        }
166    }
167
168
169    public static void appendInLogic(StringBuilder sqlBuilder, Column column, char separator) {
170
171        appendColumnName(sqlBuilder, column, separator);
172
173        sqlBuilder.append('(');
174
175        Object[] values = (Object[]) column.getValue();
176
177        //in 里的参数数量
178        int paraCount = 0;
179        for (Object v : values) {
180            if (v.getClass() == int[].class) {
181                paraCount += ((int[]) v).length;
182            } else if (v.getClass() == long[].class) {
183                paraCount += ((long[]) v).length;
184            } else if (v.getClass() == short[].class) {
185                paraCount += ((short[]) v).length;
186            } else {
187                paraCount++;
188            }
189        }
190
191        for (int i = 0; i < paraCount; i++) {
192            sqlBuilder.append('?');
193            if (i != paraCount - 1) {
194                sqlBuilder.append(',');
195            }
196        }
197        sqlBuilder.append(')');
198    }
199
200
201    public static void appendBetweenLogic(StringBuilder sqlBuilder, Column column, char separator) {
202        appendTextWithSeparator(sqlBuilder, column.getName(), separator);
203        sqlBuilder.append(' ').append(column.getLogic());
204        sqlBuilder.append(" ? AND ?");
205    }
206
207
208    public static void appendTextWithSeparator(StringBuilder sqlBuilder, String text, char separator) {
209        if (text.indexOf(".") > 0) {
210            sqlBuilder.append(text);
211        } else {
212            sqlBuilder.append(separator).append(text).append(separator);
213        }
214    }
215
216
217    public static StringBuilder forFindByColumns(String alias, List<Join> joins, String table, String loadColumns, List<Column> columns, String orderBy, char separator) {
218        StringBuilder sqlBuilder = new StringBuilder("SELECT ");
219        sqlBuilder.append(loadColumns)
220                .append(" FROM ");
221        appendTextWithSeparator(sqlBuilder, table, separator);
222
223        buildAlias(sqlBuilder, alias);
224        buildJoinSql(sqlBuilder, joins, separator);
225        buildWhereSql(sqlBuilder, columns, separator);
226
227        orderBy = escapeOrderBySql(orderBy);
228        if (StrUtil.isNotBlank(orderBy)) {
229            sqlBuilder.append(" ORDER BY ").append(orderBy);
230        }
231
232        return sqlBuilder;
233    }
234
235    //来源于 @link Dialect.java
236    private static final Pattern ORDER_BY_PATTERN = Pattern.compile(
237            "order\\s+by\\s+[^,\\s]+(\\s+asc|\\s+desc)?(\\s*,\\s*[^,\\s]+(\\s+asc|\\s+desc)?)*",
238            Pattern.CASE_INSENSITIVE | Pattern.MULTILINE);
239
240    private static String replaceOrderBy(String sql) {
241        return ORDER_BY_PATTERN.matcher(sql).replaceAll("");
242    }
243
244    public static String forPaginateDistinctTotalRow(String select, String sqlExceptSelect, Object ext) {
245        if (ext instanceof JbootModel && CPI.hasAnyJoinEffective((JbootModel) ext)) {
246            String distinct = JbootModelExts.getDistinctColumn((JbootModel) ext);
247            if (StrUtil.isNotBlank(distinct)) {
248                return "SELECT count(DISTINCT " + distinct + ") " + replaceOrderBy(sqlExceptSelect);
249            }
250        }
251        return null;
252    }
253
254
255    public static String forPaginateFrom(String alias, List<Join> joins, String table, List<Column> columns, String orderBy, char separator) {
256        StringBuilder sqlBuilder = new StringBuilder(" FROM ");
257        appendTextWithSeparator(sqlBuilder, table, separator);
258
259        buildAlias(sqlBuilder, alias);
260        buildJoinSql(sqlBuilder, joins, separator);
261        buildWhereSql(sqlBuilder, columns, separator);
262
263        orderBy = escapeOrderBySql(orderBy);
264
265        if (StrUtil.isNotBlank(orderBy)) {
266            sqlBuilder.append(" ORDER BY ").append(orderBy);
267        }
268
269        return sqlBuilder.toString();
270    }
271
272
273    public static void buildJoinSql(StringBuilder sqlBuilder, List<Join> joins, char separator) {
274        if (joins == null || joins.isEmpty()) {
275            return;
276        }
277        for (Join join : joins) {
278            if (!join.isEffective()) {
279                continue;
280            }
281
282            sqlBuilder.append(join.getType());
283            appendTextWithSeparator(sqlBuilder, join.getTable(), separator);
284
285            buildAlias(sqlBuilder, join.getAs());
286
287            sqlBuilder.append(" ON ")
288                    .append(join.getOn());
289        }
290    }
291
292
293    public static void buildAlias(StringBuilder sqlBuilder, String alias) {
294        if (StrUtil.isNotBlank(alias)) {
295            sqlBuilder.append(" AS ").append(alias);
296        }
297    }
298
299
300    public static String forFindCountByColumns(String alias, List<Join> joins, String table, String loadColumns, List<Column> columns, char separator) {
301        StringBuilder sqlBuilder = new StringBuilder("SELECT count(" + loadColumns + ") FROM ");
302        appendTextWithSeparator(sqlBuilder, table, separator);
303
304        buildAlias(sqlBuilder, alias);
305        buildJoinSql(sqlBuilder, joins, separator);
306        buildWhereSql(sqlBuilder, columns, separator);
307
308        return sqlBuilder.toString();
309    }
310
311
312    public static String escapeOrderBySql(String orignalOrderBy) {
313        if (StrUtil.isNotBlank(orignalOrderBy) && !isValidOrderBySql(orignalOrderBy)) {
314            LogKit.warn("Sql Warn: order_by value has inject chars and be filtered, order_by value: " + orignalOrderBy);
315            return "";
316        }
317        return orignalOrderBy;
318    }
319
320
321    /**
322     * 仅支持字母、数字、下划线、空格、逗号、小数点（支持多个字段排序）
323     */
324    private static String SQL_ORDER_BY_PATTERN = "[a-zA-Z0-9_\\ \\,\\.]+";
325
326    private static boolean isValidOrderBySql(String value) {
327        return value.matches(SQL_ORDER_BY_PATTERN);
328    }
329}