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. It will be very appreciated if help this issue. Thanks!

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 >
    <div id="p-header"
        
        style="min-height: 300px">
        <h1 >Add Product
            Page</h1>
        <div >
            <p >
                <a href="http://localhost:8080/Shop/product/home">Home</a>
            </p>
            <p >-</p>
            <p >Add-Page</p>
        </div>
    </div>
</div>

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

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

CodePudding user response：

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;

    // <…>
}