JPA EclipseLink DatabaseException：'表foo.SEQUENCE不存在'

Question

我已经更新了问题，以便现在 两个 表都使用自动增量。持久化到MESSAGES表的问题也许是数据库模式的问题？

在尝试这样持久保存MessageBean时：

private void persist(MessageBean messageBean) throws Exception {
    LOG.info("loading.." + messageBean);

    Messages message = new Messages(messageBean);

    emf = Persistence.createEntityManagerFactory("USENETPU");
    em = emf.createEntityManager();

    em.getTransaction().begin();
    em.persist(message);
    em.getTransaction().commit();
}

堆栈跟踪：

run:
Jul 27, 2012 3:04:06 PM net.bounceme.dur.usenet.controller.CommentsDefaultListModel persist
INFO: loading..floor installer (cultas lake)
[EL Info]: 2012-07-27 15:04:10.006--ServerSession(30409723)--EclipseLink, version: Eclipse Persistence Services - 2.3.0.v20110604-r9504
[EL Info]: 2012-07-27 15:04:11.78--ServerSession(30409723)--file:/home/thufir/NetBeansProjects/USENET/build/classes/_USENETPU login successful
[EL Warning]: 2012-07-27 15:04:12.072--ClientSession(29574192)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'nntp.SEQUENCE' doesn't exist
Error Code: 1146
Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
    bind => [2 parameters bound]
Query: DataModifyQuery(name="SEQUENCE" sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")
Jul 27, 2012 3:04:12 PM net.bounceme.dur.usenet.controller.CommentsDefaultListModel <init>
SEVERE: null
Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'nntp.SEQUENCE' doesn't exist
Error Code: 1146
Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
    bind => [2 parameters bound]
Query: DataModifyQuery(name="SEQUENCE" sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324)

从MySql：

mysql> 
mysql> 
mysql> show tables;
+----------------+
| Tables_in_nntp |
+----------------+
| comments       |
| messages       |
+----------------+
2 rows in set (0.00 sec)

mysql> 
mysql> show create table comments;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                  |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| comments | CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message_id` int(11) NOT NULL,
  `comment` text NOT NULL,
  `stamp` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `message_id` (`message_id`),
  CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`message_id`) REFERENCES `messages` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> show create table messages;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                               |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| messages | CREATE TABLE `messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `newsgroup` text NOT NULL,
  `subject` text NOT NULL,
  `content` text NOT NULL,
  `number` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

以及消息字段：

package net.bounceme.dur.usenet.controller;

import java.io.Serializable;
import java.util.Collection;
import javax.persistence.*;
import javax.xml.bind.annotation.XmlRootElement;
import javax.xml.bind.annotation.XmlTransient;

@Entity
@Table(name = "messages", catalog = "nntp", schema = "")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "Messages.findAll", query = "SELECT m FROM Messages m"),
    @NamedQuery(name = "Messages.findById", query = "SELECT m FROM Messages m WHERE m.id = :id")})
public class Messages implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @Column(name = "id", nullable = false)
    @GeneratedValue
    private Integer id;
    @Basic(optional = false)
    @Lob
    @Column(name = "newsgroup", nullable = false, length = 65535)
    private String newsgroup;
    @Basic(optional = false)
    @Lob
    @Column(name = "subject", nullable = false, length = 65535)
    private String subject;
    @Basic(optional = false)
    @Lob
    @Column(name = "content", nullable = false, length = 65535)
    private String content;
    @Basic(optional = false)
    @Lob
    @Column(name = "number", nullable = false, length = 65535)
    private String number;
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "messageId")
    private Collection<Comments> commentsCollection;

    public Messages() {
    }

和评论字段：

package net.bounceme.dur.usenet.controller;

import java.io.Serializable;
import java.util.Date;
import javax.persistence.*;
import javax.xml.bind.annotation.XmlRootElement;

@Entity
@Table(name = "comments", catalog = "nntp", schema = "")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "Comments.findAll", query = "SELECT c FROM Comments c"),
    @NamedQuery(name = "Comments.findById", query = "SELECT c FROM Comments c WHERE c.id = :id"),
    @NamedQuery(name = "Comments.findByStamp", query = "SELECT c FROM Comments c WHERE c.stamp = :stamp")})
public class Comments implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @Column(name = "id", nullable = false)
    @GeneratedValue
    private Integer id;
    @Basic(optional = false)
    @Lob
    @Column(name = "comment", nullable = false, length = 65535)
    private String comment;
    @Basic(optional = false)
    @Column(name = "stamp", nullable = false)
    @Temporal(TemporalType.DATE)
    private Date stamp;
    @JoinColumn(name = "message_id", referencedColumnName = "id", nullable = false)
    @ManyToOne(optional = false)
    private Messages messageId;

    public Comments() {
    }

Answer 1

因为mysql我建议您以下：

在表messages的字段中id添加声明auto_increment：

create table messages(
...
id  int not null auto_increment,
...
primary key (id)
)

在实体声明时使用

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

这谈到了JPA使用MySQL的自动增量功能

如果不适用（例如，您可能想在同一事务中创建相关的另一个实体），请使用TABLE策略（有关更多详细信息，请参见http://www.objectdb.com/java/jpa/entity/generation）