Unable to execute native sql query
- by Renjith
I am developing an application with Spring and hibernate. In the DAO class, I was trying to execute a native sql as follows:
SELECT * FROM product ORDER BY unitprice ASC LIMIT 6 OFFSET 0
But the system throws an exception.
org.hibernate.HibernateException: No Hibernate Session bound to thread, and configuration does not allow creation of non-transactional one here
org.springframework.orm.hibernate3.SpringSessionContext.currentSession(SpringSessionContext.java:63)
org.hibernate.impl.SessionFactoryImpl.getCurrentSession(SessionFactoryImpl.java:544)
com.dao.ProductDAO.listProducts(ProductDAO.java:15)
com.dataobjects.impl.ProductDoImpl.listProducts(ProductDoImpl.java:26)
com.action.ProductAction.showProducts(ProductAction.java:53)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
application-context.xml is show below
<bean id="propertyConfigurer"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"
p:location="/WEB-INF/jdbc.properties" />
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource"
p:driverClassName="${jdbc.driverClassName}"
p:url="${jdbc.url}"
p:username="${jdbc.username}"
p:password="${jdbc.password}" />
<!-- Hibernate SessionFactory -->
<!-- class="org.springframework.orm.hibernate3.LocalSessionFactoryBean"-->
<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource">
<ref local="dataSource"/>
</property>
<property name="configLocation">
<value>WEB-INF/classes/hibernate.cfg.xml</value>
</property>
<property name="configurationClass">
<value>org.hibernate.cfg.AnnotationConfiguration</value>
</property>
<!-- <property name="annotatedClasses">
<list>
<value>com.pojo.Product</value>
<value>com.pojo.User</value>
<value>com.pojo.UserLogin</value>
</list>
</property> -->
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">${hibernate.dialect}</prop>
<prop key="hibernate.show_sql">true</prop>
</props>
</property>
</bean>
<!-- User Bean definitions -->
<bean name="/logincheck" class="com.action.LoginAction">
<property name="userDo" ref="userDo" />
</bean>
<bean id="userDo" class="com.dataobjects.impl.UserDoImpl" >
<property name="userDAO" ref="userDAO" />
</bean>
<bean id="userDAO" class="com.dao.UserDAO" >
<property name="sessionFactory" ref="sessionFactory" />
</bean>
<bean name="/listproducts" class="com.action.ProductAction">
<property name="productDo" ref="productDo" />
</bean>
<bean id="productDo" class="com.dataobjects.impl.ProductDoImpl" >
<property name="productDAO" ref="productDAO" />
</bean>
<bean id="productDAO" class="com.dao.ProductDAO" >
<property name="sessionFactory" ref="sessionFactory" />
</bean>
And DAO class is
public class ProductDAO extends HibernateDaoSupport{
public List listProducts(int startIndex, int incrementor) {
org.hibernate.Session session = getHibernateTemplate().getSessionFactory().getCurrentSession();
String queryString = "SELECT * FROM product ORDER BY unitprice ASC LIMIT 6 OFFSET 0";
List list = null;
try{
session.beginTransaction();
org.hibernate.Query query = session.createQuery(queryString);
list = query.list();
session.getTransaction().commit();
} catch(Exception e) {
e.printStackTrace();
} finally {
session.close();
}
return list;
}
public List getProductCount() {
String queryString = "SELECT COUNT(*) FROM Product";
return getHibernateTemplate().find(queryString);
}
}
Any thoughts to fix it up?
