java - 您的 SQL 语法有错误 - hibernate 和 mysql

Question

我正在学习 Hibernate，在简单的演示应用程序中，我遇到了问题，我无法克服。只有两个表，两个实体类和主应用程序。项目在 Maven 中。

Github：github.com/fangirsan/maruszka.git

批处理类：

package com.maruszka.entity;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.CascadeType;
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.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

@Entity
@Table(name="batch")
public class Batch {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="id")
    private int id;

    @Column(name="batch_number")
    private int batchNumber;

    @Column(name="batch_style")
    private String batchStyle;

    @Column(name="batch_name")
    private String batchName;

    @Column(name="batch_creation_date", columnDefinition="DATE")
    @Temporal(TemporalType.TIMESTAMP)
    private Date batchCreationDate;

    @ManyToMany(fetch=FetchType.LAZY,
                cascade= {CascadeType.PERSIST, CascadeType.MERGE,
                          CascadeType.DETACH, CascadeType.REFRESH})
    @JoinTable(
            name="batch_malt",
            joinColumns=@JoinColumn(name="batch_id"),
            inverseJoinColumns=@JoinColumn(name="malt_id")
            )
    private List<Malt> malts;

    public Batch() {

    }

    public Batch(int batchNumber, String batchStyle, String batchName, Date batchCreationDate) {
        this.batchNumber = batchNumber;
        this.batchStyle = batchStyle;
        this.batchName = batchName;
        this.batchCreationDate = batchCreationDate;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getBatchNumber() {
        return batchNumber;
    }

    public void setBatchNumber(int batchNumber) {
        this.batchNumber = batchNumber;
    }

    public String getBatchStyle() {
        return batchStyle;
    }

    public void setBatchStyle(String batchStyle) {
        this.batchStyle = batchStyle;
    }

    public String getBatchName() {
        return batchName;
    }

    public void setBatchName(String batchName) {
        this.batchName = batchName;
    }

    public Date getBatchCreationDate() {
        return batchCreationDate;
    }

    public void setBatchCreationDate(Date batchCreationDate) {
        this.batchCreationDate = batchCreationDate;
    }

    public List<Malt> getMalts() {
        return malts;
    }

    public void setMalts(List<Malt> malts) {
        this.malts = malts;
    }

    @Override
    public String toString() {
        return "Batch [id=" + id + ", batchNumber=" + batchNumber + ", batchStyle=" + batchStyle + ", batchName="
                + batchName + ", batchCreationDate=" + batchCreationDate + ", malts=" + malts + "]";
    }

    // add a convenience method
    public void addMalt(Malt theMalt) {

        if (malts == null) {
            malts = new ArrayList<>();
        }

        malts.add(theMalt);
    }

}

麦芽类

package com.maruszka.entity;

import java.util.List;

import javax.persistence.CascadeType;
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.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.Table;

@Entity
@Table(name="malt")
public class Malt {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="id")
    private int id;

    @Column(name="malt_name")
    private String maltName;

    @Column(name="malt_manufacturer")
    private String maltManufacturer;

    @Column(name="filling")
    private int filling;

    @Column(name="ebc")
    private int ebc;

    @Column(name="usage")
    private String usage;

    @ManyToMany(fetch=FetchType.LAZY,
            cascade= {CascadeType.PERSIST, CascadeType.MERGE,
                      CascadeType.DETACH, CascadeType.REFRESH})
    @JoinTable(
        name="batch_malt",
        joinColumns=@JoinColumn(name="malt_id"),
        inverseJoinColumns=@JoinColumn(name="batch_id")
        )
    private List<Batch> batches;

    public Malt() {

    }

    public Malt(String maltName, String maltManufacturer, int filling, int ebc, String usage) {
        this.maltName = maltName;
        this.maltManufacturer = maltManufacturer;
        this.filling = filling;
        this.ebc = ebc;
        this.usage = usage;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getMaltName() {
        return maltName;
    }

    public void setMaltName(String maltName) {
        this.maltName = maltName;
    }

    public String getMaltManufacturer() {
        return maltManufacturer;
    }

    public void setMaltManufacturer(String maltManufacturer) {
        this.maltManufacturer = maltManufacturer;
    }

    public int getFilling() {
        return filling;
    }

    public void setFilling(int filling) {
        this.filling = filling;
    }

    public int getEbc() {
        return ebc;
    }

    public void setEbc(int ebc) {
        this.ebc = ebc;
    }

    public String getUsage() {
        return usage;
    }

    public void setUsage(String usage) {
        this.usage = usage;
    }

    public List<Batch> getBatches() {
        return batches;
    }

    public void setBatches(List<Batch> batches) {
        this.batches = batches;
    }

    @Override
    public String toString() {
        return "Malt [id=" + id + ", maltName=" + maltName + ", maltManufacturer=" + maltManufacturer + ", filling="
                + filling + ", ebc=" + ebc + ", usage=" + usage + ", batches=" + batches + "]";
    }

}

