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