留言板

RE: Dynamic Query In Liferay

thumbnail
Pranoti Nandurkar,修改在11 年前。

Dynamic Query In Liferay

Junior Member 帖子: 48 加入日期: 12-2-3 最近的帖子
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
thumbnail
Hitoshi Ozawa,修改在11 年前。

RE: Dynamic Query In Liferay

Liferay Legend 帖子: 7942 加入日期: 10-3-24 最近的帖子
If you're using service builder, how about using custom query (default.xml)?
thumbnail
Pranoti Nandurkar,修改在11 年前。

RE: Dynamic Query In Liferay

Junior Member 帖子: 48 加入日期: 12-2-3 最近的帖子
Is there any way to handle this using Dynamic Query??
thumbnail
Hitoshi Ozawa,修改在11 年前。

RE: Dynamic Query In Liferay

Liferay Legend 帖子: 7942 加入日期: 10-3-24 最近的帖子
Have you seen the following thread? Seems like a similar question.

http://www.liferay.com/community/forums/-/message_boards/message/11933945
thumbnail
Pranoti Nandurkar,修改在11 年前。

RE: Dynamic Query In Liferay

Junior Member 帖子: 48 加入日期: 12-2-3 最近的帖子
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();
}
thumbnail
Jitendra Rajput,修改在11 年前。

RE: Dynamic Query In Liferay

Liferay Master 帖子: 875 加入日期: 11-1-7 最近的帖子
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);
thumbnail
Pranoti Nandurkar,修改在11 年前。

RE: Dynamic Query In Liferay

Junior Member 帖子: 48 加入日期: 12-2-3 最近的帖子
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.
thumbnail
Jitendra Rajput,修改在11 年前。

RE: Dynamic Query In Liferay

Liferay Master 帖子: 875 加入日期: 11-1-7 最近的帖子
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)
thumbnail
Pranoti Nandurkar,修改在11 年前。

RE: Dynamic Query In Liferay

Junior Member 帖子: 48 加入日期: 12-2-3 最近的帖子
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.
thumbnail
Jitendra Rajput,修改在11 年前。

RE: Dynamic Query In Liferay

Liferay Master 帖子: 875 加入日期: 11-1-7 最近的帖子
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() ..
thumbnail
Hitoshi Ozawa,修改在11 年前。

RE: Dynamic Query In Liferay

Liferay Legend 帖子: 7942 加入日期: 10-3-24 最近的帖子
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,修改在11 年前。

RE: Dynamic Query In Liferay

Junior Member 帖子: 95 加入日期: 11-3-24 最近的帖子
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
thumbnail
Pranoti Nandurkar,修改在11 年前。

RE: Dynamic Query In Liferay

Junior Member 帖子: 48 加入日期: 12-2-3 最近的帖子
Yes Ruchi, It Works...

Thanks
Pranoti
thumbnail
Hitoshi Ozawa,修改在11 年前。

RE: Dynamic Query In Liferay

Liferay Legend 帖子: 7942 加入日期: 10-3-24 最近的帖子
I've always been using custom queries for this. Learned something new. Will try it out. Thanks! emoticon
ruchi sharma,修改在11 年前。

RE: Dynamic Query In Liferay

Junior Member 帖子: 95 加入日期: 11-3-24 最近的帖子
great emoticon
thumbnail
Ajay Saharan,修改在10 年前。

RE: Dynamic Query In Liferay

New Member 帖子: 18 加入日期: 09-2-25 最近的帖子
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