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
006package fc.jdbc.dbo;
007
008import java.sql.*;
009import java.util.*;
010
011import fc.jdbc.*;
012import fc.io.*;
013import fc.util.*;
014
015/**
016SQL and java type related functions 
017*/
018public class Types
019{
020static final String AVAIL = "available";
021static final String INTMAX = "intmax";
022
023String    PStmt_SetXXX_Length_Param_Type;
024boolean   mysqlBooleanHack;
025boolean   booleanObjectForNullableBooleans;
026boolean   postgresGetObjectToString;
027SystemLog log;
028
029/**
030Constructs 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*/
043public 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/**
076Returns a suitable getResultXXX method name to retrieve
077the 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*/
085public String getResultSetMethod(int java_sql_type, String pos, ColumnData cd)
086throws SQLException
087  {
088  return createMethod("get", java_sql_type, pos, null, null, cd);
089  }
090
091
092/**
093Returns a suitable getResultXXX method name to retrieve
094the data for some column (which will use a runtime prefix argument
095prepended 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*/
102public String getRuntimeResultSetMethod(int java_sql_type, String name, ColumnData cd)
103throws SQLException
104  {
105  return createMethod("getruntime", java_sql_type, name, null, null, cd);
106  }
107
108/**
109Returns a suitable setXXX method name to set the prepared statement
110placeholder data for some column. Used when generating manager code. For
111example, for an integer column at position 3 and a variable name "x" (which
112would contain a integer value at runtime), the returned method will be of
113the 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*/
124public 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
130public 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  
137private String createMethod(
138  String prefix, int java_sql_type, String pos, String varname, String psvar, ColumnData cd)
139throws 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/**
294Converts a value corresponding to {@link java.sql.Type} to the Java
295type used to represent that type. The {@link java.sql.Type Sql-Type} is
296returned by the jdbc driver for some column in a table and we map it to a
297corresponding java type that will be used to represent/work with that sql
298type in our java programs. This mapping follows the JDBC guidelines.
299
300Similar JDBC API method like {@link
301ResultSetMetaData#getColumnTypeName(column)} and {@link
302DatabaseMetaData#getColumns()} return tpye names that can be driver/db
303specific (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**/
312public 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/**
397Uses the same conversion criteria as the {@link getJavaTypeFromSQLType()}
398and then returns <tt>true</tt> if the Java type used to represent the
399specified {@link java.sql.Type} is primitive (int, boolean etc) as opposed
400to 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*/            
409public boolean usesPrimitiveJavaType(int java_sql_type, ColumnData cd)
410throws 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/**
484Uses the same conversion criteria as the {@link getJavaTypeFromSQLType()}
485and then returns <tt>true</tt> if the Java type used to represent the
486specified {@link java.sql.Type} is integral. This is used for creating
487the 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*/            
492public boolean usesSimpleIntegralJavaType(int java_sql_type)
493throws 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/**
510Converts the {@link java.sql.Type} for some column (returned by the
511driver) to a readable value. More convenient than using the "constant
512field values" section of the not neatly arranged javadocs for {@link
513java.sql.Type}.
514<p>
515Note, this method is different from {@link } because unlike {@link },
516this simply returns the variable name corresponding to the parameter
517value (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*/
522public 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
560void 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