4 Replies Latest reply on Oct 26, 2005 11:22 AM by dornus

    Am I missing something about createNativeQuery?

      I'm trying to do a basic native query in MySql to see how it works.

       private Timestamp getServerTime(){
       final Query q = em.createNativeQuery(
       "select now()", Timestamp.class
       );
       return (Timestamp)q.getSingleResult();
       }
      


      Is there a reason this doesn't work?
      [STDOUT] org.hibernate.MappingException: Unknown entity: java.sql.Timestamp
       at org.hibernate.impl.SessionFactoryImpl.getEntityPersister(SessionFactoryImpl.java:648)
       at org.hibernate.loader.custom.SQLQueryReturnProcessor.getSQLLoadable(SQLQueryReturnProcessor.java:53)
       at org.hibernate.loader.custom.SQLQueryReturnProcessor.processRootReturn(SQLQueryReturnProcessor.java:119)
       at org.hibernate.loader.custom.SQLQueryReturnProcessor.processReturn(SQLQueryReturnProcessor.java:98)
       at org.hibernate.loader.custom.SQLQueryReturnProcessor.process(SQLQueryReturnProcessor.java:87)
       at org.hibernate.loader.custom.SQLCustomQuery.<init>(SQLCustomQuery.java:105)
       at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:156)
       at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:650)
       at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:43)
      


        • 1. Re: Am I missing something about createNativeQuery?

          I am also trying something similar with one of my par classes and not having any luck

           private MyParClass getResult(String id) {
           final Query q = em.createNativeQuery(
           "from MyParClass o " +
           "where myId = :id "+
           "and myDate + INTERVAL 24 hour >= now() ",MyParClass.class
           );
           q.setParameter("id", id);
          
           return (MyParClass) q.getSingleResult();
           }
          
          


          • 2. Re: Am I missing something about createNativeQuery?

            The error for the par query is

            11:08:07,144 WARN [JDBCExceptionReporter] SQL Error: 1064, SQLState: 42000
            11:08:07,144 ERROR [JDBCExceptionReporter] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the righ
            t syntax to use near 'from MyParClass o where myId = '1' and myDate + INTERVAL 2' at line 1
            11:08:07,159 INFO [STDOUT] org.hibernate.exception.SQLGrammarException: could not execute query
             at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
             at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
             at org.hibernate.loader.Loader.doList(Loader.java:2150)
             at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2026)
             at org.hibernate.loader.Loader.list(Loader.java:2021)
             at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:109)
             at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1544)
             at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:165)
             at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:650)
             at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:43)
            


            • 3. Re: Am I missing something about createNativeQuery?
              majohnst

              For a native query, I think you have to have a fully valid SQL statement. It looks like you may be using an HQL statement " from MyClass" when it should be "select field1, field2 from MyClass".

              • 4. Re: Am I missing something about createNativeQuery?

                Yep, you're right!

                It works now.

                Thanks!