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