Comparisons of Oracle DATE column with java.sql.timestamp via JOOQ

Question

I am using jooq to build queries for Oracle. Everything works fine except for dates:

public static void main(String[] args) throws SQLException {
    java.sql.Timestamp now = new java.sql.Timestamp(new Date().getTime());
    Connection con = DriverManager.getConnection(... , ... , ...);
    final Factory create = new OracleFactory(con);
    Statement s = con.createStatement();
    s.execute("create table test_table ( test_column DATE )");
    s.execute("insert into test_table values (to_date('20111111', 'yyyymmdd'))");

    // -- using to_date
    ResultSet rs = s.executeQuery("select count(1) from test_table where test_column<to_date('20121212', 'yyyymmdd')");
    rs.next();
    System.out.println(""+rs.getInt(1));
    rs.close();     

    // -- using a preparedstatement with java.sql.timestamp
    PreparedStatement ps = con.prepareStatement("select count(1) from test_table where test_column<?");
    ps.setTimestamp(1,now);
    rs = ps.executeQuery();
    rs.next();
    System.out.println(""+rs.getInt(1));
    rs.close();

    // -- using jooq with java.sql.timestamp
    final org.jooq.Table<org.jooq.Record> table = create.tableByName("TEST_TABLE");
    final org.jooq.SelectSelectStep sss = create.select(create.count());
    final org.jooq.SelectJoinStep sjs = sss.from(table);
    final org.jooq.SelectConditionStep scs = sjs.where(create.fieldByName("TEST_COLUMN").lessThan(now));
    System.out.println(scs.toString());
    rs = s.executeQuery(scs.toString());
    rs.next();
    System.out.println(""+rs.getInt(1));
    rs.close();

    s.close();

}

Gives the following output:

   1
   1
   select count(*) from "TEST_TABLE" where "TEST_COLUMN" < '2012-12-12 19:42:34.957'
   Exception in thread "main" java.sql.SQLDataException: ORA-01861: literal does not match format string

I would have thought that JOOQ would check the type of Object in lessThan(Object) to determine whether it can come up with a reasonable conversion, but apparently it just does an Object.toString() in this case. I also remember that I never had issues with date queries via JOOQ in MySQL (although this is a while back). What am I doing wrong?

Lukas Eder · Accepted Answer · 2012-12-13 07:59:23Z

1

I suspect that this issue is due to the fact that create.fieldByName() doesn't know the type of the column (hence, Object), and coerces that unknown type on the right hand side of the comparison predicate. That should be fixed in jOOQ. I have registered #2007 for this: https://github.com/jOOQ/jOOQ/issues/2007

In the mean time, try explicitly setting the type on your field:

create.fieldByName(Timestamp.class, "TEST_COLUMN").lessThan(now)

answered Dec 13, 2012 at 7:59

Lukas Eder

223k138 gold badges731 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Comparisons of Oracle DATE column with java.sql.timestamp via JOOQ

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related