以下提供的源代码，可以让Hibernate支持Access方言：
复制
package com.hxtt.support.hibernate; import org.hibernate.Hibernate; import org.hibernate.dialect.Dialect; import org.hibernate.dialect.function.VarArgsSQLFunction; import org.hibernate.cfg.Environment; import org.hibernate.dialect.function.NoArgSQLFunction; import org.hibernate.dialect.function.StandardSQLFunction; public class HxttDialect extends Dialect { static final String DEFAULT_BATCH_SIZE = "15"; static final String NO_BATCH = "0"; public HxttDialect() { super(); //Mathematical Functions registerFunction("abs", new StandardSQLFunction("abs") ); registerFunction("ceiling", new StandardSQLFunction("ceiling", Hibernate.INTEGER) ); registerFunction("ceil", new StandardSQLFunction("ceil", Hibernate.INTEGER) ); registerFunction("sign", new StandardSQLFunction("sign", Hibernate.INTEGER) ); registerFunction("degrees", new StandardSQLFunction("degrees", Hibernate.DOUBLE) ); registerFunction("exp", new StandardSQLFunction("exp", Hibernate.DOUBLE) ); registerFunction("floor", new StandardSQLFunction("floor", Hibernate.INTEGER) ); registerFunction("int", new StandardSQLFunction("int", Hibernate.INTEGER) ); registerFunction("log", new StandardSQLFunction("log", Hibernate.DOUBLE) ); registerFunction("log10", new StandardSQLFunction("log10", Hibernate.DOUBLE) ); registerFunction("log2", new StandardSQLFunction("log2", Hibernate.DOUBLE) ); registerFunction("ln", new StandardSQLFunction("ln", Hibernate.DOUBLE) ); registerFunction("mod", new StandardSQLFunction("mod", Hibernate.INTEGER) ); registerFunction("pi", new NoArgSQLFunction("pi", Hibernate.DOUBLE) ); registerFunction("pow", new StandardSQLFunction("pow", Hibernate.DOUBLE) ); registerFunction("power", new StandardSQLFunction("power", Hibernate.DOUBLE) ); registerFunction("padians", new StandardSQLFunction("padians", Hibernate.DOUBLE) ); registerFunction("radians", new StandardSQLFunction("radians", Hibernate.DOUBLE) ); registerFunction("rand", new NoArgSQLFunction("rand", Hibernate.DOUBLE) ); registerFunction("round", new StandardSQLFunction("round", Hibernate.INTEGER) ); registerFunction("sign", new StandardSQLFunction("sign", Hibernate.INTEGER) ); registerFunction("sqrt", new StandardSQLFunction("sqrt", Hibernate.DOUBLE) ); registerFunction("trunc", new StandardSQLFunction("trunc", Hibernate.DOUBLE) ); registerFunction("truncate", new StandardSQLFunction("truncate", Hibernate.DOUBLE) ); //Trigonometric Functions registerFunction("acos", new StandardSQLFunction("acos", Hibernate.DOUBLE) ); registerFunction("asin", new StandardSQLFunction("asin", Hibernate.DOUBLE) ); registerFunction("atan", new StandardSQLFunction("atan", Hibernate.DOUBLE) ); registerFunction("atan2", new StandardSQLFunction("atan2", Hibernate.DOUBLE) ); registerFunction("cos", new StandardSQLFunction("cos", Hibernate.DOUBLE) ); registerFunction("cot", new StandardSQLFunction("cot", Hibernate.DOUBLE) ); registerFunction("crc32", new StandardSQLFunction("crc32", Hibernate.LONG) ); registerFunction("sin", new StandardSQLFunction("sin", Hibernate.DOUBLE) ); registerFunction("tan", new StandardSQLFunction("tan", Hibernate.DOUBLE) ); //String Functions registerFunction("alltrim", new StandardSQLFunction("alltrim") ); registerFunction("asc", new StandardSQLFunction("asc", Hibernate.INTEGER) ); registerFunction("ascii", new StandardSQLFunction("ascii", Hibernate.INTEGER) ); registerFunction("at", new StandardSQLFunction("at", Hibernate.INTEGER) ); registerFunction("bin", new StandardSQLFunction("bin", Hibernate.STRING) ); registerFunction("bit_length", new StandardSQLFunction("bit_length", Hibernate.LONG) ); registerFunction("char_length", new StandardSQLFunction("char_length", Hibernate.LONG) ); registerFunction("character_length", new StandardSQLFunction("character_length", Hibernate.LONG) ); registerFunction("char", new StandardSQLFunction("char", Hibernate.STRING) ); registerFunction("chr", new StandardSQLFunction("char", Hibernate.STRING) ); registerFunction("chrtran", new StandardSQLFunction("chrtran", Hibernate.STRING) ); registerFunction( "concat", new VarArgsSQLFunction(Hibernate.STRING, "", "||", "") ); registerFunction( "concat_ws", new VarArgsSQLFunction(Hibernate.STRING, "", "||", "") ); registerFunction( "conv", new StandardSQLFunction("conv", Hibernate.STRING) ); registerFunction( "difference", new StandardSQLFunction("difference", Hibernate.STRING) ); registerFunction("hex", new StandardSQLFunction("hex", Hibernate.STRING) ); registerFunction("initcap", new StandardSQLFunction("initcap") ); // registerFunction("insert", new StandardSQLFunction("insert") ); registerFunction("instr", new StandardSQLFunction("instr", Hibernate.INTEGER) ); registerFunction("lcase", new StandardSQLFunction("lcase") ); registerFunction("left", new StandardSQLFunction("left", Hibernate.INTEGER) ); registerFunction("len", new StandardSQLFunction("len", Hibernate.LONG) ); registerFunction("length", new StandardSQLFunction("length", Hibernate.LONG) ); registerFunction("locate", new StandardSQLFunction("locate", Hibernate.LONG) ); registerFunction("lower", new StandardSQLFunction("lower") ); registerFunction("lpad", new StandardSQLFunction("lpad", Hibernate.STRING) ); registerFunction("ltrim", new StandardSQLFunction("ltrim") ); registerFunction("mid", new StandardSQLFunction("mid", Hibernate.STRING) ); registerFunction("oct", new StandardSQLFunction("oct", Hibernate.STRING) ); registerFunction("octet_length", new StandardSQLFunction("octet_length", Hibernate.LONG) ); registerFunction("padc", new StandardSQLFunction("padc", Hibernate.STRING) ); registerFunction("padl", new StandardSQLFunction("padl", Hibernate.STRING) ); registerFunction("padr", new StandardSQLFunction("padr", Hibernate.STRING) ); registerFunction("position", new StandardSQLFunction("position", Hibernate.INTEGER) ); registerFunction("proper", new StandardSQLFunction("proper") ) ; registerFunction("repeat", new StandardSQLFunction("repeat", Hibernate.STRING) ); registerFunction("replicate", new StandardSQLFunction("replicate", Hibernate.STRING) ); registerFunction("replace", new StandardSQLFunction("replace", Hibernate.STRING) ); registerFunction("right", new StandardSQLFunction("right", Hibernate.INTEGER) ); registerFunction("rpad", new StandardSQLFunction("rpad", Hibernate.STRING) ); registerFunction("rtrim", new StandardSQLFunction("rtrim") ); registerFunction("soundex", new StandardSQLFunction("soundex") ); registerFunction("space", new StandardSQLFunction("space", Hibernate.STRING) ); registerFunction( "strcat", new VarArgsSQLFunction(Hibernate.STRING, "", "||", "") ); registerFunction("strcmp", new StandardSQLFunction("strcmp", Hibernate.INTEGER) ); registerFunction("strconv", new StandardSQLFunction("strconv", Hibernate.STRING) ); registerFunction("strtran", new StandardSQLFunction("strtran", Hibernate.STRING) ); registerFunction("stuff", new StandardSQLFunction("stuff", Hibernate.STRING) ); registerFunction("substr", new StandardSQLFunction("stuff", Hibernate.STRING) ); registerFunction("substring", new StandardSQLFunction("substring", Hibernate.STRING) ); registerFunction("translate", new StandardSQLFunction("translate", Hibernate.STRING) ); registerFunction("trim", new StandardSQLFunction("trim") ); registerFunction("ucase", new StandardSQLFunction("ucase") ); registerFunction("upper", new StandardSQLFunction("upper") ); registerFunction("charmirr", new StandardSQLFunction("charmirr") ); registerFunction("reverse", new StandardSQLFunction("reverse") ); //Date/Time Functions registerFunction("addtime",new StandardSQLFunction("addtime",Hibernate.TIMESTAMP)); registerFunction("cdow",new StandardSQLFunction("cdow",Hibernate.STRING)); registerFunction("cmonth",new StandardSQLFunction("cmonth",Hibernate.STRING)); registerFunction("curdate", new NoArgSQLFunction("curdate", Hibernate.DATE) ); registerFunction("curtime", new NoArgSQLFunction("curtime", Hibernate.TIME) ); registerFunction("date", new StandardSQLFunction("date", Hibernate.DATE) ); registerFunction("datediff", new StandardSQLFunction("datediff", Hibernate.INTEGER) ); registerFunction("datetime",new NoArgSQLFunction("datetime",Hibernate.TIMESTAMP)); registerFunction("date_add",new StandardSQLFunction("date_add",Hibernate.DATE)); registerFunction("date_sub",new StandardSQLFunction("date_sub",Hibernate.DATE)); registerFunction("adddate",new StandardSQLFunction("adddate",Hibernate.DATE)); registerFunction("subdate",new StandardSQLFunction("subdate",Hibernate.DATE)); registerFunction("day", new StandardSQLFunction("day", Hibernate.INTEGER) ); registerFunction("dayofmonth", new StandardSQLFunction("dayofmonth", Hibernate.INTEGER) ); registerFunction("dayname", new StandardSQLFunction("dayname", Hibernate.STRING) ); registerFunction("dayofweek", new StandardSQLFunction("dayofweek", Hibernate.INTEGER) ); registerFunction("dayofyear", new StandardSQLFunction("dayofyear", Hibernate.INTEGER) ); registerFunction("extract",new StandardSQLFunction("extract",Hibernate.INTEGER)); registerFunction("dow",new StandardSQLFunction("dow",Hibernate.STRING)); registerFunction("from_days", new StandardSQLFunction("from_days", Hibernate.DATE) ); registerFunction("gomonth", new StandardSQLFunction("gomonth", Hibernate.DATE) ); registerFunction("hour", new StandardSQLFunction("hour", Hibernate.INTEGER) ); registerFunction("last_day", new StandardSQLFunction("last_day", Hibernate.DATE) ); registerFunction("minute",new StandardSQLFunction("minute",Hibernate.INTEGER)); registerFunction("millisecond",new StandardSQLFunction("millisecond",Hibernate.INTEGER)); registerFunction("microsecond",new StandardSQLFunction("microsecond",Hibernate.INTEGER)); registerFunction("month",new StandardSQLFunction("month",Hibernate.INTEGER)); registerFunction("monthname",new StandardSQLFunction("monthname",Hibernate.STRING)); registerFunction("now", new NoArgSQLFunction("now", Hibernate.TIMESTAMP) ); registerFunction("quarter", new StandardSQLFunction("quarter", Hibernate.INTEGER) ); registerFunction("second", new StandardSQLFunction("second", Hibernate.INTEGER) ); registerFunction("sub_time", new NoArgSQLFunction("sub_time", Hibernate.TIMESTAMP) ); registerFunction("sysdate", new NoArgSQLFunction("sysdate", Hibernate.TIMESTAMP) ); registerFunction("time", new StandardSQLFunction("time", Hibernate.TIME) ); registerFunction("timediff", new StandardSQLFunction("timediff", Hibernate.TIME) ); registerFunction("timestamp", new StandardSQLFunction("timestamp", Hibernate.TIMESTAMP) ); registerFunction("timestampadd", new StandardSQLFunction("timestampadd", Hibernate.TIMESTAMP) ); registerFunction("timestampdiff", new StandardSQLFunction("timestampdiff", Hibernate.INTEGER) ); registerFunction("to_days", new StandardSQLFunction("to_days", Hibernate.INTEGER) ); registerFunction("week", new StandardSQLFunction("week", Hibernate.INTEGER) ); registerFunction("weekofyear", new StandardSQLFunction("weekofyear", Hibernate.INTEGER) ); registerFunction("year", new StandardSQLFunction("year", Hibernate.INTEGER) ); //boolean functions registerFunction("empty", new StandardSQLFunction("empty", Hibernate.BOOLEAN) ); registerFunction("isblank", new StandardSQLFunction("isblank", Hibernate.BOOLEAN) ); registerFunction("isalpha", new StandardSQLFunction("isalpha", Hibernate.BOOLEAN) ); registerFunction("isdigit", new StandardSQLFunction("isdigit", Hibernate.BOOLEAN) ); registerFunction("isnull", new StandardSQLFunction("isnull", Hibernate.BOOLEAN) ); //Conversion Functions registerFunction("cbool", new StandardSQLFunction("cbool", Hibernate.BOOLEAN) ); registerFunction("cbyte", new StandardSQLFunction("cbyte", Hibernate.BYTE) ); registerFunction("cdate", new StandardSQLFunction("cdate", Hibernate.DATE) ); registerFunction("cdbl", new StandardSQLFunction("cdbl", Hibernate.DOUBLE) ); registerFunction("cint", new StandardSQLFunction("cint", Hibernate.INTEGER) ); registerFunction("clng", new StandardSQLFunction("clng", Hibernate.LONG) ); registerFunction("csng", new StandardSQLFunction("csng", Hibernate.FLOAT) ); registerFunction("cstr", new StandardSQLFunction("cstr", Hibernate.STRING) ); registerFunction("ctod", new StandardSQLFunction("ctod", Hibernate.DATE) ); registerFunction("ctot", new StandardSQLFunction("ctot", Hibernate.TIMESTAMP) ); registerFunction("dtoc", new StandardSQLFunction("dtoc", Hibernate.STRING) ); registerFunction("dtot", new StandardSQLFunction("dtot", Hibernate.TIMESTAMP) ); registerFunction("ttoc", new StandardSQLFunction("ttoc", Hibernate.STRING) ); registerFunction("ttod", new StandardSQLFunction("ttod", Hibernate.DATE) ); //Security Functions registerFunction("compress", new StandardSQLFunction("compress", Hibernate.STRING) ); registerFunction("uncompress", new StandardSQLFunction("uncompress", Hibernate.STRING) ); registerFunction("encrypt", new StandardSQLFunction("encrypt", Hibernate.STRING) ); registerFunction("decrypt", new StandardSQLFunction("decrypt", Hibernate.STRING) ); registerFunction("encode", new StandardSQLFunction("encode", Hibernate.STRING) ); registerFunction("decode", new StandardSQLFunction("decode", Hibernate.STRING) ); registerFunction("md5", new StandardSQLFunction("md5", Hibernate.STRING) ); registerFunction("crypt3", new StandardSQLFunction("crypt3", Hibernate.STRING) ); //System Functions registerFunction( "database", new NoArgSQLFunction("database", Hibernate.STRING, false) ); registerFunction( "user", new NoArgSQLFunction("user", Hibernate.STRING, false) ); registerFunction( "deleted", new NoArgSQLFunction("deleted", Hibernate.BOOLEAN, false) ); registerFunction( "reccount", new NoArgSQLFunction("reccount", Hibernate.LONG, false) ); registerFunction( "recno", new NoArgSQLFunction("recno", Hibernate.LONG, false) ); registerFunction( "rowlocked", new NoArgSQLFunction("rowlocked", Hibernate.BOOLEAN, false) ); //Miscellaneous Functions registerFunction( "nvl", new StandardSQLFunction("nvl") ); registerFunction( "ifnull", new StandardSQLFunction("ifnull") ); getDefaultProperties().setProperty(Environment.MAX_FETCH_DEPTH, "2"); getDefaultProperties().setProperty(Environment.STATEMENT_BATCH_SIZE, DEFAULT_BATCH_SIZE); } } //////////////////////////////////////////////////////////////////////////////// package com.hxtt.support.hibernate; import java.sql.Types; /** * An SQL dialect for HXTT Access. * @author daiwei */ public class HxttAccessDialect extends HxttDialect { public HxttAccessDialect() { super(); //complete map //registerColumnType( Types.BIT, "bit" ); //registerColumnType( Types.CHAR, "varchar(1)" ); // HXTT Access DON'T SUPPORT CHAR,and there is no varchar(1) column type,so there is no CHAR registerColumnType(Types.CHAR, "varchar($l)"); registerColumnType(Types.VARCHAR, 255, "varchar($l)"); registerColumnType(Types.LONGVARCHAR, "longvarchar"); registerColumnType(Types.NUMERIC, "numeric"); registerColumnType(Types.NUMERIC, "numeric($p,$s)"); registerColumnType(Types.BOOLEAN, "boolean"); registerColumnType( Types.BIGINT, "NUMERIC(19,0)" ); // MS Access DON'T SUPPORT BIGINT //registerColumnType( Types.TINYINT, "number(3,0)" ); // MS Access DON'T SUPPORT TINYINT //registerColumnType(Types.TINYINT, "tinyint"); //HXTT Access' tinyint is from 0 to 255 registerColumnType(Types.TINYINT, "smallint"); //HXTT Access' tinyint is from 0 to 255 registerColumnType(Types.SMALLINT, "smallint"); registerColumnType(Types.INTEGER, "integer"); registerColumnType(Types.REAL, "real"); //registerColumnType( Types.FLOAT, "float" ); // HXTT Access DON'T SUPPORT FLOAT ,it will be a double type registerColumnType(Types.DOUBLE, "double"); //registerColumnType( Types.BINARY, 6784, "binary" ); registerColumnType(Types.VARBINARY, 510, "varbinary"); //registerColumnType( Types.LONGVARBINARY, "longvarbinary"); //registerColumnType( Types.DATE, "date" ); // registerColumnType( Types.TIME, "time" ); // HXTT Access DON'T SUPPORT TIME registerColumnType(Types.TIMESTAMP, "timestamp"); //registerColumnType( Types.BLOB, "blob" ); // BLOB COLUMN WILL CHANGE TO JAVA_OBJECT TYPE COLUMN //registerColumnType( Types.CLOB, "clob" ); // CLOB COLUMN WILL CHANGE TO LONGVARCHAR TYPE COLUMN registerColumnType(Types.OTHER, "currency"); //registerColumnType( Types.OTHER, "graphics" ); registerColumnType(Types.OTHER, "ole"); registerColumnType( Types.JAVA_OBJECT, "java_object" ); } //ALTER table TBNAME ADD COLUMN FIELDNAME FIELDTYPE public String getAddColumnString() { return "add column"; } ///处理到此函数 public boolean bindLimitParametersInReverseOrder() { return true; } public String getCreateSequenceString(String sequenceName) { // create sequence if not exists userID start WITH 100 increment by 2 maxvalue 2000 cache 5 cycle; return "create sequence " + sequenceName + " start with 1"; // } public String getCurrentTimestampSelectString() { return "?now()"; } public String getDropForeignKeyString() { throw new UnsupportedOperationException( "No drop foreign key syntax supported by HxttAccessDialect" ); } public String getDropSequenceString(String sequenceName) { //drop sequence if exists userID; return "drop sequence " + sequenceName; } public String getLimitString(String sql, boolean hasOffset) { /* return new StringBuffer(sql.length() + 20) .append(sql) .append(hasOffset ? " limit ?, ?" : " limit ?") .toString();*/ sql = sql.trim(); boolean isForUpdate = false; if ( sql.toLowerCase().endsWith(" for update") ) { sql = sql.substring( 0, sql.length()-11 ); isForUpdate = true; } StringBuffer pagingSelect = new StringBuffer( sql.length()+100 ); if (hasOffset) { pagingSelect.append("select * from ( select row_.*, RECNO() rownum_ from ( "); } else { pagingSelect.append("select * from ( "); } pagingSelect.append(sql); if (hasOffset) { pagingSelect.append(" ) row_ where RECNO() < = ?) where rownum_ > ?"); } else { pagingSelect.append(" ) where RECNO() < = ?"); } if (isForUpdate) pagingSelect.append(" for update"); return pagingSelect.toString(); } ////???? /** * A query used to find all sequences * * @see org.hibernate.tool.hbm2ddl.SchemaUpdate */ public String getQuerySequencesString() { return null; } //?NEXTVAL('SEQUENCENAME') public String getSequenceNextValString(String sequenceName) { return "?" + getSelectSequenceNextValString( sequenceName ) ; } public String getSelectSequenceNextValString(String sequenceName) { return "nextval('" + sequenceName+"')"; } ////???? public boolean isCurrentTimestampSelectStringCallable() { return false; } public boolean qualifyIndexName() { return false; } public boolean supportsCascadeDelete() { return true;//false; } public boolean supportsCurrentTimestampSelection() { return true; } public boolean supportsIfExistsBeforeTableName() { return true; } public boolean supportsLimit() { return true; } public boolean supportsUnionAll() { return true; } public boolean supportsSequences() { return true; } /* public boolean supportsIdentityColumns() { return true; } public String getIdentitySelectString() { return "select last_insert_id()"; } public String getIdentityColumnString() { return "not null auto_increment"; //starts with 1, implicitly }*/ }
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.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.
281.
282.
283.
284.
285.
286.
287.
288.
289.
290.
291.
292.
293.
294.
295.
296.
297.
298.
299.
300.
301.
302.
303.
304.
305.
306.
307.
308.
309.
310.
311.
312.
313.
314.
315.
316.
317.
318.
319.
320.
321.
322.
323.
324.
325.
326.
327.
328.
329.
330.
331.
332.
333.
334.
335.
336.
337.
338.
339.
340.
341.
342.
343.
344.
345.
346.
347.
348.
349.
350.
351.
352.
353.
354.
355.
356.
357.
358.
359.
360.
361.
362.
363.
364.
365.
366.
367.
368.
369.
370.
371.
372.
373.
374.
375.
376.
377.
378.
379.
380.
381.
382.
383.
384.
385.
386.
387.
388.
389.
【编辑推荐】