3 Replies Latest reply on Jan 17, 2008 4:09 PM by brian.stansberry

    stored procedures + distrib cache  + ejb3 + hibernate

      Hi,

      I'm trying to find out if it is possible to use stored procedures + distrib cache + ejb3 + hibernate.

      Scenario:
      the SF ejb3 should call jee5 (hibernate's) persistence manager native query which should do for example update through stored procedure which I have to use in my architecture.

      My question is if the cluster (based on hibernate's 2nd level cache) will able to handle it and if this is 'cluster-safe' ...

      Sorry if I'm not clear enough in my question ... please let me know if you need more information.

      thanks a lot!

      Y

        • 1. Re: stored procedures + distrib cache  + ejb3 + hibernate
          brian.stansberry

          Probably not. If your stored procedure updates any entities that are cached in the 2nd level cache, Hibernate has no way to know about the update and thus can't invalidate those entities out of the cache. The cached entities thus contain out of date data. (Similar problem applies if you are caching query results.)

          When you execute a native SQL statement, Hibernate parses it to see what tables are affected and invalidates all entities from the 2nd level cache that are associated with those tables. But if you are executing a stored procedure, Hibernate doesn't know what will happen.

          • 2. Re: stored procedures + distrib cache  + ejb3 + hibernate

            Hi,

            thank you for reply. Just one more question - the stored procedure is called using native query - so there is no way hibernate can parse or find out what data are affected? If so then it seems that I cannot use distrib cache in my architecture at all :(

            Thanks
            Y

            • 3. Re: stored procedures + distrib cache  + ejb3 + hibernate
              brian.stansberry

              You'd be better off asking on a Hibernate forum for exact details of what Hibernate can determine from your query. But in general, if the stored procedure causes a change to happen in the database that isn't expressed in the SQL statement that you ask Hibernate to execute, Hibernate can't figure it out. It's great software but it's not magic. ;)

              BTW, this issue is not specific to a distributed 2nd level cache. The same problem exists with a non-distributed 2nd level cache.