org.hibernate.HibernateException: createSQLQuery is not valid without active transaction

Question

I'm getting this exception when i want to connect to my database via hibernate, i was trying a lot of things i found on the internet but nothing helped, some of my files: dao class with connection:

@Repository
public class UserDaoImpl implements UserDao {

    @Autowired
    SessionFactory sessionFactory;
//the problem with query is here
    public List<User> getAllUsers() {
        return sessionFactory.getCurrentSession().createSQLQuery("SELECT * FROM user").list();
    }

}

web.xml:

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">


    <display-name>Archetype Created Web Application</display-name>

    <servlet>
        <servlet-name>dispatcher</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <load-on-startup>1</load-on-startup>
    </servlet>

    <servlet-mapping>
        <servlet-name>dispatcher</servlet-name>
        <url-pattern>*.htm</url-pattern>
    </servlet-mapping>

    <context-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>classpath:context.xml</param-value>
    </context-param>

    <listener>
        <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
    </listener>

</web-app>

my servlet:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:mvc="http://www.springframework.org/schema/mvc"
       xsi:schemaLocation="
           http://www.springframework.org/schema/beans
           http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
           http://www.springframework.org/schema/context
           http://www.springframework.org/schema/context/spring-context-3.1.xsd
           http://www.springframework.org/schema/mvc
           http://www.springframework.org/schema/mvc/spring-mvc-3.1.xsd">

    <context:annotation-config />
    <context:component-scan base-package="com.lime" />

    <mvc:annotation-driven />
    <mvc:default-servlet-handler />

    <bean id="viewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <property name="viewClass" value="org.springframework.web.servlet.view.JstlView" />
        <property name="prefix" value="/WEB-INF/" />
        <property name="suffix" value=".jsp" />
    </bean>

</beans>

and context.xml:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">

    <bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQL9Dialect</prop>
                <prop key="hibernate.show_sql">true</prop>
                <prop key="hibernate.current_session_context_class">thread</prop>
                <prop key="hibernate.connection.driver_class">org.postgresql.Driver</prop>
                <prop key="hibernate.connection.url">jdbc:postgresql://localhost:5432/come_to_blog_db</prop>
                <prop key="hibernate.connection.username">postgres</prop>
                <prop key="hibernate.connection.password">admin</prop>
            </props>
        </property>
        <property name="annotatedClasses">
            <list>
                <value>com.lime.model.User</value>
            </list>
        </property>
    </bean>

</beans>

Answer 1

Just try with this

@Repository
public class UserDaoImpl implements UserDao {

@Autowired
SessionFactory sessionFactory;
//the problem with query is here
public List<User> getAllUsers() {
    Session session=null;
    try 
    {
    Session session = sessionFactory.openSession();
    return session.createSQLQuery("SELECT * FROM user").list();
    }
    catch(Exception e)
    {
     //Logging
    }
    finally
    {
        if(session !=null && session.isOpen)
        {
          session.close();
          session=null;
        }
    }
}

}

Update

with genericDAO it gets the current session which needs to be explicitly open using openSession(), while getCurrentSession() just attaches it to the current session. According to the author

GenericDAO makes the assumption that you will be handling transactions externally to the DAO