Monday, June 9, 2014

Scalars in Liferay Custom SQL / Custom SQL Scalar Implementation

Introduction:

Scalars is the one of the concept in Custom SQL instead fetching specific Model Object Type Data we can fetch data in the form List Object and the array contains the data of different tables and we don’t need load Model Object Implementation class for SQL Query object.

Scenarios:
  1. Select specific columnn in SQL Query
  2. Join Tables which are in different Plugin Contexts or in Portal Level
  3. Get the Count of the result Data

Before continue this article please read below articles.



Select specific columns in SQL Query

Generally when we use Custom SQL in the SQL query we need to select all the columns of tables in the query if we join multiple tables also we should select all the tables columns otherwise it will throws exception

Example:

Assume we are join two tables Employee and Address Tables. Here we should select the all the columns of two tables

Assume the following Query



SELECT emp.*,addr.* FROM ls_employee AS emp
INNER JOIN ls_address AS addr
ON emp.employeeId=addr.employeeId

The following is Java Method implementation in XXXFinderImpl.java

public class EmployeeFinderImpl extends BasePersistenceImpl<Employee> implements
EmployeeFinder {
public List getAllEmployeeAndAddresses() {
Session session = null;
try {
session = openSession();
String sql = CustomSQLUtil.get("employeesAddressesSQLQuery");
SQLQuery queryObject = session.createSQLQuery(sql);
queryObject.setCacheable(false);

queryObject.addEntity("Employee", EmployeeImpl.class);
queryObject.addEntity("Address", AddressImpl.class);

QueryPos qPos = QueryPos.getInstance(queryObject);
// qPos.add(studentGender);
return (List) queryObject.list();
} catch (Exception e) {
e.printStackTrace();
} finally {
closeSession(session);
}
return null;
}
}


Observation:

In the above we have joined two tables and we have selected all the columns of two tables.

Scenario:

Assume we are join many tables each tables have many columns then the result data might have more columns but we need only few columns of the respective tables.

If we change the SQL query and if we select few columns and use the query then we will get exception called Unknown Column exception.

Solution:

The solution is implement scalars in Custom SQL

Assume in the Employee and Address table we need only few columns

The following is example Query


SELECT emp.employeeName AS emplyeeName,
emp.employeeDesignation AS emplyeeDesignation,
addr.street AS emplyeeStreet,
addr.city AS emplyeeCity
FROM ls_employee AS emp
INNER JOIN ls_address AS addr
ON emp.employeeId=addr.employeeId


Note:

In the query we should use the Alias Name for each column and we will use alias name in Custom SQL java method implementation as Scalar.

The Query Results as follows



Now Scalar Implementation in Custom SQL java Method

In the java method implementation we will add scalar to SQL Query object and we need specify the column alias name and its data type and we need make sure in the SQL query each Column should have alias name.

We have Type.java is class which consist different data types when we add scalar we need to specify respective Type value.

Generally the result data is List of Object Array and it doesn’t have any specific model object type.

Because we selected the data from multiple tables so data does not belong to any specify model object that is why it’s List Of object array.

When we sterilize the object we can see the data in the form of Array like JSON Array once we serialize the object array.

The following is Scalars Custom SQL java Method


public List getAllEmployeeAndAddressesWithScalars() {
Session session = null;
try {
session = openSession();
String sql = CustomSQLUtil.get("employeesAddressesScalarSQLQuery");
SQLQuery queryObject = session.createSQLQuery(sql);
queryObject.setCacheable(false);
queryObject.addScalar("emplyeeName",Type.STRING);
queryObject.addScalar("emplyeeDesignation",Type.STRING);
queryObject.addScalar("emplyeeStreet",Type.STRING);
queryObject.addScalar("emplyeeCity",Type.STRING);
QueryPos qPos = QueryPos.getInstance(queryObject);
// qPos.add(employeeId);
return (List) queryObject.list();
} catch (Exception e) {
e.printStackTrace();
} finally {
closeSession(session);
}
return null;
}


Note:

