EJB - integrity (business) rules implementation (like unique)

Discussion in 'Java' started by hab, Mar 18, 2005.

  1. hab

    hab Guest

    Hi all,
    I'm looking for a good design pattern of integrity-business rules
    implementation in J2EE/EJB. Assumption is that the rules should NOT be
    in database layer.
    I cannot find any good/detailed information of doing it in EJB in
    business layer. So this problem is trivial or noone bother with it or it
    is 'expert' knowledge which is not likely to be shared.
    Let me explain my concerns. Such simple rule as: uniqeness of column in
    table (this column is not part of primary key). So normaly I would do:
    in 'before insert to DB' I would check if object already exist, if exist
    throw error, if not 'commit'.

    But if two transactions are done in THE SAME time (creation of object
    with duplicated data) it doesn't work (when DB transaction isolataion is
    set as READ COMMITTED).
    I have feeling that there is a need for some kind of locking here, but
    how/where?
    Any good solutions for J2EE/EJB? (eventualy Session Beans + Hibernate)
    Regards,
    Hab
    hab, Mar 18, 2005
    #1
    1. Advertising

  2. hab

    Tom Dyess Guest

    "hab" <> wrote in message
    news:d1e8g7$j6i$...
    > Hi all,
    > I'm looking for a good design pattern of integrity-business rules
    > implementation in J2EE/EJB. Assumption is that the rules should NOT be in
    > database layer.
    > I cannot find any good/detailed information of doing it in EJB in business
    > layer. So this problem is trivial or noone bother with it or it is
    > 'expert' knowledge which is not likely to be shared.
    > Let me explain my concerns. Such simple rule as: uniqeness of column in
    > table (this column is not part of primary key). So normaly I would do:
    > in 'before insert to DB' I would check if object already exist, if exist
    > throw error, if not 'commit'.
    >
    > But if two transactions are done in THE SAME time (creation of object with
    > duplicated data) it doesn't work (when DB transaction isolataion is set as
    > READ COMMITTED).
    > I have feeling that there is a need for some kind of locking here, but
    > how/where?
    > Any good solutions for J2EE/EJB? (eventualy Session Beans + Hibernate)
    > Regards,
    > Hab


    Yes, the applicaiton I work on at my 9-5'er had similar issues. It's a
    scheduling system and the problem is allowing two people to schedule in the
    same slot at the same time without an overbooking if two schedulers saved it
    at the same time. Our solution was to create a lock table as such:

    create table resourcelock
    ( key_resour char(8),
    locked number(1),
    lockdate date
    )

    We then had lock methods such as this

    String query = "insert into resourcelock (key_resour, locked, lockdate)
    values (?, 1, sysdate)";
    PreparedStatement ps = connection.prepareStatement(query);
    ps.SetString(1, resKey);
    int results = results = ps.executeUpdate();
    if (results > 0) {
    // you got the lock
    }

    This was freehand typed, so don't throw it in an editor and try to compile
    it. It's just to give you an idea. The pattern I have used on several
    applications works well and is expanded to retry obtaining the lock and to
    remove stale locks. The removal of stale locks is incase an application
    halts after it has obtained a lock and before it removes the lock.

    To further example ad nausium, I use a lock on my stats table on
    OraclePower.com (shameless plug, I know). You'll have to excuse any sloppy
    anomolies you find, it was when I was first coding java, but the design
    pattern is the same (I used it extensively in Delphi) It goes something like
    this:

    protected synchronized boolean lockStatDay(java.sql.Date date) throws
    Exception {
    int tries = 0;
    int results = 0;
    java.sql.Date lockDate = new java.sql.Date(System.currentTimeMillis());
    String query = "update ora_site_stats " +
    "set hit_locked = 1, " +
    "lock_date = ? " +
    "where trunc(hit_date) = trunc(?) " +
    "and hit_locked = 0 ";
    PreparedStatement ps = conn.connection.prepareStatement(query);
    ps.setDate(1, lockDate);
    ps.setDate(2, date);
    results = ps.executeUpdate();
    while ((results == 0) && (tries <= global.lockRetries)) {
    ps.close();
    tries++;
    Thread.sleep(global.lockRetryWait);
    ps = conn.connection.prepareStatement(query);
    ps.setDate(1, lockDate);
    ps.setDate(2, date);
    results = ps.executeUpdate();
    removeStatLocks();
    }
    ps.close();
    if (results != 0) {
    conn.connection.commit();
    return true;
    }
    return false;
    }

    protected boolean unlockStatDay(java.sql.Date date) throws Exception {
    int tries = 0;
    int results = 0;
    String query = "update ora_site_stats " +
    "set hit_locked = 0, " +
    "lock_date = null " +
    "where hit_date = ?";

    PreparedStatement ps = conn.connection.prepareStatement(query);
    ps.setDate(1, date);
    do {
    results = ps.executeUpdate();
    tries++;
    } while ((results == 0) && (tries <= 5));
    ps.close();
    if (results != 0) {
    conn.connection.commit();
    return true;
    }
    return false;
    }

    public synchronized void updateStats() throws Exception {
    FlexDate lockDate = new FlexDate();
    lockDate.setToNow();
    lockDate.truncateTime();
    addStatDay();
    removeStatLocks();
    if (lockStatDay(lockDate.getSqlDate())) {
    String query = "update ora_site_stats " +
    "set hit_count = hit_count + 1 " +
    "where hit_date = ? ";
    PreparedStatement ps = conn.connection.prepareStatement(query);
    ps.setDate(1, lockDate.getSqlDate());
    int results = ps.executeUpdate();
    unlockStatDay(lockDate.getSqlDate());
    ps.close();
    }
    }

    --
    Tom Dyess
    OraclePower.com
    Tom Dyess, Mar 18, 2005
    #2
    1. Advertising

  3. hab

    hab Guest

    I think the locking can be done as Java Object as well.
    But in such solution (lock) always following problems appear:
    - deadlocks;
    - forgotten/unreleased locks

    My idea it to use somehow Entity Bean for locking - it is managed by
    the container, so above problems would not appear - but in fact I miss
    the knowledge how exactly it works.
    Hab
    hab, Mar 18, 2005
    #3
  4. hab

    Tom Dyess Guest

    "Tom Dyess" <> wrote in message
    news:FFA_d.54435$%...
    > "hab" <> wrote in message
    > news:d1e8g7$j6i$...
    >> Hi all,
    >> I'm looking for a good design pattern of integrity-business rules
    >> implementation in J2EE/EJB. Assumption is that the rules should NOT be in
    >> database layer.
    >> I cannot find any good/detailed information of doing it in EJB in
    >> business layer. So this problem is trivial or noone bother with it or it
    >> is 'expert' knowledge which is not likely to be shared.
    >> Let me explain my concerns. Such simple rule as: uniqeness of column in
    >> table (this column is not part of primary key). So normaly I would do:
    >> in 'before insert to DB' I would check if object already exist, if exist
    >> throw error, if not 'commit'.
    >>
    >> But if two transactions are done in THE SAME time (creation of object
    >> with duplicated data) it doesn't work (when DB transaction isolataion is
    >> set as READ COMMITTED).
    >> I have feeling that there is a need for some kind of locking here, but
    >> how/where?
    >> Any good solutions for J2EE/EJB? (eventualy Session Beans + Hibernate)
    >> Regards,
    >> Hab

    >
    > Yes, the applicaiton I work on at my 9-5'er had similar issues. It's a
    > scheduling system and the problem is allowing two people to schedule in
    > the same slot at the same time without an overbooking if two schedulers
    > saved it at the same time. Our solution was to create a lock table as
    > such:
    >
    > create table resourcelock
    > ( key_resour char(8),
    > locked number(1),
    > lockdate date
    > )
    >
    > We then had lock methods such as this
    >
    > String query = "insert into resourcelock (key_resour, locked, lockdate)
    > values (?, 1, sysdate)";
    > PreparedStatement ps = connection.prepareStatement(query);
    > ps.SetString(1, resKey);
    > int results = results = ps.executeUpdate();
    > if (results > 0) {
    > // you got the lock
    > }
    >
    > This was freehand typed, so don't throw it in an editor and try to compile
    > it. It's just to give you an idea. The pattern I have used on several
    > applications works well and is expanded to retry obtaining the lock and to
    > remove stale locks. The removal of stale locks is incase an application
    > halts after it has obtained a lock and before it removes the lock.
    >
    > To further example ad nausium, I use a lock on my stats table on
    > OraclePower.com (shameless plug, I know). You'll have to excuse any sloppy
    > anomolies you find, it was when I was first coding java, but the design
    > pattern is the same (I used it extensively in Delphi) It goes something
    > like this:
    >
    > protected synchronized boolean lockStatDay(java.sql.Date date) throws
    > Exception {
    > int tries = 0;
    > int results = 0;
    > java.sql.Date lockDate = new java.sql.Date(System.currentTimeMillis());
    > String query = "update ora_site_stats " +
    > "set hit_locked = 1, " +
    > "lock_date = ? " +
    > "where trunc(hit_date) = trunc(?) " +
    > "and hit_locked = 0 ";
    > PreparedStatement ps = conn.connection.prepareStatement(query);
    > ps.setDate(1, lockDate);
    > ps.setDate(2, date);
    > results = ps.executeUpdate();
    > while ((results == 0) && (tries <= global.lockRetries)) {
    > ps.close();
    > tries++;
    > Thread.sleep(global.lockRetryWait);
    > ps = conn.connection.prepareStatement(query);
    > ps.setDate(1, lockDate);
    > ps.setDate(2, date);
    > results = ps.executeUpdate();
    > removeStatLocks();
    > }
    > ps.close();
    > if (results != 0) {
    > conn.connection.commit();
    > return true;
    > }
    > return false;
    > }
    >
    > protected boolean unlockStatDay(java.sql.Date date) throws Exception {
    > int tries = 0;
    > int results = 0;
    > String query = "update ora_site_stats " +
    > "set hit_locked = 0, " +
    > "lock_date = null " +
    > "where hit_date = ?";
    >
    > PreparedStatement ps = conn.connection.prepareStatement(query);
    > ps.setDate(1, date);
    > do {
    > results = ps.executeUpdate();
    > tries++;
    > } while ((results == 0) && (tries <= 5));
    > ps.close();
    > if (results != 0) {
    > conn.connection.commit();
    > return true;
    > }
    > return false;
    > }
    >
    > public synchronized void updateStats() throws Exception {
    > FlexDate lockDate = new FlexDate();
    > lockDate.setToNow();
    > lockDate.truncateTime();
    > addStatDay();
    > removeStatLocks();
    > if (lockStatDay(lockDate.getSqlDate())) {
    > String query = "update ora_site_stats " +
    > "set hit_count = hit_count + 1 " +
    > "where hit_date = ? ";
    > PreparedStatement ps = conn.connection.prepareStatement(query);
    > ps.setDate(1, lockDate.getSqlDate());
    > int results = ps.executeUpdate();
    > unlockStatDay(lockDate.getSqlDate());
    > ps.close();
    > }
    > }
    >
    > --
    > Tom Dyess
    > OraclePower.com
    >


    The initial solution does require a unique constraint on the lock table. The
    first example was written in haste. I'll try to write something better this
    afternoon. The second example is used on the site currently and works like a
    charm.

    --
    Tom Dyess
    OraclePower.com
    Tom Dyess, Mar 18, 2005
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Paul Johnson

    Business Rules & Referential Integrity

    Paul Johnson, Nov 20, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    553
    Paul Johnson
    Nov 20, 2004
  2. Mike
    Replies:
    1
    Views:
    1,152
    Patrick TJ McPhee
    Nov 21, 2003
  3. Waleed Abdulla
    Replies:
    0
    Views:
    371
    Waleed Abdulla
    Jun 1, 2005
  4. sck10

    Using Business Rules Table

    sck10, Aug 24, 2006, in forum: ASP .Net
    Replies:
    5
    Views:
    422
    sck10
    Aug 28, 2006
  5. Raymond Schanks
    Replies:
    0
    Views:
    653
    Raymond Schanks
    Aug 3, 2010
Loading...

Share This Page