Copy the code code as follows:
package com.groundhog.codingmouse;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* Database management class
* @author CodingMouse
*2009.2.20
*/
public final class DBManager {
/**
* Database connection object
*/
private Connection dbConnection = null;
/**
* Database command execution object
*/
private PreparedStatement preStatement = null;
/**
* Result set object
*/
private ResultSet rsSet = null;
/**
* Database driver version number
*/
private static String driverVersion = null;
/**
* Database server login username and password string constants (default values are 'sa')
*/
private static String databaseUser = "sa";
private static String databasePassword = "sa";
/**
* Database driver complete class name string constant
*/
private static final String
DRIVER_CLASS_SQLSERVER2000 =
"com.microsoft.jdbc.sqlserver.SQLServerDriver"; // SQL
Server 2000 direct connection
private static final String
DRIVER_CLASS_SQLSERVER2005 =
"com.microsoft.sqlserver.jdbc.SQLServerDriver"; // SQL
Server 2005 Direct Connect
private static final String
DRIVER_CLASS_BRIDGECONNECT = "sun.jdbc.odbc.JdbcOdbcDriver";
// ODBC bridge connection
/**
* Database connection string constants
*/
private static final String
DATABASE_URL_SQLSERVER2000 =
"jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=stuD
B"; // SQL Server 2000 direct connection
private static final String
DATABASE_URL_SQLSERVER2005 =
"jdbc:sqlserver://127.0.0.1:1433;DatabaseName=stuDB";
// SQL Server 2005 direct connection
private static final String
DATABASE_URL_BRIDGECONNECT = "jdbc:odbc:stuDBSource";
// ODBC bridge connection
/**
* Define instance static variables of the class itself (applicable to singleton [ware] mode applications)
*/
private static DBManager connectionManager = null;
/**
* Privatized default construction (applies to singleton [ware] mode applications to prevent classes from being instantiated directly using the new keyword)
*/
privateDBManager() {
super();
}
/**
* Method to obtain database connection management class instance (application of singleton [ware] mode)
* @param version Database driver version number, value: (version =
2000 | version = 2005 | version = odbc)
* @param user database server login user name
* @param password database server login password
* @return database connection management object
* @throws Exception Parameter error exception
*/
public static DBManager getInstance(
String version,
String user,
String password)
throws Exception {
if (!(version == "2000" || version == "2005"
|| version == "odbc")) {
throw new Exception("The database driver version number is incorrect, the value can only be "2000/2005/odbc"!");
}
//Save the database driver version number
driverVersion = version;
if (user == null || user.equals("")) {
throw new Exception("Database server login username cannot be empty!");
}
//Save database server login user name and password
databaseUser = user;
databasePassword = password;
// Apply the singleton [ware] pattern to ensure that the class itself has only one instance
if (connectionManager == null) {
connectionManager = new DBManager();
}
// Return an instance of the class itself
return connectionManager;
}
/**
* Method to obtain database connection
* @return database connection object
*/
private Connection getConnection() {
try {
Class.forName(
driverVersion ==
"2000"
?
DRIVER_CLASS_SQLSERVER2000
: (driverVersion ==
"2005"
?
DRIVER_CLASS_SQLSERVER2005
:
DRIVER_CLASS_BRIDGECONNECT));
this.dbConnection =
DriverManager.getConnection(
driverVersion ==
"2000"
?
DATABASE_URL_SQLSERVER2000
: (driverVersion ==
"2005"
?
DATABASE_URL_SQLSERVER2005
:
DATABASE_URL_BRIDGECONNECT),
databaseUser,
databasePassword);
} catch (ClassNotFoundException ex) {
System.err.println("SQL Server not found
" + driverVersion + "Database driver class: " + ex.getMessage());
//Output exception stack information on the console
// ex.printStackTrace();
} catch (Exception ex) {
System.err.println("Get database connection error: " + ex.getMessage());
//Output exception stack information on the console
// ex.printStackTrace();
}
// Return the database connection object
return this.dbConnection;
}
/**
* Method to obtain database command execution object
* @param sql SQL command assembly statement string to be executed
* @return database command execution object
*/
private PreparedStatement getPreparedStatement
(String sql) {
try {
//Create a database command execution object based on the obtained database connection object
this.preStatement = getConnection
().prepareStatement(sql);
} catch (Exception ex) {
System.err.println("Error in obtaining database command execution object: " + ex.getMessage());
//Output exception stack information on the console
// ex.printStackTrace();
}
// Return the database command execution object
return this.preStatement;
}
/**
* Execute update statement (Insert|Update|Delete)
* @param sql SQL command assembly statement string to be executed
* @return the number of affected rows
*/
public int executeUpdate(String sql){
try {
// Empty the original content of the result set object
this.rsSet = null;
//Execute the statement and return the number of affected rows
return this.getPreparedStatement
(sql).executeUpdate();
} catch (SQLException e) {
System.err.println("Error updating data:" +
e.getMessage());
return 0;
}finally{
//Close database connection resources
closeDBResource();
}
}
/**
* Execute query statement (Select)
* @param sql SQL command assembly statement string to be executed
* @return result set object after query
*/
public ResultSet executeQuery(String sql){
try {
// Empty the original content of the result set object
this.rsSet = null;
//Execute sql statement to obtain the result set
this.rsSet =
this.getPreparedStatement(sql).executeQuery();
} catch (SQLException e) {
System.err.println("Query data error:" +
e.getMessage());
}
// Return the result set object
return this.rsSet;
}
/**
* Get the number of records in the result set returned after executing the specified SQL statement
* @param sql SQL command assembly statement string to be executed
* @return The number of records obtained from the query results
*/
public int getResultSetCount(String sql) {
//Save the counter variable that returns the number of rows recorded after the specified SQL statement is executed.
int count = 0;
try {
// Empty the original content of the result set object
this.rsSet = null;
//Execute sql statement to obtain the result set
this.rsSet = this.getPreparedStatement
(sql).executeQuery();
// Iterate through the result set and accumulate the counter
while (this.rsSet.next()) {
count++;
}
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}
/**
* Close database connection resources (including result set objects, command execution objects, and connection objects)
*/
public void closeDBResource() {
try {
closeResultSet();
closePreparedStatement();
closeConnection();
} catch (SQLException sqlEx) {
System.err.println(sqlEx.getMessage
());
//Output exception stack information on the console
// sqlEx.printStackTrace();
}
}
/**
* Method to close the result set object
* @throws SQLException
*/
private void closeResultSet() throws SQLException {
try {
if (this.rsSet != null) {
this.rsSet.close();
this.rsSet = null;
}
} catch (SQLException sqlEx) {
throw new SQLException("Error closing result set object: " + sqlEx.getMessage());
//Output exception stack information on the console
// sqlEx.printStackTrace();
}
}
/**
* Method to close database command execution object
* @throws SQLException
*/
private void closePreparedStatement() throws
SQLException {
try {
if (this.preStatement != null) {
this.preStatement.close();
this.preStatement = null;
}
} catch (SQLException sqlEx) {
throw new SQLException("Close database command execution object error: " + sqlEx.getMessage());
//Output exception stack information on the console
// sqlEx.printStackTrace();
}
}
/**
* Method to close database connection
* @throws SQLException
*/
private void closeConnection() throws SQLException {
try {
if (this.dbConnection != null && (!
this.dbConnection.isClosed())) {
this.dbConnection.close();
}
} catch (SQLException sqlEx) {
throw new SQLException("Error closing database connection: " + sqlEx.getMessage());
//Output exception stack information on the console
// sqlEx.printStackTrace();
}
}
}
