HomeDigital EditionSys-Con RadioSearch Java Cd
Advanced Java AWT Book Reviews/Excerpts Client Server Corba Editorials Embedded Java Enterprise Java IDE's Industry Watch Integration Interviews Java Applet Java & Databases Java & Web Services Java Fundamentals Java Native Interface Java Servlets Java Beans J2ME Libraries .NET Object Orientation Observations/IMHO Product Reviews Scalability & Performance Security Server Side Source Code Straight Talking Swing Threads Using Java with others Wireless XML
 

"Best Practices for JDBC Programming"
Volume: 5 Issue: 4, Page 50

	

Listing 1:

private static Connection dbConnection;
  private static HashMap companyMap = new HashMap();
  private static StringBuffer tempBuffer;

  private static final String insertPortfolioCompaniesSQL;
  static{
    tempBuffer = new StringBuffer("INSERT INTO Portfolio_Com-
    panies ");
    tempBuffer.append("(portfolio_id,business_entity_id, 
    nbr_shares_held) ");
    tempBuffer.append("VALUES(?,?,?)");
    insertPortfolioCompaniesSQL = tempBuffer.toString();
  }
  private void insertPortfolioCompanies() throws SQLException {
    PreparedStatement pstmt = null;
    try {
      pstmt = dbConnection.prepareStatement(insertPortfolio-
      CompaniesSQL);
      if(companyMap.isEmpty()){
        Collection companyCollection = companyMap.values();
        for(Iterator i = companyCollection.iterator(); i.has-
        Next(); ){
          PortfolioConstituent pc =  
          (PortfolioConstituent)i.next();
          pstmt.setDouble(1,pc.getPortfolioID());
          pstmt.setDouble(2,pc.getCompanyID());
          pstmt.setDouble(3,pc.getShares());

          pstmt.execute();
        }
        pstmt.close();
        dbConnection.commit();
      }
    } finally { JDBCUtilities.close(pstmt);  }
  }


Listing 2:

static public void close (ResultSet rs) {
    try { if (rs!=null) rs.close(); } catch (Exception e) {}
    }

  //  Works for PreparedStatement also since it extends  
  //  Statement.
  static public void close (Statement stmt) {
    try { if (stmt!=null) stmt.close(); } catch (Exception e)   {}
    }

  static public void close (java.sql.Connection conn) {
    try { if (conn!=null) conn.close(); } catch (Exception e) {}
    }

  static public void close (dvt.util.db.Connection conn) {
    try { if (conn!=null) conn.close(); } catch (Exception e) {}
    }


Listing 3:

package dvt.util.db;

import java.lang.*;
import java.sql.*;
import oracle.jdbc.driver.*;

/**
 * Connection represents a generic database connection.
 * 
 * @author Derek C. Ashmore
 * @version 1.0
 *
 */

public class Connection {

    public static final String ORACLE_8I = "8I";
    public static final String CLOUDSCAPE = "CLOUDSCAPE";
    public static final String ORACLE_LITE = "ORACLE_LITE";
    public static final String GENERIC = "GENERIC"

public static final String CLOUDSCAPE_DRIVER =   
    "COM.cloudscape.core.JDBCDriver";
    public static final String ORACLE_LITE_DRIVER = 
    "oracle.lite.poljdbc.POLJDBCDriver";
    public static final String ORACLE_8I_DRIVER = 
    "oracle.jdbc.driver.OracleDriver";

    /**
     * Registers the database driver and obtains the speci-
     * fied database connection.
     *
     * @param JDBCDriver
     * @param connectString
     * @param localUserId
     * @param localPassword
     */
    public Connection(   String jdbcDriverName,
                                String connectString,
                                String localUserId,
                                String localPassword) {

        setUserId(localUserId);
        setPassword(localPassword);

        if (jdbcDriverName.equals(ORACLE_LITE_DRIVER))
            platform = ORACLE_LITE;
        else if (jdbcDriverName.equals(CLOUDSCAPE_DRIVER))
            platform = CLOUDSCAPE;
        else if (jdbcDriverName.equals(ORACLE_8i_DRIVER))
            platform = ORACLE_8i;
        else platform = GENERIC;

        registerDBDriver(jdbcDriverName);
        currentConnection = getConnection(connectString, con-
        nectString);
    }

    /**
     * Registers the database drivers.
     */

    private void registerDBDriver(String jdbcDriverName) {
        try {
            Class.forName( jdbcDriverName );
            this.setDriverRegistered( true );
        }
        catch (Exception DBError) {
            System.out.println(DBError.getMessage());
            DBError.printStackTrace();
        }
    }

    /**
     * Prepares a SQL statement.
     * 
     * @param localSQLString
     * @exception java.sql.SQLException
     */
    
    public PreparedStatement prepareStatement(String local-
    SQLString)  throws SQLException {
        return currentConnection.prepareStatement(localSQL-
        String);
    }

    /**
     * Returns the current Oracle database connection.
     */
    
    private java.sql.Connection getConnection(String jdbc-
    DriverName, String connectString) {

        if (isConnected()) return currentConnection;
        if (! isDriverRegistered())  registerDBDriver(jdbc 
        DriverName);
        
        try {
            currentConnection = DriverManager.getConnec-
            tion(connectString,
                        userId, password);
            if (platform.equals(ORACLE_8I))   {
                OracleConnection oConnect = (OracleConnec-
                tion) currentConnection;
                oConnect.setDefaultRowPrefetch( default 
                PrefetchSize );
                oConnect.setDefaultExecuteBatch( default 
                WriteBatchSize );
                Statement alterDateFormat = currentConnec-
                tion.createStatement();
                alterDateFormat.execute("alter session set    
                NLS_DATE_FORMAT = 'YYYYMMDDHHMISS'");
                }
            currentConnection.setAutoCommit( defaultAutoCom-
            mitSetting );

            this.setConnected(true);
        }
        catch (SQLException DBError) {
            System.out.println(DBError.getMessage());
            DBError.printStackTrace();   
        }
        return currentConnection;
    }

    public java.sql.Connection getConnection()   { return   
    currentConnection; }

    /**
     * Provides the user id associated with the current  
     database connection.
     */

    public String getUserId() {
        return userId;
    }

    /**
     * Sets the user id used to obtain the database connection.
     * 
     * @param localUserId
     */
    
    private void setUserId(String localUserId) {
        userId = localUserId;
    }

    /**
     * Provides the password used to obtain the database  
       connection.
     */
    
    public String getPassword() {
        return password;
    }

    /**
     * Sets the password used to obtain the database connection.
     * 
     * @param localPassword
     */
    
    private void setPassword(String localPassword) {
        password = localPassword;
    }

    /**
     * Provides information as to whether or not database  
     * driver registration has occurred.
     */
    
    private boolean isDriverRegistered() {
        return driverRegistered;
    }

    /**
     * Issues a database commit to save all pending changes 
     * to the database.
     */
    
    public void commit()    throws SQLException {
        currentConnection.commit();
    }
    
    /**
     * Issues a database rollback to abort all pending   
     * changes to the database.
     */
    
    public void rollback()    throws SQLException {
        currentConnection.rollback();
    }

    /**
     * Issues a database disconnect.
     */
    
    public void close()    throws SQLException {
        currentConnection.close();
    }

    /**
     * Issues a database disconnect and closes a given 
     * statement
     * (provided for programatic convenience -- doesn't logically
     *  belong here).
     *
     * @param PreparedStatement
     */

    public void close(PreparedStatement preparedStatement)     
    throws SQLException {
        try  {
            preparedStatement.close();
            }
        catch (SQLException e)  {
            e.printStackTrace();
            }
        this.close();
    }

    /**
     * Issues a database disconnect and closes a given  
     * statement and result set (provided for programatic 
     * convenience -- doesn't logically belong here).
     *
     * @param PreparedStatement
     * @param ResultSet
     */

    public void close(PreparedStatement preparedStatement, 
    ResultSet resultSet)    throws SQLException {
        try  {
            resultSet.close();
            }
        catch (SQLException e)  {
            e.printStackTrace();
            }
        this.close(preparedStatement);
    }

    
    /**
     * Records registration status of database drivers.
     * 
     * @param LocalDriverRegistered
     */
    
    private void setDriverRegistered(boolean LocalDriverReg- 
    istered) {
        driverRegistered = LocalDriverRegistered;
    }

    /**
     * Provides information about current database connection  
     * status.
     */
    
    public boolean isConnected() {
        return connected;
    }

    /**
     * Sets database connection status information.
     * 
     * @param LocalConnected
     */
    
    private void setConnected(boolean LocalConnected) {
        connected = LocalConnected;
    }

    /**
     * Sets the array size used for select statements.
     * 
     * @param arraySize
     */

    public void setPrefetchSize(int arraySize) throws SQLEx-
    ception {
      if (platform.equals(ORACLE_8i))  {
        OracleConnection oConnection = (OracleConnection)    
        currentConnection;
        oConnection.setDefaultRowPrefetch( arraySize );
        }
    }

    /**
     * Provides the array size used for select statements.
     *
     */

    public int getPrefetchSize() throws SQLException {
      if (platform.equals(ORACLE_8i))  {
        OracleConnection oConnection = (OracleConnection)   
        currentConnection;
        return oConnection.getDefaultRowPrefetch();
        }
      else return -1;
    }

    /**
     * Sets the array size used for update, insert, and   
     * delete statements.
     *
     * @param arraySize
     */

    public void setWriteBatchSize(int arraySize) throws   
    SQLException {
      if (platform.equals(ORACLE_8i))  {
        OracleConnection oConnection = (OracleConnection)   
        currentConnection;
        oConnection.setDefaultExecuteBatch( arraySize );
        }
    }

    /**
     * Provides the array size used for update, insert, and  
     * delete statements.
     *
     */

    public int getWriteBatchSize() throws SQLException {
      if (platform.equals(ORACLE_8i))  {
        OracleConnection oConnection = (OracleConnection)    
        currentConnection;
        return oConnection.getDefaultExecuteBatch();
        }
      else return -1;
    }

    /**
     * Sets the AutoCommit specification for the connection.    
     * Set to true to
     * have commits automatically issued.  Set to false to   
     * handle commits
     * and rollbacks manually.
     *
     * @param autoCommitInd
     */

    public void setAutoCommitSetting(boolean autoCommitInd)   
    throws SQLException {
        currentConnection.setAutoCommit( autoCommitInd );
    }

    /**
     * Provides the AutoCommit specification for the connec-
     * tion.  True means that commits are automatically 
     * issued.  False means that commits and rollbacks are 
     * handled manually.
     *
     */

    public boolean getAutoCommitSetting() throws SQLException {
        return currentConnection.getAutoCommit();
    }

    /**
     * Provides database platform used for the connection.
     */
    public String getPlatform()   {  return platform; }

    /**
     * User ID used to obtain database connection.
     */
    private String userId;

    /**
     * Password used to obtain database connection.
     */
    private String password;

    /**
     * Prefetch size (select array processing batch size) 
     * for database connection.
     */
    private int defaultPrefetchSize = 100;

    /**
     * Default update Batch size (number of write DML state
     * ments to queue) for database connection.
     */
    private int defaultWriteBatchSize = 20;

    /**
     * Auto Commit mode for database connection.
     */

     private boolean defaultAutoCommitSetting = false;

    /**
     * Indicates database driver registration status.
     */
    private boolean driverRegistered;

    /**
     * Indicates last known database connection status.
     */
    private boolean connected;

    /**
     * Contains JDBC connection information.
     */
    private java.sql.Connection currentConnection = null;

    /**
     * Contains JDBC connection information.
     */
    private String platform = null;
}



 

All Rights Reserved
Copyright ©  2004 SYS-CON Media, Inc.
  E-mail: [email protected]

Java and Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries. SYS-CON Publications, Inc. is independent of Sun Microsystems, Inc.