Hi there

Im having problems mapping a table to my MySql DB. Im learning to use JPA , so im doing the "HelloWorld" exercises on the chapter 2 of the Book "Java Persistence with Hibernate". My field "id" from the Message.java file, is intent to be auto-increment, so i used the follow annotation: @Id @GeneratedValue(strategy=GenerationType.IDENTITY) that produces this sql line by the hbm2dll tool: MESSAGE_ID bigint generated by default as identity (start with 1). I guess MySql dont support the "generated by default as identity" definition. If i am rigth, (that im not sure) how can i define a auto-increment to Mysql DB? If not, what am i doing wrong?

Thanx for the help again:

Hibernate version:
Hibernate Core 3.2.2 GA
Hibernate EntityManager 3.2.1 GA
Hibernate Tools 3.2 beta 9

Mapping documents:
Message.java
package immoserver.persistence ;

import javax.persistence.*;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;

@Entity
@Table(name="MESSAGES")
public class Message implements java.io.Serializable {
// <editor-fold defaultstate="collapsed" desc=" PrimaryKey: Long id ">
@Id @GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name="MESSAGE_ID", nullable = false)
private Long id;

public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
//</editor-fold>

// <editor-fold defaultstate="collapsed" desc=" Property: String text ">
@Column(name="TEXT")
private String text;

public String getText() {
return text;
}
public void setText(String text) {
this.text = text;
}
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc=" N-1 Relation to Message nextMessage ">
@ManyToOne( cascade = CascadeType.ALL )
@JoinColumn(name="NEXT_MESSAGE_ID")
private Message nextMessage;

public Message getMessage() {
return this.nextMessage;
}

public void setMessage(Message message) {
this.nextMessage = message;
}
// </editor-fold>

}

Name and version of the database you are using:
MySql 4.0.20a

the persistence.xml file:
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/ ... ce_1_0.xsd">
<persistence-unit name="HelloWorld" >
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<properties>
<property name="hibernate.archive.autodetection" value="class, hbm"/>

<property name="hibernate.show_sql" value="true"/>
<property name="hibernate.format_sql" value="true"/>

<property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver" />
<property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/immo" />
<property name="hibernate.connection.username" value="root" />

<property name="hibernate.c3p0.min_size" value="5"/>
<property name="hibernate.c3p0.max_size" value="20"/>
<property name="hibernate.c3p0.timeout" value="300"/>
<property name="hibernate.c3p0.max_statements" value="50"/>

<property name="hibernate.c3p0.idle_test_period" value="3000"/>
<property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/>
<property name="hibernate.hbm2ddl.auto" value="create-drop"/>
</properties>
</persistence-unit>
</persistence>

The generated SQL by the hibernate tool hbm2ddl:
alter table MESSAGES
drop constraint FK131AF14C43978BD2;

drop table MESSAGES if exists;

create table MESSAGES (
MESSAGE_ID bigint generated by default as identity (start with 1),
TEXT varchar(255),
NEXT_MESSAGE_ID bigint,
primary key (MESSAGE_ID)
);

alter table MESSAGES
add constraint FK131AF14C43978BD2
foreign key (NEXT_MESSAGE_ID)
references MESSAGES;


Full stack trace of any exception that occurs:

alter table MESSAGES
drop constraint FK131AF14C43978BD2;

drop table MESSAGES if exists;

create table MESSAGES (
MESSAGE_ID bigint generated by default as identity (start with 1),
TEXT varchar(255),
NEXT_MESSAGE_ID bigint,
primary key (MESSAGE_ID)
);

27/Fev/2007 8:28:03 org.hibernate.tool.hbm2ddl.SchemaExport create
SEVERE: Unsuccessful: create table MESSAGES (MESSAGE_ID bigint generated by default as identity (start with 1), TEXT varchar(255), NEXT_MESSAGE_ID bigint, primary key (MESSAGE_ID))
27/Fev/2007 8:28:03 org.hibernate.tool.hbm2ddl.SchemaExport create
SEVERE: 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 'generated by default as identity (start with 1), TEXT varchar(2

alter table MESSAGES
add constraint FK131AF14C43978BD2
foreign key (NEXT_MESSAGE_ID)
references MESSAGES;

27/Fev/2007 8:28:03 org.hibernate.tool.hbm2ddl.SchemaExport create
SEVERE: Unsuccessful: alter table MESSAGES add constraint FK131AF14C43978BD2 foreign key (NEXT_MESSAGE_ID) references MESSAGES
27/Fev/2007 8:28:03 org.hibernate.tool.hbm2ddl.SchemaExport create
SEVERE: Table 'immo.messages' doesn't exist
27/Fev/2007 8:28:03 org.hibernate.tool.hbm2ddl.SchemaExport execute
INFO: schema export complete
4 errors occurred while performing <hbm2ddl>.
Error #1: java.sql.SQLException: 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 'constraint FK131AF14C43978BD2' at line 1
Error #1: java.sql.SQLException: 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 'if exists' at line 1
Error #1: java.sql.SQLException: 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 'generated by default as identity (start with 1), TEXT varchar(2
Error #1: java.sql.SQLException: Table 'immo.messages' doesn't exist

Fix by Andy:

In you persistence.xml file you have the DB dialiect set to:




This is for the Hypersonic database and NOT mysql, try changing it to the following setting




Cheers,

Andy[/code]