Executing mysql query as native query in Hibernate

I am new to Hibernate. I am creating a web application using JSF, Spring and Hibernate. I am trying to implement this query generated from the user filter using hibernate

select * from ( Select DATE_FORMAT(a.call_offered_time,'%d %b %Y %k:%i:%s') As 'call_date', a.caller_id as 'Caller', DATE_FORMAT(a.call_offered_time,'%d %b %Y %k:%i:%s') as  'Call Connected', DATE_FORMAT(a.call_disconnect_time,'%d %b %Y %k:%i:%s') as 'Call Disconnected',a.channel_no as 'Channel', a.call_status as 'Call Status', (Case When  a.call_routed_to_agent=Null or  a.call_routed_to_agent='' then 'IVRS' Else call_routed_to_agent  End ) as 'Call Routed To', b.ddi,TIMESTAMPDIFF(SECOND,a.call_offered_time,a.call_disconnect_time) as 'Duration'from CallDetail a,ddi_details b Where a.call_reference_no =b.call_reference_no and a.call_offered_time Between '2006-01-01 00:00:00' And '2010-11-20 23:59:00') as t order by STR_TO_DATE(t.call_date,'%d %b %Y %k:%i:%s')

For this I tried the following code

Session session = null;
    // This step will read hibernate.cfg.xml and prepare hibernate for use
    SessionFactory sessionFactory = this.getSessionFactory(); 

    session =sessionFactory.openSession();



    //Create Select Clause HQL
     String SQL_QUERY ="select * from ( Select DATE_FORMAT(a.call_offered_time,'%d %b %Y %k:%i:%s') As 'call_date', " +
                       "a.caller_id as 'Caller', DATE_FORMAT(a.call_offered_time,'%d %b %Y %k:%i:%s') as  " +
                       "'Call Connected', DATE_FORMAT(a.call_disconnect_time,'%d %b %Y %k:%i:%s') as " +
                       "'Call Disconnected',a.channel_no as 'Channel', a.call_status as 'Call Status', " +
                       "(Case When  a.call_routed_to_agent=Null or  a.call_routed_to_agent='' then 'IVRS' " +
                       "Else call_routed_to_agent  End ) as 'Call Routed To', b.ddi," +
                       "TIMESTAMPDIFF(SECOND,a.call_offered_time,a.call_disconnect_time) as " +
                       "'Duration'from CallDetail a,iss_master.ddi_details b Where " +
                       "a.call_reference_no =b.call_reference_no and a.call_offered_time Between " +
                       "'2006-01-01 00:00:00' And '2010-11-20 23:59:00') as t order by STR_TO_DATE(t.call_date,'%d %b %Y %k:%i:%s')";

     System.out.println(SQL_QUERY);

    Query query = session.createSQLQuery(SQL_QUERY).addEntity(InboundCallDetail.class);

 开发者_如何学JAVA   List result = query.list();

When I run this code I get the following error

org.hibernate.MappingException: Unknown entity: com.address.InboundCallDetail
at org.hibernate.impl.SessionFactoryImpl.getEntityPersister(SessionFactoryImpl.java:580)
at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.getSQLLoadable(SQLQueryReturnProcessor.java:335)
at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.processRootReturn(SQLQueryReturnProcessor.java:376)
at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.processReturn(SQLQueryReturnProcessor.java:355)
at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.process(SQLQueryReturnProcessor.java:171)
at org.hibernate.loader.custom.sql.SQLCustomQuery.<init>(SQLCustomQuery.java:87)
at org.hibernate.engine.query.NativeSQLQueryPlan.<init>(NativeSQLQueryPlan.java:67)
at org.hibernate.engine.query.QueryPlanCache.getNativeSQLQueryPlan(QueryPlanCache.java:136)
at org.hibernate.impl.AbstractSessionImpl.getNativeSQLQueryPlan(AbstractSessionImpl.java:160)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
at com.address.AddressDAOImpl.getDetails(AddressDAOImpl.java:102)
at com.address.AddressBean.getDetail(AddressBean.java:46)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at javax.el.BeanELResolver.getValue(BeanELResolver.java:62)
at javax.el.CompositeELResolver.getValue(CompositeELResolver.java:54)
at com.sun.faces.el.FacesCompositeELResolver.getValue(FacesCompositeELResolver.java:72)
at org.apache.el.parser.AstValue.getValue(AstValue.java:123)
at org.apache.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:186)
at com.sun.facelets.el.TagValueExpression.getValue(TagValueExpression.java:71)
at javax.faces.component.UIData.getValue(UIData.java:614)
at javax.faces.component.UIData.getDataModel(UIData.java:1145)
at javax.faces.component.UIData.setRowIndex(UIData.java:451)
at com.sun.faces.renderkit.html_basic.TableRenderer.encodeBegin(TableRenderer.java:77)
at javax.faces.component.UIComponentBase.encodeBegin(UIComponentBase.java:813)
at javax.faces.component.UIData.encodeBegin(UIData.java:983)
at javax.faces.component.UIComponent.encodeAll(UIComponent.java:928)
at javax.faces.component.UIComponent.encodeAll(UIComponent.java:933)
at com.sun.facelets.FaceletViewHandler.renderView(FaceletViewHandler.java:592)
at org.ajax4jsf.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:100)
at org.ajax4jsf.application.AjaxViewHandler.renderView(AjaxViewHandler.java:176)
at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:110)
at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:100)
at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:139)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:266)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.ajax4jsf.webapp.BaseXMLFilter.doXmlFilter(BaseXMLFilter.java:178)
at org.ajax4jsf.webapp.BaseFilter.handleRequest(BaseFilter.java:290)
at org.ajax4jsf.webapp.BaseFilter.processUploadsAndHandleRequest(BaseFilter.java:388)
at org.ajax4jsf.webapp.BaseFilter.doFilter(BaseFilter.java:515)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:852)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:619)

