Tuesday, April 30, 2013

Dynamic Query in Liferay 6.1

Dynamic Query :

Portlet that retrieves a lot of data from liferay data table, and eventually combines them by filtering out Users, Roles or Groups by certain criterias you will certainly come to a point, where the Standard "LocalServideUtil" - Methods are just too slow.
The best way to get around all that Java Reflection based Stuff is to create dynamic queries.

Liferay offers several factory classes helping with the creation of Dynamic queries right in your portlet.

They are in the package com.liferay.portal.kernel.dao.orm:

1. DynamicQueryFactoryUtil
2. OrderFactoryUtil
3. ProjectionFactoryUtil
4. PropertyFactoryUtil and
5. RestrictionsFactoryUtil

Inspecting those 5 should be enough for most of your needs.

So ...lets get started with an example.

DynamicQuery query = DynamicQueryFactoryUtil.forClass(Model.class, PortletClassLoaderUtil.getClassLoader("test_WAR_testportlet"));

// test_WAR_testportlet is portlet id in Portlet table

query.add(RestrictionsFactoryUtil.eq("primaryKey.empID",101);
query.add(RestrictionsFactoryUtil.eq("eventId",10101);

List<EmpModel> resultList = EmpLocalSerciceUtil.dynamicQuery(query);

Emp empModel = (Emp) resultList.get(0);

empModel.setEventStatus("CLOSE");

EmpLocalServiceUtil.updateEmp(empModel);

Creating a DynamicQuery always starts with determining the Entity you want to retrieve.
For example : If you want to retrieve all Users, the class you should retrieve is com.liferay.portal.model.User

create a DynamicQuery object :

DynamicQuery UserQuery = DynamicQueryFactoryUtil.forClass(
User.class, PortletClassloaderUtil.getClassLoader());

This creates a dynamic query that goes straight for the User class.
The Portletclassloader makes sure, that the implementation class can be loaded (UserImpl.java).

So - as in SQL you have the possibility to order your result, to create a projection f.e. only returning the id of the user and you can add restrictions, for example to a property.

at first - lets try out to only retrieve users, that have the first name "Alfred".

to do so, we use the PropertyFactoryUtil :

Criterion alfredsName = PropertyFactoryUtil.forName("firstName").eq("Alfred");

PropertyFactoryUtil doesn´t offer much more than this forName factory method. But this method returns a Property instance that allows us to create Criterion objects and to assign them to the DynamicQuery.

The Property Factory allows to test for equality, if certain properties are greater or lower and to test if values are in a list provided.

There are about 30 - 40 different methods that should cover most of your needs.

We will cover only eq, in and like here. The rest will follow in a later post.

eq : EQ tests for equality - very fast ! Note that "Alfred" is notthe same as "alfred" - depending on the System the database runs on.

in : Very powerful, because it allows to take another DynamicQuery allowing the database to optimize the Query and to returns results very fast.

like : Typical SQL "like" ... is case sensitive on case sensitive systems.

But what do we do if we need to test for a name, without looking for the case ... what about "aLfreD" ?

In this case, we need to use RestrictionsFactoryUtil. It has a method called "ilike" :

ilke: Case insensitive SQL "like" and also the only possibility to retrieve all "alFreD"s in the Database - no matter how they are written. ilike also allows us to specify the "%" character we need if we want to retrieve "Walfred".

So much about the PropertyFactoryUtil. Here is the code to retrieve all Alfred - Users with the last name "Einstein":

DynamicQuery userQuery = DynamicQueryFactoryUtil.forClass(
User.class, PortletClassloaderUtil.getClassLoader());

userQuery.add(PropertyFactoryUtil.forName("lastName").eq("Einstein");
userQuery.add(RestrictionsFactoryUtil.ilike("firstName","%Alfred%"));

When working with dynamic queries it is sometimes necessary not to retrieve the complete Entities but only to retrieve certain properties - like the user id.

This is the point where the ProjectionFactoryUtil comes into place.

Projections allow (for example) to use distincts, counts, sums or to just return a single property.

This is what we will do next.

To create a Projection, just use the ProjectionFactoryUtil like this:

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

That´s all to limit the result to the userId.

You can then set the projection to the dynamic query like this only returning distinct userIds like this:

userQuery.setProjection(ProjectionFactoryUtil
.distinct(ProjectionFactoryUtil.property("userId")));

Note: Of course there can be only one Projection for a query. But Projections can be nested as shown in the previous example.

Restrictions:

Restrictions basically allow to do the same as Property. There are methods to test for equality, to test whether properties are in a certain range, to test several properties at once ("and") or to test a property against a map.
For example, it allows to test whether first and last name equal for certain persons:

Criterion equalNames = RestrictionsFactoryUtil.eqProperty("firstName","lastName");

Order

The OrderFactoryUtil allows to order the results of a query by a property - ascending and descending.

Monday, April 29, 2013

Custom Query in Liferay 6.1

The following will explain how to work with custom-sql in liferay 6.1

1. Create a portlet
2. In a service.xml (Under docroot/WEB-INF) add the following lines

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 6.0.0//EN" "http://www.liferay.com/dtd/liferay-service-builder_6_0_0.dtd">
<service-builder package-path="com.cignex.books">
    <author>koteswara.rao</author>
    <namespace>library</namespace>

    <entity name="Books" local-service="true" remote-service="false">
        <column name="bookId" type="long" primary="true"></column>
        <column name="title" type="String"></column>
        <column name="author" type="String"></column>

        <order by="asc">
            <order-column name="bookId"></order-column>
        </order>

        <finder return-type="Collection" name="Author">
            <finder-column name="author"></finder-column>
        </finder>
    </entity>
</service-builder>

3. Build the service (you can use ant build-service or directly from IDE)
4. Create custom-sql folder under src folder
5. Create default.xml file under custom-sql and add the following lines of code

<?xml version="1.0" encoding="UTF-8"?>
<custom-sql>
<sql file="custom-sql/book.xml" />
</custom-sql>

6. Create book.xml file under the same custom-sql folder mentioned in step 4 and add the following lines of code

<?xml version="1.0" encoding="UTF-8"?>
<custom-sql>
<sql id="findBooks">
<![CDATA[
SELECT * FROM library_books
WHERE (library_books.title like ?)
]]>
</sql>
</custom-sql>

7. Create the file BooksFinderImpl.java under persistence (persistence will get after the first time build service) and add the following lines of code

public class BooksFinderImpl extends BasePersistenceImpl implements BooksFinder {
    }

8. Now run ant build-service to generate necessary files

9. Add the following lines of code in BooksFinderImpl class

public List findBooks(String name) throws SystemException {

        Session session = null;
        try {
        session = openSession();
        String sql = CustomSQLUtil.get(FIND_BOOKS);
        SQLQuery query = session.createSQLQuery(sql);
        query.addEntity("Book", BooksImpl.class);
        QueryPos qPos = QueryPos.getInstance(query);
        qPos.add(name);
        return (List)query.list();
        }catch (Exception e) {
        }
        return null;
        }
        public static String FIND_BOOKS = "findBooks";

10. Add the following lines of code under BooksLocalServiceImpl

public class BooksLocalServiceImpl extends BooksLocalServiceBaseImpl {
    /*
    * NOTE FOR DEVELOPERS:
    *
    * Never reference this interface directly. Always use {@link com.cignex.books.service.BooksLocalServiceUtil} to access the books local service.
    */
    public List<Books> findBook(String name) throws PortalException,
    SystemException, RemoteException {
    return BooksFinderUtil.findBooks("%" + name + "%");
    }
}

11. Again run "ant build-service" again passing the service.xml file as parameter.
     This will update the corresponding interface with the new method defined.

12. Now go ahead and call BookLocalServiceImpl method from your jsp or java
      normally how    you      call other methods. For testing purpose add the following
     lines of code   in    Portlet under doView() method

public void doView(RenderRequest renderRequest,
            RenderResponse renderResponse) throws IOException, PortletException {
        try {
            List<Books> books = BooksLocalServiceUtil.findBook("es");
            System.out.println("::::::::::"+books.size());
            for(Books bookName : books){
                String book = bookName.getTitle();
                System.out.println("####"+book);

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

        super.doView(renderRequest, renderResponse);

    }

That's it enjoy!!!

Thursday, April 25, 2013

JUnit Test-Cases to test Liferay Portlet Service Layer

Configuration Steps:

Create a folder named 'test' withing the project and add it as a source folder to have the test classes and maintain it separate rather than mixing with the portlet source codes.
Create a 'PortletHibernateTestConfiguration' with the below lines of code.

public class PortletHibernateTestConfiguration extends PortalHibernateConfiguration {

protected ClassLoader getConfigurationClassLoader() {

return this.getClass().getClassLoader();

}

protected String[] getConfigurationResources() {

return PropsUtil.getArray(PropsKeys.HIBERNATE_CONFIGS);

}

Create a 'portal-ext.properties' file directly under the 'test' folder using the below configurations as reference.

jdbc.default.driverClassName=com.mysql.jdbc.Driver

jdbc.default.url=jdbc:mysql://localhost/liferay6ce?characterEncoding=UTF-8

jdbc.default.username=root

jdbc.default.password=root

hibernate.cache.use_query_cache=false

hibernate.cache.use_second_level_cache=false

hibernate.cache.use_minimal_puts=false

hibernate.cache.use_structured_entries=false

ehcache.portal.cache.manager.jmx.enabled=false

liferay.home=D:/liferay-portal-6.1.0-ce-ga1

resource.repositories.root=D:/liferay-portal-6.1.0-ce-ga1/tomcat-7.0.23/webapps/ROOT

# Disable the scheduler for Unit testing

scheduler.enabled=false

hibernate.configs=\

META-INF/portal-hbm.xml,\

META-INF/portlet-hbm.xml,\

META-INF/portlet-orm.xml

Create a 'META-INF' folder under the 'test' folder and create a 'hibernate-spring.xml' within it using the below code (Note: The PortalHibernateConfiguration class is replaced with our own implementation).

<?xml version="1.0" encoding="UTF-8"?>

<beans

default-destroy-method="destroy"

default-init-method="afterPropertiesSet"

xmlns="http://www.springframework.org/schema/beans"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd"

</bean>

</property>

</bean>

<constructor-arg ref="liferayDataSource" />

<constructor-arg ref="liferayHibernateSessionFactory" />

</bean>

</bean>

</property>

</bean>

</property>

</bean>

</property>

</bean>

</property>

</bean>

</property>

</bean>

</beans>

Similarly add a 'counter-spring.xml' to the 'META-INF' folder with the below codes.

<?xml version="1.0" encoding="UTF-8"?>

<beans

default-destroy-method="destroy"

default-init-method="afterPropertiesSet"

xmlns="http://www.springframework.org/schema/beans"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd"

</bean>

</property>

</bean>

</bean>

</bean>

</beans>

Copy the files portlet-hbm.xml, portlet-orm.xml & portlet-spring.xml from 'docroot/WEB-INF/src/META-INF' folder to your 'test/META-INF' folder.
Rename the copied 'portlet-spring.xml' to 'ext-spring.xml'.
Now create a Test Class extending the JUnit TestCase class and within the setUp() method, add the below code snippets to initialize the Spring-Hibernate context and get the Service classes initialized and linked to the back-end DB.

super.setUp();

InitUtil.initWithSpring();

PortletBeanLocatorUtil.setBeanLocator(ClpSerializer.getServletContextName(), PortalBeanLocatorUtil.getBeanLocator());

It can be then continued with code to obtain the service as shown below.

userService = UserLocalServiceUtil.getService();

employeeService = EmployeeLocalServiceUtil.getService();

Start writing your test cases and proceed with the usual way in executing them. If any memory error occurs, add the below VM arguments to the JUnit run configuration.

-Xms256m -Xmx512m -XX:PermSize=128M -XX:MaxPermSize=256m

Steps to run JUnit test through Liferay Ant Script:

Follow the below steps to run the JUnit test through the ANT script.

Replace the below code of 'build-common.xml' from Liferay plugin-sdk

</path>

with

</path>

Similarly add additional jvm memory arguments to the 'junit' tag of the 'test' target of the 'build-common-plugin.xml' which is highlighted in bold. This is to avoid the Out of Memory error while running the test cases.

<jvmarg value="-Xms256m" />

<jvmarg value="-Xmx512m" />

<jvmarg value="-XX:PermSize=128m" />

<jvmarg value="-XX:MaxPermSize=256m" />

</batchtest>

</junit>

Liferay Tech

Tuesday, April 30, 2013

Dynamic Query in Liferay 6.1

Monday, April 29, 2013

Custom Query in Liferay 6.1

Thursday, April 25, 2013

JUnit Test-Cases to test Liferay Portlet Service Layer

Configuration Steps:

Steps to run JUnit test through Liferay Ant Script:

About Me