In the above implementation we don’t need to add Entity Model Implementation classes to SQL query Object i.e. queryObject.addEntity("Employee", EmployeeImpl.class)
Make sure the order of scalar adding for Query object is same as the order we have used in Native SQL query.

Access the Data

We already know we need to make it above method as Local Service once we made it as Local Service method we can access anywhere.

We already know when we use scalars then data is List of Object Array type. Once we serialize the object we can see such form of data.

Here we will convert the object array to JSON Array so that we can access the data very easy.

The following are steps
  • Serialize the object array
  • Convert serialized string to JSON Array object
  • Access JSON Array with array Index Value

After serialize the Object array you can see as follows as String


Note:

We have used four scalars in the query so we can see 4 columns values as above.

The following is code Sample


<%
List employeeList=EmployeeLocalServiceUtil.getAllEmployeeAndAddressesWithScalarsAsLocal();
String serilizeString=null;
JSONArray empoyeeJsonArray=null;
for(Object elemnetObject:employeeList){
serilizeString=JSONFactoryUtil.serialize(elemnetObject);
empoyeeJsonArray=JSONFactoryUtil.createJSONArray(serilizeString);
out.println(empoyeeJsonArray.getString(0));
out.println(empoyeeJsonArray.getString(1));
out.println(empoyeeJsonArray.getString(2));
out.println(empoyeeJsonArray.getString(3));
out.println("<br/>");
}
%>


Note:

How many scalars we used in method implementation those many column values available in the form of object array we can access with array index and the orders is same we used in SQL query and the scalar we added for the Query object in java method.

Result Data View when we use above code


Join Tables which are in different Plugin Contexts or its in Portal Level

Challenge

We already know when we use custom SQL method implementation based on the SQL Query we need add one or multiple entity model implementation class to query object.

Scernario:1


queryObject.addEntity("Employee", EmployeeImpl.class);
queryObject.addEntity("Address", AddressImpl.class);


Note:

In the above code EmployeeImpl.java and AddressImpl.java are in the same Plugin portlet or Plugin context.

So we can say model Implementation classes are available in current class path so it can be loaded into SQL object.

We don’t get any exception or any problem where model implementation classes available in current class path.

Scernario:2

Assume we have an entity User which is in Portal Level and we have Address in our Plugin portlet when we join between these tables then we need to add Model Implementation Object classes to Query Object.


queryObject.addEntity("User", UserImpl.class);
queryObject.addEntity("Address", AddressImpl.class);


When we are trying to load UserImpl.java class then we will get Hibernate Exception Unknown Entity Exception.

Because UserImpl.java class is not available in current Plugin portlet class path so that it unable to load into query object.

Scenario: 3

Assume we have tow Plugin contexts and we have requirement to join two tables which are different Plugin contexts.

Once we done SQL query in the method implementation we need add tow model implementation classes to query object.


queryObject.addEntity("PluginAModelImpl", PluginAmodelImpl.class);
queryObject.addEntity("PluginBModelImpl", PluginBmodelImpl.class);


When we run above code we will get same Hibernate exception says Unknown Entity Exception.
Because other Plugin portlet model implementation class not available in current class Plugin portlet class path.

Solution:

Using Scalars in Custom SQL Implementation

When we use scalars we don’t need to add any entity model implementation class to query object in java method implementation.

Example

Assume we have JOIN Journal Article and User_ tables and we are going select few columns from two tables.

Here we need to remember that both tables are not belongs to current Plugin portlet and these are portal level tables but we are able to fetch the data with the help of scalars.

The following is SQL Query

 
SELECT ja.userName AS userName,
ja.articleId AS ArticleId,
usr.firstName AS userEmail,
usr.userId AS UserId
FROM journalarticle AS ja
INNER JOIN User_ AS usr
ON usr.userId=ja.userId;


The result of Query as follows


Scalar Implementation in Custom SQL Java Method


