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. 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 + " ]";
}
}
