package org.jconfig.handler;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import org.jconfig.Category;
import org.jconfig.Configuration;
import org.jconfig.ConfigurationManagerException;
import org.jconfig.DefaultConfiguration;
import org.jconfig.error.ErrorReporter;
import org.jconfig.parser.ConfigurationParser;
import org.jconfig.utils.ResourceLocator;
/**
* This implementation provides the basic requirements for
* a JDBC Handler. The database structure is quite simple.
* It is a based on a Configuration table (T_CONFIGURATION),
* a Category table (T_CATEGORY), a Property table (T_PROPERTY)
* and a Variable table (T_VARIABLE).
*
* A Configuration can have 1:many Categories and 1:many Variables.
*
* A Category can have 1:many Properties.
*
* Implementation does require that the database support some sort
* of auto-increment for the object identifier (oid), if you want to
* store the Configurations.
*
* One problem with the implementation is the ability to store the
* variable information within the properties. This is an inherent
* problem with all of the ConfigurationHandlers.
*
* Following properties need to set inside of the jconfig.properties
* file:
*
* org.jconfig.jdbc.driver
* org.jconfig.jdbc.user
* org.jconfig.jdbc.pwd
* org.jconfig.jdbc.url
*
* @author Andreas Mecky <andreas dot mecky at xcom dot de>
* @author Terry Dye <terry dot dye at xcom dot de>
* @author Ralf Haemmerlin <ralf.haemmerlin@liebherr.com>
*/
public class JDBCHandler implements ConfigurationHandler {
private String driver;
private String jdbcurl;
private String user;
private String pwd;
private Connection con;
/* (non-Javadoc)
* @see org.jconfig.handler.ConfigurationHandler#load(java.lang.String)
*/
public Configuration load(String configurationName) throws ConfigurationManagerException {
if(configurationName == null) {
ErrorReporter.getErrorHandler().reportError("Configuration name cannot be <null>");
throw new ConfigurationManagerException("Configuration name cannot be <null>");
}
if(configurationName.length() == 0) {
ErrorReporter.getErrorHandler().reportError("Configuration name not valid. Empty String not allowed");
throw new ConfigurationManagerException("Configuration name not valid. Empty String not allowed");
}
setupJDBC();
return loadConfiguration(configurationName);
}
private void setupJDBC() throws ConfigurationManagerException {
loadJDBCProperties();
loadJDBCDriver();
con = JDBCLogin();
}
/**
* @return
*/
private Configuration loadConfiguration(String configurationName) throws ConfigurationManagerException {
Configuration config = new DefaultConfiguration(configurationName);
try {
PreparedStatement pstmt = con.prepareStatement("SELECT OID FROM T_CONFIGURATION WHERE NAME = ?");
pstmt.setString(1, configurationName);
ResultSet result = pstmt.executeQuery();
while(result.next()) {
long oid = result.getLong("OID");
loadCategories(config, oid);
loadVariables(config, oid);
}
result.close();
pstmt.close();
con.close();
} catch (SQLException e) {
ErrorReporter.getErrorHandler().reportError("Problem retrieving Configuration from database",e);
throw new ConfigurationManagerException("Problem retrieving Configuration from database. " + e.getMessage());
}
return config;
}
/**
* @param oid
*/
private void loadCategories(Configuration config, long oid) throws ConfigurationManagerException {
try {
PreparedStatement pstmt = con.prepareStatement("SELECT OID, NAME FROM T_CATEGORY WHERE CONFIGURATION_OID = ?");
pstmt.setLong(1, oid);
ResultSet result = pstmt.executeQuery();
while(result.next()) {
long catoid = result.getLong("OID");
String categoryName = result.getString("NAME");
Category cat = config.getCategory(categoryName);
loadProperties(cat, catoid);
}
result.close();
pstmt.close();
} catch (SQLException e) {
ErrorReporter.getErrorHandler().reportError("Problem retrieving categories",e);
throw new ConfigurationManagerException("Problem retrieving categories. " + e.getMessage());
}
}
/**
* @param oid
*/
private Hashtable readOldCategories(long oid) throws ConfigurationManagerException {
Hashtable oldCategories = new Hashtable();
try {
PreparedStatement pstmt = con.prepareStatement("SELECT OID, NAME FROM T_CATEGORY WHERE CONFIGURATION_OID = ?");
pstmt.setLong(1, oid);
ResultSet result = pstmt.executeQuery();
while(result.next()) {
Long catoid = new Long(result.getLong("OID"));
String categoryName = result.getString("NAME");
oldCategories.put(catoid,categoryName);
}
result.close();
pstmt.close();
} catch (SQLException e) {
ErrorReporter.getErrorHandler().reportError("Problem retrieving categories",e);
throw new ConfigurationManagerException("Problem retrieving categories. " + e.getMessage());
}
return oldCategories;
}
/**
*
*/
private void loadVariables(Configuration config, long oid) throws ConfigurationManagerException {
try {
PreparedStatement pstmt = con.prepareStatement("SELECT OID, NAME, VALUE FROM T_VARIABLE WHERE CONFIGURATION_OID = ?");
pstmt.setLong(1, oid);
ResultSet result = pstmt.executeQuery();
while(result.next()) {
long catoid = result.getLong("OID");
String variableName = result.getString("NAME");
String variableValue = result.getString("VALUE");
config.setVariable(variableName, variableValue);
}
result.close();
pstmt.close();
} catch (SQLException e) {
ErrorReporter.getErrorHandler().reportError("Problem retrieving variables",e);
throw new ConfigurationManagerException("Problem retrieving variables. " + e.getMessage());
}
}
/**
*
* @param oid
* @return
* @throws ConfigurationManagerException
*/
private Hashtable readOldVariables(long oid) throws ConfigurationManagerException {
Hashtable oldVariables = new Hashtable();
try {
PreparedStatement pstmt = con.prepareStatement("SELECT OID, NAME, VALUE FROM T_VARIABLE WHERE CONFIGURATION_OID = ?");
pstmt.setLong(1, oid);
ResultSet result = pstmt.executeQuery();
while(result.next()) {
long catoid = result.getLong("OID");
String variableName = result.getString("NAME");
oldVariables.put(new Long(catoid),variableName);
}
result.close();
pstmt.close();
} catch (SQLException e) {
ErrorReporter.getErrorHandler().reportError("Problem retrieving old variables",e);
throw new ConfigurationManagerException("Problem retrieving old variables. " + e.getMessage());
}
return oldVariables;
}
/**
* @param cat
* @param catoid
*/
private Hashtable readOldProperties(long catoid) throws ConfigurationManagerException {
Hashtable oldProperties = new Hashtable();
try {
PreparedStatement pstmt = con.prepareStatement("SELECT OID, NAME, VALUE FROM T_PROPERTY WHERE CATEGORY_OID = ? ");
pstmt.setLong(1, catoid);
ResultSet result = pstmt.executeQuery();
while(result.next()) {
long propoid = result.getLong("OID");
String propertyName = result.getString("NAME");
oldProperties.put(new Long(propoid),propertyName);
}
result.close();
pstmt.close();
} catch (SQLException e) {
ErrorReporter.getErrorHandler().reportError("Problem retrieving properties",e);
throw new ConfigurationManagerException("Problem retrieving properties from database. " + e.getMessage());
}
return oldProperties;
}
/**
* @param cat
* @param catoid
*/
private void loadProperties(Category cat, long catoid) throws ConfigurationManagerException {
try {
PreparedStatement pstmt = con.prepareStatement("SELECT OID, NAME, VALUE FROM T_PROPERTY WHERE CATEGORY_OID = ? ");
pstmt.setLong(1, catoid);
ResultSet result = pstmt.executeQuery();
while(result.next()) {
long propoid = result.getLong("OID");
String propertyName = result.getString("NAME");
String propertyValue = result.getString("VALUE");
cat.setProperty(propertyName, propertyValue);
}
result.close();
pstmt.close();
} catch (SQLException e) {
ErrorReporter.getErrorHandler().reportError("Problem retrieving properties",e);
throw new ConfigurationManagerException("Problem retrieving properties from database. " + e.getMessage());
}
}
private void loadJDBCDriver() throws ConfigurationManagerException {
if(driver == null)
throw new ConfigurationManagerException("JDBC Driver name is <null>. Check jconfig.properties for org.jconfig.jdbcdriver.");
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
ErrorReporter.getErrorHandler().reportError("JDBC Driver " + e.getMessage() + " was not found.");
throw new ConfigurationManagerException("JDBC Driver " + e.getMessage() + " was not found.");
}
}
private void loadJDBCProperties() throws ConfigurationManagerException {
try {
ResourceLocator locator = new ResourceLocator("jconfig.properties");
InputStream is = locator.getInputStream();
// it is possible that the jconfig.properties does not exist, we get null
if ( is != null ) {
Properties jcfProperties = new Properties();
jcfProperties.load( is );
driver = jcfProperties.getProperty("org.jconfig.jdbc.driver");
jdbcurl = jcfProperties.getProperty("org.jconfig.jdbc.url");
user = jcfProperties.getProperty("org.jconfig.jdbc.user");
pwd = jcfProperties.getProperty("org.jconfig.jdbc.pwd");
}
else {
ErrorReporter.getErrorHandler().reportError("Problem locating jconfig.properties.");
throw new ConfigurationManagerException("Problem locating jconfig.properties.");
}
} catch (IOException e) {
ErrorReporter.getErrorHandler().reportError("Problem locating jconfig.properties.",e);
throw new ConfigurationManagerException("Problem locating jconfig.properties. " + e.getMessage());
}
}
protected Connection JDBCLogin() throws ConfigurationManagerException {
if(jdbcurl == null) {
ErrorReporter.getErrorHandler().reportError("JDBC URL is <null>. Check jconfig.properties for org.jconfig.jdbc.url.");
throw new ConfigurationManagerException("JDBC URL is <null>. Check jconfig.properties for org.jconfig.jdbc.url.");
}
if(user == null) {
ErrorReporter.getErrorHandler().reportError("JDBC USER is <null>. Check jconfig.properties for org.jconfig.jdbc.user.");
throw new ConfigurationManagerException("JDBC USER is <null>. Check jconfig.properties for org.jconfig.jdbc.user.");
}
if(pwd == null) {
ErrorReporter.getErrorHandler().reportError("JDBC PASSWORD is <null>. Check jconfig.properties for org.jconfig.jdbc.pwd");
throw new ConfigurationManagerException("JDBC PASSWORD is <null>. Check jconfig.properties for org.jconfig.jdbc.pwd.");
}
try {
return DriverManager.getConnection(jdbcurl, user, pwd);
} catch (SQLException e) {
ErrorReporter.getErrorHandler().reportError("Problem getting JDBC Connection",e);
throw new ConfigurationManagerException("Problem getting JDBC Connection: " + e.getMessage());
}
}
/* (non-Javadoc)
* @see org.jconfig.handler.ConfigurationHandler#store(org.jconfig.Configuration)
*/
public void store(Configuration configuration)
throws ConfigurationManagerException {
setupJDBC();
long confoid = storeConfiguration(configuration.getConfigName());
Hashtable oldCategories = readOldCategories(confoid);
String [] categoryNameArray = configuration.getCategoryNames();
for (int i = 0; i < categoryNameArray.length; i++) {
String categoryName = categoryNameArray[i];
long catid = storeCategory(confoid, categoryName);
oldCategories.remove(new Long(catid));
storeProperties(catid, configuration.getCategory(categoryName));
}
// Delete old Categories rested in oldCategories
Enumeration enumCat = oldCategories.keys();
while (enumCat.hasMoreElements()) {
Long catid = (Long)enumCat.nextElement();
deleteCategory(catid.longValue(),oldCategories.get(catid).toString());
}
String [] variableNameArray = getStringArray(configuration.getVariables());
Hashtable oldVariables = readOldVariables(confoid);
for (int i = 0; i < variableNameArray.length; i++) {
String variableName = variableNameArray[i];
long varoid= storeVariable(confoid, variableName, configuration.getVariable(variableName));
oldVariables.remove(new Long(varoid));
}
Enumeration enumVar = oldVariables.keys();
while (enumVar.hasMoreElements()) {
Long varoid = (Long)enumVar.nextElement();
deleteVariable(varoid.longValue(),oldVariables.get(varoid).toString());
}
closeConnection();
}
private void closeConnection() throws ConfigurationManagerException {
try {
con.close();
} catch (SQLException e) {
ErrorReporter.getErrorHandler().reportError("Problem closing database connection.",e);
throw new ConfigurationManagerException("Problem closing database connection. " + e.getMessage());
}
}
/**
* @param catid
* @param category
*/
private void storeProperties(long catid, Category category) throws ConfigurationManagerException {
Enumeration enm = category.getProperties().keys();
Hashtable oldProperties = readOldProperties(catid);
while(enm.hasMoreElements()) {
String propertyName = (String)enm.nextElement();
long propoid = storeProperty(catid, propertyName, category.getProperty(propertyName));
oldProperties.remove(new Long(propoid));
}
Enumeration enumeration = oldProperties.keys();
while (enumeration.hasMoreElements()) {
Long propoid = (Long)enumeration.nextElement();
deleteProperty(propoid.longValue(),oldProperties.get(propoid).toString());
}
}
/**
* @param propertyName
* @param string
*/
private long storeProperty(long catoid, String propertyName, String propertyValue) throws ConfigurationManagerException {
long propoid = Long.MIN_VALUE;
try {
PreparedStatement pstmt = con.prepareStatement("SELECT OID FROM T_PROPERTY WHERE CATEGORY_OID = ? AND NAME= ?");
pstmt.setLong(1, catoid);
pstmt.setString(2, propertyName);
ResultSet result = pstmt.executeQuery();
while(result.next()) {
propoid = result.getLong("OID");
updateProperty(propoid, propertyValue);
}
result.close();
pstmt.close();
} catch (SQLException e) {
ErrorReporter.getErrorHandler().reportError("Problem storing configuration in database.",e);
throw new ConfigurationManagerException("Problem storing configuration in database. " + e.getMessage());
}
if(propoid == Long.MIN_VALUE) {
createNewProperty(catoid, propertyName, propertyValue);
return storeProperty(catoid, propertyName, propertyValue);
}
return propoid;
}
/**
* @param propoid
* @param propertyValue
*/
private void updateProperty(long propoid, String propertyValue) throws ConfigurationManagerException {
try {
PreparedStatement pstmt = con.prepareStatement("UPDATE T_PROPERTY SET VALUE=? WHERE OID=?");
pstmt.setString(1, propertyValue);
pstmt.setLong(2, propoid);
pstmt.execute();
pstmt.close();
} catch (SQLException e) {
ErrorReporter.getErrorHandler().reportError(propertyValue + " variable value could not be stored.",e);
throw new ConfigurationManagerException(propertyValue + " variable value could not be stored." + e.getMessage());
}
}
/**
* @param catoid
* @param propertyName
* @param propertyValue
*/
private void createNewProperty(long catoid, String propertyName, String propertyValue) throws ConfigurationManagerException {
try {
PreparedStatement pstmt = con.prepareStatement("INSERT INTO T_PROPERTY (NAME, VALUE, CATEGORY_OID) VALUES (?, ?, ?)");
pstmt.setString(1, propertyName);
pstmt.setString(2, propertyValue);
pstmt.setLong(3, catoid);
pstmt.execute();
pstmt.close();
} catch (SQLException e) {
ErrorReporter.getErrorHandler().reportError(propertyValue + " property value could not be stored.",e);
throw new ConfigurationManagerException(propertyName + " property could not be stored." + e.getMessage());
}
}
/**
* @param confoid
* @param variableName
*/
private long storeVariable(long confoid, String variableName, String variableValue) throws ConfigurationManagerException {
long varoid = Long.MIN_VALUE;
try {
PreparedStatement pstmt = con.prepareStatement("SELECT OID FROM T_VARIABLE WHERE CONFIGURATION_OID = ? AND NAME= ?");
pstmt.setLong(1, confoid);
pstmt.setString(2, variableName);
ResultSet result = pstmt.executeQuery();
while(result.next()) {
varoid = result.getLong("OID");
updateVariable(varoid, variableValue);
}
result.close();
pstmt.close();
} catch (SQLException e) {
ErrorReporter.getErrorHandler().reportError(variableName + " variable value could not be stored.",e);
throw new ConfigurationManagerException("Problem sotring variable in database. " + e.getMessage());
}
if(varoid == Long.MIN_VALUE) {
createNewVariable(confoid, variableName, variableValue);
return storeVariable(confoid, variableName, variableValue);
}
return varoid;
}
/**
* @param catoid
* @param variableValue
*/
private void updateVariable(long varoid, String variableValue) throws ConfigurationManagerException {
try {
PreparedStatement pstmt = con.prepareStatement("UPDATE T_VARIABLE SET VALUE=? WHERE OID=?");
pstmt.setString(1, variableValue);
pstmt.setLong(2, varoid);
pstmt.execute();
pstmt.close();
} catch (SQLException e) {
ErrorReporter.getErrorHandler().reportError(variableValue + " variable value could not be stored.",e);
throw new ConfigurationManagerException(variableValue + " variable value could not be stored." + e.getMessage());
}
}
/**
* @param confoid
* @param variableName
*/
private void createNewVariable(long confoid, String variableName, String variableValue) throws ConfigurationManagerException {
try {
PreparedStatement pstmt = con.prepareStatement("INSERT INTO T_VARIABLE (NAME, VALUE, CONFIGURATION_OID) VALUES (?, ?, ?)");
pstmt.setString(1, variableName);
pstmt.setString(2, variableValue);
pstmt.setLong(3, confoid);
pstmt.execute();
pstmt.close();
} catch (SQLException e) {
ErrorReporter.getErrorHandler().reportError(variableValue + " variable value could not be stored.",e);
throw new ConfigurationManagerException(variableName + " variable could not be stored." + e.getMessage());
}
}
/**
* @param confoid
* @param categoryName
*/
private long storeCategory(long confoid, String categoryName) throws ConfigurationManagerException {
long catoid = Long.MIN_VALUE;
try {
PreparedStatement pstmt = con.prepareStatement("SELECT OID FROM T_CATEGORY WHERE CONFIGURATION_OID = ? AND NAME = ?");
pstmt.setLong(1, confoid);
pstmt.setString(2, categoryName);
ResultSet result = pstmt.executeQuery();
while(result.next()) {
catoid = result.getLong("OID");
}
result.close();
pstmt.close();
} catch (SQLException e) {
ErrorReporter.getErrorHandler().reportError("Problem storing category " + categoryName + ". ",e);
throw new ConfigurationManagerException("Problem storing category " + categoryName + ". " + e.getMessage());
}
if(catoid == Long.MIN_VALUE) {
createNewCategory(confoid, categoryName);
return storeCategory(confoid, categoryName);
}
return catoid;
}
/**
* @param confoid
* @param categoryName
*/
private void deleteCategory(long confoid, String categoryName) throws ConfigurationManagerException {
try {
PreparedStatement pstmt = con.prepareStatement("DELETE FROM T_CATEGORY WHERE CONFIGURATION_OID = ? AND NAME = ?");
pstmt.setLong(1, confoid);
pstmt.setString(2, categoryName);
pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
ErrorReporter.getErrorHandler().reportError("Problem deleting category " + categoryName + ". ",e);
throw new ConfigurationManagerException("Problem deleting category " + categoryName + ". " + e.getMessage());
}
}
/**
* @param confoid
* @param categoryName
*/
private void deleteProperty(long propoid, String propertyName) throws ConfigurationManagerException {
try {
PreparedStatement pstmt = con.prepareStatement("DELETE FROM T_PROPERTY WHERE OID = ? AND NAME = ?");
pstmt.setLong(1, propoid);
pstmt.setString(2, propertyName);
pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
ErrorReporter.getErrorHandler().reportError("Problem deleting property " + propertyName + ". ",e);
throw new ConfigurationManagerException("Problem deleting property " + propertyName + ". " + e.getMessage());
}
}
/**
*
* @param varoid
* @param variableName
* @throws ConfigurationManagerException
*/
private void deleteVariable(long varoid, String variableName) throws ConfigurationManagerException {
try {
PreparedStatement pstmt = con.prepareStatement("DELETE FROM T_VARIABLE WHERE OID = ? ");
pstmt.setLong(1, varoid);
pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
ErrorReporter.getErrorHandler().reportError("Problem deleting variable " + variableName + ". ",e);
throw new ConfigurationManagerException("Problem deleting variable " + variableName + ". " + e.getMessage());
}
}
/**
* @param confoid
* @param categoryName
*/
private void createNewCategory(long confoid, String categoryName) throws ConfigurationManagerException {
try {
PreparedStatement pstmt = con.prepareStatement("INSERT INTO T_CATEGORY (NAME, CONFIGURATION_OID) VALUES (?, ?)");
pstmt.setString(1, categoryName);
pstmt.setLong(2, confoid);
pstmt.execute();
pstmt.close();
} catch (SQLException e) {
ErrorReporter.getErrorHandler().reportError("Problem storing category " + categoryName + ". ",e);
throw new ConfigurationManagerException(categoryName + " category could not be stored." + e.getMessage());
}
}
/**
* @param string
*/
private long storeConfiguration(String configurationName) throws ConfigurationManagerException {
long oid = Long.MIN_VALUE;
try {
PreparedStatement pstmt = con.prepareStatement("SELECT OID FROM T_CONFIGURATION WHERE NAME = ?");
pstmt.setString(1, configurationName);
ResultSet result = pstmt.executeQuery();
while(result.next()) {
oid = result.getLong("OID");
}
result.close();
pstmt.close();
} catch (SQLException e) {
ErrorReporter.getErrorHandler().reportError("Problem storing configuration in database.",e);
throw new ConfigurationManagerException("Problem storing configuration in database. " + e.getMessage());
}
if(oid == Long.MIN_VALUE) {
createNewConfiguration(configurationName);
return storeConfiguration(configurationName);
}
return oid;
}
/**
* @param configurationName
*/
private void createNewConfiguration(String configurationName) throws ConfigurationManagerException {
try {
PreparedStatement pstmt = con.prepareStatement("INSERT INTO T_CONFIGURATION (NAME) VALUES (?)");
pstmt.setString(1, configurationName);
pstmt.execute();
pstmt.close();
} catch (SQLException e) {
ErrorReporter.getErrorHandler().reportError("Problem storing configuration in database.",e);
throw new ConfigurationManagerException(configurationName + " configuration could not be stored." + e.getMessage());
}
}
private String [] getStringArray(Map categories) {
Set allCategories = categories.keySet();
return (String[]) allCategories.toArray(new String[0]);
}
public Configuration load(String configurationName, ConfigurationParser parser) throws ConfigurationManagerException {
throw new ConfigurationManagerException("Using a specific parser with this handler is not supported");
}
}