JPA: find result by interring foreign value

Question

Here is my database

   CREATE TABLE Orders (
        OrderID int NOT NULL,
        OrderNumber int NOT NULL,
        products_id int,
        PRIMARY KEY (OrderID),
        FOREIGN KEY (products_id) REFERENCES product(products_id)
    );

****I generated NetBeans entity class and it gives the following code;****

package Entity;

import java.io.Serializable;
import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;
import javax.xml.bind.annotation.XmlRootElement;

@Entity
@Table(name = "orders")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "Orders.findAll", query = "SELECT o FROM Orders o")
    , @NamedQuery(name = "Orders.findByOrderID", query = "SELECT o FROM Orders o WHERE o.orderID = :orderID")
    , @NamedQuery(name = "Orders.findByOrderNumber", query = "SELECT o FROM Orders o WHERE o.orderNumber = :orderNumber")})
public class Orders implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @Column(name = "OrderID")
    private Integer orderID;
    @Basic(optional = false)
    @Column(name = "OrderNumber")
    private int orderNumber;

    public Orders() {
    }

    public Orders(Integer orderID) {
        this.orderID = orderID;
    }

    public Orders(Integer orderID, int orderNumber) {
        this.orderID = orderID;
        this.orderNumber = orderNumber;
    }

    public Integer getOrderID() {
        return orderID;
    }

    public void setOrderID(Integer orderID) {
        this.orderID = orderID;
    }

    public int getOrderNumber() {
        return orderNumber;
    }

    public void setOrderNumber(int orderNumber) {
        this.orderNumber = orderNumber;
    }

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

    @Override
    public boolean equals(Object object) {

        if (!(object instanceof Orders)) {
            return false;
        }
        Orders other = (Orders) object;
        if ((this.orderID == null && other.orderID != null) || (this.orderID != null && !this.orderID.equals(other.orderID))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "Entity.Orders[ orderID=" + orderID + " ]";
    }

}

****I am new of JPA, and I just want to get the "OrderNumber" by entering the "products_id" is it possible?** I want something like this**

public Orders findOrderNumber(int productsId) {
        EntityManager em = getEntityManager();
        Orders order= null;
        try {
         order= (Orders) em.createQuery("SELECT o FROM Orders o WHERE o.productsId=" + productsId).getSingleResult();


        } catch (NoResultException e) {
            order= null;
        }

        return order;
    }

score 1 · Answer 1 · answered Jun 06 '17 at 06:51

1

Try this code:

// Retrieve operation using the JPA Entity Manager
order = em.find(Orders.class,productsId);

// get the orderNumber from an instance of Entity Orders
int orderNumber = order.getOrderNumber();

answered Jun 06 '17 at 06:51

cjslv

377
3
15

score -1 · Answer 2 · answered Jun 06 '17 at 06:49

-1

You should use a prepared statement.

How to use prepared statement in JPA

Right now your query is vulnerable to SQL-injection : https://www.owasp.org/index.php/SQL_Injection

What exactly isn't working right now?

answered Jun 06 '17 at 06:49

jvwilge

2,474
2
16
21

JPA: find result by interring foreign value

2 Answers2