001    /**
002     * Copyright (c) 2000-2011 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.portal.dao.orm.hibernate;
016    
017    import com.liferay.portal.kernel.util.CharPool;
018    import com.liferay.portal.kernel.util.StringBundler;
019    import com.liferay.portal.kernel.util.StringPool;
020    import com.liferay.portal.kernel.util.StringUtil;
021    
022    import java.util.regex.Matcher;
023    import java.util.regex.Pattern;
024    
025    import org.hibernate.dialect.SQLServerDialect;
026    
027    /**
028     * @author Steven Cao
029     */
030    public class SQLServer2008Dialect extends SQLServerDialect {
031    
032            public String getLimitString(String sql, int offset, int limit) {
033                    String sqlLowerCase = sql.toLowerCase();
034    
035                    String limitString = null;
036    
037                    if (sqlLowerCase.contains(" union ")) {
038                            limitString = getLimitStringUnion(sql, sqlLowerCase, offset, limit);
039                    }
040                    else {
041                            limitString = getLimitString(sql, sqlLowerCase, offset, limit);
042                    }
043    
044                    return limitString;
045            }
046    
047            public boolean supportsLimitOffset() {
048                    return _SUPPORTS_LIMIT_OFFSET;
049            }
050    
051            protected String getLimitString(
052                    String sql, String sqlLowerCase, int offset, int limit) {
053    
054                    int orderByPos = sqlLowerCase.lastIndexOf(" order by ");
055    
056                    if (orderByPos < 0) {
057                            return super.getLimitString(sql, offset, limit);
058                    }
059    
060                    String orderByString = sql.substring(orderByPos + 9, sql.length());
061    
062                    String[] orderByArray = StringUtil.split(
063                            orderByString, StringPool.COMMA);
064    
065                    int fromPos = sqlLowerCase.indexOf(" from ");
066    
067                    String selectFrom = sql.substring(0, fromPos);
068    
069                    for (int i = 0; i < orderByArray.length; i++) {
070                            String orderBy = orderByArray[i].trim();
071    
072                            String orderByColumn = null;
073                            String orderByType = null;
074    
075                            int columnPos = orderBy.indexOf(CharPool.SPACE);
076    
077                            if (columnPos == -1) {
078                                    orderByColumn = orderBy;
079                                    orderByType = "ASC";
080                            }
081                            else {
082                                    orderByColumn = orderBy.substring(0, columnPos);
083                                    orderByType = orderBy.substring(columnPos + 1);
084                            }
085    
086                            Pattern pattern = Pattern.compile(
087                                    "(\\S+) as \\Q".concat(orderByColumn).concat("\\E\\W"),
088                                    Pattern.CASE_INSENSITIVE);
089    
090                            Matcher matcher = pattern.matcher(selectFrom);
091    
092                            if (matcher.find()) {
093                                    orderByColumn = matcher.group(1);
094                            }
095    
096                            orderByArray[i] = orderByColumn.concat(
097                                    StringPool.SPACE).concat(orderByType);
098                    }
099    
100                    String selectFromWhere = sql.substring(fromPos, orderByPos);
101    
102                    StringBundler sb = new StringBundler(11);
103    
104                    sb.append("select * from (");
105                    sb.append(selectFrom);
106                    sb.append(", row_number() over (order by ");
107                    sb.append(StringUtil.merge(orderByArray, StringPool.COMMA));
108                    sb.append(") as _page_row_num ");
109                    sb.append(selectFromWhere);
110                    sb.append(" ) temp where _page_row_num between ");
111                    sb.append(offset + 1);
112                    sb.append(" and ");
113                    sb.append(limit);
114                    sb.append(" order by _page_row_num");
115    
116                    return sb.toString();
117            }
118    
119            protected String getLimitStringUnion(
120                    String sql, String sqlLowerCase, int offset, int limit) {
121    
122                    int orderByPos = sqlLowerCase.lastIndexOf(" order by ");
123    
124                    if (orderByPos < 0) {
125                            return super.getLimitString(sql, offset, limit);
126                    }
127    
128                    String orderByString = sql.substring(orderByPos + 9, sql.length());
129    
130                    String[] orderByArray = StringUtil.split(
131                            orderByString, StringPool.COMMA);
132    
133                    int fromPos = sqlLowerCase.indexOf(" from ");
134    
135                    String selectFrom = sql.substring(0, fromPos);
136    
137                    for (int i = 0; i < orderByArray.length; i++) {
138                            String orderBy = orderByArray[i].trim();
139    
140                            String orderByColumn = null;
141                            String orderByType = null;
142    
143                            int columnPos = orderBy.indexOf(CharPool.SPACE);
144    
145                            if (columnPos == -1) {
146                                    orderByColumn = orderBy;
147                                    orderByType = "ASC";
148                            }
149                            else {
150                                    orderByColumn = orderBy.substring(0, columnPos);
151                                    orderByType = orderBy.substring(columnPos + 1);
152                            }
153    
154                            Pattern pattern = Pattern.compile(
155                                    "\\Q".concat(orderByColumn).concat("\\E as (\\w+)"),
156                                    Pattern.CASE_INSENSITIVE);
157    
158                            Matcher matcher = pattern.matcher(selectFrom);
159    
160                            if (matcher.find()) {
161                                    orderByColumn = matcher.group(1);
162                            }
163    
164                            orderByArray[i] = orderByColumn.concat(
165                                    StringPool.SPACE).concat(orderByType);
166                    }
167    
168                    String selectFromWhere = sql.substring(fromPos, orderByPos);
169    
170                    StringBundler sb = new StringBundler(10);
171    
172                    sb.append("select * from (select *, row_number() over (order by ");
173                    sb.append(StringUtil.merge(orderByArray, StringPool.COMMA));
174                    sb.append(") as _page_row_num from (");
175                    sb.append(selectFrom);
176                    sb.append(selectFromWhere);
177                    sb.append(" ) temp ) temp2 where _page_row_num between ");
178                    sb.append(offset + 1);
179                    sb.append(" and ");
180                    sb.append(limit);
181                    sb.append(" order by _page_row_num");
182    
183                    return sb.toString();
184            }
185    
186            private static final boolean _SUPPORTS_LIMIT_OFFSET = true;
187    
188    }