Liferay is easy: Projection in Dynamic Query

Today we will discuss about Projection in Dynamic Query .Before reading this blog it is highly recommended to read my previous blog on introduction of dynamic query. We know how to fetch a complete object but what if we want only one column like only userId of User for this we use Projections.

Lets start with example:-

1)Select query for One column
Here we write a query that will return only userId:-

	/**
	* select user.userId from user_ user where user.emailAddress like '%adit%';
	*/
	DynamicQuery userQuery = DynamicQueryFactoryUtil.forClass(User.class, "user",PortalClassLoaderUtil.getClassLoader());
	userQuery.add(RestrictionsFactoryUtil.like("user.emailAddress", "%adit%"));
	userQuery.setProjection(PropertyFactoryUtil.forName("user.userId"));
	try {
	List<Object> userIds = UserLocalServiceUtil.dynamicQuery(userQuery);
	for (Object userId : userIds) {
	System.out.println("User Id=>"+userId);
	}

	} catch (SystemException e) {
	e.printStackTrace();
	}

view raw SingleColumn.java hosted with ❤ by GitHub

2)Select query for Two columns
Here we write a query that will return only userId and firstname:-

	/**
	* select user.userId,user.firstName from user_ user where user.emailAddress like '%adit%';
	*/
	DynamicQuery userQuery = DynamicQueryFactoryUtil.forClass(User.class, "user",PortalClassLoaderUtil.getClassLoader());
	userQuery.add(RestrictionsFactoryUtil.like("user.emailAddress", "%adit%"));

	ProjectionList projectionList = ProjectionFactoryUtil.projectionList();
	projectionList.add(PropertyFactoryUtil.forName("user.userId"));
	projectionList.add(PropertyFactoryUtil.forName("user.firstName"));
	userQuery.setProjection(projectionList);

	try {
	List<Object[]> users = UserLocalServiceUtil.dynamicQuery(userQuery);
	for (Object[] user : users) {
	System.out.println("user Id=>"+user[0]+" user Name=>"+user[1]);
	}

	} catch (SystemException e) {
	e.printStackTrace();
	}

view raw MultipleColumn.java hosted with ❤ by GitHub

3)Select query for Aggregate functions
Projection is also used for aggregate function like SUM, MAX, COUNT, AVG etc. Aggregate function always return single value.

	/**
	* SELECT MAX(user.userId) from user_ user;
	*/
	DynamicQuery userQuery = DynamicQueryFactoryUtil.forClass(User.class, "user",PortalClassLoaderUtil.getClassLoader());
	Projection projection =PropertyFactoryUtil.forName("user.userId").max();
	userQuery.setProjection(projection);
	try {
	List<Object>list = UserLocalServiceUtil.dynamicQuery(userQuery);
	System.out.println("MAX user id=>"+list.get(0));

	} catch (SystemException e2) {
	e2.printStackTrace();
	}