Dynamic Query In Liferay

[Pranoti Nandurkar, modificado hace 11 años.]

Hello All,

I am new to liferay, I just build the service layer and now I have to retrieving list of records from table. I just want to write Sql Statement for following Query.

"Select ColumnName from TableName
where Condition"

Can Anybody know how to do this??

--
Thanks in Advance
Pranoti

[Hitoshi Ozawa, modificado hace 11 años.]

If you're using service builder, how about using custom query (default.xml)?

[Pranoti Nandurkar, modificado hace 11 años.]

Is there any way to handle this using Dynamic Query??

[Hitoshi Ozawa, modificado hace 11 años.]

Have you seen the following thread? Seems like a similar question.

http://www.liferay.com/community/forums/-/message_boards/message/11933945

[Pranoti Nandurkar, modificado hace 11 años.]

I am using following code and getting all columns in the table. I just want value from only one column which satisfies the condition.
The Sql Query I want to execute is:

SELECT AreaID FROM pincodeareamapping
WHERE PinCode = 110005


DynamicQuery PinAreaQuery = DynamicQueryFactoryUtil.forClass(PinCodeAreaMapping.class);
String pincode1 = request.getParameter("pincode1");
int ParsePin = Integer.parseInt(pincode1);
PinAreaQuery.add(RestrictionsFactoryUtil.like("PinCode", ParsePin));
try {
List pin = PinCodeAreaMappingLocalServiceUtil.dynamicQuery(PinAreaQuery);

for(int i=0; i<pin.size();i++)
{
System.out.println(pin.get(i));
}

//System.out.println("----------------"+pin);
} catch (SystemException e1) {
e1.printStackTrace();
}

[Jitendra Rajput, modificado hace 11 años.]

You are using like query so it may return more then one record.
Try to use dynamic query in following way to get exact record.

DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(MyCustomTable.class);
 

dynamicQuery.add(PropertyFactoryUtil.forName("PinCode ").eq(10122);

[Pranoti Nandurkar, modificado hace 11 años.]

Hello Jitendra,

I tried that, it is giving the records with all columns which satisfies the condition, but I want only records returning only one column value not all column values.

[Jitendra Rajput, modificado hace 11 años.]

So that's the correct behavior it will return the records based on condition specified.
In above case there are multiple records with the same pin code in your database . Thats the reason its returning multiple records.

If you need only record then take directly from returned list.

pin.get(0)

[Pranoti Nandurkar, modificado hace 11 años.]

Yes, I have multiple records with same pin codes. My requirement is, I want only list of AreaName with same pin codes. I did not want another columns.

[Jitendra Rajput, modificado hace 11 años.]

Sorry , I misunderstood your question ..!!

I am not sure how you can retrieve exactly one column. But whats the problem if you retrieve your areaId from model class.
i.e pinCodeAreaMapping.getAreaId() ..

[Hitoshi Ozawa, modificado hace 11 años.]

So, use custom query instead of dynamic query.

http://www.liferay.com/en/community/wiki/-/wiki/1071674/Custom+queries+in+Liferay+5.2

[ruchi sharma, modificado hace 11 años.]

Hello Pranoti,
Use the following code for getting just one column (say userId) as resultset

Projection projection = ProjectionFactoryUtil.property("userId");
dynamicQuery.setProjection(ProjectionFactoryUtil.property("userId"));

Hope this helps.
Thanks
Ruchi

[Pranoti Nandurkar, modificado hace 11 años.]

Yes Ruchi, It Works...

Thanks
Pranoti

[Hitoshi Ozawa, modificado hace 11 años.]

I've always been using custom queries for this. Learned something new. Will try it out. Thanks!

[ruchi sharma, modificado hace 11 años.]

great

[Ajay Saharan, modificado hace 10 años.]

Thanks for great solution.
Now I am facing one challange in using dynamic query.

I am using different database for services created by me. like
<entity name="MultipleDB" local-service="true" table="training" remote-service="true" data-source="myDatasource">

code in my action class .........

multipleDB = MultipleDBLocalServiceUtil.fetchMultipleDB(1);
System.out.println("multipleDB.getName() in action from service ::"+multipleDB.getName()+multipleDB.getIsActive());

When I am using above code I am getting my results properly. But when i write below code it's giving error that
"Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'training'."


DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(com.rnd.model.MultipleDB.class);

Criterion criterion = null;

criterion = RestrictionsFactoryUtil.like("Name", StringPool.PERCENT + "Ajay"+ StringPool.PERCENT);

criterion = RestrictionsFactoryUtil.and(criterion, RestrictionsFactoryUtil.eq("IsActive",1));

dynamicQuery.add(criterion);
try {

List<com.rnd.model.MultipleDB> requestList = MultipleDBLocalServiceUtil.dynamicQuery(dynamicQuery);

for (com.rnd.model.MultipleDB multipleDB2 : requestList) {
System.out.println("Name :"+multipleDB2.getName());
}

} catch (SystemException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}


Do anybody help me to identify that what's wrong I am doing.

Thanks in Advanced,
Ajay Saharan