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