Action based DB Schema Switching

page80 · **Joined:** Thu Aug 11, 2005 12:11 pm **Posts:** 8

Hibernate version: 3.0.3

Name and version of the database you are using: Oracle 9i

Alrighty, to start off, I have read the FAQ, I have read the documentation, and I have searched the forums, and while the information I have found has been helpful, none of which I have found is addressing my problem. This being said, here's the skinny:

I have hibernate connections configured using hibernate.cfg.xml using a "connection.datastore" from a context.xml that defines the attributes needed when making a connection to the database (url, username, password, etc). I use a HibernateUtil class to instantiate the static SessionFactory as such:

Code:

public class HibernateUtil {

  private static final Logger LOGGER = Logger.getLogger(HibernateUtil.class);
  private static final LogHelper LOG_HELPER = LogHelper.getInstance();

  private static Configuration configuration;
  private static SessionFactory sessionFactory;
  private static final ThreadLocal threadSession = new ThreadLocal();
  private static final ThreadLocal threadTransaction = new ThreadLocal();
  private static final ThreadLocal threadInterceptor = new ThreadLocal();

  // Create the initial SessionFactory from the default configuration files
  static {
    try {
      configuration = new Configuration();
      sessionFactory = configuration.configure().buildSessionFactory();
    } catch (Throwable ex) {
      LOGGER.error("Building SessionFactory failed.", ex);
      throw new ExceptionInInitializerError(ex);
    }
  }

  public static SessionFactory getSessionFactory() {
    return sessionFactory;
  }

  public static Configuration getConfiguration() {
    return configuration;
  }

  public static Session getSession()
    throws InfrastructureException {
    Session s = (Session) threadSession.get();
    try {
      if (s == null) {
        LOGGER.debug("Opening new Session for this thread.");
        if (getInterceptor() != null) {
          LOGGER.debug("Using interceptor: " + getInterceptor().getClass());
          s = getSessionFactory().openSession(getInterceptor());
        } else {
          s = getSessionFactory().openSession();
        }
        threadSession.set(s);
      }
    } catch (HibernateException ex) {
      LOG_HELPER.errorStackTrace(ex, LOGGER) ;
      throw new InfrastructureException(ex);
    }
    return s;
  }
...
}

What I'm looking to do here, is based on a specific "first action" a user makes, I need to switch how I connect to the database. I'm using the same database, but I need to connect as a different user, and thus access different DB schema within Oracle.

Thus far, the only way I have seen to do this using my current configuration would be to create a separate xxx.cfg.xml and separate HibernateUtilxxx class that uses hard coded names of the individual cfg.xml files in the

Code:

sessionFactory = configuration.configure("xxx.cfg.xml").buildSessionFactory();

statement for each and every "first action" so that the user would be in the proper context to access the proper schema based data. While this would seemingly work, this seems a bit overkill and bulky, especially if I have 10 different actions that required 10 different schema, not to mention quite tricky in determining what type of HibernateUtilityxxx class to use for the connection. (I thought about trying to parameterize the cfg.xml part of the statement, but how would I get a parameter passed to a static block?)

So, what I'm looking for is either confirmation that my assessment is correct, or that there is a simpler way of accomplishing what I'm looking for.

Thanks in advance for your help and patience with a first-timer.

kgignatyev · **Posted:** Thu Aug 11, 2005 2:04 pm

Yes, there is a simpler way ( I actually did that on production).

There is one factory that does not define how to connect to DB
- to gat session use factory.openSession( getMyConnection() ) method to obtain H session;
- do not forget to disable 2nd level cache;

getMyConnection() returns Connection which points to the necessary schema, in the method you make decision which connection to return. ( you may open them every time, maintain few connection pools, etc. sky is the limit)

page80 · **Joined:** Thu Aug 11, 2005 12:11 pm **Posts:** 8

Hello again,

Thanks for the suggestion, it has prooved useful, but now I'm facing a new problem. I have referenced http://forum.hibernate.org/viewtopic.php?t=937367 and http://www.hibernate.org/hib_docs/reference/en/html/session-configuration.html and I have implemented an active switching between users in the database and I have been able to change schemas (seemingly) for selection, but I have been unable to get a call to saveOrUpdate(objVo) to actually update the database at all (or in the proper schema as I'd like it to).

