Results 1 to 10 of 10
  1. #1
    dennisome is offline Member
    Join Date
    Nov 2008
    Posts
    5
    Rep Power
    0

    Default check record dependency before deleting

    Hi all!

    Please help me on this...

    I would just like to create a method that checks if a record or records are being used in another table(s). We're using EJB3, JBoss Framework and MySQL in the development.

    Currently, our implementation is using a try catch. When trying to delete the record returns an exception, which is commonly about the record is being used in another table, it immediately stops the current thread and prompts the user that the record is being used in another table.

    Thanks,
    Dennis

  2. #2
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,372
    Blog Entries
    1
    Rep Power
    20

  3. #3
    dennisome is offline Member
    Join Date
    Nov 2008
    Posts
    5
    Rep Power
    0

    Default

    if you can give me, please, why not.

  4. #4
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    Setup your foreign keys properly.

  5. #5
    dennisome is offline Member
    Join Date
    Nov 2008
    Posts
    5
    Rep Power
    0

    Default

    the foreign keys was set to 'restrict' on delete. what should it be?...
    'no action'...?

  6. #6
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    No, that is correct. That means you should get an exception if you attempt to delete a record that others are dependent upon. simply catch the Exception and check that it is that exception that was raised, rather than informing the user immediately. If you want to inform the user, simply keep a list of all the "records" that caused the exception and show it to the user at the end.

    Edit: That will take less time than a possibly very large and time consuming join query with it's round trip to check the results.
    Last edited by masijade; 11-07-2008 at 09:58 AM.

  7. #7
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    And I know the try catch is what you already had, I was stating to you to kepp doing it that way, as that is the proper way. A query beforehand is a (possibly very) large amount of unneccesary overhead.

  8. #8
    dennisome is offline Member
    Join Date
    Nov 2008
    Posts
    5
    Rep Power
    0

    Default

    That is good to know, masijade. But you see, the exception error also results an error message, "Transaction has failed," that goes with the message that is normally the only message I want to show up when deletion fails. I'm using richfaces message (<rich:message>) you know.
    I believe this will be a JBoss richfaces question, but if you have any idea, please let me know... Is there a way to remove that exception message?

  9. #9
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    Well, you are already catching the exceptions, right, then adding them to this message (however you may be adding them). Well, use getErrorCode() from the SQLException and determine whether it was that exception that was thrown before adding the exception to the message.

    If you are not using try catch inside the method doing the delete (but rather just throwing the exception and letting a generic routine handle it) then change that method from:
    Java Code:
      public Whatever whateverYouCallIt(Whatever whatever) throws SQLException {
        // whatever
      }
    to
    Java Code:
      public Whatever whateverYouCallIt(Whatever whatever) throws SQLException {
        try {
          // whatever
        } catch (SQLException sqle) {
          if (sqle.getErrorCode() != whateverTheErrorCodeForTheForeignKeyConstraintIs) {
            throw sqle;
          }
        }
      }

  10. #10
    dennisome is offline Member
    Join Date
    Nov 2008
    Posts
    5
    Rep Power
    0

Similar Threads

  1. Maven Dependency Overview 1.1
    By Java Tip in forum Java Software
    Replies: 0
    Last Post: 07-02-2008, 08:13 PM
  2. Maven Dependency Overview 1.0
    By Java Tip in forum Java Software
    Replies: 0
    Last Post: 04-15-2008, 07:52 PM
  3. What are dependency injection and its advantages
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 03-29-2008, 01:36 PM
  4. What are dependency injection and its advantages
    By JavaBean in forum Java Tip
    Replies: 0
    Last Post: 09-26-2007, 09:27 PM
  5. Dependency Analyzer 1.0.3-rc0
    By levent in forum Java Software
    Replies: 0
    Last Post: 07-30-2007, 05:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •