Dynamically selecting Hibernate session factory

amy

Good afternoon,

I have an application that has a login screen and a DB selection drop-down that allows the user to dynamically choose which database they wish to connect to via Hibernate. Once that DB is chosen that DB remains the current DB for the remainder of the application usage (i.e. only one DB is actively being used at any point in time)

What is the best approach for integrating this into my application. I have already created multiple session factorys and can change my config file to point to the database of interest, but I would like to integrate this with the DB selection drop-down so the user can choose at run-time.

I have thought of dynamically loading my configuration files based upon the DB choice but then I am concerned about how this will impact the remainder of my application objects.

I have also thought of splitting my object definitions into two separate files, one which has the object required to get the login page up and then load the remainder of my application objects (service objects, DAO objects, i.e. the majority of the application objects) once the DB selection has been made, which allows the Spring container to hookup all my object dependencies.

Is there are pre-canned solution for this or what is the best way to approach this.

Thanks in advance.

Whatty

You could store connection information for each of your possible databases in a Maplt;String, YourConnectionInformationgt;, then bind that to the user's HTML form selection.

Once the user makes his selection, you initialize your hibernate beans using the connection supplied in whichever YourConnectionInformation object the user chose.

If your databases are the same in structure (i.e. same tables etc.) you only need 1 SessionFactory not multiple, you only need to switch the database. Which you could do with something like this.

This is exactly what I was looking for, thank-you.

You mention in your article that this has been posted back for inclusion in the framework. I am hoping that they include an example application on how to use this since this is something that I personally have seen several times throughout my career and is extremely helpful.

One side question, should I be concerned about the overhead about maintaining the extra unused connections. I am not overly concerned about this since most likely I will deploy to PROD with a different configuration and only the default PROD db connection. However, in my UAT, QC, DEV environments, are these connections (which tend to be heavy objects) actually instantiated and sitting idle.

Thanks in advance.

Whatty

Marten,

It looks like in the article that you left a fair amount as an exercise for the reader.

I checked out your SVN repo and some key files are missing, you wouldn't happen to have the full source of the working Hibernate sessionfactory example that you mentioned in the article.

I am trying to lash everything together right now and am obviously missing a key point here.

Thanks in advance.

Whatty

mons.dbcp.BasicDataSourcequot;gt;
lt;property name=quot;driverClassNamequot; value=quot;com.sybase.jdbc3.jdbc.SybDriverquot; /gt;
lt;property name=quot;uclquot; value=quot;jdbc:sybase:Tds:lt;value omittedgt;:lt;value omittedgt;/lt;value omittedgt;quot; /gt;
lt;property name=quot;usernamequot; value=quot;lt;value omittedgt;quot; /gt;
lt;property name=quot;passwordquot; value=quot;lt;value omittedgt;quot; /gt;
lt;/beangt;

lt;bean id=quot;MS-2K5quot; class=quot;org.apache.commons.dbcp.BasicDataSourcequot;gt;
lt;property name=quot;driverClassNamequot; value=quot;com.microsoft.sqlserver.jdbc.SQLServerDriverquot; /gt;
lt;property name=quot;uclquot; value=quot;jdbc:sqlserver--lt;value omittedgt;;DatabaseName=lt;value omittedgt;quot; /gt;
lt;property name=quot;usernamequot; value=quot;lt;value omittedgt;quot; /gt;
lt;property name=quot;passwordquot; value=quot;lt;value omittedgt;quot; /gt;
lt;/beangt;

lt;bean id=quot;MS-2K8quot; class=quot;org.apache.commons.dbcp.BasicDataSourcequot;gt;
lt;property name=quot;driverClassNamequot; value=quot;com.microsoft.sqlserver.jdbc.SQLServerDriverquot; /gt;
lt;property name=quot;uclquot; value=quot;jdbc:sqlserver--lt;value omittedgt;;DatabaseName=lt;value omittedgt;quot; /gt;
lt;property name=quot;usernamequot; value=quot;lt;value omittedgt;quot; /gt;
lt;property name=quot;passwordquot; value=quot;lt;value omittedgt;quot; /gt;
lt;/beangt;

