`Unknown column 'Camera' in 'where clause` when querying inheritance entity in Hibernate

Question

--EDIT2--

I tried to join Inventory with Product and the last error is not occured, now this error occured: Unknown column 'Camera' in 'where clause'

InventoryRepository

@Repository
public interface InventoryRepository extends JpaRepository<Inventory, Long>  {

@Query("SELECT inventory "
        + "FROM Inventory inventory "
        + "INNER JOIN inventory.product product "
        + "WHERE inventory.product.id = product.id "
        + "AND product.class = Phone "
        + "OR product.class = Camera")
    List<Inventory> findProducts();

}

--EDIT--

After following @Simo suggestion, I change the query into the following:

@Repository
public interface InventoryRepository extends JpaRepository<Inventory, Long>  {

    @Query("SELECT inventory "
            + "FROM Inventory AS inventory "
            + "WHERE inventory.product.class = 'Phone' "
            + "OR inventory.product.class = 'Camera'")
    List<Inventory> findProducts();

}

The original error, which is 'could not resolve property: class of' is not occured, but the following one occured:

java.sql.SQLWarning: Unknown column 'productvar1_1_.product_id' in 'where clause'
    at com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:725)
    at com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:658)
    at com.mysql.jdbc.StatementImpl.getWarnings(StatementImpl.java:2146)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.handleAndClearWarnings(SqlExceptionHelper.java:320)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logAndClearWarnings(SqlExceptionHelper.java:273)
    at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.close(JdbcCoordinatorImpl.java:529)
    at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.cleanup(JdbcCoordinatorImpl.java:509)
    at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.close(JdbcCoordinatorImpl.java:204)
    at org.hibernate.engine.transaction.internal.TransactionCoordinatorImpl.close(TransactionCoordinatorImpl.java:297)
    at org.hibernate.internal.SessionImpl.close(SessionImpl.java:369)
    at org.hibernate.jpa.internal.EntityManagerImpl.close(EntityManagerImpl.java:153)
    at sun.reflect.GeneratedMethodAccessor35.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:344)
    at com.sun.proxy.$Proxy84.close(Unknown Source)
    at org.springframework.orm.jpa.EntityManagerFactoryUtils.closeEntityManager(EntityManagerFactoryUtils.java:435)
    at org.springframework.orm.jpa.support.OpenEntityManagerInViewInterceptor.afterCompletion(OpenEntityManagerInViewInterceptor.java:111)
    at org.springframework.web.servlet.handler.WebRequestHandlerInterceptorAdapter.afterCompletion(WebRequestHandlerInterceptorAdapter.java:72)
    at org.springframework.web.servlet.HandlerExecutionChain.triggerAfterCompletion(HandlerExecutionChain.java:170)
    at org.springframework.web.servlet.DispatcherServlet.triggerAfterCompletion(DispatcherServlet.java:1295)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:974)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:967)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:858)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:843)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:154)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at com.example.XAuthTokenFilter.doFilter(XAuthTokenFilter.java:46)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:110)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:50)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
    at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:85)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:217)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:518)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1091)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:673)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1500)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1456)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Unknown Source)

And this is the sql:

2015-12-01 17:36:06.530 DEBUG 5964 --- [nio-8080-exec-2] org.hibernate.SQL                        : 
    select
        inventory0_.product_id as product_7_10_,
        inventory0_.store_id as store_id6_10_,
    from
        inventory inventory0_ cross 
    join
        product productvar1_ 
    where
        inventory0_.product_id=productvar1_.product_id 
        and (
            case 
                when productvar1_1_.product_id is not null then Tablet 
                when productvar1_2_.product_id is not null then Phone 
                when productvar1_.product_id is not null then 'Product' 
            end='Phone' 
            or case 
                when productvar1_1_.product_id is not null then Tablet 
                when productvar1_2_.product_id is not null then Phone 
                when productvar1_.product_id is not null then 'Product' 
            end='Camera'
        )
