5 Replies Latest reply on May 12, 2010 1:35 AM by Adam Warski

    Delete entity +audit trail?

    Raoul Kaltenhäuser Newbie

      I know that it is against the base idea of the Envers Project, but I have to delete an entity plus all it's envers audit table entries.

      Is there an easy way to do that? Or do I have to write a complex sql statement?

        • 1. Re: Delete entity +audit trail?
          Adam Warski Master

          You have to write a quite simple hql statement ;)

          Just do:
          delete from full.package.name.Entity_AUD e where e.id = :id

          Adam

          1 of 1 people found this helpful
          • 2. Re: Delete entity +audit trail?
            Laurent Tonon Newbie

            Hi Adam,

             

            Thanks for your answer which is very useful but I still have a problem.

             

            When I run these lines :

             

             

            public void onPostDelete(PostDeleteEvent arg0) {
              // TODO Auto-generated method stub
              Object o = arg0.getEntity();
              if (o instanceof User){
                Session session = HibernateUtil.getSessionFactory().getCurrentSession();
                session.beginTransaction();
                User u = (User) o;
                Query q = session.createQuery("delete from com.marakana.testenvers.domain.User_AUD u where u.id = :userid");
                q.setLong("userid", u.getId());
                q.executeUpdate();
                session.getTransaction().commit();
              }
            }
            
            
            

             

            I get these lines :

            Hibernate: delete from User_AUD where (id, REV)=(?, ?)

            com.mysql.jdbc.JDBC4PreparedStatement@13e0aba: delete from User_AUD where (id, REV)=(1, ** NOT SPECIFIED **)

            and then this exception :

             

            Caused by: java.sql.SQLException: No value specified for parameter 2
            

             

            The thing is I only specify one parameter in my query, so I don't understand what's going on

             

            Thanks for your time folks

             

            Laurent.

            • 3. Re: Delete entity +audit trail?
              Adam Warski Master

              "id" is a hibernate keyword for the id of an entity, whatever the names is; in case of audit entities, the id is composite and is called "originalId". Try:

               

              delete from com.marakana.testenvers.domain.User_AUD u where u.originalId.id = :userid

               

              Adam

              • 4. Re: Delete entity +audit trail?
                Laurent Tonon Newbie

                Thank you very much Adam! That works great

                 

                I just have one last thing, is there any way to do a delete cascade for revinfo table? I mean, If I delete an entry in the audit table, I'd like to remove associated tuples in the revinfo table.

                 

                I don't really see how to do that in HQL query...

                 

                Thanks again

                • 5. Re: Delete entity +audit trail?
                  Adam Warski Master

                  Hello,

                   

                  that would be quite dangerous, as there could be other entries for the revisions in other audit tables.

                  You could, however, specify the foreign key as ON_DELETE_CASCADE (the SQL cascade) for every audit table then, when you delete a revision, you get all the audit entries deleted as well.

                   

                  Adam