Currently, I'm using a custom VO to store connection and schema information, and within my DAO, I make the connection and schema changes as follows:

Code:

xxxDAO.java

  public boolean put (Customer objvo, Channel channel ) throws Exception {
    try 
    {
      if (channel == null) {
        Session session_temp = HibernateUtil.getSession() ;
        session_temp.saveOrUpdate(objvo) ;
      }
      else {
        Configuration cfg = HibernateUtil.getConfiguration();
        if (cfg.getProperty("default_schema") == null || 
            cfg.getProperty("default_schema").trim().length() == 0 || 
            !(cfg.getProperty("default_schema").equalsIgnoreCase(channel.getUserName()))) 
        {
          cfg.setProperty("default_schema", channel.getUserName().toUpperCase());
          HibernateUtil.rebuildSessionFactory(cfg);
        }
        Session session_temp = HibernateUtil.getSession(channel.getConnection(),channel.getUserName(),channel.getPassword());
        session_temp.saveOrUpdate(objvo);
      }
    }
    catch (Exception hex) 
    {
      LOGGER.error("CustomerDAO put failed.", hex);
      hex.printStackTrace() ;
      return false;
    }
    return true;
  }

HibernateUtil.java:

public class HibernateUtil {
  private static Configuration configuration;
  private static SessionFactory sessionFactory;
  private static final ThreadLocal threadSession = new ThreadLocal();
  private static final ThreadLocal threadTransaction = new ThreadLocal();
  private static final ThreadLocal threadInterceptor = new ThreadLocal();

  // Create the initial SessionFactory from the default configuration files
  static {
    try {
      configuration = new Configuration();
      sessionFactory = configuration.configure().buildSessionFactory();
    } catch (Throwable ex) {
      throw new ExceptionInInitializerError(ex);
    }
  }

  public static SessionFactory getSessionFactory() {
    return sessionFactory;
  }

  public static Configuration getConfiguration() {
    return configuration;
  }

   public static void rebuildSessionFactory()
    throws InfrastructureException {
    synchronized(sessionFactory) {
      try {
        sessionFactory = getConfiguration().buildSessionFactory();
      } catch (Exception ex) {
        ex.printStackTrace();
        throw new InfrastructureException(ex);
      }
    }
   }

   public static void rebuildSessionFactory(Configuration cfg)
    throws InfrastructureException {
    synchronized(sessionFactory) {
      try {
        sessionFactory = cfg.buildSessionFactory();
        configuration = cfg;
      } catch (Exception ex) {
        ex.printStackTrace();
        throw new InfrastructureException(ex);
      }
    }
   }

  public static Session getSession()
    throws InfrastructureException {
    Session s = (Session) threadSession.get();
    try {
      if (s == null) {
        LOGGER.debug("Opening new Session for this thread.");
        if (getInterceptor() != null) {
          LOGGER.debug("Using interceptor: " + getInterceptor().getClass());
          s = getSessionFactory().openSession(getInterceptor());
        } else {
          s = getSessionFactory().openSession();
        }
        threadSession.set(s);
      }
    } catch (HibernateException ex) {
      LOG_HELPER.errorStackTrace(ex, LOGGER) ;
      throw new InfrastructureException(ex);
    }
    return s;
  }

  public static Session getSession(String url, String usr, String pass)
  throws InfrastructureException {
  Session s = (Session) threadSession.get(); 
  try {
    if (s == null || !(((s.connection().getMetaData().getUserName()).toString()).equals(usr))) {
      LOGGER.debug("Opening new Session for this thread.");
      if (getInterceptor() != null) {
        LOGGER.debug("Using interceptor: " + getInterceptor().getClass());
        s = getSessionFactory().openSession(DriverManager.getConnection(url,usr,pass), getInterceptor());
      } else {
        s = getSessionFactory().openSession(DriverManager.getConnection(url,usr,pass));
      }
      threadSession.set(s);
    }
  } catch (HibernateException ex) {
    LOG_HELPER.errorStackTrace(ex, LOGGER) ;
    throw new InfrastructureException(ex);
  }
  catch (SQLException e) {
    e.printStackTrace();
  }
  return s;
}

}

