HTTP status 500 error, Request processing failed; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement

Question

I keep getting SQLGrammarException error, I could not find any syntax error from my code. Here is my controller, entity, DAO and JSP file code. Maybe my form tag in addProduct.jsp is wrong, but I could not find the solution.

HomeController.java

package com.soccershop.springdemo.controller;

import java.io.IOException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import com.soccershop.springdemo.entity.Top;
import com.soccershop.springdemo.service.ProductService;

@Controller
public class HomeController {
    
    @Autowired
    private ProductService productService;
    
    @RequestMapping("/home")
    public String home(Model model) {
        List<Top> topProductList = productService.getTopProducts();
        model.addAttribute("topProducts", topProductList);
        
        return "home" ;
    }
    
    @RequestMapping("/productDetail/{id}")
    public String productDetail(@PathVariable int id, Model model) throws IOException {
        Top top = productService.getTopById(id);
        model.addAttribute(top);
        return "productDetail";
    }
    
    @RequestMapping("/admin")
    public String adminPage() {
        return "admin";
    }
    
    @RequestMapping("/admin/productInventory")
    public String productInventory(Model model) {
        List<Top> topProductList = productService.getTopProducts();
        model.addAttribute("topProducts", topProductList);
        return "productInventory";
    }
    
    @GetMapping("/admin/addProduct")
    public String addProduct(Model model) {
        Top top= new Top();
        model.addAttribute("newProduct", top);
        return "addProduct";
    }
    
    @PostMapping("/admin/saveTop")
    public String saveTop(@ModelAttribute("newProduct") Top theTop) {
        productService.saveTop(theTop);
        
        return "redirect:/admin/productInventory";
    }

}

ProductDAOImpl.java

package com.soccershop.springdemo.dao;

import java.io.IOException;
import java.util.List;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.query.Query;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.soccershop.springdemo.entity.Bottom;
import com.soccershop.springdemo.entity.Top;

@Repository
public class ProductDAOImpl implements ProductDAO {

    // need to inject the session factory
    @Autowired
    private SessionFactory sessionFactory;
            
    @Override
    @Transactional
    public List<Top> getTopProducts() {
        // get the current hibernate session
        Session currentSession = sessionFactory.getCurrentSession();
                
        // create a query
        Query<Top> theQuery = 
                currentSession.createQuery("from Top", Top.class);
        
        // execute query and get result list
        List<Top> topProducts = theQuery.getResultList();
                
        // return the results       
        return topProducts;
    }
    
    @Override
    @Transactional
    public List<Bottom> getBottomProducts() {
        // get the current hibernate session
        Session currentSession = sessionFactory.getCurrentSession();
                
        // create a query
        Query<Bottom> theQuery = 
                currentSession.createQuery("from Bottom", Bottom.class);
        
        // execute query and get result list
        List<Bottom> topProducts = theQuery.getResultList();
                
        // return the results       
        return topProducts;
    }
    
    @Override
    public Top getTopById(int topId) throws IOException {
        for(Top theTop : getTopProducts()) {
            if(theTop.getId() == topId) {
                return theTop;
            }
        }
        throw new IOException("No product found.");
    }
    
    @Override
    public void saveTop(Top theTop) {
        Session currentSession = sessionFactory.getCurrentSession();
        
        currentSession.save(theTop);
    }
}

Top.java

package com.soccershop.springdemo.entity;

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

@Entity
@Table(name="Top")
public class Top {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="id")
    private int id;
    
    @Column(name="category")
    private String productCategory;
    
    @Column(name="product_name")
    private String productName;
    
    @Column(name="price")
    private int price;
    
    @Column(name="img")
    private String img;
    
    @Column(name="desc")
    private String desc;
     
    @Column(name="quantity")
    private int quantity;

    public Top() {
        
    }
    
    public int getQuantity() {
        return quantity;
    }

    public void setQuantity(int quantity) {
        this.quantity = quantity;
    }

    public String getProductCategory() {
        return productCategory;
    }

    public void setProductCategory(String productCategory) {
        this.productCategory = productCategory;
    }

    public String getDesc() {
        return desc;
    }

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

    public int getId() {
        return id;
    }

    public String getProductName() {
        return productName;
    }

    public int getPrice() {
        return price;
    }

    public String getImg() {
        return img;
    }

    public void setId(int id) {
        this.id = id;
    }

    public void setProductName(String productName) {
        this.productName = productName;
    }

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

    public void setImg(String img) {
        this.img = img;
    }

    @Override
    public String toString() {
        return "Top [id=" + id + ", productCategory=" + productCategory + ", productName=" + productName + ", price="
                + price + ", img=" + img + ", desc=" + desc + ", quantity=" + quantity + "]";
    }   
}

