dbstorage.java

java开源的企业总线.xmlBlaster

/*------------------------------------------------------------------------------
Name:      DbStorage.java
Project:   xmlBlaster.org
Copyright: xmlBlaster.org, see xmlBlaster-LICENSE file
------------------------------------------------------------------------------*/

package org.xmlBlaster.contrib.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Set;
import java.util.TreeSet;
import java.util.logging.Logger;

import org.xmlBlaster.contrib.I_Info;
import org.xmlBlaster.contrib.InfoHelper;
import org.xmlBlaster.util.qos.ClientProperty;

/**
 * DbStorage
 * @author <a href="mailto:[e-mail]">Michele Laghi</a>
 */
public class DbStorage {

   private static Logger log = Logger.getLogger(DbStorage.class.getName());
   
   private final static int CONTEXT = 1;
   private final static int KEY = 2;
   private final static int VALUE = 3;
   private final static int TYPE = 4;
   private final static int ENCODING = 5;

   private String context;
   private I_DbPool pool;
   private DbMetaHelper dbHelper;
   private String tableName;
   
   private String createSql;
   private String modifySql;
   private String addSql;
   private String getSql;
   private String cleanSql;
   private String deleteSql;
   private String getKeysSql;
   
   private final void prepareSql(I_Info info, String ctx) throws Exception {
      
      String tmp = info.get("dbs.context", null);
      if (tmp != null && !tmp.equals(ctx))
         log.warning("Property 'dbs.context' already set to '" + tmp + "' will overwrite it to '" + ctx + "'");
      info.put("dbs.context", ctx);
      
      String table = info.get("dbs.table", "DBINFO");
      this.tableName = this.dbHelper.getIdentifier(table);

      String contextName = info.get("dbs.table", "context");
      String keyName = info.get("dbs.keyName", "name");
      String valueName = info.get("dbs.valueName", "value");
      String typeName = info.get("dbs.typeName", "type");
      String encodingName = info.get("dbs.encodingName", "encoding");
      String[] originalNames = new String[] { contextName, keyName, valueName, typeName, encodingName };
      info.put("dbs.table", this.tableName);
      
      info.put("dbs.contextName", contextName);
      info.put("dbs.keyName", keyName);
      info.put("dbs.valueName", valueName);
      info.put("dbs.typeName", typeName);
      info.put("dbs.encodingName", encodingName);
      
      InfoHelper helper = new InfoHelper(info);
      
      tmp = "CREATE TABLE ${dbs.table} (${dbs.contextName} VARCHAR(255), ${dbs.keyName} VARCHAR(255), ${dbs.valueName} VARCHAR(255), ${dbs.typeName} VARCHAR(16), ${dbs.encodingName} VARCHAR(16), PRIMARY KEY (${dbs.contextName}, ${dbs.keyName}))";
      createSql = helper.replace(info.get("dbs.createSql", tmp));
      createTableOrReadColumnNames(originalNames, info);

      
      log.fine("create statement: '" + createSql + "'");
      tmp = "UPDATE ${dbs.table} SET ${dbs.valueName}=?, ${dbs.typeName}=?, ${dbs.encodingName}=? WHERE ${dbs.contextName}=? AND ${dbs.keyName}=?";
      modifySql = helper.replace(info.get("dbs.mofifySql", tmp));
      log.fine("modify statement: '" + modifySql + "'");
      tmp = "INSERT INTO ${dbs.table} VALUES(?, ?, ?, ?, ?)";
      addSql = helper.replace(info.get("dbs.addSql", tmp));
      log.fine("add statement: '" + addSql + "'");
      tmp = "SELECT * FROM ${dbs.table} WHERE ${dbs.contextName}=? AND ${dbs.keyName}=?";
      getSql = helper.replace(info.get("dbs.getSql", tmp));
      log.fine("get statement: '" + getSql + "'");
      tmp = "DELETE FROM ${dbs.table} WHERE ${dbs.contextName}='${dbs.context}'";
      cleanSql = helper.replace(info.get("dbs.cleanSql", tmp));
      log.fine("clean statement: '" + cleanSql + "'");
      tmp = "SELECT ${dbs.keyName} FROM ${dbs.table} WHERE ${dbs.contextName}=?";
      getKeysSql = helper.replace(info.get("db.getKeysSql", tmp));
      log.fine("getKeys statement: '" + getKeysSql + "'");
      tmp = "DELETE FROM ${dbs.table} WHERE ${dbs.contextName}='${dbs.context}' AND ${dbs.keyName}=?";
      deleteSql = helper.replace(info.get("dbs.deleteSql", tmp));
      log.fine("delete statement: '" + deleteSql + "'");
      /*
      this.createSql = "CREATE TABLE " + table + " (context VARCHAR(255), " + KEY_TXT + " VARCHAR(255), value VARCHAR(255), type VARCHAR(16), encoding VARCHAR(16), PRIMARY KEY (context, " + KEY_TXT + "))";
      this.modifySql = "UPDATE " + table + " SET value=?, type=?, encoding=? WHERE context=? AND " + KEY_TXT + "=?"; 
      this.addSql = "INSERT INTO " + table + " VALUES(?, ?, ?, ?, ?)";
      this.getSql = "SELECT * FROM " + table + " WHERE context=? AND " + KEY_TXT + "=?"; 
      this.cleanSql = "DELETE FROM " + table + " WHERE context='" + ctx + "'";
      this.getKeysSql = "SELECT " + KEY_TXT + " FROM " + table + " WHERE context=?";
      this.deleteSql = "DELETE FROM " + table + " WHERE context='" + ctx + "' AND " + KEY_TXT + "=?";
      */
   }
   