Hibernate: 
    select
        inventory0_.product_id as product_7_10_,
        inventory0_.store_id as store_id6_10_,
    from
        inventory inventory0_ cross 
    join
        product productvar1_ 
    where
        inventory0_.product_id=productvar1_.product_id 
        and (
            case 
                when productvar1_1_.product_id is not null then Tablet 
                when productvar1_2_.product_id is not null then Phone 
                when productvar1_.product_id is not null then 'Product' 
            end='Phone' 
            or case 
                when productvar1_1_.product_id is not null then Tablet 
                when productvar1_2_.product_id is not null then Phone 
                when productvar1_.product_id is not null then 'Product' 
            end='Camera'
        )

--Original Question--

I have a list of different products which I store in SQL using inheritance in JPA. What I want to do is retrieving all the products based on a certain type. But, this error occured:

could not resolve property: class of: com.example.Inventory [SELECT inventory FROM com.example.Inventory AS inventory WHERE TYPE(inventory.product) = 'Phone' OR TYPE(inventory.product) = 'Camera']

Below is my code.

Product

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn(name="type")
public abstract class Product {

    @Id
    @Column(name = "product_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    //some code
}

Phone

@Entity
@DiscriminatorValue("Phone")
public class Phone extends Product {
    //some code
}

Camera

@Entity
@DiscriminatorValue("Camera")
public class Camera extends Product {
    //some code
}

Inventory

@Entity
@IdClass(InventoryPK.class)
public class Inventory {

    @Id
    @ManyToOne()
    @JoinColumn(name = "product_id", referencedColumnName = "product_id")
    private Product product;

    @Id
    @ManyToOne()
    @JoinColumn(name = "store_id", referencedColumnName = "store_id")
    private Store store;    

    //some code
}

InventoryRepository

@Repository
public interface InventoryRepository extends JpaRepository<Inventory, Long>  {

    @Query("SELECT inventory "
            + "FROM Inventory AS inventory "
            + "WHERE TYPE(inventory.product) = 'Phone' "
            + "OR TYPE(inventory.product) = 'Camera'")
    List<Inventory> findProducts();

}

what version of hibernate do you use? Perhaps it is this bug https://hibernate.atlassian.net/browse/HHH-4881 ? — Si mo, Nov 30 '15 at 07:25
Try this query: @Query("SELECT inventory " + "FROM Inventory AS inventory " + "WHERE inventory.product.class = 'Phone' " + "OR inventory.product.class = 'Camera'") — Si mo, Nov 30 '15 at 08:47
now, this error occured: `Unknown column 'productvar1_1_.product_id' in 'where clause'` — Disp Hay, Nov 30 '15 at 09:57
The product and inventory table needs a column named product_id. There must be a problem with the mapping. Please post the full stack trace. Does the same error occur if you change the query to "Select inventory from Inventory inventory" ? — Si mo, Nov 30 '15 at 11:25
See my updated question. btw, changing the query into `Select inventory from Inventory inventory` doesn't help. thanks. — Disp Hay, Dec 01 '15 at 04:43
Hello, the mapping seems correct. The exception is from your MySQl Database. Please set the log level to debug (or set the hibernate property "hibernate.show_sql" to "true"), to see the full sql query. — Si mo, Dec 01 '15 at 08:29
excuse me, may I know how to set the log level in Eclipse? I've searched it and didn't find it. — Disp Hay, Dec 01 '15 at 09:19
no. the log level in your logging framework. log4j or logback — Si mo, Dec 01 '15 at 09:21
Sorry, don't understand why hibernate generates wrong sql. Perhaps you can ask a new question why this sql is generated? The documentation https://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html/ch16.html#queryhql-where says it is possible in this way — Si mo, Dec 01 '15 at 11:34

score 1 · Accepted Answer · edited May 23 '17 at 11:58

Temporary solution I found is by adding a dedicated Column for type in the parent entity

@Column(name = "type", insertable = false, updatable = false)
private String type;

and then, you can querying it like this:

@Repository
public interface InventoryRepository extends JpaRepository<Inventory, Long>  {

    @Query("SELECT inventory "
            + "FROM Inventory AS inventory "
            + "WHERE inventory.product.type = 'Phone' "
            + "OR inventory.product.type = 'Camera'")
    List<Inventory> findProducts();

}

I don't think it's elegant, but at least, it works.

Source:

How to get the DiscriminatorValue at run time

JPA query for DTYPE

`Unknown column 'Camera' in 'where clause` when querying inheritance entity in Hibernate

1 Answers1