Table doesn't exists using hibernate

Question

I'm definitely new in hibernate and this actually bugs me. It can find the table even though the query is correct. What could be the solution in this problem?

This is the part of my dao where the error persists--------------------------------------------------------------------

@Override
    public void emptyCart() {

        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction transaction = session.beginTransaction();

        String sqlQuery = "DELETE from ShoppingCart";
        Query query = session.createQuery(sqlQuery);

        query.executeUpdate();

        transaction.commit();
        session.close();
    }

Below is the stacktrace-------------------------------------------------------------------------------------------------------

 SEVERE: Servlet.service() for servlet [appServlet] in context with path [/NutsAboutCandyWebProject] threw exception [Request processing failed; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement] with root cause
    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'db_nutsaboutcandy.shopping_cart_shopping_cart' doesn't exist
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at com.mysql.jdbc.Util.getInstance(Util.java:386)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2825)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2156)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2441)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2366)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2350)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:187)
        at org.hibernate.hql.internal.ast.exec.BasicExecutor.doExecute(BasicExecutor.java:109)
        at org.hibernate.hql.internal.ast.exec.DeleteExecutor.execute(DeleteExecutor.java:121)
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:445)
        at org.hibernate.engine.query.spi.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:374)
        at org.hibernate.internal.SessionImpl.executeUpdate(SessionImpl.java:1286)
        at org.hibernate.internal.QueryImpl.executeUpdate(QueryImpl.java:118)
        at com.nutsaboutcandywebproject.dao.SQLShoppingCartDataAccess.emptyCart(SQLShoppingCartDataAccess.java:61)
        at com.nutsaboutcandywebproject.service.ServiceFacadeImpl.emptyCart(ServiceFacadeImpl.java:221)
        at com.nutsaboutcandywebproject.controller.OrderController.orderSuccessful(OrderController.java:209)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215)
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:690)
        at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83)
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:945)
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:876)
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
        at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
        at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:928)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:987)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:539)
        at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:300)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
        at java.lang.Thread.run(Thread.java:724)

ShoppingCart entity

package com.nutsaboutcandywebproject.model;

import java.io.Serializable;
import java.util.List;

import javax.persistence.Column;
import javax.persistence.ElementCollection;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "shopping_cart", catalog = "db_nutsaboutcandy")
public class ShoppingCart implements Serializable {

    private static final long serialVersionUID = 1L;

    private Integer cartId;
    private Integer userId;
    private Integer productId;
    private Integer sizeId;
    private Integer numberOfItems;


    private List<ShoppingCart> cart;

    @ElementCollection
    public List<ShoppingCart> getCart() {
        return cart;
    }

    public void setCart(List<ShoppingCart> cart) {
        this.cart = cart;
    }

    public ShoppingCart(){
        super();
    }

    public ShoppingCart(Integer cartId, Integer userId, Integer productId,
            Integer sizeId, Integer numberOfItems) {
        super();
        this.cartId = cartId;
        this.userId = userId;
        this.productId = productId;
        this.sizeId = sizeId;
        this.numberOfItems = numberOfItems;
    }

    @Id
    @GeneratedValue
    @Column(name="cart_id", nullable = false)
    public Integer getCartId() {
        return cartId;
    }

    public void setCartId(Integer cardId) {
        this.cartId = cardId;
    }

    @Column(name="user_id", nullable = false)
    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }



    @Column(name="product_id", nullable = false)
    public Integer getProductId() {
        return productId;
    }

    public void setProductId(Integer productId) {
        this.productId = productId;
    }


    @Column(name="size_id", nullable = false)
    public Integer getSizeId() {
        return sizeId;
    }

    public void setSizeId(Integer sizeId) {
        this.sizeId = sizeId;
    }

    @Column(name = "number_of_items", nullable = false)
    public Integer getNumberOfItems() {
        return numberOfItems;
    }

    public void setNumberOfItems(Integer numberOfItems) {
        this.numberOfItems = numberOfItems;
    }

}

So, you really have a table named shopping_cart_shopping_cartin your database? Why do you call sqlQuerya query that is NOT a SQL query, but a HQL query? — JB Nizet, Commented Mar 22, 2014 at 17:55
I don't have that table. I don't know why it became like that but the table name is specified in the query itself. With regards to the variable name, because I converted my queries from sql to hql and I haven't changed their names yet. — Erika, Commented Mar 22, 2014 at 17:58
No, the table name is not in the query. The query is a HQL query. HQL never uses table and column names. It always uses entity names and their field names. Check the @Table annotation of the ShoppingCart entity, and fix its name. Then read the javadoc of createQuery() and the reference documentation of HQL. SQL != HQL. — JB Nizet, Commented Mar 22, 2014 at 18:02
Yes you're right, my bad again.. I've edited the question and added the ShoppingCart entity. — Erika, Commented Mar 22, 2014 at 18:07
Why do you have a list of shopping carts in your shopping cart? That doesn't make any sense. — JB Nizet, Commented Mar 22, 2014 at 18:09

JB Nizet · Accepted Answer · 2014-03-22 18:43:18Z

What you have there is a HQL query, not a SQL query. So ShoppingCart is an entity name, not a table name. HQL never uses table and column names. It always uses entity names and their field names.

You're telling Hibernate that each shopping cart contains a list of shopping carts. This is not the reality.

To get a list of all your shopping carts, you execute an HQL query:

select c from ShoppingCart c

and Hibernate will query the database and return the list of shopping carts.

Arjun Sreepad · Accepted Answer · 2016-07-04 05:11:14Z

Even I faced the same problem. I set the "hibernate.hbm2ddl.auto = update" in config file.

hibernate.hbm2ddl.auto Automatically validates or exports schema DDL to the database when the SessionFactory is created. With create-drop, the database schema will be dropped when the SessionFactory is closed explicitly.

e.g. validate | update | create | create-drop

So the list of possible options are,

validate: validate the schema, makes no changes to the database. update: update the schema. create: creates the schema, destroying previous data. create-drop: drop the schema at the end of the session.

yaromir · Accepted Answer · 2024-07-05 17:47:52Z

0

If your table is partitioned then make sure that you have following property in your application.yml:

spring:
  jpa:
    properties:
        hibernate:
            hbm2ddl:
                extra_physical_table_types: PARTITIONED TABLE

answered Jul 5 at 17:47

yaromir

3757 silver badges19 bronze badges

Add a comment |

Collectives™ on Stack Overflow

Table doesn't exists using hibernate

3 Answers 3

Not the answer you're looking for? Browse other questions tagged
java
hibernate
or ask your own question.

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Not the answer you're looking for? Browse other questions tagged javahibernate or ask your own question.

Related

Not the answer you're looking for? Browse other questions tagged
java
hibernate
or ask your own question.