2

In Java application which uses JPA 2.1 with Hibernate 4.3.11 implementation I am trying to use SqlResultSetMapping to map native query results to entity. Query includes joined two tables with same column names, so I need to use aliases and map them (issue described here: http://www.tinesoft.com/java/be-aware-of-mutliple-result-mappings-in-jpa)

For simplicity of question I shrank query and entity to minimum, that still causes the issue. Real code uses two entities and DB function, which is the reason for using native query instead of JPQL.

Gateway entity:

@Entity
public class Gateway implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private Integer active;
    ...
    @Column(name = "activation_code")
    private String activationCode;
    ...
}

SqlResultSetMapping:

@SqlResultSetMapping(
        name = "GatewayWithLoc",
        entities = {
            @EntityResult( entityClass = Gateway.class , fields =     @FieldResult(name = "id", column = "gw_id"))
        }
)

Query:

Query query = em.createNativeQuery("SELECT gw.id AS gw_id, ..., active, activation_code, ... FROM gateway gw", "GatewayWithLoc");
List<Object[]> rows = query.getResultList();

Exception:

Caused by: org.postgresql.util.PSQLException: The column name activati2_1_0_ was not found in this ResultSet.
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.findColumn(AbstractJdbc2ResultSet.java:2803)
...

If I use SELECT * without fields = @FieldResult... in SqlResultSetMapping, code works as expected, but I can't do that because of same column names in different tables.

I will solve this by manual mapping results to entities as I need quick solution, but it would be great to know if I am doing something wrong or Hibernate does not support what I am trying to do. Book "Pro JPA 2" contains very similar example which is supposed to work.

Update: Tested with Hibernate 5.1.1 (Spring 4.3.2) - same result

Update: It looks like all column names need to be specified as mentioned in multiple answers. This seems like a big issue of JPA/Hibernate to me - those column names could be derived from entity, with just exceptions specified manually. Instead, I need to write column names 3 times (1. query, 2. result set mapping 3. entity) which is ugly and difficult to maintain.

Miha
  • 45
  • 1
  • 6
  • 1
    I have only today discovered this issue. I wasn't too happy with JPA before, as I am pretty disappointed at the lowe expressiveness of JPQL (i need native query way too often I feel), but now this... It's dramatic! I have to list 20 columns and if just one of them changes I have to fix it in 3 places. This is a disaster and the very problem I was trying to solve with JPA + Annotations in the first place. Why not just depend on order? First column named 'id' corresponds with first entity that has an 'id' field, next column to next entity etc. – Stijn de Witt Feb 01 '17 at 19:59

3 Answers3

1

I think you need to specify all the @FieldResult explicitly especially since you have column names that are conflicting between the two tables.

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
0

Looks like the native query does not map to the SqlResultSetMapping. You have way more columns in your query than in your mapping. The selected columns in your query have to match your mapping.

Jürgen
  • 418
  • 3
  • 7
  • It looks like it. But there are JPA examples (e.g. in book "Pro JPA 2") that show that is valid - only column names that do not match entity fields must be explicitly specified. I also tried to specify everything, but encountered different issues. Not to mention that it looked extremely ugly - each of ~50 fields needed to be specified 3 times (1. in entity, 2. in query, 3. in result set mapping). – Miha Aug 31 '16 at 08:13
  • Have a look here: http://www.thoughts-on-java.org/result-set-mapping-basics/ There is stated "The query needs to return all properties of the entity and the JPA implementation" – Jürgen Aug 31 '16 at 08:18
  • My query does return all properties of the entity. They are just not specified in the mapping, because JPA should be able to determine their names from entity. As mentioned in original post, if I don't specify *any* field in mapping (only entities) and don't use the aliases, it works fine. So JPA is clearly able to establish column<>propery mapping, but it stops using it as soon as single exception is defined in "fields=..". – Miha Aug 31 '16 at 08:33
  • Here is a supposedly working example that uses same principle as my code: http://www.java2s.com/Code/Java/JPA/SqlResultsetMappingWithAlias.htm - I will do more tests on the topic when time permits. It would be great if this worked. – Miha Aug 31 '16 at 08:45
  • Does your `Gateway`class have all the attributes selected in the native query? – Jürgen Aug 31 '16 at 09:01
  • Yes, it does have all the attributes. – Miha Aug 31 '16 at 09:29
  • No need. The problem he describes is real. JPA simply selects the first column named `'id'` for every entity that has a field `'id'`... so all your entities will have the same ID... – Stijn de Witt Feb 01 '17 at 20:02
0

You can use SELECT NEW :

  1. Create a class has all column needed use in query .

    package dz.my;

public class GatewayGatewayWithLoc {
    private Integer idGw;
    private Integer activeGw;
    private Integer idGwWl;
    private Integer activeGwWl;

        public GatewayGatewayWithLoc(Integer idGw, Integer activeGw, Integer idGwWl, Integer activeGwWl) {
            this.idGw = idGw;
            this.activeGw = activeGw;
            this.idGwWl = idGwWl;
            this.activeGwWl = activeGwWl;
        }
       ....
     // getter and setter
     ...... 

    }

  2. Use the Query

    Query query = em.createQuery("SELECT NEW dz.my.GatewayGatewayWithLoc(gw.id, gw.active, gwwl.id, gwwl.active) FROM Gateway gw ,GatewayGatewayWithLoc gwwl WHERE ...");
List<GatewayGatewayWithLoc> liste = query.getResultList();
// display the answer
for (GatewayGatewayWithLoc gwgw : liste) {
    System.out.println("GatewayGatewayWithLoc : " + gwgw.getIdGw() + " " + gwgw.getActiveGw()+ " " + gwgw.getIdGwWl()+ " " + gwgw.getActiveGwWl());
}`
Samir CHEKOR
  • 36
  • 7
  • Thanks for your response, SELECT NEW looks generally useful. However in my case I need to use native query as count() on custom DB function is used (which further uses output of another DB function as input). – Miha Sep 01 '16 at 09:22