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;
}
}
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.