001    /**
002     * Copyright (c) 2000-present Liferay, Inc. All rights reserved.
003     *
004     * This library is free software; you can redistribute it and/or modify it under
005     * the terms of the GNU Lesser General Public License as published by the Free
006     * Software Foundation; either version 2.1 of the License, or (at your option)
007     * any later version.
008     *
009     * This library is distributed in the hope that it will be useful, but WITHOUT
010     * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
011     * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
012     * details.
013     */
014    
015    package com.liferay.util.dao.orm;
016    
017    import com.liferay.portal.kernel.dao.jdbc.DataAccess;
018    import com.liferay.portal.kernel.dao.orm.QueryDefinition;
019    import com.liferay.portal.kernel.dao.orm.WildcardMode;
020    import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
021    import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
022    import com.liferay.portal.kernel.log.Log;
023    import com.liferay.portal.kernel.log.LogFactoryUtil;
024    import com.liferay.portal.kernel.util.ArrayUtil;
025    import com.liferay.portal.kernel.util.CharPool;
026    import com.liferay.portal.kernel.util.GetterUtil;
027    import com.liferay.portal.kernel.util.OrderByComparator;
028    import com.liferay.portal.kernel.util.PortalClassLoaderUtil;
029    import com.liferay.portal.kernel.util.PropsKeys;
030    import com.liferay.portal.kernel.util.PropsUtil;
031    import com.liferay.portal.kernel.util.StringBundler;
032    import com.liferay.portal.kernel.util.StringPool;
033    import com.liferay.portal.kernel.util.StringUtil;
034    import com.liferay.portal.kernel.util.Validator;
035    import com.liferay.portal.kernel.workflow.WorkflowConstants;
036    import com.liferay.portal.kernel.xml.Document;
037    import com.liferay.portal.kernel.xml.Element;
038    import com.liferay.portal.kernel.xml.UnsecureSAXReaderUtil;
039    import com.liferay.portal.util.PortalUtil;
040    
041    import java.io.IOException;
042    import java.io.InputStream;
043    
044    import java.sql.Connection;
045    import java.sql.DatabaseMetaData;
046    import java.sql.SQLException;
047    
048    import java.util.ArrayList;
049    import java.util.HashMap;
050    import java.util.List;
051    import java.util.Map;
052    import java.util.Properties;
053    
054    /**
055     * @author Brian Wing Shun Chan
056     * @author Bruno Farache
057     * @author Raymond Aug??
058     */
059    public class CustomSQL {
060    
061            public static final String DB2_FUNCTION_IS_NOT_NULL =
062                    "CAST(? AS VARCHAR(32672)) IS NOT NULL";
063    
064            public static final String DB2_FUNCTION_IS_NULL =
065                    "CAST(? AS VARCHAR(32672)) IS NULL";
066    
067            public static final String INFORMIX_FUNCTION_IS_NOT_NULL =
068                    "NOT lportal.isnull(?)";
069    
070            public static final String INFORMIX_FUNCTION_IS_NULL = "lportal.isnull(?)";
071    
072            public static final String MYSQL_FUNCTION_IS_NOT_NULL =
073                    "IFNULL(?, '1') = '0'";
074    
075            public static final String MYSQL_FUNCTION_IS_NULL = "IFNULL(?, '1') = '1'";
076    
077            public static final String SYBASE_FUNCTION_IS_NOT_NULL =
078                    "CONVERT(VARCHAR,?) IS NOT NULL";
079    
080            public static final String SYBASE_FUNCTION_IS_NULL =
081                    "CONVERT(VARCHAR,?) IS NULL";
082    
083            public CustomSQL() throws SQLException {
084                    reloadCustomSQL();
085            }
086    
087            public String appendCriteria(String sql, String criteria) {
088                    if (Validator.isNull(criteria)) {
089                            return sql;
090                    }
091    
092                    if (!criteria.startsWith(StringPool.SPACE)) {
093                            criteria = StringPool.SPACE.concat(criteria);
094                    }
095    
096                    if (!criteria.endsWith(StringPool.SPACE)) {
097                            criteria = criteria.concat(StringPool.SPACE);
098                    }
099    
100                    int pos = sql.indexOf(_GROUP_BY_CLAUSE);
101    
102                    if (pos != -1) {
103                            return sql.substring(0, pos + 1).concat(criteria).concat(
104                                    sql.substring(pos + 1));
105                    }
106    
107                    pos = sql.indexOf(_ORDER_BY_CLAUSE);
108    
109                    if (pos != -1) {
110                            return sql.substring(0, pos + 1).concat(criteria).concat(
111                                    sql.substring(pos + 1));
112                    }
113    
114                    return sql.concat(criteria);
115            }
116    
117            public String get(String id) {
118                    return _sqlPool.get(id);
119            }
120    
121            public String get(String id, QueryDefinition<?> queryDefinition) {
122                    return get(id, queryDefinition, StringPool.BLANK);
123            }
124    
125            public String get(
126                    String id, QueryDefinition<?> queryDefinition, String tableName) {
127    
128                    String sql = get(id);
129    
130                    if (!Validator.isBlank(tableName) &&
131                            !tableName.endsWith(StringPool.PERIOD)) {
132    
133                            tableName = tableName.concat(StringPool.PERIOD);
134                    }
135    
136                    if (queryDefinition.getStatus() == WorkflowConstants.STATUS_ANY) {
137                            sql = sql.replace(_STATUS_KEYWORD, _STATUS_CONDITION_EMPTY);
138                    }
139                    else {
140                            if (queryDefinition.isExcludeStatus()) {
141                                    sql = sql.replace(
142                                            _STATUS_KEYWORD,
143                                            tableName.concat(_STATUS_CONDITION_INVERSE));
144                            }
145                            else {
146                                    sql = sql.replace(
147                                            _STATUS_KEYWORD,
148                                            tableName.concat(_STATUS_CONDITION_DEFAULT));
149                            }
150                    }
151    
152                    return sql;
153            }
154    
155            /**
156             * Returns <code>true</code> if Hibernate is connecting to a DB2 database.
157             *
158             * @return <code>true</code> if Hibernate is connecting to a DB2 database
159             */
160            public boolean isVendorDB2() {
161                    return _vendorDB2;
162            }
163    
164            /**
165             * Returns <code>true</code> if Hibernate is connecting to a Hypersonic
166             * database.
167             *
168             * @return <code>true</code> if Hibernate is connecting to a Hypersonic
169             *         database
170             */
171            public boolean isVendorHSQL() {
172                    return _vendorHSQL;
173            }
174    
175            /**
176             * Returns <code>true</code> if Hibernate is connecting to an Informix
177             * database.
178             *
179             * @return <code>true</code> if Hibernate is connecting to an Informix
180             *         database
181             */
182            public boolean isVendorInformix() {
183                    return _vendorInformix;
184            }
185    
186            /**
187             * Returns <code>true</code> if Hibernate is connecting to a MySQL database.
188             *
189             * @return <code>true</code> if Hibernate is connecting to a MySQL database
190             */
191            public boolean isVendorMySQL() {
192                    return _vendorMySQL;
193            }
194    
195            /**
196             * Returns <code>true</code> if Hibernate is connecting to an Oracle
197             * database. Oracle has a nasty bug where it treats '' as a
198             * <code>NULL</code> value. See
199             * http://thedailywtf.com/forums/thread/26879.aspx for more information on
200             * this nasty bug.
201             *
202             * @return <code>true</code> if Hibernate is connecting to an Oracle
203             *         database
204             */
205            public boolean isVendorOracle() {
206                    return _vendorOracle;
207            }
208    
209            /**
210             * Returns <code>true</code> if Hibernate is connecting to a PostgreSQL
211             * database.
212             *
213             * @return <code>true</code> if Hibernate is connecting to a PostgreSQL
214             *         database
215             */
216            public boolean isVendorPostgreSQL() {
217                    return _vendorPostgreSQL;
218            }
219    
220            /**
221             * Returns <code>true</code> if Hibernate is connecting to a Sybase
222             * database.
223             *
224             * @return <code>true</code> if Hibernate is connecting to a Sybase database
225             */
226            public boolean isVendorSybase() {
227                    return _vendorSybase;
228            }
229    
230            public String[] keywords(String keywords) {
231                    return keywords(keywords, true, WildcardMode.SURROUND);
232            }
233    
234            public String[] keywords(String keywords, boolean lowerCase) {
235                    return keywords(keywords, lowerCase, WildcardMode.SURROUND);
236            }
237    
238            public String[] keywords(
239                    String keywords, boolean lowerCase, WildcardMode wildcardMode) {
240    
241                    if (Validator.isNull(keywords)) {
242                            return new String[] {null};
243                    }
244    
245                    if (_CUSTOM_SQL_AUTO_ESCAPE_WILDCARDS_ENABLED) {
246                            keywords = escapeWildCards(keywords);
247                    }
248    
249                    if (lowerCase) {
250                            keywords = StringUtil.toLowerCase(keywords);
251                    }
252    
253                    keywords = keywords.trim();
254    
255                    List<String> keywordsList = new ArrayList<>();
256    
257                    for (int i = 0; i < keywords.length(); i++) {
258                            char c = keywords.charAt(i);
259    
260                            if (c == CharPool.QUOTE) {
261                                    int pos = i + 1;
262    
263                                    i = keywords.indexOf(CharPool.QUOTE, pos);
264    
265                                    if (i == -1) {
266                                            i = keywords.length();
267                                    }
268    
269                                    if (i > pos) {
270                                            String keyword = keywords.substring(pos, i);
271    
272                                            keywordsList.add(insertWildcard(keyword, wildcardMode));
273                                    }
274                            }
275                            else {
276                                    while (Character.isWhitespace(c)) {
277                                            i++;
278    
279                                            c = keywords.charAt(i);
280                                    }
281    
282                                    int pos = i;
283    
284                                    while (!Character.isWhitespace(c)) {
285                                            i++;
286    
287                                            if (i == keywords.length()) {
288                                                    break;
289                                            }
290    
291                                            c = keywords.charAt(i);
292                                    }
293    
294                                    String keyword = keywords.substring(pos, i);
295    
296                                    keywordsList.add(insertWildcard(keyword, wildcardMode));
297                            }
298                    }
299    
300                    return keywordsList.toArray(new String[keywordsList.size()]);
301            }
302    
303            public String[] keywords(String keywords, WildcardMode wildcardMode) {
304                    return keywords(keywords, true, wildcardMode);
305            }
306    
307            public String[] keywords(String[] keywordsArray) {
308                    return keywords(keywordsArray, true);
309            }
310    
311            public String[] keywords(String[] keywordsArray, boolean lowerCase) {
312                    if (ArrayUtil.isEmpty(keywordsArray)) {
313                            return new String[] {null};
314                    }
315    
316                    if (lowerCase) {
317                            for (int i = 0; i < keywordsArray.length; i++) {
318                                    keywordsArray[i] = StringUtil.lowerCase(keywordsArray[i]);
319                            }
320                    }
321    
322                    return keywordsArray;
323            }
324    
325            public void reloadCustomSQL() throws SQLException {
326                    PortalUtil.initCustomSQL();
327    
328                    Connection con = DataAccess.getConnection();
329    
330                    String functionIsNull = PortalUtil.getCustomSQLFunctionIsNull();
331                    String functionIsNotNull = PortalUtil.getCustomSQLFunctionIsNotNull();
332    
333                    try {
334                            if (Validator.isNotNull(functionIsNull) &&
335                                    Validator.isNotNull(functionIsNotNull)) {
336    
337                                    _functionIsNull = functionIsNull;
338                                    _functionIsNotNull = functionIsNotNull;
339    
340                                    if (_log.isDebugEnabled()) {
341                                            _log.debug(
342                                                    "functionIsNull is manually set to " + functionIsNull);
343                                            _log.debug(
344                                                    "functionIsNotNull is manually set to " +
345                                                            functionIsNotNull);
346                                    }
347                            }
348                            else if (con != null) {
349                                    DatabaseMetaData metaData = con.getMetaData();
350    
351                                    String dbName = GetterUtil.getString(
352                                            metaData.getDatabaseProductName());
353    
354                                    if (_log.isInfoEnabled()) {
355                                            _log.info("Database name " + dbName);
356                                    }
357    
358                                    if (dbName.startsWith("DB2")) {
359                                            _vendorDB2 = true;
360                                            _functionIsNull = DB2_FUNCTION_IS_NULL;
361                                            _functionIsNotNull = DB2_FUNCTION_IS_NOT_NULL;
362    
363                                            if (_log.isInfoEnabled()) {
364                                                    _log.info("Detected DB2 with database name " + dbName);
365                                            }
366                                    }
367                                    else if (dbName.startsWith("HSQL")) {
368                                            _vendorHSQL = true;
369    
370                                            if (_log.isInfoEnabled()) {
371                                                    _log.info("Detected HSQL with database name " + dbName);
372                                            }
373                                    }
374                                    else if (dbName.startsWith("Informix")) {
375                                            _vendorInformix = true;
376                                            _functionIsNull = INFORMIX_FUNCTION_IS_NULL;
377                                            _functionIsNotNull = INFORMIX_FUNCTION_IS_NOT_NULL;
378    
379                                            if (_log.isInfoEnabled()) {
380                                                    _log.info(
381                                                            "Detected Informix with database name " + dbName);
382                                            }
383                                    }
384                                    else if (dbName.startsWith("MySQL")) {
385                                            _vendorMySQL = true;
386                                            //_functionIsNull = MYSQL_FUNCTION_IS_NULL;
387                                            //_functionIsNotNull = MYSQL_FUNCTION_IS_NOT_NULL;
388    
389                                            if (_log.isInfoEnabled()) {
390                                                    _log.info(
391                                                            "Detected MySQL with database name " + dbName);
392                                            }
393                                    }
394                                    else if (dbName.startsWith("Sybase") || dbName.equals("ASE")) {
395                                            _vendorSybase = true;
396                                            _functionIsNull = SYBASE_FUNCTION_IS_NULL;
397                                            _functionIsNotNull = SYBASE_FUNCTION_IS_NOT_NULL;
398    
399                                            if (_log.isInfoEnabled()) {
400                                                    _log.info(
401                                                            "Detected Sybase with database name " + dbName);
402                                            }
403                                    }
404                                    else if (dbName.startsWith("Oracle")) {
405                                            _vendorOracle = true;
406    
407                                            if (_log.isInfoEnabled()) {
408                                                    _log.info(
409                                                            "Detected Oracle with database name " + dbName);
410                                            }
411                                    }
412                                    else if (dbName.startsWith("PostgreSQL")) {
413                                            _vendorPostgreSQL = true;
414    
415                                            if (_log.isInfoEnabled()) {
416                                                    _log.info(
417                                                            "Detected PostgreSQL with database name " + dbName);
418                                            }
419                                    }
420                                    else {
421                                            if (_log.isDebugEnabled()) {
422                                                    _log.debug(
423                                                            "Unable to detect database with name " + dbName);
424                                            }
425                                    }
426                            }
427                    }
428                    catch (Exception e) {
429                            _log.error(e, e);
430                    }
431                    finally {
432                            DataAccess.cleanUp(con);
433                    }
434    
435                    if (_sqlPool == null) {
436                            _sqlPool = new HashMap<>();
437                    }
438                    else {
439                            _sqlPool.clear();
440                    }
441    
442                    try {
443                            Class<?> clazz = getClass();
444    
445                            ClassLoader classLoader = clazz.getClassLoader();
446    
447                            String[] configs = getConfigs();
448    
449                            for (String _config : configs) {
450                                    read(classLoader, _config);
451                            }
452                    }
453                    catch (Exception e) {
454                            _log.error(e, e);
455                    }
456            }
457    
458            public String removeGroupBy(String sql) {
459                    int x = sql.indexOf(_GROUP_BY_CLAUSE);
460    
461                    if (x != -1) {
462                            int y = sql.indexOf(_ORDER_BY_CLAUSE);
463    
464                            if (y == -1) {
465                                    sql = sql.substring(0, x);
466                            }
467                            else {
468                                    sql = sql.substring(0, x) + sql.substring(y);
469                            }
470                    }
471    
472                    return sql;
473            }
474    
475            public String removeOrderBy(String sql) {
476                    int pos = sql.indexOf(_ORDER_BY_CLAUSE);
477    
478                    if (pos != -1) {
479                            sql = sql.substring(0, pos);
480                    }
481    
482                    return sql;
483            }
484    
485            public String replaceAndOperator(String sql, boolean andOperator) {
486                    String andOrConnector = "OR";
487                    String andOrNullCheck = "AND ? IS NOT NULL";
488    
489                    if (andOperator) {
490                            andOrConnector = "AND";
491                            andOrNullCheck = "OR ? IS NULL";
492                    }
493    
494                    sql = StringUtil.replace(
495                            sql, new String[] {"[$AND_OR_CONNECTOR$]", "[$AND_OR_NULL_CHECK$]"},
496                            new String[] {andOrConnector, andOrNullCheck});
497    
498                    if (_vendorPostgreSQL) {
499                            sql = StringUtil.replace(
500                                    sql,
501                                    new String[] {
502                                            "Date >= ? AND ? IS NOT NULL",
503                                            "Date <= ? AND ? IS NOT NULL", "Date >= ? OR ? IS NULL",
504                                            "Date <= ? OR ? IS NULL"
505                                    },
506                                    new String[] {
507                                            "Date >= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
508                                            "Date <= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
509                                            "Date >= ? OR CAST(? AS TIMESTAMP) IS NULL",
510                                            "Date <= ? OR CAST(? AS TIMESTAMP) IS NULL"
511                                    });
512                    }
513    
514                    sql = replaceIsNull(sql);
515    
516                    return sql;
517            }
518    
519            public String replaceGroupBy(String sql, String groupBy) {
520                    if (groupBy == null) {
521                            return sql;
522                    }
523    
524                    int x = sql.indexOf(_GROUP_BY_CLAUSE);
525    
526                    if (x != -1) {
527                            int y = sql.indexOf(_ORDER_BY_CLAUSE);
528    
529                            if (y == -1) {
530                                    sql = sql.substring(0, x + _GROUP_BY_CLAUSE.length()).concat(
531                                            groupBy);
532                            }
533                            else {
534                                    sql = sql.substring(0, x + _GROUP_BY_CLAUSE.length()).concat(
535                                            groupBy).concat(sql.substring(y));
536                            }
537                    }
538                    else {
539                            int y = sql.indexOf(_ORDER_BY_CLAUSE);
540    
541                            if (y == -1) {
542                                    sql = sql.concat(_GROUP_BY_CLAUSE).concat(groupBy);
543                            }
544                            else {
545                                    StringBundler sb = new StringBundler(4);
546    
547                                    sb.append(sql.substring(0, y));
548                                    sb.append(_GROUP_BY_CLAUSE);
549                                    sb.append(groupBy);
550                                    sb.append(sql.substring(y));
551    
552                                    sql = sb.toString();
553                            }
554                    }
555    
556                    return sql;
557            }
558    
559            public String replaceIsNull(String sql) {
560                    if (Validator.isNotNull(_functionIsNull)) {
561                            sql = StringUtil.replace(
562                                    sql, new String[] {"? IS NULL", "? IS NOT NULL"},
563                                    new String[] {_functionIsNull, _functionIsNotNull});
564                    }
565    
566                    return sql;
567            }
568    
569            public String replaceKeywords(
570                    String sql, String field, boolean last, int[] values) {
571    
572                    if ((values != null) && (values.length == 1)) {
573                            return sql;
574                    }
575    
576                    StringBundler oldSql = new StringBundler(4);
577    
578                    oldSql.append(StringPool.OPEN_PARENTHESIS);
579                    oldSql.append(field);
580                    oldSql.append(" = ?)");
581    
582                    if (!last) {
583                            oldSql.append(" [$AND_OR_CONNECTOR$]");
584                    }
585    
586                    if (ArrayUtil.isEmpty(values)) {
587                            return StringUtil.replace(sql, oldSql.toString(), StringPool.BLANK);
588                    }
589    
590                    StringBundler newSql = new StringBundler(values.length * 4 + 3);
591    
592                    newSql.append(StringPool.OPEN_PARENTHESIS);
593    
594                    for (int i = 0; i < values.length; i++) {
595                            if (i > 0) {
596                                    newSql.append(" OR ");
597                            }
598    
599                            newSql.append(StringPool.OPEN_PARENTHESIS);
600                            newSql.append(field);
601                            newSql.append(" = ?)");
602                    }
603    
604                    newSql.append(StringPool.CLOSE_PARENTHESIS);
605    
606                    if (!last) {
607                            newSql.append(" [$AND_OR_CONNECTOR$]");
608                    }
609    
610                    return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
611            }
612    
613            public String replaceKeywords(
614                    String sql, String field, boolean last, long[] values) {
615    
616                    if ((values != null) && (values.length == 1)) {
617                            return sql;
618                    }
619    
620                    StringBundler oldSql = new StringBundler(4);
621    
622                    oldSql.append(StringPool.OPEN_PARENTHESIS);
623                    oldSql.append(field);
624                    oldSql.append(" = ?)");
625    
626                    if (!last) {
627                            oldSql.append(" [$AND_OR_CONNECTOR$]");
628                    }
629    
630                    if (ArrayUtil.isEmpty(values)) {
631                            return StringUtil.replace(sql, oldSql.toString(), StringPool.BLANK);
632                    }
633    
634                    StringBundler newSql = new StringBundler(values.length * 4 + 3);
635    
636                    newSql.append(StringPool.OPEN_PARENTHESIS);
637    
638                    for (int i = 0; i < values.length; i++) {
639                            if (i > 0) {
640                                    newSql.append(" OR ");
641                            }
642    
643                            newSql.append(StringPool.OPEN_PARENTHESIS);
644                            newSql.append(field);
645                            newSql.append(" = ?)");
646                    }
647    
648                    newSql.append(StringPool.CLOSE_PARENTHESIS);
649    
650                    if (!last) {
651                            newSql.append(" [$AND_OR_CONNECTOR$]");
652                    }
653    
654                    return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
655            }
656    
657            public String replaceKeywords(
658                    String sql, String field, String operator, boolean last,
659                    String[] values) {
660    
661                    if ((values != null) && (values.length <= 1)) {
662                            return sql;
663                    }
664    
665                    StringBundler oldSql = new StringBundler(6);
666    
667                    oldSql.append(StringPool.OPEN_PARENTHESIS);
668                    oldSql.append(field);
669                    oldSql.append(" ");
670                    oldSql.append(operator);
671                    oldSql.append(" ? [$AND_OR_NULL_CHECK$])");
672    
673                    if (!last) {
674                            oldSql.append(" [$AND_OR_CONNECTOR$]");
675                    }
676    
677                    StringBundler newSql = new StringBundler(values.length * 6 + 3);
678    
679                    newSql.append(StringPool.OPEN_PARENTHESIS);
680    
681                    for (int i = 0; i < values.length; i++) {
682                            if (i > 0) {
683                                    newSql.append(" OR ");
684                            }
685    
686                            newSql.append(StringPool.OPEN_PARENTHESIS);
687                            newSql.append(field);
688                            newSql.append(" ");
689                            newSql.append(operator);
690                            newSql.append(" ? [$AND_OR_NULL_CHECK$])");
691                    }
692    
693                    newSql.append(StringPool.CLOSE_PARENTHESIS);
694    
695                    if (!last) {
696                            newSql.append(" [$AND_OR_CONNECTOR$]");
697                    }
698    
699                    return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
700            }
701    
702            public String replaceOrderBy(String sql, OrderByComparator<?> obc) {
703                    if (obc == null) {
704                            return sql;
705                    }
706    
707                    String orderBy = obc.getOrderBy();
708    
709                    int pos = sql.indexOf(_ORDER_BY_CLAUSE);
710    
711                    if ((pos != -1) && (pos < sql.length())) {
712                            sql = sql.substring(0, pos + _ORDER_BY_CLAUSE.length()).concat(
713                                    orderBy);
714                    }
715                    else {
716                            sql = sql.concat(_ORDER_BY_CLAUSE).concat(orderBy);
717                    }
718    
719                    return sql;
720            }
721    
722            protected String[] getConfigs() {
723                    if (PortalClassLoaderUtil.getClassLoader() ==
724                                    CustomSQL.class.getClassLoader()) {
725    
726                            Properties propsUtil = PortalUtil.getPortalProperties();
727    
728                            return StringUtil.split(
729                                    propsUtil.getProperty("custom.sql.configs"));
730                    }
731                    else {
732                            return new String[] {"custom-sql/default.xml"};
733                    }
734            }
735    
736            protected String insertWildcard(String keyword, WildcardMode wildcardMode) {
737                    if (wildcardMode == WildcardMode.LEADING) {
738                            return StringPool.PERCENT.concat(keyword);
739                    }
740                    else if (wildcardMode == WildcardMode.SURROUND) {
741                            return StringUtil.quote(keyword, StringPool.PERCENT);
742                    }
743                    else if (wildcardMode == WildcardMode.TRAILING) {
744                            return keyword.concat(StringPool.PERCENT);
745                    }
746                    else {
747                            throw new IllegalArgumentException(
748                                    "Invalid wildcard mode " + wildcardMode);
749                    }
750            }
751    
752            protected void read(ClassLoader classLoader, String source)
753                    throws Exception {
754    
755                    InputStream is = classLoader.getResourceAsStream(source);
756    
757                    if (is == null) {
758                            return;
759                    }
760    
761                    if (_log.isDebugEnabled()) {
762                            _log.debug("Loading " + source);
763                    }
764    
765                    Document document = UnsecureSAXReaderUtil.read(is);
766    
767                    Element rootElement = document.getRootElement();
768    
769                    for (Element sqlElement : rootElement.elements("sql")) {
770                            String file = sqlElement.attributeValue("file");
771    
772                            if (Validator.isNotNull(file)) {
773                                    read(classLoader, file);
774                            }
775                            else {
776                                    String id = sqlElement.attributeValue("id");
777                                    String content = transform(sqlElement.getText());
778    
779                                    content = replaceIsNull(content);
780    
781                                    _sqlPool.put(id, content);
782                            }
783                    }
784            }
785    
786            protected String transform(String sql) {
787                    sql = PortalUtil.transformCustomSQL(sql);
788    
789                    StringBundler sb = new StringBundler();
790    
791                    try (UnsyncBufferedReader unsyncBufferedReader =
792                                    new UnsyncBufferedReader(new UnsyncStringReader(sql))) {
793    
794                            String line = null;
795    
796                            while ((line = unsyncBufferedReader.readLine()) != null) {
797                                    sb.append(line.trim());
798                                    sb.append(StringPool.SPACE);
799                            }
800                    }
801                    catch (IOException ioe) {
802                            return sql;
803                    }
804    
805                    return sb.toString();
806            }
807    
808            private String escapeWildCards(String keywords) {
809                    if (!isVendorMySQL() && !isVendorOracle()) {
810                            return keywords;
811                    }
812    
813                    StringBuilder sb = new StringBuilder(keywords);
814    
815                    for (int i = 0; i < sb.length(); ++i) {
816                            char c = sb.charAt(i);
817    
818                            if (c == CharPool.BACK_SLASH) {
819                                    i++;
820    
821                                    continue;
822                            }
823    
824                            if ((c == CharPool.UNDERLINE) || (c == CharPool.PERCENT)) {
825                                    sb.insert(i, CharPool.BACK_SLASH);
826    
827                                    i++;
828    
829                                    continue;
830                            }
831                    }
832    
833                    return sb.toString();
834            }
835    
836            private static final boolean _CUSTOM_SQL_AUTO_ESCAPE_WILDCARDS_ENABLED =
837                    GetterUtil.getBoolean(
838                            PropsUtil.get(PropsKeys.CUSTOM_SQL_AUTO_ESCAPE_WILDCARDS_ENABLED));
839    
840            private static final String _GROUP_BY_CLAUSE = " GROUP BY ";
841    
842            private static final String _ORDER_BY_CLAUSE = " ORDER BY ";
843    
844            private static final String _STATUS_CONDITION_DEFAULT = "status = ?";
845    
846            private static final String _STATUS_CONDITION_EMPTY =
847                    WorkflowConstants.STATUS_ANY + " = ?";
848    
849            private static final String _STATUS_CONDITION_INVERSE = "status != ?";
850    
851            private static final String _STATUS_KEYWORD = "[$STATUS$]";
852    
853            private static final Log _log = LogFactoryUtil.getLog(CustomSQL.class);
854    
855            private String _functionIsNotNull;
856            private String _functionIsNull;
857            private Map<String, String> _sqlPool;
858            private boolean _vendorDB2;
859            private boolean _vendorHSQL;
860            private boolean _vendorInformix;
861            private boolean _vendorMySQL;
862            private boolean _vendorOracle;
863            private boolean _vendorPostgreSQL;
864            private boolean _vendorSybase;
865    
866    }