0 Replies Latest reply on Aug 9, 2011 1:35 PM by Hubert Lyczek

    native query and mapping

    Hubert Lyczek Newbie

      Hello,

       

      AS: Jboss.6.0.0.Final

      DB: MySQL 5.1

       

      I cannot force my simple native query with result mapping to work.

      I have simple test table. It looks like that:

      create table test_city ( 
            city_id int not null auto_increment
            city_name varchar(256), 
            city_population int
            primary key (city_id)
      );

       

      I have entity bean that represents this table.

       

       

      @Entity
      @Table(name="test_city")
       ...
       public class TestCity implements Serializable {
        private static final long serialVersionUID = 1L;
      
        @Id
        @GeneratedValue(strategy=GenerationType.AUTO)
        @Column(name="city_id", unique=true, nullable=false)
        private Integer cityId;
      
        @Column(name="city_name", length=256)
        private String cityName;
      
        @Column(name="city_population")
        private Integer cityPopulation;
       ...
      }
      
      

       

      Simple and easy.

       

      Now i want to get values from some columns (not all), let's say from city_name and city_population.

      In order to do this i have to use native query and mapping.

      My code:

       

       

      String sql = "select c.city_name, c.city_population from test_city c";
      Query query = entityManager.createNativeQuery(sql, "testCityNameAndPopulationMapping");
      return (List<TestCity>) query.getResultList();
       
      

       

      The mapping testCityNameAndPopulationMapping looks like that:

       

       

      @SqlResultSetMapping(
         name="testCityNameAndPopulationMapping",
         entities={
           @EntityResult(
             entityClass=TestCity.class,
             fields={
              @FieldResult(name="cityName", column="city_name"),
              @FieldResult(name="cityPopulation", column="city_population")
             }
           ) 
         }
      )
      
      

       

      For me looks good, but not for Jboss.

      when i try to execute this query i get exception:

       

       

      Exception in thread "main" javax.ejb.EJBException: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
      ...
      Caused by: java.sql.SQLException: Column 'city1_126_0_' not found.
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
      ...

       

      Hmm, why i get Column 'city1_126_0' not found?

      I cannot figure this out.

       

      But when I modify query to get city_id:

       

        
      String sql = 
      "select c.city_id, c.city_name, c.city_population from test_city c";
      

       

      and mapping:

       

       

       fields={  
              @FieldResult(name="cityId", column="city_id"),
              @FieldResult(name="cityName", column="city_name"),
              @FieldResult(name="cityPopulation", column="city_population")
             }
      
      

       

      everything works fine. But the idea to use mapping is to map some fields, we have data for.

       

      So, could you guys tell me what is wrong with my original code, please?

      Thanks in advance

      Hubert