Connect external DB in Liferay

Sometimes, we need connect to external DB in Liferay. How to do it?

JNDI Name

In tomcat server:

In server.xml file in Server in Eclispe:

        <GlobalNamingResources>

              <!-- Editable user database that can also be used by UserDatabaseRealm

                     to authenticate users -->

              <Resource auth="Container" description="User database that can be updated and saved"

                     factory="org.apache.catalina.users.MemoryUserDatabaseFactory" name="UserDatabase"

                     pathname="conf/tomcat-users.xml" type="org.apache.catalina.UserDatabase" />

              <Resource description="My DataSource" driverClassName="oracle.jdbc.driver.OracleDriver"

                     maxActive="20" maxIdle="10" maxWait="5000" name="jdbc/mypool"

                     password="mypass" testOnBorrow="true" type="javax.sql.DataSource"

                     url="jdbc:oracle:thin:@localhost:1521:mydb" username="myname"

                     validationQuery="SELECT 1 FROM DUAL" />

       </GlobalNamingResources>

In context.xml file in Server in Eclispe:

<!-- Default set of monitored resources -->

    <WatchedResource>WEB-INF/web.xml</WatchedResource>

    <!-- Uncomment this to disable session persistence across Tomcat restarts -->

    <!--

    <Manager pathname="" />

    -->

   

    <ResourceLink global="jdbc/mmtpool" name="jdbc/mypool" type="javax.sql.DataSource"/>

    <!-- Uncomment this to enable Comet connection tacking (provides events

         on session expiration as well as webapp lifecycle) -->

    <!--

    <Valve className="org.apache.catalina.valves.CometConnectionManagerValve" />

    -->

The JNDI name highlighted in green must be the same with JNDI define in code.

Using Liferay util class and Hibnerate:

<!-- configuration for external db -->
    <bean id="myDataSourceTarget" class="com.liferay.portal.spring.jndi.JndiObjectFactoryBean"
        lazy-init="true">
        <property name="jndiName">
            <value>jdbc/mypool</value>
        </property>
    </bean>
    <bean id="myDataSource"
        class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
        <property name="targetDataSource">
            <ref bean="myDataSourceTarget" />
        </property>
    </bean>
    <bean id="myHibernateSessionFactory"
        class="com.liferay.portal.spring.hibernate.PortletHibernateConfiguration">
        <property name="dataSource">
            <ref bean="myDataSource" />
        </property>
    </bean>
    <bean id="mySessionFactory" class="com.liferay.portal.dao.orm.hibernate.SessionFactoryImpl">
        <property name="sessionFactoryImplementor">
            <ref bean="myHibernateSessionFactory" />
        </property>
    </bean>
    <bean id="myTransactionManager"
        class="org.springframework.orm.hibernate3.HibernateTransactionManager">
        <property name="dataSource">
            <ref bean="myDataSource" />
        </property>
        <property name="sessionFactory">
            <ref bean="myHibernateSessionFactory" />
        </property>
    </bean>

In *FinderImpl file, add function like this:

        public int countTicket(String status, String priority, String ticketNo, String assigneeId)
            throws SystemException {

        Session session = null;

        try {
            // open a new hibernate session in normal case when you are opening
            // session for same entity
            session = openSession();
            // pull out our query from default.xml, created earlier
            String sql = CustomSQLUtil.get(COUNT_TICKET);
            // create a SQLQuery object
            SQLQuery query = session.createSQLQuery(sql);

            // Get query position instance
            QueryPos qPos = QueryPos.getInstance(query);
            qPos.add(status);
            qPos.add(priority);
            qPos.add(ticketNo);
            qPos.add(assigneeId);
            query.addScalar("total", Type.INTEGER);
            List results = (List) query.list();

            Integer count = (Integer) results.get(0);
            return count;

        } catch (Exception e) {
            throw new SystemException(e);
        } finally {
            closeSession(session);
        }

*FinderImpl class should extends from BasePersistenceImpl<Your_Entity> and implements *Finder.

Using Spring util class and JPA:

<!-- DataSource -->
    <jee:jndi-lookup id="dataSourceTarget" jndi-name="jdbc/mypool" expected-type="javax.sql.DataSource" />
      
    <bean id="dataSource"
        class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
        <property name="targetDataSource" ref="dataSourceTarget" />
    </bean>
       
    <!-- Entity Manager Factory for Spring Data JPA -->
    <bean id="jpaVendorAdapter" class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
        <property name="showSql" value="false"/>
        <property name="generateDdl" value="false"/>
        <property name="database" value="ORACLE"/>
    </bean>
   
    <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="jpaVendorAdapter" ref="jpaVendorAdapter"/>
        <!-- spring based scanning for entity classes-->
        <property name="packagesToScan" value="com.example.dao.entity"/>
    </bean>
   

    <!-- Transaction Manager for Spring Data JPA -->
    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="entityManagerFactory"/>        
    </bean>
       
    <tx:annotation-driven transaction-manager="transactionManager"/>    

From now, we can use JPA Repository. Reference: http://www.petrikainulainen.net/spring-data-jpa-tutorial/ OR write your own query function, like below:

@PersistenceContext
protected EntityManager em;

public List getProgram() {

        String sql = "SELECT * FROM program";
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }