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