CreateBatchAndMalt.class（主要）：

package com.maruszka.test;

import java.util.Date;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

import com.maruszka.entity.Malt;
import com.maruszka.entity.Batch;

public class CreateBatchAndMalt {

    public static void main(String[] args) {

        // create session factory
        SessionFactory factory = new Configuration()
                                .configure("hibernate.cfg.xml")
                                .addAnnotatedClass(Batch.class)
                                .addAnnotatedClass(Malt.class)
                                .buildSessionFactory();

        // create session
        Session session = factory.getCurrentSession();

            try {           

                // start a transaction
                session.beginTransaction();

                // create a malt
                Malt tempMalt = new Malt("Carafa (R) typ I", "Weyerman", 10, 900, "Stout, Porter, Schwarzbier");

                // save the malt
                System.out.println("\nSaving the malt ...");
                session.save(tempMalt);
                System.out.println("Saved the malt: " + tempMalt);

                // create the batch
                Date now = new Date();
                Batch tempBatch = new Batch(2, "Mild", "Szatan", now);

                // add malt to the batch
                tempBatch.addMalt(tempMalt);

                // save the batch
                System.out.println("\nSaving batch ...");
                session.save(tempBatch);
                System.out.println("Saved batch: " + tempBatch.getMalts());

                // commit transaction
                session.getTransaction().commit();

                System.out.println("Done!");
            }
            finally {

                // close session
                session.close();

                factory.close();
            }
    }

}

pom.xml：

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">

  <modelVersion>4.0.0</modelVersion>

  <groupId>com.maruszka</groupId>
  <artifactId>maruszka</artifactId>
  <packaging>war</packaging>
  <version>1.0</version>

  <name>maruszka Maven Webapp</name>
  <url>http://maven.apache.org</url>

    <properties>
        <springframework.version>5.0.2.RELEASE</springframework.version>
        <springsecurity.version>5.0.1.RELEASE</springsecurity.version>

        <hibernate.version>5.3.1.Final</hibernate.version>
        <!-- 5.2.12.Final -->
        <mysql.connector.version>5.1.38</mysql.connector.version>
        <c3po.version>0.9.5.2</c3po.version>

        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

  <dependencies>

    <!-- Add support for JUnit -->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>3.8.1</version>
        <scope>test</scope>
    </dependency>

    <!-- Spring MVC support -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-webmvc</artifactId>
        <version>${springframework.version}</version>
    </dependency>

    <!-- Add support for Spring Tags -->
    <dependency>
        <groupId>org.springframework.security</groupId>
        <artifactId>spring-security-taglibs</artifactId>
        <version>${springsecurity.version}</version>
    </dependency>

     <!-- Hibernate Core -->
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>${hibernate.version}</version>
    </dependency>

    <!-- Hibernate Validator -->
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-validator</artifactId>
        <version>6.0.4.Final</version>
    </dependency>

    <!-- Add MySQL and C3P0 support -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>${mysql.connector.version}</version>
    </dependency>

    <!-- dependency>
        <groupId>com.mchange</groupId>
        <artifactId>c3p0</artifactId>
        <version>0.9.5.2</version>
    </dependency-->


  </dependencies>
  <build>
    <!-- TO DO: Add support for Maven WAR Plugin -->
    <finalName>maruszka</finalName> 
    <pluginManagement>
        <plugins>
            <plugin>
                <!-- Add Maven coordinates for: maven-war-plugin -->
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-war-plugin</artifactId>
                <version>3.2.0</version>
            </plugin>
        </plugins>
    </pluginManagement>

  </build>
