Tuesday, February 17, 2015

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









5 comments:

  1. Can we pass String array as Positional Param in above example.
    >-----------------WHERE addr.country = ?");
    QueryPos pos = QueryPos.getInstance(query);
    pos.add(countryName);
    countyName is String Array
    There are two methods available add(String[] var) n add(String[] var,int count)

    ReplyDelete
    Replies
    1. Hi Tahir,

      Yes you can but better approach is that you can create a String that contain the query with parameter apply string manipulation to create the complete query and then directly pass this.
      And not use
      pos.add()

      Delete

Total Pageviews

Like Us on Facebook

Number Of Unique Visitor

Free counters!