createSQLQuery in Spring

[asli kilic]

Hi,

I'm using hibernate + spring
i have a query like "select * from (select..." but i saw that "select" is not supported after "from".
Then i decide to use native sql (createSQLQuery)

So i found some examples like
SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
session.createSQLQuery(sql.toString()).list();

Congifuration() above tries to find hibernate configuration file to get sessionfactory but i have not that file in spring, however i have applicationContext.xml for configuration.

How can i get the session to create SQL Query? Please help me...Thank you

[Mark Spritzler]

OK, so I don't understand your first comment about the "SELECT" but if you are using HQL, you do not need the select portion, if it was going to be "Select * from..."

Anyway, you have your Hibernate Session Factory defined in your Spring configuration. The next step is to inject the Session Factory into you DAO/Repository class. So you can either do that with a constructor or a settter. Now in your code you just call sessionFactory.getCurrentSession() to get the session and now you are good to go. Do not use the new Configuration().configure().buildSessionFactory(); you do not need to do that if you are using Spring and Spring is creating the SessionFactory for you.

Mark

[asli kilic]

hi Mark thank you for your answer
my sql query is
select max(P.notu)-1 from (select sum(ky.notu) as notu from Yorum ky group by ky.yorumPK.kitapSiraNo) P

actually, i wanted to get max of sums.

Could you give an example about how to inject the Session Factory into my DAO/Repository class via constructor please? I can't see any configuration about session factory in applicationContext.xml (configuration file of Spring)
[ September 15, 2008: Message edited by: asli kilic ]

[asli kilic]

I get session but i have a new problem
when i run the query i take the exception above
what is my mistake?

"Caused by: org.hibernate.exception.SQLGrammarException: could not execute query"

In my DAO

Session session = null;
StringBuffer sql = new StringBuffer("select y from Yorum y");
session = this.sessionFactory.getCurrentSession();
List list = session.createSQLQuery(sql.toString()).list();
 
@Autowired
public void setSessionFactory(SessionFactory sessionFactory) {
this.sessionFactory = sessionFactory;
}

part of applicationContext.xml (forum does not allow text contains url , i can not paste the header here)

<?xml version="1.0" encoding="UTF-8"?>

<context :-P	 roperty-placeholder location="classpath:ayarlar.properties"/>
<context:component-scan base-package="com.tai.kutuphane" />
<tx:annotation-driven transaction-manager="txManager"/>
 
<bean id="entityManagerFactory"
class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<property name="persistenceUnitName" value="Kutuphane"/>
<property name="dataSource" ref="myDataSource" />
<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
<property name="databasePlatform" value="${database.dialect}"/>
<property name="showSql" value="${database.showSql}" />
<property name="generateDdl" value="${database.generateDdl}" />
</bean>
</property>
</bean>
 
 <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" destroy-method="close">
        <property name="driverClassName" value="${database.driver}"/>
        <property name="url" value="${database.url}"/>
        <property name="username" value="${database.username}"/>
        <property name="password" value="${database.password}"/>
    </bean>
     <bean id="myDataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
       <property name="jndiName" value="java:comp/env/jdbc/kutuphane"/>
     </bean>
    
 
<bean id="txManager" class="org.springframework.orm.jpa.JpaTransactionManager">
<property name="entityManagerFactory" ref="entityManagerFactory" />
</bean>

<bean class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor"/>

<bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor" />

 
<!-- Spring/Hibernate Session Factory (obtained from JDNI) -->
<bean id="mySessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="hibernateProperties">
<value>
hibernate.dialect=org.hibernate.dialect.HSQLDialect
</value>
</property>
</bean>
 

 
<!-- This is a bean post processor.
It gets a chance to look at every bean in this file as it is created.
If the bean contains the @Transactional annotation,
a transactional proxy is automatically created to wrap it.
 -->
</beans>

[Mark Spritzler]

  
public class MyRepositoryImpl implements MyRepository {
    private SessionFactory sessionFactory;
    public MyRespository(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }
}
  

And for the applicationConfig.xml

  
 <bean id="myRepository" class="com.mycompany.MyRepositoryImpl">
     <constructor-arg ref="sessionFactory"/>
 </bean>
 
  

There you go a sessionFactory injected via a constructor. Now you already have the bean definition for the sessionFactory and all the others, I am not going to repeat that stuff.

Mark