Liferay is easy

Service Hook in Liferay

Custom Sql With Two Table

Custom sql/sql query with two table

As discussed in my previous article Custom Sql in Liferay , custom sql is used to write native sql query in liferay. Today we will discuss how to write custom query that belong to two or more table like join query. Before Reading this blog it is highly recommended to read my previous blog on Service Builder in Detail and custom sql in liferay .

Lets Start this step by step:-

Step 1:-Create service.xml
You can create service as mention in my previous article Service Builder in Detail .

service.xml

And Build Service

Step 2:- Fill data in tables
After inserting data tables in data base look like this:-

For our example we like to fetch all the student details that belong to India ie our query is:-

Step 3:- Create our method
Open either any one from:-
a)StudentLocalServiceImpl b)StudentAddressLocalServiceImpl
Because we have to fetch the data from both the tables so pick any one .

StudentLocalServiceImpl
Explanation:-

1)List<Object[]> getAllDetailByCountry(String name)
Here we create a method that take String argument and whose return type is List<Object[]> because output result is neither of Employee type and not of Address type it is a combination of both.

2)Session session = studentPersistence.openSession()
Here we create a Session object with studentPersistence

3)SQLQuery query = session.createSQLQuery(" ")
Here we create our query this is not the standard way normally we take query from xml file like i described in Custom sql in Liferay but this approach is sometimes very handy when we have to save time.

4)QueryPos pos = QueryPos.getInstance(query)
Here we pass the query and fill the value of ? with pos like
Ex- pos.add(countryName);
and than finally return the list.

Again Build Service

So that this can be available in Util class.

Step 4:- Call method from util Class
After building services this method is available in StudentLocalServiceUtil use it like:-

You can download source code from Custom Sql with two table

Hope this will help....

Related Post:-

Liferay Service Builder in Detail

Custom Sql in Liferay

Many To Many Relationship mapping in Liferay Services

Service Hook in Liferay

Many To Many Relationship in Liferay Services

Today we will discuss Many To Many relationship in Liferay

As discuss earlier Liferay Service Builder create tables with service.xml. But is there any way to provide Many To Many relationship between entities. Yes there is today we will discuss Many To Many relationship in liferay. Before Reading this blog it is highly recommended to read my previous blog on Service Builder in Detail.

Lets Start this step by step:-

Step 1:-Create service.xml
You can create service as mention in my previous article Service Builder in Detail .

service.xml

Just focus on line no 11 and 17:-

Explanation:-
Here we create a third entity(table) Employee_department Which contain the primary key of both tables.

Step 2:-Check point
Now build the service.xml and open tables.sql inside sql folder.

tables.sql

As you can see 3 tables are created two for entities and one for mapping.

Step 3:-Calling Services
Just create a doView() method and add data to the tables here we consider a scenario where one employee belong to two department.

Thats it just see the tables in database:-

You can Download source code from Many To Many mapping in Liferay

Hope this will help....

Related Post:-

Liferay Service Builder in Detail

Finder Method for Service Builder in Liferay

Custom Sql in Liferay

Custom Sql with two table in Liferay

Liferay Service Builder in Detail

How to install Maven

Creating Portlet/Services using Maven

Message Customization in Liferay

Custom Sql in Liferay

Today we will discuss Custom Sql/Sql query in Liferay

Liferay Service Builder create basic CRUD method but there are some scenarios when we have to write SQL query. For writing native SQL query we use the concept of Custom Query in Liferay. Before Reading this blog it is highly recommended to read my previous blog on Service Builder in Detail.

Lets Start this step by step:-

Step 1:-Create service.xml
You can create service as mention in my previous article Service Builder in Detail .

service.xml

Step 2:-Create xml files for sql Query
Now create a folder custom-sql inside src and create default.xml in it.

default.xml(/WEB-INF/src/custom-sql/default.xml)

here we can write our sql query but for good maintanability we create seperate file student-custom-sql.xml and include in default.xml.

student-custom-sql.xml

Here we use sql id this is unique for each query.This id is used to fetch the query in method.
No need to write semicolon(;).
You can create one xml file for one entity and then include in default.xml.

Step 3:-Create xxxFinderImpl Class
Now create xxxFinderImpl class inside persistence in our case create StudentFinderImpl that extends BasePersistenceImpl and implements StudentFinder.