Please correct my mistake or suggest any alternate ways.

Thanks in advance,

P.S This is a report application so the 'where' condition in the query is generated by the user through the filter.

Update Sorry for being late! Since I created the application using Spring with Hibernate I did not create hibernate-config.xml instead I furnished the details in application-context.xml

My application-context.xml file is

<?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:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">


<!-- DataSource Definition -->
<bean id="dataSource"
    class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <!-- MySQL Database -->
    <property name="driverClassName">
        <value>com.mysql.jdbc.Driver</value>
    </property>
    <property name="url">
        <value>jdbc:mysql://localhost:3306/spring_hibernate?characterEncoding=UTF-8</value>
    </property>
    <property name="username">
        <value>root</value>
    </property>
    <property name="password">
        <value>sa</value>
    </property>
</bean>

<!-- Hibernate SessionFactory Definition -->
<bean id="sessionFactory"
    class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="mappingResources">
        <list>
            <value>
                com/address/Address.hbm.xml
            </value>                
        </list>
    </property>

    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">
                org.hibernate.dialect.MySQLDialect
            </prop>
            <prop key="hibernate.show_sql">true</prop>
            <prop key="hibernate.hbm2ddl.auto">update</prop>
            <prop key="hibernate.cglib.use_reflection_optimizer">
                true
            </prop>
            <prop key="hibernate.cache.provider_class">
                org.hibernate.cache.HashtableCacheProvider
            </prop>
            <prop key="hibernate.connection.useUnicode">true</prop>
            <prop key="hibernate.connection.characterEncoding">UTF-8</prop>
        </props>
    </property>

    <property name="dataSource">
        <ref bean="dataSource" />
    </property>
</bean>

<!-- Spring Data Access Exception Translator Definition -->
<bean id="jdbcExceptionTranslator"
    class="org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator">
    <property name="dataSource">
        <ref bean="dataSource" />
    </property>
</bean>

<!-- Hibernate Template Definition -->
<bean id="hibernateTemplate"
    class="org.springframework.orm.hibernate3.HibernateTemplate">
    <property name="sessionFactory">
        <ref bean="sessionFactory" />
    </property>
    <property name="jdbcExceptionTranslator">
        <ref bean="jdbcExceptionTranslator" />
    </property>
</bean> 



<bean id="dao" class="com.address.AddressDAOImpl">  
<property name="hibernateTemplate">
        <ref bean="hibernateTemplate" />
    </property>                 
</bean>
<bean id="addressBean"
    class="com.address.AddressBean">
    <property name="dao">
        <ref local="dao" />
    </property>         
</bean>
<!-- ========================= Start of SERVICE DEFINITIONS =========================
-->

<!-- Hibernate Transaction Manager Definition -->
<bean id="transactionManager"
    class="org.springframework.orm.hibernate3.HibernateTransactionManager">
    <property name="sessionFactory">
        <ref local="sessionFactory" />
    </property>
</bean>

As @Shervin suggested I tried createNativeSQL(), I got the following error

Caused by: org.springframework.beans.BeanInstantiationException: Could not instantiate bean class [com.address.AddressDAOImpl]: Constructor threw exception; nested exception is javax.persistence.PersistenceException: org.hibernate.HibernateException: Hibernate Dialect must be explicitly set
at org.springframework.beans.BeanUtils.instantiateClass(BeanUtils.java:115)
at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:61)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateBean(AbstractAutowireCapableBeanFactory.java:877)
... 31 more

