|
1 package org.hibernate.dialect;
3
4 import java.sql.CallableStatement  ;
5 import java.sql.ResultSet ;
6 import java.sql.SQLException ;
7 import java.sql.Types ;
8
9 import org.hibernate.Hibernate;
10 import org.hibernate.HibernateException;
11 import org.hibernate.cfg.Environment;
12 import org.hibernate.dialect.function.NoArgSQLFunction;
13 import org.hibernate.dialect.function.NvlFunction;
14 import org.hibernate.dialect.function.SQLFunctionTemplate;
15 import org.hibernate.dialect.function.StandardSQLFunction;
16 import org.hibernate.dialect.function.VarArgsSQLFunction;
17 import org.hibernate.exception.ErrorCodeConverter;
18 import org.hibernate.exception.JDBCExceptionHelper;
19 import org.hibernate.exception.SQLExceptionConverter;
20 import org.hibernate.exception.TemplatedViolatedConstraintNameExtracter;
21 import org.hibernate.exception.ViolatedConstraintNameExtracter;
22 import org.hibernate.util.ReflectHelper;
23
24
28 public class Oracle9Dialect extends Dialect {
29
30 public Oracle9Dialect() {
31 super();
32 registerColumnType( Types.BIT, "number(1,0)" );
33 registerColumnType( Types.BIGINT, "number(19,0)" );
34 registerColumnType( Types.SMALLINT, "number(5,0)" );
35 registerColumnType( Types.TINYINT, "number(3,0)" );
36 registerColumnType( Types.INTEGER, "number(10,0)" );
37 registerColumnType( Types.CHAR, "char(1)" );
38 registerColumnType( Types.VARCHAR, 4000, "varchar2($l)" );
39 registerColumnType( Types.VARCHAR, "long" );
40 registerColumnType( Types.FLOAT, "float" );
41 registerColumnType( Types.DOUBLE, "double precision" );
42 registerColumnType( Types.DATE, "date" );
43 registerColumnType( Types.TIME, "date" );
44 registerColumnType( Types.TIMESTAMP, "timestamp" );
45 registerColumnType( Types.VARBINARY, 2000, "raw($l)" );
46 registerColumnType( Types.VARBINARY, "long raw" );
47 registerColumnType( Types.NUMERIC, "number($p,$s)" );
48 registerColumnType( Types.BLOB, "blob" );
49 registerColumnType( Types.CLOB, "clob" );
50
51 getDefaultProperties().setProperty(Environment.USE_STREAMS_FOR_BINARY, "true");
52 getDefaultProperties().setProperty(Environment.STATEMENT_BATCH_SIZE, DEFAULT_BATCH_SIZE);
53
54 registerFunction( "abs", new StandardSQLFunction("abs") );
55 registerFunction( "sign", new StandardSQLFunction("sign", Hibernate.INTEGER) );
56
57 registerFunction( "acos", new StandardSQLFunction("acos", Hibernate.DOUBLE) );
58 registerFunction( "asin", new StandardSQLFunction("asin", Hibernate.DOUBLE) );
59 registerFunction( "atan", new StandardSQLFunction("atan", Hibernate.DOUBLE) );
60 registerFunction( "cos", new StandardSQLFunction("cos", Hibernate.DOUBLE) );
61 registerFunction( "cosh", new StandardSQLFunction("cosh", Hibernate.DOUBLE) );
62 registerFunction( "exp", new StandardSQLFunction("exp", Hibernate.DOUBLE) );
63 registerFunction( "ln", new StandardSQLFunction("ln", Hibernate.DOUBLE) );
64 registerFunction( "sin", new StandardSQLFunction("sin", Hibernate.DOUBLE) );
65 registerFunction( "sinh", new StandardSQLFunction("sinh", Hibernate.DOUBLE) );
66 registerFunction( "stddev", new StandardSQLFunction("stddev", Hibernate.DOUBLE) );
67 registerFunction( "sqrt", new StandardSQLFunction("sqrt", Hibernate.DOUBLE) );
68 registerFunction( "tan", new StandardSQLFunction("tan", Hibernate.DOUBLE) );
69 registerFunction( "tanh", new StandardSQLFunction("tanh", Hibernate.DOUBLE) );
70 registerFunction( "variance", new StandardSQLFunction("variance", Hibernate.DOUBLE) );
71
72 registerFunction( "round", new StandardSQLFunction("round") );
73 registerFunction( "trunc", new StandardSQLFunction("trunc") );
74 registerFunction( "ceil", new StandardSQLFunction("ceil") );
75 registerFunction( "floor", new StandardSQLFunction("floor") );
76
77 registerFunction( "chr", new StandardSQLFunction("chr", Hibernate.CHARACTER) );
78 registerFunction( "initcap", new StandardSQLFunction("initcap") );
79 registerFunction( "lower", new StandardSQLFunction("lower") );
80 registerFunction( "ltrim", new StandardSQLFunction("ltrim") );
81 registerFunction( "rtrim", new StandardSQLFunction("rtrim") );
82 registerFunction( "soundex", new StandardSQLFunction("soundex") );
83 registerFunction( "upper", new StandardSQLFunction("upper") );
84 registerFunction( "ascii", new StandardSQLFunction("ascii", Hibernate.INTEGER) );
85 registerFunction( "length", new StandardSQLFunction("length", Hibernate.LONG) );
86
87 registerFunction( "to_char", new StandardSQLFunction("to_char", Hibernate.STRING) );
88 registerFunction( "to_date", new StandardSQLFunction("to_date", Hibernate.TIMESTAMP) );
89
90 registerFunction( "current_date", new NoArgSQLFunction("current_date", Hibernate.DATE, false) );
91 registerFunction( "current_time", new NoArgSQLFunction("current_timestamp", Hibernate.TIME, false) );
92 registerFunction( "current_timestamp", new NoArgSQLFunction("current_timestamp", Hibernate.TIMESTAMP, false) );
93
94 registerFunction( "lastday", new StandardSQLFunction("lastday", Hibernate.DATE) );
95 registerFunction( "sysdate", new NoArgSQLFunction("sysdate", Hibernate.DATE, false) );
96 registerFunction( "systimestamp", new NoArgSQLFunction("systimestamp", Hibernate.TIMESTAMP, false) );
97 registerFunction( "uid", new NoArgSQLFunction("uid", Hibernate.INTEGER, false) );
98 registerFunction( "user", new NoArgSQLFunction("user", Hibernate.STRING, false) );
99
100 registerFunction( "rowid", new NoArgSQLFunction("rowid", Hibernate.LONG, false) );
101 registerFunction( "rownum", new NoArgSQLFunction("rownum", Hibernate.LONG, false) );
102
103 registerFunction( "concat", new VarArgsSQLFunction(Hibernate.STRING, "", "||", "") );
105 registerFunction( "instr", new StandardSQLFunction("instr", Hibernate.INTEGER) );
106 registerFunction( "instrb", new StandardSQLFunction("instrb", Hibernate.INTEGER) );
107 registerFunction( "lpad", new StandardSQLFunction("lpad", Hibernate.STRING) );
108 registerFunction( "replace", new StandardSQLFunction("replace", Hibernate.STRING) );
109 registerFunction( "rpad", new StandardSQLFunction("rpad", Hibernate.STRING) );
110 registerFunction( "substr", new StandardSQLFunction("substr", Hibernate.STRING) );
111 registerFunction( "substrb", new StandardSQLFunction("substrb", Hibernate.STRING) );
112 registerFunction( "translate", new StandardSQLFunction("translate", Hibernate.STRING) );
113
114 registerFunction( "substring", new StandardSQLFunction( "substr", Hibernate.STRING ) );
115 registerFunction( "locate", new StandardSQLFunction( "instr", Hibernate.INTEGER ) );
116 registerFunction( "bit_length", new SQLFunctionTemplate( Hibernate.INTEGER, "vsize(?1)*8" ) );
117 registerFunction( "coalesce", new NvlFunction() );
118
119 registerFunction( "atan2", new StandardSQLFunction("atan2", Hibernate.FLOAT) );
121 registerFunction( "log", new StandardSQLFunction("log", Hibernate.INTEGER) );
122 registerFunction( "mod", new StandardSQLFunction("mod", Hibernate.INTEGER) );
123 registerFunction( "nvl", new StandardSQLFunction("nvl") );
124 registerFunction( "nvl2", new StandardSQLFunction("nvl2") );
125 registerFunction( "power", new StandardSQLFunction("power", Hibernate.FLOAT) );
126
127 registerFunction( "add_months", new StandardSQLFunction("add_months", Hibernate.DATE) );
129 registerFunction( "months_between", new StandardSQLFunction("months_between", Hibernate.FLOAT) );
130 registerFunction( "next_day", new StandardSQLFunction("next_day", Hibernate.DATE) );
131
132 registerFunction( "str", new StandardSQLFunction("to_char", Hibernate.STRING) );
133 }
134
135 public String getAddColumnString() {
136 return "add";
137 }
138
139 public String getSequenceNextValString(String sequenceName) {
140 return "select " + getSelectSequenceNextValString( sequenceName ) + " from dual";
141 }
142
143 public String getSelectSequenceNextValString(String sequenceName) {
144 return sequenceName + ".nextval";
145 }
146
147 public String getCreateSequenceString(String sequenceName) {
148 return "create sequence " + sequenceName; }
150
151 public String getDropSequenceString(String sequenceName) {
152 return "drop sequence " + sequenceName;
153 }
154
155 public String getCascadeConstraintsString() {
156 return " cascade constraints";
157 }
158
159 public boolean dropConstraints() {
160 return false;
161 }
162
163 public String getForUpdateNowaitString() {
164 return " for update nowait";
165 }
166
167 public boolean supportsSequences() {
168 return true;
169 }
170
171 public boolean supportsLimit() {
172 return true;
173 }
174
175 public String getLimitString(String sql, boolean hasOffset) {
176
177 sql = sql.trim();
178 boolean isForUpdate = false;
179 if ( sql.toLowerCase().endsWith(" for update") ) {
180 sql = sql.substring( 0, sql.length()-11 );
181 isForUpdate = true;
182 }
183
184 StringBuffer pagingSelect = new StringBuffer ( sql.length()+100 );
185 if (hasOffset) {
186 pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
187 }
188 else {
189 pagingSelect.append("select * from ( ");
190 }
191 pagingSelect.append(sql);
192 if (hasOffset) {
193 pagingSelect.append(" ) row_ where rownum <= ?) where rownum_ > ?");
194 }
195 else {
196 pagingSelect.append(" ) where rownum <= ?");
197 }
198
199 if (isForUpdate) pagingSelect.append(" for update");
200
201 return pagingSelect.toString();
202 }
203
204 public String getForUpdateString(String aliases) {
205 return getForUpdateString() + " of " + aliases;
206 }
207
208 public String getForUpdateNowaitString(String aliases) {
209 return getForUpdateString() + " of " + aliases + " nowait";
210 }
211
212 public boolean bindLimitParametersInReverseOrder() {
213 return true;
214 }
215
216 public boolean useMaxForLimit() {
217 return true;
218 }
219
220 public boolean forUpdateOfColumns() {
221 return true;
222 }
223
224 public String getQuerySequencesString() {
225 return "select sequence_name from user_sequences";
226 }
227
228 public String getSelectGUIDString() {
229 return "select rawtohex(sys_guid()) from dual";
230 }
231
232
244 public SQLExceptionConverter buildSQLExceptionConverter() {
245 return new ExceptionConverter( getViolatedConstraintNameExtracter() );
246 }
247
248 public ViolatedConstraintNameExtracter getViolatedConstraintNameExtracter() {
249 return EXTRACTER;
250 }
251
252 private static class ExceptionConverter extends ErrorCodeConverter {
253 private int[] sqlGrammarCodes = new int[] { 900, 903, 904, 917, 936, 942, 17006 };
254 private int[] integrityViolationCodes = new int[] { 1, 1400, 2291, 2292 };
255 private int[] lockAcquisitionCodes = new int[] { 54, 55 };
256
257 public ExceptionConverter(ViolatedConstraintNameExtracter extracter) {
258 super(extracter);
259 }
260
261 protected int[] getSQLGrammarErrorCodes() {
262 return sqlGrammarCodes;
263 }
264
265 protected int[] getIntegrityViolationErrorCodes() {
266 return integrityViolationCodes;
267 }
268
269 protected int[] getLockAcquisitionErrorCodes() {
270 return lockAcquisitionCodes;
271 }
272 }
273
274 private static ViolatedConstraintNameExtracter EXTRACTER = new TemplatedViolatedConstraintNameExtracter() {
275
276
282 public String extractConstraintName(SQLException sqle) {
283 int errorCode = JDBCExceptionHelper.extractErrorCode(sqle);
284 if ( errorCode == 1 || errorCode == 2291 || errorCode == 2292 ) {
285 return extractUsingTemplate( "constraint (", ") violated", sqle.getMessage() );
286 }
287 else if ( errorCode == 1400 ) {
288 return null;
290 }
291 else {
292 return null;
293 }
294 }
295
296 };
297
298 int oracletypes_cursor_value = 0;
300 public int registerResultSetOutParameter(java.sql.CallableStatement statement,int col) throws SQLException {
301 if(oracletypes_cursor_value==0) {
302 try {
303 Class types = ReflectHelper.classForName("oracle.jdbc.driver.OracleTypes");
304 oracletypes_cursor_value = types.getField("CURSOR").getInt(types.newInstance());
305 } catch (Exception se) {
306 throw new HibernateException("Problem while trying to load or access OracleTypes.CURSOR value",se);
307 }
308 }
309 statement.registerOutParameter(col, oracletypes_cursor_value);
311 col++;
312 return col;
313 }
314
315 public ResultSet getResultSet(CallableStatement ps) throws SQLException {
316 ps.execute();
317 ResultSet rs = (ResultSet ) ps.getObject(1);
318 return rs;
319 }
320
321 public boolean supportsUnionAll() {
322 return true;
323 }
324
325 public boolean supportsCommentOn() {
326 return true;
327 }
328
329 public boolean supportsTemporaryTables() {
330 return true;
331 }
332
333 public String generateTemporaryTableName(String baseTableName) {
334 String name = super.generateTemporaryTableName(baseTableName);
335 return name.length() > 30 ? name.substring( 1, 30 ) : name;
336 }
337
338 public String getTemporaryTableCreationCommand() {
339 return "create global temporary table";
340 }
341
342 public String getTemporaryTableCreationPostfix() {
343 return "on commit delete rows";
344 }
345
346 }
347 |
Popular Tags |
Java API By Example, From Geeks To Geeks.