JPA EclipseLink DatabaseException : 'table foo.SEQUENCE n'existe pas'

Question

J'ai mis à jour la question de manière à ce que à la fois utilisent désormais l'auto-incrémentation. Le problème n'est-il pas de persister jusqu'à la MESSAGES table un problème avec le schéma de la base de données ?

En essayant de persister un MessageBean comme suit :

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();
}

Trace de pile :

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)

A partir de 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> 

et les champs pour les messages :

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() {
    }

Et les champs Commentaires :

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

Pour mysql Je vous recommande de suivre ce qui suit :

À votre table messages sur le terrain id ajouter une déclaration auto_increment :

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

Lors de la déclaration de l'entité, utiliser

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

Il s'agit de l'utilisation par JPA de la fonction d'auto-incrémentation de MySQL.

Si elle n'est pas applicable (par exemple, si vous souhaitez créer une autre entité liée dans la même transaction), utilisez la stratégie TABLE (pour plus de détails, voir http://www.objectdb.com/java/jpa/entity/generated )

Answer 2

La stratégie AUTO est un alias de NATIVE si votre base de données le supporte, ou de SEQUENCE si votre base de données le supporte, ou de TABLE si votre base de données ne supporte aucun de ces éléments.

Ainsi, si la base de données ne prend pas en charge NATIVE et SEQUENCE, vous devez créer la table qu'EclipseLink utilise pour générer des identifiants.

Avec MySQL, NATIVE devrait être pris en charge. Vous devez cependant faire en sorte que la colonne ID soit une colonne auto_incrémentée. Assurez-vous de configurer les paramètres Plate-forme de base de données également.