I have a problem generating a mysql schema with hibernate. I've searched for an answer to my problem but I haven't found it (many similar problems but not exactly mine).
I need to map a State diagram into mysql (or transition table), and delete the states in cascade. A State may have many States as children (many to many relationship). I succeeded in mapping the State class and the "next state" relationship, but not in generating the correct SQL option ON DELETE CASCADE.
The Java class is as follows:
public class State {
private Integer version;
private Integer oid;
private String name;
private Map<String, State> nextStates;
public State(String name) {
super();
this.name = name;
this.nextStates = new HashMap<String, State>();
}
/**
* Adds some state to the transitions table.
* The table is indexed by states names.
*
*/
public void addNextState(State s) {
this.nextStates.put(s.getName(), s);
}
(...)
The hibernate configuration is :
<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost/testStatesDB</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.connection.password">pass</property>
<property name="hibernate.connection.pool_size">10</property>
<property name="show_sql">true</property>
<property name="format_sql">true</property>
<property name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
<property name="hibernate.hbm2ddl.auto">create-drop</property>
<property name="hibernate.bytecode.use_reflection_optimizer">false</property>
<property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
<mapping resource="State.hbm.xml" />
</session-factory>
</hibernate-configuration>
and the State mapping is as follows:
<hibernate-mapping>
<class name="State" table="state" optimistic-lock="version">
<id name="oid" column="OID">
<generator class="increment" />
</id>
<version column="version" name="version" unsaved-value="null"/>
<property name="name" type="java.lang.String"/>
<map name="nextStates" table="nextStates" cascade="delete" optimistic-lock="true">
<key column="parentState_oid" />
<index column="children_stateName" type="java.lang.String"/>
<many-to-many column="children_oid" class="State" />
</map>
</class>
</hibernate-mapping>
which creates the following schema:
Hibernate:
create table nextStates (
parentState_oid integer not null,
children_oid integer not null,
children_stateName varchar(255) not null,
primary key (parentState_oid, children_stateName)
) ENGINE=InnoDB
Hibernate:
create table state (
OID integer not null,
version integer not null,
name varchar(255),
primary key (OID)
) ENGINE=InnoDB
Hibernate:
alter table nextStates
add index FKB4A102D5587BDC23 (parentState_oid),
add constraint FKB4A102D5587BDC23
foreign key (parentState_oid)
references state (OID)
Hibernate:
alter table nextStates
add index FKB4A102D5F71F7FB (children_oid),
add constraint FKB4A102D5F71F7FB
foreign key (children_oid)
references state (OID)
As an example, consider running the following test code,
State pend = new State("Pending");
State dev = new State("In development");
State finali = new State("Finalized");
Session session = HibernateUtil.getSessionFactory().openSession();
session.beginTransaction();
pend.addNextState(dev);
dev.addNextState(finali);
dev.addNextState(pend);
session.save(pend);
session.save(dev);
session.save(finali);
session.getTransaction().commit();
session.beginTransaction();
dev = (State)session.load(State.class, 3);
session.delete(dev);
session.getTransaction().commit();
I get the following error message:
Cannot delete or update a parent row: a foreign key constraint fails (`testStatesDB`.`nextstates`, CONSTRAINT `FKB4A102D5F71F7FB` FOREIGN KEY (`children_oid`) REFERENCES `state` (`OID`))
and if I go to the database and add the property ON DELETE CASCADE to the generated foreign keys, the example works as I need.
The question is: Is this the correct way of mapping a many to many relationship using HashMaps? And if it is correct, how do I generate the ON DELETE CASCADE option with an hibernate xml mapping?
I've been dealing with this problem for a long time, but I didn't find a good solution.
