英文:
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
可能的原因之一可能是列name和index使用了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. ![]()
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。


评论