001    // Copyright (c) 2001 Hursh Jain (http://www.mollypages.org) 
002    // The Molly framework is freely distributable under the terms of an
003    // MIT-style license. For details, see the molly pages web site at:
004    // http://www.mollypages.org/. Use, modify, have fun !
005    
006    package fc.jdbc.dbo;
007    
008    import java.sql.*;
009    import java.util.*;
010    
011    import fc.jdbc.*;
012    import fc.io.*;
013    import fc.util.*;
014    
015    /**
016    SQL and java type related functions 
017    */
018    public class Types
019    {
020    static final String AVAIL = "available";
021    static final String INTMAX = "intmax";
022    
023    String    PStmt_SetXXX_Length_Param_Type;
024    boolean   mysqlBooleanHack;
025    boolean   booleanObjectForNullableBooleans;
026    SystemLog log;
027    
028    /**
029    Constructs a new object. 
030    
031    @param  log   logging destination
032    @param  props PropertyManager that may contain:
033            <blockquote>
034            <ul>
035            <li>dbspecific.mysql.boolean_hack</li>
036            <li>generate.preparedstatement.setxxx.length_param</li>
037            </ul>
038            If the above key values are missing, then the
039            defaults are used (see usage info for {@link Generate})
040            </blockquote>
041    */
042    public Types(SystemLog log, PropertyMgr props)
043      {
044      this.log = log;
045      String key = "dbspecific.mysql.boolean_hack";
046      mysqlBooleanHack = 
047        Boolean.valueOf(props.get(key, "true")).booleanValue();
048    
049      key = "generate.booleanObjectForNullableBooleans";
050      booleanObjectForNullableBooleans = 
051        Boolean.valueOf(props.get(key, "true")).booleanValue();
052    
053      key = "generate.preparedstatement.setxxx.length_param";
054      
055      String temp = props.get(key, AVAIL);
056      if (temp == null) {
057        log.warn("Did not understand value for key:", key, " defaulting to using available()");
058        PStmt_SetXXX_Length_Param_Type = AVAIL;
059        }
060      else {
061        temp = temp.toLowerCase().intern();
062        if (temp == INTMAX)
063          PStmt_SetXXX_Length_Param_Type = INTMAX;
064        else if (temp == AVAIL)
065          PStmt_SetXXX_Length_Param_Type = AVAIL;
066        else { //defaults to avail
067          log.warn("Did not understand value for key:", key, "defaulting to using available()");
068          PStmt_SetXXX_Length_Param_Type = AVAIL;
069          }
070        }
071      }
072      
073    /**
074    Returns a suitable getResultXXX method name to retrieve
075    the data for some column. Used when generating manager code.
076    
077    @param  java_sql_type   the {@link java.sql.Type} corresponding
078                to the column for which the method will
079                be generated
080    @param  pos       A string containing the column index 
081                number (<b>starts from 1)</b>
082    */
083    public String getResultSetMethod(int java_sql_type, String pos, ColumnData cd)
084    throws SQLException
085      {
086      return createMethod("get", java_sql_type, pos, null, null, cd);
087      }
088    
089    
090    /**
091    Returns a suitable getResultXXX method name to retrieve
092    the data for some column (which will use a runtime prefix argument
093    prepended to the column name).
094    
095    @param  java_sql_type   the {@link java.sql.Type} corresponding
096                to the column for which the method will
097                be generated
098    @param  name      column name
099    */
100    public String getRuntimeResultSetMethod(int java_sql_type, String name, ColumnData cd)
101    throws SQLException
102      {
103      return createMethod("getruntime", java_sql_type, name, null, null, cd);
104      }
105    
106    /**
107    Returns a suitable setXXX method name to set the prepared statement
108    placeholder data for some column. Used when generating manager code. For
109    example, for an integer column at position 3 and a variable name "x" (which
110    would contain a integer value at runtime), the returned method will be of
111    the form: <tt>"setInt(3, x)"</tt>
112    
113    @param  java_sql_type   the {@link java.sql.Type} corresponding
114                to the column for which the method will
115                be generated
116    @param  pos       a String containing the column index number
117                (<b>starts from 1</b>) or a runtime value
118                to insert in generate code like "pos++"
119    @param  varname     the name of the variable containing the
120                value to be set at runtime.
121    */
122    public String getPreparedStmtSetMethod(
123     int java_sql_type, String psvar, String pos, String varname, ColumnData cd) throws SQLException
124      {
125      return createMethod("set", java_sql_type, pos, varname, psvar, cd);
126      }
127    
128    public String getPreparedStmtSetNullMethod(
129     int java_sql_type, String pos, String varname, ColumnData cd) throws SQLException
130      {
131      return createMethod("setNull", java_sql_type, pos, varname, null, cd);
132      }
133      
134      
135    private String createMethod(
136      String prefix, int java_sql_type, String pos, String varname,
137      String psvar, ColumnData cd)
138    throws SQLException
139      {
140      //PreparedStatement methods for setAsciiStream etc.,
141      //needs an additional length parameter, in which
142      //case this will be set to true
143      boolean needs_length = false;
144      
145      String result = null;
146      
147      String typestr = null;
148        switch(java_sql_type)
149          {
150           //integral types
151          case java.sql.Types.TINYINT:  
152            typestr = (mysqlBooleanHack) 
153                  ? "Boolean(": "Byte("; 
154            break;
155        
156        case java.sql.Types.SMALLINT: typestr = "Short("; break;
157        case java.sql.Types.INTEGER:  typestr = "Int(";   break;
158        case java.sql.Types.BIGINT:   typestr = "Long(";  break;
159                
160        //floating
161          case java.sql.Types.FLOAT:  typestr  = "Float(";  break;
162        case java.sql.Types.REAL:   typestr  = "Float(";  break;
163          case java.sql.Types.DOUBLE: typestr = "Double("; break;
164        
165        //arbitrary precision
166        case java.sql.Types.DECIMAL: typestr = "BigDecimal("; break; 
167          case java.sql.Types.NUMERIC: typestr = "BigDecimal("; break;
168          
169        //char
170        case java.sql.Types.CHAR:    typestr = "String("; break;
171          case java.sql.Types.VARCHAR: typestr = "String("; break;
172        case java.sql.Types.LONGVARCHAR: 
173            typestr = "AsciiStream("; 
174            needs_length = true;
175            break;
176          
177        //date-time
178        case java.sql.Types.DATE: typestr = "Date("; break;
179          case java.sql.Types.TIME: typestr = "Time("; break;
180          case java.sql.Types.TIMESTAMP: typestr = "Timestamp("; break;
181          
182        //stream and binary
183        case java.sql.Types.BLOB: typestr = "Blob("; break;
184          case java.sql.Types.CLOB: typestr = "Clob("; break;
185          case java.sql.Types.BINARY: typestr = "Bytes("; break;
186          case java.sql.Types.VARBINARY: typestr = "Bytes("; break;
187          case java.sql.Types.LONGVARBINARY: 
188            typestr = "BinaryStream("; 
189            needs_length = true;
190            break;
191          
192        //misc
193        case java.sql.Types.ARRAY: typestr = "Array("; break;
194    
195        case java.sql.Types.BIT: 
196        case java.sql.Types.BOOLEAN: 
197          typestr = "Boolean("; 
198          break;
199          
200        case java.sql.Types.DATALINK: unsupported(java.sql.Types.DATALINK); break;
201          case java.sql.Types.DISTINCT: unsupported(java.sql.Types.DISTINCT); break;
202          case java.sql.Types.JAVA_OBJECT: unsupported(java.sql.Types.JAVA_OBJECT); break;
203          case java.sql.Types.NULL: unsupported(java.sql.Types.NULL); break;
204          case java.sql.Types.OTHER: unsupported(java.sql.Types.OTHER); break;
205          case java.sql.Types.REF: typestr = "Ref("; break;
206          case java.sql.Types.STRUCT: typestr = "Struct("; break;
207        
208        default: unsupported(java_sql_type); 
209        }
210    
211      if (prefix.equals("setNull"))
212        {
213        result = "setNull(" 
214              + pos + "," 
215              + java_sql_type + ")"
216                + "/*"+getSQLTypeName(java_sql_type)+"*/";
217        }
218        
219      else if (prefix.equals("set"))
220        {
221        result = "";
222        
223        if (needs_length && PStmt_SetXXX_Length_Param_Type.equals(AVAIL)) {
224          result += "try { "; 
225          }
226          
227        if (cd.useBooleanObject()) { 
228          //set needs "Object", type string = Boolean or boolean
229          //in JDBC, cannot set null like so: setBoolean(1, null)
230          result += psvar + "setObject(" + pos + ", " + varname;
231          }
232        else{
233          result += psvar + "set" + typestr + pos + ", " + varname;
234          }
235      
236        if (needs_length) 
237          {
238          result += ", ";
239          if (PStmt_SetXXX_Length_Param_Type.equals(AVAIL))
240            result += varname + ".available()";
241          else if (PStmt_SetXXX_Length_Param_Type.equals(INTMAX))
242            result += "Integer.MAX_VALUE";
243          }
244        result += "); ";
245    
246        if (needs_length && PStmt_SetXXX_Length_Param_Type.equals(AVAIL)) {
247          result += "} catch (IOException e) { throw new SQLException(e.toString()); } "; 
248          }
249        }
250        
251      else if (prefix.equals("get"))
252        {
253        if (cd.useBooleanObject()) { //"(Boolean) rs." so we put "rs." here
254          result = "((Boolean) rs.getObject(" + pos + "))";
255          }
256        else{
257          result = "get" + typestr + pos + ")";   
258          }
259        }
260        
261      else if (prefix.equals("getruntime"))
262        {
263        if (cd.useBooleanObject()) {  
264          result = "((Boolean) rs.getObject(prefix" + "+\"_" + pos + "\"))";        
265          }
266        else{
267          result = "get" + typestr + "prefix" + "+\"_" + pos + "\")";
268          }
269        }
270        
271      else{
272        throw new SQLException("I dont understand how to handle: " + prefix);
273        }
274        
275        return result;
276      }
277    
278    /**
279    Converts a value corresponding to {@link java.sql.Type} to the Java
280    type used to represent that type. The {@link java.sql.Type Sql-Type} is
281    returned by the jdbc driver for some column in a table and we map it to a
282    corresponding java type that will be used to represent/work with that sql
283    type in our java programs. This mapping follows the JDBC guidelines.
284    
285    Similar JDBC API method like {@link
286    ResultSetMetaData#getColumnTypeName(column)} and {@link
287    DatabaseMetaData#getColumns()} return tpye names that can be driver/db
288    specific (and don't have to correspond to Java types anyway).
289    
290    @param  java_sql_type    the {@link java.sql.Type} to convert to a
291                 java language type
292    @param  cd         used to find out if the column is nullable
293                 (this can have the effect of using primitive or
294                 object types, in some cases, for example, Boolean
295                 vs boolean)
296    **/
297    public String getJavaTypeFromSQLType(int java_sql_type, ColumnData cd) throws SQLException
298      {
299      boolean columnIsNullable = cd.isNullable();
300      String result = null;
301      switch (java_sql_type) 
302        {
303        //integral types
304          case java.sql.Types.TINYINT:  
305            result = (mysqlBooleanHack) ? "Boolean" : "byte";  
306            break;
307        
308        case java.sql.Types.SMALLINT: result = "short"; break;
309        case java.sql.Types.INTEGER:  result = "int";   break;
310        case java.sql.Types.BIGINT:   result = "long";  break;
311                
312        //floating
313          case java.sql.Types.FLOAT: result  = "float";  break;
314        case java.sql.Types.REAL: result   = "float";  break;
315          case java.sql.Types.DOUBLE: result = "double"; break;
316        
317        //arbitrary precision
318        case java.sql.Types.DECIMAL: result = "BigDecimal"; break; 
319          case java.sql.Types.NUMERIC: result = "BigDecimal"; break;
320          
321        //char
322        case java.sql.Types.CHAR: result    = "String"; break;
323          case java.sql.Types.VARCHAR: result   = "String"; break;
324        case java.sql.Types.LONGVARCHAR: result = "InputStream"; break;
325          
326        //date-time
327        case java.sql.Types.DATE: result = "java.sql.Date"; break;
328          case java.sql.Types.TIME: result = "Time"; break;
329          case java.sql.Types.TIMESTAMP: result = "Timestamp"; break;
330          
331        //stream and binary
332        case java.sql.Types.BLOB: result = "java.sql.Blob"; break;
333          case java.sql.Types.CLOB: result = "java.sql.Clob"; break;
334          case java.sql.Types.BINARY: result = "byte[]"; break;
335          case java.sql.Types.VARBINARY: result = "byte[]"; break;
336          case java.sql.Types.LONGVARBINARY: result = "InputStream"; break;
337          
338        //misc
339        case java.sql.Types.ARRAY: result = "java.sql.Array"; break;
340    
341        //note: postgres booleans are/seen as BIT by the driver.
342        case java.sql.Types.BIT: 
343        case java.sql.Types.BOOLEAN: 
344          if (columnIsNullable && booleanObjectForNullableBooleans) {
345            result = "Boolean";
346            }
347          else{
348            result = "boolean";
349            }
350            break;
351    
352        case java.sql.Types.DATALINK: unsupported(java.sql.Types.DATALINK); break;
353          case java.sql.Types.DISTINCT: unsupported(java.sql.Types.DISTINCT); break;
354          case java.sql.Types.JAVA_OBJECT: unsupported(java.sql.Types.JAVA_OBJECT); break;
355          case java.sql.Types.NULL: unsupported(java.sql.Types.NULL); break;
356          case java.sql.Types.OTHER: unsupported(java.sql.Types.OTHER); break;
357          case java.sql.Types.REF: result = "java.sql.Ref"; break;
358          case java.sql.Types.STRUCT: result = "java.sql.Struct"; break;
359        
360        default: unsupported(java_sql_type); 
361        }
362    
363      return result;
364      }
365    
366    /**
367    Uses the same conversion criteria as the {@link getJavaTypeFromSQLType()}
368    and then returns <tt>true</tt> if the Java type used to represent the
369    specified {@link java.sql.Type} is primitive (int, boolean etc) as opposed
370    to an Object type.
371    
372    @param  java_sql_type    the {@link java.sql.Type} for the corresponding
373                 java type.
374    @param  cd         used to find out if the column is nullable
375                 (this can have the effect of using primitive or
376                 object types, in some cases, for example, Boolean
377                 vs boolean)
378    */            
379    public boolean usesPrimitiveJavaType(int java_sql_type, ColumnData cd)
380    throws SQLException
381      {
382      boolean primitive = false;
383      switch (java_sql_type) 
384        {
385        //integral types
386          case java.sql.Types.TINYINT:  /*short*/
387          case java.sql.Types.SMALLINT: /*short*/ 
388        case java.sql.Types.INTEGER:  /*int*/  
389        case java.sql.Types.BIGINT:  /*long*/
390            primitive = true;
391            break;
392            
393        //floating
394          case java.sql.Types.FLOAT: /*float*/  
395          case java.sql.Types.REAL:  /*float*/  
396          case java.sql.Types.DOUBLE: /*double*/ 
397              primitive = true;
398            break;
399    
400        //arbitrary precision
401        case java.sql.Types.DECIMAL: /*BigDecimal*/ break; 
402          case java.sql.Types.NUMERIC: /*BigDecimal*/ break; 
403       
404        //char
405        case java.sql.Types.CHAR: /*String*/ break;
406        case java.sql.Types.VARCHAR: /*String*/ break;
407        case java.sql.Types.LONGVARCHAR: /*InputStream*/ break;
408          
409        //date-time
410        case java.sql.Types.DATE: /*java.sql.Date*/ break;
411          case java.sql.Types.TIME: /*Time*/ break;
412          case java.sql.Types.TIMESTAMP:/*Timestamp*/ break;
413          
414        //stream and binary
415        case java.sql.Types.BLOB:/*java.sql.Blob*/ break;
416          case java.sql.Types.CLOB:/*java.sql.Clob*/ break;
417          case java.sql.Types.BINARY: /*byte[]*/ break;
418          case java.sql.Types.VARBINARY:/*byte[]*/ break;
419          case java.sql.Types.LONGVARBINARY:/*InputStream*/ break;
420          
421        //misc
422        case java.sql.Types.ARRAY: /*java.sql.Array*/ break;
423        
424        case java.sql.Types.BIT:     /*boolean or Boolean*/ 
425        case java.sql.Types.BOOLEAN: /*boolean or Boolean*/ 
426          if (cd.useBooleanObject()) {
427            //primitive is already false
428            }
429          else{
430            primitive = true;
431            }
432          break;
433      
434        case java.sql.Types.DATALINK: unsupported(java.sql.Types.DATALINK); break;
435          case java.sql.Types.DISTINCT: unsupported(java.sql.Types.DISTINCT); break;
436          case java.sql.Types.JAVA_OBJECT: unsupported(java.sql.Types.JAVA_OBJECT); break;
437          case java.sql.Types.NULL: unsupported(java.sql.Types.NULL); break;
438          case java.sql.Types.OTHER: unsupported(java.sql.Types.OTHER); break;
439          case java.sql.Types.REF:  /*java.sql.Ref*/ break;
440          case java.sql.Types.STRUCT: /*java.sql.Struct*/ break;
441        
442        default: unsupported(java_sql_type); 
443        }
444    
445      return primitive;
446      }
447    
448    
449    /**
450    Uses the same conversion criteria as the {@link getJavaTypeFromSQLType()}
451    and then returns <tt>true</tt> if the Java type used to represent the
452    specified {@link java.sql.Type} is integral. This is used for creating
453    the inc/dec methods (only for short, int and long)
454    
455    @param  java_sql_type    the {@link java.sql.Type} for the corresponding
456                 java type
457    */            
458    public boolean usesSimpleIntegralJavaType(int java_sql_type)
459    throws SQLException
460      {
461      boolean simple = false;
462      
463        switch(java_sql_type)
464          {
465        case java.sql.Types.INTEGER:  /*int*/  
466        case java.sql.Types.BIGINT:  /*long*/
467            simple = true;
468            break;
469          }  
470      
471      return simple;
472      }
473    
474    
475    /**
476    Converts the {@link java.sql.Type} for some column (returned by the
477    driver) to a readable value. More convenient than using the "constant
478    field values" section of the not neatly arranged javadocs for {@link
479    java.sql.Type}.
480    <p>
481    Note, this method is different from {@link } because unlike {@link },
482    this simply returns the variable name corresponding to the parameter
483    value (for example, <tt>java.sql.Type.INTEGER == 4</tt> and passing
484    <tt>4</tt> to this method will return "<tt>INTEGER</tt>").
485    
486    @param java_sql_type  a type from {@link java.sql.Type}
487    */
488    public String getSQLTypeName(int java_sql_type)
489      {
490      switch (java_sql_type)
491        {
492        case 2003:  return "ARRAY"; 
493        case -5:  return "BIGINT"; 
494        case -2:  return "BINARY"; 
495        case -7:  return "BIT"; 
496        case 2004:  return "BLOB"; 
497        case 16:  return "BOOLEAN"; 
498        case 1:   return "CHAR"; 
499        case 2005:  return "CLOB"; 
500        case 70:  return "DATALINK"; 
501        case 91:  return "DATE"; 
502        case 3:   return "DECIMAL"; 
503        case 2001:  return "DISTINCT"; 
504        case 8:   return "DOUBLE"; 
505        case 6:   return "FLOAT"; 
506        case 4:   return "INTEGER"; 
507        case 2000:  return "JAVA_OBJECT"; 
508        case -4:  return "LONGVARBINARY"; 
509        case -1:  return "LONGVARCHAR"; 
510        case 0:   return "NULL"; 
511        case 2:   return "NUMERIC"; 
512        case 1111:  return "OTHER"; 
513        case 7:   return "REAL"; 
514        case 2006:  return "REF"; 
515        case 5:   return "SMALLINT"; 
516        case 2002:  return "STRUCT"; 
517        case 92:  return "TIME"; 
518        case 93:  return "TIMESTAMP"; 
519        case -6:  return "TINYINT"; 
520        case -3:  return "VARBINARY"; 
521        case 12:  return "VARCHAR"; 
522        default:  return "NOT KNOWN/ERROR";
523        }
524      }
525    
526    void unsupported(int type) throws SQLException {
527      throw new SQLException("This framework does not understand/support columns of this type. [java.sql.Type: " + type + "]"); 
528      }
529    
530    } //~Types
531