</project>

hibernate.cfg.xml（在 maruszka\src\main\resources\hibernate.cfg.xml 中）：

<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

    <session-factory>

        <!-- JDBC Database connection settings -->
        <property name="connection.driver_class">com.mysql.cj.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://localhost:3306/maruszka?serverTimezone=UTC</property>
        <!-- &useSSL=false -->
        <property name="connection.username">***</property>
        <property name="connection.password">***</property>

        <!-- JDBC connection pool settings ... using built-in test pool -->
        <property name="connection.pool_size">1</property>

        <!-- Select our SQL dialect -->
        <property name="dialect">org.hibernate.dialect.MySQLDialect</property>

        <!-- Echo the SQL to stdout -->
        <property name="show_sql">false</property>

        <!-- Set the current session context -->
        <property name="current_session_context_class">thread</property>

    </session-factory>

</hibernate-configuration>

数据库脚本：

CREATE DATABASE  IF NOT EXISTS `maruszka`;
USE `maruszka`;

SET FOREIGN_KEY_CHECKS=0;

-- batch
DROP TABLE IF EXISTS `batch`;
CREATE TABLE `batch` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `batch_number` int(3) NOT NULL,
    `batch_style` varchar(45) DEFAULT NULL,
    `batch_name` varchar(45) DEFAULT NULL,
    `batch_creation_date` DATE,
    PRIMARY KEY (`id`),
    UNIQUE KEY(`batch_number`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;    

LOCK TABLE `batch` WRITE;

INSERT INTO `batch` VALUES
  (1, 1, 'Stout', 'Happy Stout', '2018-06-06');

UNLOCK TABLES;

-- malt
DROP TABLE IF EXISTS `malt`;
CREATE TABLE `malt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `malt_name` varchar(45) DEFAULT NULL,
  `malt_manufacturer` varchar(45) DEFAULT NULL,
  `filling` int(3) DEFAULT NULL,
  `ebc` int(3) DEFAULT NULL,
  `usage` varchar(254) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY(`malt_name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

LOCK TABLES `malt` WRITE;

INSERT INTO `malt` VALUES
    (1, 'Pale Ale', 'Malteurop', 100, 6, 'All');

UNLOCK TABLES;

 -- join table for batch - malt
 DROP TABLE IF EXISTS `batch-malt`;
 CREATE TABLE `batch-malt` (
    `batch_id` int(11) NOT NULL,
    `malt_id` int(11) NOT NULL,
    PRIMARY KEY (`batch_id`, `malt_id`),

    -- fk_[referencing table name]_[referenced table name]_[referencing field name]
    CONSTRAINT `FK_BATCH_MALT_ID` FOREIGN KEY (`malt_id`)
    REFERENCES `malt` (`id`)
    ON DELETE NO ACTION ON UPDATE NO ACTION,

    CONSTRAINT `FK_MALT_BATCH_ID` FOREIGN KEY (`batch_id`)
    REFERENCES `batch` (`id`)
    ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 SET FOREIGN_KEY_CHECKS=1;

在版本 8.0.11 中使用 mysql 连接器时：

cze 06, 2018 9:33:53 PM org.hibernate.Version logVersion
INFO: HHH000412: Hibernate Core {5.3.1.Final}
cze 06, 2018 9:33:53 PM org.hibernate.cfg.Environment <clinit>
INFO: HHH000206: hibernate.properties not found
cze 06, 2018 9:33:53 PM org.hibernate.annotations.common.reflection.java.JavaReflectionManager <clinit>
INFO: HCANN000001: Hibernate Commons Annotations {5.0.3.Final}
cze 06, 2018 9:33:53 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure
WARN: HHH10001002: Using Hibernate built-in connection pool (not for production use!)
cze 06, 2018 9:33:53 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001005: using driver [com.mysql.cj.jdbc.Driver] at URL [jdbc:mysql://localhost:3306/maruszka?serverTimezone=UTC]
cze 06, 2018 9:33:53 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001001: Connection properties: {user=root, password=****}
cze 06, 2018 9:33:53 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001003: Autocommit mode: false
cze 06, 2018 9:33:53 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl$PooledConnections <init>
INFO: HHH000115: Hibernate connection pool size: 1 (min=1)
Wed Jun 06 21:33:53 CEST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
cze 06, 2018 9:33:53 PM org.hibernate.dialect.Dialect <init>
INFO: HHH000400: Using dialect: org.hibernate.dialect.MySQLDialect
cze 06, 2018 9:33:54 PM org.hibernate.validator.internal.util.Version <clinit>
INFO: HV000001: Hibernate Validator 6.0.4.Final

Saving the malt ...
cze 06, 2018 9:33:54 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 1064, SQLState: 42000
cze 06, 2018 9:33:54 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
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 near 'usage) values (900, 10, 'Weyerman', 'Carafa (R) typ I', 'Stout, Porter, Schwarzb' at line 1
cze 06, 2018 9:33:54 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl stop
INFO: HHH10001008: Cleaning up connection pool [jdbc:mysql://localhost:3306/maruszka?serverTimezone=UTC]
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute statement
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:178)
    at org.hibernate.dialect.identity.GetGeneratedKeysDelegate.executeAndExtract(GetGeneratedKeysDelegate.java:57)
    at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:42)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3037)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3628)
    at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:81)
    at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:645)
    at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:282)
    at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:263)
    at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:317)
    at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:359)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:292)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:200)
    at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:131)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:192)
    at org.hibernate.event.internal.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:38)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:177)
    at org.hibernate.event.internal.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:32)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:73)
    at org.hibernate.internal.SessionImpl.fireSave(SessionImpl.java:709)
    at org.hibernate.internal.SessionImpl.save(SessionImpl.java:701)
    at org.hibernate.internal.SessionImpl.save(SessionImpl.java:696)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.hibernate.context.internal.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:349)
    at com.sun.proxy.$Proxy36.save(Unknown Source)
    at com.maruszka.test.CreateBatchAndMalt.main(CreateBatchAndMalt.java:42)
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 'usage) values (900, 10, 'Weyerman', 'Carafa (R) typ I', 'Stout, Porter, Schwarzb' at line 1
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:118)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:960)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1116)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1066)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1396)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1051)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
    ... 28 more

我仔细检查了语法，但无法弄清楚是什么原因造成的：

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 near 'usage) values (900, 10, 'Weyerman', 'Carafa (R) typ I', 'Stout, Porter, Schwarzb' at line 1

此致

标签： javamysqlhibernate

Answer 1

在您的 sql 表中malt，您有一个名为的列usage，该名称是MySQL 中的保留字，所以这就是您的插入查询不起作用的原因。

最简单的方法是将名称更改为其他名称，例如malt_usage

所以这不起作用：

INSERT INTO malt(filling, ebc, malt_manufacturer, malt_name, usage) 
VALUES (900, 10, 'Weyerman', 'Carafa (R) typ I', 'Stout, Porter, Schwarzb')

但这会

INSERT INTO malt(filling, ebc, malt_manufacturer, malt_name, malt_usage) 
VALUES (900, 10, 'Weyerman', 'Carafa (R) typ I', 'Stout, Porter, Schwarzb')

在java中这不是问题，所以usage如果你愿意，你可以保留名字

@Column(name="malt_usage")
private String usage;