DB2Platform


1   /*
2    * The contents of this file are subject to the terms 
3    * of the Common Development and Distribution License 
4    * (the "License").  You may not use this file except 
5    * in compliance with the License.
6    * 
7    * You can obtain a copy of the license at 
8    * glassfish/bootstrap/legal/CDDLv1.0.txt or 
9    * https://glassfish.dev.java.net/public/CDDLv1.0.html. 
10   * See the License for the specific language governing 
11   * permissions and limitations under the License.
12   * 
13   * When distributing Covered Code, include this CDDL 
14   * HEADER in each file and include the License file at 
15   * glassfish/bootstrap/legal/CDDLv1.0.txt.  If applicable, 
16   * add the following below this CDDL HEADER, with the 
17   * fields enclosed by brackets "[]" replaced with your 
18   * own identifying information: Portions Copyright [yyyy] 
19   * [name of copyright owner]
20   */
21  // Copyright (c) 1998, 2006, Oracle. All rights reserved.  
22  package oracle.toplink.essentials.platform.database;
23  
24  import java.io.*;
25  import java.sql.*;
26  import java.util.*;
27  
28  import oracle.toplink.essentials.exceptions.ValidationException;
29  import oracle.toplink.essentials.expressions.*;
30  import oracle.toplink.essentials.internal.helper.*;
31  import oracle.toplink.essentials.queryframework.*;
32  import oracle.toplink.essentials.internal.databaseaccess.*;
33  import oracle.toplink.essentials.internal.sessions.AbstractSession;
34  import oracle.toplink.essentials.internal.expressions.ParameterExpression;
35  import oracle.toplink.essentials.internal.helper.BasicTypeHelperImpl;
36  
37  /**
38   * <p><b>Purpose</b>: Provides DB2 specific behaviour.
39   * <p><b>Responsibilities</b>:<ul>
40   * <li> Native SQL for byte[], Date, Time, & Timestamp.
41   * <li> Support for table qualified names.
42   * </ul>
43   *
44   * @since TOPLink/Java 1.0
45   */
46  public class DB2Platform extends oracle.toplink.essentials.platform.database.DatabasePlatform {
47  
48      /**
49       * INTERNAL:
50       * Append a byte[] in native DB@ format BLOB(hexString) if usesNativeSQL(),
51       * otherwise use ODBC format from DatabasePLatform.
52       */
53      protected void appendByteArray(byte[] bytes, Writer writer) throws IOException {
54          if (usesNativeSQL()) {
55              writer.write("BLOB(x'");
56              Helper.writeHexString(bytes, writer);
57              writer.write("')");
58          } else {
59              super.appendByteArray(bytes, writer);
60          }
61      }
62  
63      /**
64       * INTERNAL:
65       * Appends the Date in native format if usesNativeSQL() otherwise use ODBC format from DatabasePlatform.
66       * Native format: 'mm/dd/yyyy'
67       */
68      protected void appendDate(java.sql.Date   date, Writer writer) throws IOException {
69          if (usesNativeSQL()) {
70              appendDB2Date(date, writer);
71          } else {
72              super.appendDate(date, writer);
73          }
74      }
75  
76      /**
77       * INTERNAL:
78       * Write a timestamp in DB2 specific format (mm/dd/yyyy).
79       */
80      protected void appendDB2Date(java.sql.Date   date, Writer writer) throws IOException {
81          writer.write("'");
82          // PERF: Avoid deprecated get methods, that are now very inefficient and used from toString.
83          Calendar calendar = Helper.allocateCalendar();
84          calendar.setTime(date);
85  
86          if ((calendar.get(Calendar.MONTH) + 1) < 10) {
87              writer.write('0');
88          }
89          writer.write(Integer.toString(calendar.get(Calendar.MONTH) + 1));
90          writer.write('/');
91          if (calendar.get(Calendar.DATE) < 10) {
92              writer.write('0');
93          }
94          writer.write(Integer.toString(calendar.get(Calendar.DATE)));
95          writer.write('/');
96          writer.write(Integer.toString(calendar.get(Calendar.YEAR)));
97          writer.write("'");
98  
99          Helper.releaseCalendar(calendar);
100     }
101 
102     /**
103      * INTERNAL:
104      * Write a timestamp in DB2 specific format (yyyy-mm-dd-hh.mm.ss.ffffff).
105      */
106     protected void appendDB2Timestamp(java.sql.Timestamp   timestamp, Writer writer) throws IOException {
107         // PERF: Avoid deprecated get methods, that are now very inefficient and used from toString.
108         Calendar calendar = Helper.allocateCalendar();
109         calendar.setTime(timestamp);
110 
111         writer.write(Helper.printDate(calendar));
112         writer.write('-');
113         if (calendar.get(Calendar.HOUR_OF_DAY) < 10) {
114             writer.write('0');
115         }
116         writer.write(Integer.toString(calendar.get(Calendar.HOUR_OF_DAY)));
117         writer.write('.');
118         if (calendar.get(Calendar.MINUTE) < 10) {
119             writer.write('0');
120         }
121         writer.write(Integer.toString(calendar.get(Calendar.MINUTE)));
122         writer.write('.');
123         if (calendar.get(Calendar.SECOND) < 10) {
124             writer.write('0');
125         }
126         writer.write(Integer.toString(calendar.get(Calendar.SECOND)));
127         writer.write('.');
128 
129         Helper.releaseCalendar(calendar);
130 
131         // Must truncate the nanos to six decimal places,
132         // it is actually a complex algorithm...
133         String   nanoString = Integer.toString(timestamp.getNanos());
134         int numberOfZeros = 0;
135         for (int num = Math.min(9 - nanoString.length(), 6); num > 0; num--) {
136             writer.write('0');
137             numberOfZeros++;
138         }
139         if ((nanoString.length() + numberOfZeros) > 6) {
140             nanoString = nanoString.substring(0, (6 - numberOfZeros));
141         }
142         writer.write(nanoString);
143     }
144 
145     /**
146      * INTERNAL:
147      * Write a timestamp in DB2 specific format (yyyy-mm-dd-hh.mm.ss.ffffff).
148      */
149     protected void appendDB2Calendar(Calendar calendar, Writer writer) throws IOException {
150         int hour;
151         int minute;
152         int second;
153         if (!Helper.getDefaultTimeZone().equals(calendar.getTimeZone())) {
154             // Must convert the calendar to the local timezone if different, as dates have no timezone (always local).
155             Calendar localCalendar = Helper.allocateCalendar();
156             JavaPlatform.setTimeInMillis(localCalendar, JavaPlatform.getTimeInMillis(calendar));
157             hour = calendar.get(Calendar.HOUR_OF_DAY);
158             minute = calendar.get(Calendar.MINUTE);
159             second = calendar.get(Calendar.SECOND);
160             Helper.releaseCalendar(localCalendar);
161         } else {
162             hour = calendar.get(Calendar.HOUR_OF_DAY);
163             minute = calendar.get(Calendar.MINUTE);
164             second = calendar.get(Calendar.SECOND);
165         }
166         writer.write(Helper.printDate(calendar));
167         writer.write('-');
168         if (hour < 10) {
169             writer.write('0');
170         }
171         writer.write(Integer.toString(hour));
172         writer.write('.');
173         if (minute < 10) {
174             writer.write('0');
175         }
176         writer.write(Integer.toString(minute));
177         writer.write('.');
178         if (second < 10) {
179             writer.write('0');
180         }
181         writer.write(Integer.toString(second));
182         writer.write('.');
183 
184         // Must truncate the nanos to six decimal places,
185         // it is actually a complex algorithm...
186         String   millisString = Integer.toString(calendar.get(Calendar.MILLISECOND));
187         int numberOfZeros = 0;
188         for (int num = Math.min(3 - millisString.length(), 3); num > 0; num--) {
189             writer.write('0');
190             numberOfZeros++;
191         }
192         if ((millisString.length() + numberOfZeros) > 3) {
193             millisString = millisString.substring(0, (3 - numberOfZeros));
194         }
195         writer.write(millisString);
196     }
197 
198     /**
199      * INTERNAL:
200      * Append the Time in Native format if usesNativeSQL() otherwise use ODBC format from DAtabasePlatform.
201      * Native Format: 'hh:mm:ss'
202      */
203     protected void appendTime(java.sql.Time   time, Writer writer) throws IOException {
204         if (usesNativeSQL()) {
205             writer.write("'");
206             writer.write(Helper.printTime(time));
207             writer.write("'");
208         } else {
209             super.appendTime(time, writer);
210         }
211     }
212 
213     /**
214      * INTERNAL:
215      * Append the Timestamp in native format if usesNativeSQL() is true otherwise use ODBC format from DatabasePlatform.
216      * Native format: 'YYYY-MM-DD-hh.mm.ss.SSSSSS'
217      */
218     protected void appendTimestamp(java.sql.Timestamp   timestamp, Writer writer) throws IOException {
219         if (usesNativeSQL()) {
220             writer.write("'");
221             appendDB2Timestamp(timestamp, writer);
222             writer.write("'");
223         } else {
224             super.appendTimestamp(timestamp, writer);
225         }
226     }
227 
228     /**
229      * INTERNAL:
230      * Append the Timestamp in native format if usesNativeSQL() is true otherwise use ODBC format from DatabasePlatform.
231      * Native format: 'YYYY-MM-DD-hh.mm.ss.SSSSSS'
232      */
233     protected void appendCalendar(Calendar calendar, Writer writer) throws IOException {
234         if (usesNativeSQL()) {
235             writer.write("'");
236             appendDB2Calendar(calendar, writer);
237             writer.write("'");
238         } else {
239             super.appendCalendar(calendar, writer);
240         }
241     }
242 
243     protected Hashtable buildFieldTypes() {
244         Hashtable fieldTypeMapping = new Hashtable();
245 
246         fieldTypeMapping.put(Boolean  .class, new FieldTypeDefinition("SMALLINT DEFAULT 0", false));
247 
248         fieldTypeMapping.put(Integer  .class, new FieldTypeDefinition("INTEGER", false));
249         fieldTypeMapping.put(Long  .class, new FieldTypeDefinition("INTEGER", false));
250         fieldTypeMapping.put(Float  .class, new FieldTypeDefinition("FLOAT", false));
251         fieldTypeMapping.put(Double  .class, new FieldTypeDefinition("FLOAT", false));
252         fieldTypeMapping.put(Short  .class, new FieldTypeDefinition("SMALLINT", false));
253         fieldTypeMapping.put(Byte  .class, new FieldTypeDefinition("SMALLINT", false));
254         fieldTypeMapping.put(java.math.BigInteger  .class, new FieldTypeDefinition("BIGINT", false));
255         fieldTypeMapping.put(java.math.BigDecimal  .class, new FieldTypeDefinition("DECIMAL", 15));
256         fieldTypeMapping.put(Number  .class, new FieldTypeDefinition("DECIMAL", 15));
257 
258         fieldTypeMapping.put(String  .class, new FieldTypeDefinition("VARCHAR", 255));
259         fieldTypeMapping.put(Character  .class, new FieldTypeDefinition("CHAR", 1));
260         fieldTypeMapping.put(Byte  [].class, new FieldTypeDefinition("BLOB", 64000));
261         fieldTypeMapping.put(Character  [].class, new FieldTypeDefinition("CLOB", 64000));
262         fieldTypeMapping.put(byte[].class, new FieldTypeDefinition("BLOB", 64000));
263         fieldTypeMapping.put(char[].class, new FieldTypeDefinition("CLOB", 64000));        
264         fieldTypeMapping.put(java.sql.Blob  .class, new FieldTypeDefinition("BLOB", 64000));
265         fieldTypeMapping.put(java.sql.Clob  .class, new FieldTypeDefinition("CLOB", 64000));  
266 
267         fieldTypeMapping.put(java.sql.Date  .class, new FieldTypeDefinition("DATE", false));
268         fieldTypeMapping.put(java.sql.Time  .class, new FieldTypeDefinition("TIME", false));
269         fieldTypeMapping.put(java.sql.Timestamp  .class, new FieldTypeDefinition("TIMESTAMP", false));
270 
271         return fieldTypeMapping;
272     }
273 
274     /**
275      * INTERNAL:
276      * returns the maximum number of characters that can be used in a field
277      * name on this platform.
278      */
279     public int getMaxFieldNameSize() {
280         return 128;
281     }
282 
283     /**
284      * INTERNAL:
285      * returns the maximum number of characters that can be used in a foreign key
286      * name on this platform.
287      */
288     public int getMaxForeignKeyNameSize() {
289         return 18;
290     }
291 
292     /**
293      * INTERNAL:
294      * Return the catalog information through using the native SQL catalog selects.
295      * This is required because many JDBC driver do not support meta-data.
296      * Willcards can be passed as arguments.
297      */
298     public Vector getNativeTableInfo(String   table, String   creator, AbstractSession session) {
299         String   query = "SELECT * FROM SYSIBM.SYSTABLES WHERE TBCREATOR NOT IN ('SYS', 'SYSTEM')";
300         if (table != null) {
301             if (table.indexOf('%') != -1) {
302                 query = query + " AND TBNAME LIKE " + table;
303             } else {
304                 query = query + " AND TBNAME = " + table;
305             }
306         }
307         if (creator != null) {
308             if (creator.indexOf('%') != -1) {
309                 query = query + " AND TBCREATOR LIKE " + creator;
310             } else {
311                 query = query + " AND TBCREATOR = " + creator;
312             }
313         }
314         return session.executeSelectingCall(new oracle.toplink.essentials.queryframework.SQLCall(query));
315     }
316 
317     /**
318      * INTERNAL:
319      * Used for sp calls.
320      */
321     public String   getProcedureCallHeader() {
322         return "CALL ";
323     }
324     
325     /**
326      * INTERNAL:
327      */
328     public String   getSelectForUpdateString() {
329         return " FOR UPDATE";
330     }
331     
332     /**
333      * INTERNAL:
334      * Used for stored procedure defs.
335      */
336     public String   getProcedureEndString() {
337         return "END";
338     }
339     
340     /**
341      * INTERNAL:
342      * Used for stored procedure defs.
343      */
344     public String   getProcedureBeginString() {
345         return "BEGIN";
346     }
347     
348     /**
349      * INTERNAL:
350      * Used for stored procedure defs.
351      */
352     public String   getProcedureAsString() {
353         return "";
354     }
355     
356     /**
357      * INTERNAL:
358      * This is required in the construction of the stored procedures with
359      * output parameters
360      */
361     public boolean shouldPrintOutputTokenAtStart() {
362         return true;
363     }
364     
365     /**
366      * INTERNAL:
367      * This method returns the query to select the timestamp
368      * from the server for DB2.
369      */
370     public ValueReadQuery getTimestampQuery() {
371         if (timestampQuery == null) {
372             timestampQuery = new ValueReadQuery();
373             timestampQuery.setSQLString("SELECT DISTINCT CURRENT TIMESTAMP FROM SYSIBM.SYSTABLES");
374         }
375         return timestampQuery;
376     }
377 
378     /**
379      * INTERNAL:
380      * Initialize any platform-specific operators
381      */
382     protected void initializePlatformOperators() {
383         super.initializePlatformOperators();
384 
385         addOperator(ExpressionOperator.simpleFunction(ExpressionOperator.ToUpperCase, "UCASE"));
386         addOperator(ExpressionOperator.simpleFunction(ExpressionOperator.ToLowerCase, "LCASE"));
387         addOperator(concatOperator());
388         addOperator(ExpressionOperator.simpleTwoArgumentFunction(ExpressionOperator.Instring, "Locate"));
389         addOperator(todayOperator());
390         addOperator(currentDateOperator());
391         //CR#2811076 some missing DB2 functions added.
392         addOperator(ExpressionOperator.simpleFunction(ExpressionOperator.ToNumber, "DECIMAL"));
393         addOperator(ExpressionOperator.simpleFunction(ExpressionOperator.ToChar, "CHAR"));
394         addOperator(ExpressionOperator.simpleFunction(ExpressionOperator.DateToString, "CHAR"));
395         addOperator(ExpressionOperator.simpleFunction(ExpressionOperator.ToDate, "DATE"));
396     }
397 
398     public boolean isDB2() {
399         return true;
400     }
401 
402     /**
403      * INTERNAL:
404      * Builds a table of maximum numeric values keyed on java class. This is used for type testing but
405      * might also be useful to end users attempting to sanitize values.
406      * <p><b>NOTE</b>: BigInteger & BigDecimal maximums are dependent upon their precision & Scale
407      */
408     public Hashtable maximumNumericValues() {
409         Hashtable values = new Hashtable();
410 
411         values.put(Integer  .class, new Integer  (Integer.MAX_VALUE));
412         values.put(Long  .class, new Long  ((long)Integer.MAX_VALUE));
413         values.put(Float  .class, new Float  (123456789));
414         values.put(Double  .class, new Double  ((double)Float.MAX_VALUE));
415         values.put(Short  .class, new Short  (Short.MAX_VALUE));
416         values.put(Byte  .class, new Byte  (Byte.MAX_VALUE));
417         values.put(java.math.BigInteger  .class, new java.math.BigInteger  ("999999999999999"));
418         values.put(java.math.BigDecimal  .class, new java.math.BigDecimal  ("0.999999999999999"));
419         return values;
420     }
421 
422     /**
423      * INTERNAL:
424      * Builds a table of minimum numeric values keyed on java class. This is used for type testing but
425      * might also be useful to end users attempting to sanitize values.
426      * <p><b>NOTE</b>: BigInteger & BigDecimal minimums are dependent upon their precision & Scale
427      */
428     public Hashtable minimumNumericValues() {
429         Hashtable values = new Hashtable();
430 
431         values.put(Integer  .class, new Integer  (Integer.MIN_VALUE));
432         values.put(Long  .class, new Long  ((long)Integer.MIN_VALUE));
433         values.put(Float  .class, new Float  (-123456789));
434         values.put(Double  .class, new Double  ((double)Float.MIN_VALUE));
435         values.put(Short  .class, new Short  (Short.MIN_VALUE));
436         values.put(Byte  .class, new Byte  (Byte.MIN_VALUE));
437         values.put(java.math.BigInteger  .class, new java.math.BigInteger  ("-999999999999999"));
438         values.put(java.math.BigDecimal  .class, new java.math.BigDecimal  ("-0.999999999999999"));
439         return values;
440     }
441 
442     /**
443      * INTERNAL:
444      * Allow for the platform to ignore exceptions.
445      * This is required for DB2 which throws no-data modified as an exception.
446      */
447     public boolean shouldIgnoreException(SQLException exception) {
448         if (exception.getMessage().equals("No data found") || exception.getMessage().equals("No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table") || (exception.getErrorCode() == 100)) {
449             return true;
450         }
451         return super.shouldIgnoreException(exception);
452     }
453 
454     /**
455      * INTERNAL:
456      * JDBC defines and outer join syntax, many drivers do not support this. So we normally avoid it.
457      */
458     public boolean shouldUseJDBCOuterJoinSyntax() {
459         return false;
460     }
461 
462     /**
463      * The Concat operator is of the form
464      * .... VARCHAR ( <operand1> || <operand2> )
465      */
466     private ExpressionOperator concatOperator() {
467         ExpressionOperator exOperator = new ExpressionOperator();
468         exOperator.setType(ExpressionOperator.FunctionOperator);
469         exOperator.setSelector(ExpressionOperator.Concat);
470         Vector v = new Vector(5);
471         v.addElement("VARCHAR(");
472         v.addElement(" || ");
473         v.addElement(")");
474         exOperator.printsAs(v);
475         exOperator.bePrefix();
476         exOperator.setNodeClass(ClassConstants.FunctionExpression_Class);
477         return exOperator;
478     }
479 
480     /**
481      * INTERNAL:
482      * Create the sysdate operator for this platform
483      */
484     protected ExpressionOperator todayOperator() {
485         return ExpressionOperator.simpleFunctionNoParentheses(ExpressionOperator.Today, "CURRENT TIMESTAMP");
486     }
487 
488     /**
489      * INTERNAL:
490      * Create the current date operator for this platform
491      */
492     protected ExpressionOperator currentDateOperator() {
493         return ExpressionOperator.simpleFunctionNoParentheses(ExpressionOperator.currentDate, "CURRENT DATE");
494     }
495 
496     /**
497      * INTERNAL:
498      * Build the identity query for native sequencing.
499      */
500     public ValueReadQuery buildSelectQueryForNativeSequence() {
501         ValueReadQuery selectQuery = new ValueReadQuery();
502         StringWriter writer = new StringWriter();
503         writer.write("VALUES IDENTITY_VAL_LOCAL()");
504         selectQuery.setSQLString(writer.toString());
505         return selectQuery;
506     }
507 
508     /**
509      * INTERNAL:
510      * Append the receiver's field 'identity' constraint clause to a writer
511      */
512     public void printFieldIdentityClause(Writer writer) throws ValidationException {
513         try {
514             writer.write(" GENERATED ALWAYS AS IDENTITY");
515         } catch (IOException ioException) {
516             throw ValidationException.fileError(ioException);
517         }
518     }
519 
520     /**
521      * INTERNAL:
522      * If native sequencing is being used on DB2 then the values must be
523      * retrieved after the insert.
524      * This method is to be used *ONLY* by sequencing classes
525      */
526     public boolean shouldNativeSequenceAcquireValueAfterInsert() {
527         return true;
528     }
529 
530     /**
531      * Return true if the receiver uses host sequence numbers, generated on the database.
532      * DB2 does through AS IDENTITY field types.
533      */
534     public boolean supportsNativeSequenceNumbers() {
535         return true;
536     }
537 
538     /**
539      * INTERNAL:
540      */
541      public boolean supportsGlobalTempTables() {
542          return true;
543      }
544 
545     /**
546      * INTERNAL:
547      */
548      protected String   getCreateTempTableSqlPrefix() {
549          return "DECLARE GLOBAL TEMPORARY TABLE ";
550      }
551 
552     /**
553      * INTERNAL:
554      */
555      public DatabaseTable getTempTableForTable(DatabaseTable table) {
556          DatabaseTable tempTable =  super.getTempTableForTable(table);
557          tempTable.setTableQualifier("session");
558          return tempTable;
559      }
560 
561     /**
562      * INTERNAL:
563      */
564     protected String   getCreateTempTableSqlSuffix() {
565         return " ON COMMIT DELETE ROWS NOT LOGGED";
566     }          
567 
568     /**
569      * INTERNAL:
570      */
571      protected String   getCreateTempTableSqlBodyForTable(DatabaseTable table) {
572          return " LIKE " + table.getQualifiedName();
573      }               
574 
575     /**
576      * INTERNAL:
577      */
578      public boolean dontBindUpdateAllQueryUsingTempTables() {
579          return true;
580      }
581 
582     public void writeParameterMarker(Writer writer, ParameterExpression parameter) throws IOException {
583         // DB2 requires cast around parameter markers if both operands of certian
584         // operators are parameter markers
585         // This method generates CAST for parameter markers whose type is correctly
586         // identified by the query compiler
587         String   paramaterMarker = "?";
588         Object   type = parameter.getType();
589         if(type != null) {
590             BasicTypeHelperImpl typeHelper = BasicTypeHelperImpl.getInstance();
591             String   castType = null;
592             if (typeHelper.isBooleanType(type) || typeHelper.isByteType(type) || typeHelper.isShortType(type)) {
593                 castType = "SMALLINT";
594             } else if (typeHelper.isIntType(type)) {
595                 castType = "INTEGER";
596             } else if (typeHelper.isLongType(type)) {
597                 castType = "BIGINT";
598             } else if (typeHelper.isFloatType(type)) {
599                 castType = "REAL";
600             } else if (typeHelper.isDoubleType(type)) {
601                 castType = "DOUBLE";
602             } else if (typeHelper.isStringType(type)) {
603                 castType = "VARCHAR(32672)";
604             }
605 
606             if(castType != null){
607                 paramaterMarker = "CAST (? AS " + castType + " )";
608             }
609         }
610         writer.write(paramaterMarker);
611     }
612 
613     /**
614      * INTERNAL
615      * Allows platform to override adding a literal to a call.
616      * Overriddeen to avoid binding of literals
617      */
618     public void appendLiteralToCall(Call call, Writer writer, Object   literal) {
619         int nParametersToAdd = appendParameterInternal(call, writer, literal);
620         for(int i=0; i < nParametersToAdd; i++ ) {
621             ((DatabaseCall)call).getParameterTypes().addElement(DatabaseCall.LITERAL);
622         }
623     }
624 }
625
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags