JPA/JPQL Query and Foreign Keys

Question

I'm trying to run a query where I get specific Work done by a Customer based of their id. I'm having troubles making a queue with JPQL that will actually retrieve all the data based on the foreign key of the customerId.

I've tried SELECT w FROM WorkEntry w WHERE w.customerId = 1 and that doesn't work. Every time I try to run it using the Persistence Unit testing I get the following error:

java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: Exception Description: Problem compiling [SELECT w FROM WorkEntry w WHERE w.customerId = 1]. [14, 23] The abstract schema type 'WorkEntry' is unknown. [32, 44] The state field path 'w.customerId' cannot be resolved to a valid type. at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManager‌​Impl.java:1605)

I'm wondering what I am missing? I'm fairly new to JPA and JPQL.

This is the customer class:

@Entity
@Table(name = "customer")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "Customer.findAll", query = "SELECT c FROM Customer c"),
@NamedQuery(name = "Customer.findByCustomerId", query = "SELECT c FROM Customer c WHERE c.customerId = :customerId"),
@NamedQuery(name = "Customer.findByFirstName", query = "SELECT c FROM Customer c WHERE c.firstName = :firstName"),
@NamedQuery(name = "Customer.findByLastName", query = "SELECT c FROM Customer c WHERE c.lastName = :lastName"),
@NamedQuery(name = "Customer.findByStreetAddress", query = "SELECT c FROM Customer c WHERE c.streetAddress = :streetAddress"),
@NamedQuery(name = "Customer.findByCity", query = "SELECT c FROM Customer c WHERE c.city = :city"),
@NamedQuery(name = "Customer.findByState", query = "SELECT c FROM Customer c WHERE c.state = :state"),
@NamedQuery(name = "Customer.findByPostalCode", query = "SELECT c FROM Customer c WHERE c.postalCode = :postalCode"),
@NamedQuery(name = "Customer.findByPhoneNumber", query = "SELECT c FROM Customer c WHERE c.phoneNumber = :phoneNumber"),
@NamedQuery(name = "Customer.findByEmail", query = "SELECT c FROM Customer c WHERE c.email = :email")})
public class Customer implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "customer_id")
private Integer customerId;
@Size(max = 75)
@Column(name = "first_name")
private String firstName;
@Size(max = 75)
@Column(name = "last_name")
private String lastName;
@Size(max = 250)
@Column(name = "street_address")
private String streetAddress;
@Size(max = 50)
@Column(name = "city")
private String city;
@Size(max = 50)
@Column(name = "state")
private String state;
@Size(max = 45)
@Column(name = "postal_code")
private String postalCode;
@Size(max = 45)
@Column(name = "phone_number")
private String phoneNumber;
// @Pattern(regexp="[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?", message="Invalid email")//if the field contains email address consider using this annotation to enforce field validation
@Size(max = 250)
@Column(name = "email")
private String email;
@OneToMany(mappedBy = "customerId")
private Collection<ExpenseEntry> expenseEntryCollection;
@OneToMany(mappedBy = "customerId")
private Collection<WorkEntry> workEntryCollection;

public Customer() {
}

public Customer(Integer customerId, String firstName, String lastName, String streetAddress, String city, String state, String postalCode, String phoneNumber, String email) {
    this.customerId = customerId;
    this.firstName = firstName;
    this.lastName = lastName;
    this.streetAddress = streetAddress;
    this.city = city;
    this.state = state;
    this.postalCode = postalCode;
    this.phoneNumber = phoneNumber;
    this.email = email;
}

public Customer(String firstName, String lastName, String streetAddress, String city, String state, String postalCode, String phoneNumber, String email) {
    this.firstName = firstName;
    this.lastName = lastName;
    this.streetAddress = streetAddress;
    this.city = city;
    this.state = state;
    this.postalCode = postalCode;
    this.phoneNumber = phoneNumber;
    this.email = email;
}


public Customer(Integer customerId) {
    this.customerId = customerId;
}

public Integer getCustomerId() {
    return customerId;
}

public void setCustomerId(Integer customerId) {
    this.customerId = customerId;
}

public String getFirstName() {
    return firstName;
}

public void setFirstName(String firstName) {
    this.firstName = firstName;
}

public String getLastName() {
    return lastName;
}

public void setLastName(String lastName) {
    this.lastName = lastName;
}

public String getStreetAddress() {
    return streetAddress;
}

public void setStreetAddress(String streetAddress) {
    this.streetAddress = streetAddress;
}

public String getCity() {
    return city;
}

public void setCity(String city) {
    this.city = city;
}

public String getState() {
    return state;
}

public void setState(String state) {
    this.state = state;
}

public String getPostalCode() {
    return postalCode;
}

public void setPostalCode(String postalCode) {
    this.postalCode = postalCode;
}

public String getPhoneNumber() {
    return phoneNumber;
}

public void setPhoneNumber(String phoneNumber) {
    this.phoneNumber = phoneNumber;
}

public String getEmail() {
    return email;
}

public void setEmail(String email) {
    this.email = email;
}

@XmlTransient
public Collection<ExpenseEntry> getExpenseEntryCollection() {
    return expenseEntryCollection;
}

