为什么尽管已经配置了正确的方言,Hibernate还是会导致SQL语法错误?

huangapple go评论79阅读模式
英文:

Why does Hibernate cause a SQL syntax error despite having the correct dialect configured?

问题

我被卡住了。我正在设置一个新的Spring Boot项目。我已经创建了几个实体类。它能够成功地在数据库中创建一半的表,所以我知道它在很大程度上能够连接到数据库。我已经检查过,在配置中我已经设置了方言 - 我正在使用org.hibernate.dialect.MySQLDialect。
当我尝试运行这个程序时,我一直在得到一系列像这样的错误:

Caused by: java.sql.SQLSyntaxErrorException: 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 'integer, symbol varchar(5), primary key (id)) engine=InnoDB' at line 1

我尝试了在谷歌和其他stackoverflow问题中查找,但没有成功。我找到的大多数内容都引导我确保我已经设置了正确的方言,并避免使用保留关键字,而我已经检查过了。

以下是另外两个出现的错误:

Caused by: java.sql.SQLSyntaxErrorException: 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 'integer, primary key (id)) type=MyISAM' at line 1
Caused by: java.sql.SQLSyntaxErrorException: 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 'varchar(255), clock_in varchar(255), clock_out varchar(255), coin varchar(255), ' at line 1

编辑:
以下是Hibernate生成的SQL语句:

Hibernate: create table record (id integer not null, cash varchar(255), check varchar(255), clock_in varchar(255), clock_out varchar(255), coin varchar(255), primary key (id)) type=MyISAM
create table route (id integer not null, name varchar(100), color integer, driver varchar(255), index integer, primary key (id)) type=MyISAM
create table store (id integer not null, name varchar(100), address varchar(255), index integer, symbol varchar(255), primary key (id)) type=MyISAM

编辑2 - 这是其中一个在自动表生成方面似乎出问题的实体类:

package org.tampasa.kettles.models;

import javax.persistence.Entity;

@Entity
public class Store extends AbstractEntity{

    private String address;

    private String symbol;

    private int index;

    // Constructors

    public Store() {
    }

    // Getters and Setters

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getSymbol() {
        return symbol;
    }

    public void setSymbol(String symbol) {
        this.symbol = symbol;
    }

    public Integer getIndex() {
        return index;
    }

    public void setIndex(Integer index) {
        this.index = index;
    }

}

以下是AbstractEntity:

package org.tampasa.kettles.models;

import org.hibernate.validator.constraints.Length;

import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.MappedSuperclass;
import javax.validation.constraints.NotBlank;
import java.util.Objects;

@MappedSuperclass
public abstract class AbstractEntity {

    @Id
    @GeneratedValue
    private int id;

    @NotBlank(message = "Name must not be blank")
    @Length(min = 1, max = 100, message = "Name must be between 1 and 100 characters")
    private String name;

    public int getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return name;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        AbstractEntity that = (AbstractEntity) o;
        return id == that.id;
    }

    @Override
    public int hashCode() {
        return Objects.hash(id);
    }

}
英文:

I'm stumped. I'm setting up a new spring boot project. I've created several entity classes. It is able to create half of the tables in the database fine, so I know for the most part is is able to connect to the database. I've checked and I have the dialect set up in the configuration - I'm using org.hibernate.dialect.MySQLDialect.
When I try running the problem, I keep getting a set of errors like this:

Caused by: java.sql.SQLSyntaxErrorException: 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 'integer, symbol varchar(5), primary key (id)) engine=InnoDB' at line 1

I tried checking google and other stackoverflow questions to no avail. Mostly what I found guided me to making sure that I had the right dialect set up, and to avoid reserved keywords which I checked.

here are the other two errors that show up:

Caused by: java.sql.SQLSyntaxErrorException: 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 'integer, primary key (id)) type=MyISAM' at line 1
Caused by: java.sql.SQLSyntaxErrorException: 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 'varchar(255), clock_in varchar(255), clock_out varchar(255), coin varchar(255), ' at line 1

Edit:
Here is the sql that hibernate is generating:

Hibernate: create table record (id integer not null, cash varchar(255), check varchar(255), clock_in varchar(255), clock_out varchar(255), coin varchar(255), primary key (id)) type=MyISAM
create table route (id integer not null, name varchar(100), color integer, driver varchar(255), index integer, primary key (id)) type=MyISAM
create table store (id integer not null, name varchar(100), address varchar(255), index integer, symbol varchar(255), primary key (id)) type=MyISAM

Edit 2 - Here is one of the entity classes that seems to have trouble with the auto table generation:

package org.tampasa.kettles.models;

import javax.persistence.Entity;

@Entity
public class Store extends AbstractEntity{

    private String address;

    private String symbol;

    private int index;

    // Constructors

    public Store() {
    }


    // Getters and Setters

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getSymbol() {
        return symbol;
    }

    public void setSymbol(String symbol) {
        this.symbol = symbol;
    }

    public Integer getIndex() {
        return index;
    }

    public void setIndex(Integer index) {
        this.index = index;
    }

}

And here is AbstractEntity:

package org.tampasa.kettles.models;

import org.hibernate.validator.constraints.Length;

import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.MappedSuperclass;
import javax.validation.constraints.NotBlank;
import java.util.Objects;

@MappedSuperclass
public abstract class AbstractEntity {

    @Id
    @GeneratedValue
    private int id;

    @NotBlank(message = "Name must not be blank")
    @Length(min = 1, max = 100, message = "Name must be between 1 and 100 characters")
    private String name;

    public int getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return name;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        AbstractEntity that = (AbstractEntity) o;
        return id == that.id;
    }

    @Override
    public int hashCode() {
        return Objects.hash(id);
    }

}

答案1

得分: 3

可能的原因之一可能是列nameindex使用了SQL保留字,至少在MySQL 8.0中是如此。通常也是如此。
我也遇到过这个问题,可以参考这里。:D

根据这个答案,你可以在你的.properties文件中添加一行hibernate.globally_quoted_identifiers=true。这应该使Hibernate转义保留字,并可能修复你遇到的问题。

英文:

One possible reason could be that columns name and index use SQL reserved words. At least in MySQL 8.0. And generally too.
I've stumbled upon it too. 为什么尽管已经配置了正确的方言,Hibernate还是会导致SQL语法错误?

As per this answer you can add line hibernate.globally_quoted_identifiers=true to your .properties file. This should make Hibernate escape reserved words and maybe can fix issues you are experiencing.

huangapple
  • 本文由 发表于 2020年9月6日 02:31:51
  • 转载请务必保留本文链接:https://go.coder-hub.com/63757270.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定