Accessing Database from Service other than the default Liferay Database

Using ServiceBuilder in Liferay by default creates and uses tables in the same database which liferay is by default configured to use. In order to use a different database through your service builder do the following:

Create ext-spring.xml file in /src/META-INF folder and add the following:

<bean id="customDataSource"
 class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
      <property name="targetDataSource">
   <bean class="com.liferay.portal.dao.jdbc.util.DataSourceFactoryBean">
       <property name="propertyPrefix" value="jdbc.custom.default." />
   </bean>
      </property>
</bean>
<bean id="liferayHibernateSessionFactory"
   class="com.liferay.portal.spring.hibernate.PortletHibernateConfiguration">
     <property name="dataSource" ref="customDataSource" />
</bean>

Make the following entry in your portal-ext.properties file

jdbc.custom.default.driverClassName=com.mysql.jdbc.Driver

jdbc.custom.default.url=jdbc:mysql://127.0.0.1/lportal_custom?useUnicode=true&characterEncoding=UTF-8&useFastDateParsing=false

jdbc.custom.default.username=root

jdbc.custom.default.password=root

Do the following in service.xml file

While defining the entity, set the data-source as customDataSource as follows:

<entity name="Customer" local-service="true" remote-service="false" data-source="customDataSource">

This will ensure that all the calls being made, will be made to the custom (non-liferay) database. Now the next important point

The aforementioned will not help in manipulating data. For e.g. if you try to fire an update or delete your service will still hit the liferay database. Also note that your transactions will not work. For transactionManager to work such that from the <Entity>LocalServiceImpl if you throw PortalException or SystemException the transaction must be rolled back, you need to make some additional changes. Here is the complete configuration of ext-spring.xml file (you don't need to make modifications in any other .xml file).  I have tested the following configuration with Liferay 6.1 GA2 and it works very well (ensure that you configure data-source, session-factory and tx-manager as customDataSource, customSessionFactory and customTransactionManager respectively in service.xml file for your Entities)

    <bean id="customDataSource"
        class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
        <property name="targetDataSource">
            <bean class="com.liferay.portal.dao.jdbc.util.DataSourceFactoryBean">
                <property name="propertyPrefix" value="jdbc.custom.default." />
            </bean>
        </property>
    </bean>

    <bean id="customHibernateSessionFactory" 
        class="com.liferay.portal.kernel.spring.util.SpringFactoryUtil"
        factory-method="newBean">
        <constructor-arg
            value="com.liferay.portal.spring.hibernate.PortletHibernateConfiguration" />
        <constructor-arg>
            <map>
                <entry key="dataSource" value-ref="customDataSource" />
            </map>
        </constructor-arg>
    </bean>
    <bean id="customSessionFactory" class="com.liferay.portal.kernel.spring.util.SpringFactoryUtil"
        factory-method="newBean">
        <constructor-arg
            value="com.liferay.portal.dao.orm.hibernate.PortletSessionFactoryImpl" />
        <constructor-arg>
            <map>
                <entry key="dataSource" value-ref="customDataSource" />
                <entry key="sessionFactoryClassLoader" value-ref="portletClassLoader" />
                <entry key="sessionFactoryImplementor" value-ref="customHibernateSessionFactory" />
            </map>
        </constructor-arg>
    </bean>
    
    <bean id="transactionAdvice" class="com.liferay.portal.kernel.spring.util.SpringFactoryUtil"
          factory-method="newBean">
        <constructor-arg value="com.liferay.portal.spring.transaction.TransactionInterceptor" />
        <constructor-arg>
            <map>
                <entry key="platformTransactionManager" value-ref="customTransactionManager" />
                <entry key="transactionAttributeSource" value-ref="transactionAttributeSource" />
            </map>
        </constructor-arg>
    </bean>
    
    

    <bean id="customTransactionManager"
        class="org.springframework.orm.hibernate3.HibernateTransactionManager"
        lazy-init="true">
        <property name="dataSource">
            <ref bean="customDataSource" />
        </property>
        <property name="sessionFactory">
            <ref bean="customHibernateSessionFactory" />
        </property>
    </bean>