Issue
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"%>
Solution
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;
// <…>
}
Answered By - Sergey Vyacheslavovich Brunov
Answer Checked By - Senaida (JavaFixing Volunteer)
