Hibernate HQL - not exists condition throwing org.hibernate.exception.SQLGrammarException:

Question

I have 3 tables (many to many condition) - Stock, Categories and an intermediate Stock_Category.

In Hibernate, I have 2 Entities - Stock having set of categories and Category entity having set of stocks.

Now I want to fetch all stocks not belonging to a particular category, identified by category name.

I have the following correlated subquery coded in HQL:

Query query = session.createQuery("Select s from Stock s join fetch                   s.categories c " +
                "where not exists (Select cs from Category cs " +
                "where s in cs.stocks and cs.categoryname = :categoryname)");
        query.setParameter("categoryname", "MATTEL");

It seems hibernate can understand the query because it gives in console its own version of the interpretation, however still throws an Sql exception. What is the missing factor?

Given by hibernate:

Hibernate: 
/* Select
    s 
from
    Stock s 
join
    fetch s.categories c 
where
    not exists (
        Select
            cs 
        from
            Category cs 
        where
            s in cs.stocks 
            and cs.categoryname = :categoryname
    ) */ select
        stock0_.STOCK_ID as STOCK_ID1_1_0_,
        categories1_.CATEGORY_ID as CATEGORY_ID1_0_1_,
        stock0_.STOCK_CODE as STOCK_CODE2_1_0_,
        stock0_.STOCK_NAME as STOCK_NAME3_1_0_,
        categories1_.CATEGORY_NAME as CATEGORY_NAME2_0_1_,
        categories1_.CATEGORY_DESC as CATEGORY_DESC3_0_1_,
        categories1_.STOCK_ID as STOCK_ID4_1_0__,
        categories1_.CATEGORY_ID as CATEGORY_ID1_0_0__ 
    from
        STOCK stock0_ 
    inner join
        CATEGORY categories1_ 
            on stock0_.STOCK_ID=categories1_.STOCK_ID 
    where
        not (exists (select
            category2_.CATEGORY_ID 
        from
            CATEGORY category2_,
            STOCK stocks3_ 
        where
            category2_.CATEGORY_ID=stocks3_.CATEGORY_ID 
            and (stock0_.STOCK_ID in (.)) 
            and category2_.CATEGORY_NAME=?))

This is the entity class for the CATEGORY table:

@Entity
@Table(name = "category")
public class Category implements java.io.Serializable {

private Integer categoryId;
private String categoryname;
private String categorydesc;
private Set<Stock> stocks = new HashSet<Stock>(0);

public Category() {
}

public Category(String name, String desc) {
    this.categoryname = name;
    this.categorydesc = desc;
}

public Category(String name, String desc, Set<Stock> stocks) {
    this.categoryname = name;
    this.categorydesc = desc;
    this.stocks = stocks;
}

@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "CATEGORY_ID", unique = true, nullable = false)
public Integer getCategoryId() {
    return this.categoryId;
}

public void setCategoryId(Integer categoryId) {
    this.categoryId = categoryId;
}

@Column(name = "CATEGORY_NAME", nullable = false, length = 10)
public String getCategoryname() {
    return this.categoryname;
}

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

@Column(name = "[CATEGORY_DESC]", nullable = false)
public String getCategorydesc() {
    return this.categorydesc;
}

public void setCategorydesc(String desc) {
    this.categorydesc = desc;
}

@ManyToMany(fetch = FetchType.LAZY, mappedBy = "categories")
public Set<Stock> getStocks() {
    return this.stocks;
}

public void setStocks(Set<Stock> stocks) {
    this.stocks = stocks;
}

}

This is the mapping in Category.hbm.xml:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Mar 17, 2017 8:59:40 AM by Hibernate Tools 3.5.0.Final -->
<hibernate-mapping>
<class name="com.mkyong.stock.Category" table="CATEGORY">
    <id name="categoryId" type="java.lang.Integer">
        <column name="CATEGORY_ID" />
        <generator class="assigned" />
    </id>
    <property name="categoryname" type="java.lang.String">
        <column name="CATEGORY_NAME" />
    </property>
    <property name="categorydesc" type="java.lang.String">
        <column name="CATEGORY_DESC" />
    </property>
    <set name="stocks" table="STOCK" inverse="false" lazy="true">
        <key>
            <column name="CATEGORY_ID" />
        </key>
        <one-to-many class="com.mkyong.stock.Stock" />
    </set>
