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:
- Select specific columnn in SQL Query
- Join Tables which are in different Plugin Contexts or in Portal Level
- 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
0 comments :
Post a Comment