Query error - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

The project uses MS SQL but I want to switch to MySQL and I have a query problem:

>...threw exception [Request processing failed; nested exception is org.hibernate.exception.SQLGrammarException:
could not extract ResultSet] with root cause
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:

>You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '9 product0_.ID as ID1_11_, product0_.Available as Availabl2_11_, product0_.Categ' at line 1

Package controller:
UserProduct:

package hieuboy.controller;
import java.io.File;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Scanner;
import java.util.Set;
import javax.mail.internet.MimeMessage;
import javax.servlet.ServletContext;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.mail.javamail.JavaMailSender;
import org.springframework.mail.javamail.MimeMessageHelper;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.CookieValue;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import hieuboy.admin.controller.HieuBoyShopController;
import hieuboy.model.Product;
import hieuboy.other.PagerModel;
@Controller
@Transactional
@RequestMapping(value = "user/product")
public class UserProductController extends HieuBoyShopController {
@Autowired
ServletContext application;
@Autowired
JavaMailSender mailSender;
// Tìm kiếm sản phẩm
@SuppressWarnings("unchecked")
@RequestMapping("search-product.htm")
public String searchProduct(ModelMap model, HttpServletRequest request, @RequestParam("") String search) {
Session session = sessionFactory.getCurrentSession();
// Đếm sản phẩm để phân trang và tìm kiếm
String hqlCount = "SELECT COUNT(p) FROM Product p WHERE p.status = 1 and p.nameProduct LIKE '%" + search + "%'";
Long rowCount = (Long) session.createQuery(hqlCount).uniqueResult();
System.out.println(rowCount);
PagerModel pager = new PagerModel();
pager = PagerModel.getPager("acpager", 9, rowCount.intValue(), request);
pager.setRowCount(rowCount.intValue());
pager.setPageSize(9);
pager.navigate(request);
model.addAttribute("search", search);
model.addAttribute("flat", "search-product");
// Tìm kiếm sản phẩm
String hql = "FROM Product p WHERE p.status = 1 and p.nameProduct LIKE '%" + search + "%'";
Query query = session.createQuery(hql);
query.setFirstResult(pager.getStartRow());
query.setMaxResults(pager.getPageSize());
List<Product> list = query.list();
model.addAttribute("list", list);
return "user/product-list";
}
// Show danh sách sản phẩm
@SuppressWarnings("unchecked")
@RequestMapping("list")
public String list(ModelMap model, HttpServletRequest request) {
Session session = sessionFactory.getCurrentSession();
// Đếm sản phẩm và phân trang
String hqlCount = "SELECT COUNT(p) FROM Product p";
Long rowCount = (Long) session.createQuery(hqlCount).uniqueResult();
PagerModel pager = new PagerModel();
pager = PagerModel.getPager("acpager", 9, rowCount.intValue(), request);
pager.setRowCount(rowCount.intValue());
pager.navigate(request);
model.addAttribute("flat", "list");
// Lấy danh sách sản phẩm có views giảm dần
String hql = "FROM Product p WHERE p.status = 1 ORDER BY p.views DESC";
Query query = session.createQuery(hql);
query.setFirstResult(pager.getStartRow());
query.setMaxResults(pager.getPageSize());
List<Product> list = query.list();
model.addAttribute("list", list);
return "user/product-list";
}
// Show danh sách sản phẩm theo danh mục
@SuppressWarnings("unchecked")
@RequestMapping("list-by-category/{id}.htm")
public String listCategory(ModelMap model, @PathVariable("id") Integer id, HttpServletRequest request) {
Session session = sessionFactory.getCurrentSession();
// Phân trang sản phẩm theo danh mục
String hqlCount = "SELECT COUNT(p) FROM Product p WHERE p.status = 1 and p.category.id=:cid1";
Long rowCount = (Long) session.createQuery(hqlCount).setParameter("cid1", id).uniqueResult();
PagerModel pager = new PagerModel();
pager = PagerModel.getPager("acpager", 9, rowCount.intValue(), request);
pager.setRowCount(rowCount.intValue());
pager.navigate(request);
model.addAttribute("flat", "list-by-category/" + id);
// Lấy danh sách sản phẩm theo danh mục
String hql = "FROM Product p WHERE p.status = 1 and p.category.id=:cid";
Query query = session.createQuery(hql);
query.setParameter("cid", id);
query.setFirstResult(pager.getStartRow());
query.setMaxResults(pager.getPageSize());
List<Product> list = query.list();
model.addAttribute("list", list);
return "user/product-list";
}
// Show danh sách sản phẩm theo hãng sản xuất
@SuppressWarnings("unchecked")
@RequestMapping("list-by-producer/{id}.htm")
public String listProducer(ModelMap model, @PathVariable("id") Integer id, HttpServletRequest request) {
Session session = sessionFactory.getCurrentSession();
// Phân trang sản phẩm theo hãng
String hqlCount = "SELECT COUNT(p) FROM Product p WHERE p.status = 1 and p.producer.id = :pid1";
Long rowCount = (Long) session.createQuery(hqlCount).setParameter("pid1", id).uniqueResult();
PagerModel pager = new PagerModel();
pager = PagerModel.getPager("acpager", 9, rowCount.intValue(), request);
pager.setRowCount(rowCount.intValue());
pager.navigate(request);
model.addAttribute("flat", "list-by-producer/" + id);
// Show danh sách sản phẩm theo hãng
String hql = "FROM Product p WHERE p.status = 1 and p.producer.id = :pid";
Query query = session.createQuery(hql);
query.setParameter("pid", id);
query.setFirstResult(pager.getStartRow());
query.setMaxResults(pager.getPageSize());
List<Product> list = query.list();
model.addAttribute("list", list);
return "user/product-list";
}
// Xem chi tiết
@RequestMapping("detail/{id}.htm")
public String detailProduct(ModelMap model, HttpServletResponse response, @PathVariable("id") Integer id,
@ModelAttribute("product") Product product, @CookieValue(value = "views", defaultValue = "") String views) {
product.setId(id);
Session session = sessionFactory.getCurrentSession();
session.refresh(product);
// Tăng số lần xem
product.setViews(product.getViews() + 1);
session.update(product);
// Ghi nhận mặt hàng đã xem vào cookie
if (!views.contains(id.toString())) {
views += "," + id;
}
String hql = "FROM Product " + " WHERE id IN(2018" + views + ")";
Query query = session.createQuery(hql);
model.addAttribute("views", query.list());
Cookie cookie = new Cookie("views", views);
cookie.setMaxAge(60 * 60 * 24 * 30);
response.addCookie(cookie);
return "user/product-detail";
}
@SuppressWarnings("unchecked")
@RequestMapping("type/{value}.htm")
public String listSpecial(ModelMap model, @PathVariable("value") String value, HttpServletRequest request) {
Session session = sessionFactory.getCurrentSession();
Long rowCount = (long) 0;
Query query = null;
// Danh sách hàng bán chạy
if (value.equalsIgnoreCase("best")) {
// Đếm sản phẩm trên 40 đơn hàng chi tiết
String hqlCount = "SELECT COUNT(p) FROM Product p WHERE p.status = 1 and SIZE(p.orderDetail) >40";
rowCount = (Long) session.createQuery(hqlCount).uniqueResult();
System.out.println(rowCount);
PagerModel pager = new PagerModel();
pager = PagerModel.getPager("acpager", 9, rowCount.intValue(), request);
pager.setRowCount(rowCount.intValue());
pager.navigate(request);
// Lấy danh sách sản phẩm trên 40 đơn hàng chi tiết
String hql = "FROM Product p " + " WHERE p.status = 1 and SIZE(p.orderDetail) > 40 "
+ " ORDER BY SIZE(p.orderDetail) DESC ";
query = session.createQuery(hql);
query.setFirstResult(pager.getStartRow());
query.setMaxResults(pager.getPageSize());
model.addAttribute("flat", "type/best");
}
// Danh sách hàng mới nhất
else if (value.equalsIgnoreCase("latest")) {
// Đếm sản phẩm mới nhất để phân trang
String hqlCount = "SELECT COUNT(p) FROM Product p WHERE p.status =1 and p.latest=1";
rowCount = (Long) session.createQuery(hqlCount).uniqueResult();
if (rowCount > 24) {
rowCount = (long) 24;
}
PagerModel pager = new PagerModel();
pager = PagerModel.getPager("acpager", 9, rowCount.intValue(), request);
pager.navigate(request);
// Lấy danh sách sản phẩm mới nhất
String hql = "FROM Product p WHERE p.status = 1 and p.latest = 1";
query = session.createQuery(hql);
query.setFirstResult(pager.getStartRow());
query.setMaxResults(pager.getPageSize());
model.addAttribute("flat", "type/latest");
}
// Danh sách hàng có lượt xem cao nhất
else if (value.equalsIgnoreCase("views")) {
// Đếm sản phẩm với lượt view cao hơn 0
String hqlCount = "SELECT COUNT(p) FROM Product p WHERE p.status = 1 and p.views > 0";
rowCount = (Long) session.createQuery(hqlCount).uniqueResult();
if (rowCount > 24) {
rowCount = (long) 24;
}
PagerModel pager = new PagerModel();
pager = PagerModel.getPager("acpager", 9, rowCount.intValue(), request);
pager.setRowCount(rowCount.intValue());
pager.navigate(request);
// Lấy danh sách sản phẩm theo views
String hql = "FROM Product p WHERE p.status = 1 and p.views > 0 ORDER BY p.views DESC";
query = session.createQuery(hql);
query.setFirstResult(pager.getStartRow());
query.setMaxResults(pager.getPageSize());
model.addAttribute("flat", "type/views");
}
// Danh sách hàng có kiểu đăc biệt
else if (value.equalsIgnoreCase("special")) {
// Đếm danh sách sản phẩm và phân trang theo spcecial
String hqlCount = "SELECT COUNT(p) FROM Product p WHERE p.status=1 and p.special = 1";
rowCount = (Long) session.createQuery(hqlCount).uniqueResult();
if (rowCount > 24) {
rowCount = (long) 24;
}
PagerModel pager = new PagerModel();
pager = PagerModel.getPager("acpager", 9, rowCount.intValue(), request);
pager.setRowCount(rowCount.intValue());
pager.navigate(request);
// Lấy danh sách sản phẩm theo kiểu đặc biệt
String hql = "FROM Product p WHERE p.status = 1 and p.special=1";
query = session.createQuery(hql);
query.setFirstResult(pager.getStartRow());
query.setMaxResults(pager.getPageSize());
model.addAttribute("flat", "type/special");
}
// Danh sách hàng giảm giá
else if (value.equalsIgnoreCase("saleoff")) {
// Đếm sản phẩm theo loại giảm giá và phân trang
String hqlCount = "SELECT COUNT(p) FROM Product p WHERE p.status = 1 and p.discount > 0 ";
rowCount = (Long) session.createQuery(hqlCount).uniqueResult();
if (rowCount > 24) {
rowCount = (long) 24;
}
PagerModel pager = new PagerModel();
pager = PagerModel.getPager("acpager", 9, rowCount.intValue(), request);
pager.setRowCount(rowCount.intValue());
pager.navigate(request);
// Lấy danh sách sản phẩm theo giảm giá
String hql = "FROM Product p WHERE p.status = 1 and p.discount > 0 ORDER BY p.discount DESC ";
query = session.createQuery(hql);
query.setFirstResult(pager.getStartRow());
query.setMaxResults(pager.getPageSize());
model.addAttribute("flat", "type/saleoff");
}
// Danh sách hàng được yêu thích
else if (value.equalsIgnoreCase("favorite")) {
Map<Integer, Integer> map = new HashMap<Integer, Integer>();
String path = application.getRealPath("like.txt");
try {
@SuppressWarnings("resource")
Scanner scanner = new Scanner(new File(path));
String values = scanner.nextLine();
values = values.substring(1, value.length() - 1);
System.out.println("values:" + values);
String[] keyValuePairs = values.split(",");
for (String pair : keyValuePairs) {
String[] entry = pair.split("=");
map.put(Integer.parseInt(entry[0].trim()), Integer.parseInt(entry[1].trim()));
rowCount++;
}
} catch (Exception e) {
e.printStackTrace();
}
Set<Integer> key = map.keySet();
String listId = key.toString();
listId = listId.substring(1, listId.length() - 1);
//
PagerModel pager = new PagerModel();
pager = PagerModel.getPager("acpager", 9, rowCount.intValue(), request);
pager.setRowCount(rowCount.intValue());
pager.navigate(request);
//
String hql = "FROM Product p WHERE p.status = 1 and p.id IN(" + listId + ")";
query = session.createQuery(hql);
query.setFetchSize(pager.getStartRow());
query.setMaxResults(pager.getPageSize());
model.addAttribute("flat", "type/favorite");
}
List<Product> list = query.list();
model.addAttribute("list", list);
return "user/product-list";
}
// Gửi mail link sản phẩm cho bạn bè
@RequestMapping("sendToFriend")
public String sendToFriend(ModelMap model, HttpServletRequest request, @RequestParam String id,
@RequestParam String from, @RequestParam String to, @RequestParam String subject,
@RequestParam String body) {
try {
MimeMessage message = mailSender.createMimeMessage();
MimeMessageHelper helper = new MimeMessageHelper(message, true);
helper.setFrom(from);
helper.setTo(to);
helper.setSubject(subject);
String url = request.getRequestURL().toString().replace("sendToFriend", "detail/" + id);
System.out.println(url);
String text = body + "<br/> Hãy click vào đây <a href='" + url + "'>Xem sản phẩm </a>";
helper.setText(text, true);
mailSender.send(message);
model.addAttribute("message", "success");
} catch (Exception e) {
model.addAttribute("message", "error");
}
return "redirect:/user/product/detail/" + id + ".htm";
}
}