StudentFinderImpl.java

Initally it shows error because there is no StudentFinder interface.

Run Service builder

After this error is gone and StudentFinder interface is created which is blank.

StudentFinder.java
One more class StudentFinderUtil is also created .

StudentFinderUtil.java

Step 4:-Create method in xxxFinderImpl Class
Create method in this class and provide implementation.This is the main task where we fire query and get the result.

StudentFinderImpl.java

Explanation:-

1)CustomSQLUtil.get("studentBetweenQuery");
Here we fetch the sql query from xml file with the help of sql id. For different queries different id and method in this class.

2)queryObject.addEntity("Student", StudentImpl.class);
Here we add our entity to queryObject. If sql query belong to multiple tables than we have to add all the entities to query object.

3)return (List<Student>) queryObject.list();
Here we return List<Student> because our query return all records belong to Student table but if our query contain two or more table like in case of join then we return List<Object[]>.

Step 5:-Expose method to LocalServiceUtil Class
We can't directly call method from StudentFinderImpl or StudentFinderUtil . All method are call from xxxLocalServiceUtil
Class so first we create method in xxxLocalServiceImpl

StudentLocalServiceImpl.java

Here this show error because StudentFinderUtil has not contain getStudentBetweenStudentId(start, end).

Run Service builder

Now error is gone coz this will create method in StudentFinder interface and StudentFinderUtil Class also.Now StudentFinder and StudentFinderUtil become:-

StudentFinder.java

StudentFinderUtil.java

Now Both Contain getStudentBetweenStudentId(int start, int end).

Note:-
Normally we first create method in interface then provide implementation in Impl class but in Liferay we provide implementation in xxxLocalServiceImpl first and Run service builder which create method in Inteface.

Step 6:-Call method from LocalServiceUtil Class
Now from your doView() or from jsp call your method as:-

You can download source code from Custom Sql in Liferay

Hope this will help....

Related Post:-

Liferay Service Builder in Detail

Custom Sql with two table in Liferay

Finder Method for Service Builder in Liferay

Many To Many Relationship mapping in Liferay Services

Liferay Service Builder in Detail

How to install Maven

Creating Portlet/Services using Maven

Message Customization in Liferay

Finder method for Service Builder in Liferay

Today we will Discuss Finder method for Service Builder in Liferay

In my Previous article Service Builder in Detail we see that liferay created basic CRUD method automatically but what if we want to add our custom method in Liferay services . There are many ways but creating Finder method is the simplest one.

Lets Start this step by step:-

Step 1:-Create service.xml
You can create service as mention in my previous article Service Builder in Detail .Today we add finder method in my Services:-

Service.xml

Just Focus on these lines:-

Explanation:-

1) <finder name="Mobile" return-type="Collection">

a) name="Mobile"

This is the name by which method is Created. Our method is Created by name

findByMobile(). findBy is automatically prepended by liferay.

Note=> The name must be Capital otherwise method is created as findBymobile()
which is not follow java camel case convention.

b) return-type="Collection"
return type of our method Collection represent List<T> in this case List<Student>.

2)<finder-column name="mobileno"></finder-column>
Column-name represent on which column you want to apply conditions.

After doing this just build your service and refresh your project.

Step 2:-Check Point

At this point liferay create our method in two places:-

1)StudentPersistence (Interface)
(under web-inf/services/com/aditya/services/persistence)

This interface contain many overloaded method of same name like:-
Their are other method also like removeByMobile, countByMobile etc.

2)StudentPersistenceImpl (Class)
(under web-inf/src/com/aditya/services/persistence)
This contain the implementation of StudentPersistence interface.

Step 3:-Expose method to Util Classes

Till now our method is not available to LocalServiceUtil Class.We have to expose it.Open StudentLocalServiceImpl Class. This class is just blank provide implementation of your method here so that it can be available by Util Class.

As you see in implementation we just call the method from interface thats it.Now again build services and that it .Your method is now available to StudentLocalServiceUtil Class.

You can download source code from Finder method for service builder in lifeary

Hope this will help....

Related Post:-

Liferay Service Builder in Detail

Custom Sql in Liferay

Custom Sql with two table in Liferay

Many To Many Relationship mapping in Liferay Services

Liferay Service Builder in Detail

How to install Maven

Creating Portlet/Services using Maven

Message Customization in Liferay