Custom Query to join two tables in lIferay

[Sarath Endluri，修改在11 年前。]

Hi,

Iam trying to join two tables in liferay say one is User and the UserAddress entities.I have followed the approach mentioned in the below thread i.e https://www.liferay.com/community/forums/-/message_boards/message/14937161..

when i try to build the services after writing the custom code i was facing the below exception.

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'com.expando.slayer.service.UserAddressLocalService': Could not inject BeanReference fields; nested exception is java.lang.IllegalArgumentException: Can not set com.expando.slayer.service.persistence.UserAddressFinder field com.expando.slayer.service.base.UserAddressLocalServiceBaseImpl.userAddressFinder to $Proxy388
at com.liferay.portal.spring.bean.BeanReferenceAnnotationBeanPostProcessor._autoInject(BeanReferenceAnnotationBeanPostProcessor.java:160)
at com.liferay.portal.spring.bean.BeanReferenceAnnotationBeanPostProcessor._autoInject(BeanReferenceAnnotationBeanPostProcessor.java:166)
at com.liferay.portal.spring.bean.BeanReferenceAnnotationBeanPostProcessor.postProcessBeforeInitialization(BeanReferenceAnnotationBeanPostProcessor.java:75)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyBeanPostProcessorsBeforeInitialization(AbstractAutowireCapableBeanFactory.java:394)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1413)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:519)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:293)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:290)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:192)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:585)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:895)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:425)
at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:282)
at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:204)
at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:47)
at com.liferay.portal.spring.context.PortletContextLoaderListener.contextInitialized(PortletContextLoaderListener.java:99)
at com.liferay.portal.kernel.servlet.PortalClassLoaderServletContextListener.doPortalInit(PortalClassLoaderServletContextListener.java:91)
at com.liferay.portal.kernel.util.BasePortalLifecycle.portalInit(BasePortalLifecycle.java:42)
at com.liferay.portal.kernel.util.PortalLifecycleUtil.register(PortalLifecycleUtil.java:61)
at com.liferay.portal.kernel.util.PortalLifecycleUtil.register(PortalLifecycleUtil.java:53)
at com.liferay.portal.kernel.util.BasePortalLifecycle.registerPortalLifecycle(BasePortalLifecycle.java:52)
at com.liferay.portal.kernel.servlet.PortalClassLoaderServletContextListener.contextInitialized(PortalClassLoaderServletContextListener.java:50)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4765)
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5260)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:866)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:842)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:615)
at org.apache.catalina.startup.HostConfig.deployDescriptor(HostConfig.java:649)
at org.apache.catalina.startup.HostConfig$DeployDescriptor.run(HostConfig.java:1581)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
at java.util.concurrent.FutureTask.run(FutureTask.java:138)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.lang.IllegalArgumentException: Can not set com.expando.slayer.service.persistence.UserAddressFinder field com.expando.slayer.service.base.UserAddressLocalServiceBaseImpl.userAddressFinder to $Proxy388
at sun.reflect.UnsafeFieldAccessorImpl.throwSetIllegalArgumentException(UnsafeFieldAccessorImpl.java:146)
at sun.reflect.UnsafeFieldAccessorImpl.throwSetIllegalArgumentException(UnsafeFieldAccessorImpl.java:150)
at sun.reflect.UnsafeObjectFieldAccessorImpl.set(UnsafeObjectFieldAccessorImpl.java:63)
at java.lang.reflect.Field.set(Field.java:657)
at com.liferay.portal.spring.bean.BeanReferenceAnnotationBeanPostProcessor._autoInject(BeanReferenceAnnotationBeanPostProcessor.java:157)

Below is my service.xml file that i have used for the requirement.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 6.1.0//EN" "http://www.liferay.com/dtd/liferay-service-builder_6_1_0.dtd">
<service-builder package-path="com.expando.slayer">
<author>abc</author>
<namespace>userDetails</namespace>

<entity name="UserAddress" local-service="true" remote-service="false">
<!-- PK fields -->

<column name="userId" type="long" primary="true" />
<!-- UI fields -->

<column name="userName" type="String"/>
<column name="orgId" type="long" />
<column name="orgName" type="String"/>
<column name="UserAddress" type="String" />


<reference package-path="com.liferay.portal" entity="User" />

</entity>
</service-builder>

Please let me know the approach that i need to follow for creating a custom query to join two tables.Thanks in Advance.
Sharath.E

[Jignesh Vachhani，修改在11 年前。]

Hi Sarath,

Please have a look at useful blog: http://www.liferaysolution.com/2012/03/custome-query.html.
Hope this would help you to create custom query.

[Sarath Endluri，修改在11 年前。]

Hi Jignesh,

Thanks For the Reply...but this is for creating a query for single table..If i want to use the join query how do my service.xml file will look like...

say for example i have two entities User and UserAddress .Inorder to query these two tables how is my service.xml is going to be ..

Please provide me a sample xml file..

Thanks,
Sharath

[Jignesh Vachhani，修改在11 年前。]

Hi Sarath,

For joining a table you dont need to write anything into service.xml but you may have to create your own entity.xml which you have to make entry in default.xml.
you can see below sample xml :

<sql id="com.liferay.portal.service.persistence.GroupFinder.joinByGroupsUserGroups">
		
			INNER JOIN
				Groups_UserGroups ON
					(Groups_UserGroups.groupId = Group_.groupId)
			INNER JOIN
				Users_UserGroups ON
					(Users_UserGroups.userGroupId = Groups_UserGroups.userGroupId)
			WHERE
				(Group_.liveGroupId = 0) AND
				(Users_UserGroups.userId = ?)
		
	</sql>

Here you can see 2 table ref. given Groups_UserGroups & Users_UserGroups

[Sarath Endluri，修改在11 年前。]

Hi Jignesh,

So you mean to say that if i need to join any two tables i need not to write any thing i.e any entry related to any of the tables??even for a single table also.

Please suggest.

Thanks,
Sharath.E

[Jignesh Vachhani，修改在11 年前。]

If i am not wrong , you want to fetch data from more then one table.
service.xml is used for service builder which will create entity in your DB and will generate persistence classes to make DB operation for that entity only.

While Custom query is used to fetch data from more then one tabel and for that you may have to design your own xml file where you will have to put
Query like which i show in above post and you can then get result.

Please refer the link which i posted above for How to create custom query ?

[Sarath Endluri，修改在11 年前。]

Hi Jignesh,

I have followed all the steps mentioned in the link http://www.liferaysolution.com/2012/03/custome-query.html to generate the custom query.

I have a doubt that in the code return BookFinderUtil.findBooks("%" + name + "%"); in step 7 of Create LocalServiceImpl class is the findBooks method will be created automatically or do we need to manually create it,because when i build the services i could n't see the method (findBooks()) in BookFinderUtil.java.

Please help me in this.

Thanks,
Sharath

[Jignesh Vachhani，修改在11 年前。]

Yes Sarath,

That will be created manually and again need to run ant build-service .
Then you can call this method from utility class.