UserHome:

@SuppressWarnings("unchecked")
@ModelAttribute("saleOffProducts")
public List<Product> get9SaleOffProducts() {
Session session = sessionFactory.getCurrentSession();
String hql = "FROM Product p WHERE p.status = 1 AND p.discount > 0 ORDER BY p.discount DESC";
Query query = session.createQuery(hql);
query.setMaxResults(9);
return query.list();
}

Model:

package hieuboy.model;
import java.util.Collection;
import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import org.springframework.format.annotation.DateTimeFormat;
@Entity
@Table(name = "Products",catalog = "HieuBoyShop")
public class Product implements java.io.Serializable {
private static final long serialVersionUID = 4208468054445602750L;
private Integer id;
private Category category;
private Producer producer;
private String nameProduct;
private String photo;
private Integer quantity;
private Date productDate;
private String unitBrief;
private Double unitPrice;
private Double discount;
private String description;
private Integer views;
private Boolean available;
private Boolean special;
private Boolean latest;
private Boolean status;
private Collection<OrderDetail> orderDetail;
public Product() {
}
public Product(Integer id, Category category, Producer producer, String nameProduct, Integer quantity,
Date productDate, String unitBrief, Double unitPrice) {
this.id = id;
this.category = category;
this.producer = producer;
this.nameProduct = nameProduct;
this.quantity = quantity;
this.productDate = productDate;
this.unitBrief = unitBrief;
this.unitPrice = unitPrice;
}
public Product(Integer id, Category category, Producer producer, String nameProduct, String photo, Integer quantity,
Date productDate, String unitBrief, Double unitPrice, Double discount, String description, Integer views,
Boolean available, Boolean special, Boolean latest, Boolean status, Collection<OrderDetail> orderDetail) {
this.id = id;
this.category = category;
this.producer = producer;
this.nameProduct = nameProduct;
this.photo = photo;
this.quantity = quantity;
this.productDate = productDate;
this.unitBrief = unitBrief;
this.unitPrice = unitPrice;
this.discount = discount;
this.description = description;
this.views = views;
this.available = available;
this.special = special;
this.latest = latest;
this.status = status;
this.orderDetail = orderDetail;
}
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID", unique = true, nullable = false)
public Integer getId() {
return this.id;
}
public void setId(Integer id) {
this.id = id;
}
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "CategoryID", nullable = false)
public Category getCategory() {
return this.category;
}
public void setCategory(Category category) {
this.category = category;
}
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "ProducerID", nullable = false)
public Producer getProducer() {
return this.producer;
}
public void setProducer(Producer producer) {
this.producer = producer;
}
@Column(name = "NameProduct", nullable = false)
public String getNameProduct() {
return this.nameProduct;
}
public void setNameProduct(String nameProduct) {
this.nameProduct = nameProduct;
}
@Column(name = "Photo")
public String getPhoto() {
return this.photo;
}
public void setPhoto(String photo) {
this.photo = photo;
}
@Column(name = "Quantity", nullable = false)
public Integer getQuantity() {
return this.quantity;
}
public void setQuantity(Integer quantity) {
this.quantity = quantity;
}
@Temporal(TemporalType.DATE)
@DateTimeFormat(pattern = "dd/MM/yyyy")
@Column(name = "ProductDate", nullable = false, length = 23)
public Date getProductDate() {
return this.productDate;
}
public void setProductDate(Date productDate) {
this.productDate = productDate;
}
@Column(name = "UnitBrief", nullable = false)
public String getUnitBrief() {
return this.unitBrief;
}
public void setUnitBrief(String unitBrief) {
this.unitBrief = unitBrief;
}
@Column(name = "UnitPrice", nullable = false, precision = 53, scale = 0)
public Double getUnitPrice() {
return this.unitPrice;
}
public void setUnitPrice(Double unitPrice) {
this.unitPrice = unitPrice;
}
@Column(name = "Discount", precision = 53, scale = 0)
public Double getDiscount() {
return this.discount;
}
public void setDiscount(Double discount) {
this.discount = discount;
}
@Column(name = "Description")
public String getDescription() {
return this.description;
}
public void setDescription(String description) {
this.description = description;
}
@Column(name = "Views")
public Integer getViews() {
return this.views;
}
public void setViews(Integer views) {
this.views = views;
}
@Column(name = "Available")
public Boolean getAvailable() {
return this.available;
}
public void setAvailable(Boolean available) {
this.available = available;
}
@Column(name = "Special")
public Boolean getSpecial() {
return this.special;
}
public void setSpecial(Boolean special) {
this.special = special;
}
@Column(name = "Latest")
public Boolean getLatest() {
return this.latest;
}
public void setLatest(Boolean latest) {
this.latest = latest;
}
@Column(name = "Status", nullable = false)
public Boolean getStatus() {
return this.status;
}
public void setStatus(Boolean status) {
this.status = status;
}
@OneToMany(fetch = FetchType.EAGER, mappedBy = "product")
public Collection<OrderDetail> getOrderDetail() {
return orderDetail;
}
public void setOrderDetail(Collection<OrderDetail> orderDetail) {
this.orderDetail = orderDetail;
}
}