public List getUserJournalArticleData() {
Session session = null;
try {
session = openSession();
String sql = CustomSQLUtil.get("sclarExamplePortalLevelTables");
SQLQuery queryObject = session.createSQLQuery(sql);
queryObject.setCacheable(false);
queryObject.addScalar("userName",Type.STRING);
queryObject.addScalar("ArticleId",Type.STRING);
queryObject.addScalar("userEmail",Type.STRING);
queryObject.addScalar("UserId",Type.LONG);
QueryPos qPos = QueryPos.getInstance(queryObject);
// qPos.add(employeeId);
return (List) queryObject.list();
} catch (Exception e) {
e.printStackTrace();
} finally {
closeSession(session);
}
return null;
}


Note:

In the above implementation we have used Portal Level tables that is JournalArtcile and User_ tables.

We have used scalars on specific required columns and we don’t need to load any Entity Model Implementation class to Query Object.

We can apply same thing to any tables which are in different Plugin context or portal level we don’t need to bother about Model Implementation classes and its loading. We don’t bother about class path and it model implementation classes and its availability.

Access the Data

The following is code Sample


<%
List userJournalDataList=EmployeeLocalServiceUtil.getUserJournalArticleDataAsLocal();
String serilizeString=null;
JSONArray userJournalDataJsonArray=null;
for(Object userJournalData:userJournalDataList){
serilizeString=JSONFactoryUtil.serialize(userJournalData);
userJournalDataJsonArray=JSONFactoryUtil.createJSONArray(serilizeString);
out.println(userJournalDataJsonArray.getString(0));
out.println(userJournalDataJsonArray.getString(1));
out.println(userJournalDataJsonArray.getString(2));
out.println(userJournalDataJsonArray.getString(3));
out.println("<br/>");
}
%>


Note:

How many scalars we used in method implementation those many column values available in the form of object array we can access with array index and the orders is same we used in SQL query and the scalar we added for the Query object in java method.

Result Data View when we use above code



Get the Count of the result Data

Some time we may need to find the count of records. Apart from columns name we can use alias for count and other variable then we can fetch those values through the scalars.

Each time we may not fetch columns, some time we may calculate some values and we can fetch those values through scalars.

Best example is find count of records.

Example SQL Query as Follows

 
SELECT COUNT(*) as recordsTotalCount
FROM ls_employee AS emp
INNER JOIN ls_address AS addr
ON emp.employeeId=addr.employeeId


We are finding count for result of above query.



In the above query we are finding count and we already know when we use scalars we need alias name for each column or variable and the same way we need to give some alias name to the count.

Scalar Count Custom SQL Method Implementation


public List getTotalCountUsingScalar() {
Session session = null;
try {
session = openSession();
String sql = CustomSQLUtil.get("recordsTotalCountQuery");
SQLQuery queryObject = session.createSQLQuery(sql);
queryObject.setCacheable(false);
queryObject.addScalar("recordsTotalCount",Type.INTEGER);
QueryPos qPos = QueryPos.getInstance(queryObject);
// qPos.add(employeeId);
return (List) queryObject.list();
} catch (Exception e) {
e.printStackTrace();
} finally {
closeSession(session);
}
return null;
}


Access the data in JSP page

Generally when we get count its only one record in result so from the list we need to access the object with 0 indexes.

Here we simply serialize the object is enough because its only one value we don’t need to make it as JSON array.

The following is example code.


<%
List recordCountList=EmployeeLocalServiceUtil.getTotalCountUsingScalarAsLocal();
String serilizeString=JSONFactoryUtil.serialize(recordCountList.get(0));
out.println("Total Number of Records::"+serilizeString);
out.println("<br/>");
%>


The following is result view once we use above code



Note:

Please have look into portlet source code then you can see all example code and specially have a look into EmployeeFinderImpl.java, EmployeeLocalServiceImpl.java, SQL configuration xml file and all JSP pages.

Download Scalar Custom SQL Example Portlet




Environment:

Liferay IDE 2.x+Eclipse (Kepler) +Liferay Plugins SDK 6.2+Tomcat 7.x Liferay Portal Bundle

Deployment and its Working.

Download portlet you can source or war file to deploy into liferay portal as your convenient.
Once portlet successfully deployed drag the portlet in any desired page. Portlet is available in sample category.

Portlet Screens:

Default Page


Reference Links










Author

Popular Posts

Recent Posts

Recent Posts Widget