You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ofbiz.apache.org by ad...@apache.org on 2010/07/02 08:28:14 UTC

svn commit: r959868 - /ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java

Author: adrianc
Date: Fri Jul  2 06:28:14 2010
New Revision: 959868

URL: http://svn.apache.org/viewvc?rev=959868&view=rev
Log:
Small enhancement to JdbcValueHandler: add support for sub-second Timestamp precision on databases that don't support it.

If a user chooses to do so, they can assign the date-time field type to a CHAR(30) SQL type. This will enable sub-second Timestamp precision on databases that don't support it.

The Timestamp will be stored as a string in JDBC Timestamp format (yyyy-mm-dd hh:mm:ss.fffffffff). Date/time comparisons can still be performed in SQL statements, but any date/time functions performed on the field will fail. Users could create UDFs to mimic those functions.

In addition, the CHAR(30) field will consume more storage space than a native Timestamp field (usually 8 bytes).

It will be up to the user to decide if they want to use this feature. If used in a pure OFBiz environment, everything should work as expected.

Modified:
    ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java

Modified: ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java
URL: http://svn.apache.org/viewvc/ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java?rev=959868&r1=959867&r2=959868&view=diff
==============================================================================
--- ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java (original)
+++ ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java Fri Jul  2 06:28:14 2010
@@ -29,6 +29,7 @@ import java.sql.Blob;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
+import java.sql.Timestamp;
 import java.sql.Types;
 import java.util.Map;
 
