Java Database Connectivity (JDBC)

In past 2 days, I tried to get to know how Oracle JDBC driver deal with table's column with Date or Timestamp data type. I have finished some test. I'd like to show the result below.

The main issue is difference behavior between driver version 9i and 10g.

Runtime Environments:

1, Sun JDK 1.4.2_04

2, Oracle Application Server 10.1.0.2
Installed at D:/oc4j1012

3, Oracle JDBC Driver 10g :
quote from D:/oc4j1012/jdbc/lib/ojdbc14.jar/META-INF/MANIFEST.MF

Implementation-Version: "Oracle JDBC Driver version - 10.1.0.3.0"
Specification-Title: "Oracle JDBC driver classes for use with JDK1.4"
Specification-Version: "Oracle JDBC Driver version - 10.1.0.3.0"
Implementation-Title: "ojdbc14.jar"
Implementation-Time: "Mon Nov 22 12:08:18 2004"

4, Oracle JDBC Driver 9.2.0.1.0 :
quote from D:/oracle/ora92/jdbc/lib/ojdbc14.jar/META-INF/MANIFEST.MF

Specification-Title: "Oracle JDBC driver classes for use with JDK1.4"
Specification-Version: "Oracle JDBC Driver version - 9.0.2.0.0"
Specification-Vendor: "Oracle Corporation" .
Implementation-Title: "ojdbc14_g.jar"
Implementation-Version: "Oracle JDBC Driver version - 9.0.2.0.0"
Implementation-Vendor: "Oracle Corporation"
Implementation-Time: "Thu Apr 25 23:48:25 2002"

5, Oracle JDBC Driver 9.2.0.5.0:
I download this ojdbc14.jar from otn web site.

Specification-Title: "Oracle JDBC driver classes for use with JDK1.4"
Specification-Version: "Oracle JDBC Driver version - 9.0.2.0.0"
Specification-Vendor: "Oracle Corporation" .
Implementation-Title: "ojdbc14.jar"
Implementation-Version: "Oracle JDBC Driver version - 9.0.2.0.0"
Implementation-Vendor: "Oracle Corporation"
Implementation-Time: "Tue Apr 6 01:10:57 2004"

6, Database Server and Table

Database Server:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

Table for test:

CREATE TABLE AA_TEMP
(
D1 DATE,
D2 TIMESTAMP(3)
)

INSERT INTO AA_TEMP (D1, D2 ) VALUES ( TO_Date( '04/20/2005 12:22:22', 'MM/DD/YYYY HH:MI:SS AM'), 2005-4-20 22:22:35.000000);

7, The test program executes a simple query, select * from AA_TEMP.

Resultset rs = ......

ResultSetMetaData rsmd = rs.getMetaData();

int t1 = rsmd.getColumnType(1)
int t2 = rsmd.getColumnType(2)

Object o1 = rs.getObject(1)
Object o2 = rs.getObject(2)

String c1 = o1.getClass().getName();
String c2 = o2.getClass().getName();

8, Test result
case 1) When using driver version 9.0.2.1.0
t1 = 91 (java.sql.Types.DATE)
c1 = java.sql.Timestamp

t2 = 93 (java.sql.Types.TIMESTAMP)
c2 = oracle.sql.TIMESTAMP

case 2) When using driver version 9.0.2.5.0
t1 = 91 (java.sql.Types.DATE)
c1 = java.sql.Timestamp

t2 = 93 (java.sql.Types.TIMESTAMP)
c2 = oracle.sql.DATE

case 3) When using driver version 10g, just put D:/oc4j1012/jdbc/lib/ojdbc14.jar into CLASSPATH
t1 = 91 (java.sql.Types.DATE)
c1 = java.sql.Date. Only data of part of year, month, day, even if this column data include hour, minute, second component

t2 = 93 (java.sql.Types.TIMESTAMP)
c2 = oracle.sql.TIMESTAMP

case 4) When using driver version 10g, just put D:/oc4j1012/j2ee/home/oc4j.jar into CLASSPATH, don't put D:/oc4j1012/jdbc/lib/ojdbc14.jar into CLASSPATH directly.
t1 = 91 (java.sql.Types.DATE)
c1 = java.sql.Date. Only data of part of year, month, day, even if this column data include hour, minute, second component

t2 = 93 (java.sql.Types.TIMESTAMP)
c2 = java.sql.Timestamp

According to JDBC specification, Date mapped into java.sql.Date, Timestamp mapped into java.sql.Timestamp

Only in last case, the driver fully apply for JDBC specification. I notice in this case, actually, the program runs just like in Oracle Application Server environment. I just put D:/oc4j1012/j2ee/home/oc4j.jar into CLASSPATH, I don't put D:/oc4j1012/jdbc/lib/ojdbc14.jar into CLASSPATH directly. It must be oc4j.jar makes the mystery. Does it a mystery? It does'nt. But It's so funny.

oc4j.jar reference other jar files at runtime. Quote from oc4j.jar/META-INF/MANIFEST.MF:
Main-Class: com.evermind.server.OC4JServer
Class-Path: lib/ejb.jar lib/servlet.jar lib/ojsp.jar lib/jndi.jar lib/
jdbc.jar iiop.jar iiop_gen_bin.jar lib/jms.jar lib/jta.jar lib/jmxri
.jar lib/javax77.jar lib/javax88.jar ../../opmn/lib/ons.jar ../../op
mn/lib/optic.jar ../../lib/dms.jar ../../dms/lib/dms.jar ../../diagno
stics/lib/ojdl.jar ../../dms/diagnostics/lib/ojdl.jar lib/connector.
jar lib/bcel.jar lib/cos.jar lib/jsse.jar ../../oracle/lib/jsse.jar
lib/jnet.jar lib/jcert.jar lib/activation.jar lib/mail.jar ../../java
vm/lib/jasper.zip ../../lib/xmlparserv2.jar ../../oracle/lib/xmlparse
rv2.jar ../../jdbc/lib/orai18n.jar ../../oracle/jdbc/lib/orai18n.jar
lib/jaxp.jar lib/jaas.jar jazn.jar ../../jdbc/lib/classes12dms.jar ..
/../oracle/jdbc/lib/classes12dms.jar ../../jdbc/lib/nls_charset12.jar
../../oracle/jdbc/lib/nls_charset12.jar jaxb-rt-1.0-ea.jar ../../so
ap/lib/soap.jar ../../webservices/lib/wsserver.jar ../../webservices/
lib/wsdl.jar ../../rdbms/jlib/aqapi.jar lib/jem.jar ../../javacache/l
ib/cache.jar lib/http_client.jar ../../jlib/jssl-1_1.jar ../../oracle
/jlib/jssl-1_1.jar ../../jlib/repository.jar ../../oracle/jlib/reposi
tory.jar lib/jaasmodules.jar ../../sqlj/lib/runtime12ee.jar ../../sql
j/lib/translator.jar lib/crimson.jar ../../jlib/ojpcs.jar ../../oracl
e/jlib/ojpcs.jar ../../jlib/ojpcp.jar ../../oracle/jlib/ojpcp.jar ../
../jlib/ojpse.jar ../../oracle/jlib/ojpse.jar ../../jlib/ojpsmime.jar
../../oracle/jlib/ojpsmime.jar ../../jlib/ojpcms.jar ../../oracle/jl
ib/ojpcms.jar

9, If my program runs under non OAS(Oracle Application Server) system, How should I deal with Date or Timestamp data type?