CODEWITHSUNDEEP
javasqloracle-databasejdbcto-date
Starryd
Starryd

Reputation: 23

Java, time-related objects and oracle to_date function

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?

Upvotes: 2

Views: 6391

Answers (2)

Basil Bourque
Basil Bourque

Reputation: 339053

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.

Upvotes: 3

Youcef LAIDANI
Youcef LAIDANI

Reputation: 60006

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 > ?

Upvotes: 2

Related Questions