   public DbStorage(I_Info info, I_DbPool pool, String context) throws Exception {
      if (context == null || context.trim().length() < 1)
         this.context = "/";
      else
         this.context = context;
      if (pool == null)
         throw new Exception("DbStorage constructor: The Database pool 'pool' was null. This is not allowed.");
      this.pool = pool;
      this.dbHelper = new DbMetaHelper(this.pool);
      prepareSql(info, this.context);
   }
   
   private final boolean tableExists() throws Exception {
      tableName = this.dbHelper.getIdentifier(tableName);
      Connection conn = null;
      try {
         conn = this.pool.reserve();
         conn.setAutoCommit(true);
         ResultSet rs = conn.getMetaData().getTables(null, null, tableName, null);
         boolean exists = rs.next();
         rs.close();
         return exists;
      }
      finally {
         if (conn != null)
            this.pool.release(conn);
      }
   }
   
   private void createTableOrReadColumnNames(String originalNames[], I_Info info) throws Exception {
      if (!tableExists()) {
         // TODO: Add schema as Oracle finds the same named table in another schema
         // and make tableName configurable 'xmlBlaster.DBINFO'
         // TODO !!!!!
         log.info("Going to create the table with the statement '" + createSql + "'");
         this.pool.update(createSql);
      }
      else {
         String catalog = info.get("dbs.catalog", null);
         String schema = info.get("dbs.schema", null);
         String names[] = this.dbHelper.getColumnNames(pool, catalog, schema, tableName);
         if (names.length < 5)
            throw new Exception("Wrong number of columns in table '" + this.tableName + "' it should be at least 5 but is " + names.length);
         for (int i=0; i < 5; i++) {
            if (dbHelper.getIdentifier(originalNames[i]).equals(names[i]))
               continue;
            log.warning("The proposed name for column '" + originalNames[i] + "' is already set to '" + names[i] + "' on the db for table '" + this.tableName + "' (should not have any impact on the application)");
         }
         info.put("dbs.contextName", names[0]);
         info.put("dbs.keyName", names[1]);
         info.put("dbs.valueName", names[2]);
         info.put("dbs.typeName", names[3]);
         info.put("dbs.encodingName", names[4]);
      }
   }

