Running a stored procedure Liferay

Here myreload() is the stored procedure:-

1. my-portlet\src\main\resources\custom-sql\reload_data.xml

<?xml version="1.0" encoding="UTF-8"?>
<custom-sql>
<sql id="com.me.service.persistence.MyFinder.reloadStaging">
<![CDATA[
select myreload();
]]>
</sql>
</custom-sql>

2. Create MyFinderImpl.java

package com.me.service.persistence;

import com.liferay.portal.kernel.dao.orm.Dialect;

import com.liferay.portal.kernel.dao.orm.QueryUtil;

import com.liferay.portal.kernel.dao.orm.SQLQuery;

import com.liferay.portal.kernel.dao.orm.Session;

import com.liferay.portal.kernel.dao.orm.Type;

import com.liferay.portal.kernel.exception.SystemException;

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

import com.liferay.util.dao.orm.CustomSQLUtil;

public class MyFinderImpl extends BasePersistenceImpl implements MyFinder {

public static String RELOAD_STAGING = MyFinder.class.getName()+".reloadStaging";

    public void reloadStaging()

            throws SystemException {

        // open a new hibernate session in normal case when you are opening

        // session for same entity

        Session session = null;

        SQLQuery query = null;

        try {

            session = openSession();

            String sql = CustomSQLUtil.get(RELOAD_STAGING);

            System.out.println("sql: "+sql);

            // create a SQLQuery object

            System.out.println("dialect: "+getDialect());

            query = session.createSQLQuery(sql);

            query.setCacheable(false);

            

            query.addScalar("myreload", Type.TEXT);

           //This is used when you want to pass any value to the query and place "?" in the query where you want the value to be set

           QueryUtil.list(query, getDialect(), -1, -1);

           

        } catch (Exception e) {

            System.out.println("Exception : Finder custom");

            e.printStackTrace();

        } finally {

            closeSession(session);

        }

        // execute the query and return a list from the db

        //return (Object) query.list().get(0);

    }

}

3. Add the following code to MyLocalServiceImpl.java

public void reloadStagingData() throws SystemException {

         TerminalTrackFinderUtil.reloadStaging();

    }

You can run it like any other LocalServiceImpl in your code