Mysql:

CREATE TABLE `products` (
`ID` int NOT NULL AUTO_INCREMENT,
`NameProduct` varchar(50) NOT NULL,
`Photo` varchar(255) DEFAULT NULL,
`Quantity` int NOT NULL,
`ProductDate` datetime NOT NULL,
`UnitBrief` varchar(50) NOT NULL,
`UnitPrice` float NOT NULL,
`Discount` float DEFAULT NULL,
`Description` varchar(1000) DEFAULT NULL,
`Views` int DEFAULT NULL,
`Available` bit(1) DEFAULT NULL,
`Special` bit(1) DEFAULT NULL,
`Latest` bit(1) DEFAULT NULL,
`Status` bit(1) NOT NULL,
`CategoryID` int NOT NULL,
`ProducerID` int NOT NULL,
PRIMARY KEY (`ID`),
KEY `FK_Products_Categories` (`CategoryID`),
KEY `FK_Products_Producers` (`ProducerID`),
CONSTRAINT `FK_Products_Categories` FOREIGN KEY (`CategoryID`) REFERENCES `categories` (`ID`),
CONSTRAINT `FK_Products_Producers` FOREIGN KEY (`ProducerID`) REFERENCES `producers` (`ID`)
)

We hope to help, thank you so much.

Remove query.setMaxResults(9); from UserHome, apparently the query is constructed wrong - that number 9 ends up in wrong place.