trying to run a named query
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",
resultSetMappin开发者_如何学Pythong=" 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?
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.
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.
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; }
}
精彩评论