package net.sf.xbus.technical.database;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Hashtable;
import java.util.List;
import java.util.Vector;
import net.sf.xbus.base.core.Constants;
import net.sf.xbus.base.core.TAManager;
import net.sf.xbus.base.core.TAResource;
import net.sf.xbus.base.core.XException;
import net.sf.xbus.base.core.config.Configuration;
import net.sf.xbus.base.core.reflection.ReflectionSupport;
/**
* <code>DBConnection</code> manages connections to databases.
* <p>
*
* It implements two Design-Patterns:
* <ol>
* <li><b>Singleton: </b> An instance of <code>DBConnection</code> is created
* for every thread. This instance can be fetched with
* <code>getInstance()</code>.</li>
* <li><b>Facade: </b> The complexity of creating and managing a connection is
* capsuled.</li>
* </ol>
*/
public class DBConnection implements TAResource
{
public static final String UNNAMED = "UNNAMED";
private static Hashtable mDBConnections = new Hashtable();
private static final Object classLock = DBConnection.class;
private Hashtable mPreparedStatements = new Hashtable();
private StringBuffer mDBUrl = null;
private Connection mConnection = null;
private boolean mIsOpen = false;
private String mName = null;
String mConfigChapter = null;
String mConfigSection = null;
/**
* The constructor is private, instances of <code>DBConnection</code> can
* only be generated by the method <code>getInstance()</code>. Each
* instance is put in a <code>Hashtable</code> with the name of the thread
* as the key.
*/
private DBConnection(String name) throws XException
{
mName = name;
if (UNNAMED.equals(mName))
{
mConfigChapter = "Connection";
mConfigSection = "Database";
}
else
{
mConfigChapter = "DBConnection";
mConfigSection = mName;
}
Configuration config = Configuration.getInstance();
String driver = config.getValue(mConfigChapter, mConfigSection,
"Driver");
String url = config.getValueOptional(mConfigChapter, mConfigSection,
"URL");
mDBUrl = new StringBuffer();
if (url != null)
{
mDBUrl.append(url);
}
else
{
String prefix = config.getValue(mConfigChapter, mConfigSection,
"URLPrefix");
String host = config.getValueOptional(mConfigChapter,
mConfigSection, "Host");
String port = config.getValueOptional(mConfigChapter,
mConfigSection, "Port");
String database = config.getValueOptional(mConfigChapter,
mConfigSection, "Database");
mDBUrl.append(prefix);
if (host != null)
{
mDBUrl.append("://").append(host);
}
if (port != null)
{
mDBUrl.append(":").append(port);
}
if (database != null)
{
mDBUrl.append("/").append(database);
}
}
ReflectionSupport.classForName(driver);
open();
mDBConnections.put(getFullName(mName), this);
/*
* DBConnection will only be registered in TAManager when AutoCommit =
* false
*/
if (!config.getValueAsBoolean(mConfigChapter, mConfigSection,
"AutoCommit"))
{
TAManager.getInstance().registerResource(this);
}
}
/**
* Delivers an instance of <code>DBConnection</code>.
* <p>
*
* If it is the first call for the actual thread, a new
* <code>DBConnection</code> -object gets created. Subsequent calls in
* this thread will deliver the object, that has been created by the first
* call.
*/
public static DBConnection getInstance(String name) throws XException
{
synchronized (classLock)
{
DBConnection dbConnection = (DBConnection) mDBConnections
.get(getFullName(name));
if (dbConnection == null)
{
dbConnection = new DBConnection(name);
}
return dbConnection;
}
}
/**
* Opens the connection to the database.
*/
public void open() throws XException
{
if (!mIsOpen)
{
try
{
Configuration config = Configuration.getInstance();
String user = config.getValueOptional(mConfigChapter,
mConfigSection, "User");
if (user != null)
{
String password = config.getValue(mConfigChapter,
mConfigSection, "Password");
mConnection = DriverManager.getConnection(
mDBUrl.toString(), user, password);
}
else
{
mConnection = DriverManager
.getConnection(mDBUrl.toString());
}
mConnection.setAutoCommit(config.getValueAsBoolean(
mConfigChapter, mConfigSection, "AutoCommit"));
}
catch (SQLException e)
{
throw new XException(Constants.LOCATION_EXTERN,
Constants.LAYER_TECHNICAL,
Constants.PACKAGE_TECHNICAL_DATABASE, "0", e);
}
mIsOpen = true;
}
}
public void close() throws XException
{
if (mIsOpen)
{
try
{
mConnection.close();
}
catch (SQLException e)
{
throw new XException(Constants.LOCATION_EXTERN,
Constants.LAYER_TECHNICAL,
Constants.PACKAGE_TECHNICAL_DATABASE, "0", e);
}
}
}
/**
* Commits all actions on the database.
*/
public void commit() throws XException
{
try
{
if (!mConnection.getAutoCommit())
{
mConnection.commit();
}
}
catch (SQLException e)
{
throw new XException(Constants.LOCATION_EXTERN,
Constants.LAYER_TECHNICAL,
Constants.PACKAGE_TECHNICAL_DATABASE, "0", e);
}
}
/**
* Performs a rollback for all actions on the database.
*/
public void rollback() throws XException
{
try
{
if (!mConnection.getAutoCommit())
{
mConnection.rollback();
}
}
catch (SQLException e)
{
throw new XException(Constants.LOCATION_EXTERN,
Constants.LAYER_TECHNICAL,
Constants.PACKAGE_TECHNICAL_DATABASE, "0", e);
}
}
/**
* Reopens a database connection after an error has been detected. All
* internal variables are initialized.
*/
public void reopen() throws XException
{
mIsOpen = false;
mPreparedStatements.clear();
open();
}
/**
* Executes a SQL statement to read data.
*
* @param statement SQL statement to read data
* @return set of read rows
* @throws XException if something goes wrong
*/
public ResultSet executeRead(String statement) throws XException
{
Configuration config = Configuration.getInstance();
Statement stmt = null;
try
{
stmt = mConnection.createStatement();
int maxRows = config.getValueAsInt(mConfigChapter, mConfigSection,
"MaxRows");
if (maxRows >= 0)
{
stmt.setMaxRows(maxRows);
}
ResultSet results = stmt.executeQuery(statement);
return results;
}
catch (SQLException e)
{
throw new XException(Constants.LOCATION_EXTERN,
Constants.LAYER_TECHNICAL,
Constants.PACKAGE_TECHNICAL_DATABASE, "0", e);
}
}
/**
* Executes an update, insert or delete statement.
*
* @param statement SQL statement to update, insert or delete rows
* @return number of rows affected
* @throws XException if something goes wrong
*/
public int executeUpdate(String statement) throws XException
{
Statement stmt = null;
int result;
try
{
stmt = mConnection.createStatement();
result = stmt.executeUpdate(statement);
}
catch (SQLException e)
{
throw new XException(Constants.LOCATION_EXTERN,
Constants.LAYER_TECHNICAL,
Constants.PACKAGE_TECHNICAL_DATABASE, "0", e);
}
finally
{
if (stmt != null)
{
try
{
stmt.close();
}
catch (SQLException e1)
{
// do nothing
}
}
}
return result;
}
/**
* Stores the given SQL statement as a prepared statement under the given
* name.
*/
public void prepareStatement(Object name, String statement)
throws XException
{
try
{
PreparedStatement pStatement = mConnection
.prepareStatement(statement);
mPreparedStatements.put(name, pStatement);
}
catch (SQLException e)
{
throw new XException(Constants.LOCATION_EXTERN,
Constants.LAYER_TECHNICAL,
Constants.PACKAGE_TECHNICAL_DATABASE, "0", e);
}
}
/**
* Binds a string value to a parameter in a stored prepared statement.
*
* @param name the name under which the prepared statement is stored
* @param pos position of the parameter in the prepared statement
* @param value value that will be bound to the parameter
*/
public void bind(Object name, int pos, String value) throws XException
{
try
{
PreparedStatement pStatement = (PreparedStatement) mPreparedStatements
.get(name);
if (pStatement == null)
{
List params = new Vector();
params.add(name);
throw new XException(Constants.LOCATION_EXTERN,
Constants.LAYER_TECHNICAL,
Constants.PACKAGE_TECHNICAL_DATABASE, "7", params);
}
pStatement.setString(pos, value);
}
catch (SQLException e)
{
throw new XException(Constants.LOCATION_EXTERN,
Constants.LAYER_TECHNICAL,
Constants.PACKAGE_TECHNICAL_DATABASE, "0", e);
}
}
/**
* Binds an integer value to a parameter in a stored prepared statement.
*
* @param name the name under which the prepared statement is stored
* @param pos position of the parameter in the prepared statement
* @param value value that will be bound to the parameter
*/
public void bind(Object name, int pos, int value) throws XException
{
try
{
PreparedStatement pStatement = (PreparedStatement) mPreparedStatements
.get(name);
if (pStatement == null)
{
List params = new Vector();
params.add(name);
throw new XException(Constants.LOCATION_EXTERN,
Constants.LAYER_TECHNICAL,
Constants.PACKAGE_TECHNICAL_DATABASE, "7", params);
}
pStatement.setInt(pos, value);
}
catch (SQLException e)
{
throw new XException(Constants.LOCATION_EXTERN,
Constants.LAYER_TECHNICAL,
Constants.PACKAGE_TECHNICAL_DATABASE, "0", e);
}
}
/**
* Binds a null value to a parameter in a stored prepared statement.
*
* @param name the name under which the prepared statement is stored
* @param pos position of the parameter in the prepared statement
* @param type the type of the column (see <code>java.sql.Types</code>)
*/
public void bindNull(Object name, int pos, int type) throws XException
{
try
{
PreparedStatement pStatement = (PreparedStatement) mPreparedStatements
.get(name);
if (pStatement == null)
{
List params = new Vector();
params.add(name);
throw new XException(Constants.LOCATION_EXTERN,
Constants.LAYER_TECHNICAL,
Constants.PACKAGE_TECHNICAL_DATABASE, "7", params);
}
pStatement.setNull(pos, type);
}
catch (SQLException e)
{
throw new XException(Constants.LOCATION_EXTERN,
Constants.LAYER_TECHNICAL,
Constants.PACKAGE_TECHNICAL_DATABASE, "0", e);
}
}
/**
* Executes a stored prepared statement.
*
* @param name the name under which the prepared statement is stored
*
* @return number of affected rows
*/
public int executeUpdatePrepared(Object name) throws XException
{
int result;
try
{
PreparedStatement pStatement = (PreparedStatement) mPreparedStatements
.get(name);
if (pStatement == null)
{
List params = new Vector();
params.add(name);
throw new XException(Constants.LOCATION_EXTERN,
Constants.LAYER_TECHNICAL,
Constants.PACKAGE_TECHNICAL_DATABASE, "7", params);
}
result = pStatement.executeUpdate();
}
catch (SQLException e)
{
throw new XException(Constants.LOCATION_EXTERN,
Constants.LAYER_TECHNICAL,
Constants.PACKAGE_TECHNICAL_DATABASE, "0", e);
}
return result;
}
/**
* Executes a stored prepared statement.
*
* @param name the name under which the prepared statement is stored
* @return the ResultSet for the query
*/
public ResultSet executeReadPrepared(Object name) throws XException
{
ResultSet result = null;
try
{
PreparedStatement pStatement = (PreparedStatement) mPreparedStatements
.get(name);
if (pStatement == null)
{
List params = new Vector();
params.add(name);
throw new XException(Constants.LOCATION_EXTERN,
Constants.LAYER_TECHNICAL,
Constants.PACKAGE_TECHNICAL_DATABASE, "7", params);
}
int maxRows = Configuration.getInstance().getValueAsInt(
mConfigChapter, mConfigSection, "MaxRows");
if (maxRows >= 0)
{
pStatement.setMaxRows(maxRows);
}
result = pStatement.executeQuery();
}
catch (SQLException e)
{
throw new XException(Constants.LOCATION_EXTERN,
Constants.LAYER_TECHNICAL,
Constants.PACKAGE_TECHNICAL_DATABASE, "0", e);
}
return result;
}
/**
* Tests if a prepared statement is stored.
*
* @param name the name under which the prepared statement is stored
*
* @return true if the prepared statement exists
*/
public boolean existsPrepared(Object name)
{
return mPreparedStatements.containsKey(name);
}
static public void clear()
{
mDBConnections.clear();
}
static private String getFullName(String name)
{
return new StringBuffer().append(name).append(".").append(
Thread.currentThread().getName()).toString();
}
/**
* Returns the complete URL of the DBConnection
*/
public String getUrl()
{
return mDBUrl.toString();
}
}