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

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

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

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

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

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();
}

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.

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.

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.

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.

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() ..

So, use custom query instead of dynamic query.

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

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

Yes Ruchi, It Works...

Thanks
Pranoti

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

great

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