native query and mapping

vitor · **Joined:** Tue Aug 09, 2011 1:50 pm **Posts:** 3

Hello,

AS: Jboss.6.0.0.Final (jboss uses Hibernate 3.6.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:

Quote:

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.

Quote:

@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:

Quote:

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:

Code:

@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 Hibernate.
when i try to execute this query i get exception:

Quote:

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 also:

Quote:

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

and mapping:

Quote:

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
vitor

janu019 · **Joined:** Sat Nov 07, 2009 10:31 am **Posts:** 22

Here you mean to say It worked by adding cityId because its a primary key?

Or you have all the feilds mapped instead of some?

janu019 · **Joined:** Sat Nov 07, 2009 10:31 am **Posts:** 22

I got what the issue is.

we must need to fetch Primary key column in the native Query.

in your case try fetching cityId, cityName both (not all) this will work.

here to map native result with an entity it expects Primary Key (@Id feild). This may be because to avoid object idenetiy (Ex : let us consider you are loading more than 1 country )
then it may put it in Set<County> in this case duplicates will be removed only one country will be returned)

To avoid that situation they made it compulsory to have id fetch in Native Query.

Let me knwo if it is not working.

vitor · **Joined:** Tue Aug 09, 2011 1:50 pm **Posts:** 3

Hey,

Thanks for your reply.

I thought that my error was becouse of lack of primary key fetched from database. I tried to get only primary key (city_id), and city_name but unfortunately I got the same exception.
So for me that works only when i fetch all columns from database. But i want to get only some of them, and i cannot force it to work.
I'm confused.

vitor

janu019 · **Joined:** Sat Nov 07, 2009 10:31 am **Posts:** 22

No. If you have primary key and one city alone it should work.

This will work. please test it again properly and let me knw.

vitor · **Joined:** Tue Aug 09, 2011 1:50 pm **Posts:** 3

Hmm...

Originally I tried native query with mapping using Jboss. So I decided to download Hibernate 3.6.0 and Hibernate 3.6.6 to try my example using pure Hibernate.
I got the same exception.
And I don't know how to test it more properly.

Simply that works:

Code:

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

Quote:

SQLQuery query = session.createSQLQuery("select c.city_id, c.city_name, c.city_population from test_city c");
query.setResultSetMapping("testCityNameAndPopulationMapping");
List<Object> objects = query.list();

and this does not:

Code:

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

Quote:

SQLQuery query = session.createSQLQuery("select c.city_id, c.city_name from test_city c");
query.setResultSetMapping("testCityNameAndPopulationMapping");
List<Object> objects = query.list();

This should be easy, why it is not?
Now I don't have any clue how to force my example to work.

vitor