"Best Practices for JDBC Programming"
Vol. 9, Issue 10, p. 22

	



Listing 1

	public void myMethod()
	{
		Connection conn = null;

		try
		{
			Context initContext = new InitialContext();
			Context envContext  = 
				(Context)initContext.lookup("java:/comp/env");

			DataSource ds = 
				(DataSource)envContext.lookup("jdbc/ora10g");
			conn = ds.getConnection();

			// Your application code here
		}
		catch (Throwable t)
		{
			// Your error handling code here
			t.printStackTrace();
		}
		finally
		{
			DatabaseUtility.close(conn);
		}
	}

Listing 2

  public PurchaseOrderVO getPurchaseOrder(int orderNbr)
    throws SQLException
  {
    PurchaseOrderVO order = null;

    PreparedStatement pStmt = null;
    ResultSet results = null;

    try
    {
	// Application code here.
    }
    finally
    {
      	if (results != null)
      	{
      		try {results.close();}
      		catch (SQLException s)
      		{
      			// Log warning here.
      		}
      	}
	if (pStmt != null)
	{
		try {pStmt.close();}
		catch (SQLException s)
		{
			// Log warning here.
		}
	}
	// Connection purposely not closed -
	//    managed elsewhere.
    }

    return order;
  }

Listing 2a

  public PurchaseOrderVO getPurchaseOrder(int orderNbr)
    throws SQLException
  {
    PurchaseOrderVO order = null;

    PreparedStatement pStmt = null;
    ResultSet results = null;

    try
    {
	// Application code here.
    }
    finally
    {
    	// CementJ alternative for close
      	DatabaseUtility.close(results, pStmt);
    }

    return order;
  }

Listing 3

public class DatabaseUtility
{
  public static void close(PreparedStatement pStmt)
  {
    if (pStmt == null) return;
    try  {pStmt.close();}
    catch (SQLException e) 
    {
LogManager.getLogger().logWarning("Prepared statement close error", e);
    }
  }

  public static void close(Statement stmt)
  {
    if (stmt == null) return;
    try  {stmt.close();}
    catch (SQLException e)
    {
LogManager.getLogger().logWarning("Statement close error", e);
    }
  }

  public static void close(ResultSet rs)
  {
    if (rs == null) return;
    try  {rs.close();}
    catch (SQLException e)
    {
LogManager.getLogger().logWarning("ResultSet close error", e);
    }
  }

    public static void close(Object dbObj)
    {
      if (dbObj == null) return;
      if (dbObj instanceof PreparedStatement) close( (PreparedStatement) dbObj);
      else if (dbObj instanceof Statement) close( (Statement) dbObj);
      else if (dbObj instanceof ResultSet) close( (ResultSet) dbObj);
      else if (dbObj instanceof CallableStatement) close( (CallableStatement)
	   dbObj);
      else if (dbObj instanceof Connection) close( (Connection) dbObj);
      else 
 	throw new IllegalArgumentException(
	"Close attempted on unrecognized Database Object!");
    }
}

Listing 4

	private static final String INSERT_STMT =
		"insert into PURCHASE_ORDER (CUSTOMER_ID) values (?)";
	protected void runTest() throws Exception
	{
		PreparedStatement pStmt = null;
		ResultSet genKeys = null;
		try
		{
			pStmt = _dbConnection.prepareStatement(INSERT_STMT);
			pStmt.setString(1, "foo");
			pStmt.executeUpdate();

			// Generated Key Processing
			genKeys = pStmt.getGeneratedKeys();
			while (genKeys.next())
			{
				System.out.println(genKeys.getString(1));
			}
		}
		finally
		{
			DatabaseUtility.close(genKeys, pStmt);
		}
	}

Listing 5

	private static final String SQL_STMT =
		"select * from all_views";
	private long timeFetch(int fetchSize)
		throws SQLException
	{
		long beginTime = System.currentTimeMillis();

		Statement stmt = null;
		ResultSet results = null;

		try
		{
			stmt = _dbConnection.createStatement();
			if (fetchSize > 0)
			{
				stmt.setFetchSize(fetchSize);
			}
			results = stmt.executeQuery(SQL_STMT);
			while (results.next())  {}
		}
		finally
		{
			DatabaseUtility.close(results, stmt);
		}

		return (System.currentTimeMillis() - beginTime);
	}

Listing 6

	private static final String INSERT_STMT =
		"insert into PURCHASE_ORDER_LN " +
		"(ORDER_NBR, ITEM_ID, QTY_ORDERED, PRICE) " +
		"values (?,?,?,?)";
	protected void runTest() throws Exception
	{
		PreparedStatement pStmt = null;
		try
		{
			pStmt = _dbConnection.prepareStatement(INSERT_STMT);

			pStmt.setInt(1, 21);
			pStmt.setString(2, "itm1");
			pStmt.setInt(3, 5);
			pStmt.setDouble(4, 5.95);
			pStmt.addBatch();

			pStmt.setInt(1, 21);
			pStmt.setString(2, "itm2");
			pStmt.setInt(3, 10);
			pStmt.setDouble(4, 6.95);
			pStmt.addBatch();

			pStmt.setInt(1, 21);
			pStmt.setString(2, "itm3");
			pStmt.setInt(3, 8);
			pStmt.setDouble(4, 7.95);
			pStmt.addBatch();

			pStmt.executeBatch();
		}
		finally
		{
			DatabaseUtility.close(pStmt);
		}
	}