6

I have a problem with a query in my application. This is the query method doing the query:

public List<Product> obtainProductListByCategory(String category)
   {
      Query query = em.createQuery("SELECT p FROM PRODUCT p WHERE CATEGORY='" + category + "'");
      List<Product> ret = query.getResultList();

      if (ret == null)
      {
         return new ArrayList<Product>();
      }
      else
      {
         return ret;
      }
   } 

And this is the error: javax.ejb.EJBException

And in the trace I found:

Caused by: java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: Exception Description: Syntax error parsing [SELECT * FROM PRODUCT WHERE CATEGORY='Humano']. [22, 22] A select statement must have a FROM clause. [7, 7] The left expression is missing from the arithmetic expression. [9, 22] The right expression is not an arithmetic expression

Any ideas? My objective is to refresh a JSF datatable in my webpage.


Edited my code based on @Ilya's answer, and now I got this exception

Caused by: java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: Exception Description: Problem compiling [SELECT p FROM PRODUCT p WHERE CATEGORY='Humano']. [14, 21] The abstract schema type 'PRODUCT' is unknown. [30, 38] The identification variable 'CATEGORY' is not defined in the FROM clause.


As requested by @Ilya, I post my Product class: EDIT: Added @Table to the annotations.

package model;

import java.io.Serializable;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;


@Table
@Entity
public class Product implements Serializable, IProduct
{
   private static final long serialVersionUID = 1L;
   @GeneratedValue(strategy = GenerationType.AUTO)
   @Id
   private String name;
   private int stock;
   private float price;
   private String category;
   private String description;

  @Override 
  public String getDescription() {
        return description;
    }


  public void setDescription(String description) {
        this.description = description;
    }


   public Product()
   {
   }


   public Product(String name, int stock, float price, String category, String description)
   {
      this.name = name;
      this.stock = stock;
      this.price = price;
      this.category = category;
      this.description = description;
   }





   @Override
   public String getName()
   {
      return name;
   }


   public void setName(String name)
   {
      this.name = name;
   }


   @Override
   public float getPrice()
   {
      return price;
   }


   public void setPrice(float price)
   {
      this.price = price;
   }


   @Override
   public int getStock()
   {
      return stock;
   }


   public void setStock(int stock)
   {
      this.stock = stock;
   }


   @Override
   public int hashCode()
   {
      return name.hashCode();
   }


   @Override
   public boolean equals(Object object)
   {
      if (!(object instanceof Product))
      {
         return false;
      }
      Product other = (Product) object;
      if (name.equals(other.getName()))
      {
         return true;
      }
      return false;
   }

   @Override
   public String getCategory()
   {
      return category;
   }

   @Override
   public String toString()
   {
      return "Marketv2.model.Product[ name=" + name + " ]";
   }

}

Thanks for the help so far. Here I post another query in my application, which is working properly:

   public void removeProduct(Product g)
   {
      Query q = em.createQuery("SELECT x FROM BasketItem x WHERE x.product.name = '" + g.getName() + "'");
      List<BasketItem> bItems = q.getResultList();
      for (BasketItem i : bItems)
      {
         em.remove(i);
      }

      q = em.createQuery("DELETE FROM Product x WHERE x.name = '" + g.getName() + "'");
      q.executeUpdate();
   }
}
1
  • Please do not delete the older problems, otherwise the answers that tried to solve that problem become invalid. Just add the relevant info to the specific problem. Commented Oct 16, 2013 at 20:14

1 Answer 1

20

1) You should specify alias for tables in FROM clause, and SELECT clause should contains alias
Product should be an entity

em.createQuery("SELECT p FROM Product p WHERE p.category='" + category + "'");   

If PRODUCT isn't an entity, you should create nativeQuery

em.createNativeQuery("SELECT p FROM PRODUCT p WHERE p.CATEGORY='" + category + "'");  

EntityManager::createQuery is for JPQL (Java Persistence query language)
EntityManager::createNativeQuery is for SQL

2) JPA throws the "Unknown abstract schema type" error when JPA fails to locate your entity class
Also add entity to persistence.xml

 <persistence-unit ...>
        <class>com.package.Product</class>  

3) Add @Table annotation to your @Entity
4) As I see in documentation, JPQL is case-sensitive.

With the exception of names of Java classes and properties, queries are case-insensitive. So SeLeCT is the same as sELEct is the same as SELECT, but org.hibernate.eg.FOO and org.hibernate.eg.Foo are different, as are foo.barSet and foo.BARSET.

So JPQL query should be

SELECT p FROM Product p WHERE p.category = '...
Sign up to request clarification or add additional context in comments.

12 Comments

Just edited my question, and changed the code to what you suggested. I'm getting a new error now.
@JeanCarlosSuárezMarranzini is Product an entity? Add Product class
It would be better to add parameters using named parameters instead of just adding their values naively like this. Note that these leaks make your application open to SQL Injection attacks.
@JeanCarlosSuárezMarranzini JPA will throws the "Unknown abstract schema type" error when JPA fails to locate your entity class
@JeanCarlosSuárezMarranzini may be case-sensitive parser is used. Try SELECT p FROM Product p WHERE p.category=' ... instead of upper-case
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.