jdbchandler.java

jConfig,JAVA读取XML的开源项目

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 <[e-mail]>
 */
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());
        }