Carry a SELECT to a NAMED QUERY

Question

Carry a SELECT to a NAMED QUERY

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

I want to do the following SELECT but in a NAMED QUERY , I do not know if it's possible.

SELECT DISTINCT (q1.code ||'/'|| q1.name)
  FROM (
       SELECT c.code,c.name 
         FROM USER c
       ) q1

java hibernate jpa

asked by Gabriel Solarte 05.08.2016 в 16:53

source

2 answers

Filter by dates does not show data Connection to mysql databases from HTML

score 1 · Answer 1

The native query you have can be a @NamedNativeQuery , simply rename it in the "USER" entity (if you have one), for example:

@Entity
@Table(name = "USER")
@NamedNativeQuery(name = "nativeSQL", query = "TU_QUERY_NATIVA",  resultClass = String.class)
public class User {
 ...contenido...
}

And it is executed in the same way as the others named:

em.createNamedQuery("nativeSQL").getResultList();

The difference is that you will get a list of the class indicated in the resultClass of the annotation.

If the columns returned by the query are more complex and you want to map something more "manually", you can change the "resultclass" by the sqlResultSetMapping attribute where you can indicate a @ SqlResultSetMapping that you have declared in your application. In the javadoc of the annotation is this very explanatory example that is a sqlResultSetMapping:

Query q = em.createNativeQuery(
    "SELECT o.id AS order_id, " +
        "o.quantity AS order_quantity, " +
        "o.item AS order_item, " +
        "i.name AS item_name, " +
    "FROM Order o, Item i " +
    "WHERE (order_quantity > 25) AND (order_item = i.id)",
"OrderResults");

@SqlResultSetMapping(name="OrderResults", 
    entities={ 
        @EntityResult(entityClass=com.acme.Order.class, fields={
            @FieldResult(name="id", column="order_id"),
            @FieldResult(name="quantity", column="order_quantity"), 
            @FieldResult(name="item", column="order_item")})},
    columns={
        @ColumnResult(name="item_name")}
)

score 1 · Answer 2

You can use a @NamedNativeQuery which is a simpler option as follows:

@Entity
@Table(name = "USER")
@XmlRootElement
@NamedQueries({
@NamedNativeQuery(name = "USER.ejemploConsulta", query = "SELECT DISTINCT (q1.code ||'/'|| q1.name)
  FROM (
       SELECT c.code,c.name 
         FROM USER c
       ) q1")})

public class User {

}

but you can also create it as @NamedQuery: Example