Hi Friends,
As we are using Liferay services builder to generate an Entity and other service classes for our database entities. We are able to generate fetching models and CRUID operations.
But some time we need to write complex join queries to fetch the data and listing on some pages.
So for that we can write custom queries. In that case we need to define some entity in Service.XML. But we can't define the field from multiple entities in single entity object definition.
So another way is to create Database views and use those views in our service builders. As we can use this only to fetch and display data, this approach will not work for create and update operations.
So to use views we need to follow below steps.
1) Create a database view like
CREATE view EmployeeDetails AS
SELECT NEWID() AS Id,EmployeeID, EmployeeName, DepartmentName from Employee emp join department dept on dept.employeeId=emp.employeeID
Why we use (NEWID() AS Id)?
Because in service builder we always need a fields which should be Unique and Primary key. So while using view we can use this Id as primary key.
2) Then we need to map this view with Entity in service.xml
<entity name="EmployeeDetails" local-service="true" table="V_EmployeeDetails" cache-enabled="false">
<column name="ID" type="String" primary="true"></column>
<column name="EmployeeID" type="long" db-name="EmployeeID"></column>
<column name="EmployeeName" type="String" db-name="EmployeeName"></column>
<column name="DepartmentName" type="String" db-name="DepartmentName"></column>
</entity>
As view is dependent on different different tables so to get the updated information we should keep cache-enabled="false".
Hope this will be helpful to them those are searching for database Views support in Liferay Service Builder.
-- Ajay Saharan
As we are using Liferay services builder to generate an Entity and other service classes for our database entities. We are able to generate fetching models and CRUID operations.
But some time we need to write complex join queries to fetch the data and listing on some pages.
So for that we can write custom queries. In that case we need to define some entity in Service.XML. But we can't define the field from multiple entities in single entity object definition.
So another way is to create Database views and use those views in our service builders. As we can use this only to fetch and display data, this approach will not work for create and update operations.
So to use views we need to follow below steps.
1) Create a database view like
CREATE view EmployeeDetails AS
SELECT NEWID() AS Id,EmployeeID, EmployeeName, DepartmentName from Employee emp join department dept on dept.employeeId=emp.employeeID
Why we use (NEWID() AS Id)?
Because in service builder we always need a fields which should be Unique and Primary key. So while using view we can use this Id as primary key.
2) Then we need to map this view with Entity in service.xml
<entity name="EmployeeDetails" local-service="true" table="V_EmployeeDetails" cache-enabled="false">
<column name="ID" type="String" primary="true"></column>
<column name="EmployeeID" type="long" db-name="EmployeeID"></column>
<column name="EmployeeName" type="String" db-name="EmployeeName"></column>
<column name="DepartmentName" type="String" db-name="DepartmentName"></column>
</entity>
As view is dependent on different different tables so to get the updated information we should keep cache-enabled="false".
Hope this will be helpful to them those are searching for database Views support in Liferay Service Builder.
-- Ajay Saharan
