Tuesday, 26 April 2011

Display DATE columns from Oracle JDBC

If we display a DATE column from a Oracle JDBC program we may not get the desired output. This basic demo shows how to format the display to meet your own needs using the toText method from a oracle.sql.DATE object.

Code

package pas.au.jdbc.dates;

import java.sql.Connection;
import java.sql.SQLException;

import java.util.Date;

import oracle.jdbc.OracleResultSet;
import oracle.jdbc.OracleStatement;
import oracle.jdbc.pool.OracleDataSource;

import oracle.sql.DATE;


public class FormatDateJDBC
{
  private OracleDataSource ods = null;
  private final String userId = "scott";
  private final String password = "tiger";
  private final String url = 
    "jdbc:oracle:thin:@beast.au.oracle.com:1524/linux11gr2";
  
  public FormatDateJDBC() throws SQLException
  {
    ods = new OracleDataSource();
    ods.setUser(userId);
    ods.setPassword(password);
    ods.setURL(url);
  }

  public Connection getConnection() throws SQLException
  {
    return ods.getConnection();
  }

  public void run()
  {
    Connection conn = null;
    OracleStatement stmt = null;
    OracleResultSet rset = null;
    
    try
    {
      conn = getConnection();
      System.out.println(conn);
      stmt = (OracleStatement)conn.createStatement();
      rset = (OracleResultSet) stmt.executeQuery("select SYSDATE from dual");
      rset.next();
      if (rset != null)
      { 
        DATE todaysDate = rset.getDATE(1);
        System.out.println
          (String.format("Todays date is %s", 
                         todaysDate.toText("DD/MM/YY HH:MM:SS", null)));
      }
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
    finally
    {
      /* 
       * if exception occurs here no real need to log it just catch it and continue
       */
      if (rset != null)
      {
        try
        {
          rset.close();
        }
        catch (SQLException s)
        {}
      }
      
      if (stmt != null)
      {
        try
        {
          stmt.close();
        }
        catch (SQLException s)
        {}
      }     

      if (conn != null)
      {
        try
        {
          conn.close();
        }
        catch (SQLException s)
        {}
      }
    }
  }
  
  public static void main(String[] args)
  {
    FormatDateJDBC demo = null;
    try
    {
      System.out.println("Started JDBC display date at " + new Date());
      demo = new FormatDateJDBC();
      demo.run();
      System.out.println("Completed JDBC display date at " + new Date());
    }
    catch (SQLException e)
    {
      e.printStackTrace();
      System.exit(-1);
    }
  }
}

Output

Started JDBC display date at Tue Apr 26 11:07:59 EST 2011
oracle.jdbc.driver.T4CConnection@c24c0
Todays date is 26/04/11 10:04:38
Completed JDBC display date at Tue Apr 26 11:08:00 EST 2011

No comments: