1 /*
2 * Hibernate, Relational Persistence for Idiomatic Java
3 *
4 * Copyright (c) 2008, Red Hat Middleware LLC or third-party contributors as
5 * indicated by the @author tags or express copyright attribution
6 * statements applied by the authors. All third-party contributions are
7 * distributed under license by Red Hat Middleware LLC.
8 *
9 * This copyrighted material is made available to anyone wishing to use, modify,
10 * copy, or redistribute it subject to the terms and conditions of the GNU
11 * Lesser General Public License, as published by the Free Software Foundation.
12 *
13 * This program is distributed in the hope that it will be useful,
14 * but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
15 * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License
16 * for more details.
17 *
18 * You should have received a copy of the GNU Lesser General Public License
19 * along with this distribution; if not, write to:
20 * Free Software Foundation, Inc.
21 * 51 Franklin Street, Fifth Floor
22 * Boston, MA 02110-1301 USA
23 *
24 */
25 package org.hibernate.dialect;
26
27 import java.sql.CallableStatement;
28 import java.sql.ResultSet;
29 import java.sql.SQLException;
30 import java.sql.Types;
31
32 import org.hibernate.Hibernate;
33 import org.hibernate.cfg.Environment;
34 import org.hibernate.dialect.function.NoArgSQLFunction;
35 import org.hibernate.dialect.function.StandardSQLFunction;
36 import org.hibernate.util.StringHelper;
37
38 /**
39 * An SQL dialect for MySQL (prior to 5.x).
40 *
41 * @author Gavin King
42 */
43 public class MySQLDialect extends Dialect {
44
45 public MySQLDialect() {
46 super();
47 registerColumnType( Types.BIT, "bit" );
48 registerColumnType( Types.BIGINT, "bigint" );
49 registerColumnType( Types.SMALLINT, "smallint" );
50 registerColumnType( Types.TINYINT, "tinyint" );
51 registerColumnType( Types.INTEGER, "integer" );
52 registerColumnType( Types.CHAR, "char(1)" );
53 registerColumnType( Types.FLOAT, "float" );
54 registerColumnType( Types.DOUBLE, "double precision" );
55 registerColumnType( Types.DATE, "date" );
56 registerColumnType( Types.TIME, "time" );
57 registerColumnType( Types.TIMESTAMP, "datetime" );
58 registerColumnType( Types.VARBINARY, "longblob" );
59 registerColumnType( Types.VARBINARY, 16777215, "mediumblob" );
60 registerColumnType( Types.VARBINARY, 65535, "blob" );
61 registerColumnType( Types.VARBINARY, 255, "tinyblob" );
62 registerColumnType( Types.NUMERIC, "decimal($p,$s)" );
63 registerColumnType( Types.BLOB, "longblob" );
64 // registerColumnType( Types.BLOB, 16777215, "mediumblob" );
65 // registerColumnType( Types.BLOB, 65535, "blob" );
66 registerColumnType( Types.CLOB, "longtext" );
67 // registerColumnType( Types.CLOB, 16777215, "mediumtext" );
68 // registerColumnType( Types.CLOB, 65535, "text" );
69 registerVarcharTypes();
70
71 registerFunction("ascii", new StandardSQLFunction("ascii", Hibernate.INTEGER) );
72 registerFunction("bin", new StandardSQLFunction("bin", Hibernate.STRING) );
73 registerFunction("char_length", new StandardSQLFunction("char_length", Hibernate.LONG) );
74 registerFunction("character_length", new StandardSQLFunction("character_length", Hibernate.LONG) );
75 registerFunction("lcase", new StandardSQLFunction("lcase") );
76 registerFunction("lower", new StandardSQLFunction("lower") );
77 registerFunction("length", new StandardSQLFunction("length", Hibernate.LONG) );
78 registerFunction("ltrim", new StandardSQLFunction("ltrim") );
79 registerFunction("ord", new StandardSQLFunction("ord", Hibernate.INTEGER) );
80 registerFunction("quote", new StandardSQLFunction("quote") );
81 registerFunction("reverse", new StandardSQLFunction("reverse") );
82 registerFunction("rtrim", new StandardSQLFunction("rtrim") );
83 registerFunction("soundex", new StandardSQLFunction("soundex") );
84 registerFunction("space", new StandardSQLFunction("space", Hibernate.STRING) );
85 registerFunction("ucase", new StandardSQLFunction("ucase") );
86 registerFunction("upper", new StandardSQLFunction("upper") );
87 registerFunction("unhex", new StandardSQLFunction("unhex", Hibernate.STRING) );
88
89 registerFunction("abs", new StandardSQLFunction("abs") );
90 registerFunction("sign", new StandardSQLFunction("sign", Hibernate.INTEGER) );
91
92 registerFunction("acos", new StandardSQLFunction("acos", Hibernate.DOUBLE) );
93 registerFunction("asin", new StandardSQLFunction("asin", Hibernate.DOUBLE) );
94 registerFunction("atan", new StandardSQLFunction("atan", Hibernate.DOUBLE) );
95 registerFunction("cos", new StandardSQLFunction("cos", Hibernate.DOUBLE) );
96 registerFunction("cot", new StandardSQLFunction("cot", Hibernate.DOUBLE) );
97 registerFunction("crc32", new StandardSQLFunction("crc32", Hibernate.LONG) );
98 registerFunction("exp", new StandardSQLFunction("exp", Hibernate.DOUBLE) );
99 registerFunction("ln", new StandardSQLFunction("ln", Hibernate.DOUBLE) );
100 registerFunction("log", new StandardSQLFunction("log", Hibernate.DOUBLE) );
101 registerFunction("log2", new StandardSQLFunction("log2", Hibernate.DOUBLE) );
102 registerFunction("log10", new StandardSQLFunction("log10", Hibernate.DOUBLE) );
103 registerFunction("pi", new NoArgSQLFunction("pi", Hibernate.DOUBLE) );
104 registerFunction("rand", new NoArgSQLFunction("rand", Hibernate.DOUBLE) );
105 registerFunction("sin", new StandardSQLFunction("sin", Hibernate.DOUBLE) );
106 registerFunction("sqrt", new StandardSQLFunction("sqrt", Hibernate.DOUBLE) );
107 registerFunction("tan", new StandardSQLFunction("tan", Hibernate.DOUBLE) );
108
109 registerFunction("radians", new StandardSQLFunction("radians", Hibernate.DOUBLE) );
110 registerFunction("degrees", new StandardSQLFunction("degrees", Hibernate.DOUBLE) );
111
112 registerFunction("ceiling", new StandardSQLFunction("ceiling", Hibernate.INTEGER) );
113 registerFunction("ceil", new StandardSQLFunction("ceil", Hibernate.INTEGER) );
114 registerFunction("floor", new StandardSQLFunction("floor", Hibernate.INTEGER) );
115 registerFunction("round", new StandardSQLFunction("round", Hibernate.INTEGER) );
116
117 registerFunction("datediff", new StandardSQLFunction("datediff", Hibernate.INTEGER) );
118 registerFunction("timediff", new StandardSQLFunction("timediff", Hibernate.TIME) );
119 registerFunction("date_format", new StandardSQLFunction("date_format", Hibernate.STRING) );
120
121 registerFunction("curdate", new NoArgSQLFunction("curdate", Hibernate.DATE) );
122 registerFunction("curtime", new NoArgSQLFunction("curtime", Hibernate.TIME) );
123 registerFunction("current_date", new NoArgSQLFunction("current_date", Hibernate.DATE, false) );
124 registerFunction("current_time", new NoArgSQLFunction("current_time", Hibernate.TIME, false) );
125 registerFunction("current_timestamp", new NoArgSQLFunction("current_timestamp", Hibernate.TIMESTAMP, false) );
126 registerFunction("date", new StandardSQLFunction("date", Hibernate.DATE) );
127 registerFunction("day", new StandardSQLFunction("day", Hibernate.INTEGER) );
128 registerFunction("dayofmonth", new StandardSQLFunction("dayofmonth", Hibernate.INTEGER) );
129 registerFunction("dayname", new StandardSQLFunction("dayname", Hibernate.STRING) );
130 registerFunction("dayofweek", new StandardSQLFunction("dayofweek", Hibernate.INTEGER) );
131 registerFunction("dayofyear", new StandardSQLFunction("dayofyear", Hibernate.INTEGER) );
132 registerFunction("from_days", new StandardSQLFunction("from_days", Hibernate.DATE) );
133 registerFunction("from_unixtime", new StandardSQLFunction("from_unixtime", Hibernate.TIMESTAMP) );
134 registerFunction("hour", new StandardSQLFunction("hour", Hibernate.INTEGER) );
135 registerFunction("last_day", new StandardSQLFunction("last_day", Hibernate.DATE) );
136 registerFunction("localtime", new NoArgSQLFunction("localtime", Hibernate.TIMESTAMP) );
137 registerFunction("localtimestamp", new NoArgSQLFunction("localtimestamp", Hibernate.TIMESTAMP) );
138 registerFunction("microseconds", new StandardSQLFunction("microseconds", Hibernate.INTEGER) );
139 registerFunction("minute", new StandardSQLFunction("minute", Hibernate.INTEGER) );
140 registerFunction("month", new StandardSQLFunction("month", Hibernate.INTEGER) );
141 registerFunction("monthname", new StandardSQLFunction("monthname", Hibernate.STRING) );
142 registerFunction("now", new NoArgSQLFunction("now", Hibernate.TIMESTAMP) );
143 registerFunction("quarter", new StandardSQLFunction("quarter", Hibernate.INTEGER) );
144 registerFunction("second", new StandardSQLFunction("second", Hibernate.INTEGER) );
145 registerFunction("sec_to_time", new StandardSQLFunction("sec_to_time", Hibernate.TIME) );
146 registerFunction("sysdate", new NoArgSQLFunction("sysdate", Hibernate.TIMESTAMP) );
147 registerFunction("time", new StandardSQLFunction("time", Hibernate.TIME) );
148 registerFunction("timestamp", new StandardSQLFunction("timestamp", Hibernate.TIMESTAMP) );
149 registerFunction("time_to_sec", new StandardSQLFunction("time_to_sec", Hibernate.INTEGER) );
150 registerFunction("to_days", new StandardSQLFunction("to_days", Hibernate.LONG) );
151 registerFunction("unix_timestamp", new StandardSQLFunction("unix_timestamp", Hibernate.LONG) );
152 registerFunction("utc_date", new NoArgSQLFunction("utc_date", Hibernate.STRING) );
153 registerFunction("utc_time", new NoArgSQLFunction("utc_time", Hibernate.STRING) );
154 registerFunction("utc_timestamp", new NoArgSQLFunction("utc_timestamp", Hibernate.STRING) );
155 registerFunction("week", new StandardSQLFunction("week", Hibernate.INTEGER) );
156 registerFunction("weekday", new StandardSQLFunction("weekday", Hibernate.INTEGER) );
157 registerFunction("weekofyear", new StandardSQLFunction("weekofyear", Hibernate.INTEGER) );
158 registerFunction("year", new StandardSQLFunction("year", Hibernate.INTEGER) );
159 registerFunction("yearweek", new StandardSQLFunction("yearweek", Hibernate.INTEGER) );
160
161 registerFunction("hex", new StandardSQLFunction("hex", Hibernate.STRING) );
162 registerFunction("oct", new StandardSQLFunction("oct", Hibernate.STRING) );
163
164 registerFunction("octet_length", new StandardSQLFunction("octet_length", Hibernate.LONG) );
165 registerFunction("bit_length", new StandardSQLFunction("bit_length", Hibernate.LONG) );
166
167 registerFunction("bit_count", new StandardSQLFunction("bit_count", Hibernate.LONG) );
168 registerFunction("encrypt", new StandardSQLFunction("encrypt", Hibernate.STRING) );
169 registerFunction("md5", new StandardSQLFunction("md5", Hibernate.STRING) );
170 registerFunction("sha1", new StandardSQLFunction("sha1", Hibernate.STRING) );
171 registerFunction("sha", new StandardSQLFunction("sha", Hibernate.STRING) );
172
173 registerFunction( "concat", new StandardSQLFunction( "concat", Hibernate.STRING ) );
174
175 getDefaultProperties().setProperty(Environment.MAX_FETCH_DEPTH, "2");
176 getDefaultProperties().setProperty(Environment.STATEMENT_BATCH_SIZE, DEFAULT_BATCH_SIZE);
177 }
178
179 protected void registerVarcharTypes() {
180 registerColumnType( Types.VARCHAR, "longtext" );
181 // registerColumnType( Types.VARCHAR, 16777215, "mediumtext" );
182 // registerColumnType( Types.VARCHAR, 65535, "text" );
183 registerColumnType( Types.VARCHAR, 255, "varchar($l)" );
184 }
185
186 public String getAddColumnString() {
187 return "add column";
188 }
189
190 public boolean qualifyIndexName() {
191 return false;
192 }
193
194 public boolean supportsIdentityColumns() {
195 return true;
196 }
197
198 public String getIdentitySelectString() {
199 return "select last_insert_id()";
200 }
201
202 public String getIdentityColumnString() {
203 return "not null auto_increment"; //starts with 1, implicitly
204 }
205
206 public String getAddForeignKeyConstraintString(
207 String constraintName,
208 String[] foreignKey,
209 String referencedTable,
210 String[] primaryKey, boolean referencesPrimaryKey
211 ) {
212 String cols = StringHelper.join(", ", foreignKey);
213 return new StringBuffer(30)
214 .append(" add index ")
215 .append(constraintName)
216 .append(" (")
217 .append(cols)
218 .append("), add constraint ")
219 .append(constraintName)
220 .append(" foreign key (")
221 .append(cols)
222 .append(") references ")
223 .append(referencedTable)
224 .append(" (")
225 .append( StringHelper.join(", ", primaryKey) )
226 .append(')')
227 .toString();
228 }
229
230 public boolean supportsLimit() {
231 return true;
232 }
233
234 public String getDropForeignKeyString() {
235 return " drop foreign key ";
236 }
237
238 public String getLimitString(String sql, boolean hasOffset) {
239 return new StringBuffer( sql.length()+20 )
240 .append(sql)
241 .append( hasOffset ? " limit ?, ?" : " limit ?")
242 .toString();
243 }
244
245 /*
246 * Temporary, until MySQL fix Connector/J bug
247 */
248 /*public String getLimitString(String sql, int offset, int limit) {
249 StringBuffer buf = new StringBuffer( sql.length()+20 )
250 .append(sql);
251 if (offset>0) {
252 buf.append(" limit ")
253 .append(offset)
254 .append(", ")
255 .append(limit);
256 }
257 else {
258 buf.append(" limit ")
259 .append(limit);
260 }
261 return buf.toString();
262 }*/
263
264 /*
265 * Temporary, until MySQL fix Connector/J bug
266 */
267 /*public boolean supportsVariableLimit() {
268 return false;
269 }*/
270
271 public char closeQuote() {
272 return '`';
273 }
274
275 public char openQuote() {
276 return '`';
277 }
278
279 public boolean supportsIfExistsBeforeTableName() {
280 return true;
281 }
282
283 public String getSelectGUIDString() {
284 return "select uuid()";
285 }
286
287 public boolean supportsCascadeDelete() {
288 return false;
289 }
290
291 public String getTableComment(String comment) {
292 return " comment='" + comment + "'";
293 }
294
295 public String getColumnComment(String comment) {
296 return " comment '" + comment + "'";
297 }
298
299 public boolean supportsTemporaryTables() {
300 return true;
301 }
302
303 public String getCreateTemporaryTableString() {
304 return "create temporary table if not exists";
305 }
306
307 public String getCastTypeName(int code) {
308 if ( code==Types.INTEGER ) {
309 return "signed";
310 }
311 else if ( code==Types.VARCHAR ) {
312 return "char";
313 }
314 else if ( code==Types.VARBINARY ) {
315 return "binary";
316 }
317 else {
318 return super.getCastTypeName( code );
319 }
320 }
321
322 public boolean supportsCurrentTimestampSelection() {
323 return true;
324 }
325
326 public boolean isCurrentTimestampSelectStringCallable() {
327 return false;
328 }
329
330 public String getCurrentTimestampSelectString() {
331 return "select now()";
332 }
333
334 public int registerResultSetOutParameter(CallableStatement statement, int col) throws SQLException {
335 return col;
336 }
337
338 public ResultSet getResultSet(CallableStatement ps) throws SQLException {
339 boolean isResultSet = ps.execute();
340 while (!isResultSet && ps.getUpdateCount() != -1) {
341 isResultSet = ps.getMoreResults();
342 }
343 return ps.getResultSet();
344 }
345
346 public boolean supportsRowValueConstructorSyntax() {
347 return true;
348 }
349
350 public Boolean performTemporaryTableDDLInIsolation() {
351 return Boolean.FALSE;
352 }
353
354
355 // Overridden informational metadata ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
356
357 public boolean supportsEmptyInList() {
358 return false;
359 }
360
361 public boolean areStringComparisonsCaseInsensitive() {
362 return true;
363 }
364
365 public boolean supportsLobValueChangePropogation() {
366 // note: at least my local MySQL 5.1 install shows this not working...
367 return false;
368 }
369
370 public boolean supportsSubqueryOnMutatingTable() {
371 return false;
372 }
373 }
Save This Page