   /**
    * Adds a new property to the Table. If the entry already exists, an exception is thrown.
    * @param prop
    * @throws Exception
    */
   public boolean addProperty(ClientProperty prop) throws Exception {
      if (prop == null)
         throw new Exception("The client property was null");
      String key = prop.getName();
      if (key == null)
         throw new Exception("The name of the property '" + prop.toXml() + "' was not defined.");
      PreparedStatement st = null;
      Connection conn = null;
      try {
         conn = this.pool.reserve();
         conn.setAutoCommit(true);
         st = conn.prepareStatement(addSql);
         st.setString(CONTEXT, this.context);
         st.setString(KEY, key);
         st.setString(VALUE, prop.getValueRaw());
         st.setString(TYPE, prop.getType());
         st.setString(ENCODING, prop.getEncoding());
         int ret = st.executeUpdate();
         return ret != 0;
      }
      finally {
         if (st != null) {
            try {
               st.close();
            }
            catch (Throwable ex) { 
               ex.printStackTrace(); 
            }
         }
         if (conn != null)
            this.pool.release(conn);
      }
   }
   
   
   /**
    * Modifies an existing property. If the property does not exist, an exception is thrown. 
    * 
    * @param prop
    * @throws Exception
    */
   public boolean modifyProperty(ClientProperty prop) throws Exception {
      if (prop == null)
         throw new Exception("The client property was null");
      String key = prop.getName();
      if (key == null)
         throw new Exception("The name of the property '" + prop.toXml() + "' was not defined.");
      PreparedStatement st = null;
      Connection conn = null;
      try {
         conn = this.pool.reserve();
         conn.setAutoCommit(true);
         st = conn.prepareStatement(modifySql);
         st.setString(1, prop.getValueRaw());
         st.setString(2, prop.getType());
         st.setString(3, prop.getEncoding());
         st.setString(4, this.context);
         st.setString(5, key);
         int ret = st.executeUpdate();
         return ret != 0;
      }
      finally {
         if (st != null) {
            try {
               st.close();
            }
            catch (Throwable ex) { 
               ex.printStackTrace(); 
            }
         }
         if (conn != null)
            this.pool.release(conn);
      }
   }

   
   /**
    * Modifies an existing property. If the property does not exist, an exception is thrown. 
    * 
    * @param prop
    * @throws Exception
    */
   public ClientProperty getProperty(String key) throws Exception {
      if (key == null)
         throw new Exception("The key to search was null");
      PreparedStatement st = null;
      Connection conn = null;
      try {
         conn = this.pool.reserve();
         conn.setAutoCommit(true);
         st = conn.prepareStatement(getSql);
         st.setString(1, this.context);
         st.setString(2, key);
         ResultSet rs = st.executeQuery();
         if (!rs.next())
            return null; // then no entry found.
         // we don't need context and string.
         String value = rs.getString(3);
         String type = rs.getString(4);
         String encoding = rs.getString(5);
         return new ClientProperty(key, type, encoding, value);
      }
      finally {
         if (st != null) {
            try {
               st.close();
            }
            catch (Throwable ex) { 
               ex.printStackTrace(); 
            }
         }
         if (conn != null)
            this.pool.release(conn);
      }
   }
   
   /**
    * Removes the property with the given key. If none found, nothing happens. 
    * @param key
    * @return true if the entry was removed, false otherwise (i.e. if the entry was not found).
    * @throws Exception
    */
   public boolean remove(String key) throws Exception {
      if (key == null)
         throw new Exception("The key to remove was null");

      PreparedStatement st = null;
      Connection conn = null;
      try {
         conn = this.pool.reserve();
         conn.setAutoCommit(true);
         st = conn.prepareStatement(deleteSql);
         st.setString(1, key);
         return st.executeUpdate() != 0;
      }
      finally {
         if (st != null) {
            try {
               st.close();
            }
            catch (Throwable ex) { 
               ex.printStackTrace(); 
            }
         }
         if (conn != null)
            this.pool.release(conn);
      }
   }
   
   /**
    * Removes all the properties of this context.  
    * @return the number of entries removed.
    * @throws Exception
    */
   public int clean() throws Exception {
      return this.pool.update(cleanSql);
   }
   
   /**
    * This method tries first to update the entry. If an exception, then presumably the entry did not exist, so an insert is made. If this fails too,
    * then an exception is thrown.
    * 
    * @param prop
    * @throws Exception
    */
   public void put(ClientProperty prop) throws Exception {
      if (prop == null)
         throw new Exception("The property to put into the table was null");
      boolean hasAdded = this.modifyProperty(prop);
      if (!hasAdded)
         this.addProperty(prop);
      
   }

   public Set getKeys() throws Exception {
      PreparedStatement st = null;
      Connection conn = null;
      try {
         conn = this.pool.reserve();
         conn.setAutoCommit(true);
         st = conn.prepareStatement(getKeysSql);
         st.setString(1, this.context);

         ResultSet rs = st.executeQuery();
         Set ret = new TreeSet();
         
         while (rs.next()) {
            ret.add(rs.getString(1));
         }
         return ret;
      }
      finally {
         if (st != null) {
            try {
               st.close();
            }
            catch (Throwable ex) { 
               ex.printStackTrace(); 
            }
         }
         if (conn != null)
            this.pool.release(conn);
      }
   }
   
}