java - 向表中插入数据时生成 SQl 语法异常

Question

大家好，我是 hibernate 新手。我正在学习 one2many，这是程序。
我的程序有 1 个主类、1 个 cfg 文件和 1 个 hbm 文件。问题是当我将数据插入到我的表 state 和 city 时，它生成了 sql_queries 但之后它也生成了一个异常，该异常阻止了要在数据库中更新的数据.

配置.cfg.xml

   <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<!--
To change this license header, choose License Headers in Project Properties.
To change this template file, choose Tools | Templates
and open the template in the editor.
-->
<hibernate-configuration>
  <session-factory>
    <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/hibernate</property>
    <property name="hibernate.connection.username">root</property>
    <property name="hibernate.connection.password">ankita</property>
    <property name="hibernate.connection.autocommit">false</property>
    <property name="show_sql">true</property>
    <property name="format_sql">true</property>
    <property name="use_sql_comments">true</property>
    <property name="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
    <mapping resource="resources/State.hbm.xml"/>
  </session-factory>
</hibernate-configuration>

状态.hbm.xml

   <?xml version="1.0" encoding="UTF-8"?>
<!--
To change this license header, choose License Headers in Project Properties.
To change this template file, choose Tools | Templates
and open the template in the editor.
-->
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
    <class name="onetomany.States" table="stat">
        <id name="name" column="name" type="string">
            <generator class="assigned"></generator>
        </id>

        <property name="id" column="id" type="integer"></property>
        <list name="city" table="city" cascade="none" lazy="true">
            <key column="state"></key>
            <list-index base="0" column="index"></list-index>
            <one-to-many class="onetomany.Cities"></one-to-many>
        </list>
    </class>

    <class name="onetomany.Cities" table="city">
        <id name="id" column="id" type="integer">
            <generator class="increment"></generator>
        </id>

        <property name="name" column="name" type="string"></property>
    </class>

</hibernate-mapping>

主类

    /*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package onetomany;

import java.util.ArrayList;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

/**
 *
 * @author ankita mohanty
 */
public class Insert {
    public static void main(String[] args){
        Configuration cfg= new Configuration();
        cfg.configure("/resources/Config.cfg.xml");
        SessionFactory sf= cfg.buildSessionFactory();
        Session s= sf.openSession();
        Transaction tx= (Transaction) s.beginTransaction();

        States st= new States();
        st.setId(1);
        st.setName("Odisha");

        Cities ct= new Cities();
        ct.setName("Jajpur");
        Cities ct1= new Cities();
        ct1.setName("Bhadrak");
        Cities ct2= new Cities();
        ct2.setName("Jharsuguda");
        Cities ct3= new Cities();
        ct3.setName("Sambalpur");
        Cities ct4= new Cities();
        ct4.setName("Bhubaneswar");

        List<Cities> list= new ArrayList<Cities>();
        list.add(ct);
        list.add(ct1);
        list.add(ct3);
        list.add(ct4);

        st.setCity(list);
        s.save(st);
        s.save(ct);
        s.save(ct1);
        s.save(ct2);
        s.save(ct3);
        s.save(ct4);
        tx.commit();
        s.close();



    }
}

我的 table
(1)统计--------id
|--名称(PK)

(2)城市--------id(PK)
|--名字
|--状态(FK)
|--index(列表索引)

异常

org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:262)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:182)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1206)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:375)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137)
at onetomany.Insert.main(Insert.java:55)<br>
Caused by: java.sql.BatchUpdateException: 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 'index=3 where id=5' at line 1
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2054)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1467)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
... 8 more
Caused by: 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 'index=3 where id=5' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2794)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2006)
... 11 more

Answer 1

index 是 a reserved mysql keyword ，这可能是这里的问题，看到错误消息。

要解决此问题，您应该重命名该列。也许您可以在列名前加上表名，例如 city_index 等