Liferay 7 External Database ServiceBuilder Integration

In this tutorial, we will see how to configure external database via service builder. If your application wants to communicate to external database to load the data into liferay system. you can follow below approaches

1. Custom Implementation : you can use hibernate or native JDBC to load the data into portal system.
2. Service Builder Implementation:  Service builder generates you the business layer that includes cache, finder impl to load the data.

In this tutorial, we will see the Service builder approach to connect to external database in liferay.

1. Create New New Project of Service builder
2. Give package path like below and leave Component class name value as empty
3. Create Service.xml file like below: At the entity level provide
   • data-source attribute to custom data source.. let’s say “extDataSource”
   • table as Country
   • at column level, update db-name value with corresponding column name

   • <service-builder package-path="org.javasavvy.external" >
      <namespace>jay</namespace>
      <entity local-service="true" name="Country" table="country" data-source="extDataSource" 
      remote-service="false" uuid="false">
      <column name="countryId" db-name="countryId" primary="true" type="long" />
      <column name="countryName" db-name="countryName" type="String" />
      </entity>
     </service-builder>

4. In this Step, we will create ext-spring.xml file in META-INF/spring folder and you can configure data base connection details in the following  approaches:

via Portal-ext Properties: 

• portlet-ext properties:  This is recommended approach to configure Database connection details in portlet-ext properties

  1. jdbc.external.url=jdbc:mysql://localhost:3306/external
     jdbc.external.driverClassName=com.mysql.jdbc.Driver
     jdbc.external.username=root
     jdbc.external.password=root

• ext-spring.xml: In the bean “liferayDataSourceImpl”, just set the properties “propertyPrefix” to “jdbc.external.”
• Maku sure you restart the server after setting properties in portal-ext.properties

  1. <?xml version="1.0" encoding="UTF-8"?>
     <beans default-destroy-method="destroy" default-init-method="afterPropertiesSet"
      xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">
     
      <bean class="com.liferay.portal.dao.jdbc.spring.DataSourceFactoryBean" id="liferayDataSourceImpl">
          <property name="propertyPrefix" value="jdbc.external." />
      </bean>
     
      <bean class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy" id="liferayDataSource">
      <property name="targetDataSource" ref="liferayDataSourceImpl" />
      </bean>
      
      <alias alias="extDataSource" name="liferayDataSource" />
      
     </beans>

•  through spring configuration: In this, approach, we will provide the database connection details in the bean it self or you can use spring property placeholder bean to load from jdbc.properties file.  Do not change the “liferayDataSource” bean Id as it is being using by hibernate session factory and it will give you bean creation error. Liferay is restricted this bean name to liferayDataSource, not sure why they configured like this.

1. • <?xml version="1.0" encoding="UTF-8"?>
     <beans default-destroy-method="destroy" default-init-method="afterPropertiesSet"
      xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">
      
      <bean class="com.liferay.portal.dao.jdbc.spring.DataSourceFactoryBean" id="dataSourceBean">
          <property name="propertyPrefix" value="jdbc." />
          <property name="properties">
             <props>
              <prop key="jdbc.driverClassName">com.mysql.jdbc.Driver</prop>
              <prop key="jdbc.url">jdbc:mysql://localhost:3306/external</prop>
              <prop key="jdbc.username">root</prop>
            <prop key="jdbc.password">root</prop>
          </props>
      </property>
      </bean>
      <bean class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy" id="liferayDataSource">
           <property name="targetDataSource" ref="dataSourceBean" />
      </bean>
      <alias alias="extDataSource" name="liferayDataSource" />
     </beans>

     Now execute buildService gradle task and deploy the modules. you can use this as gradle dependency in other modules and invoke the CountryLocalServiceUtil.

For JNDI datasource integration, Click on below tutorial:

Liferay 7 External Database using JNDI

In this tutorial we will see how to integrate with external database using JNDI with Service Builder. Installations: Liferay Eclipse IDE M1 Liferay 7 Tomcat GA 3 Create new database scheme external and create…