MySQLDialect


1   //$Id: MySQLDialect.java,v 1.33 2005/07/04 02:54:27 oneovthafew Exp $
2   package org.hibernate.dialect;
3   
4   import java.sql.Types  ;
5   
6   import org.hibernate.Hibernate;
7   import org.hibernate.cfg.Environment;
8   import org.hibernate.dialect.function.NoArgSQLFunction;
9   import org.hibernate.dialect.function.StandardSQLFunction;
10  import org.hibernate.exception.ErrorCodeConverter;
11  import org.hibernate.exception.SQLExceptionConverter;
12  import org.hibernate.exception.ViolatedConstraintNameExtracter;
13  import org.hibernate.util.StringHelper;
14  
15  /**
16   * An SQL dialect for MySQL.
17   * @author Gavin King
18   */
19  public class MySQLDialect extends Dialect {
20  
21      public MySQLDialect() {
22          super();
23          registerColumnType( Types.BIT, "bit" );
24          registerColumnType( Types.BIGINT, "bigint" );
25          registerColumnType( Types.SMALLINT, "smallint" );
26          registerColumnType( Types.TINYINT, "tinyint" );
27          registerColumnType( Types.INTEGER, "integer" );
28          registerColumnType( Types.CHAR, "char(1)" );
29          registerColumnType( Types.VARCHAR, "longtext" );
30          registerColumnType( Types.VARCHAR, 16777215, "mediumtext" );
31          registerColumnType( Types.VARCHAR, 65535, "text" );
32          registerColumnType( Types.VARCHAR, 255, "varchar($l)" );
33          registerColumnType( Types.FLOAT, "float" );
34          registerColumnType( Types.DOUBLE, "double precision" );
35          registerColumnType( Types.DATE, "date" );
36          registerColumnType( Types.TIME, "time" );
37          registerColumnType( Types.TIMESTAMP, "datetime" );
38          registerColumnType( Types.VARBINARY, "longblob" );
39          registerColumnType( Types.VARBINARY, 16777215, "mediumblob" );
40          registerColumnType( Types.VARBINARY, 65535, "blob" );
41          registerColumnType( Types.VARBINARY, 255, "tinyblob" );
42          registerColumnType( Types.NUMERIC, "numeric($p,$s)" );
43          registerColumnType( Types.BLOB, "longblob" );
44          registerColumnType( Types.BLOB, 16777215, "mediumblob" );
45          registerColumnType( Types.BLOB, 65535, "blob" );
46          registerColumnType( Types.CLOB, "longtext" );
47          registerColumnType( Types.CLOB, 16777215, "mediumtext" );
48          registerColumnType( Types.CLOB, 65535, "text" );
49  
50          registerFunction("ascii", new StandardSQLFunction("ascii", Hibernate.INTEGER) );
51          registerFunction("bin", new StandardSQLFunction("bin", Hibernate.STRING) );
52          registerFunction("char_length", new StandardSQLFunction("char_length", Hibernate.LONG) );
53          registerFunction("character_length", new StandardSQLFunction("character_length", Hibernate.LONG) );
54          registerFunction("lcase", new StandardSQLFunction("lcase") );
55          registerFunction("lower", new StandardSQLFunction("lower") );
56          registerFunction("length", new StandardSQLFunction("length", Hibernate.LONG) );
57          registerFunction("ltrim", new StandardSQLFunction("ltrim") );
58          registerFunction("ord", new StandardSQLFunction("ord", Hibernate.INTEGER) );
59          registerFunction("quote", new StandardSQLFunction("quote") );
60          registerFunction("reverse", new StandardSQLFunction("reverse") );
61          registerFunction("rtrim", new StandardSQLFunction("rtrim") );
62          registerFunction("soundex", new StandardSQLFunction("soundex") );
63          registerFunction("space", new StandardSQLFunction("space", Hibernate.STRING) );
64          registerFunction("ucase", new StandardSQLFunction("ucase") );
65          registerFunction("upper", new StandardSQLFunction("upper") );
66          registerFunction("unhex", new StandardSQLFunction("unhex", Hibernate.STRING) );
67  
68          registerFunction("abs", new StandardSQLFunction("abs") );
69          registerFunction("sign", new StandardSQLFunction("sign", Hibernate.INTEGER) );
70  
71          registerFunction("acos", new StandardSQLFunction("acos", Hibernate.DOUBLE) );
72          registerFunction("asin", new StandardSQLFunction("asin", Hibernate.DOUBLE) );
73          registerFunction("atan", new StandardSQLFunction("atan", Hibernate.DOUBLE) );
74          registerFunction("cos", new StandardSQLFunction("cos", Hibernate.DOUBLE) );
75          registerFunction("cot", new StandardSQLFunction("cot", Hibernate.DOUBLE) );
76          registerFunction("crc32", new StandardSQLFunction("crc32", Hibernate.LONG) );
77          registerFunction("exp", new StandardSQLFunction("exp", Hibernate.DOUBLE) );
78          registerFunction("ln", new StandardSQLFunction("ln", Hibernate.DOUBLE) );
79          registerFunction("log", new StandardSQLFunction("log", Hibernate.DOUBLE) );
80          registerFunction("log2", new StandardSQLFunction("log2", Hibernate.DOUBLE) );
81          registerFunction("log10", new StandardSQLFunction("log10", Hibernate.DOUBLE) );
82          registerFunction("pi", new NoArgSQLFunction("pi", Hibernate.DOUBLE) );
83          registerFunction("rand", new NoArgSQLFunction("rand", Hibernate.DOUBLE) );
84          registerFunction("sin", new StandardSQLFunction("sin", Hibernate.DOUBLE) );
85          registerFunction("sqrt", new StandardSQLFunction("sqrt", Hibernate.DOUBLE) );
86          registerFunction("tan", new StandardSQLFunction("tan", Hibernate.DOUBLE) );
87  
88          registerFunction("radians", new StandardSQLFunction("radians", Hibernate.DOUBLE) );
89          registerFunction("degrees", new StandardSQLFunction("degrees", Hibernate.DOUBLE) );
90  
91          registerFunction("ceiling", new StandardSQLFunction("ceiling", Hibernate.INTEGER) );
92          registerFunction("ceil", new StandardSQLFunction("ceil", Hibernate.INTEGER) );
93          registerFunction("floor", new StandardSQLFunction("floor", Hibernate.INTEGER) );
94          registerFunction("round", new StandardSQLFunction("round", Hibernate.INTEGER) );
95  
96          registerFunction("curdate", new NoArgSQLFunction("curdate", Hibernate.DATE) );
97          registerFunction("curtime", new NoArgSQLFunction("curtime", Hibernate.TIME) );
98          registerFunction("current_date", new NoArgSQLFunction("current_date", Hibernate.DATE, false) );
99          registerFunction("current_time", new NoArgSQLFunction("current_time", Hibernate.TIME, false) );
100         registerFunction("current_timestamp", new NoArgSQLFunction("current_timestamp", Hibernate.TIMESTAMP, false) );
101         registerFunction("date", new StandardSQLFunction("date", Hibernate.DATE) );
102         registerFunction("day", new StandardSQLFunction("day", Hibernate.INTEGER) );
103         registerFunction("dayofmonth", new StandardSQLFunction("dayofmonth", Hibernate.INTEGER) );
104         registerFunction("dayname", new StandardSQLFunction("dayname", Hibernate.STRING) );
105         registerFunction("dayofweek", new StandardSQLFunction("dayofweek", Hibernate.INTEGER) );
106         registerFunction("dayofyear", new StandardSQLFunction("dayofyear", Hibernate.INTEGER) );
107         registerFunction("from_days", new StandardSQLFunction("from_days", Hibernate.DATE) );
108         registerFunction("from_unixtime", new StandardSQLFunction("from_unixtime", Hibernate.TIMESTAMP) );
109         registerFunction("hour", new StandardSQLFunction("hour", Hibernate.INTEGER) );
110         registerFunction("last_day", new StandardSQLFunction("last_day", Hibernate.DATE) );
111         registerFunction("localtime", new NoArgSQLFunction("localtime", Hibernate.TIMESTAMP) );
112         registerFunction("localtimestamp", new NoArgSQLFunction("localtimestamp", Hibernate.TIMESTAMP) );
113         registerFunction("microseconds", new StandardSQLFunction("microseconds", Hibernate.INTEGER) );
114         registerFunction("minute", new StandardSQLFunction("minute", Hibernate.INTEGER) );
115         registerFunction("month", new StandardSQLFunction("month", Hibernate.INTEGER) );
116         registerFunction("monthname", new StandardSQLFunction("monthname", Hibernate.STRING) );
117         registerFunction("now", new NoArgSQLFunction("now", Hibernate.TIMESTAMP) );
118         registerFunction("quarter", new StandardSQLFunction("quarter", Hibernate.INTEGER) );
119         registerFunction("second", new StandardSQLFunction("second", Hibernate.INTEGER) );
120         registerFunction("sec_to_time", new StandardSQLFunction("sec_to_time", Hibernate.TIME) );
121         registerFunction("sysdate", new NoArgSQLFunction("sysdate", Hibernate.TIMESTAMP) );
122         registerFunction("time", new StandardSQLFunction("time", Hibernate.TIME) );
123         registerFunction("timestamp", new StandardSQLFunction("timestamp", Hibernate.TIMESTAMP) );
124         registerFunction("time_to_sec", new StandardSQLFunction("time_to_sec", Hibernate.INTEGER) );
125         registerFunction("to_days", new StandardSQLFunction("to_days", Hibernate.LONG) );
126         registerFunction("unix_timestamp", new StandardSQLFunction("unix_timestamp", Hibernate.LONG) );
127         registerFunction("utc_date", new NoArgSQLFunction("utc_date", Hibernate.STRING) );
128         registerFunction("utc_time", new NoArgSQLFunction("utc_time", Hibernate.STRING) );
129         registerFunction("utc_timestamp", new NoArgSQLFunction("utc_timestamp", Hibernate.STRING) );
130         registerFunction("week", new StandardSQLFunction("week", Hibernate.INTEGER) );
131         registerFunction("weekday", new StandardSQLFunction("weekday", Hibernate.INTEGER) );
132         registerFunction("weekofyear", new StandardSQLFunction("weekofyear", Hibernate.INTEGER) );
133         registerFunction("year", new StandardSQLFunction("year", Hibernate.INTEGER) );
134         registerFunction("yearweek", new StandardSQLFunction("yearweek", Hibernate.INTEGER) );
135 
136         registerFunction("hex", new StandardSQLFunction("hex", Hibernate.STRING) );
137         registerFunction("oct", new StandardSQLFunction("oct", Hibernate.STRING) );
138 
139         registerFunction("octet_length", new StandardSQLFunction("octet_length", Hibernate.LONG) );
140         registerFunction("bit_length", new StandardSQLFunction("bit_length", Hibernate.LONG) );
141 
142         registerFunction("bit_count", new StandardSQLFunction("bit_count", Hibernate.LONG) );
143         registerFunction("encrypt", new StandardSQLFunction("encrypt", Hibernate.STRING) );
144         registerFunction("md5", new StandardSQLFunction("md5", Hibernate.STRING) );
145         registerFunction("sha1", new StandardSQLFunction("sha1", Hibernate.STRING) );
146         registerFunction("sha", new StandardSQLFunction("sha", Hibernate.STRING) );
147 
148         registerFunction( "concat", new StandardSQLFunction( "concat", Hibernate.STRING ) );
149 
150         getDefaultProperties().setProperty(Environment.MAX_FETCH_DEPTH, "2");
151         getDefaultProperties().setProperty(Environment.STATEMENT_BATCH_SIZE, DEFAULT_BATCH_SIZE);
152     }
153 
154     public String   getAddColumnString() {
155         return "add column";
156     }
157     
158     public boolean qualifyIndexName() {
159         return false;
160     }
161 
162     public boolean supportsIdentityColumns() {
163         return true;
164     }
165     
166     public String   getIdentitySelectString() {
167         return "select last_insert_id()";
168     }
169 
170     public String   getIdentityColumnString() {
171         return "not null auto_increment"; //starts with 1, implicitly
172     }
173 
174     public String   getAddForeignKeyConstraintString(
175             String   constraintName, 
176             String  [] foreignKey, 
177             String   referencedTable, 
178             String  [] primaryKey, boolean referencesPrimaryKey
179     ) {
180         String   cols = StringHelper.join(", ", foreignKey);
181         return new StringBuffer  (30)
182             .append(" add index ")
183             .append(constraintName)
184             .append(" (")
185             .append(cols)
186             .append("), add constraint ")
187             .append(constraintName)
188             .append(" foreign key (")
189             .append(cols)
190             .append(") references ")
191             .append(referencedTable)
192             .append(" (")
193             .append( StringHelper.join(", ", primaryKey) )
194             .append(')')
195             .toString();
196     }
197 
198     public boolean supportsLimit() {
199         return true;
200     }
201     
202     public String   getDropForeignKeyString() {
203         return " drop foreign key ";
204     }
205 
206     public String   getLimitString(String   sql, boolean hasOffset) {
207         return new StringBuffer  ( sql.length()+20 )
208             .append(sql)
209             .append( hasOffset ? " limit ?, ?" : " limit ?")
210             .toString();
211     }
212     
213     /*
214      * Temporary, until MySQL fix Connector/J bug
215      */
216     /*public String getLimitString(String sql, int offset, int limit) {
217         StringBuffer buf = new StringBuffer( sql.length()+20 )
218             .append(sql);
219         if (offset>0) {
220             buf.append(" limit ")
221                 .append(offset)
222                 .append(", ")
223                 .append(limit);
224         }
225         else {
226             buf.append(" limit ")
227                 .append(limit);
228         }
229         return buf.toString();
230     }*/
231 
232     /*
233      * Temporary, until MySQL fix Connector/J bug
234      */
235     /*public boolean supportsVariableLimit() {
236         return false;
237     }*/
238 
239     public char closeQuote() {
240         return '`';
241     }
242 
243     public char openQuote() {
244         return '`';
245     }
246 
247     public boolean supportsIfExistsBeforeTableName() {
248         return true;
249     }
250 
251     public char getSchemaSeparator() {
252       return '_';
253     }
254 
255     public String   getSelectGUIDString() {
256         return "select uuid()";
257     }
258 
259     public boolean supportsCascadeDelete() {
260         return false;
261     }
262     
263     public String   getTableComment(String   comment) {
264         return " comment='" + comment + "'";
265     }
266 
267     public String   getColumnComment(String   comment) {
268         return " comment '" + comment + "'";
269     }
270 
271     public boolean supportsTemporaryTables() {
272         return true;
273     }
274 
275     public String   getTemporaryTableCreationCommand() {
276         return "create temporary table if not exists";
277     }
278 
279     public String   getCastTypeName(int code) {
280         if ( code==Types.INTEGER ) {
281             return "signed";
282         }
283         else {
284             return super.getCastTypeName( code );
285         }
286     }
287 
288     /**
289      * Build an instance of the SQLExceptionConverter preferred by this dialect for
290      * converting SQLExceptions into Hibernate's JDBCException hierarchy.  The default
291      * Dialect implementation simply returns a converter based on X/Open SQLState codes.
292      * <p/>
293      * It is strongly recommended that specific Dialect implementations override this
294      * method, since interpretation of a SQL error is much more accurate when based on
295      * the ErrorCode rather than the SQLState.  Unfortunately, the ErrorCode is a vendor-
296      * specific approach.
297      *
298      * @return The Dialect's preferred SQLExceptionConverter.
299      */
300     public SQLExceptionConverter buildSQLExceptionConverter() {
301         return new ExceptionConverter( getViolatedConstraintNameExtracter() );
302     }
303 
304     private static class ExceptionConverter extends ErrorCodeConverter {
305         private int[] sqlGrammarCodes = new int[] { 1054, 1064, 1146 };
306         private int[] integrityViolationCodes = new int[] { 1062, 1216, 1217 };
307         private int[] connectionCodes = new int[] { 1049 };
308         private int[] lockAcquisitionErrorCodes = new int[] { 1099, 1100, 1150, 1165, 1192, 1205, 1206, 1207, 1213, 1223 };
309 
310         public ExceptionConverter(ViolatedConstraintNameExtracter extracter) {
311             super(extracter);
312         }
313 
314         protected int[] getSQLGrammarErrorCodes() {
315             return sqlGrammarCodes;
316         }
317 
318         protected int[] getIntegrityViolationErrorCodes() {
319             return integrityViolationCodes;
320         }
321 
322         protected int[] getConnectionErrorCodes() {
323             return connectionCodes;
324         }
325 
326         protected int[] getLockAcquisitionErrorCodes() {
327             return lockAcquisitionErrorCodes;
328         }
329     }
330 }
331
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags