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;
}
No comments:
Post a Comment