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
Related Post:-
Liferay Service Builder in Detail
Custom Sql in Liferay
Many To Many Relationship mapping in Liferay Services
Can we pass String array as Positional Param in above example.
ReplyDelete>-----------------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)
Hi Tahir,
DeleteYes 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()
Thanku Sir, Done
ReplyDeleteGood what approach you are following?
DeleteString string manipulation
ReplyDelete