« Back to FrontPage

Working with Database Views in Liferay

Couple of months ago I had to create Database View with LR (Liferay) using service builder. Searching on web I couldn’t find any tutorial or solution. LR don’t know anything like views of databases. After some night work I came to know that it is very easy, as View is just an entity in DB so by fooling (not actually fooling) service builder that our View is an entity you can very easily create service classes and other functionalities of View via service builder.

  Trick is to just create an entity in service.xml instead of view, run your service builder, which will create all necessary classes for that entity (actually view), run your LR project which will create this entity / view in actual DB. After this delete the created entity and run manual sql to create view of same name as it was entity in LR. Now LR will consider this view as an entity and enjoy your rest of work.

  Let’s have a simple but practical example in which there will be two entities and one view.

I am using LR 6.1 JSF for front end, tomcat 7, and MySQL as DB for this example.

  Two entities,

Citizen (Citizen_Id, First_Name, Middle_Name, Last_Name, Mobile, Marital_Status_Id) where Marital_Status_Id is you can say the FK from Marital_Status entity, I am handling FK at front end in this example.

Marital_Status (Marital_Status_Id, Marital_Status)

and one View is our discussion point

Citizen_View (Citizen_Id, Complete_Name, Martal_Status) where Complete_Name will be concatenation of First_Name, Middle_Name and Last_Name of Citizen entity where as Marital_Status will come from Marital_Status entity.

  Step 1:

Create service.xml via service builder. Here is the complete service.xml for our example.

  <?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE service-builder PUBLIC "-LiferayDTD Service Builder 6.1.0EN" "http://www.liferay.com/dtd/liferay-service-builder_6_1_0.dtd">

<service-builder package-path="com.view.test">



    <entity name="Citizen" local-service="true" remote-service="true">             <!-- PK fields -->             <column name="Citizen_Id" type="long" primary="true" />             <column name="First_Name" type="String" />            <column name="Middle_Name" type="String" />            <column name="Last_Name" type="String" />            <column name="Mobile" type="String" />            <column name="Marital_Status_Id" type="long"/>      </entity>          

<entity name="Marital_Status" local-service="true" remote-service="true">      

     <!-- PK fields -->            <column name="Marital_Status_Id" type="long" primary="true" />                       <column name="Marital_Status" type="String" />      </entity>           <entity name="Citizen_View" local-service="true" remote-service="true">             <!-- PK fields -->           <column name="Citizen_Id" type="long" primary="true" />                       <column name="Complete_Name" type="String" />            <column name="Marital_Status" type="String" />      </entity>          


  Run your service builder, which will create all service classes for our entities.

Step 2:

Run your server, upon successful run LR will create our desired entities in Database.

Step 3:

Here is the tricky point, drop your entity named “Citizen_View”, and run following sql command in your database.

  CREATE VIEW abc_Citizen_View AS

      SELECT         Citizen_Id ,  CONCAT (First_Name, ' ' ,   Middle_Name, ' ' ,   Last_Name) Complete_Name ,      m.Marital_status      FROM  abc_citizen  c         INNER JOIN abc_marital_status m      ON( m.Marital_Status_ID = c.Marital_Status_ID ); 

Which will create View with the same name as was your entity in DB, if you will not delete the entity first this command will give you error like entity already exist with same name.

  Step 4:

Populate your two entities, “Citizen” and “Marital_Status”, you will see that your view is already populated according to entries of above two tables.

   Done, just code your required logic as data is available in view

  Following is code proof which works with our example if we run our portlet.


public String getViewData() throws SystemException, PortalException{

                       List<Citizen_View> viewList = Citizen_ViewLocalServiceUtil.getCitizen_Views            (0, Citizen_ViewLocalServiceUtil.getCitizen_ViewsCount());                       System.out.println("Size of Citizen_View List "+viewList.size());           for (Citizen_View citizen_View : viewList) {                  System.out.println("Name "+citizen_View.getComplete_Name());                  System.out.println("Marital Status: "+ citizen_View.getMarital_Status());            }                       / another way/                       Citizen_View view = Citizen_ViewLocalServiceUtil.getCitizen_View(1);            System.out.println("");            System.out.println("Name "+view.getComplete_Name());            System.out.println("Marital Status: "+ view.getMarital_Status());                                  return "";                 


  If you feel any problem regarding above concepts feel free to ask.


0 Attachments
Average (2 Votes)
The average rating is 3.0 stars out of 5.
Threaded Replies Author Date
Very interesting article. Thanks! Jeffrey P Handa August 28, 2015 2:35 PM

Very interesting article. Thanks!
Posted on 8/28/15 2:35 PM.