0

I have a large database row, and want to only fetch some specific colums. Ideally they should be automatically mapped to my @Entity.

I tried the following, which did not work, as somehow the id attribute is required. Why?

@Entity
@NamedNativeQuery(
    name = "testQuery",
    query = "SELECT city FROM mytable",
    resultClass = MyEntity.class
)

public class MyEntity {
    @Id
    private int id;

    private String country;
    private String city;

    public MyEntity() {

    }

    //getter+setter for all attributes
}

Execution:

getEntityManager().createNamedQuery("testQuery", MyEntity.class);

Result:

WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper: SQL Error: 0, SQLState: 42703
ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper: The column id was not found in the ResultSet.
ERROR : PSQLException: The column id was not found in the ResultSet.

Even if I write SELECT id, city FROM mytable I would get: The column country was not found in the ResultSet.` So somehow every column that is present in the database, but not selected by the query, causes this failure. Why?

Regarding the duplicate mark:

Quote from that anser:

@NamedNativeQuery(
    name="jedisQry", 
    query = "SELECT name,age FROM jedis_table", 
    resultClass = Jedi.class
)

Then, we can simply do:

TypedQuery<Jedi> query = em.createNamedQuery("jedisQry", Jedi.class);
List<Jedi> items = query.getResultList();

Well, if that's the solution, it is exactly the same as my question code. Any for me it does not work??

Solution in my case:

@NamedNativeQuery(
    name = "testQuery",
    query = "SELECT city FROM mytable",
    resultSetMapping = "testResultSet"
)

@SqlResultSetMapping(
    name = "testResultSet",
    classes = {
        @ConstructorResult(
            targetClass = MyEntity.class,
            columns = {
                @ColumnResult(name = "city")
            }
        )
    }
)
Patrick
  • 1,717
  • 7
  • 21
  • 28
membersound
  • 81,582
  • 193
  • 585
  • 1,120
  • 1
    Your result cannot be the entity, it must by an `Object[]` - one "cell" for each column and one `Object[]` for each row. You are asking it to select only `city` and then somehow hydrate that into a fully fledged `MyEntity` - it should be obvious why JPA is confused. – Boris the Spider Sep 19 '14 at 09:30
  • Assume that it possible. You get you object partially filled. Then change something. Hibernate need to store you changes to db how do it guess what to do with not filled attributes? – talex Sep 19 '14 at 09:32
  • But shouldnt JPA be able to map ony the returned colums to the entity specified in the query? – membersound Sep 19 '14 at 09:32
  • Use dynamic query for that kind of requirement and retrieved as Object[] mapping to column values!! – Wundwin Born Sep 19 '14 at 09:32
  • Please see my update, where the answer code is exactly like mine in the question, but it does not work for me... – membersound Sep 19 '14 at 09:38
  • 1
    As stated in the accepted answer, you need to use a `ConstructorResult` or provide a `SqlResultSetMapping`. Neither of which you do. – Boris the Spider Sep 19 '14 at 09:42

0 Answers0