trying to run a named query

Question

hi,

I’m doing the following:

@Entity
@SqlResultSetMapping(name="getxxxx",
        entities=@EntityResult(xxxx.class,
            fields = {
                    @FieldResult(name="x1", column = "x1"),
                    @FieldResult(name="x2", column = "x2")}))

@NamedNativeQuery(name=" getxxxx ",
    query="select x1, x2 from yyyy", 
    resultSetMapping=" getxxxx ")

} )public class xxxx{

    .
    .
    .
    public xxxx() {

}

i get an error: "Table "xxxx" cannot be resolved", the class xxxx is not a table mapped into my source, I’m trying to query the DB and return the results into my class is it possible?

Answer 1

In this situation the first thing I would try would be to remove the @Entity annotation. And then change either the class name or the native query name so that one of them is "xxxx" and one of them is "zzzz," so that I was sure I knew which thing the runtime was complaining about.

Answer 2

It sounds like xxxx should not be an entity bean, since JPA is not happy with returning results in non-entity beans. You must instead call createNativeQuery with just the SQL String. Then call query.getResultList() to fetch the result as a List(Object[]) and use this to fill your non entity result bean.

A few years back I wrote a blog post, that might help you perform advanced native queries with JPA.

Answer 3

Yes, this is possible, but a little tricky. Here's a complex example that should cover most of the bases. In this example:

• You have an INVOICE object with a due date;
• Each INVOICE has a many-to-one relationship with a COMPANY;
• Each INVOICE also has a zero- or one-to-many relationship with a set of ITEMS

Here is the schema:

CREATE TABLE "public"."invoice" (
  id SERIAL, 
  company_id int, 
  due_date date, 
  PRIMARY KEY(id)
);
CREATE TABLE "public"."item" (
  id SERIAL, 
  invoice_id int,
  description text,
  PRIMARY KEY(id)
);
CREATE TABLE "public"."company" (
  id SERIAL,
  name text,
  PRIMARY KEY(id)
);

The INVOICE object (incredibly convoluted example for the sake of completeness):

@Entity
@Table(name = "invoice")
@Loader(namedQuery = "loadInvoiceObject")
@NamedNativeQuery(name="loadInvoiceObject",
  query="SELECT " +
        "inv.id," +
        "inv.due_date," +
        "co.*," +
        "it.*," +
        "FROM invoice inv " +
        "JOIN company co ON co.id = inv.company_id " +
        "LEFT OUTER JOIN item it ON it.invoice_id = inv.id " +
        "WHERE inv.id = :id",
  resultSetMapping = "invoicemap")
@SqlResultSetMapping(name = "invoicemap",
  entities = {
    @EntityResult(entityClass = Invoice.class),
    @EntityResult(entityClass = Company.class),
    @EntityResult(entityClass = Item.class)
  }
)
public class Invoice {

  private Integer id;
  private Date dueDate;
  private Company company;
  private List<Item> items = new ArrayList<Item>();

  public Invoice() { /* no-args constructor */ }

  @Id
  @Column(name = "id", nullable = false)
  public Integer getId() { return id; }
  public void setId(Integer id) { this.id = id; }

  @Column(name = "due_date")
  @Temporal(TemporalType.DATE)
  public Date getDueDate() { return dueDate; }
  public void setDueDate(Date dueDate) { this.dueDate = dueDate; }

  @ManyToOne(optional = false)
  @JoinColumn(name = "company_id", nullable = false)
  public Company getCompany() { return company; }
  public void setCompany(Company company) { this.company = company; }

  @OneToMany(mappedBy = "invoice")
  public List<Item> getItems() { return items; }
  public void setItems(List<Item> items) { this.items = items; }

}

The ITEM object:

@Entity
@Table(name = "item")
public class Item {

  private Integer id;
  private String description;
  private Invoice invoice;

  public Item() { /* no-args constructor */ }

  @Id
  @Column(name = "id", nullable = false)
  public Integer getId() { return id; }
  public void setId(Integer id) { this.id = id; }

  @Column(name = "description")
  public String getDescription() { return description; }
  public void setDescription(String description) { this.description = description; }

  @ManyToOne(optional = false)
  @JoinColumn(name = "invoice_id", nullable = false)
  public Invoice getInvoice() { return invoice; }
  public void setInvoice(Invoice invoice) { this.invoice = invoice; }

}

The COMPANY object:

@Entity
@Table(name = "company")
public class Company {

  private Integer id;
  private String name;
  private List<Invoice> invoices = new ArrayList<Invoice>();

  public Company() { /* no-args constructor */ }

  @Id
  @Column(name = "id", nullable = false)
  public Integer getId() { return id; }
  public void setId(Integer id) { this.id = id; }

  @Column(name = "name")
  public String getName() { return name; }
  public void setName(String name) { this.name = name; }

  @OneToMany(mappedBy = "company")
  public List<Invoice> getInvoices() { return invoices; }
  public void setInvoices(List<Invoice> invoices) { this.invoices = invoices; }

}