addProduct.jsp

<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<%@include file="/WEB-INF/view/template/header.jsp"%>

<div class="container-fluid bg-secondary mb-5">
    <div id="p-header"
        class="d-flex flex-column align-items-center justify-content-center"
        style="min-height: 300px">
        <h1 class="font-weight-semi-bold text-uppercase mb-3">Add Product
            Page</h1>
        <div class="d-inline-flex">
            <p class="m-0">
                <a href="http://localhost:8080/Shop/product/home">Home</a>
            </p>
            <p class="m-0 px-2">-</p>
            <p class="m-0">Add-Page</p>
        </div>
    </div>
</div>

<section id="services">
    <div class="container-fluid">
        <div class="col-lg-8 col-lg-offset-2 text-center">
            <p>Please fill out the below information to add a product.</p>
            <hr class="primary">
        </div>
    </div>
    <div class="container col-md-6 col-md-offset-3">
        <form:form action="saveTop" modelAttribute="newProduct" method="POST" >
            <div class="form-group">
                <label for="category">Category</label>
                <label class="checkbox-inline">
                    <form:radiobutton path="productCategory" id="category" value="top" />Top
                </label>
                <label class="checkbox-inline">
                    <form:radiobutton path="productCategory" id="category" value="bottom" />Bottom
                </label>
                <label class="checkbox-inline">
                    <form:radiobutton path="productCategory" id="category" value="shoes" />Shoes
                </label>
            </div>
            
            <div class="form-group">
                <label for="name">Product Name</label>
                <form:input path="productName" id="name" Class="form-control" />
            </div>
            
            <div class="form-group">
                <label for="unitInStock">Unit in Stock</label>
                <form:textarea path="quantity" id="unitInStock" Class="form-control" />
            </div>
            
            <div class="form-group">
                <label for="price">Price</label>
                <form:textarea path="price" id="price" Class="form-control" />
            </div>
            
            
            <div class="form-group">
                <label for="img">Image URL</label>
                <form:textarea path="img" id="img" Class="form-control" />
            </div>
            
            <div class="form-group">
                <label for="description">Description</label>
                <form:textarea path="desc" id="description" Class="form-control" />
            </div><br>
            
            
            <div class="col-lg-8 col-lg-offset-2 text-center">
                <input type="submit" value="submit" class="btn btn-primary">
                <a href= "<c:url value="/admin/productInventory" />" class="btn btn-primary">Cancel</a>
            </div>
        
        </form:form>
    
    </div>
</section>

<%@include file="/WEB-INF/view/template/footer.jsp"%>

Somewhere there is a log file and it likely contains the real underlying error. Focus on the persistence layer. — stdunbar, Aug 02 '22 at 01:24
Dear @Jayden pak, Welcome to Stack Overflow! Could you please specify what database server you are using? In addition, could you please provide the exception details (message, stack trace, etc.)? — Sergey Vyacheslavovich Brunov, Aug 02 '22 at 01:51

Sergey Vyacheslavovich Brunov · Accepted Answer · 2022-08-02T02:23:34.343

Analysis

It looks like some names used in the database context (a table name, a column name, etc.) collide with the reserved words of the database server that you currently use.

Here is a related question: java - org.hibernate.exception.SQLGrammarException: could not execute statement - Stack Overflow.

Solution

Please, check the documentation for the reserved words of the database server.

Please, change the names that collide with the reserved words of the database server.

Draft examples

Microsoft SQL server

Reserved Keywords (Transact-SQL) - SQL Server | Microsoft Docs:

DESC.
TOP.

Therefore, it is required to rename the Top table and the desc column. For example:

@Entity
@Table(name="TopProduct")
public class Top {
    // <…>

    @Column(name="description")
    private String desc;

    // <…>
}

MySQL 8.0

MySQL :: MySQL 8.0 Reference Manual :: 9.3 Keywords and Reserved Words:

DESC.

Therefore, it is required to rename the desc column. For example:

// <…>
public class Top {
    // <…>

    @Column(name="description")
    private String desc;

    // <…>
}

Thank you so much, it works after I renamed Top.java file to TopProduct.java and desc to description. — Jayden pak, Aug 02 '22 at 02:08
@Jaydenpak, Thank you for checking the solution! Glad it has helped you! I have updated the answer with the pieces of source code. — Sergey Vyacheslavovich Brunov, Aug 02 '22 at 02:18
A list of SQL reserved words. covering the ANSI SQL standard as well as a bunch of dbms products: https://en.wikipedia.org/wiki/SQL_reserved_words — jarlh, Aug 02 '22 at 10:46