Date support - null issue

[Carl J. Mosca]

In looking at Column.java I am wondering why the getResultSetMethodObject() method does not return a getter from the CodeWriter.MGR_CLASS

We are using sql2java with web services which do not handle null dates very well.  We would like to have custom, template-based getters for the date/time classes.

Is there a particular reason why the getters and setters for date/time types are not supported in the templates?

I am wondering what issues I might run into.

TIA,
Carl

[Alain Fagot Béarez]

I suppose there is no issue in putting a new group of getters/setter for the Date type in the Manager.java template.  It may be that nobody ever needed the custom getters for that datatype.  Once you're ready with your modifications, don't forget to post a patch for us to integrate it in the next release.

http://sourceforge.net/tracker/?func=add&group_id=54687&atid=474469

Saludos,
Alain.

[Carl J. Mosca]

OK so dates were causing us different types of problems as we passed our beans over the wire.  Even if we were not doing so, null dates in a database can certainly cause issues.

We needed a way to add custom getters/setters for the dates (we used Calendar) like some of the other types have. 

We also wanted support for a perschema class which can reference all tables.

Here are the changes (all have "moscac" on or before and after the changes and were diff'ed just now 8/24/07):

CodeWriter.java

process()

        // Generate core and manager classes for all tables
        Table tables[] = db.getTables();
        // moscac - added 4/9/07
        current_vc = new VelocityContext(vc);
        current_vc.put("tables", tables);
        String[] service_templates = getSchemaTemplates("velocity.templates");
        for (int i = 0; i < service_templates.length; i++) {
            writeComponent(service_templates[i]);
        }
        // moscac - end of added code   
        for(int i = 0; i < tables.length; i++) {
            if (authorizeProcess(tables[i].getName(), "tables.include", "tables.exclude"))
                writeTable(tables[i]);
        }

Column.java

    public static final int M_CALENDAR = 18; // moscac 2007-08-09

    /**
     * return internal type for the current column
     */
    public int getMappedType() {
        switch(getType()) {
        case Types.ARRAY: return M_ARRAY;
        case Types.BIGINT : return M_LONG;
        case Types.BINARY : return M_BYTES;
        case Types.BIT : return M_BOOLEAN;
        case Types.BLOB : return M_BLOB;
        case Types.BOOLEAN : return M_BOOLEAN;
        case Types.CHAR : return M_STRING;
        case Types.CLOB : return M_CLOB;
        case Types.DATALINK : return M_URL;
        case Types.DATE :
            if (CodeWriter.dateClassName.equals("java.util.Date")) return M_UTILDATE;
            if (CodeWriter.dateClassName.equals("java.sql.Date")) return M_SQLDATE;
            if (CodeWriter.dateClassName.equals("java.util.Calendar")) return M_CALENDAR; // moscac 2007-08-09       
            tuoe();
        case Types.DECIMAL : return getDecimalDigits() > 0 ? M_BIGDECIMAL : M_LONG;
        case Types.DISTINCT : return M_OBJECT;
        case Types.DOUBLE : return M_DOUBLE;
        case Types.FLOAT : return M_DOUBLE;
        case Types.INTEGER :return M_INTEGER;
        case Types.JAVA_OBJECT : return M_OBJECT;
        case Types.LONGVARBINARY :return M_BYTES;
        case Types.LONGVARCHAR : return M_STRING;

//        case Types.NULL : return M_NULL;

        case Types.NUMERIC : return getDecimalDigits() > 0 ? M_BIGDECIMAL : M_LONG;
        case Types.OTHER : return M_OBJECT;
        case Types.REAL : return M_FLOAT;
        case Types.REF : return M_REF;
        case Types.SMALLINT :return M_INTEGER;
        case Types.STRUCT : return M_OBJECT;
        case Types.TIME :
            if (CodeWriter.timeClassName.equals("java.util.Date")) return M_UTILDATE;
            if (CodeWriter.timeClassName.equals("java.sql.Time")) return M_TIME;
            if (CodeWriter.timeClassName.equals("java.util.Calendar")) return M_CALENDAR; // moscac 2007-08-09
            tuoe();
        case Types.TIMESTAMP :
            if (CodeWriter.timestampClassName.equals("java.util.Date")) return M_UTILDATE;
            if (CodeWriter.timestampClassName.equals("java.sql.Timestamp")) return M_TIMESTAMP;
            if (CodeWriter.timestampClassName.equals("java.util.Calendar")) return M_CALENDAR; // moscac 2007-08-09
            tuoe();
        case Types.TINYINT :return M_INTEGER;
        case Types.VARBINARY :return M_BYTES;
        case Types.VARCHAR :return M_STRING;
        default:tuoe();
        }
        return -1;

    public String getResultSetMethodObject(String resultSet, String pos)
    {
        switch (getMappedType())
        {
// lines ommitted
case M_CALENDAR : return CodeWriter.MGR_CLASS + ".getCalendar(" + resultSet + ", " + pos + ")"; // moscac 2007-08-09   

        }

These changes are using in conjunction with a template which

I also noticed (but have not investigated further) what for us was a "showstopper" bug in Table.java  This is probably not ideal, but the generation continues and the
tables which do not have a single primary key are not problematic with the template we are using.  I will look at this more.

    public Column getPrimaryKey() throws RuntimeException
    {
// moscac modified 2007-08-12
    if(priKey.size() != 1)
        {
            System.out.println((new StringBuilder()).append("Table ").append(getName()).append(" has a composite key, not a unique primary key").toString());
        }  

        return (Column)priKey.get(0);
// moscac end of mod
    }

[Alain Fagot Béarez]

Some notes about your contributed code.  First of all, thanks for contributing it!

As for the modification of the CodeWriter.java, I am afraid this would lead to double generation of all the perschema templates.  If you need a reference to all tables in your perschema template, just include the schema.include.vm and you got it through ${tables}.  No need for source code modification.

## -- example
#parse( "schema.include.vm" )
#foreach ( $table in $tables )
$table.getName() has $table.countColumns() columns.
#end
## -- end example

As for the modification of the Table.java, I think the error was in the included table.include.vm template which had a permanent reference to the primary key.  As of version 2.6.4, this template as been adapted.  You should use ${table.getPrimaryKey()} only when there is only one column in the key (${table.countPrimaryKeys()} == 1).  Otherwise, I would recommend to iterate over the keys collection given by ${primaryKeys}.

As for the manager template, your sentence has been cut off.  I tried to get a guess at what you would like it to be.  Don't hesitate to post again in this thread if my committed code is far away from what you expected.

Thanks for helping us maintaining sql2java useful for its users!

Alain.

[Carl J. Mosca]

Alain,

I have not tried to do such an include since switching to the latest version; I did not see a way to do that in the previous release but the structure makes sense now and I'll give it a try.  Just to confirm, the table.include.vm can also be parsed in your example.  Is this correct?

On the modificaton to Table.java and template error, I am wondering if I have the latest version of the template files (I am using CVS) because I have noticed some issues with the after insert code in the pertable manager.java.vm.  It seems the test in the writePostInsert section to see if the primary key has been modified will always return false and the value is not retrieved/populated. I had to remove the "!" to get it to work.

            if (!bean.$pKey.getModifiedMethod()())

I will revisit the primary key logic.

As for the manager template, I think I was about to include an example from the Manager template with and for the get/set Calendar.  We simply needed a way to replace nulls with a value when we send them over the wire and decided to add Calendar.

    public static Calendar getCalendar(ResultSet rs, int pos) {
        Calendar calendar = Calendar.getInstance();
        try {
            calendar.setTime(rs.getDate(pos));
            if (rs.wasNull()) {
                setValueRepresentingNull(calendar);
            }
        } catch (SQLException se) {
            setValueRepresentingNull(calendar);
        }
        return calendar;
    }

    public static void setCalendar(PreparedStatement ps, int pos, Calendar calendar) throws SQLException {
        if ((calendar == null) || (isValueRepresentingNull(calendar)))
          ps.setNull(pos, Types.TIMESTAMP);
        else
          ps.setDate(pos, new java.sql.Date(calendar.getTimeInMillis()));

    }

    private static void setValueRepresentingNull(Calendar calendar) {
        calendar.set(Calendar.YEAR, DATE_REPRESENTING_NULL_YEAR);
        calendar.set(Calendar.MONTH, DATE_REPRESENTING_NULL_MONTH);
        calendar.set(Calendar.DATE, DATE_REPRESENTING_NULL_DATE);
        calendar.set(Calendar.HOUR, 0);
        calendar.set(Calendar.MINUTE, 0);
        calendar.set(Calendar.SECOND, 0);
        calendar.set(Calendar.MILLISECOND, 0);
        calendar.set(Calendar.ZONE_OFFSET,
                (calendar.getTimeZone().getRawOffset() + calendar.getTimeZone().getDSTSavings()) / 60000);
    }
   
    public static boolean isValueRepresentingNull(Calendar calendar) {
        return (calendar.get(Calendar.YEAR) == DATE_REPRESENTING_NULL_YEAR) &&
            (calendar.get(Calendar.MONTH) == DATE_REPRESENTING_NULL_MONTH) &&
            (calendar.get(Calendar.DATE) == DATE_REPRESENTING_NULL_DATE);
    }
   

    private static int DATE_REPRESENTING_NULL_YEAR = 1899;
    private static int DATE_REPRESENTING_NULL_MONTH = Calendar.JANUARY;
    private static int DATE_REPRESENTING_NULL_DATE = 1;

Thank you for a very nice piece of software!!  I like the templates which genarate the html docs and diagrams.

[Carl J. Mosca]

The schema.include.vm suggestion worked just fine for me, thank you.

The M_CALENDAR support in Column.java diff follows:

Index: Column.java

RCS file: /cvsroot/sql2java/sql2java/src/java/net/sourceforge/sql2java/Column.java,v
retrieving revision 1.15
diff -u -r1.15 Column.java
--- Column.java    8 Aug 2007 09:48:07 -0000    1.15
+++ Column.java    27 Aug 2007 12:33:10 -0000
@@ -29,6 +29,7 @@
     public static final int M_TIMESTAMP = 15;
     public static final int M_URL = 16;
     public static final int M_OBJECT = 17;
+    public static final int M_CALENDAR = 18; // moscac 2007-08-09

     private String catalog, schema, tableName, name, remarks, defaultValue;
     private int size, decDigits, radix, nullable, ordinal;
@@ -123,6 +124,7 @@
         case Types.DATE :
             if (CodeWriter.dateClassName.equals("java.util.Date")) return M_UTILDATE;
             if (CodeWriter.dateClassName.equals("java.sql.Date")) return M_SQLDATE;
+            if (CodeWriter.dateClassName.equals("java.util.Calendar")) return M_CALENDAR; // moscac 2007-08-09       
             tuoe();
         case Types.DECIMAL : return getDecimalDigits() > 0 ? M_BIGDECIMAL : M_LONG;
         case Types.DISTINCT : return M_OBJECT;
@@ -144,10 +146,12 @@
         case Types.TIME :
             if (CodeWriter.timeClassName.equals("java.util.Date")) return M_UTILDATE;
             if (CodeWriter.timeClassName.equals("java.sql.Time")) return M_TIME;
+            if (CodeWriter.timeClassName.equals("java.util.Calendar")) return M_CALENDAR; // moscac 2007-08-09
             tuoe();
         case Types.TIMESTAMP :
             if (CodeWriter.timestampClassName.equals("java.util.Date")) return M_UTILDATE;
             if (CodeWriter.timestampClassName.equals("java.sql.Timestamp")) return M_TIMESTAMP;
+            if (CodeWriter.timestampClassName.equals("java.util.Calendar")) return M_CALENDAR; // moscac 2007-08-09
             tuoe();
         case Types.TINYINT :return M_INTEGER;
         case Types.VARBINARY :return M_BYTES;
@@ -227,6 +231,7 @@
         case M_TIMESTAMP: return "java.sql.Timestamp";
         case M_URL: return "java.net.URL";
         case M_OBJECT: return "Object";
+        case M_CALENDAR: return "java.util.Calendar"; // moscac 2007-08-09
         }
         return null;
     }
@@ -361,6 +366,11 @@
             return false;
         }
     }
+   
+    // moscac added isCalendar()
+    public boolean isCalendar() {
+        return (getMappedType() == M_CALENDAR);
+    }

     /**
      * does this type has a compareTo method ?
@@ -439,6 +449,7 @@
             case Types.TIMESTAMP: return resultSet + ".getTimestamp(" + pos + ")";
             case Types.DATE: return resultSet + ".getDate(" + pos + ")";
             }
+    case M_CALENDAR : return CodeWriter.MGR_CLASS + ".getCalendar(" + resultSet + ", " + pos + ")"; // moscac 2007-08-09   
         default:tuoe();
         }
         return null;
@@ -503,6 +514,7 @@
             case Types.TIME: return sb.append("ps.setTime(").append(pos).append(", new java.sql.Time(").append(var).append(".getTime()));").toString();
             default: return null;
             }
+        case M_CALENDAR: return sb.append("Manager.setCalendar(ps, ").append(end).toString();           
         case M_REF: return "ps.setRef(" + end;
         default: return "ps.setObject(" + end;
         }