Custom Queries in mPire

There are 3 files that we will be working with

• ext/ext-impl/classes/custom-sql/${portlet}.xml
  
• ext/ext-impl/src/com/mpower/queryutil/${portlet}.ApiHelper.java
• extext-impl/classes/custom-sql/default.xml

Step 2) Find the XML files for the queries of the desired portelt

AddressBook.xml









AddressbookApiHelper.java

package com.mpower.queryutil;

import com.liferay.portal.SystemException;
import com.liferay.portal.spring.hibernate.CustomSQLUtil;
import com.liferay.portal.spring.hibernate.HibernateUtil;

import com.liferay.portlet.service.BasePersistence;

import com.mpower.common.addressbook.model.Address;
import com.mpower.common.addressbook.model.impl.AddressImpl;

import org.hibernate.SQLQuery;
import org.hibernate.Session;

import java.math.BigInteger;

import java.util.List;


public class AddressbookApiHelper extends BasePersistence {
public static String GET_RECORD_COUNT = Address.class.getName() +
".getRecordCount";

public static long getRecordCount() throws SystemException {
Session session = null;

try {
long count = 0;

session = HibernateUtil.openSession();

String sql = CustomSQLUtil.get(GET_RECORD_COUNT);

SQLQuery q = session.createSQLQuery(sql);

q.setCacheable(false);

List list = q.list();

if (list != null) {
BigInteger bint = (BigInteger) list.get(0);
count = Long.parseLong(bint.toString());
}

return count;
} catch (Exception e) {
throw new SystemException(e);
}
}
}

package com.mpower.queryutil;

import com.liferay.portal.SystemException;
import com.liferay.portal.spring.hibernate.CustomSQLUtil;
import com.liferay.portal.spring.hibernate.HibernateUtil;

import com.liferay.portlet.service.BasePersistence;

import com.mpower.common.addressbook.model.Address;
import com.mpower.common.addressbook.model.impl.AddressImpl;

import org.hibernate.SQLQuery;
import org.hibernate.Session;

import java.math.BigInteger;

import java.util.List;


public class AddressbookApiHelper extends BasePersistence {
public static String GET_RECORD_COUNT = Address.class.getName() +
".getRecordCount";

public static long getRecordCount() throws SystemException {
Session session = null;

try {
long count = 0;

session = HibernateUtil.openSession();

String sql = CustomSQLUtil.get(GET_RECORD_COUNT);

SQLQuery q = session.createSQLQuery(sql);

q.setCacheable(false);

List list = q.list();

if (list != null) {
BigInteger bint = (BigInteger) list.get(0);
count = Long.parseLong(bint.toString());
}

return count;
} catch (Exception e) {
throw new SystemException(e);
}
}
}

Default.xml

If you are Connecting to Another Datasource/Database. Need two extra steps:

1. Go to ext-spring-training.xml and get the id trainingSessionFactory or what you name it.
2. Whith this id, open a new hibernate session on the code above, refering to the another database, like this:

                        //open a new session
  session = HibernateUtil.openSession(&trainingSessionFactory);