Database connection pool sharing between portal and portlets

Liferay ROOT uses a database connection pool that manages database connections for all data-related requests inside the portal. Most portlets depend on Liferay's service API, so implicitly they depend on the same pool. However, it is possible that you have to persist your own entities inside a portlet, independent of the existing Liferay entities. E.g. you want to store a Dossier entity with Hibernate. In this case, you have to take care of the persistence layer yourself. Concerning connection management, you have 2 choices:

• Let your portlet define and use its own connection pool. A disadvantage is that there will be a separate pool for each portlet. Each pool has to reserve a certain amount of memory.
• Let your portlet use the connection pool of the portal. In this case there's a central place of configuration and the sizing and tweaking of the pool can be done once and for all.

The second approach is clearly the better one. Now, there are 2 ways of sharing the connection pool between portal and portlet. The first one uses merely Spring configuration to assign Liferay's data source to a SessionFactory or EntityManagerFactory. The second approach exposes the portal data source through JNDI, which can then be referenced easily as well.

Approach 1: Spring configuration

In this configuration, database connection properties live inside portal-ext.properties as usual:

portal-ext.properties

jdbc.default.driverClassName=com.mysql.jdbc.Driver jdbc.default.url=jdbc:mysql://db_prod/liferay_prod?useUnicode=true&characterEncoding=UTF-8&useFastDateParsing=false&autoReconnectForPools=true&dumpQueriesOnException=true&logSlowQueries=true&explainSlowQueries=true jdbc.default.username=sa_aca jdbc.default.password=tesla jdbc.default.testConnectionOnCheckout=true jdbc.default.preferredTestQuery=SELECT 1; jdbc.default.acquireIncrement=1 jdbc.default.maxIdleTime=10800 jdbc.default.maxConnectionAge=14400 jdbc.default.numHelperThreads=20

Beware!

By default, Liferay uses C3PO for connection pooling. This means that you can only use C3PO configuration parameters, such as preferredTestquery. Check here for a parameter overview. If you'd like to use another mechanism, set the jdbc.default.liferay.pool.provider parameter to dbcp or tomcat.

Internally, Liferay will encapsulate these properties into a datasource object. The datasource is exposed by a static factory called InfrastructureUtil. Given that you use a Spring setup, you can configure something like this in the context configuration of your portlet:

applicationContext.xml

< beans ...>      < context:component-scan base-package = "be.vlaanderen" />        < bean id = "dataSource" class = "com.liferay.portal.kernel.util.InfrastructureUtil" factory-method = "getDataSource" />        < bean id = "sessionFactory" class = "org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean" >          < property name = "dataSource" ref = "dataSource" />          < property name = "packagesToScan" value = "be.vlaanderen.contact.hibernate.model" />      </ bean >        < tx:annotation-driven />      < bean id = "transactionManager" class = "org.springframework.orm.hibernate3.HibernateTransactionManager" >          < property name = "sessionFactory" ref = "sessionFactory" />      </ bean > </ beans >

Let's go through this configuration line by line:

• First, we do a component scan for @Named or @Component classes. These beans are automatically added to the context.
• Next, we define a datasource. Notice that we can use InfrastructureUtil of Liferay to get a DataSource object based on the connection parameters specified in the portal-ext.properties.
• This datasource is then assigned to a SessionFactoryBean. This could as well be a LocalContainerEntityManagerBean, if you prefer using JPA's EntityManager instead of Hibernate's Session syntax.
• Last but not least, you'd want to enable transaction handling. For this, enable annotation-driven transaction management and configure Spring's transaction manager with the session factory.

Now you can write a DAO or Repository like this:

DossierDAO

@Named @Transactional (readOnly= true ) public class DossierController implements Serializable, IDossierController {        @Inject private SessionFactory sessionFactory;        public List<Dossier> getDossiers( long companyId, boolean dirty) {          Criteria criteria = sessionFactory.getCurrentSession().createCriteria(Dossier. class );          criteria.add(Restrictions.eq( "companyId" , companyId));          criteria.add(Restrictions.eq( "dirty" , dirty));          return criteria.list();      }        @Transactional      public void deleteContact(Dossier dossier) {          sessionFactory.getCurrentSession().delete(dossier);      } }

Beware for the following:

• Always define an interface for your DAO if you're working with transactions and inject the interface on the beans that want to use the DAO.
• Set readOnly=true by default on class-level and redefine @Transactional on methods that are not read-only (CUD).

Approach 2: JNDI

This second approach is especially useful if there are multiple data sources to connect to. E.g. some of your portlets might connect to a different database to retrieve their data. First, you'll need to define all datasources inside the context.xml of Tomcat:

tomcat/conf/context.xml

< Context >      < WatchedResource >WEB-INF/web.xml</ WatchedResource >        < Resource name = "jdbc/liferay"          auth = "Container"          type = "javax.sql.DataSource"          maxActive = "40"          maxIdle = "20"          maxWait = "1000"          username = "%db_user%"          password = "%db_password%"          driverClassName = "org.postgresql.Driver"          url = "jdbc:postgresql://%db_host%:%db_port%/%db_name%"          validationQuery = "select version();"      />        < Resource name = "jdbc/other" auth = "Container" type = "javax.sql.DataSource"            ....      /> </ Context >

Beware!

Tomcat has its own JDBC connection pooling mechanism with its own set of parameters, such as validationQuery. Check this page for more information and an overview of configuration parameters. If you want to use another DB connection pooling mechanism (e.g. C3PO), set the type of the Resource tag to com.mchange.v2.c3p0.ComboPooledDataSource.

Now your portal-ext.properties can reference the JNDI name of the datasource, instead of configuring all properties itself. All other properties will be silently ignored.

portal-ext.properties

jdbc.default.jndi.name=jdbc/liferay

In the Spring configuration of your portlet, reference the necessary datasource(s) and assign them to a Spring bean.

applicationContext.xml

< beans ...>      < bean id = "liferayDataSource" class = "org.springframework.jndi.JndiObjectFactoryBean" >          < property name = "jndiName" value = "java:comp/env/jdbc/liferay" />      </ bean >      < bean id = "otherDataSource" class = "org.springframework.jndi.JndiObjectFactoryBean" >          < property name = "jndiName" value = "java:comp/env/jdbc/other" />      </ bean > </ beans >

Conclusion

• Know for sure which connection pooling mechanism is active. Tune the configuration parameters appropriately.
• Use JNDI if there are multiple databases to connect to or if you've deployed other web applications inside Liferay's Tomcat.
• Use InfrastructureUtil to quickly get a reference to the portal-level datasource object.

[Roberto Tellado]

I work with approach 1, but i have problemas with getCurrentSessionFactory. Throw this exception: No Hibernate Session bound to thread, and configuration does not allow creation of non-transactional one here. Do you know why? Thanks.