How to use custom mysql query from my liferay custom portlet?

Question

I am using Liferay and developing my custom portlet, now I want to use custom query to retrieve some data from multiple table with joins etc.

I have googled the things for my problem but can't find the simple way to understand the step-by-step procedure.

So if any one can guide me or give me any tutorial to create Custom SQL query for my custom portlet.

after this 4th step i have built my service in eclipse,and its showing successfully.there are two file created in service/persistence package with the name AdvertiseFinder.java and AdvertiseFinderUtil.java but when i try to access the method getAd_DisplayforReports with the advertiseFinderUtil.getAd_DisplayforReports("Any arguement with string") its giving me error that no such method in AdvertiseFinderUtil

I have build the service after updating my AdvertiseFinderImpl Method.but its not working

this is my AdvertiseFinderImpl Class

package emenu.advertise.database.service.persistence;

import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;

import emenu.advertise.database.model.ad_display;
import emenu.advertise.database.model.advertise;
import emenu.advertise.database.model.impl.ad_displayImpl;

import java.util.List;


import com.liferay.portal.SystemException;
import com.liferay.portal.kernel.dao.orm.QueryPos;
import com.liferay.portal.kernel.dao.orm.SQLQuery;
import com.liferay.portal.kernel.dao.orm.Session;
import com.liferay.util.dao.orm.CustomSQLUtil;

public class AdvertiseFinderImpl  extends BasePersistenceImpl<ad_display> implements advertiseFinder{

    public void getall() {
    }

    // the name of the query
        public static String GET_ADVERTISE = AdvertiseFinderImpl.class.getName()
                + ".getAdvertise";

        // the method which will be called from the ServiceImpl class
        public List<ad_display> getAd_DisplayforReports(String pattern) throws SystemException {

            Session session = null;
            try {
                // open a new hibernate session
                session = openSession();

                // pull out our query from book.xml, created earlier
                String sql = CustomSQLUtil.get(GET_ADVERTISE);

                // create a SQLQuery object
                SQLQuery q = session.createSQLQuery(sql);

                // replace the "Book" in the query string with the fully qualified java class
                // this has to be the hibernate table name
                q.addEntity("a_ad_display", ad_displayImpl.class);


                // Get query position instance
                QueryPos qPos = QueryPos.getInstance(q);

                // fill in the "?" value of the custom query
                // this is same like forming a prepared statement
                qPos.add(pattern);

                // execute the query and return a list from the db
                return (List<ad_display>)q.list();

                /*
                 // use this block if you want to return the no. of rows (count)

                 int rows = 0;

                 Iterator<Long> itr = q.list().iterator();

                 if (itr.hasNext()) { Long count = itr.next();

                 if (count != null) { rows = count.intValue(); } }

                 return rows;
                 */
            } catch (Exception e) {
                throw new SystemException(e);
            } finally {
                closeSession(session);
            }
        }

}

my default-ext.xml is following

<?xml version="1.0"?>

<custom-sql>
<sql file="custom-sql/emenu.xml" />
</custom-sql>

my emenu.xml is here

<custom-sql>
    <sql id="emenu.advertise.database.service.persistence.AdvertiseFinderImpl.getAd_DisplayforReports">
      <![CDATA[
            SELECT
                    *
            FROM
                a_ad_display
        ]]>
    </sql>
</custom-sql>

Answer 1

change

return (List<ad_display>)q.list();

to

return (List<ad_display>) QueryUtil.list(q, getDialect(), -1, -1);

Answer 2

Following are the steps to write custom query / finder methods in Liferay:

1. Create a new finder called EntityFinderImpl.java in the /generated/service/persistence directory.
2. 'build-service' on the project.
3. The ServiceBuilder autogenerates the following two extra files: EntityFinder.java and EntityFinderUtil.java
4. Now open the EntityFinderImpl.java file and let this class extend the BasePersistenceImpl and implement EntityFinder. (Assumed that the Entity (table-name) is defined in the service.xml and other required classes are also autogenerated by ServiceBuilder)
5. Now add required custom method to EntityFinderImpl.java and build service again to distribute this method to Util classes.

Custom method can be created using liferay's DynamicQuery API or SQL-query as following:

public List<Entity> getCustomDataFromFinder("Parameters") throws SystemException {

    Session session = null;
    StringBuilder queryString = new StringBuilder();    
    Entity e = new EntityImpl();

    try {
        session = openSession();

        queryString.append(" Write your Query here and conditionally append parameter value(s).");

        SQLQuery query = session.createSQLQuery(queryString.toString());
                 query.addEntity("EntityName", EntityImpl.class);           

        return (List<Entity>) QueryUtil.list(query, getDialect(), 0, -1);
    } 
    catch (Exception e) {
        throw new SystemException(e);
    }       
    finally {
        if (session != null) {
            closeSession(session);
        }
    }
}