@@ -698,6 +699,11 @@ public abstract class JdbcValueHandler {
 
     /**
      * A <code>java.sql.Timestamp</code> JDBC value handler.
+     * <p>This <code>JdbcValueHandler</code> accommodates databases that
+     * don't support sub-second precision. If the date-time field type
+     * is a <code>CHAR(30)</code> SQL type, <code>java.sql.Timestamp</code>s
+     * will be stored as JDBC escape strings
+     * (<code>yyyy-mm-dd hh:mm:ss.fffffffff</code>).</p> 
      */
     protected static class TimestampJdbcValueHandler extends JdbcValueHandler {
         protected TimestampJdbcValueHandler(int jdbcType) {
@@ -713,7 +719,22 @@ public abstract class JdbcValueHandler {
         }
         @Override
         protected JdbcValueHandler newInstance(int sqlType) {
-            return new TimestampJdbcValueHandler(sqlType);
+            if (sqlType == Types.CHAR) {
+                return new TimestampJdbcValueHandler(sqlType) {
+                    protected void castAndSetValue(PreparedStatement ps, int parameterIndex, Object obj) throws SQLException {
+                        ps.setString(parameterIndex, ((java.sql.Timestamp) obj).toString());
+                    }
+                    public Object getValue(ResultSet rs, int columnIndex) throws SQLException {
+                        String str = rs.getString(columnIndex);
+                        if (str == null) {
+                            return null;
+                        }
+                        return Timestamp.valueOf(str);
+                    }
+                };
+            } else {
+                return new TimestampJdbcValueHandler(sqlType);
+            }
         }
     }
 }



Re: svn commit: r959868 - /ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java

Posted by Adrian Crum <ad...@yahoo.com>.
Okay, we can rejoice. I just checked the java.sql.Timestamp source code and the stored strings are referenced to UTC.

-Adrian

--- On Thu, 7/1/10, Adrian Crum <ad...@yahoo.com> wrote:

> From: Adrian Crum <ad...@yahoo.com>
> Subject: Re: svn commit: r959868 - /ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java
> To: dev@ofbiz.apache.org, "Jacques Le Roux" <ja...@les7arts.com>
> Date: Thursday, July 1, 2010, 11:47 PM
> Let's not rejoice just yet. There
> might be time zone issues. I'm thinking we might need to
> reference UTC in the Timestamp/String conversions.
> 
> -Adrian
> 
> --- On Thu, 7/1/10, Jacques Le Roux <ja...@les7arts.com>
> wrote:
> 
> > From: Jacques Le Roux <ja...@les7arts.com>
> > Subject: Re: svn commit: r959868 -
> /ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java
> > To: dev@ofbiz.apache.org
> > Date: Thursday, July 1, 2010, 11:41 PM
> > Nice!
> > 
> > Jacques
> > 
> > From: <ad...@apache.org>
> > > Author: adrianc
> > > Date: Fri Jul  2 06:28:14 2010
> > > New Revision: 959868
> > > 
> > > URL: http://svn.apache.org/viewvc?rev=959868&view=rev
> > > Log:
> > > Small enhancement to JdbcValueHandler: add
> support for
> > sub-second Timestamp precision on databases that
> don't
> > support it.
> > > 
> > > If a user chooses to do so, they can assign the
> > date-time field type to a CHAR(30) SQL type. This
> will
> > enable sub-second Timestamp precision on databases
> that
> > don't support it.
> > > 
> > > The Timestamp will be stored as a string in JDBC
> > Timestamp format (yyyy-mm-dd hh:mm:ss.fffffffff).
> Date/time
> > comparisons can still be performed in SQL statements,
> but
> > any date/time functions performed on the field will
> fail.
> > Users could create UDFs to mimic those functions.
> > > 
> > > In addition, the CHAR(30) field will consume
> more
> > storage space than a native Timestamp field (usually
> 8
> > bytes).
> > > 
> > > It will be up to the user to decide if they want
> to
> > use this feature. If used in a pure OFBiz
> environment,
> > everything should work as expected.
> > > 
> > > Modified:
> > >   
> >
> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java
> > > 
> > > Modified:
> >
> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java
> > > URL: http://svn.apache.org/viewvc/ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java?rev=959868&r1=959867&r2=959868&view=diff
> > >
> >
> ==============================================================================
> > > ---
> >
> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java
> > (original)
> > > +++
> >
> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java
> > Fri Jul  2 06:28:14 2010
> > > @@ -29,6 +29,7 @@ import java.sql.Blob;
> > > import java.sql.PreparedStatement;
> > > import java.sql.ResultSet;
> > > import java.sql.SQLException;
> > > +import java.sql.Timestamp;
> > > import java.sql.Types;
> > > import java.util.Map;
> > > 
> > > @@ -698,6 +699,11 @@ public abstract class
> > JdbcValueHandler {
> > > 
> > >     /**
> > >      * A
> > <code>java.sql.Timestamp</code> JDBC
> value
> > handler.
> > > +     * <p>This
> > <code>JdbcValueHandler</code>
> accommodates
> > databases that
> > > +     * don't support sub-second
> > precision. If the date-time field type
> > > +     * is a
> > <code>CHAR(30)</code> SQL type,
> > <code>java.sql.Timestamp</code>s
> > > +     * will be stored as JDBC
> > escape strings
> > > +     * (<code>yyyy-mm-dd
> > hh:mm:ss.fffffffff</code>).</p>
> > >      */
> > >     protected static class
> > TimestampJdbcValueHandler extends JdbcValueHandler {
> > >         protected
> > TimestampJdbcValueHandler(int jdbcType) {
> > > @@ -713,7 +719,22 @@ public abstract class
> > JdbcValueHandler {
> > >         }
> > >         @Override
> > >         protected
> > JdbcValueHandler newInstance(int sqlType) {
> > > -            return new
> > TimestampJdbcValueHandler(sqlType);
> > > +            if (sqlType
> > == Types.CHAR) {
> > > +             
> >   return new TimestampJdbcValueHandler(sqlType) {
> > > +             
> >       protected void
> > castAndSetValue(PreparedStatement ps, int
> parameterIndex,
> > Object obj) throws SQLException {
> > > +             
> >          
> > ps.setString(parameterIndex, ((java.sql.Timestamp)
> > obj).toString());
> > > +             
> >       }
> > > +             
> >       public Object getValue(ResultSet rs,
> > int columnIndex) throws SQLException {
> > > +             
> >           String str =
> > rs.getString(columnIndex);
> > > +             
> >           if (str == null) {
> > > +             
> >               return
> > null;
> > > +             
> >           }
> > > +             
> >           return
> > Timestamp.valueOf(str);
> > > +             
> >       }
> > > +             
> >   };
> > > +            } else {
> > > +             
> >   return new TimestampJdbcValueHandler(sqlType);
> > > +            }
> > >         }
> > >     }
> > > }
> > > 
> > > 
> > 
> > 
> > 
> 
> 
>   
> 


      

Re: svn commit: r959868 - /ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java

Posted by Adrian Crum <ad...@yahoo.com>.
Let's not rejoice just yet. There might be time zone issues. I'm thinking we might need to reference UTC in the Timestamp/String conversions.

-Adrian

--- On Thu, 7/1/10, Jacques Le Roux <ja...@les7arts.com> wrote:

> From: Jacques Le Roux <ja...@les7arts.com>
> Subject: Re: svn commit: r959868 - /ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java
> To: dev@ofbiz.apache.org
> Date: Thursday, July 1, 2010, 11:41 PM
> Nice!
> 
> Jacques
> 
> From: <ad...@apache.org>
> > Author: adrianc
> > Date: Fri Jul  2 06:28:14 2010
> > New Revision: 959868
> > 
> > URL: http://svn.apache.org/viewvc?rev=959868&view=rev
> > Log:
> > Small enhancement to JdbcValueHandler: add support for
> sub-second Timestamp precision on databases that don't
> support it.
> > 
> > If a user chooses to do so, they can assign the
> date-time field type to a CHAR(30) SQL type. This will
> enable sub-second Timestamp precision on databases that
> don't support it.
> > 
> > The Timestamp will be stored as a string in JDBC
> Timestamp format (yyyy-mm-dd hh:mm:ss.fffffffff). Date/time
> comparisons can still be performed in SQL statements, but
> any date/time functions performed on the field will fail.
> Users could create UDFs to mimic those functions.
> > 
> > In addition, the CHAR(30) field will consume more
> storage space than a native Timestamp field (usually 8
> bytes).
> > 
> > It will be up to the user to decide if they want to
> use this feature. If used in a pure OFBiz environment,
> everything should work as expected.
> > 
> > Modified:
> >   
> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java
> > 
> > Modified:
> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java
> > URL: http://svn.apache.org/viewvc/ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java?rev=959868&r1=959867&r2=959868&view=diff
> >
> ==============================================================================
> > ---
> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java
> (original)
> > +++
> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java
> Fri Jul  2 06:28:14 2010
> > @@ -29,6 +29,7 @@ import java.sql.Blob;
> > import java.sql.PreparedStatement;
> > import java.sql.ResultSet;
> > import java.sql.SQLException;
> > +import java.sql.Timestamp;
> > import java.sql.Types;
> > import java.util.Map;
> > 
> > @@ -698,6 +699,11 @@ public abstract class
> JdbcValueHandler {
> > 
> >     /**
> >      * A
> <code>java.sql.Timestamp</code> JDBC value
> handler.
> > +     * <p>This
> <code>JdbcValueHandler</code> accommodates
> databases that
> > +     * don't support sub-second
> precision. If the date-time field type
> > +     * is a
> <code>CHAR(30)</code> SQL type,
> <code>java.sql.Timestamp</code>s
> > +     * will be stored as JDBC
> escape strings
> > +     * (<code>yyyy-mm-dd
> hh:mm:ss.fffffffff</code>).</p>
> >      */
> >     protected static class
> TimestampJdbcValueHandler extends JdbcValueHandler {
> >         protected
> TimestampJdbcValueHandler(int jdbcType) {
> > @@ -713,7 +719,22 @@ public abstract class
> JdbcValueHandler {
> >         }
> >         @Override
> >         protected
> JdbcValueHandler newInstance(int sqlType) {
> > -            return new
> TimestampJdbcValueHandler(sqlType);
> > +            if (sqlType
> == Types.CHAR) {
> > +             
>   return new TimestampJdbcValueHandler(sqlType) {
> > +             
>       protected void
> castAndSetValue(PreparedStatement ps, int parameterIndex,
> Object obj) throws SQLException {
> > +             
>          
> ps.setString(parameterIndex, ((java.sql.Timestamp)
> obj).toString());
> > +             
>       }
> > +             
>       public Object getValue(ResultSet rs,
> int columnIndex) throws SQLException {
> > +             
>           String str =
> rs.getString(columnIndex);
> > +             
>           if (str == null) {
> > +             
>               return
> null;
> > +             
>           }
> > +             
>           return
> Timestamp.valueOf(str);
> > +             
>       }
> > +             
>   };
> > +            } else {
> > +             
>   return new TimestampJdbcValueHandler(sqlType);
> > +            }
> >         }
> >     }
> > }
> > 
> > 
> 
> 
> 


      

Re: svn commit: r959868 - /ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java

Posted by Jacques Le Roux <ja...@les7arts.com>.
Nice!

Jacques

From: <ad...@apache.org>
> Author: adrianc
> Date: Fri Jul  2 06:28:14 2010
> New Revision: 959868
>
> URL: http://svn.apache.org/viewvc?rev=959868&view=rev
> Log:
> Small enhancement to JdbcValueHandler: add support for sub-second Timestamp precision on databases that don't support it.
>
> If a user chooses to do so, they can assign the date-time field type to a CHAR(30) SQL type. This will enable sub-second Timestamp 
> precision on databases that don't support it.
>
> The Timestamp will be stored as a string in JDBC Timestamp format (yyyy-mm-dd hh:mm:ss.fffffffff). Date/time comparisons can still 
> be performed in SQL statements, but any date/time functions performed on the field will fail. Users could create UDFs to mimic 
> those functions.
>
> In addition, the CHAR(30) field will consume more storage space than a native Timestamp field (usually 8 bytes).
>
> It will be up to the user to decide if they want to use this feature. If used in a pure OFBiz environment, everything should work 
> as expected.
>
> Modified:
>    ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java
>
> Modified: ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java
> URL: 
> http://svn.apache.org/viewvc/ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java?rev=959868&r1=959867&r2=959868&view=diff
> ==============================================================================
> --- ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java (original)
> +++ ofbiz/trunk/framework/entity/src/org/ofbiz/entity/jdbc/JdbcValueHandler.java Fri Jul  2 06:28:14 2010
> @@ -29,6 +29,7 @@ import java.sql.Blob;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> +import java.sql.Timestamp;
> import java.sql.Types;
> import java.util.Map;
>
> @@ -698,6 +699,11 @@ public abstract class JdbcValueHandler {
>
>     /**
>      * A <code>java.sql.Timestamp</code> JDBC value handler.
> +     * <p>This <code>JdbcValueHandler</code> accommodates databases that
> +     * don't support sub-second precision. If the date-time field type
> +     * is a <code>CHAR(30)</code> SQL type, <code>java.sql.Timestamp</code>s
> +     * will be stored as JDBC escape strings
> +     * (<code>yyyy-mm-dd hh:mm:ss.fffffffff</code>).</p>
>      */
>     protected static class TimestampJdbcValueHandler extends JdbcValueHandler {
>         protected TimestampJdbcValueHandler(int jdbcType) {
> @@ -713,7 +719,22 @@ public abstract class JdbcValueHandler {
>         }
>         @Override
>         protected JdbcValueHandler newInstance(int sqlType) {
> -            return new TimestampJdbcValueHandler(sqlType);
> +            if (sqlType == Types.CHAR) {
> +                return new TimestampJdbcValueHandler(sqlType) {
> +                    protected void castAndSetValue(PreparedStatement ps, int parameterIndex, Object obj) throws SQLException {
> +                        ps.setString(parameterIndex, ((java.sql.Timestamp) obj).toString());
> +                    }
> +                    public Object getValue(ResultSet rs, int columnIndex) throws SQLException {
> +                        String str = rs.getString(columnIndex);
> +                        if (str == null) {
> +                            return null;
> +                        }
> +                        return Timestamp.valueOf(str);
> +                    }
> +                };
> +            } else {
> +                return new TimestampJdbcValueHandler(sqlType);
> +            }
>         }
>     }
> }
>
>