001
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
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 }