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    boolean   postgresGetObjectToString;
027    SystemLog log;
028    
029    /**
030    Constructs a new object. 
031    
032    @param  log   logging destination
033    @param  props PropertyManager that may contain:
034            <blockquote>
035            <ul>
036            <li>dbspecific.mysql.boolean_hack</li>
037            <li>generate.preparedstatement.setxxx.length_param</li>
038            </ul>
039            If the above key values are missing, then the
040            defaults are used (see usage info for {@link Generate})
041            </blockquote>
042    */
043    public Types(SystemLog log, PropertyMgr props)
044      {
045      this.log = log;
046      String key = "dbspecific.mysql.boolean_hack";
047      mysqlBooleanHack = Boolean.valueOf(props.get(key, "true")).booleanValue();
048    
049      key = "generate.booleanObjectForNullableBooleans";
050      booleanObjectForNullableBooleans = Boolean.valueOf(props.get(key, "true")).booleanValue();
051    
052      key = "dbspecific.postgres.getObjectToString";
053      postgresGetObjectToString = Boolean.valueOf(props.get(key, "true")).booleanValue();
054    
055      key = "generate.preparedstatement.setxxx.length_param";
056      
057      String temp = props.get(key, AVAIL);
058      if (temp == null) {
059        log.warn("Did not understand value for key:", key, " defaulting to using available()");
060        PStmt_SetXXX_Length_Param_Type = AVAIL;
061        }
062      else {
063        temp = temp.toLowerCase().intern();
064        if (temp == INTMAX)
065          PStmt_SetXXX_Length_Param_Type = INTMAX;
066        else if (temp == AVAIL)
067          PStmt_SetXXX_Length_Param_Type = AVAIL;
068        else { //defaults to avail
069          log.warn("Did not understand value for key:", key, "defaulting to using available()");
070          PStmt_SetXXX_Length_Param_Type = AVAIL;
071          }
072        }
073      }
074      
075    /**
076    Returns a suitable getResultXXX method name to retrieve
077    the data for some column. Used when generating manager code.
078    
079    @param  java_sql_type   the {@link java.sql.Type} corresponding
080                to the column for which the method will
081                be generated
082    @param  pos       A string containing the column index 
083                number (<b>starts from 1)</b>
084    */
085    public String getResultSetMethod(int java_sql_type, String pos, ColumnData cd)
086    throws SQLException
087      {
088      return createMethod("get", java_sql_type, pos, null, null, cd);
089      }
090    
091    
092    /**
093    Returns a suitable getResultXXX method name to retrieve
094    the data for some column (which will use a runtime prefix argument
095    prepended to the column name).
096    
097    @param  java_sql_type   the {@link java.sql.Type} corresponding
098                to the column for which the method will
099                be generated
100    @param  name      column name
101    */
102    public String getRuntimeResultSetMethod(int java_sql_type, String name, ColumnData cd)
103    throws SQLException
104      {
105      return createMethod("getruntime", java_sql_type, name, null, null, cd);
106      }
107    
108    /**
109    Returns a suitable setXXX method name to set the prepared statement
110    placeholder data for some column. Used when generating manager code. For
111    example, for an integer column at position 3 and a variable name "x" (which
112    would contain a integer value at runtime), the returned method will be of
113    the form: <tt>"setInt(3, x)"</tt>
114    
115    @param  java_sql_type   the {@link java.sql.Type} corresponding
116                to the column for which the method will
117                be generated
118    @param  pos       a String containing the column index number
119                (<b>starts from 1</b>) or a runtime value
120                to insert in generate code like "pos++"
121    @param  varname     the name of the variable containing the
122                value to be set at runtime.
123    */
124    public String getPreparedStmtSetMethod(
125     int java_sql_type, String psvar, String pos, String varname, ColumnData cd) throws SQLException
126      {
127      return createMethod("set", java_sql_type, pos, varname, psvar, cd);
128      }
129    
130    public String getPreparedStmtSetNullMethod(
131     int java_sql_type, String pos, String varname, ColumnData cd) throws SQLException
132      {
133      return createMethod("setNull", java_sql_type, pos, varname, null, cd);
134      }
135      
136      
137    private String createMethod(
138      String prefix, int java_sql_type, String pos, String varname, String psvar, ColumnData cd)
139    throws SQLException
140      {
141      //PreparedStatement methods for setAsciiStream etc.,
142      //needs an additional length parameter, in which 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          
205          case java.sql.Types.OTHER: 
206        if (cd.getTable().generate.isPostgres() && postgresGetObjectToString) {
207              typestr = "String("; 
208              }
209            else{
210              typestr = "Object("; 
211              }
212            break;
213                  
214          case java.sql.Types.REF: typestr = "Ref("; break;
215          case java.sql.Types.STRUCT: typestr = "Struct("; break;
216        
217        default: unsupported(java_sql_type); 
218        }
219    
220      if (prefix.equals("setNull"))
221        {
222        result = "setNull(" 
223              + pos + "," 
224              + java_sql_type + ")"
225                + "/*" + getSQLTypeName(java_sql_type) + "*/";
226        }
227        
228      else if (prefix.equals("set"))
229        {
230        result = "";
231        
232        if (needs_length && PStmt_SetXXX_Length_Param_Type.equals(AVAIL)) {
233          result += "try { "; 
234          }
235          
236        if (cd.useBooleanObject()) { 
237          //set needs "Object", type string = Boolean or boolean
238          //in JDBC, cannot set null like so: setBoolean(1, null)
239          result += psvar + "setObject(" + pos + ", " + varname;
240          }
241        /* special casing OTHER, since we have to provide Types.OTHER as a 3rd param in setObject*/
242        else if (java_sql_type == java.sql.Types.OTHER)  {
243          //we do this even when converting OTHER->String for postgres, else driver complains since 
244          //it's NOT a string in the DB.
245          result += psvar + "setObject(" + pos + ", " + varname + ", java.sql.Types.OTHER";
246          }
247        else{
248          result += psvar + "set" + typestr + pos + ", " + varname;
249          }
250      
251        if (needs_length) 
252          {
253          result += ", ";
254          if (PStmt_SetXXX_Length_Param_Type.equals(AVAIL))
255            result += varname + ".available()";
256          else if (PStmt_SetXXX_Length_Param_Type.equals(INTMAX))
257            result += "Integer.MAX_VALUE";
258          }
259        result += "); ";
260    
261        if (needs_length && PStmt_SetXXX_Length_Param_Type.equals(AVAIL)) {
262          result += "} catch (IOException e) { throw new SQLException(e.toString()); } "; 
263          }
264        }
265        
266      else if (prefix.equals("get"))
267        {
268        if (cd.useBooleanObject()) { //"(Boolean) rs." so we put "rs." here
269          result = "((Boolean) rs.getObject(" + pos + "))";
270          }
271        else{
272          result = "get" + typestr + pos + ")";   
273          }
274        }
275        
276      else if (prefix.equals("getruntime"))
277        {
278        if (cd.useBooleanObject()) {  
279          result = "((Boolean) rs.getObject(prefix" + "+\"_" + pos + "\"))";        
280          }
281        else{
282          result = "get" + typestr + "prefix" + "+\"_" + pos + "\")";
283          }
284        }
285        
286      else{
287        throw new SQLException("I dont understand how to handle: " + prefix);
288        }
289        
290        return result;
291      }
292    
293    /**
294    Converts a value corresponding to {@link java.sql.Type} to the Java
295    type used to represent that type. The {@link java.sql.Type Sql-Type} is
296    returned by the jdbc driver for some column in a table and we map it to a
297    corresponding java type that will be used to represent/work with that sql
298    type in our java programs. This mapping follows the JDBC guidelines.
299    
300    Similar JDBC API method like {@link
301    ResultSetMetaData#getColumnTypeName(column)} and {@link
302    DatabaseMetaData#getColumns()} return tpye names that can be driver/db
303    specific (and don't have to correspond to Java types anyway).
304    
305    @param  java_sql_type    the {@link java.sql.Type} to convert to a
306                 java language type
307    @param  cd         used to find out if the column is nullable
308                 (this can have the effect of using primitive or
309                 object types, in some cases, for example, Boolean
310                 vs boolean)
311    **/
312    public String getJavaTypeFromSQLType(int java_sql_type, ColumnData cd) throws SQLException
313      {
314      boolean columnIsNullable = cd.isNullable();
315      String result = null;
316      switch (java_sql_type) 
317        {
318        //integral types
319          case java.sql.Types.TINYINT:  
320            result = (mysqlBooleanHack) ? "Boolean" : "byte";  
321            break;
322        
323        case java.sql.Types.SMALLINT: result = "short"; break;
324        case java.sql.Types.INTEGER:  result = "int";   break;
325        case java.sql.Types.BIGINT:   result = "long";  break;
326                
327        //floating
328          case java.sql.Types.FLOAT: result  = "float";  break;
329        case java.sql.Types.REAL: result   = "float";  break;
330          case java.sql.Types.DOUBLE: result = "double"; break;
331        
332        //arbitrary precision
333        case java.sql.Types.DECIMAL: result = "BigDecimal"; break; 
334          case java.sql.Types.NUMERIC: result = "BigDecimal"; break;
335          
336        //char
337        case java.sql.Types.CHAR: result    = "String"; break;
338          case java.sql.Types.VARCHAR: result   = "String"; break;
339        case java.sql.Types.LONGVARCHAR: result = "InputStream"; break;
340          
341        //date-time
342        case java.sql.Types.DATE: result = "java.sql.Date"; break;
343          case java.sql.Types.TIME: result = "Time"; break;
344          case java.sql.Types.TIMESTAMP: result = "Timestamp"; break;
345          
346        //stream and binary
347        case java.sql.Types.BLOB: result = "java.sql.Blob"; break;
348          case java.sql.Types.CLOB: result = "java.sql.Clob"; break;
349          case java.sql.Types.BINARY: result = "byte[]"; break;
350          case java.sql.Types.VARBINARY: result = "byte[]"; break;
351          case java.sql.Types.LONGVARBINARY: result = "InputStream"; break;
352          
353        //misc
354        case java.sql.Types.ARRAY: result = "java.sql.Array"; break;
355    
356        //note: postgres booleans are/seen as BIT by the driver.
357        case java.sql.Types.BIT: 
358        case java.sql.Types.BOOLEAN: 
359          if (columnIsNullable && booleanObjectForNullableBooleans) {
360            result = "Boolean";
361            }
362          else{
363            result = "boolean";
364            }
365            break;
366    
367        case java.sql.Types.DATALINK: unsupported(java.sql.Types.DATALINK); break;
368          case java.sql.Types.DISTINCT: unsupported(java.sql.Types.DISTINCT); break;
369          case java.sql.Types.JAVA_OBJECT: unsupported(java.sql.Types.JAVA_OBJECT); break;
370          case java.sql.Types.NULL: unsupported(java.sql.Types.NULL); break;
371          
372        //json, jsonb, enum, etc are all returned as OTHER by pg
373        //use setObject(int, object, Types.OTHER) when saving. And set the java type to be Object as well 
374        //(doesn't have to be declared as String - even if the postgres convert to string option is true)
375        //keeping it as object gives some potential flexibility down the road for as yet unknown use cases
376          case java.sql.Types.OTHER: 
377            if (cd.getTable().generate.isPostgres() && postgresGetObjectToString) {
378              result = "String"; //however, when savings, we still have to save as setObject(..., Type.OTHER)
379                        //else the driver craps out (since NOT defined as String in DB). This
380                        //conversion is maybe not worth the code complexity
381              }
382            else{
383              result = "Object"; 
384              }
385            break;
386            
387          case java.sql.Types.REF: result = "java.sql.Ref"; break;
388          case java.sql.Types.STRUCT: result = "java.sql.Struct"; break;
389        
390        default: unsupported(java_sql_type); 
391        }
392    
393      return result;
394      }
395    
396    /**
397    Uses the same conversion criteria as the {@link getJavaTypeFromSQLType()}
398    and then returns <tt>true</tt> if the Java type used to represent the
399    specified {@link java.sql.Type} is primitive (int, boolean etc) as opposed
400    to an Object type.
401    
402    @param  java_sql_type    the {@link java.sql.Type} for the corresponding
403                 java type.
404    @param  cd         used to find out if the column is nullable
405                 (this can have the effect of using primitive or
406                 object types, in some cases, for example, Boolean
407                 vs boolean)
408    */            
409    public boolean usesPrimitiveJavaType(int java_sql_type, ColumnData cd)
410    throws SQLException
411      {
412      boolean primitive = false;
413      switch (java_sql_type) 
414        {
415        //integral types
416          case java.sql.Types.TINYINT:  /*short*/
417          case java.sql.Types.SMALLINT: /*short*/ 
418        case java.sql.Types.INTEGER:  /*int*/  
419        case java.sql.Types.BIGINT:  /*long*/
420            primitive = true;
421            break;
422            
423        //floating
424          case java.sql.Types.FLOAT: /*float*/  
425          case java.sql.Types.REAL:  /*float*/  
426          case java.sql.Types.DOUBLE: /*double*/ 
427              primitive = true;
428            break;
429    
430        //arbitrary precision
431        case java.sql.Types.DECIMAL: /*BigDecimal*/ break; 
432          case java.sql.Types.NUMERIC: /*BigDecimal*/ break; 
433       
434        //char
435        case java.sql.Types.CHAR: /*String*/ break;
436        case java.sql.Types.VARCHAR: /*String*/ break;
437        case java.sql.Types.LONGVARCHAR: /*InputStream*/ break;
438          
439        //date-time
440        case java.sql.Types.DATE: /*java.sql.Date*/ break;
441          case java.sql.Types.TIME: /*Time*/ break;
442          case java.sql.Types.TIMESTAMP:/*Timestamp*/ break;
443          
444        //stream and binary
445        case java.sql.Types.BLOB:/*java.sql.Blob*/ break;
446          case java.sql.Types.CLOB:/*java.sql.Clob*/ break;
447          case java.sql.Types.BINARY: /*byte[]*/ break;
448          case java.sql.Types.VARBINARY:/*byte[]*/ break;
449          case java.sql.Types.LONGVARBINARY:/*InputStream*/ break;
450          
451        //misc
452        case java.sql.Types.ARRAY: /*java.sql.Array*/ break;
453        
454        case java.sql.Types.BIT:     /*boolean or Boolean*/ 
455        case java.sql.Types.BOOLEAN: /*boolean or Boolean*/ 
456          if (cd.useBooleanObject()) {
457            //primitive is already false
458            }
459          else{
460            primitive = true;
461            }
462          break;
463      
464        case java.sql.Types.DATALINK: unsupported(java.sql.Types.DATALINK); break;
465          case java.sql.Types.DISTINCT: unsupported(java.sql.Types.DISTINCT); break;
466          case java.sql.Types.JAVA_OBJECT: unsupported(java.sql.Types.JAVA_OBJECT); break;
467          case java.sql.Types.NULL: unsupported(java.sql.Types.NULL); break;
468          
469          case java.sql.Types.OTHER: 
470              primitive = false; 
471              break;
472          
473          case java.sql.Types.REF:  /*java.sql.Ref*/ break;
474          case java.sql.Types.STRUCT: /*java.sql.Struct*/ break;
475        
476        default: unsupported(java_sql_type); 
477        }
478    
479      return primitive;
480      }
481    
482    
483    /**
484    Uses the same conversion criteria as the {@link getJavaTypeFromSQLType()}
485    and then returns <tt>true</tt> if the Java type used to represent the
486    specified {@link java.sql.Type} is integral. This is used for creating
487    the inc/dec methods (only for short, int and long)
488    
489    @param  java_sql_type    the {@link java.sql.Type} for the corresponding
490                 java type
491    */            
492    public boolean usesSimpleIntegralJavaType(int java_sql_type)
493    throws SQLException
494      {
495      boolean simple = false;
496      
497        switch(java_sql_type)
498          {
499        case java.sql.Types.INTEGER:  /*int*/  
500        case java.sql.Types.BIGINT:  /*long*/
501            simple = true;
502            break;
503          }  
504      
505      return simple;
506      }
507    
508    
509    /**
510    Converts the {@link java.sql.Type} for some column (returned by the
511    driver) to a readable value. More convenient than using the "constant
512    field values" section of the not neatly arranged javadocs for {@link
513    java.sql.Type}.
514    <p>
515    Note, this method is different from {@link } because unlike {@link },
516    this simply returns the variable name corresponding to the parameter
517    value (for example, <tt>java.sql.Type.INTEGER == 4</tt> and passing
518    <tt>4</tt> to this method will return "<tt>INTEGER</tt>").
519    
520    @param java_sql_type  a type from {@link java.sql.Type}
521    */
522    public String getSQLTypeName(int java_sql_type)
523      {
524      switch (java_sql_type)
525        {
526        case 2003:  return "ARRAY"; 
527        case -5:  return "BIGINT"; 
528        case -2:  return "BINARY"; 
529        case -7:  return "BIT"; 
530        case 2004:  return "BLOB"; 
531        case 16:  return "BOOLEAN"; 
532        case 1:   return "CHAR"; 
533        case 2005:  return "CLOB"; 
534        case 70:  return "DATALINK"; 
535        case 91:  return "DATE"; 
536        case 3:   return "DECIMAL"; 
537        case 2001:  return "DISTINCT"; 
538        case 8:   return "DOUBLE"; 
539        case 6:   return "FLOAT"; 
540        case 4:   return "INTEGER"; 
541        case 2000:  return "JAVA_OBJECT"; 
542        case -4:  return "LONGVARBINARY"; 
543        case -1:  return "LONGVARCHAR"; 
544        case 0:   return "NULL"; 
545        case 2:   return "NUMERIC"; 
546        case 1111:  return "OTHER"; 
547        case 7:   return "REAL"; 
548        case 2006:  return "REF"; 
549        case 5:   return "SMALLINT"; 
550        case 2002:  return "STRUCT"; 
551        case 92:  return "TIME"; 
552        case 93:  return "TIMESTAMP"; 
553        case -6:  return "TINYINT"; 
554        case -3:  return "VARBINARY"; 
555        case 12:  return "VARCHAR"; 
556        default:  return "NOT KNOWN/ERROR";
557        }
558      }
559    
560    void unsupported(int type) throws SQLException {
561      throw new SQLException("This framework does not understand/support columns of this type. [java.sql.Type: " + type + "]"); 
562      }
563    
564    } //~Types
565