-
Notifications
You must be signed in to change notification settings - Fork 3.5k
/
SQLServer2005Dialect.java
258 lines (227 loc) · 8.81 KB
/
SQLServer2005Dialect.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
/*
* Hibernate, Relational Persistence for Idiomatic Java
*
* Copyright (c) 2010, Red Hat Inc. or third-party contributors as
* indicated by the @author tags or express copyright attribution
* statements applied by the authors. All third-party contributions are
* distributed under license by Red Hat Inc.
*
* This copyrighted material is made available to anyone wishing to use, modify,
* copy, or redistribute it subject to the terms and conditions of the GNU
* Lesser General Public License, as published by the Free Software Foundation.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
* or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License
* for more details.
*
* You should have received a copy of the GNU Lesser General Public License
* along with this distribution; if not, write to:
* Free Software Foundation, Inc.
* 51 Franklin Street, Fifth Floor
* Boston, MA 02110-1301 USA
*/
package org.hibernate.dialect;
import java.sql.Types;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.hibernate.LockMode;
import org.hibernate.dialect.function.NoArgSQLFunction;
import org.hibernate.type.StandardBasicTypes;
/**
* A dialect for Microsoft SQL 2005. (HHH-3936 fix)
*
* @author Yoryos Valotasios
*/
public class SQLServer2005Dialect extends SQLServerDialect {
private static final String SELECT = "select";
private static final String FROM = "from";
private static final String DISTINCT = "distinct";
private static final String ORDER_BY = "order by";
private static final int MAX_LENGTH = 8000;
/**
* Regular expression for stripping alias
*/
private static final Pattern ALIAS_PATTERN = Pattern.compile( "\\sas\\s[^,]+(,?)" );
public SQLServer2005Dialect() {
// HHH-3965 fix
// As per http://www.sql-server-helper.com/faq/sql-server-2005-varchar-max-p01.aspx
// use varchar(max) and varbinary(max) instead of TEXT and IMAGE types
registerColumnType( Types.BLOB, "varbinary(MAX)" );
registerColumnType( Types.VARBINARY, "varbinary(MAX)" );
registerColumnType( Types.VARBINARY, MAX_LENGTH, "varbinary($l)" );
registerColumnType( Types.LONGVARBINARY, "varbinary(MAX)" );
registerColumnType( Types.CLOB, "varchar(MAX)" );
registerColumnType( Types.LONGVARCHAR, "varchar(MAX)" );
registerColumnType( Types.VARCHAR, "varchar(MAX)" );
registerColumnType( Types.VARCHAR, MAX_LENGTH, "varchar($l)" );
registerColumnType( Types.BIGINT, "bigint" );
registerColumnType( Types.BIT, "bit" );
registerColumnType( Types.BOOLEAN, "bit" );
registerFunction( "row_number", new NoArgSQLFunction( "row_number", StandardBasicTypes.INTEGER, true ) );
}
@Override
public boolean supportsLimitOffset() {
return true;
}
@Override
public boolean bindLimitParametersFirst() {
return false;
}
@Override
public boolean supportsVariableLimit() {
return true;
}
@Override
public int convertToFirstRowValue(int zeroBasedFirstResult) {
// Our dialect paginated results aren't zero based. The first row should get the number 1 and so on
return zeroBasedFirstResult + 1;
}
@Override
public String getLimitString(String query, int offset, int limit) {
// We transform the query to one with an offset and limit if we have an offset and limit to bind
if ( offset > 1 || limit > 1 ) {
return getLimitString( query, true );
}
return query;
}
/**
* Add a LIMIT clause to the given SQL SELECT (HHH-2655: ROW_NUMBER for Paging)
*
* The LIMIT SQL will look like:
*
* <pre>
* WITH query AS (
* SELECT ROW_NUMBER() OVER (ORDER BY orderby) as __hibernate_row_nr__,
* original_query_without_orderby
* )
* SELECT * FROM query WHERE __hibernate_row_nr__ BEETWIN offset AND offset + last
* </pre>
*
* @param querySqlString The SQL statement to base the limit query off of.
* @param hasOffset Is the query requesting an offset?
*
* @return A new SQL statement with the LIMIT clause applied.
*/
@Override
public String getLimitString(String querySqlString, boolean hasOffset) {
StringBuilder sb = new StringBuilder( querySqlString.trim() );
int orderByIndex = shallowIndexOfWord( sb, ORDER_BY, 0 );
CharSequence orderby = orderByIndex > 0 ? sb.subSequence( orderByIndex, sb.length() )
: "ORDER BY CURRENT_TIMESTAMP";
// Delete the order by clause at the end of the query
if ( orderByIndex > 0 ) {
sb.delete( orderByIndex, orderByIndex + orderby.length() );
}
// HHH-5715 bug fix
replaceDistinctWithGroupBy( sb );
insertRowNumberFunction( sb, orderby );
// Wrap the query within a with statement:
sb.insert( 0, "WITH query AS (" ).append( ") SELECT * FROM query " );
sb.append( "WHERE __hibernate_row_nr__ >= ? AND __hibernate_row_nr__ < ?" );
return sb.toString();
}
/**
* Utility method that checks if the given sql query is a select distinct one and if so replaces the distinct select
* with an equivalent simple select with a group by clause.
*
* @param sql an sql query
*/
protected static void replaceDistinctWithGroupBy(StringBuilder sql) {
int distinctIndex = shallowIndexOfWord( sql, DISTINCT, 0 );
int selectEndIndex = shallowIndexOfWord( sql, FROM, 0 );
if (distinctIndex > 0 && distinctIndex < selectEndIndex) {
sql.delete( distinctIndex, distinctIndex + DISTINCT.length() + " ".length());
sql.append( " group by" ).append( getSelectFieldsWithoutAliases( sql ) );
}
}
public static final String SELECT_WITH_SPACE = SELECT + ' ';
/**
* This utility method searches the given sql query for the fields of the select statement and returns them without
* the aliases.
*
* @param sql sql query
*
* @return the fields of the select statement without their alias
*/
protected static CharSequence getSelectFieldsWithoutAliases(StringBuilder sql) {
final int selectStartPos = shallowIndexOf( sql, SELECT_WITH_SPACE, 0 );
final int fromStartPos = shallowIndexOfWord( sql, FROM, selectStartPos );
String select = sql.substring( selectStartPos + SELECT.length(), fromStartPos );
// Strip the as clauses
return stripAliases( select );
}
/**
* Utility method that strips the aliases.
*
* @param str string to replace the as statements
*
* @return a string without the as statements
*/
protected static String stripAliases(String str) {
Matcher matcher = ALIAS_PATTERN.matcher( str );
return matcher.replaceAll( "$1" );
}
/**
* Right after the select statement of a given query we must place the row_number function
*
* @param sql the initial sql query without the order by clause
* @param orderby the order by clause of the query
*/
protected void insertRowNumberFunction(StringBuilder sql, CharSequence orderby) {
// Find the end of the select statement
int selectEndIndex = shallowIndexOfWord( sql, FROM, 0 );
// Insert after the select statement the row_number() function:
sql.insert( selectEndIndex - 1, ", ROW_NUMBER() OVER (" + orderby + ") as __hibernate_row_nr__" );
}
@Override // since SQLServer2005 the nowait hint is supported
public String appendLockHint(LockMode mode, String tableName) {
if ( mode == LockMode.UPGRADE_NOWAIT ) {
return tableName + " with (updlock, rowlock, nowait)";
}
return super.appendLockHint( mode, tableName );
}
/**
* Returns index of the first case-insensitive match of search term surrounded by spaces
* that is not enclosed in parentheses.
*
* @param sb String to search.
* @param search Search term.
* @param fromIndex The index from which to start the search.
* @return Position of the first match, or {@literal -1} if not found.
*/
private static int shallowIndexOfWord(final StringBuilder sb, final String search, int fromIndex) {
final int index = shallowIndexOf( sb, ' ' + search + ' ', fromIndex );
return index != -1 ? ( index + 1 ) : -1; // In cas of match adding one because of space placed in front of search term.
}
/**
* Returns index of the first case-insensitive match of search term that is not enclosed in parentheses.
*
* @param sb String to search.
* @param search Search term.
* @param fromIndex The index from which to start the search.
* @return Position of the first match, or {@literal -1} if not found.
*/
private static int shallowIndexOf(StringBuilder sb, String search, int fromIndex) {
final String lowercase = sb.toString().toLowerCase(); // case-insensitive match
final int len = lowercase.length();
final int searchlen = search.length();
int pos = -1, depth = 0, cur = fromIndex;
do {
pos = lowercase.indexOf( search, cur );
if ( pos != -1 ) {
for ( int iter = cur; iter < pos; iter++ ) {
char c = sb.charAt( iter );
if ( c == '(' ) {
depth = depth + 1;
}
else if ( c == ')' ) {
depth = depth - 1;
}
}
cur = pos + searchlen;
}
} while ( cur < len && depth != 0 && pos != -1 );
return depth == 0 ? pos : -1;
}
}