Hi All,

Im experiencing an annoying problem with hibernate. The problem involves Two Entities: Pergunta (Question) and Alternativa (Choices). Conceptually:

Pergunta -> 1..* Alternativa
Alternativa -> 1..1 Pergunta

There is a rule tha says: ONE of the Alternativas (Choices) MUST BE the "Gabarito" (Correct Answer).

I Modelled this rule as another relationship between Pergunta and Alternativa

Pergunta -> 1..1 Alternatva (the Correct Choice)

My Database Tables (relational model) is like that:

CREATE TABLE IF NOT EXISTS pergunta (
id int auto_increment primary key,
id_gabarito int not null,
id_submetedor int not null,
id_autor int,
id_materia int not null,
situacao char(20),
enunciado varchar(500) not null,

FOREIGN KEY (id, id_gabarito) REFERENCES alternativa(id_pergunta,id),
...
/* Other Foreign Key Rules that not involves Pertunta-Alternativa...*/
);

CREATE TABLE IF NOT EXISTS alternativa (
id int not null,
id_pergunta int not null,
enunciado varchar(500) not null,

PRIMARY KEY(id, id_pergunta),
FOREIGN KEY (id_pergunta) REFERENCES pergunta(id)
);


The correspondent Entities with annotations is described in "Mapping Documents"

Face this situation, don't know how can i insert a value in attributes Pergunta(id, id_gabarito) without breaking the ForeignKey constraint in table Pergunta: FOREIGN KEY (id, id_gabarito) REFERENCES alternativa(id_pergunta,id)

(remember that when a insert these values, the attributes Alternativa(id, id_pergunta) has no values yet.. and that broke the FK Rule)

If a first insert the values in Alternativa(id, id_pergunta) i break another Fk Rule in table Alternativa: FOREIGN KEY (id_pergunta) REFERENCES pergunta(id)


Thats the Chicken-Egg situation..

What can i do to hibernate solve this problem for me? OR i must resolve this problem with triggers (i don't want to do this because of applicationportability)

I hope you all could understand my situation.. and answers are wellcome!


Hibernate version:

INFO: Hibernate EntityManager 3.2.0.GA
29/11/2006 11:49:30 org.hibernate.cfg.annotations.Version <clinit>
INFO: Hibernate Annotations 3.2.0.GA
29/11/2006 11:49:30 org.hibernate.cfg.Environment <clinit>
INFO: Hibernate 3.2.0

Mapping documents:

@Entity
@Table(name = "pergunta", catalog = "jogoconcurso", uniqueConstraints = {})
public class Pergunta implements java.io.Serializable {

...<fields>

@Id @GeneratedValue(strategy=GenerationType.AUTO)
@Column(name = "id", unique = true, nullable = false, insertable = true, updatable = true)
public int getId() {
return this.id;
}

.....

@OneToOne(cascade = {CascadeType.ALL}, fetch = FetchType.LAZY, optional=false)
@JoinColumns( {
@JoinColumn(name = "id_gabarito", unique = false, nullable = false, insertable = false, updatable = false),
@JoinColumn(name = "id", unique = true, nullable = false, insertable = false, updatable = false) })

public Alternativa getGabarito() {
return this.gabarito;
}



public void setGabarito(Alternativa alternativaGabarito) {
this.gabarito = alternativaGabarito;
alternativaGabarito.setPergunta(this);
}


@OneToMany(cascade = { CascadeType.ALL }, fetch = FetchType.LAZY)
public Set<Alternativa> getAlternativas() {
return this.alternativas;
}

public void setAlternativas(Set<Alternativa> alternativas) {
this.alternativas = alternativas;
}

.....
}

// Class Alternativa...

@Entity
@Table(name = "alternativa", catalog = "jogoconcurso", uniqueConstraints = {})
public class Alternativa implements java.io.Serializable {

... <fields>

@EmbeddedId
@AttributeOverrides( {
@AttributeOverride(name = "id", column = @Column(name = "id", unique = false, nullable = false, insertable = true, updatable = true)),
@AttributeOverride(name = "idPergunta", column = @Column(name = "id_pergunta", unique = false, nullable = false, insertable = true, updatable = true)) })
public AlternativaId getId() {
return this.id;
}

public void setId(AlternativaId id) {
this.id = id;
}


@ManyToOne(cascade = {}, fetch = FetchType.LAZY)
@JoinColumn(name = "id_pergunta", unique = false, nullable = false, insertable = false, updatable = false)
public Pergunta getPergunta() {
return this.pergunta;
}

public void setPergunta(Pergunta pergunta) {
this.pergunta = pergunta;
}

.... some get/set

}

// the AlternativaId class

@Embeddable
public class AlternativaId implements java.io.Serializable {

...
@Column(name = "id", unique = false, nullable = false, insertable = true, updatable = true)
public int getId() {
return this.id;
}

public void setId(int id) {
this.id = id;
}

@Column(name = "id_pergunta", unique = false, nullable = false, insertable = true, updatable = true)
public int getIdPergunta() {
return this.idPergunta;
}

public void setIdPergunta(int idPergunta) {
this.idPergunta = idPergunta;
}

.. equals().. hashCode()..

}
Code between sessionFactory.openSession() and session.close():

public void persist(Pergunta transientInstance) {
log.debug("persisting Pergunta instance");
EntityTransaction t = entityManager.getTransaction();
try {
t.begin();
entityManager.persist(transientInstance);
log.debug("persist successful");
t.commit();
} catch (RuntimeException re) {
log.error("persist failed", re);
t.rollback();
throw re;
}
}

Full stack trace of any exception that occurs:
29/11/2006 11:49:33 org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 1364, SQLState: HY000
29/11/2006 11:49:33 org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: Field 'id_gabarito' doesn't have a default value
29/11/2006 11:49:33 jogo.persistence.dao.PerguntaHome persist
SEVERE: persist failed
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not insert: [jogo.model.Pergunta]
at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:647)
at org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityManagerImpl.java:218)
at jogo.persistence.dao.PerguntaHome.persist(PerguntaHome.java:37)
at teste.persistence.dao.PerguntaHomeTest.testSimpleInsertPergunta(PerguntaHomeTest.java:67)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:128)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: org.hibernate.exception.GenericJDBCException: could not insert: [jogo.model.Pergunta]
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:40)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2093)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2573)
at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:47)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:290)
at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:180)
at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:108)
at org.hibernate.event.def.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:131)
at org.hibernate.event.def.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:87)
at org.hibernate.event.def.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:38)
at org.hibernate.impl.SessionImpl.firePersist(SessionImpl.java:618)
at org.hibernate.impl.SessionImpl.persist(SessionImpl.java:592)
at org.hibernate.impl.SessionImpl.persist(SessionImpl.java:596)
at org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityManagerImpl.java:212)
... 18 more
Caused by: java.sql.SQLException: Field 'id_gabarito' doesn't have a default value
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2921)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1570)
at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1085)
at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:670)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1159)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1076)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1061)
at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:73)
at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:33)
... 32 more


Name and version of the database you are using:

MySQL, version: 5.0.27-community-nt

The generated SQL (show_sql=true):

Hibernate: insert into jogoconcurso.pergunta (id_materia, id_submetedor, enunciado, id_autor, situacao) values (?, ?, ?, ?, ?)