lt;bean id=quot;DEVSessionFactoryquot; class=quot;org..orm.hibernate3.LocalSessionFactoryBeanquot;gt;
lt;property name=quot;dataSourcequot; ref=quot;DEVquot; /gt;
lt;property name=quot;mappingResourcesquot;gt;
lt;listgt;
lt;valuegt;UserType.hbm.xmllt;/valuegt;
lt;/listgt;
lt;/propertygt;
lt;/beangt;

lt;bean id=quot;MS-2K5SessionFactoryquot; class=quot;org..orm.hibernate3.LocalSessionFactoryBeanquot;gt;
lt;property name=quot;dataSourcequot; ref=quot;MS-2K5quot; /gt;
lt;property name=quot;mappingResourcesquot;gt;
lt;listgt;
lt;valuegt;UserType.hbm.xmllt;/valuegt;
lt;/listgt;
lt;/propertygt;
lt;/beangt;

lt;bean id=quot;MS-2K8SessionFactoryquot; class=quot;org..orm.hibernate3.LocalSessionFactoryBeanquot;gt;
lt;property name=quot;dataSourcequot; ref=quot;MS-2K8quot; /gt;
lt;property name=quot;mappingResourcesquot;gt;
lt;listgt;
lt;valuegt;UserType.hbm.xmllt;/valuegt;
lt;/listgt;
lt;/propertygt;
lt;/beangt;

lt;bean id=quot;dataSourcesquot; class=quot;com.purefacts.util..ContextSwappableTargetSourcequot;gt;
lt;constructor-arg type=quot;java.lang.Classquot; value=quot;org..orm.hibernate3.LocalSessionFactoryBeanquot;gt;lt;/constructor-arggt;
lt;property name=quot;targetsquot;gt;
lt;mapgt;
lt;entry key=quot;DEVquot; value-ref=quot;DEVSessionFactoryquot;/gt;
lt;entry key=quot;MS-2K5quot; value-ref=quot;MS-2K5SessionFactoryquot;/gt;
lt;entry key=quot;MS-2K8quot; value-ref=quot;MS-2K8SessionFactoryquot;/gt;
lt;/mapgt;
lt;/propertygt;

lt;/beangt;

lt;bean id=quot;userTypeDAOquot; class=quot;com.purefacts.db.hibernate.implementation.UserTypeDAOImplquot;gt;
lt;property name=quot;sessionFactoryquot; ref=quot;DEVSessionFactoryquot; /gt;
lt;property name=quot;dataSourcesquot; ref=quot;dataSourcesquot; /gt;
lt;/beangt;

lt;/beansgt;
I passed my ContextSwappableTargetSource down to my DAO object but when I try to get the session from the LocalSessionFactoryBean I can't seem to get the actual session object required.

