9 Replies Latest reply on Aug 25, 2009 1:22 AM by Arbi Sookazian

    Handling repetitive fields in your db schema

    Arbi Sookazian Master

      We always have the following four fields in each table for our CRUD db:


      insertDate
      insertByUserId
      updatedDate
      updatedByUserId



      So the problem is when I need to update a record, I must remember to call foo.setUpdatedDate(new Date()) prior to flushing the PC.


      Is there a way to automate this somehow for inserts and updates so these values are properly set prior to committing the tx?  Does anybody else have a similar db schema template?

        • 1. Re: Handling repetitive fields in your db schema
          Arbi Sookazian Master

          And yes, I do know about envers and Hibernate interceptors (see JPA/Hibernate book), but I believe the db architects selected this design for easy reporting.  Not sure that it's necessarily easier overall, but....

          • 2. Re: Handling repetitive fields in your db schema
            Leo van den berg Master

            Have you tried to annaotate your entities with the @PrePersist  and/or @PreUpdate annotations ? Here you can add specific values to attributes.

            • 3. Re: Handling repetitive fields in your db schema
              Arbi Sookazian Master

              Well basically it's always setting the updatedate or insertdate using new java.util.Date(); and the user is an outjected instance/class from the authenticator component.


              Is it possible to inject a Seam component into an entity class?

              • 4. Re: Handling repetitive fields in your db schema
                Leo van den berg Master

                I would stick to inject an Entity into another Entity. Something like having the currentUser.getId() etc.  But I wouldn't inject Seam-services or other components in the DomainLayer.


                • 5. Re: Handling repetitive fields in your db schema
                  Arbi Sookazian Master

                  I just tested the following successfully in one of my JPA entity classes:


                  @PrePersist
                       public void prePersist(){          
                            final LogProvider log = Logging.getLogProvider(EquipmentRecoveryStatusChangeLog.class);
                            log.info("in prePersist()");
                            
                            ApplicationUser applicationUser = (ApplicationUser)Component.getInstance(ApplicationUser.class);
                            int userId = applicationUser.getUserId();
                            String userName = applicationUser.getUserName();     
                            log.info("userId = "+userId + "; userName = "+userName);
                       }



                  I had to add the @Name annotation to the ApplicationUser entity class.  Injections have null references in entity classes.


                  For example, when I tried the following in the entity class, I got NPE:


                  @Logger Log log;



                  log.info("foo"); //<-- NPE



                  So now I'm thinking of factoring out the prePersist() method to a class that will be extended by all the JPA domain entity classes so they inherit the @PrePersist and @PreUpdate functionality.


                  This way, the developer will not need to manually set these values in the constructor or setter methods for the entity classes b/c they will happen automajically in the ancestor class.


                  Is this a good or bad practice/strategy?

                  • 6. Re: Handling repetitive fields in your db schema
                    Leo van den berg Master

                    Arbi,


                    look at the @MappedSuperClass which provides you with a sort of holder for entities. I do that for all ValueObjects, these classes have a set of basic attributes such as entryTimeStamp, lastChangeTimeStamp, code, translationKey etc.


                    If you use different inheritance strategies, you should annotate that on the subclass, otherwise you can have the same strategy for all sub-classes. This approach works better than extending normal abstract classes; you never forget your basic set of attributes.


                    I prefer NOT to do any Seamish work on EntityClasses, because I like to keep-them-clean for re-use in other projects such as in Java-Client basd programs. I have all domain-classes separeted over different JARS, to make it even more re-usable. I use a common archive with all abstract common-stuff sucha as Person, Organisation, User, Role etc. A second level contains all sepecific classes; such as our Traffic Domain objects, a third JAR contains all Entity-helper classes such as stuff for Envers. On top of that comes the Persistency-layer, EJB and MDB's and the lot.


                    It's a lot of Jars, but with the help of Maven you get a really good maintainable environment.


                    Maybe this was helpful,


                    Leo


                    • 7. Re: Handling repetitive fields in your db schema
                      Arbi Sookazian Master

                      I am getting this now:


                      Caused by: org.hibernate.PropertyValueException: not-null property references a null or transient value: com.cox.ers.entity.EquipmentRecoveryStatusChangeLog.applicationUserByUpdatedByUserId
                           at org.hibernate.engine.Nullability.checkNullability(Nullability.java:72)
                           at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:290)
                           at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:181)
                           at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:107)
                           at org.hibernate.ejb.event.EJB3PersistEventListener.saveWithGeneratedId(EJB3PersistEventListener.java:49)
                           at org.hibernate.event.def.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:131)
                           at org.hibernate.event.def.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:87)
                           at org.hibernate.event.def.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:38)
                           at org.hibernate.impl.SessionImpl.firePersist(SessionImpl.java:618)
                           at org.hibernate.impl.SessionImpl.persist(SessionImpl.java:592)
                           at org.hibernate.impl.SessionImpl.persist(SessionImpl.java:596)
                           at org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityManagerImpl.java:220)
                           ... 99 more



                      I removed the date and user fields from the constructors for the entity classes (it was working fine prior to the removal of those fields in the constructor). 


                      I added this to an existing entity class:


                      @PrePersist
                           public void prePersist(){
                                super.prePersist();
                                addedDate = super.getAddedDate();
                                applicationUserByAddedByUserId = super.getApplicationUser();          
                           }
                           
                           @PreUpdate
                           public void preUpdate(){
                                super.preUpdate();
                                addedDate = super.getUpdatedDate();
                                applicationUserByUpdatedByUserId = super.getApplicationUser();          
                           }



                      PreCRUD class (ancestor class that is extended by above entity class):


                      public class PreCRUD implements Serializable {
                           
                           private static final long serialVersionUID = -1847350574914328801L;
                           final LogProvider log = Logging.getLogProvider(PreCRUD.class);
                           
                           protected Date addedDate;     
                           protected Date updatedDate;
                           protected ApplicationUser applicationUser;     
                           protected int addedByUserId;     
                           protected int updatedByUserId;
                           
                           
                           /*******************************************BEGIN BUSINESS METHODS***********************************************************/
                           
                           public void prePersist(){                              
                                log.info("in prePersist()");
                                
                                applicationUser = (ApplicationUser)Component.getInstance(ApplicationUser.class);
                                addedByUserId = applicationUser.getUserId();          
                                addedDate = new Date();
                           }
                           
                           public void preUpdate(){
                                log.info("in preUpdate()");
                                
                                applicationUser = (ApplicationUser)Component.getInstance(ApplicationUser.class);
                                updatedByUserId = applicationUser.getUserId();          
                                updatedDate = new Date();
                           }

                      • 8. Re: Handling repetitive fields in your db schema
                        Arbi Sookazian Master

                        My approach was apparently rudimentary.


                        This article is somewhat complicated but it's similar to what I did and does use the @MappedSuperClass annotation:


                        http://www.theserverside.com/tt/articles/article.tss?l=JPAObjectModel


                        The problem delineated in this article is very similar to our domain model's.  thx.

                        • 9. Re: Handling repetitive fields in your db schema
                          Arbi Sookazian Master

                          Ok good news.  I apparently got it to work for one of my entity classes.  The article above is very complicated and does not show all the source code for brevity (and the source code download is not available).


                          In a nutshell, I ended up writing a ModelListener class, a ModelBase class, and extended the ModelBase class in my entity class.


                          I had to remove the common fields and getters/setters from my entity class.  I did not understand why the author used an @Id field in the ModelBase abstract super class.


                          Now I need to go eliminate the redundant code (instance variables and getters/setters) from my other entities and extend the ModelBase for those as well.  In my case, I did not need to use @AttributeOverrides for each entity class b/c the common columns are named the same in each table (which is not the case in the article example tables).


                          Here's my code:


                          ModelBase:


                          @MappedSuperclass
                          @EntityListeners({ModelListener.class})
                          public abstract class ModelBase {
                               
                               private ApplicationUser applicationUserByUpdatedByUserId;
                               private ApplicationUser applicationUserByAddedByUserId;
                               private Date addedDate;
                               private Date updatedDate;
                                          
                               @ManyToOne(fetch = FetchType.LAZY)
                               @JoinColumn(name = "UpdatedByUserID", nullable = false)
                               @NotNull
                               public ApplicationUser getApplicationUserByUpdatedByUserId() {
                                    return this.applicationUserByUpdatedByUserId;
                               }
                          
                               public void setApplicationUserByUpdatedByUserId(ApplicationUser applicationUserByUpdatedByUserId) {
                                    this.applicationUserByUpdatedByUserId = applicationUserByUpdatedByUserId;
                               }
                               
                               @ManyToOne(fetch = FetchType.LAZY)
                               @JoinColumn(name = "AddedByUserID", nullable = false)
                               @NotNull
                               public ApplicationUser getApplicationUserByAddedByUserId() {
                                    return this.applicationUserByAddedByUserId;
                               }
                          
                               public void setApplicationUserByAddedByUserId(ApplicationUser applicationUserByAddedByUserId) {
                                    this.applicationUserByAddedByUserId = applicationUserByAddedByUserId;
                               }
                               
                               @Temporal(TemporalType.TIMESTAMP)
                               @Column(name = "AddedDate", nullable = false, length = 23)
                               @NotNull
                               public Date getAddedDate() {
                                    return this.addedDate;
                               }
                          
                               public void setAddedDate(Date addedDate) {
                                    this.addedDate = addedDate;
                               }
                               
                               @Temporal(TemporalType.TIMESTAMP)
                               @Column(name = "UpdatedDate", nullable = false, length = 23)
                               @NotNull
                               public Date getUpdatedDate() {
                                    return this.updatedDate;
                               }
                          
                               public void setUpdatedDate(Date updatedDate) {
                                    this.updatedDate = updatedDate;
                               }
                          
                              
                          }
                          
                          



                          ModelListener:


                          public class ModelListener {
                                
                                @PrePersist
                                public void setDatesAndUserForInsert(ModelBase modelBase) {
                           
                                    // set createdBy and updatedBy User information
                                    ApplicationUser currentUser = (ApplicationUser)Component.getInstance(ApplicationUser.class);
                           
                                    // check to see if modelBase and currentUser are 
                                   // the same, if so, make currentUser modelBase.
                                   if (modelBase.equals(currentUser)) {
                                       currentUser = (ApplicationUser) modelBase;
                                   }
                          
                                   if (currentUser != null) {
                                       if (modelBase.getApplicationUserByAddedByUserId() == null) {
                                           modelBase.setApplicationUserByAddedByUserId(currentUser);
                                       }
                                       modelBase.setApplicationUserByUpdatedByUserId(currentUser);
                                   }
                          
                                   // set dateCreated and dateUpdated fields
                                   Date now = new Date();
                                   if (modelBase.getAddedDate() == null) {
                                       modelBase.setAddedDate(now);
                                   }
                                   modelBase.setUpdatedDate(now);
                               }
                                
                               @PreUpdate
                               public void setDatesAndUserForUpdate(ModelBase modelBase){
                                   // set updatedBy User information
                                   ApplicationUser currentUser = (ApplicationUser)Component.getInstance(ApplicationUser.class);
                          
                                   // check to see if modelBase and currentUser are 
                                  // the same, if so, make currentUser modelBase.
                                  if (modelBase.equals(currentUser)) {
                                      currentUser = (ApplicationUser) modelBase;
                                  }
                          
                                  if (currentUser != null) {            
                                      modelBase.setApplicationUserByUpdatedByUserId(currentUser);
                                  }
                          
                                  Date now = new Date();       
                                  modelBase.setUpdatedDate(now);
                               }
                          }



                          entity class:


                          @Entity
                          @Table(name = "EquipmentRecoveryStatusChangeLog", schema = "dbo", catalog = "EquipmentRecovery")
                          public class EquipmentRecoveryStatusChangeLog extends ModelBase implements java.io.Serializable {...}