Issue with Custom Query with External Database

[Tulsi Rai，修改在7 年前。]

Hello,
We are working on a liferay-based project. Everything is working fine so far until I ran into this issue. I have a scenario that requires to join a couple of tables returning a few fields from both the tables and the resultset does not map to any table in the database. So based on the suggestions on the Forum, I went down to this custom query approach. I am connecting to the SQL Server database for this project. So here's what I have -
1. ext-spring.xml

<!--?xml version="1.0"?-->

<beans xmlns="http://www.springframework.org/schema/beans" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemalocation="http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">

	<bean id="basePersistence" abstract="true">
		<property name="dataSource" ref="rdmDataSource" />
		<property name="sessionFactory" ref="rdmSessionFactory" />
	</bean>
	<bean id="rdmHibernateSessionFactory" class="com.liferay.portal.spring.hibernate.PortletHibernateConfiguration" lazy-init="true">
		<property name="dataSource" ref="rdmDataSource" />
	</bean>
	<bean id="rdmSessionFactory" class="com.liferay.portal.dao.orm.hibernate.SessionFactoryImpl" lazy-init="true">
		<property name="sessionFactoryImplementor" ref="rdmHibernateSessionFactory" />
	</bean>
	<bean id="rdmTransactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager" lazy-init="true">
		<property name="dataSource" ref="rdmDataSource" />
		<property name="globalRollbackOnParticipationFailure" value="false" />
		<property name="sessionFactory" ref="rdmSessionFactory" />
	</bean>
	<bean id="rdmDataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
		<property name="jndiName" value="java:comp/env/jdbc/rdmDBConnectionPool" />
	</bean>
</beans> 

2. WEB-INF/src/custom-sql/default.xml

<!--?xml version="1.0" encoding="UTF-8"?-->
<custom-sql>
    <sql id="com.abc.esu.bc.service.persistence.BillingInfoFinder.getBillingInfo">
				       
		        SELECT R.mgbxBillFileName,R.fileReference,I.billingFileDate, I.totalAmount, I.addCount, I.updCount, I.delCount,I.billingFileStatus
				   FROM esu.BReference R, esu.InsproListBill I
				   WHERE R.groupAccountNumber = I.billingFileNumber
				   AND R.payrollProcessor = ?
				   AND R.statusCode= ?
				   AND I.premiumDueDate = ?
				   ORDER BY I.billingFileName DESC, I.billingFileDate DESC
		
    </sql>
</custom-sql>

Since my query results in fields from both the table which are not mapped to any table, I am using this SQLQuery.addScalar(...) method.
3. com.abc.esu.bc.service.persistence.BillingInfoFinder.java

package com.abc.esu.bc.service.persistence;

import java.util.Date;
import java.util.List;

import com.liferay.portal.kernel.bean.PortalBeanLocatorUtil;
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.portal.kernel.dao.orm.SessionFactory;
import com.liferay.portal.kernel.dao.orm.Type;
import com.liferay.portal.kernel.exception.SystemException;
import com.liferay.util.dao.orm.CustomSQLUtil;

public class BillingInfoFinder {

	public static String FIND_BILLING_INFO = BillingInfoFinder.class.getName()	+ ".getBillingInfo";
	private static SessionFactory sessionFactory = (SessionFactory) PortalBeanLocatorUtil.locate("rdmSessionFactory");

	public static List<object> getBillingInfo(String payrollProcessor,
			String statusCode, Date premiumDueDate) throws SystemException {
		Session session = null;

		session = sessionFactory.openSession();
		String sql = CustomSQLUtil.get(FIND_BILLING_INFO);
		SQLQuery query = session.createSQLQuery(sql);
		query.setCacheable(false);
		query.addScalar("mgbxBillFileName", Type.STRING);
		query.addScalar("fileReference", Type.STRING);
		query.addScalar("billingFileDate", Type.DATE);
		query.addScalar("totalAmount", Type.DOUBLE);
		query.addScalar("addCount", Type.INTEGER);
		query.addScalar("updCount", Type.INTEGER);
		query.addScalar("delCount", Type.INTEGER);
		query.addScalar("billingFileStatus", Type.STRING);
		QueryPos queryPos = QueryPos.getInstance(query);
		queryPos.add(payrollProcessor);
		queryPos.add(statusCode);
		queryPos.add(premiumDueDate);
		return query.list();
	}

}

<br>I access the <strong>BillingInfoFinder.java</strong> in  my <strong>XXXLocalServiceImpl.java</strong> as shown below -<br><strong>4. LBReferenceLocalServiceImpl .java</strong><br><pre><code>
public class LBReferenceLocalServiceImpl extends LBReferenceLocalServiceBaseImpl {
	/*
	 * NOTE FOR DEVELOPERS:
	 *
	 * Never reference this interface directly. Always use {@link com.agia.esu.bc.service.LBReferenceLocalServiceUtil} to access the l b reference local service.
	 */
	
	public List<object> getBillingInfo(String payrollProcessor, String statusCode, Date premiumDueDate) throws SystemException{
		return BillingInfoFinder.getBillingInfo(payrollProcessor, statusCode, premiumDueDate);
	}
}
<br><br>And here's the error I get -<br><pre><code>

Caused by: org.springframework.beans.factory.NoSuchBeanDefinitionException: No bean named 'rdmSessionFactory' is defined
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.getBeanDefinition(DefaultListableBeanFactory.java:529)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getMergedLocalBeanDefinition(AbstractBeanFactory.java:1094)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:276)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:192)
	at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1075)
	at com.liferay.portal.bean.BeanLocatorImpl.doLocate(BeanLocatorImpl.java:178)
	at com.liferay.portal.bean.BeanLocatorImpl.locate(BeanLocatorImpl.java:95)
	... 194 more
</code></pre><br><br>As far as I can see, this line <strong> (SessionFactory) PortalBeanLocatorUtil.locate("rdmSessionFactory")</strong> is not able to locate the <strong>rdmSessionFactory</strong> that is defined in <strong>ext-spring.xml</strong>. I am pretty sure I am missing some configuration or something that's not linked correctly in order for this <strong>rdmSessionFactory</strong> to be discovered but I can't see what. Can you please help me with some guidance here?<br>Thank you.<br>Tulsi</object></code></pre></object>

[Tulsi Rai，修改在7 年前。]

Any help on this?

[David H Nebinger，修改在7 年前。]

Tulsi Rai:

As far as I can see, this line (SessionFactory) PortalBeanLocatorUtil.locate("rdmSessionFactory") is not able to locate the rdmSessionFactory that is defined in ext-spring.xml

I have a blog on why you have to actually redefine the liferay session factory bean for external databases in an OSGi world: https://web.liferay.com/web/user.26526/blog/-/blogs/liferay-7-service-builder-and-external-databases