Ok let me give this a stab, this might help you....
Please find the attached files, not sure if the attached file's makes its
way to the list....
SampleCode = Sample code, that gets the connection etc
DBConnectionManager = manages the connection pool
DBUtil = DB utility
System.properties = configuration
DBoptions = DB options etc
All you have to do is change the system.properties to fit your need and
compile.
-----Original Message-----
From: Scott Purcell [mailto:[EMAIL PROTECTED]
Sent: Friday, April 15, 2005 4:56 PM
To: user@struts.apache.org
Subject: Database Assistance Needed
I have had some issues this past week, trying to come up with a way to
cleanly connect to my, MySQL database. I know this is not necessarily a
struts issue, but I am betting that there is no one on this list who is not
using some type of database in the back-end.
Now I have the O'Reilly book on Struts, and using the ObjectRelationalBridge
is a little too large for me to take on currently, same as Hibernate or
anything else I would have to research thoroughly. I just need a solid,
simple way to grab a connection from a pool, use it in a Business Object and
call it a day. Since I am running on Tomcat 5.5, I have tried to
incorporate the DBCP from jakarta into my struts stuff. Problem is most
examples do not work, or are incomplete for the 5.5 Tomcat, and I cannot
find any decent examples of doing this.
I am basically Running Mysql, and Tomcat 5.5, and struts 1.2. I really do
not want to use the data-source in struts, as I intend to use a solution
that will not be depreciated in the next release. Could anyone throw me a
bone here. I have searched google to death for good examples, but come up
with outdated examples, or incomplete. The examples for Tomcat make you use
JNDI, and I am not sure if that is the way to go.
Any assistance would be sincerely appreciated.
Thanks,
Scott
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
public boolean isMemberAvailableByUserName(String memberUserName) throws
SQLException {
boolean memberPresent = false;
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
StringBuffer sql = new StringBuffer(512);
sql.append("SELECT MemberUserName");
sql.append(" FROM " + TABLE_NAME);
sql.append(" WHERE MemberUserName = ?");
try {
connection = DBUtils.getConnection();
statement = connection.prepareStatement(sql.toString());
statement.setString(1, memberUserName);
resultSet = statement.executeQuery();
if (resultSet.next()) {
memberPresent = true;
}
}
catch (SQLException sqle) {
sqle.printStackTrace();
logger.fatal("Error executing " + sql + memberUserName);
}
finally {
DBUtils.closeResultSet(resultSet);
DBUtils.closeStatement(statement);
DBUtils.closeConnection(connection);
}
return memberPresent;
}
import java.sql.*;
import java.util.*;
import org.apache.commons.logging.*;
/**
* DBConnectionManager
*
* This class is a Singleton that provides access to the
* connection pool. A client gets access to the single
* instance through the static getInstance() method
* and can then check-out and check-in connections from a pool.
* When the client shuts down it should call the release() method
* to close all opened connections and do other clean up.
*/
public class DBConnectionManager {
/**
* Logger
*/
private static Log logger = LogFactory.getLog(DBConnectionManager.class);
/**
* TIME_BETWEEN_RETRIES
*/
private static final int TIME_BETWEEN_RETRIES = 500; // O.5 second
/**
* DBConnectionManager instance
*/
static private DBConnectionManager instance = null; // The single instance
/**
* DBConnectionPool pool
*/
private DBConnectionPool pool = null;// please be careful if u want to make this variable static
/**
* A private constructor since this is a Singleton Note: This constructor is
* lightweight since DBConnectionPool is lightweight, so no connection is
* created until the first time getConnection() is called
*
* @param option DBOptions
*/
private DBConnectionManager(DBOptions option) {
try {
Class.forName(option.driverClassName).newInstance();
} catch (Exception e) {
logger.fatal("DBConnectionManager: Unable to load driver = " + option.driverClassName);
}
//if (pool == null) {//uncomment since pool is an instance variable
pool = new DBConnectionPool(option.databaseURL, option.databaseUser, option.databasePassword, option.maxConnection);
//}
}
/**
* Returns the single instance, creating one if it's the
* first time this method is called.
*
* @return DBConnectionManager The single instance.
*/
static synchronized public DBConnectionManager getInstance() {
if (instance == null) {
DBOptions option = new DBOptions();
instance = new DBConnectionManager(option);
}
return instance;
}
/**
* Returns the single instance, creating one if it's the first time this
* method is called.
*
* @return DBConnectionManager The single instance.
* @param option DBOptions
*/
static synchronized public DBConnectionManager getInstance(DBOptions option) {
if (instance == null) {
if (option == null) {
option = new DBOptions();
}
instance = new DBConnectionManager(option);
}
return instance;
}
/**
* Returns a connection to the pool.
*
* @throws SQLException
* @param con Connection
*/
public void freeConnection(Connection con) throws SQLException {
pool.freeConnection(con);
}
/**
* Returns an open connection. If no one is available, and the max number of
* connections has not been reached, a new connection is created.
*
* @return Connection The connection or null
* @throws SQLException
*/
public Connection getConnection() throws SQLException {
return pool.getConnection();
}
/**
* Returns an open connection. If no one is available, and the max number of
* connections has not been reached, a new connection is created. If the max
* number has been reached, waits until one is available or the specified
* time has elapsed.
*
* @param time The number of milliseconds to wait
* @return Connection The connection or null
* @throws SQLException
*/
public
/*synchronized*/ Connection getConnection(long time) throws SQLException {
return pool.getConnection(time);
}
/**
* Closes all open connections.
*/
public void release() {
pool.release();
}
/**
* This inner class represents a connection pool. It creates new
* connections on demand, up to a max number if specified.
* It also checks to make sure that the connection is still open
* before it is returned to a client.
*/
class DBConnectionPool {
private int checkedOut = 0;
private Vector freeConnections = new Vector();
private int maxConn = 0;
private String password = null;
private String url = null;
private String user = null;
/**
* Creates new connection pool.
* NOTE: new an instance of this class is lightweight since it does not create any connections
*
* @param url The JDBC URL for the database
* @param user The database user, or null
* @param password The database user password, or null
* @param maxConn The maximal number of connections, or 0 for no limit
*/
public DBConnectionPool(String url, String user, String password, int maxConn) {
this.url = url;
this.user = user;
this.password = password;
this.maxConn = maxConn;
}
/**
* Checks in a connection to the pool. Notify other Threads that
* may be waiting for a connection.
*
* @todo: Maybe we dont need notifyAll(); ???
*
* @param con The connection to check in
*/
synchronized void freeConnection(Connection con) {
// Put the connection at the end of the Vector
if (con != null) { //make sure that the connection is not null
// note that we dont have to check if the connection is not connected
// this will be check in the getConnection method
freeConnections.addElement(con);
// FIXME: posible negative value
checkedOut--; // NOTE: this number can be negative (in case connection does not come from the pool)
notifyAll(); // can I remove it ???
}
}
/**
* Checks out a connection from the pool. If no free connection is
* available, a new connection is created unless the max number of
* connections has been reached. If a free connection has been closed by
* the database, it's removed from the pool and this method is called
* again recursively.
*
* @throws SQLException
* @return Connection
*/
synchronized Connection getConnection() throws SQLException{
Connection con = null;
while ( (freeConnections.size() > 0) && (con == null) ) {
// Pick the first Connection in the Vector
// to get round-robin usage
con = (Connection) freeConnections.firstElement();
freeConnections.removeElementAt(0);
try {
if (con.isClosed()) {
logger.info("Removed bad connection in DBConnectionPool.");
con = null; // to make the while loop to continue
}
} catch (SQLException e) {
con = null; // to make the while loop to continue
}
} // while
if (con == null) {// cannot get any connection from the pool
if (maxConn == 0 || checkedOut < maxConn) {// maxConn = 0 means unlimited connections
try{
con = newConnection();
}
catch(SQLException ex){
logger.fatal("Unable to connect the Database.");
throw new SQLException("Unable to connect the Database.");
}
}
}
if (con != null) {
checkedOut++;
}
return con;
}
/**
* Checks out a connection from the pool. If no free connection
* is available, a new connection is created unless the max
* number of connections has been reached. If a free connection
* has been closed by the database, it's removed from the pool
* and this method is called again recursively.
* <P>
* If no connection is available and the max number has been
* reached, this method waits the specified time for one to be
* checked in. </P>
*
* Note that this method is not synchronized since it relies on the
* getConnection(void) method I also believe that this method SHOULD NOT
* synchronized because I use #sleep() method
*
* @todo: check if we should synchronize this method and use wait
* instead of sleep ???
*
* @param timeout long The timeout value in milliseconds
* @throws SQLException
* @return Connection
*/
Connection getConnection(long timeout) throws SQLException{
long startTime = System.currentTimeMillis();
Connection con;
while ((con = getConnection()) == null) {
long elapsedTime = System.currentTimeMillis() - startTime;
if (elapsedTime >= timeout) {
// Timeout has expired
return null;
}
long timeToWait = timeout - elapsedTime;
if (timeToWait > TIME_BETWEEN_RETRIES){
timeToWait = TIME_BETWEEN_RETRIES;
// we dont want to wait for more than TIME_BETWEEN_RETRIES second each time
}
try {
Thread.sleep(timeToWait);
} catch (InterruptedException e) {}
}
return con;
}
/**
* Closes all available connections.
*/
synchronized void release() {
Enumeration allConnections = freeConnections.elements();
while (allConnections.hasMoreElements()) {
Connection con = (Connection) allConnections.nextElement();
try {
con.close();
} catch (SQLException e) {
logger.error("Can't close connection in DBConnectionPool.");
}
}
freeConnections.removeAllElements();
}
/**
* Creates a new connection, using a userid and password if specified.
*
* @todo: check if this method need synchronized
* @throws SQLException
* @return Connection
*/
private Connection newConnection() throws SQLException{
Connection con = null;
try {
if (user == null) {
con = DriverManager.getConnection(url);
} else {
con = DriverManager.getConnection(url, user, password);
}
} catch (SQLException e) {
logger.error("Can't create a new connection in DBConnectionPool. URL = " + url, e);
throw new SQLException("Unable to connect to the Database");
// return null;
}
return con;
}
}
}
import java.sql.*;
import javax.sql.DataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
/**
* DBUtils
*
*/
public final class DBUtils {
/**
* Log logger
*/
private static Log logger = LogFactory.getLog(DBUtils.class);
/**
* DATABASE_UNKNOWN
*/
public static final int DATABASE_UNKNOWN = 0;
/**
* DATABASE_GENERAL
*/
public static final int DATABASE_GENERAL = 1;
/**
* DATABASE_NOSCROLL
*/
public static final int DATABASE_NOSCROLL = 2;
/**
* DATABASE_ORACLE
*/
public static final int DATABASE_ORACLE = 10;
/**
* int DATABASE_SQLSERVER
*/
public static final int DATABASE_SQLSERVER = 11;
/**
* int DATABASE_DB2
*/
public static final int DATABASE_DB2 = 12;
/**
* int DATABASE_SYBASE
*/
public static final int DATABASE_SYBASE = 13;
/**
* int DATABASE_IMFORMIX
*/
public static final int DATABASE_IMFORMIX = 14;
/**
* int DATABASE_MYSQL
*/
public static final int DATABASE_MYSQL = 15;
/**
* int DATABASE_POSTGRESQL
*/
public static final int DATABASE_POSTGRESQL = 16;
/**
* int DATABASE_HSQLDB
*/
public static final int DATABASE_HSQLDB = 17;
/**
* int DATABASE_ACCESS
*/
public static final int DATABASE_ACCESS = 18;
/**
* int databaseType
*/
private static int databaseType = DATABASE_UNKNOWN;
/**
* boolean useDatasource
*/
private static boolean useDatasource = false;
/**
* int maxTimeToWait
*/
private static int maxTimeToWait = 2000;// 2 seconds
/**
* DBConnectionManager connectionManager
*/
private static DBConnectionManager connectionManager = null;
/**
* DataSource dataSource
*/
private static DataSource dataSource = null;
// static init of the class
static {
DBOptions option = new DBOptions();
if (option.useDatasource) {
useDatasource = true;
try {
javax.naming.Context context = new javax.naming.InitialContext();
// sample data source = java:comp/env/jdbc/MysqlDataSource
dataSource = (DataSource) context.lookup(option.datasourceName);
logger.info("DBUtils : use datasource = " + option.datasourceName);
} catch (javax.naming.NamingException e) {
logger.error("Cannot get DataSource: datasource name = " + option.datasourceName, e);
}
} else {
useDatasource = false;
maxTimeToWait = option.maxTimeToWait;
connectionManager = DBConnectionManager.getInstance(option);
logger.info("DBUtils : use built-in DBConnectionManager (maxTimeToWait = " + maxTimeToWait + ")");
}
}
/**
* contructor
*/
private DBUtils() {// so cannot new an instance
}
/**
* Use this method to get the database type. This method will automatically
* detect the database type. You could override this value by modifying
* the value in system.properties
* @return : the database type
*/
public static int getDatabaseType() {
if (databaseType == DATABASE_UNKNOWN) {
Connection connection = null;
try {
connection = DBUtils.getConnection();
DatabaseMetaData dbmd = connection.getMetaData();
String databaseName = dbmd.getDatabaseProductName().toLowerCase();
if (databaseName.indexOf("oracle") != -1) {
databaseType = DATABASE_ORACLE;
} else if (databaseName.indexOf("sql server") != -1) {
databaseType = DATABASE_SQLSERVER;
} else if (databaseName.indexOf("mysql") != -1) {
databaseType = DATABASE_MYSQL;
} else if (databaseName.indexOf("postgresql") != -1) {
databaseType = DATABASE_POSTGRESQL;
} else if (databaseName.indexOf("hsql") != -1) {
databaseType = DATABASE_HSQLDB;
} else {
databaseType = DATABASE_GENERAL;
}
} catch (Exception ex) {
logger.error("Error when running getDatabaseType", ex);
} finally {
DBUtils.closeConnection(connection);
}
}
return databaseType;
}
/**
* Get a connection from the connection pool. The returned connection
* must be closed by calling DBUtils.closeConnection()
*
* @return Connection a new connection from the pool if succeed
* @throws SQLException : if cannot get a connection from the pool
*/
public static Connection getConnection() throws SQLException {
Connection conection = null;
if (useDatasource) {
if (dataSource != null) {
conection = dataSource.getConnection();
}
} else {
if (connectionManager != null) {
conection = connectionManager.getConnection(maxTimeToWait);
} else {
logger.fatal("Assertion: DBUtils.connectionManager == null");
}
}
if (conection == null) {
throw new SQLException("DBUtils: Cannot get connection from Connection Pool.");
}
return conection;
}
/**
* Use this method to return the connection to the connection pool
* Do not use this method to close connection that is not from
* the connection pool
* @param connection : the connection that needs to be returned to the pool
*/
public static void closeConnection(Connection connection) {
if (connection == null){
return;
}
if (useDatasource) {
try {
connection.close();
} catch (SQLException e) {
logger.error("DBUtils: Cannot close connection.", e);
}
} else {
try {
connectionManager.freeConnection(connection);
} catch (SQLException ex) {
//
}
}
}
/**
* Use this method to reset the MaxRows and FetchSize of the Statement
* to the default values
* @param statement : the statement that needs to be reseted
*/
public static void resetStatement(Statement statement) {
if (statement != null) {
try {
statement.setMaxRows(0); //reset to the default value
} catch (SQLException e) {
logger.error("DBUtils: Cannot reset statement MaxRows.", e);
}
try {
statement.setFetchSize(0); //reset to the default value
} catch (SQLException sqle) {
//do nothing, postgreSQL doesnt support this method
}
}
}
/**
* Use this method to close the Statement
* @param statement : the statement that needs to be closed
*/
public static void closeStatement(Statement statement) {
try {
if (statement != null){
statement.close();
}
} catch (SQLException e) {
logger.error("DBUtils: Cannot close statement.", e);
}
}
/**
* Use this method to close the ResultSet
* @param rs : the resultset that needs to be closed
*/
public static void closeResultSet(ResultSet rs) {
try {
if (rs != null){
rs.close();
}
} catch (SQLException e) {
logger.error("DBUtils: Cannot close resultset.", e);
}
}
/*
public static void main(String[] args) {
//DBUtils DBUtils1 = new DBUtils();
//log.info("i = " + dataSource1);
}*/
}
import java.util.ResourceBundle;
import java.util.MissingResourceException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
/**
* DBOptions
*
*/
class DBOptions {
private static Log logger = LogFactory.getLog(DBOptions.class);
properties = new Properties();
InputStream is = null;
try {
is = getClass().getResourceAsStream("system.properties");
properties.load(is);
logger.info("Loaded the Property file... system.properties");
}
catch (Exception exception) {
logger.fatal("Can't read the properties file. /system.properties");
}
finally {
try {
if (is != null) {
is.close();
}
}
catch (IOException exception) {
// ignored
}
}
//default values
boolean useDatasource = false;
// MUST NOT refer to DBUtils, not we will get an infinite recurse
int databaseType = 0; //DATABASE_UNKNOWN
// if useDatasource = true
String datasourceName = "";
// if useDatasource = false
String driverClassName = "org.gjt.mm.mysql.Driver";
String databaseURL = "jdbc:mysql://localhost/test";
String databaseUser = "user";
String databasePassword = "password";
int maxConnection = 20;
int maxTimeToWait = 2000;// 2 seconds
DBOptions() {
try {
String strUseDatasource = "false";
try {
strUseDatasource = cfg.getValue("USE_DATASOURCE").trim();
} catch (MissingResourceException ex) {
// ignore exception
}
try {
databaseType = Integer.parseInt(cfg.getValue("DATABASE_TYPE").trim());
} catch (Exception ex) {
logger.error("Fail to read DATABASE_TYPE, use default value = " + databaseType);
}
if (strUseDatasource.equals("true")) {
useDatasource = true;
datasourceName = cfg.getValue("DATASOURCE_NAME").trim();
} else {
useDatasource = false;
driverClassName = cfg.getValue("DRIVER_CLASS_NAME").trim();
databaseURL = cfg.getValue("DATABASE_URL").trim();
databaseUser = cfg.getValue("DATABASE_USER").trim();
databasePassword = cfg.getValue("DATABASE_PASSWORD").trim();
try {
maxConnection = Integer.parseInt(cfg.getValue("DATABASE_MAXCONNECTION").trim());
} catch (Exception ex) {
logger.error("Fail to read DATABASE_MAXCONNECTION, use default value = " + maxConnection, ex);
}
try {
maxTimeToWait = Integer.parseInt(cfg.getValue("MAX_TIME_TO_WAIT").trim());
} catch (Exception ex) {
logger.error("Fail to read MAX_TIME_TO_WAIT, use default value = " + maxTimeToWait, ex);
}
}
} catch (Exception e) {
String message = "system.properties: Can't read the properties file. Make sure the file is in your CLASSPATH";
logger.error(message, e);
}
}//constructor
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