What is really bizzare about this is that there are no exceptions being thrown that I can find, and when selecting (similar to the "put" method in the DAO), the selection runs fine (albeit the schema quantifier is not in front of the table name, as would be expected, so it may not be working after all). Is there something/anything I'm missing about changing schema and connections? According to the debug, no objects are null during this process and no exceptions are thrown, but no updates ever take place.

Thanks in advance for your help.

kgignatyev · **Posted:** Fri Aug 19, 2005 3:55 pm

I do not see where you commit the transaction.

See also my posts here:
http://forum.hibernate.org/viewtopic.php?p=2256278

On the using CGLib based proxy to take care of automatic session creation/propagation and commiting/rolling back the transaction.

page80 · **Joined:** Thu Aug 11, 2005 12:11 pm **Posts:** 8

Sorry, I excluded that portion of the HibernateUtil class:

Code:

 public static void closeSession()
    throws InfrastructureException {
    try {
      Session s = (Session) threadSession.get();
      threadSession.set(null);
      if (s != null && s.isOpen()) {
        LOGGER.debug("Closing Session of this thread.");
        s.close();
      }
    } catch (HibernateException ex) {
      LOG_HELPER.errorStackTrace(ex, LOGGER) ;
      throw new InfrastructureException(ex);
    }
  }

  public static void beginTransaction()
    throws InfrastructureException {
    Transaction tx = (Transaction) threadTransaction.get();
    try {
      if (tx == null) {
        LOGGER.debug("Starting new database transaction in this thread.");
        tx = getSession().beginTransaction();
        threadTransaction.set(tx);
      }
    } catch (HibernateException ex) {
      LOG_HELPER.errorStackTrace(ex, LOGGER) ;
      throw new InfrastructureException(ex);
    }
  }

  public static void commitTransaction()
    throws InfrastructureException {
    Transaction tx = (Transaction) threadTransaction.get();
    try {
      if ( tx != null && !tx.wasCommitted()
              && !tx.wasRolledBack() ) {
        LOGGER.debug("Committing database transaction of this thread.");
        tx.commit();
      }
      threadTransaction.set(null);
    } catch (HibernateException ex) {
      rollbackTransaction();
      LOG_HELPER.errorStackTrace(ex, LOGGER) ;
      throw new InfrastructureException(ex);
    }
  }

  public static void rollbackTransaction()
    throws InfrastructureException {
    Transaction tx = (Transaction) threadTransaction.get();
    try {
      threadTransaction.set(null);
      if ( tx != null && !tx.wasCommitted() && !tx.wasRolledBack() ) {
        LOGGER.debug("Tyring to rollback database transaction of this thread.");
        tx.rollback();
      }
    } catch (HibernateException ex) {
      LOG_HELPER.errorStackTrace(ex, LOGGER) ;
      throw new InfrastructureException(ex);
    } finally {
      closeSession();
    }
  }

  public static void reconnect(Session session)
    throws InfrastructureException {
    try {
      session.reconnect();
      threadSession.set(session);
    } catch (HibernateException ex) {
      throw new InfrastructureException(ex);
    }
  }

  public static Session disconnectSession()
    throws InfrastructureException {

    Session session = getSession();
    try {
      threadSession.set(null);
      if (session.isConnected() && session.isOpen())
        session.disconnect();
    } catch (HibernateException ex) {
      throw new InfrastructureException(ex);
    }
    return session;
  }

  public static void registerInterceptor(Interceptor interceptor) {
    threadInterceptor.set(interceptor);
  }

  private static Interceptor getInterceptor() {
    Interceptor interceptor =
      (Interceptor) threadInterceptor.get();
    return interceptor;
  }

However, the only time that the transaction gets committed is from HibernateFilter doFilter() so I'm not sure what you're looking for here.

kgignatyev · **Posted:** Fri Aug 19, 2005 5:12 pm

I meant that in your DAO code nobody starts or rollbacks transaction:
Session session_temp = HibernateUtil.getSession() ;

but the code should be 'wrapped' into something like this:

Transaction tx = HibernateUtil.getSession().beginT();
==do transactional stuff==
tx.commit();
HibernateUtil.getSession().close();

Do you have somebody doing that? ( HiveMind, Spring, CGLib interceptors etc)?