Excerpt from my DAO object.Code:   /**    * Retrieve a UserType objects from the database for the given ID.    *    * @return a UserType object.    *     * @throws DataAccessException An exception is thrown if some error occurs accessing the data.    */   public IUserType getUserTypeByID(int id) throws DataAccessException {       Session session = this.getSession();       // Query query = session.createQuery(quot;from UserTypeImplquot;);       Query query = session.getNamedQuery(quot;get_user_type_by_idquot;);       query.setParameter(0, id);       IUserType result = (IUserType)query.list().get(0);       return result;   }
   public void setDataSources(ContextSwappableTargetSource dataSources) {       this.dataSources = dataSources;   }      public ContextSwappableTargetSource getDataSources() {       return this.dataSources;   }
was looking to replace the getSession call with something to the target return from ContextSwappableTargetSource.

Have I missed a key point here or am I out in left field on this one.

Thanks in advance.

Whatty

mons.dbcp.BasicDataSourcequot;gt;
lt;property name=quot;driverClassNamequot; value=quot;com.sybase.jdbc3.jdbc.SybDriverquot; /gt;
lt;property name=quot;uclquot; value=quot;jdbc:sybase:Tds:lt;value omittedgt;:lt;value omittedgt;/lt;value omittedgt;quot; /gt;
lt;property name=quot;usernamequot; value=quot;lt;value omittedgt;quot; /gt;
lt;property name=quot;passwordquot; value=quot;lt;value omittedgt;quot; /gt;
lt;/beangt;

lt;bean id=quot;MS-2K5quot; class=quot;org.apache.commons.dbcp.BasicDataSourcequot;gt;
lt;property name=quot;driverClassNamequot; value=quot;com.microsoft.sqlserver.jdbc.SQLServerDriverquot; /gt;
lt;property name=quot;uclquot; value=quot;jdbc:sqlserver--lt;value omittedgt;;DatabaseName=lt;value omittedgt;quot; /gt;
lt;property name=quot;usernamequot; value=quot;lt;value omittedgt;quot; /gt;
lt;property name=quot;passwordquot; value=quot;lt;value omittedgt;quot; /gt;
lt;/beangt;

lt;bean id=quot;MS-2K8quot; class=quot;org.apache.commons.dbcp.BasicDataSourcequot;gt;
lt;property name=quot;driverClassNamequot; value=quot;com.microsoft.sqlserver.jdbc.SQLServerDriverquot; /gt;
lt;property name=quot;uclquot; value=quot;jdbc:sqlserver--lt;value omittedgt;;DatabaseName=lt;value omittedgt;quot; /gt;
lt;property name=quot;usernamequot; value=quot;lt;value omittedgt;quot; /gt;
lt;property name=quot;passwordquot; value=quot;lt;value omittedgt;quot; /gt;
lt;/beangt;

lt;bean id=quot;sessionFactoryquot; class=quot;org..orm.hibernate3.LocalSessionFactoryBeanquot;gt;
lt;property name=quot;dataSourcequot; ref=quot;dataSourcesquot; /gt;
lt;property name=quot;mappingResourcesquot;gt;
lt;listgt;
lt;valuegt;UserType.hbm.xmllt;/valuegt;
lt;/listgt;
lt;/propertygt;
lt;/beangt;

lt;bean id=quot;dataSourcesquot; class=quot;com.purefacts.util..ContextSwappableTargetSourcequot;gt;
lt;constructor-arg type=quot;java.lang.Classquot; value=quot;javax.sql.DataSourcequot;gt;lt;/constructor-arggt;
lt;property name=quot;targetsquot;gt;
lt;mapgt;
lt;entry key=quot;DEVquot; value-ref=quot;DEVquot;/gt;
lt;entry key=quot;MS-2K5quot; value-ref=quot;MS-2K5quot;/gt;
lt;entry key=quot;MS-2K8quot; value-ref=quot;MS-2K8quot;/gt;
lt;/mapgt;
lt;/propertygt;

lt;/beangt;

lt;bean id=quot;userTypeDAOquot; class=quot;com.purefacts.db.hibernate.implementation.UserTypeDAOImplquot;gt;
lt;property name=quot;sessionFactoryquot; ref=quot;sessionFactoryquot; /gt;
lt;property name=quot;dataSourcesquot; ref=quot;dataSourcesquot; /gt;
lt;/beangt;

lt;/beansgt;
That should be more or less your configuration. Your daos shouldn't know about a different sessionfactory/datasource that is all transparantly handled by the TargetSource.

Now that I am looking at it again (having stepped away for the rest of the morning) and your example, that definitely makes a lot more sense.

I will give it a shot.

Thanks

Whatty

This approach makes a lot more sense but the configuration is wrong somewhere, I can't set the dataSource to be that type of object, it is expecting a javax.sql.datasource not the ContextSwappableTargetSource

Which version  of the framework is this based on, I am using Spring 2.5.6.Code:
Caused by: java.lang.IllegalArgumentException: Cannot convert value of type [com.purefacts.util..ContextSwappableTargetSource] to required type [javax.sql.DataSource] for property 'dataSource': no matching editors or conversion strategy found
at org..beans.TypeConverterDelegate.convertIfNecessary(TypeConverterDelegate.java:231)
at org..beans.TypeConverterDelegate.convertIfNecessary(TypeConverterDelegate.java:138)
at org..beans.BeanWrapperImpl.convertForProperty(BeanWrapperImpl.java:386)
... 59 moreSorry my mistake, you will have to wrap the ContextSwappableTargetSource in a ProxyFactoryBean (that will detect the fact that it it actually a TargetSource). That should be also on my blog...