KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > hibernate > dialect > DB2Dialect


1 //$Id: DB2Dialect.java,v 1.29 2005/06/15 04:04:16 oneovthafew Exp $
2 package org.hibernate.dialect;
3
4 import java.sql.CallableStatement JavaDoc;
5 import java.sql.ResultSet JavaDoc;
6 import java.sql.SQLException JavaDoc;
7 import java.sql.Types JavaDoc;
8
9 import org.hibernate.Hibernate;
10 import org.hibernate.cfg.Environment;
11 import org.hibernate.dialect.function.NoArgSQLFunction;
12 import org.hibernate.dialect.function.SQLFunctionTemplate;
13 import org.hibernate.dialect.function.StandardSQLFunction;
14 import org.hibernate.dialect.function.VarArgsSQLFunction;
15
16 /**
17  * An SQL dialect for DB2.
18  * @author Gavin King
19  */
20 public class DB2Dialect extends Dialect {
21
22     public DB2Dialect() {
23         super();
24         registerColumnType( Types.BIT, "smallint" );
25         registerColumnType( Types.BIGINT, "bigint" );
26         registerColumnType( Types.SMALLINT, "smallint" );
27         registerColumnType( Types.TINYINT, "smallint" );
28         registerColumnType( Types.INTEGER, "integer" );
29         registerColumnType( Types.CHAR, "char(1)" );
30         registerColumnType( Types.VARCHAR, "varchar($l)" );
31         registerColumnType( Types.FLOAT, "float" );
32         registerColumnType( Types.DOUBLE, "double" );
33         registerColumnType( Types.DATE, "date" );
34         registerColumnType( Types.TIME, "time" );
35         registerColumnType( Types.TIMESTAMP, "timestamp" );
36         registerColumnType( Types.VARBINARY, "varchar($l) for bit data" );
37         registerColumnType( Types.NUMERIC, "numeric($p,$s)" );
38         registerColumnType( Types.BLOB, "blob($l)" );
39         registerColumnType( Types.CLOB, "clob($l)" );
40
41         registerFunction("abs", new StandardSQLFunction("abs") );
42         registerFunction("absval", new StandardSQLFunction("absval") );
43         registerFunction("sign", new StandardSQLFunction("sign", Hibernate.INTEGER) );
44
45         registerFunction("ceiling", new StandardSQLFunction("ceiling") );
46         registerFunction("ceil", new StandardSQLFunction("ceil") );
47         registerFunction("floor", new StandardSQLFunction("floor") );
48         registerFunction("round", new StandardSQLFunction("round") );
49
50         registerFunction("acos", new StandardSQLFunction("acos", Hibernate.DOUBLE) );
51         registerFunction("asin", new StandardSQLFunction("asin", Hibernate.DOUBLE) );
52         registerFunction("atan", new StandardSQLFunction("atan", Hibernate.DOUBLE) );
53         registerFunction("cos", new StandardSQLFunction("cos", Hibernate.DOUBLE) );
54         registerFunction("cot", new StandardSQLFunction("cot", Hibernate.DOUBLE) );
55         registerFunction("degrees", new StandardSQLFunction("degrees", Hibernate.DOUBLE) );
56         registerFunction("exp", new StandardSQLFunction("exp", Hibernate.DOUBLE) );
57         registerFunction("float", new StandardSQLFunction("float", Hibernate.DOUBLE) );
58         registerFunction("hex", new StandardSQLFunction("hex", Hibernate.STRING) );
59         registerFunction("ln", new StandardSQLFunction("ln", Hibernate.DOUBLE) );
60         registerFunction("log", new StandardSQLFunction("log", Hibernate.DOUBLE) );
61         registerFunction("log10", new StandardSQLFunction("log10", Hibernate.DOUBLE) );
62         registerFunction("radians", new StandardSQLFunction("radians", Hibernate.DOUBLE) );
63         registerFunction("rand", new NoArgSQLFunction("rand", Hibernate.DOUBLE) );
64         registerFunction("sin", new StandardSQLFunction("sin", Hibernate.DOUBLE) );
65         registerFunction("soundex", new StandardSQLFunction("soundex", Hibernate.STRING) );
66         registerFunction("sqrt", new StandardSQLFunction("sqrt", Hibernate.DOUBLE) );
67         registerFunction("stddev", new StandardSQLFunction("stddev", Hibernate.DOUBLE) );
68         registerFunction("tan", new StandardSQLFunction("tan", Hibernate.DOUBLE) );
69         registerFunction("variance", new StandardSQLFunction("variance", Hibernate.DOUBLE) );
70
71         registerFunction("julian_day", new StandardSQLFunction("julian_day", Hibernate.INTEGER) );
72         registerFunction("microsecond", new StandardSQLFunction("microsecond", Hibernate.INTEGER) );
73         registerFunction("midnight_seconds", new StandardSQLFunction("midnight_seconds", Hibernate.INTEGER) );
74         registerFunction("minute", new StandardSQLFunction("minute", Hibernate.INTEGER) );
75         registerFunction("month", new StandardSQLFunction("month", Hibernate.INTEGER) );
76         registerFunction("monthname", new StandardSQLFunction("monthname", Hibernate.STRING) );
77         registerFunction("quarter", new StandardSQLFunction("quarter", Hibernate.INTEGER) );
78         registerFunction("hour", new StandardSQLFunction("hour", Hibernate.INTEGER) );
79         registerFunction("second", new StandardSQLFunction("second", Hibernate.INTEGER) );
80         registerFunction("current_date", new NoArgSQLFunction("current date", Hibernate.DATE, false) );
81         registerFunction("date", new StandardSQLFunction("date", Hibernate.DATE) );
82         registerFunction("day", new StandardSQLFunction("day", Hibernate.INTEGER) );
83         registerFunction("dayname", new StandardSQLFunction("dayname", Hibernate.STRING) );
84         registerFunction("dayofweek", new StandardSQLFunction("dayofweek", Hibernate.INTEGER) );
85         registerFunction("dayofweek_iso", new StandardSQLFunction("dayofweek_iso", Hibernate.INTEGER) );
86         registerFunction("dayofyear", new StandardSQLFunction("dayofyear", Hibernate.INTEGER) );
87         registerFunction("days", new StandardSQLFunction("days", Hibernate.LONG) );
88         registerFunction("current_time", new NoArgSQLFunction("current time", Hibernate.TIME, false) );
89         registerFunction("time", new StandardSQLFunction("time", Hibernate.TIME) );
90         registerFunction("current_timestamp", new NoArgSQLFunction("current timestamp", Hibernate.TIMESTAMP, false) );
91         registerFunction("timestamp", new StandardSQLFunction("timestamp", Hibernate.TIMESTAMP) );
92         registerFunction("timestamp_iso", new StandardSQLFunction("timestamp_iso", Hibernate.TIMESTAMP) );
93         registerFunction("week", new StandardSQLFunction("week", Hibernate.INTEGER) );
94         registerFunction("week_iso", new StandardSQLFunction("week_iso", Hibernate.INTEGER) );
95         registerFunction("year", new StandardSQLFunction("year", Hibernate.INTEGER) );
96
97         registerFunction("double", new StandardSQLFunction("double", Hibernate.DOUBLE) );
98         registerFunction("varchar", new StandardSQLFunction("varchar", Hibernate.STRING) );
99         registerFunction("real", new StandardSQLFunction("real", Hibernate.FLOAT) );
100         registerFunction("bigint", new StandardSQLFunction("bigint", Hibernate.LONG) );
101         registerFunction("char", new StandardSQLFunction("char", Hibernate.CHARACTER) );
102         registerFunction("integer", new StandardSQLFunction("integer", Hibernate.INTEGER) );
103         registerFunction("smallint", new StandardSQLFunction("smallint", Hibernate.SHORT) );
104
105         registerFunction("digits", new StandardSQLFunction("digits", Hibernate.STRING) );
106         registerFunction("chr", new StandardSQLFunction("chr", Hibernate.CHARACTER) );
107         registerFunction("upper", new StandardSQLFunction("upper") );
108         registerFunction("lower", new StandardSQLFunction("lower") );
109         registerFunction("ucase", new StandardSQLFunction("ucase") );
110         registerFunction("lcase", new StandardSQLFunction("lcase") );
111         registerFunction("length", new StandardSQLFunction("length", Hibernate.LONG) );
112         registerFunction("ltrim", new StandardSQLFunction("ltrim") );
113         registerFunction("rtrim", new StandardSQLFunction("rtrim") );
114         registerFunction( "substr", new StandardSQLFunction( "substr", Hibernate.STRING ) );
115         registerFunction( "posstr", new StandardSQLFunction( "posstr", Hibernate.INTEGER ) );
116
117         registerFunction( "substring", new StandardSQLFunction( "substr", Hibernate.STRING ) );
118         registerFunction( "trim", new SQLFunctionTemplate( Hibernate.INTEGER, "ltrim(rtrim(?1))" ) );
119         registerFunction( "bit_length", new SQLFunctionTemplate( Hibernate.INTEGER, "length(?1)*8" ) );
120         
121         registerFunction( "concat", new VarArgsSQLFunction(Hibernate.STRING, "", "||", "") );
122
123         registerFunction( "str", new SQLFunctionTemplate( Hibernate.STRING, "rtrim(char(?1))" ) );
124
125         getDefaultProperties().setProperty(Environment.STATEMENT_BATCH_SIZE, NO_BATCH);
126     }
127
128     public String JavaDoc getLowercaseFunction() {
129         return "lcase";
130     }
131     
132     public String JavaDoc getAddColumnString() {
133         return "add column";
134     }
135     public boolean dropConstraints() {
136         return false;
137     }
138     public boolean supportsIdentityColumns() {
139         return true;
140     }
141     public String JavaDoc getIdentitySelectString() {
142         return "values identity_val_local()";
143     }
144     public String JavaDoc getIdentityColumnString() {
145         return "generated by default as identity"; //not null ... (start with 1) is implicit
146 }
147     public String JavaDoc getIdentityInsertString() {
148         return "default";
149     }
150
151     public String JavaDoc getSequenceNextValString(String JavaDoc sequenceName) {
152         return "values nextval for " + sequenceName;
153     }
154     public String JavaDoc getCreateSequenceString(String JavaDoc sequenceName) {
155         return "create sequence " + sequenceName;
156     }
157     public String JavaDoc getDropSequenceString(String JavaDoc sequenceName) {
158         return "drop sequence " + sequenceName + " restrict";
159     }
160
161     public boolean supportsSequences() {
162         return true;
163     }
164
165     public String JavaDoc getQuerySequencesString() {
166         return "select seqname from sysibm.syssequences";
167     }
168
169     public boolean supportsLimit() {
170         return true;
171     }
172
173     /*public String getLimitString(String sql, boolean hasOffset) {
174         StringBuffer rownumber = new StringBuffer(50)
175             .append(" rownumber() over(");
176         int orderByIndex = sql.toLowerCase().indexOf("order by");
177         if (orderByIndex>0) rownumber.append( sql.substring(orderByIndex) );
178         rownumber.append(") as row_,");
179         StringBuffer pagingSelect = new StringBuffer( sql.length()+100 )
180             .append("select * from ( ")
181             .append(sql)
182             .insert( getAfterSelectInsertPoint(sql)+16, rownumber.toString() )
183             .append(" ) as temp_ where row_ ");
184         if (hasOffset) {
185             pagingSelect.append("between ?+1 and ?");
186         }
187         else {
188             pagingSelect.append("<= ?");
189         }
190         return pagingSelect.toString();
191     }*/
192     
193     /**
194      * Render the <tt>rownumber() over ( .... ) as rownumber_,</tt>
195      * bit, that goes in the select list
196      */
197     private String JavaDoc getRowNumber(String JavaDoc sql) {
198         StringBuffer JavaDoc rownumber = new StringBuffer JavaDoc(50)
199             .append("rownumber() over(");
200
201         int orderByIndex = sql.toLowerCase().indexOf("order by");
202         
203         if ( orderByIndex>0 && !hasDistinct(sql) ) {
204             rownumber.append( sql.substring(orderByIndex) );
205         }
206              
207         rownumber.append(") as rownumber_,");
208         
209         return rownumber.toString();
210     }
211
212     public String JavaDoc getLimitString(String JavaDoc sql, boolean hasOffset) {
213         
214         int startOfSelect = sql.toLowerCase().indexOf("select");
215         
216         StringBuffer JavaDoc pagingSelect = new StringBuffer JavaDoc( sql.length()+100 )
217                     .append( sql.substring(0, startOfSelect) ) //add the comment
218 .append("select * from ( select ") //nest the main query in an outer select
219 .append( getRowNumber(sql) ); //add the rownnumber bit into the outer query select list
220
221         if ( hasDistinct(sql) ) {
222             pagingSelect.append(" row_.* from ( ") //add another (inner) nested select
223 .append( sql.substring(startOfSelect) ) //add the main query
224 .append(" ) as row_"); //close off the inner nested select
225 }
226         else {
227             pagingSelect.append( sql.substring( startOfSelect + 6 ) ); //add the main query
228 }
229                 
230         pagingSelect.append(" ) as temp_ where rownumber_ ");
231         
232         //add the restriction to the outer select
233 if (hasOffset) {
234             pagingSelect.append("between ?+1 and ?");
235         }
236         else {
237             pagingSelect.append("<= ?");
238         }
239         
240         return pagingSelect.toString();
241     }
242
243     private static boolean hasDistinct(String JavaDoc sql) {
244         return sql.toLowerCase().indexOf("select distinct")>=0;
245     }
246     
247     public String JavaDoc getForUpdateString() {
248         return " for update with rr";
249     }
250
251     public boolean useMaxForLimit() {
252         return true;
253     }
254     
255     public boolean supportsOuterJoinForUpdate() {
256         return false;
257     }
258     
259     public boolean supportsNotNullUnique() {
260         return false;
261     }
262
263     public String JavaDoc getSelectClauseNullString(int sqlType) {
264         String JavaDoc literal;
265         switch(sqlType) {
266             case Types.VARCHAR:
267             case Types.CHAR:
268                 literal = "'x'";
269                 break;
270             case Types.DATE:
271                 literal = "'2000-1-1'";
272                 break;
273             case Types.TIMESTAMP:
274                 literal = "'2000-1-1 00:00:00'";
275                 break;
276             case Types.TIME:
277                 literal = "'00:00:00'";
278                 break;
279             default:
280                 literal = "0";
281         }
282         return "nullif(" + literal + ',' + literal + ')';
283     }
284     
285     public static void main(String JavaDoc[] args) {
286         System.out.println( new DB2Dialect().getLimitString("/*foo*/ select * from foos", true) );
287         System.out.println( new DB2Dialect().getLimitString("/*foo*/ select distinct * from foos", true) );
288         System.out.println( new DB2Dialect().getLimitString("/*foo*/ select * from foos foo order by foo.bar, foo.baz", true) );
289         System.out.println( new DB2Dialect().getLimitString("/*foo*/ select distinct * from foos foo order by foo.bar, foo.baz", true) );
290     }
291
292     public boolean supportsUnionAll() {
293         return true;
294     }
295     
296     public int registerResultSetOutParameter(CallableStatement JavaDoc statement,int col) throws SQLException JavaDoc {
297         return col;
298     }
299     
300     public ResultSet JavaDoc getResultSet(CallableStatement JavaDoc ps) throws SQLException JavaDoc {
301         boolean isResultSet = ps.execute();
302         // This assumes you will want to ignore any update counts
303 while (!isResultSet && ps.getUpdateCount() != -1) {
304             isResultSet = ps.getMoreResults();
305         }
306         ResultSet JavaDoc rs = ps.getResultSet();
307         // You may still have other ResultSets or update counts left to process here
308 // but you can't do it now or the ResultSet you just got will be closed
309 return rs;
310     }
311
312     public boolean supportsCommentOn() {
313         return true;
314     }
315     
316     public boolean supportsTemporaryTables() {
317         return true;
318     }
319
320     public String JavaDoc getTemporaryTableCreationCommand() {
321         return "declare global temporary table";
322     }
323
324     public String JavaDoc getTemporaryTableCreationPostfix() {
325         return "not logged";
326     }
327
328     public String JavaDoc generateTemporaryTableName(String JavaDoc baseTableName) {
329         return "session." + super.generateTemporaryTableName(baseTableName);
330     }
331
332 }
333
Popular Tags