8

I have a Java application where the user builds their SQL select statement on the screen. Once they enter the select, I analyze with JDBC the column types.

For Oracle, the JDBC equivalent of type DATE is java.sql.Types.Timestamp, as DATE includes the time.

The problem is that I need to format the result set depending on the column type. If it's DATE, I need to format the value YYYY-MM-DD. If it's TIMESTAMP, I need to format the value YYYY-MM-DD HH:MM:SS.

What I need is to differentiate in JDBC between DATE and TIMESTAMP results. Is there a way to achieve this?

Sample code:

        String sql = "select date_col, timestamp_col from some_table";
        ResultSet rs = stmt.executeQuery(sql);
        ResultSetMetaData meta = rs.getMetaData();
        int count = meta.getColumnCount();

        for (int i=1;i <= count;i++) {
            int type = meta.getColumnType(i);
            System.out.println(type);
        }

This prints twice 93, which is java.sql.Types.Timestamp.

CREATE TABLE "DB1"."SOME_TABLE" 
   ("SOMENUM" NUMBER(9,0), 
    "DATE_COL" DATE, 
    "TIMESTAMP_COL" TIMESTAMP (6), 
    "ACCOUNT" NUMBER(9,0), 
    "BALANCE" FLOAT(126))

Note: I'm running this on Wildfly 14

I tried to cast the sql Connection to OracleConnection (to get the oracle.sql types) and Wildfly throws:

org.jboss.jca.adapters.jdbc.jdk8.WrappedConnectionJDK8 cannot be cast to oracle.jdbc.OracleConnection

ps0604
  • 1,227
  • 23
  • 133
  • 330
  • Possible duplicate of [Date type without time in Oracle](https://stackoverflow.com/questions/10429276/date-type-without-time-in-oracle) – Mick Mnemonic Mar 29 '19 at 22:20
  • @Mick The answers to the question you mention talk about how to insert a DATE column without time. What I need is to differentiate in JDBC between DATE and TIMESTAMP. – ps0604 Mar 29 '19 at 22:26
  • Hmm.. actually, according to [the specs](https://docs.oracle.com/cd/E11882_01/java.112/e16548/apxref.htm#JJDBC28905) SQL DATE in Oracle maps to [`java.sql.Date`](https://docs.oracle.com/javase/7/docs/api/java/sql/Date.html), which doesn't include any time information. Could you share some code to highlight your exact problem? – Mick Mnemonic Mar 29 '19 at 22:34
  • added sample code – ps0604 Mar 29 '19 at 22:38
  • Could you share the DDL for `some_table`? – Mick Mnemonic Mar 29 '19 at 22:41
  • Added the CREATE TABLE – ps0604 Mar 29 '19 at 22:48
  • I don't have an Oracle DB to try this on, but `ResultSetMetaData` has `getSchemaName`, `getTableName` and `getColumnName` methods. Can you use these to look up info in the metadata views (I think `ALL_TAB_COLUMNS` is the appropriate one in Oracle), and use the JDBC type as a fallback? – Player One Mar 29 '19 at 23:00
  • you mean to get the type from the column name? I cannot do that as a column may be a function. – ps0604 Mar 29 '19 at 23:19
  • Sorry, I misread the specs link I added above. The type used is actually Oracle-specific, `oracle.sql.Date`. Could you try to use Oracle-specific types by type-casting the metadata object into [`OracleDatabaseMetaData`](https://download.oracle.com/otn_hosted_doc/jdeveloper/905/jdbc-javadoc/oracle/jdbc/OracleDatabaseMetaData.html)? – Mick Mnemonic Mar 30 '19 at 00:31
  • In Oracle, `DATE` **is** equivalent to the SQL standard `TIMESTAMP` type as it as a time component, so that is why JDBC reports it as a `java.sql.Types.TIMESTAMP`. – Mark Rotteveel Mar 30 '19 at 07:43
  • 1
    What does the 'getColumnTypeName(int column)' method return? – JAlexey Apr 01 '19 at 20:31
  • 1
    @JAlexey great idea, they return DATE and TIMESTAMP. – ps0604 Apr 01 '19 at 21:21
  • Casting is the wrong way to get a handle to the `OracleConnection` object. Instead: `oracleConn = (oracle.jdbc.OracleConnection) conn.getUnderlyingConnection();` – Matthew McPeak Apr 02 '19 at 12:22

1 Answers1

4

You can use ResultSetMetdata#getColumnTypeName to get actual DB type name

Retrieves the designated column's database-specific type name.

Return type name used by the database. If the column type is a user-defined type, then a fully-qualified type name is returned.

Community
  • 1
  • 1
Mạnh Quyết Nguyễn
  • 17,677
  • 1
  • 23
  • 51