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 import com.liferay.portal.kernel.util.Validator;
022
023 import java.util.regex.Matcher;
024 import java.util.regex.Pattern;
025
026
030 public class SQLServerLimitStringUtil {
031
032 public static String getLimitString(String sql, int offset, int limit) {
033 String sqlLowerCase = StringUtil.toLowerCase(sql);
034
035 int fromPos = sqlLowerCase.indexOf(" from ");
036
037 String selectFrom = sql.substring(0, fromPos);
038
039 int orderByPos = sqlLowerCase.lastIndexOf(" order by ");
040
041 String selectFromWhere = null;
042
043 String orderBy = StringPool.BLANK;
044
045 if (orderByPos > 0) {
046 selectFromWhere = sql.substring(fromPos, orderByPos);
047
048 orderBy = sql.substring(orderByPos + 9);
049 }
050 else {
051 selectFromWhere = sql.substring(fromPos);
052 }
053
054 String[] splitOrderBy = _splitOrderBy(selectFrom, orderBy);
055
056 String innerOrderBy = splitOrderBy[0];
057 String outerOrderBy = splitOrderBy[1];
058
059 String innerSelectFrom = _getInnerSelectFrom(
060 selectFrom, innerOrderBy, limit);
061
062 StringBundler sb = new StringBundler(13);
063
064 sb.append("select * from (");
065 sb.append("select *, row_number() over (");
066 sb.append(outerOrderBy);
067 sb.append(") as _page_row_num from (");
068 sb.append(innerSelectFrom);
069 sb.append(selectFromWhere);
070 sb.append(innerOrderBy);
071 sb.append(" ) _temp_table_1 ) _temp_table_2");
072 sb.append(" where _page_row_num between ");
073 sb.append(offset + 1);
074 sb.append(" and ");
075 sb.append(limit);
076 sb.append(" order by _page_row_num");
077
078 return sb.toString();
079 }
080
081 private static String _getInnerSelectFrom(
082 String selectFrom, String innerOrderBy, int limit) {
083
084 String innerSelectFrom = selectFrom;
085
086 if (Validator.isNotNull(innerOrderBy)) {
087 Matcher matcher = _selectPattern.matcher(innerSelectFrom);
088
089 innerSelectFrom = matcher.replaceAll(
090 "select top ".concat(String.valueOf(limit)).concat(
091 StringPool.SPACE));
092 }
093
094 return innerSelectFrom;
095 }
096
097 private static final String[] _splitOrderBy(
098 String selectFrom, String orderBy) {
099
100 StringBundler innerOrderBySB = new StringBundler();
101 StringBundler outerOrderBySB = new StringBundler();
102
103 String[] orderByColumns = StringUtil.split(orderBy, CharPool.COMMA);
104
105 for (String orderByColumn : orderByColumns) {
106 orderByColumn = orderByColumn.trim();
107
108 String orderByColumnName = orderByColumn;
109 String orderByType = "ASC";
110
111 int spacePos = orderByColumn.lastIndexOf(CharPool.SPACE);
112
113 if (spacePos != -1) {
114 int parenPos = orderByColumn.indexOf(
115 CharPool.OPEN_PARENTHESIS, spacePos);
116
117 if (parenPos == -1) {
118 orderByColumnName = orderByColumn.substring(0, spacePos);
119 orderByType = orderByColumn.substring(spacePos + 1);
120 }
121 }
122
123 String patternString = "\\Q".concat(orderByColumnName).concat(
124 "\\E as (\\w+)");
125
126 Pattern pattern = Pattern.compile(
127 patternString, Pattern.CASE_INSENSITIVE);
128
129 Matcher matcher = pattern.matcher(selectFrom);
130
131 if (matcher.find()) {
132 orderByColumnName = matcher.group(1);
133 }
134
135 if (selectFrom.contains(orderByColumnName)) {
136 if (outerOrderBySB.length() == 0) {
137 outerOrderBySB.append(" order by ");
138 }
139 else {
140 outerOrderBySB.append(StringPool.COMMA);
141 }
142
143 matcher = _qualifiedColumnPattern.matcher(orderByColumnName);
144
145 orderByColumnName = matcher.replaceAll("$1");
146
147 outerOrderBySB.append(orderByColumnName);
148 outerOrderBySB.append(StringPool.SPACE);
149 outerOrderBySB.append(orderByType);
150 }
151 else {
152 if (innerOrderBySB.length() == 0) {
153 innerOrderBySB.append(" order by ");
154 }
155 else {
156 innerOrderBySB.append(StringPool.COMMA);
157 }
158
159 innerOrderBySB.append(orderByColumnName);
160 innerOrderBySB.append(StringPool.SPACE);
161 innerOrderBySB.append(orderByType);
162 }
163 }
164
165 if (outerOrderBySB.length() == 0) {
166 outerOrderBySB.append(" order by CURRENT_TIMESTAMP");
167 }
168
169 return new String[] {
170 innerOrderBySB.toString(), outerOrderBySB.toString()
171 };
172 }
173
174 private static final Pattern _qualifiedColumnPattern = Pattern.compile(
175 "\\w+\\.([\\w\\*]+)");
176 private static final Pattern _selectPattern = Pattern.compile(
177 "SELECT ", Pattern.CASE_INSENSITIVE);
178
179 }