Home / Questions / 13488
Accepted
Lina Cortés
Asked: 2020-06-10 20:27:00 +0800 CST 2020-06-10 20:27:00 +0800 CST

JPQL query problem

  • 772

What I need to do is list the vendors that have responded to a single request. I do the query but I get the error (Object comparisons can only be used with OneToOneMappings. Other mapping comparisons must be done through query keys or direct attribute level comparisons)

The query I am doing is like this

SELECT e.Provider ID FROM Contribution e, Order f where e.idOrder=1

The contributing entity is like this

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "idAporte")
private Integer idAporte;
@Basic(optional = false)
@NotNull
@Column(name = "cantidadAportada")
private int cantidadAportada;
@Basic(optional = false)
@NotNull
@Column(name = "precioAporte")
private int precioAporte;
@JoinColumn(name = "idPedido", referencedColumnName = "idPedido")
@ManyToOne(optional = false)
private Pedido idPedido;
@JoinColumn(name = "idProducto", referencedColumnName = "codigoProducto")
@ManyToOne(optional = false)
private Producto idProducto;
@JoinColumn(name = "cedulaProveedor", referencedColumnName = "cedulaProveedor")
@ManyToOne(optional = false)
private Proveedor cedulaProveedor;

I know that the error is because I have a relationship between the Contributions and Orders tables and they are related through the "idOrder" column, therefore I cannot send the 1, but I do not know how to send the order object that has the id 1 .

If you could help me I would really appreciate it.

java

3 Answers

  1. Best Answer

    You must do a JOIN for this query. The JOIN clause will bring you the union of 2 tables through a common field. In this case, Aporteand Pedidoare related via idPedidoin JPA (and which is mapped to id_pedidoin the table usually).

    Your query would look like this:

    SELECT e.cedularProveedor FROM Aporte e JOIN e.idPedido p WHERE p.idPedido = :idPedido

    Which translates to:

    SELECT e.cedular_proveedor FROM aporte e INNER JOIN Pedido p ON e.id_pedido = p.id_pedido WHERE e.id_pedido = ?

    This way you get the contributions that correspond to a specific request.

    • 1

  2. You don't show the code of the object Pedido, but it seems that you are confusing the object itself, with its id.

    The query should be:

    SELECT e.cedulaProveedor FROM Aporte e, Pedido f where e.idPedido.id =1

    I think the most logical thing would be to rename your attribute:

    private Pedido idPedido;

    By:

    private Pedido pedido;

    In this way, the query would be more coherent:

    SELECT e.cedulaProveedor FROM Aporte e, Pedido f where e.pedido.id =1

    I hope I've helped.

    • 0

  3. This JPQL query shows you which vendors have responded to a single request:

    SELECT aporte.cedulaproveedor, count( distinct aporte.pedido.idpedido ) 
FROM Aporte aporte 
GROUP BY aporte.cedulaproveedor HAVING count( distinct aporte.pedido.idpedido ) = 1

    Mainly, there are three useful clauses:

    • The GROUP BY: allows you to group the contributions by each provider
    • The count distinct: allows you to know how many orders have been made by each supplier
    • The HAVING: allows you to specify that the number of orders per supplier you are looking for is 1

    I have tested the query with the Eclipse Hibernate Tools plugin, very practical!

    • 0