Pulpet,
I am pasting in the body of a Jato Tip which deals with this subject. If it is not clear, follow up with further questions.
Tip #: 0004
Tip Name: Dynamic Modification of Insert/Update SQL Values
Type: SQL Workaround
JATO Version: 1.2.1
Credits: Todd Fast, Craig Conover
Keywords: modifying sql, insert, update, value modify, sql
construction, timestamp, date, time,
Task/Problem/Objective
-
Relational databases can vary greatly in implementation of date/time
field value formats and other complex data types. This makes it
difficult to constuct SQL INSERT and UPDATE statements that will work
properly from one vendor's RDBMS to another's.
Solution Description
--
JATO 1.2.1 has a new method that can be overridden to give you the
flexibility to modify the format of the values just before the value
is added to the SQL statement (INSERT or UPDATE) during SQL statement
construction.
JATO's default behavior is to add the model field's value exactly as
it is represented as a string version of the Java data type. The
timestamp database data type (and other date/time types) are of the
most inconsistently implemented types of them all by the various
RDBMS vendors.
For example, Hypersonic SQL (and many others) expect a timestamp of
the format:
YYYY-MM-DD HH:MM:SS.sss (ex: 2001-01-17 17:09:42.931) placed in
quotes like this:
'2001-01-17 17:09:42.931'
PointBase, on the hand, expects the same quoted value but prefixed
with the keyword TIMESTAMP. So the value in the SQL should appear as
follows:
TIMESTAMP '2001-01-17 17:09:42.931'
Quoting values is easy performed by overriding the shouldQuoteType()
method of the QueryModelBase class (see tip #0003), however,
modifying the actual format of the value can only be performed with
the new methods added to JATO 1.2.1: getInsertQueryValue() and
getUpdateQueryValue().
Here's how to add the TIMESTAMP prefix for an insert statement when
using PointBase.
Let's assume we are inserting a value into the ORDERS table that has
a column named ORDER_DATE that is a TIMESTAMP data type. Let's also
assume that we have a JATO SQL model named OrdersModelImpl that is a
subclass of QueryModelBase, and has a "logical" field named OrderDate
that represents the ORDER_DATE column in the ORDERS table.
1) In the OrdersModelImpl, override the method from QueryModelBase
named:
protected Object getInsertQueryValue(String fieldName)
In this method, use the fieldName parameter to check to see if it is
the field for which we want to modify the value. If it is the desired
fieldName, using the fieldName, get its value, modify as needed, then
use the modified value as the return value of this method. Here's a
code sample:
In our OrdersModelImpl (subclass of JATO's QueryModelBase)
protected Object getInsertQueryValue(String fieldName)
{
if (fieldName.equals("OrderDate"))
return "TIMESTAMP " + getValue(fieldName);
return getValue(fieldName);
}
Since the value also needs to be quoted (the value, but not the
TIMESTAMP prefix), you can either add the quotes here, or override
the shouldQuoteType() method for TIMESTAMP types as well, and don't
re-quote the value here (see tip #0003 for use of shouldQuoteType()
method). The sample code above assumes that the shouldQuoteType()
technique was implemented for TIMESTAMP values.
Advanced Techniques
-
Using the QueryFieldSchema of your SQL Models, you can use all sorts
of metadata about the fields to make value modifications at a much
more coarse grain level. For instance, the TIMESTAMP value
modification is going to need to be performed for every TIMESTAMP
field in the entire database, not just in the ORDERS table.
It would be to our advantage to compose a common query model class
that extends QueryModelBase that all of our SQL Models would extend
to inherit some common behavior like the implementation of the above
getInsertQueryValue method.
Now let's assume that we've created a class named
CommonQueryModelBase that extends QueryModelBase and that all of our
SQL Models extend CommonQueryModelBase, like so:
QueryModelBase
|
|
CommonQueryModelBase (overrides getInsertQueryValue(String))
| |
||
OrdersModelImplCustomersModelImpl ... and so on
Now, instead of just checking for a specific field name, we use the
fieldName param to get the fields schema, and check a particular
piece of the field schema, like the Class type (String, Integer,
BigDecimal, Timestamp, etc), and modify the value as required by our
target database. Here is the new code to perform a Class type check:
In our CommonQueryModelBase (subclass of JATO's QueryModelBase)
protected Object getInsertQueryValue(String fieldName)
{
Class fieldClass = getFieldSchema()
.getFieldDescriptor(fieldName)
.getFieldClass();
// PointBase TIMESTAMP columns need a TIMESTAMP prefix
if (fieldClass == java.sql.Timestamp.class)
return "TIMESTAMP " + getValue(fieldName);
// PointBase TIME columns need a TIME prefix
else
if (fieldClass == java.sql.Time.class)
return "TIME " + getValue(fieldName);
// PointBase DATE columns need a DATE prefix
else
if (fieldClass == java.sql.Date.class)
return "DATE " + getValue(fieldName);
// no value mods needed - return original value
return getValue(fieldName);
}
This will solve our task for all of our fields in all of our SQL
Models that extend our CommonQueryModelBase class.
We could use this same technique based on other types of field
metadata. The following are methods of JATO's QueryFieldDescriptor
class that may be usefull. For a complete list of methods and
documentation, refer to the JATO API javadocs:
- public Class getFieldClass() (used in above example)
- public int getInsertValueSource()
- public String getColumnName()
- public String getQualifiedColumnName()
- public boolean isComputedField()
- public boolean isKey()
