-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: Cant cascade delete on Many-To-One relation (MySQL)
PostPosted: Wed Jul 28, 2010 12:02 am 
Newbie

Joined: Tue Jul 27, 2010 11:34 pm
Posts: 1
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!


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.