run custom sql to access existing liferay tables

[king hu, geändert vor 12 Jahren.]

Hello, everyone

We have the requirement to write custom sql to access existing liferay tables. Below is how i am doing this:

(1) Write persistence interface:
public interface EntityPersistence {
public List<Entity> getEntities(long companyId) throws SystemException;
}

(2) Implements the persistence interface and extends the BasePersistenceImpl.Set the session factory in the constructor

public class EntityPersistenceImpl extends BasePersistenceImpl implements EntityPersistence {

public CommunityPersistenceImpl() {
setSessionFactory((SessionFactory) PortalBeanLocatorUtil.getBeanLocator().locate(
LayoutModelImpl.SESSION_FACTORY));
setDataSource((DataSource) PortalBeanLocatorUtil.getBeanLocator().locate(LayoutModelImpl.DATA_SOURCE));
}

@Override
public List<Entity> getEntities(long companyId) throws SystemException {
Session session = null;
List results = null;
try {
sql="";
session = openNewSession(getDataSource().getConnection());
SQLQuery query = session.createSQLQuery(sql);
results = QueryUtil.list(query, getDialect(), 0, size);
} catch (Exception e) {
System.out.println(e);
throw processException(e);
} finally {
closeSession(session);
}

..........
}

This way does work and i can get the results from database. But the problem is that there is performance issue here by using openNewSession(). I want to use openSession() method from the BasePersistenceImpl. But when i tried this, i got the following exceptions:

org.hibernate.HibernateException: No Hibernate Session bound to thread, and configuration does not allow creation of non-transactional one here
03:01:21,875 ERROR [BasePersistenceImpl:188] Caught unexpected exception org.hibernate.HibernateException

Can anyone help on this? Or is there other solutions to access existing liferay tables?

[jelmer kuperus, geändert vor 12 Jahren.]

My answer in this thread may help :

http://www.liferay.com/community/forums/-/message_boards/message/7158040

You still need a transaction though. The easiest way to solve it is to generate an empty servicebuilder service and call the finder from there

[king hu, geändert vor 12 Jahren.]

Thanks for your answer. I also tried service builder but always get an error "BeanLocator is null". Below are the steps i tried:
(1) write service.xml under /WEB-INF:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 6.0.0//EN" "http://www.liferay.com/dtd/liferay-service-builder_6_0_0.dtd">
<service-builder package-path="xxx.x">
<author>King</author>
<namespace>CM</namespace>
<entity name="Entity" local-service="true" remote-service="false">
</entity>
</service-builder>

(2) run ant build-service
(3) Create EntityFinder interface manually

public interface EntityFinder {
public List<Entity> getEntity(long companyId) throws SystemException;
}
(4) Create EntityFinderImpl class which implements the EntityFinder interface and extends BasePersistenceImpl

public class EntityFinderImpl extends BasePersistenceImpl implements EntityFinder {

public EntityFinderImpl () {

}

@Override
public List<Entity> getEntity(long companyId) throws SystemException {
Session session = null;
List results = null;
try {
sql = "";
session = openSession();
SQLQuery query = session.createSQLQuery(sql);
query.setCacheable(false);
results = QueryUtil.list(query, getDialect(), 0, size);
} catch (Exception e) {
throw processException(e);
} finally {
closeSession(session);
}

....... }
}

(5) In the generated EntityLocalServiceImpl, add the following lines:
@BeanReference(type = EntityFinder.class)
private EntityFinder entityFinder;

public List getEntity(long companyId)
throws SystemException {

return entityFinder.getEntity(companyId,);
}

(6) rerun ant builid-service
(7) in generated portlet-spring.xml, add the followingn lines:


<bean id="xxx.EntityFinderclass="xxx.EntityFinderImpl" parent="basePersistence" />

What's wrong during these steps? Or could you show me the details on how to finish this?
Thanks for your great help!

[Tony Rad, geändert vor 12 Jahren.]

Hi,

Another option could be to use Liferay Dynamic query API in the service layer

Regards

[king hu, geändert vor 12 Jahren.]

Seems that dynamic query doesn't support left join

[king hu, geändert vor 12 Jahren.]

Seems that "BeanLocator is null... BeanLocator has not been set " issue is common to Liferay 6.0.x.

Can anybody help on fix this issue?