2

I have an Oracle database with column called UPDATED marked in sqlDeveloper as TIMESTAMP(6). An example entry in that column is 05-MAY-18 04.49.45.000000000

I want to execute following query against it using prepared statement:

SELECT * FROM EXAMPLE_TABLE WHERE UPDATED > TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'.

The question is - how do I bind Java date timeobject (from org.joda.time.LocalDateTime or java.time.LocalDateTime or any other that keeps both data and time?

Here is a test snippet, no matter what I do I can't make it work (apart from situation where I bind new Date(2018, 5,5) but firstly it is just date without time and secondly sql.Date is outdated.

public class Dbaccess {
    public static void main(String[] args) throws SQLException {
        Connection connection = DriverManager.getConnection("someurl", "some_username", "some_password");
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM EXAMPLE WHERE UPDATED > TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS')");
        preparedStatement.setObject(1, LocalDateTime.now());
        ResultSet resultSet = preparedStatement.executeQuery();
        System.out.println("resultSet:" + resultSet);
        while(resultSet.next()) {
            System.out.println(resultSet.getString("some_id"));
        }
    }
}

Unfortunately, I am getting Exception in thread "main" java.sql.SQLException: Invalid column type.

How should we these days binds such variable that is supposed to represent datetime?

Youcef LAIDANI
  • 55,661
  • 15
  • 90
  • 140
Starryd
  • 23
  • 1
  • 4
  • important - it works if I deploy it on h2 database with Oracle compatibility mode but fails on normal oracle db, so it needs to be replicated on real oracle db – Starryd May 07 '18 at 14:58
  • 2
    The parameter you're sending should be a `String`. Why are you doing that, when you can send it as a `java.sql.Timestamp` instead? – Luiggi Mendoza May 07 '18 at 15:01
  • Sorry, not entirely sure what you mean - could you please elaborate? – Starryd May 07 '18 at 15:03
  • 2
    First, your query here tries to receive a `String`: `WHERE UPDATED > TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS')`. This is plain wrong. You can pass an instance of `java.sql.Timestamp` instead that is already a datetime object for Oracle database. So, your query would be changed to `WHERE UPDATED > ?`. – Luiggi Mendoza May 07 '18 at 15:05
  • Ah, I see, my bad! – Starryd May 07 '18 at 15:14

2 Answers2

3

tl;dr

Check what version of JDBC is supported by your JDBC driver.

In JDBC 4.2 and later, you can do the following:

myPreparedStatement.setObject(
    … ,
    LocalDateTime.now()
)

Before JDBC 4.2, see Answer by YCF_L.

JDBC 4.2

As of JDBC 4.2, you can directly exchange java.time objects with your database. No need to ever use java.sql.Timestamp again.

LocalDateTime ldt = LocalDateTime.now() ;  // Better to pass explicitly your desired time zone rather than rely implicitly on the JVM’s current default.

Pass to your prepared statement with setObject method.

myPreparedStatement.setObject( … , ldt ) ;

Retrieval.

LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;

JDBC 4.1 and earlier

If your JDBC driver is not yet updated for JDBC 4.2 or later, see the Answer by YCF_L.

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
2

try to use setDate like this :

LocalDateTime ldt = LocalDateTime.now();
//Convert LocalDateTime to SQL TimeStamp
java.sql.Timestamp dateTimeSql = java.sql.Timestamp.valueOf(ldt);
//Set this time stamp to the query
preparedStatement.setDate(1, java.sql.Timestamp.valueOf(dateTimeSql));

Or just :

preparedStatement.setDate(1, 
     java.sql.Timestamp.valueOf(java.sql.Timestamp.valueOf(LocalDateTime.now()))
);

then you don't need to use TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS') you can just use :

SELECT * FROM EXAMPLE WHERE UPDATED > ?
Youcef LAIDANI
  • 55,661
  • 15
  • 90
  • 140