public void setExpenseEntryCollection(Collection<ExpenseEntry> expenseEntryCollection) {
    this.expenseEntryCollection = expenseEntryCollection;
}

@XmlTransient
public Collection<WorkEntry> getWorkEntryCollection() {
    return workEntryCollection;
}

public void setWorkEntryCollection(Collection<WorkEntry> workEntryCollection) {
    this.workEntryCollection = workEntryCollection;
}

@Override
public int hashCode() {
    int hash = 0;
    hash += (customerId != null ? customerId.hashCode() : 0);
    return hash;
}

@Override
public boolean equals(Object object) {
    // TODO: Warning - this method won't work in the case the id fields are not set
    if (!(object instanceof Customer)) {
        return false;
    }
    Customer other = (Customer) object;
    if ((this.customerId == null && other.customerId != null) || (this.customerId != null && !this.customerId.equals(other.customerId))) {
        return false;
    }
    return true;
}

@Override
public String toString() {
    return "consultant.billing.entity.Customer[ customerId=" + customerId + " ]";
}

}

And this is my WorkEntry class:

@Entity
@Table(name = "work_entry")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "WorkEntry.findAll", query = "SELECT w FROM WorkEntry w"),
@NamedQuery(name = "WorkEntry.findByWorkEntryId", query = "SELECT w FROM WorkEntry w WHERE w.workEntryId = :workEntryId"),
@NamedQuery(name = "WorkEntry.findByDate", query = "SELECT w FROM WorkEntry w WHERE w.date = :date"),
@NamedQuery(name = "WorkEntry.findByHoursWorked", query = "SELECT w FROM WorkEntry w WHERE w.hoursWorked = :hoursWorked")})
public class WorkEntry implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "work_entry_id")
private Integer workEntryId;
@Column(name = "date")
@Temporal(TemporalType.DATE)
private Date date;
// @Max(value=?)  @Min(value=?)//if you know range of your decimal fields consider using these annotations to enforce field validation
@Column(name = "hours_worked")
private Double hoursWorked;
@JoinColumn(name = "activity_id", referencedColumnName = "activity_id")
@ManyToOne
private Activity activityId;
@JoinColumn(name = "customer_id", referencedColumnName = "customer_id")
@ManyToOne
private Customer customerId;

public WorkEntry() {
}

public WorkEntry(Date date, Double hoursWorked, Customer customerId, Activity activityId) {
    this.date = date;
    this.hoursWorked = hoursWorked;
    this.activityId = activityId;
    this.customerId = customerId;
}


public WorkEntry(Integer workEntryId) {
    this.workEntryId = workEntryId;
}

public Integer getWorkEntryId() {
    return workEntryId;
}

public void setWorkEntryId(Integer workEntryId) {
    this.workEntryId = workEntryId;
}

public Date getDate() {
    return date;
}

public void setDate(Date date) {
    this.date = date;
}

public Double getHoursWorked() {
    return hoursWorked;
}

public void setHoursWorked(Double hoursWorked) {
    this.hoursWorked = hoursWorked;
}

public Activity getActivityId() {
    return activityId;
}

public void setActivityId(Activity activityId) {
    this.activityId = activityId;
}

public Customer getCustomerId() {
    return customerId;
}

public void setCustomerId(Customer customerId) {
    this.customerId = customerId;
}

@Override
public int hashCode() {
    int hash = 0;
    hash += (workEntryId != null ? workEntryId.hashCode() : 0);
    return hash;
}

@Override
public boolean equals(Object object) {
    // TODO: Warning - this method won't work in the case the id fields are not set
    if (!(object instanceof WorkEntry)) {
        return false;
    }
    WorkEntry other = (WorkEntry) object;
    if ((this.workEntryId == null && other.workEntryId != null) || (this.workEntryId != null && !this.workEntryId.equals(other.workEntryId))) {
        return false;
    }
    return true;
}

@Override
public String toString() {
    return "consultant.billing.entity.WorkEntry[ workEntryId=" + workEntryId + " ]";
}

}

Solution

CustomerId is an Entity, not an Integer which is why you are getting the exeption. Pass in the entity you want, or change the query to:

SELECT w FROM WorkEntry w WHERE w.customerId.customerId = 1

Comments

[BadZen]

"it doesn't work". Please be more specific.

[Jamie Studzinski]

Every time I try to run it using the Persistence Unit testing I get the following error: java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: Exception Description: Problem compiling [SELECT w FROM WorkEntry w WHERE w.customerId = 1]. [14, 23] The abstract schema type 'WorkEntry' is unknown. [32, 44] The state field path 'w.customerId' cannot be resolved to a valid type. at org.eclipse.persistence.internal.jpa.EntityManagerImpl.creat‌​eQuery(EntityManager‌​Impl.java:1605)

[Neil Stockton]

so if WorkEntry "is not known" then concentrate on making it known rather than looking at the query. Namely look at your persistence.xml (the way a JPA implementation knows about entities)

[Jamie Studzinski]

I've got other queries to work with WorkEntry

[Michael Tontchev]

Can you tell us which ones?

[Jamie Studzinski]

That worked thank you Is there a way to just pass in a Customer Entity into a JPQL query?