CODEWITHSUNDEEP
javaoracle-databasejdbctimestampcachedrowset
Jadic
Jadic

Reputation: 33

How can i get whole date and time data when using cachedrowset from Oracle date field

I get data from oracle with result set and then populate them to cachedrowset. But when I get a date field in database and print it, it only print the date. I check the api and know that the getDate() method return java.sql.date,and time of sql.date is casted to 0. I test that if I call resultset.getString("datefield"), it can get whole date and time data, so is there any way to get whole date and time data using cahcedrowset, thanks

here's my source code

    CachedRowSet rs = null;
    try {
    rs = executeQuery("select * from t_web_order_result ");
    if (rs != null && rs.next()) {
            KKLog.info(rs.getDate("stime"));//2012-04-24,it should be 2012-04-24 09:23:33
        KKLog.info(rs.getString("stime"));
    }
    } catch (SQLException e) {
        logErr("queryCheckers Err", e);
    } finally {
        closeCachedRowset(rs);
    }

executeQuery method 

    CachedRowSet rowset = null;
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet rs = null;
    try {
        connection = getConnection();
        statement = connection.prepareStatement(sql);
        for (int i = 0; i < params.length; i ++) {
            statement.setObject(i + 1, params[i]);
        }
        rs = statement.executeQuery();
        rowset = new CachedRowSetImpl();
        rowset.populate(rs);
        rs.close();
        statement.close();
    } finally {
         //close....            
    }
    return rowset;

i used three method to print the log 

    KKLog.info(rs.getDate("stime"));
    KKLog.info(rs.getString("stime"));
    KKLog.info(rs.getTimestamp("stime"));

and the log content is

    12-11-11 17:06:11 2012-04-24 
    12-11-11 17:06:11 2012-04-24 
    12-11-11 17:06:11 2012-04-24 00:00:00.0

Upvotes: 1

Views: 6255

Answers (1)

Jacob
Jacob

Reputation: 14731

If you want to get the date and time from database, you have to use

KKLog.info(rs.getTimestamp("stime"))

This will give both date and time from table provided date and time is stored as date data type or as timestamp . Also note that you have to use java.sql.Date instead of java.util.Date.

Update 1

There are some bugs associated with getting time part from database if cachedrowset is used.

I tried using CachedRowSet and time part was missing. In order to resolve this issue, add the following in the database connection part. I could see the time part as well.

    java.util.Properties prop = new java.util.Properties();
    prop.put("oracle.jdbc.V8Compatible", "true"); 
    prop.put("user", "user");
    prop.put("password", "password");
   Connection conn = 
  DriverManager.getConnection("jdbc:oracle:thin:@server:port:SID",prop);

Adding prop.put("oracle.jdbc.V8Compatible", "true"); is the key in resolving this issue.

My date and time before 

2012-11-08 00:00:00

After adding V8Compatible

2012-11-08 13:28:35

Hope this helps

Upvotes: 2

Related Questions