Hi, I started using Hibernate recently and I'm still a newbie, however, the error I'm facing doesn't seem to be simple.
My current environment is:
- Windows 7
- MySQL 5.1.49-community
- mysql-connector-java-5.1.13-bin.jar
- hibernate-distribution-3.6.0.Beta1
Steps I followed:
1) Created 2 tables using MySQL Workbench: User and UserClass. User contains a user_class_id field that is a foreign key to UserClass.
2) Created a test program to validate Cascade Deletion of UserClasses. I expect all users that have a given userclass is deleted when the refered userclass is deleted.
Here it all relevant code:
User.java:
Code:
package domain;
public class User {
private String userName;
private String password;
private Boolean blocked;
private UserClass userClass;
public User() { }
public String getUserName() { return userName; }
public void setUserName(String userName) { this.userName = userName; }
public String getPassword() { return password; }
public void setPassword(String password) { this.password = password; }
public Boolean getBlocked() { return blocked; }
public void setBlocked(Boolean blocked) { this.blocked = blocked; }
public UserClass getUserClass() { return this.userClass; }
public void setUserClass(UserClass userClass) { this.userClass = userClass; }
}
User.hbm.xml:
Code:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="domain">
<class name="User" table="user">
<id
column="username"
name="userName"
type="string">
<generator class="assigned"/>
</id>
<property
column="password"
name="password"
not-null="true"
type="string"/>
<property column="blocked" name="blocked" type="boolean"/>
<many-to-one
name="userClass"
column="user_class_id"
class="UserClass"
not-null="true"/>
</class>
</hibernate-mapping>
UserClass.java:
Code:
package domain;
import java.util.HashSet;
import java.util.Set;
public class UserClass {
private Long id;
private String title;
private String permissions;
private Set users = new HashSet();
public UserClass() { }
public Long getId() { return id; }
private void setId(Long id) { this.id = id; }
public String getTitle() { return title; }
public void setTitle(String title) { this.title = title; }
public String getPermissions() { return permissions; }
public void setPermissions(String permissions) { this.permissions = permissions; }
public void setUsers(Set users) { this.users = users; }
public Set getUsers() { return this.users; }
public void addUser(User user) { user.setUserClass(this); this.users.add(user); }
}
UserClass.hbm.xml:
Code:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="domain">
<class
name="UserClass"
table="userclass">
<id
column="id"
name="id">
<generator class="native"/>
</id>
<property
column="title"
name="title"
not-null="true"
type="string"/>
<property
column="permissions"
name="permissions"
not-null="true"
type="string"/>
<set
name="users"
inverse="true"
cascade="all-delete-orphan">
<key column="user_class_id"/>
<one-to-many class="User"/>
</set>
</class>
</hibernate-mapping>
DatabaseFactory.java:
Code:
package Database;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
public class DatabaseFactory {
private static DatabaseFactory instance = null;
private SessionFactory sessionFactory;
public static DatabaseFactory getInstance() {
if (DatabaseFactory.instance == null) {
DatabaseFactory.instance = new DatabaseFactory().init();
}
return DatabaseFactory.instance;
}
public SessionFactory getSessionFactory() { return this.sessionFactory; }
public Session getSession() { return this.sessionFactory.openSession(); }
private DatabaseFactory init() {
Configuration cfg = new Configuration();
cfg.addClass(domain.UserClass.class);
cfg.addClass(domain.User.class);
cfg.setProperties(System.getProperties());
cfg.configure();
SessionFactory sessions = cfg.buildSessionFactory();
sessionFactory = cfg.configure().buildSessionFactory();
return this;
}
}
Main.java (test class):
Code:
package netbeansproject;
import Database.DatabaseFactory;
import domain.*;
import java.util.List;
import java.util.Iterator;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
public class Main {
public void testUserClassAndUsers() {
System.out.println("Testing Users and UserClasses...");
Session newSession = DatabaseFactory.getInstance().getSession();
System.out.println("1 - Creating UserClasses:");
Transaction t1 = newSession.beginTransaction();
UserClass uc1 = new UserClass();
uc1.setTitle("UserClass 1");
uc1.setPermissions("XYZ");
newSession.save(uc1);
t1.commit();
System.out.println("2 - Creating Users:");
Transaction t2 = newSession.beginTransaction();
User u1 = new User();
u1.setUserName("User 1");
u1.setPassword("Password 1");
u1.setBlocked(false);
u1.setUserClass(uc1);
newSession.save(u1);
User u2 = new User();
u2.setUserName("User 2");
u2.setPassword("Password 2");
u2.setBlocked(false);
u2.setUserClass(uc1);
newSession.save(u2);
t2.commit();
System.out.println("3 - Deleting UserClass (\"UserClass 1\"):");
Transaction t3 = newSession.beginTransaction();
newSession.delete(uc1);
t3.commit();
newSession.close();
}
public static void main(String[] args) {
Main instance = new Main();
instance.testUserClassAndUsers();
}
}
SQL Script to create DB (generated by MySQL Workbench):
Code:
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
DROP SCHEMA IF EXISTS `LojaTest` ;
CREATE SCHEMA IF NOT EXISTS `LojaTest` ;
SHOW WARNINGS;
USE `LojaTest` ;
-- -----------------------------------------------------
-- Table `LojaTest`.`UserClass`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `LojaTest`.`UserClass` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `LojaTest`.`UserClass` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`title` VARCHAR(45) NOT NULL ,
`permissions` VARCHAR(16) NULL ,
PRIMARY KEY (`id`) ,
UNIQUE INDEX `id_UNIQUE` (`id` ASC) )
ENGINE = InnoDB;
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `LojaTest`.`User`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `LojaTest`.`User` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `LojaTest`.`User` (
`username` VARCHAR(10) NOT NULL ,
`password` VARCHAR(30) NOT NULL ,
`blocked` TINYINT(1) NOT NULL DEFAULT false ,
`user_class_id` INT UNSIGNED NOT NULL ,
PRIMARY KEY (`username`) ,
UNIQUE INDEX `id_UNIQUE` (`username` ASC)
)
ENGINE = InnoDB;
SHOW WARNINGS;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Note I'm not creating any foreign key... however, after I run my code, a foreign key is created in the User table referencing the UserClass table id.
Before this test, I've tried the same SQL script, however with the following options for the User table:
Code:
CONSTRAINT `fk_User_UserClass1`
FOREIGN KEY (`user_class_id` )
REFERENCES `LojaTest`.`UserClass` (`id` )
ON DELETE CASCADE
ON UPDATE NO ACTION
And
Code:
CONSTRAINT `fk_User_UserClass1`
FOREIGN KEY (`user_class_id` )
REFERENCES `LojaTest`.`UserClass` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION
None of then worked.
This is the StackTrace:
Code:
Exception in thread "main" org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:96)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:184)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1215)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:382)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:133)
at netbeansproject.Main.testUserClassAndUsers(Main.java:42)
at netbeansproject.Main.main(Main.java:55)
Caused by: java.sql.BatchUpdateException: Cannot delete or update a parent row: a foreign key constraint fails (`lojatest`.`user`, CONSTRAINT `fk_User_UserClass1` FOREIGN KEY (`user_class_id`) REFERENCES `userclass` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2020)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1451)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
... 9 more
Can anyone please help me?
I'm getting crazy! :(
I'm stuck in this issue for the last 7 days...
Have been searching all the internet for a similar issue, and I found a lot, but no solution.
Thanks very much!