How to connect an external database with Liferay service builder in a few easy steps and Integrate the SP(Stored Procedure) in the external DB.

In this blog, We will see how to configure an external database with Liferay service builder and How to execute Stored Procedure in the external database with the same service builder in a few simple steps.

There will be two phases in the blog, One for the Connection of external database and another one for the executing stored procedure.

Let’s start with Phase one as below.

Phase 1 - Connect a Liferay service builder with an external Database.

In this phase, We will see how to configure an external database with a Liferay service builder.

Step 1: Create a simple service builder in Eclipse/Liferay Studio as below.

Step 2: Open the service.xml file of the same service builder, Which is available at the entity level and provide your custom data-source in it as shown below.

  • Data-source attribute used for custom data-source as data-source="externalDataSource".

As we are connecting external databases it’s required to create custom data-source with custom configuration.

Step 3: Now we will create an ext-spring.xml file in our service builder on the below path.

  • Path to create ext-sprint.xml file: META-INF/spring
  • Inside it we can configure our database details and custom data source as below.

Step 4: Now add below properties in portal-ext.properties file, It will be used to configure external database details

#EXT - DB Configuration
jdbc.ext.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.ext.username=test
jdbc.ext.password=*******
jdbc.ext.url=jdbc:sqlserver://host-name:1433;databaseName=externalExtDB;

Please make sure you have restarted the server after setting the above properties in the portal-ext file.

After restarting the server Build the service, Refresh the Gradle and deploy the same in the application server. And check whether the deployables are deployed or not and it should be in active state.

Now we are able to access the service in other modules after adding dependencies for the same.

There are also other ways to connect an external database like JNDI, Spring Configuration(XML Based configuration of an external database).

Please find below links for the JNDI and Spring Configuration for the external database connection:

Phase 2: Execute a Stored Procedure in the external database through Liferay service builder.

In this phase, we will see how to execute Stored Procedure through FinderImpl class in service builder.

Step 1: Create a Stored Procedure in your database and check whether it is working as expected or not.

I have created below SP in my database to retrieve the data.

CREATE PROCEDURE retrivePolicybaseDetails @PolicyNo nvarchar(100)
AS
	SELECT PB.[ccs_PlanCode],PB.[ccs_policynumber] FROM [ccs_policyBase] as PB where PB.[ccs_policynumber] = @PolicyNo; 

Step 2: Try to execute the SP to check if it is working as expected or not.

EXEC retrivePolicybaseDetails @PolicyNo = '***';

Now, Let’s move to the service builder side.

Step 3: Create an Entity in service.xml file of your service builder for storing the data in model class.

I have created one entity as PolicyDetails,you can create as per your requirement.

Step 4: Create EntityFinderImpl class on the below path, As I have to fetch PolicyDetails data, I have created PolicyDetailsFinderImpl in my service builder.

  • Path to create ****FinderImplClass: com.test.app.ext.db.service.persistence.impl.
  • After creating the class extend the BasePersistence.
  • Also implement the ModelFinder interface, in my case I have implemented PolicyDetailsFinder.
import com.test.app.ext.db.model.PolicyDetails;
import com.test.app.ext.db.service.persistence.PolicyDetailsFinder;
import com.liferay.portal.kernel.service.persistence.impl.BasePersistenceImpl;

public class PolicyDetailsFinderImpl extends BasePersistenceImpl implements PolicyDetailsFinder {

}

Initially, the PolicyDetailsFinder class will give an error for cannot resolve type.

After creating a class as above Build th service and refresh the Gradle, it will resolve the error and the FinderImplt class will look as below.

import java.util.List;
import com.test.app.ext.db.model.PolicyDetails;
import com.test.app.ext.db.service.persistence.PolicyDetailsFinder;
public class PolicyDetailsFinderImpl extends PolicyDetailsFinderBaseImpl implements PolicyDetailsFinder {
			
}

Step 5: Create a method inside the FinderImple class to execute the stored procedure and set the data in the model class.

/**
* Method used for Execute the Stored Procedure in External Database and Populate the
* data in PolicyDetails Model Class(Stored Procedure has been created in External db
* with retrivePolicybaseDetails name).
* 
* @author fazal.babaria
* @return List 
*/
public List findPolicyDetailsByPolicyNumber(String policyNo) {	
	try {
		Session session = openSession(); //DB Connection
		SQLQuery query = session.createSQLQuery("EXEC retrivePolicybaseDetails @PolicyNo = '" + policyNo + "'");  //Execute the Stored Procedure
		Iterator iterator = query.list().iterator();   //Fetch Data through Iterator
		List policyDetailsList = new ArrayList<>();  // For Return the data
			while (iterator.hasNext()) {   // Iterate the iterator
				Object[] tuple = (Object[]) iterator.next();  // Parse in Object array
				PolicyDetails policyDetails = policyDetailsPersistence.create(CounterLocalServiceUtil.increment() + "");  // Model Class object
				policyDetails.setProductCode(tuple[0] + "");
				policyDetails.setPolicyNumber(tuple[1] + ""); 
			}
			return policyDetailsList;
	} catch (Exception exp) {
		exp.printStackTrace();
		log.error("Error while getting policy details from CRM DB: " + exp.getMessage());
	}
	return null;
}

In the above method, we are retrieving the through Iterator and Object[], While compile-time is allowed to Type-Cast the result but runtime it will give multiple frustrating errors and which is near to impossible to typecast the object to a particular model.

Please comment if you find any way to typecast in an object into the Model class, Thanks in advance :).

So above is the best way to set the data in the model class.

Now build the service and refresh the gradle.

Step 6: Now create a method in LocalServiceImpl so that it will be available in LocalServiceUtil class.

public List findPolicyDetailsByPolicyNumber(String policyNo) {
			return policyDetailsFinder.findPolicyDetailsByPolicyNumber(policyNo);
}

After creating the above method, Build the service and refresh the Gradle, The method will be accessible in any module in which you have added dependency of service.

Thanks for reading my blog :).

Blogs

This is how I solved the casting issue:

    List<Entity> list = null;

    SQLQuery query = session.createSQLQuery("THE QUERY");

    query.addEntity("Entity",EntityImpl.class);

    list = (List<Entity>) q.list();

    return list;

hi, it's very helpful.

but why don't you just create a mothad in LocalServiceUtil for example:

    Connection con = XXXPersistence.getDataSource().getConnection();

    do something.......

    finally,  DataAccess.cleanUp

and do jsut like normal java.