Caused by: javax.persistence.PersistenceException: org.hibernate.HibernateException: Hibernate Dialect must be explicitly set
at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:698)
at org.hibernate.ejb.HibernatePersistence.createEntityManagerFactory(HibernatePersistence.java:121)
at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:51)
at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:33)
at com.address.AddressDAOImpl.<init>(AddressDAOImpl.java:19)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at org.springframework.beans.BeanUtils.instantiateClass(BeanUtils.java:100)
... 33 more

Caused by: org.hibernate.HibernateException: Hibernate Dialect must be explicitly set
at org.hibernate.dialect.DialectFactory.determineDialect(DialectFactory.java:80)
at org.hibernate.dialect.DialectFactory.buildDialect(DialectFactory.java:62)
at org.hibernate.cfg.SettingsFactory.determineDialect(SettingsFactory.java:460)
at org.hibernate.cfg.SettingsFactory.buildSettings(SettingsFactory.java:155)
at org.hibernate.cfg.Configuration.buildSettings(Configuration.java:2101)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1325)
at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:691)
... 42 more

My updated java code is as follows

@PersistenceContext private EntityManager em;
 EntityManagerFactory emf = Persistence.createEntityManagerFactory("myapp");

em = emf.createEntityManager();
Query query = em.createNativeQuery(SQL_QUERY);

Please correct my problems. Thanks for updates!!

Update

I tried as suggested by @ckuetbach and I added

<value>
        com/address/InboundCallDetail.hbm.xml
    </value>   

in the application-context.xml file

Now I am getting this error

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactory' defined in ServletContext resource [/WEB-INF/applicationContext.xml]: Invocation of init method failed; nested exception is org.hibernate.MappingException: column attribute may not be used together with <column> subelement
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1337)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:473)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory$1.run(AbstractAutowireCapableBeanFactory.java:409)
at java.security.AccessController.doPrivileged(Native Method)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:380)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:264)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:221)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:261)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:185)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:164)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:423)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:729)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:381)
at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:255)
at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:199)
at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:45)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3972)
at org.apache.catalina.core.StandardContext.start(StandardContext.java:4467)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
at org.apache.catalina.core.StandardHost.start(StandardHost.java:785)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
at org.apache.catalina.core.StandardService.start(StandardService.java:519)
at org.apache.catalina.core.StandardServer.start(StandardServer.java:710)
at org.apache.catalina.startup.Catalina.start(Catalina.java:581)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:289)
at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:414)
Caused by: org.hibernate.MappingException: column attribute may not be used together with <column> subelement
at org.hibernate.cfg.HbmBinder.bindColumns(HbmBinder.java:1075)
at org.hibernate.cfg.HbmBinder.bindColumnsOrFormula(HbmBinder.java:1547)
at org.hibernate.cfg.HbmBinder.bindSimpleValue(HbmBinder.java:1143)
at org.hibernate.cfg.HbmBinder.createClassProperties(HbmBinder.java:2102)
at org.hibernate.cfg.HbmBinder.createClassProperties(HbmBinder.java:2060)
at org.hibernate.cfg.HbmBinder.bindRootPersistentClassCommonValues(HbmBinder.java:381)
at org.hibernate.cfg.HbmBinder.bindRootClass(HbmBinder.java:295)
at org.hibernate.cfg.HbmBinder.bindRoot(HbmBinder.java:166)
at org.hibernate.cfg.Configuration.add(Configuration.java:702)
at org.hibernate.cfg.Configuration.addInputStream(Configuration.java:537)
at org.springframework.orm.hibernate3.LocalSessionFactoryBean.buildSessionFactory(LocalSessionFactoryBean.java:631)
at org.springframework.orm.hibernate3.AbstractSessionFactoryBean.afterPropertiesSet(AbstractSessionFactoryBean.java:211)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1368)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1334)
... 30 more

Please correct my mistake!

---

If you want to execute mysql query as native query in Hibernate you can use createNativeQuery()

---

I think you missed to include a mapping to you Entity. You have to create and add mapping files for all of your Entities.

<property name="mappingResources">
    <list>
        <value>
            com/address/Address.hbm.xml
        </value>                
        <value>
            com/address/InboundCallDetail.hbm.xml
        </value>     
    </list>
</property>

The second problem seems to be, that you did not set the Hibernate Dialect Hibernate Dialect must be explicitly