Dealing with Oracle's TIMESTAMP WITH TIMEZONE when exporting metadata

[timowest]

Hi again everyone.

This question is in regard to supporting (or rather working around) custom database types when exporting meta data.

In my case, I'm working with a preexisting schema inside an Oracle Database, and I'm finding tables which are of Oracle's TIMESTAMP WITH TIMEZONE type. It seems other people have dealt with this issue between Oracle and JDBC. http://stackoverflow.com/questions/223096?sort=oldest

As there seems to be no equivalent JDBC Type definition, I found that my first attempt to use MetaDataExporter with my target Oracle schema died with a NullPointerException at

MetaDataExporter::handleColumn

if I'm not mistaken (sorry, I'm at home with no access to the code at my job :P), the reason is that when the code tries to find a Java class mapping for int -101 (a code not present in java.sql.Types, and perhaps a number chosen by Oracle to represent TIMESTAMP WITH TIMEZONE), this line returns null:

Class<?> clazz = sqlTypeMapping.get(columns.getInt(COLUMN_TYPE));

The only workaround I found was to define a “dummy†OracleTimestampWithTimeZone type like this:


package org.groundhog;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.naming.OperationNotSupportedException;
import com.mysema.query.sql.types.Type;

public class OracleTimeStampWithTimeZoneType implements Type<String> {

    @Override
    public int[] getSQLTypes() {
        return new int [] { -101 };
    }

    @Override
    public Class<String> getReturnedClass() {
        return String.class;
    }

    @Override
    public String getValue(ResultSet rs, int startIndex) throws SQLException {
        return rs.getString(startIndex);
    }

    @Override
    public void setValue(PreparedStatement st, int startIndex, String value)
            throws SQLException {
        throw new SQLException(new OperationNotSupportedException("Sorry... don't know what to do :("));
    }
}


And then registering it in QueryDSL SQL's Configuration.DEFAULT instance like so:

Configuration.DEFAULT.register(new OracleTimeStampWithTimeZoneType());

essentially telling QueryDSL “yeah... that's basically a String, but I don't know how to handle it†.

So I'm wondering if there's a cleaner way to deal with database types which do not have a corresponding JDBC Type mapping?

Thanks again for your time and help.

JZM

timowest

The approach you chose is the right one, but you should probably pick a different Java representation than String for Timestamp with timezone, e.g. java.sql.Date, java.sql.Timestamp or oracle.sql.TIMESTAMPTZ or something from the Joda time API.

Something like this might be better


public class OracleTimeStampWithTimeZoneType implements Type<java.sql.Date> {

    @Override
    public int[] getSQLTypes() {
        return new int [] { -101 };
    }

    @Override
    public Class<String> getReturnedClass() {
        return Date.class;
    }

    @Override
    public Date getValue(ResultSet rs, int startIndex) throws SQLException {
        TIMESTAMPTZ ts = (TIMESTAMPTZ)rs.getObject(startIndex);
        return ts.dateValue();
    }

    @Override
    public void setValue(PreparedStatement st, int startIndex, Date value)
            throws SQLException {
        st.setObject(startIndex, new TIMESTAMPTZ(getConnection(), value), -101);
    }
}


I haven't tested this yet, but something similar should work. Maybe you can also use getDate / setDate directly.