I would like to get the DATETIME column from an Oracle DB Table with JDBC. Here is my code:
int columnType = rsmd.getColumnType(i); if(columnType == Types.DATE) { Date aDate = rs.getDate(i); valueToInsert = aDate.toString(); } else if(columnType == Types.TIMESTAMP) { Timestamp aTimeStamp = rs.getTimestamp(i); valueToInsert = aTimeStamp.toString(); } else { valueToInsert = rs.getString(i); }
I have to identify the column type first. The field I am interested in is recognized as a Types.DATE, but it is really a DATETIME in the DB since it has this format: "07.05.2009 13:49:32"
getDate truncates the time: "07.05.2009" and getString appends ".0" to it: "07.05.2009 13:49:32.0"
Of course I could just remove the final .0 and work with getString all the time, but it is a dirty workaround.
Any ideas? I was looking for a getDateTime method.
The getRow() method of the ResultSet interface retrieves the current row number/position of the ResultSet pointer. This method returns an integer value representing the current row number to which the ResultSet pointer points to.
We can use getX() method to get the data of the columns while iterating through the results where X – is the datatype of the column. We can use either Column Names or Index to get the values using getX() methods. We can also mention index number of the Column instead of Column Name in the getX() methods.
The JDBC ResultSet doesn't provide any isEmpty(), length() or size() method to check if its empty or not. Hence, when a Java programmer needs to determine if ResultSet is empty or not, it just calls the next() method and if next() returns false it means ResultSet is empty.
This answer is outdated. Continue to Basil Bourque's answer.
java.util.Date date; Timestamp timestamp = resultSet.getTimestamp(i); if (timestamp != null) date = new java.util.Date(timestamp.getTime()));
Then format it the way you like.
The answer by Leos Literak is correct but now outdated, using one of the troublesome old date-time classes, java.sql.Timestamp
.
it is really a DATETIME in the DB
Nope, it is not. No such data type as DATETIME
in Oracle database.
I was looking for a getDateTime method.
Use java.time classes in JDBC 4.2 and later rather than troublesome legacy classes seen in your Question. In particular, rather than java.sql.TIMESTAMP
, use Instant
class for a moment such as the SQL-standard type TIMESTAMP WITH TIME ZONE
.
Contrived code snippet:
if( JDBCType.valueOf( myResultSetMetaData.getColumnType( … ) ) .equals( JDBCType.TIMESTAMP_WITH_TIMEZONE ) ) { Instant instant = myResultSet.getObject( … , Instant.class ) ; }
Oddly enough, the JDBC 4.2 specification does not require support for the two most commonly used java.time classes, Instant
and ZonedDateTime
. So if your JDBC does not support the code seen above, use OffsetDateTime
instead.
OffsetDateTime offsetDateTime = myResultSet.getObject( … , OffsetDateTime.class ) ;
I would like to get the DATETIME column from an Oracle DB Table with JDBC.
According to this doc, there is no column data type DATETIME
in the Oracle database. That terminology seems to be Oracle’s word to refer to all their date-time types as a group.
I do not see the point of your code that detects the type and branches on which data-type. Generally, I think you should be crafting your code explicitly in the context of your particular table and particular business problem. Perhaps this would be useful in some kind of generic framework. If you insist, read on to learn about various types, and to learn about the extremely useful new java.time classes built into Java 8 and later that supplant the classes used in your Question.
valueToInsert = aDate.toString();
You appear to trying to exchange date-time values with your database as text, as String
objects. Don’t.
To exchange date-time values with your database, use date-time objects. Now in Java 8 and later, that means java.time objects, as discussed below.
You may be confusing three sets of date-time related data types:
The SQL standard defines five types:
DATE
TIME WITHOUT TIME ZONE
TIME WITH TIME ZONE
TIMESTAMP WITHOUT TIME ZONE
TIMESTAMP WITH TIME ZONE
DATE
TIME
or TIME WITHOUT TIME ZONE
TIME WITH TIME ZONE
(or TIMETZ
)TIMESTAMP
or TIMESTAMP WITHOUT TIME ZONE
TIMESTAMP WITH TIME ZONE
(or TIMESTAMPTZ
)Many database offer their own date-time related types. The proprietary types vary widely. Some are old, legacy types that should be avoided. Some are believed by the vendor to offer certain benefits; you decide whether to stick with the standard types only or not. Beware: Some proprietary types have a name conflicting with a standard type; I’m looking at you Oracle DATE
.
The Java platform's handles the internal details of date-time differently than does the SQL standard or specific databases. The job of a JDBC driver is to mediate between these differences, to act as a bridge, translating the types and their actual implemented data values as needed. The java.sql.* package is that bridge.
Prior to Java 8, the JDBC spec defined 3 types for date-time work. The first two are hacks as before Version 8, Java lacked any classes to represent a date-only or time-only value.
So that answers your question regarding no "getDateTime" method in the ResultSet interface. That interface offers getter methods for the three bridging data types defined in JDBC:
getDate
for java.sql.DategetTime
for java.sql.TimegetTimestamp
for java.sql.TimestampNote that the first lack any concept of time zone or offset-from-UTC. The last one, java.sql.Timestamp
is always in UTC despite what its toString
method tells you.
You should avoid those poorly-designed JDBC classes listed above. They are supplanted by the java.time types.
java.sql.Date
, use LocalDate
. Suits SQL-standard DATE
type.java.sql.Time
, use LocalTime
. Suits SQL-standard TIME WITHOUT TIME ZONE
type.java.sql.Timestamp
, use Instant
. Suits SQL-standard TIMESTAMP WITH TIME ZONE
type. As of JDBC 4.2 and later, you can directly exchange java.time objects with your database. Use setObject
/getObject
methods.
Insert/update.
myPreparedStatement.setObject( … , instant ) ;
Retrieval.
Instant instant = myResultSet.getObject( … , Instant.class ) ;
The Instant
class represents a moment on the timeline in UTC with a resolution of nanoseconds (up to nine (9) digits of a decimal fraction).
If you want to see the moment of an Instant
as viewed through the wall-clock time used by the people of a particular region (a time zone) rather than as UTC, adjust by applying a ZoneId
to get a ZonedDateTime
object.
ZoneId zAuckland = ZoneId.of( "Pacific/Auckland" ) ; ZonedDateTime zdtAuckland = instant.atZone( zAuckland ) ;
The resulting ZonedDateTime
object is the same moment, the same simultaneous point on the timeline. A new day dawns earlier to the east, so the date and time-of-day will differ. For example, a few minutes after midnight in New Zealand is still “yesterday” in UTC.
You can apply yet another time zone to either the Instant
or ZonedDateTime
to see the same simultaneous moment through yet another wall-clock time used by people in some other region.
ZoneId zMontréal = ZoneId.of( "America/Montreal" ) ; ZonedDateTime zdtMontréal = zdtAuckland.withZoneSameInstant( zMontréal ) ; // Or, for the same effect: instant.atZone( zMontréal )
So now we have three objects (instant
, zdtAuckland
, zMontréal
) all representing the same moment, same point on the timeline.
To get back to the code in Question about detecting the data-type of the databases: (a) not my field of expertise, (b) I would avoid this as mentioned up top, and (c) if you insist on this, beware that as of Java 8 and later, the java.sql.Types
class is outmoded. That class is now replaced by a proper Java Enum
of JDBCType
that implements the new interface SQLType
. See this Answer to a related Question.
This change is listed in JDBC Maintenance Release 4.2, sections 3 & 4. To quote:
Addition of the java.sql.JDBCType Enum
An Enum used to identify generic SQL Types, called JDBC Types. The intent is to use JDBCType in place of the constants defined in Types.java.
The enum has the same values as the old class, but now provides type-safety.
A note about syntax: In modern Java, you can use a switch
on an Enum
object. So no need to use cascading if-then statements as seen in your Question. The one catch is that the enum object’s name must be used unqualified when switching for some obscure technical reason, so you must do your switch
on TIMESTAMP_WITH_TIMEZONE
rather than the qualified JDBCType.TIMESTAMP_WITH_TIMEZONE
. Use a static import
statement.
So, all that is to say that I guess (I’ve not tried yet) you can do something like the following code example.
final int columnType = myResultSetMetaData.getColumnType( … ) ; final JDBCType jdbcType = JDBCType.valueOf( columnType ) ; switch( jdbcType ) { case DATE : // FYI: Qualified type name `JDBCType.DATE` not allowed in a switch, because of an obscure technical issue. Use a `static import` statement. … break ; case TIMESTAMP_WITH_TIMEZONE : … break ; default : … break ; }
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.
UPDATE: The Joda-Time project, now in maintenance mode, advises migration to the java.time classes. This section left intact as history.
Prior to Java 8 (java.time.* package), the date-time classes bundled with java (java.util.Date & Calendar, java.text.SimpleDateFormat) are notoriously troublesome, confusing, and flawed.
A better practice is to take what your JDBC driver gives you and from that create Joda-Time objects, or in Java 8, java.time.* package. Eventually, you should see new JDBC drivers that automatically use the new java.time.* classes. Until then some methods have been added to classes such as java.sql.Timestamp to interject with java.time such as toInstant
and fromInstant
.
As for the latter part of the question, rendering a String… A formatter object should be used to generate a string value.
The old-fashioned way is with java.text.SimpleDateFormat. Not recommended.
Joda-Time provide various built-in formatters, and you may also define your own. But for writing logs or reports as you mentioned, the best choice may be ISO 8601 format. That format happens to be the default used by Joda-Time and java.time.
//java.sql.Timestamp timestamp = resultSet.getTimestamp(i); // Or, fake it // long m = DateTime.now().getMillis(); // java.sql.Timestamp timestamp = new java.sql.Timestamp( m ); //DateTime dateTimeUtc = new DateTime( timestamp.getTime(), DateTimeZone.UTC ); DateTime dateTimeUtc = new DateTime( DateTimeZone.UTC ); // Defaults to now, this moment. // Convert as needed for presentation to user in local time zone. DateTimeZone timeZone = DateTimeZone.forID("Europe/Paris"); DateTime dateTimeZoned = dateTimeUtc.toDateTime( timeZone );
Dump to console…
System.out.println( "dateTimeUtc: " + dateTimeUtc ); System.out.println( "dateTimeZoned: " + dateTimeZoned );
When run…
dateTimeUtc: 2014-01-16T22:48:46.840Z dateTimeZoned: 2014-01-16T23:48:46.840+01:00
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With