How can we use dynamic query or custom query with plugin-sdk ?

[Jignesh Vachhani, geändert vor 12 Jahren.]

Hi All,

Can anyone have idea about how can we use dynamic query or custom query using plugin-sdk in LR 5.2.X ?
if you have tried or used anywhere please provide sample code.
I tried with LR 6.0.X its working but i am not able to find any solution for LR 5.2.X.
Your valuable inputs will help me a lot.
Thanks

[Ravi Kumar Gupta, geändert vor 12 Jahren.]

Hi Jignesh,

I am using custom query with LR5.2.3CE

Create a default.xml, query.xml in docroot\WEB-INF\src\custom-sql and add the following

<!--?xml version="1.0"?-->
<custom-sql>
	<sql file="custom-sql/query.xml" />
</custom-sql>

in query.xml

<!--?xml version="1.0"?-->

<custom-sql>
	<!--    Below is the custom sql to be used used-->
	<sql id="com.test.service.persistence.ABCFinder.findAll">
		
			SELECT
				DISTINCT {MY_TABLE.*}
			FROM
				MY_TABLE
			WHERE 
				MY_TABLE.C Like ?	
			
	</sql>
</custom-sql>

Make note of id. Now in ABCFinderImpl.java. This class is in WEB-INF/src not in WEB-INF/service
public static String FIND_ALL_ABC = "com.test.service.persistence.ABCFinder.findAll"; //this is the same id as used in custom-sql
and the method to find ABC now.

public List<abc> getAllABC(String c) throws SystemException {
		Session session = null;
		StringBuffer sql = new StringBuffer();
		try {
			session = openSession();
			sql.append(CustomSQLUtil.get(FIND_ALL_ABC ));
			
			SQLQuery q = session.createSQLQuery(sql.toString());
			q.addEntity(ABCImpl.TABLE_NAME, ABCImpl.class);
                        QueryPos qPos = QueryPos.getInstance(q);
                        c = "%"+c+"%"; //only if needed.. 
			qPos.add(c);
			return q.list();
		} catch (Exception e) {
			throw new SystemException(e);
		} finally {
			closeSession(session);
		}
	}
</abc>

This is it.. i guess..

[Jignesh Vachhani, geändert vor 12 Jahren.]

Thanks Ravi,

I think these steps we can used in EXT environment.But is it possible for plugin-sdk ?

[Ravi Kumar Gupta, geändert vor 12 Jahren.]

Hi Jignesh,

I am using these in plugins-sdk 5.2.3. It works fine.

HTH
-Ravi

[Jignesh Vachhani, geändert vor 12 Jahren.]

Alright Ravi,
Thanks i will try and also have you ever been used Dynamic query in plugin-sdk ?

[Ravi Kumar Gupta, geändert vor 12 Jahren.]

Yes it works fine, you can run it with ABCLocalServiceUtil.dynamicQuery(query) method.

[Jignesh Vachhani, geändert vor 12 Jahren.]

I tried with the same but its giving me null pointer exception because it could not create session.

[Ravi Kumar Gupta, geändert vor 12 Jahren.]

I just checked.. its working fine..
For your reference, this is the dynamic query formed.

String cTag2 = "abc";
DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(ABC.class);
dynamicQuery.add(PropertyFactoryUtil.forName("primaryKey.cTag2").eq(cTag2));
ProjectionList projectionList = ProjectionFactoryUtil.projectionList();
projectionList.add(ProjectionFactoryUtil.count("primaryKey.cTag2"));
dynamicQuery.setProjection(projectionList);
 

And after this a simple call to ServiceUtil.dynamicQuery

HTH

[Jignesh Vachhani, geändert vor 12 Jahren.]

Thanks Ravi ,
Might be there would be some problem in my portlet as i used the same thing.
So i will check it again by creating completely new portlet and replace my code.
Hope it will work