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.SAXReaderUtil;
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<String>();
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<String, String>();
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,
496                            new String[] {
497                                    "[$AND_OR_CONNECTOR$]", "[$AND_OR_NULL_CHECK$]"
498                            },
499                            new String[] {
500                                    andOrConnector, andOrNullCheck
501                            });
502    
503                    if (_vendorPostgreSQL) {
504                            sql = StringUtil.replace(
505                                    sql,
506                                    new String[] {
507                                            "Date >= ? AND ? IS NOT NULL",
508                                            "Date <= ? AND ? IS NOT NULL", "Date >= ? OR ? IS NULL",
509                                            "Date <= ? OR ? IS NULL"
510                                    },
511                                    new String[] {
512                                            "Date >= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
513                                            "Date <= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
514                                            "Date >= ? OR CAST(? AS TIMESTAMP) IS NULL",
515                                            "Date <= ? OR CAST(? AS TIMESTAMP) IS NULL"
516                                    });
517                    }
518    
519                    sql = replaceIsNull(sql);
520    
521                    return sql;
522            }
523    
524            public String replaceGroupBy(String sql, String groupBy) {
525                    if (groupBy == null) {
526                            return sql;
527                    }
528    
529                    int x = sql.indexOf(_GROUP_BY_CLAUSE);
530    
531                    if (x != -1) {
532                            int y = sql.indexOf(_ORDER_BY_CLAUSE);
533    
534                            if (y == -1) {
535                                    sql = sql.substring(0, x + _GROUP_BY_CLAUSE.length()).concat(
536                                            groupBy);
537                            }
538                            else {
539                                    sql = sql.substring(0, x + _GROUP_BY_CLAUSE.length()).concat(
540                                            groupBy).concat(sql.substring(y));
541                            }
542                    }
543                    else {
544                            int y = sql.indexOf(_ORDER_BY_CLAUSE);
545    
546                            if (y == -1) {
547                                    sql = sql.concat(_GROUP_BY_CLAUSE).concat(groupBy);
548                            }
549                            else {
550                                    StringBundler sb = new StringBundler(4);
551    
552                                    sb.append(sql.substring(0, y));
553                                    sb.append(_GROUP_BY_CLAUSE);
554                                    sb.append(groupBy);
555                                    sb.append(sql.substring(y));
556    
557                                    sql = sb.toString();
558                            }
559                    }
560    
561                    return sql;
562            }
563    
564            public String replaceIsNull(String sql) {
565                    if (Validator.isNotNull(_functionIsNull)) {
566                            sql = StringUtil.replace(
567                                    sql,
568                                    new String[] {
569                                            "? IS NULL", "? IS NOT NULL"
570                                    },
571                                    new String[] {
572                                            _functionIsNull, _functionIsNotNull
573                                    });
574                    }
575    
576                    return sql;
577            }
578    
579            public String replaceKeywords(
580                    String sql, String field, boolean last, int[] values) {
581    
582                    if ((values != null) && (values.length == 1)) {
583                            return sql;
584                    }
585    
586                    StringBundler oldSql = new StringBundler(4);
587    
588                    oldSql.append(StringPool.OPEN_PARENTHESIS);
589                    oldSql.append(field);
590                    oldSql.append(" = ?)");
591    
592                    if (!last) {
593                            oldSql.append(" [$AND_OR_CONNECTOR$]");
594                    }
595    
596                    if (ArrayUtil.isEmpty(values)) {
597                            return StringUtil.replace(sql, oldSql.toString(), StringPool.BLANK);
598                    }
599    
600                    StringBundler newSql = new StringBundler(values.length * 4 + 3);
601    
602                    newSql.append(StringPool.OPEN_PARENTHESIS);
603    
604                    for (int i = 0; i < values.length; i++) {
605                            if (i > 0) {
606                                    newSql.append(" OR ");
607                            }
608    
609                            newSql.append(StringPool.OPEN_PARENTHESIS);
610                            newSql.append(field);
611                            newSql.append(" = ?)");
612                    }
613    
614                    newSql.append(StringPool.CLOSE_PARENTHESIS);
615    
616                    if (!last) {
617                            newSql.append(" [$AND_OR_CONNECTOR$]");
618                    }
619    
620                    return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
621            }
622    
623            public String replaceKeywords(
624                    String sql, String field, boolean last, long[] values) {
625    
626                    if ((values != null) && (values.length == 1)) {
627                            return sql;
628                    }
629    
630                    StringBundler oldSql = new StringBundler(4);
631    
632                    oldSql.append(StringPool.OPEN_PARENTHESIS);
633                    oldSql.append(field);
634                    oldSql.append(" = ?)");
635    
636                    if (!last) {
637                            oldSql.append(" [$AND_OR_CONNECTOR$]");
638                    }
639    
640                    if (ArrayUtil.isEmpty(values)) {
641                            return StringUtil.replace(sql, oldSql.toString(), StringPool.BLANK);
642                    }
643    
644                    StringBundler newSql = new StringBundler(values.length * 4 + 3);
645    
646                    newSql.append(StringPool.OPEN_PARENTHESIS);
647    
648                    for (int i = 0; i < values.length; i++) {
649                            if (i > 0) {
650                                    newSql.append(" OR ");
651                            }
652    
653                            newSql.append(StringPool.OPEN_PARENTHESIS);
654                            newSql.append(field);
655                            newSql.append(" = ?)");
656                    }
657    
658                    newSql.append(StringPool.CLOSE_PARENTHESIS);
659    
660                    if (!last) {
661                            newSql.append(" [$AND_OR_CONNECTOR$]");
662                    }
663    
664                    return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
665            }
666    
667            public String replaceKeywords(
668                    String sql, String field, String operator, boolean last,
669                    String[] values) {
670    
671                    if ((values != null) && (values.length <= 1)) {
672                            return sql;
673                    }
674    
675                    StringBundler oldSql = new StringBundler(6);
676    
677                    oldSql.append(StringPool.OPEN_PARENTHESIS);
678                    oldSql.append(field);
679                    oldSql.append(" ");
680                    oldSql.append(operator);
681                    oldSql.append(" ? [$AND_OR_NULL_CHECK$])");
682    
683                    if (!last) {
684                            oldSql.append(" [$AND_OR_CONNECTOR$]");
685                    }
686    
687                    StringBundler newSql = new StringBundler(values.length * 6 + 3);
688    
689                    newSql.append(StringPool.OPEN_PARENTHESIS);
690    
691                    for (int i = 0; i < values.length; i++) {
692                            if (i > 0) {
693                                    newSql.append(" OR ");
694                            }
695    
696                            newSql.append(StringPool.OPEN_PARENTHESIS);
697                            newSql.append(field);
698                            newSql.append(" ");
699                            newSql.append(operator);
700                            newSql.append(" ? [$AND_OR_NULL_CHECK$])");
701                    }
702    
703                    newSql.append(StringPool.CLOSE_PARENTHESIS);
704    
705                    if (!last) {
706                            newSql.append(" [$AND_OR_CONNECTOR$]");
707                    }
708    
709                    return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
710            }
711    
712            public String replaceOrderBy(String sql, OrderByComparator<?> obc) {
713                    if (obc == null) {
714                            return sql;
715                    }
716    
717                    String orderBy = obc.getOrderBy();
718    
719                    int pos = sql.indexOf(_ORDER_BY_CLAUSE);
720    
721                    if ((pos != -1) && (pos < sql.length())) {
722                            sql = sql.substring(0, pos + _ORDER_BY_CLAUSE.length()).concat(
723                                    orderBy);
724                    }
725                    else {
726                            sql = sql.concat(_ORDER_BY_CLAUSE).concat(orderBy);
727                    }
728    
729                    return sql;
730            }
731    
732            protected String[] getConfigs() {
733                    if (PortalClassLoaderUtil.getClassLoader() ==
734                                    CustomSQL.class.getClassLoader()) {
735    
736                            Properties propsUtil = PortalUtil.getPortalProperties();
737    
738                            return StringUtil.split(
739                                    propsUtil.getProperty("custom.sql.configs"));
740                    }
741                    else {
742                            return new String[] {"custom-sql/default.xml"};
743                    }
744            }
745    
746            protected String insertWildcard(String keyword, WildcardMode wildcardMode) {
747                    if (wildcardMode == WildcardMode.LEADING) {
748                            return StringPool.PERCENT.concat(keyword);
749                    }
750                    else if (wildcardMode == WildcardMode.SURROUND) {
751                            return StringUtil.quote(keyword, StringPool.PERCENT);
752                    }
753                    else if (wildcardMode == WildcardMode.TRAILING) {
754                            return keyword.concat(StringPool.PERCENT);
755                    }
756                    else {
757                            throw new IllegalArgumentException(
758                                    "Invalid wildcard mode " + wildcardMode);
759                    }
760            }
761    
762            protected void read(ClassLoader classLoader, String source)
763                    throws Exception {
764    
765                    InputStream is = classLoader.getResourceAsStream(source);
766    
767                    if (is == null) {
768                            return;
769                    }
770    
771                    if (_log.isDebugEnabled()) {
772                            _log.debug("Loading " + source);
773                    }
774    
775                    Document document = SAXReaderUtil.read(is);
776    
777                    Element rootElement = document.getRootElement();
778    
779                    for (Element sqlElement : rootElement.elements("sql")) {
780                            String file = sqlElement.attributeValue("file");
781    
782                            if (Validator.isNotNull(file)) {
783                                    read(classLoader, file);
784                            }
785                            else {
786                                    String id = sqlElement.attributeValue("id");
787                                    String content = transform(sqlElement.getText());
788    
789                                    content = replaceIsNull(content);
790    
791                                    _sqlPool.put(id, content);
792                            }
793                    }
794            }
795    
796            protected String transform(String sql) {
797                    sql = PortalUtil.transformCustomSQL(sql);
798    
799                    StringBundler sb = new StringBundler();
800    
801                    try (UnsyncBufferedReader unsyncBufferedReader =
802                                    new UnsyncBufferedReader(new UnsyncStringReader(sql))) {
803    
804                            String line = null;
805    
806                            while ((line = unsyncBufferedReader.readLine()) != null) {
807                                    sb.append(line.trim());
808                                    sb.append(StringPool.SPACE);
809                            }
810                    }
811                    catch (IOException ioe) {
812                            return sql;
813                    }
814    
815                    return sb.toString();
816            }
817    
818            private String escapeWildCards(String keywords) {
819                    if (!isVendorMySQL() && !isVendorOracle()) {
820                            return keywords;
821                    }
822    
823                    StringBuilder sb = new StringBuilder(keywords);
824    
825                    for (int i = 0; i < sb.length(); ++i) {
826                            char c = sb.charAt(i);
827    
828                            if (c == CharPool.BACK_SLASH) {
829                                    i++;
830    
831                                    continue;
832                            }
833    
834                            if ((c == CharPool.UNDERLINE) || (c == CharPool.PERCENT)) {
835                                    sb.insert(i, CharPool.BACK_SLASH);
836    
837                                    i++;
838    
839                                    continue;
840                            }
841                    }
842    
843                    return sb.toString();
844            }
845    
846            private static final boolean _CUSTOM_SQL_AUTO_ESCAPE_WILDCARDS_ENABLED =
847                    GetterUtil.getBoolean(
848                            PropsUtil.get(PropsKeys.CUSTOM_SQL_AUTO_ESCAPE_WILDCARDS_ENABLED));
849    
850            private static final String _GROUP_BY_CLAUSE = " GROUP BY ";
851    
852            private static final String _ORDER_BY_CLAUSE = " ORDER BY ";
853    
854            private static final String _STATUS_CONDITION_DEFAULT = "status = ?";
855    
856            private static final String _STATUS_CONDITION_EMPTY =
857                    WorkflowConstants.STATUS_ANY + " = ?";
858    
859            private static final String _STATUS_CONDITION_INVERSE = "status != ?";
860    
861            private static final String _STATUS_KEYWORD = "[$STATUS$]";
862    
863            private static Log _log = LogFactoryUtil.getLog(CustomSQL.class);
864    
865            private String _functionIsNotNull;
866            private String _functionIsNull;
867            private Map<String, String> _sqlPool;
868            private boolean _vendorDB2;
869            private boolean _vendorHSQL;
870            private boolean _vendorInformix;
871            private boolean _vendorMySQL;
872            private boolean _vendorOracle;
873            private boolean _vendorPostgreSQL;
874            private boolean _vendorSybase;
875    
876    }