</class>
</hibernate-mapping>

And this is the entity class for the STOCK table:

@Entity
@Table(name = "stock", uniqueConstraints = {
    @UniqueConstraint(columnNames = "STOCK_NAME"),
    @UniqueConstraint(columnNames = "STOCK_CODE") }) 
public class Stock implements java.io.Serializable {

private Integer stockId;
private String stockCode;
private String stockName;
private Set<Category> categories = new HashSet<Category>(0);

public Stock() {
}

public Stock(int stockid, String stockCode, String stockName) {
    this.stockId = stockid;
    this.stockCode = stockCode;
    this.stockName = stockName;
}

public Stock(String stockCode, String stockName, Set<Category> categories) {
    this.stockCode = stockCode;
    this.stockName = stockName;
    this.categories = categories;
}

@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "STOCK_ID", unique = true, nullable = false)
public Integer getStockId() {
    return this.stockId;
}

public void setStockId(Integer stockId) {
    this.stockId = stockId;
}

@Column(name = "STOCK_CODE", unique = true, nullable = false, length = 10)
public String getStockCode() {
    return this.stockCode;
}

public void setStockCode(String stockCode) {
    this.stockCode = stockCode;
}

@Column(name = "STOCK_NAME", unique = true, nullable = false, length = 20)
public String getStockName() {
    return this.stockName;
}

public void setStockName(String stockName) {
    this.stockName = stockName;
}

@ManyToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@JoinTable(name = "stock_category", joinColumns = { 
        @JoinColumn(name = "STOCK_ID", nullable = false, updatable = false) }, 
        inverseJoinColumns = { @JoinColumn(name = "CATEGORY_ID", 
                nullable = false, updatable = false) })
public Set<Category> getCategories() {
    return this.categories;
}

public void setCategories(Set<Category> categories) {
    this.categories = categories;
}

}

This is the mapping in Stock.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Mar 17, 2017 8:59:40 AM by Hibernate Tools 3.5.0.Final -->
<hibernate-mapping>
<class name="com.mkyong.stock.Stock" table="STOCK">
    <id name="stockId" type="java.lang.Integer">
        <column name="STOCK_ID" />
        <generator class="assigned" />
    </id>
    <property name="stockCode" type="java.lang.String">
        <column name="STOCK_CODE" />
    </property>
    <property name="stockName" type="java.lang.String">
        <column name="STOCK_NAME" />
    </property>
    <set name="categories" table="CATEGORY" inverse="false" lazy="true">
        <key>
            <column name="STOCK_ID" />
        </key>
        <one-to-many class="com.mkyong.stock.Category" />
    </set>
</class>
</hibernate-mapping>

The hibernate.cfg.xmL

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
                                         "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
 <session-factory name="">
  <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
  <property name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521:xe</property>
  <property name="hibernate.connection.username">system</property>
  <property name="hibernate.connection.password">anishakshi1</property>
  <property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>
  <property name="show_sql">true</property>
  <property name="format_sql">true</property>
  <property name="use_sql_comments">true</property>
  <mapping resource="com/mkyong/stock/Category.hbm.xml"/>
  <mapping resource="com/mkyong/stock/Stock.hbm.xml"/>
  </session-factory>
 </hibernate-configuration>

Answer 1

I think you can go for a join instead of an in statement:

"Select s from Stock s join fetch s.categories c " +
"where not exists (Select cs from Category cs inner join cs.stocks ss " +
"where s.id = ss.id and cs.categoryname = :categoryname)"

Update

Your xml mappings are wrongs.. they do not specify a manytomany mapping. Your annotations seem to be fine so:

1) Remove those properties:

<mapping resource="com/mkyong/stock/Category.hbm.xml"/>
  <mapping resource="com/mkyong/stock/Stock.hbm.xml"/>

2) Add this property to your sessionFactory config:

<property name="packagesToScan" value="com.mkyong"/>

Answer 2

Update on property like this

property name="hibernate.hbm2ddl.auto">validate