时间戳

首页 > 解决方案 > SQL 语法异常。在休眠中映射和保存列的问题

sql - SQL 语法异常。在休眠中映射和保存列的问题

问题描述

我有一个奇怪的问题

    CREATE TABLE comment(
    commentId int(10) NOT NULL auto_increment,
    commentName varchar(45) DEFAULT NULL,

    primary key(commentId)

    );

    CREATE TABLE post(
    postId int(10) NOT NULL auto_increment,
    postName varchar(45) DEFAULT NULL,
    primary key(postId),
    `comment_id` int(11) DEFAULT NULL,
    KEY `FK_COMMENT_ID_idx` (`comment_id`),

    CONSTRAINT `FK_COMMENT` 
    FOREIGN KEY (`comment_id`) 
    REFERENCES `comment` (`commentId`) 

    ON DELETE NO ACTION ON UPDATE NO ACTION
    );

我想在帖子和评论之间建立单向关系。一篇文章有​​很多评论。我应该在哪里定义外键约束。在评论表或帖子表中（如代码中）。我正在使用休眠来保存帖子和评论。但我没有得到想要的结果。

我的休眠代码：

评论.java

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name="comment")
public class Comment {

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

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

public Comment() {
    
}

public Comment(String commentName) {
    this.commentName = commentName;
}

public int getCommentId() {
    return commentId;
}

public void setCommentId(int commentId) {
    this.commentId = commentId;
}

public String getCommentName() {
    return commentName;
}

public void setCommentName(String commentName) {
    this.commentName = commentName;
}

@Override
public String toString() {
    return "Comment [commentId=" + commentId + ", commentName=" + commentName + "]";
}
     }

Post.java

    import java.util.ArrayList;
    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.OneToMany;
    import javax.persistence.Table;

    @Entity
    @Table(name="post")
    public class Post {

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

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


@OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@JoinColumn(name="comment_id")
private List<Comment> comments;

public Post() {
    
}

public Post(String postName) {
    
    this.postName = postName;
}

public int getPostId() {
    return postId;
}

public void setPostId(int postId) {
    this.postId = postId;
}

public String getPostName() {
    return postName;
}

public void setPostName(String postName) {
    this.postName = postName;
}



public List<Comment> getComments() {
    return comments;
}

public void setComments(List<Comment> comments) {
    this.comments = comments;
}

@Override
public String toString() {
    return "POST [postId=" + postId + ", postName=" + postName + "]";
}

public void add(Comment theComment)
{
    if(comments==null)
    {
        comments=new ArrayList<>();
    }
    comments.add(theComment);
    }
}

CreatePostComment.java

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

    import com.learnJava.entity.Comment;
    import com.learnJava.entity.Post;

    public class CreatePostComment {

public static void main(String[] args) {

    SessionFactory factory=
            new Configuration()
            .configure("hibernate.cfg.xml")
            .addAnnotatedClass(Post.class)
            .addAnnotatedClass(Comment.class)
            .buildSessionFactory();
    
    Session session= factory.getCurrentSession();
    
    try {
        session.beginTransaction();
        
        
        Comment theComment=new Comment("Nice Comment");
        
        Post thePost= new Post("Amazing Post");
        
        thePost.add(theComment);
        
        session.save(thePost);
        
        session.getTransaction().commit();
        
    }
    catch (Exception e) {
    e.printStackTrace();
    }
    finally {
        session.close();
        factory.close();
    }

}

    }

输出

Nov 12, 2020 10:38:54 AM org.hibernate.Version logVersion
INFO: HHH000412: Hibernate ORM core version 5.4.19.Final
Nov 12, 2020 10:38:55 AM org.hibernate.annotations.common.reflection.java.JavaReflectionManager <clinit>
INFO: HCANN000001: Hibernate Commons Annotations {5.1.0.Final}
Nov 12, 2020 10:38:56 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure
WARN: HHH10001002: Using Hibernate built-in connection pool (not for production use!)
Nov 12, 2020 10:38:56 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001005: using driver [com.mysql.cj.jdbc.Driver] at URL [jdbc:mysql://localhost:3306/hb-04-one-to-many-uni?useSSL=false&serverTimezone=UTC]
Nov 12, 2020 10:38:56 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001001: Connection properties: {user=hbstudent, password=****}
Nov 12, 2020 10:38:56 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001003: Autocommit mode: false
Nov 12, 2020 10:38:56 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl$PooledConnections <init>
INFO: HHH000115: Hibernate connection pool size: 1 (min=1)
Nov 12, 2020 10:38:57 AM org.hibernate.dialect.Dialect <init>
INFO: HHH000400: Using dialect: org.hibernate.dialect.MySQLDialect
Hibernate: insert into post (postName) values (?)
Hibernate: insert into comment (commentName) values (?)
Hibernate: update comment set comment_id=? where commentId=?
Nov 12, 2020 10:38:59 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 1054, SQLState: 42S22
Nov 12, 2020 10:38:59 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: Unknown column 'comment_id' in 'field list'
Nov 12, 2020 10:38:59 AM org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl release
INFO: HHH000010: On release of batch it still contained JDBC statements
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute statement
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188)
    at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1364)
    at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:451)
    at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3210)
    at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2378)
    at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:447)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:183)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:40)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:281)
    at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101)
    at com.learnJava.hibernate.CreatePostComment.main(CreatePostComment.java:35)
Caused by: 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:113)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:200)
    at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:46)
    at org.hibernate.persister.collection.AbstractCollectionPersister.recreate(AbstractCollectionPersister.java:1357)
    at org.hibernate.persister.collection.OneToManyPersister.recreate(OneToManyPersister.java:187)
    at org.hibernate.action.internal.CollectionRecreateAction.execute(CollectionRecreateAction.java:52)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
    at org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1(ActionQueue.java:478)
    at java.util.LinkedHashMap.forEach(Unknown Source)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:475)
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:348)
    at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:40)
    at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:102)
    at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1360)
    ... 9 more
Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'comment_id' in 'field list'
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1347)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
    ... 21 more
Nov 12, 2020 10:38:59 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl$PoolState stop
INFO: HHH10001008: Cleaning up connection pool [jdbc:mysql://localhost:3306/hb-04-one-to-many-uni?useSSL=false&serverTimezone=UTC]

标签： sqlhibernatehibernate-mapping

解决方案

一个帖子有很多评论

如果这就是它应该的样子，那么你的整个结构就是倒退的。评论必须引用帖子，而不是引用评论的帖子。

CREATE TABLE post(
    id int(10) NOT NULL auto_increment,
    name varchar(45),
    content text,
    primary key(id)
);

CREATE TABLE comment(
    id int(10) NOT NULL auto_increment,
    post_id int(10) not null,
    name varchar(45) DEFAULT NULL,
    content text not null,
    primary key(id),
    CONSTRAINT `fk_post` FOREIGN KEY (`post_id`) 
    REFERENCES `post` (`id`) 
    ON DELETE NO ACTION ON UPDATE NO ACTION
);

然后在您的 Java 类中 Comment 应该有：

@ManyToOne
@JoinColumn(name = "post_id")
private Post post;

这里JoinColumn指定数据库中的列名，在comment表中。

Post 类应该有：

@OneToMany(mappedBy = "post", cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REFRESH, CascadeType.DETACH}, fetch = FetchType.LAZY, orphanRemoval = false)
private List<Comment> comments;

这里mappedBy指定了子java类中引用这个父类的字段名。

现在通常您可能希望值cascade​​为CascadeType.ALL从列表中）。

在持久化它们之前，您必须运行

theComment.setPost(thePost);

在每条评论上，否则您将违反约束。

推荐阅读