JPA Database Access

This document outlines how database and java database access should be performed in Oscar. This does not tell you how to use these technologies, just specifically how we intend to use them in our project.

Prequisite knowledge java java annotations java generics java jpa sql and database transactions spring ioc

High level guideline

We will be following the Dao/Model object idiom for database access. We will use JPA to do our ORM. We will use Spring to manage our transactions. We will use annotations to manage the JPA and Spring configuration as much as possible, i.e. the model object configuration and the dao transaction boundaries. The objects should be loosely coupled in terms of relationship mappings, they should not be embedded.

Guideline By Example

The example we will follow is taken directly from the integrator subproject. SiteUser is basically a user that is allowed to login to the web site, i.e. a typical user/password object.

In most cases we will be creating one dao and model class per database table. We will follow the standard database table and column naming convention provided by JPA / Hibernate / OpenJpa.

Sql Table

mysql> desc SiteUser;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| disabled  | tinyint(1)  | NO   |     | NULL    |                |
| lastLogin | datetime    | YES  |     | NULL    |                |
| name      | varchar(32) | NO   | UNI | NULL    |                |
| password  | varchar(32) | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Do not put default values into the database table definition. If you want a default value, place it in the model object. Make sure you constrain “not null” or allow null as appropriate.

Java Classes

There are 2 classes you should subclass for your model and dao objects, AbstractModel.java and AbstractDao.java. For this example, we will look at SiteUser.java SiteUserDao.java and their super classes AbstracModel.java and AbstractDao.java. This example assumes you already have a basic idea of JPA / ORM's so we're only going to discuss things specific to how we're going to use them. (Some lines of code maybe removed or slightly altered to simplify the discussion, hence the missing line numbers or any minor anomalies or differences if you look at the integrator code in cvs.)

First lets look at the model objects

SiteUser.java

 1  package org.oscarehr.caisi_integrator.dao;
 2  import java.util.GregorianCalendar;
 3  import javax.persistence.Column;
 4  import javax.persistence.Entity;
 5  import javax.persistence.GeneratedValue;
 6  import javax.persistence.GenerationType;
 7  import javax.persistence.Id;
 8  import javax.persistence.Temporal;
 9  import javax.persistence.TemporalType;
10  import org.oscarehr.caisi_integrator.util.MiscUtils;
11  import org.oscarehr.caisi_integrator.util.Named;
12  @Entity
13  public class SiteUser extends AbstractModel<Integer>
14  {
15      @Id
16      @GeneratedValue(strategy = GenerationType.IDENTITY)
17      private Integer id = null;
18
19      private String name = null;
21      private String password = null;
22      @Temporal(TemporalType.TIMESTAMP)
23      private GregorianCalendar lastLogin = null;
24
25      private boolean disabled = false;
26      @Override
27      public Integer getId(){
28      
29          return(id);
30      }
31      public String getName(){
32      
33          return name;
34      }
35      public void setName(String name){
36      
37          this.name = MiscUtils.validateAndNormaliseUserName(name);
38      }
39      public String getPassword(){
40      
41          return password;
42      }
43      public void setPassword(String password){
44      
45          if (password == null) throw(new IllegalArgumentException("password can't be null"));
46          this.password = password;
47      }
48      public GregorianCalendar getLastLogin(){
49      
50          return lastLogin;
51      }
52      public void setLastLogin(GregorianCalendar lastLogin){
53      
54          this.lastLogin = lastLogin;
55      }
56      public boolean isDisabled(){
57      
58          return disabled;
59      }
60      public void setDisabled(boolean disabled){
61      
62          this.disabled = disabled;
63      }
64  }

Looking at individual lines here :

• 12 : @Entity marks this as a JPA entity. This is a standard JPA annotation. Annotations are not normally inherited so you can not annotated this is the superclass.
• 13 : The generics parameter denotes that this model object will use an Integer as it's primary key. You can specify any valid jpa primary key class here.
• 15 : Note that we are annotating the field variables, not the methods. This makes JPA use field access to access the values allowing it to by pass anything we might do in our getters and setters which is mostly input style verification.
• 15 : @Id marks which variables is the primary key variable. This is a standard JPA annotation.
• 15: @GeneratedValue specifies who populates this variable, in our example it means the database generates the id via the auto increment option. This is a standard JPA annotation.
• 17 : Note that although it's perfectly legal to use primitives like “int” as an ID, it's probably not a good idea. The reason is nuder the scene it's an Integer anyways so you're just causing a lot of auto-boxing to occur.
• 22 : @Temporal helps distinguish between date and date+time. It should always be specified for date fields. Both java.util.Date and java.util.Calendare can be persisted. I suggest using Calendar for usability reasons of the object. @Temporal is a JPA standard annotation. Take note if you have a date of some type which includes hours,minutes,etc and you persist it as a sql date without time, the data value you read out, will not match the data value you wrote in. This inherently true but it seems to be an often forgotten point when writing junit tests so it was worth mentioning.
• 27 : Note there is a getId() but no setId(), ID's should never be set after the creation of an entity instance. If an user or application defined ID is required for an object, make a constructor that requires an ID instead of making a setId() method. Do not attempt to persist a new entity by setting an existing entity's ID to null. The problem here is that the entity manager is allowed to hold a reference to entities so a conflict of persisting / merging may occur. Also entites returned from a retrieval from the DB are proxied instances, they are not the same as making a declaring a “new Object()”. The proxy is allowed to do “work” as it sees fit assuming it is an existing entity. Also note that this has really conflicting logistics once you're in a transaction as well. Also note that JPA and Hibernate support level2 caches. The L2 caches are often just HashMap's of PK->Object, If you change the PK within the object, you will throw the l2 cache out of wack as the map will not point to the right object.
• 37 & 45 : Note that I do manual data checking here. Even though name and password columns are declared not null, the error returned by persisting an object is not-friendly to parse. As an example, if both were null, you would still only receive one error, and it would be something about “db constraint xxx failed” it wouldn't actually indicate which variable you errored on and why. It would also be thrown by either the merge/persist method or possible by the framework during a commit call so you won't even know who set it wrong. You may also want to do other constraint checking like the length of a string etc.

AbstractModel.java

 1  package org.oscarehr.caisi_integrator.dao;
 2  import java.util.List;
 3  import org.apache.commons.lang.builder.ReflectionToStringBuilder;
 4  import org.oscarehr.caisi_integrator.util.MiscUtils;
 5  abstract class AbstractModel<T> implements java.io.Serializable
 6  {
 7      protected static final String OBJECT_NOT_YET_PERISTED="The object is not persisted yet, this operation requires the object to already be persisted.";
 8
 9      public abstract T getId();
10
11      @Override
12          public String toString()
13      {
14          return(ReflectionToStringBuilder.toString(this));
15      }
16      @Override
17          public int hashCode()
18      {
19          if (getId() == null)
20          {
21              MiscUtils.getLogger().warn(OBJECT_NOT_YET_PERISTED, new Exception());
22              return(super.hashCode());
23          }
24
25          return(getId().hashCode());
26      }
27
28      @Override
29      public boolean equals(Object o)
30      {
31          if (getClass()!=o.getClass()) return(false);
32          @SuppressWarnings("unchecked")
33          AbstractModel<T> abstractModel=(AbstractModel<T>)o;
34          if (getId() == null)
35          {
36              MiscUtils.getLogger().warn(OBJECT_NOT_YET_PERISTED, new Exception());
37          }
38          return(getId().equals(abstractModel.getId()));
39      }
60  }

For the most part everything here is just convenience methods so they're not duplicated everywhere, it's not actually required, just convenient. - 17 & 28 : It is important to remember that java equals(), by default is a memory location comparison. This means you can not by default use Sets or anything that relies on equality unless you override the equals method. In this case we have decided that equals means if both the type of object and it's id is equal (not necessarily it's other fields). This creates an anomaly though, if an object is not yet persisted, it has no ID, which means it will behave awkwardly in a HashSet or anything requiring equals. This is a well documented anomaly of ORM's in general. We log a warning so you know there's some code which probably doesn't make sense. It is pretty important to understands this concept.

Next we will look at the Dao objects.

SiteUserDao.java

 1  package org.oscarehr.caisi_integrator.dao;
 2  import java.util.List;
 3  import javax.persistence.Query;
 4  import org.oscarehr.caisi_integrator.util.MiscUtils;
 5  import org.springframework.stereotype.Repository;
 6  @Repository
 7  public class SiteUserDao extends AbstractDao<SiteUser>
 8  {
 9          public SiteUserDao(){
10      
11          super(SiteUser.class);
12      }
13      public SiteUser findByName(String name){
14      
15          name = MiscUtils.validateAndNormaliseUserName(name);
16          Query query = entityManager.createQuery("select x from "+modelClass.getSimpleName()+" x where x.name=?1");
17          query.setParameter(1, name);
18          return(getSingleResultOrNull(query));
19      }
20
21          public List<SiteUser> findAll(){
22      
23          Query query = entityManager.createQuery("select x from "+modelClass.getSimpleName()+" x");
24
25          @SuppressWarnings("unchecked")
26          List<SiteUser> results=query.getResultList();
27
28          return(results);
29      }
30  }

Dao objects are managed by Spring. The reason is because since they are the ones doing the database work, they must have a transaction, and we would like to use spring annotated transactions.

• 6 : @Repository is a spring specific annotation. This annotation provides us with a few things. 1) is automatic registering of the bean with spring. 2) sets this bean up as a prototype (adverse to singleton) (because the entityManager must be thread specific). 3) it marks every method in this class as requiring a transaction similar to the @Transaction annotation. There is a more in depth discussion on transactions later.
• 7 : The generic here indicates the model object this dao works with.
• 11 : You need to set the model class. This may seem redundant to line 7, and it is, but no one seems to have come up with a good work around for this in java. Basically generics does type erasure and is a compile time feature. If we want to do things at runtime, we'll have to specify the class manually again because no generics information is available anymore at runtime. We use both compile time checking and a runtime instances of the model object so we have to specify it on line 7 and 11 here.
• 13 : JPA is part of EJB3 and the replacement for it's database beans. As a result I've followed their convention of the “find” naming convention. (feel free to let me know if this has since changed, it's been a while).
• 16 : entityManager referenced here is provided by the super class. This is akin to hibernates Session. We will discuss this with regards to transactions later.
• 18 : By default JPA's query.getSingleResult() expects a result and throws an exception if there is no result. Because of this, it is not suitable to search options where the result may or may not exist, i.e. like checking if a username is already taken or not. The reason is because (in both JPA and the original hibernate) once an exception is thrown, the entityManager (or hibernate session) is no longer valid and should no longer be used. The entityManager (hibernate session) must be thrown out. If you do not, undefined behaviour may take place. This is well documented and is a critical concept to understand when working with JPA or hibernate. Because searching is such a common function, we've written our own convenience method which returns null for no entry.
• 21 : Please take note of the standard for results lists. Null is never returned. An empty list is returned for “no results”. This is a JPA standard.
• 21 : In this example we have a findAll function mainly because I was lazy. Do not put a find all method in the superclass even though it's possible to do so. It's too dangerous because inevitably some one will call the method on a large table (like a logging table) and crash the system. I only put findAll's specifically in tables / objects I know to be small, otherwise a paginated style of retrieval should be used like find(startIndex, itemsToReturn).
• 25 & 26 : Note that query.getResultList() is not genericised. This means we have to set it to the genericised result ourselves, and suppress the warning. Please do not suppress warnings on the entire method, only suppress the line that needs to be suppressed.

AbstractDao.java

 1  package org.oscarehr.caisi_integrator.dao;
 2  import java.lang.reflect.InvocationTargetException;
 3  import java.util.List;
 4  import javax.persistence.EntityManager;
 5  import javax.persistence.NonUniqueResultException;
 6  import javax.persistence.PersistenceContext;
 7  import javax.persistence.Query;
 8  import org.apache.commons.beanutils.BeanUtils;
 9  import org.springframework.transaction.annotation.Transactional;
10  @Transactional
11  abstract class AbstractDao<T extends AbstractModel<?>>{
12  
13      protected Class<T> modelClass;
14      @PersistenceContext
15      protected EntityManager entityManager = null;
16      protected AbstractDao(Class<T> modelClass){
17      
18          this.modelClass=modelClass;
19      }
20
21      /**
22       * aka update
23       */
24      public void merge(T o){
25      
26          entityManager.merge(o);
27      }
28      /**
29       * aka create
30       */
31      public void persist(T o){
32      
33          entityManager.persist(o);
34      }
35      /**
36       * You can only remove attached instances.
37       */
38      public void remove(T o){
39      
40          entityManager.remove(o);
41      }
42      /**
43           * You can only refresh attached instances.
44       */
45      public void refresh(T o){
46      
47          entityManager.refresh(o);
48      }
49
50      public T find(Object id){
51      
52          return(entityManager.find(modelClass, id));
53      }
54      public void remove(Object id){
55      
56          T abstractModel=find(id);
57          if (abstractModel!=null) remove(abstractModel);
58      }
59      protected T getSingleResultOrNull(Query query){
60      
61          @SuppressWarnings("unchecked")
62          List<T> results=query.getResultList();
63          if (results.size()==1) return(results.get(0));
64          else if (results.size()==0) return(null);
65          else throw(new NonUniqueResultException("SingleResult requested but result was not unique : "+results.size()));
66      }

75      public int getCountAll(){
76      
77          String sqlCommand="select count(*) from "+modelClass.getSimpleName();
78          Query query = entityManager.createNativeQuery(sqlCommand, Integer.class);
79          return((Integer)query.getSingleResult());
80      }
81  }

The AbstractDao class is also just a convenience class. It's not required but it will make life a lot easier.

• 10 : @Transactional. This marks all the methods in this class as needing a transaction. The reason why we have to mark it here is because annotations don't carry over between subclassing. This means even though SiteUserDao is @Repository, it only applies to those methods and not the ones writen here. We don't want to mark the AbstractDao as @repository though or it would mean there's an instance of it in spring etc. We will discussion transactions again later in this document.
• 14 : @PersistentContext is a JPA standard annotation. It tells the IOC container (spring) to inject the variable, i.e. provide this instance with it's own (not shared) entityManager instance (i.e. a hibernate session). Merge() & persist() & remove() & refresh() methods are proxied calls to the entityManager. For encapsulation reasons, we do not want anyone using the entityManager other than DAO classes. As a result we need to expose those methods. It also means that the transactions are also taken care of when those methods are called through this class. Please do not attempt to replicate your own version of the hibernate saveOrUpdate() method. In general your code path should always know whether you are creating a new object or updating and existing one. The only rare times I've wanted a saveOrUpdate() is writing a data cache but that's rare and should probably be in that specific Dao instead of the superclass.
• 52 : Note the default entityManger find method behaves differently than query.getSingleResult(), find returns null if the entry is not found.
• 59 : Here's our getSingleResultOrNull convenience method you previously saw us call from SiteUserDao.java. This makes the usage routine similar to the experience you should get with the entityManager.find(), i.e. returning null for no entry instead of an exception.
• 75 : Note that JPA is not restricted to object based access. Query.CreateNativeQuery() essentially gives you free access to run any sql you want. The advantage of putting it in this class and within the jpa query style methods is that it will be managed by the same transaction management system as the rest of the calls.

Relationships between model objects

Relationships between model objects should not be embedded. i.e. if User and Role are two top level entities, there should not be a User.getRoles() method. Instead you should have a RoleDao.findRolesByUserId() method. Note that the Dao class should always return items of it's own type. i.e. you should not have a UserDao.findRolesByUser(). If it's a one-to-many relationship you should still separate the 2 entities. i.e. User to PetDogs, PetDog should have a OwnerId. So to retrieve a PetDogs owner you would do UserDao.find(petDog.getOwnerId()), where as to find a users PetDogs you would do PetDogDao.findByUserId(user.getId()). There are memory, CPU, and coupling implications of using embedded v.s. Non embedded objects. The simplest and most cpu and memory friendly way is to use loosely coupled model objects. Especially when transactions are in use, a lot of people get confused when different objects which are embedded are either saved when not expected or saved in a different order causing foreign key errors etc. The price of doing so is that the code is a little more verbose and relationships need to be manually maintained. The conceptual model is simpler and less error prone though.

Conclusion

This concludes the overview of how we intend to use JPA + Spring + annotated configuration. Note that no xml is required for Dao objects (there is some xml to confgure the original spring + JPA framework but there's no need for xml entries for each class). Keep model objects loosely coupled and not embedded.

Important note on EntityManager (and hibernate sessions) and transactions.

EntityManagers are like JDBC connections. They usually directly hold jdbc connections. If you ever create or get an entityManager (or session), you must make sure you close or return the entity manager – just like you must do with JDBC connections, i.e. in a try/finally block.

The @Transaction tag effectively takes care of the entityManager creation and destruction for you and forces the transaction into a block style (similar to the java key word synchronized v.s. ReentrantLock styles). The transaction tag on it's default setting effectively says “before running the method, if I'm in a transaction, join the existing one, if there is none, create me a new one”. After the method it will close the transaction if it created it. If an exception is thrown, it will roll back the transaction if it created it.

The idiom used with @Repository and @transaction in the Dao objects effectively mean that all our data accesses methods are in their own transactions, i.e. non transactional. This doesn't preclude us from using transactions if we choose to do so though. The @Transaction tag is a spring tag and can be used on any spring managed bean. So as an example lets say you had 2 classes :

public class UserDao{
...
}

public class UserImageDao{
...
}

Lets say you were trying to update a user and their image at the same time

public void updateUserAndImage(int userId, String newUserName, byte[] newImage){
        User user=userDao.find(userId);
        user.setUserName(newUserName);
        userDao.merge(user);

        UserImage userImage=userImageDao.find(userId);
        userImage.setBytes(newImage);
        userImageDao.merge(userImage);
}

In the above example 4 transactions actually took place. 1.userDao.find() 2.userDao.persist() 3.userImageDao.find() 4.userImageDao.persist()

For efficiency purposes or for transactional purposes we could do

@Transactional
public void updateUserAndImage(int userId, String newUserName, byte[] newImage){
        User user=userDao.find(userId);
        user.setUserName(newUserName);
        UserImage userImage=userImageDao.find(userId);
        userImage.setBytes(newImage);
}

The above would start a transaction at the beginning of the updateUserAndImage() method, then the 4 dao calls would join the transaction instead of making their own, then after the updateUserAndImage() method the transaction would close. There are logistical differences in this. If you are in a single transaction and the “setBytes()” fails, it means the “setUserName()” is rolled back too. You need to understand this.

In the above example, also notice that I no longer called “merge()”. This is because the JPA spec states (and was also true for hibernate sessions), that if a transaction is open, any change made to the objects which are participating in the transaction, will be committed to the database upon completion of the transaction. This is also critical to understand. You can call merge on an open transaction but it has no effect, merge does not commit() a transaction. This is commonly referred to as “attached” and “detached” instances of objects. Detached refers to objects which are no longer participating in a transaction and therefore to save changes you must call merge(). Attached refers to objects which are still in a transaction so what ever changes you make will sooner or later find their way back to the database tables – assuming no rollback is done. You must always cognizant about the attached and detached state of your objects.

The concept of a running transaction also ties back to the query.getSingleResult() call throwing an exception. If you do make a query which uses getSingleResult(), if it fails it will invalidate and rollback the entire transaction. This means previous update calls can be rolled back due to a read call, not just other update calls. This is true for any JPA (and hibernate) call. If Any exception is ever thrown from the javax.persistence.* or org.hibernate.* tree, you must assume the entityManager or hibernate session is now invalid. The @Transaction tag will roll back the transaction for you if the exception reaches the transaction boundary. If you catch and squelch the exception within a transaction though, you must not continue to do database work in the entityManager. Generally speaking if you find yourself in this state, it means the logical flow of your code is not very clean.

Immutable Objects

Sometimes some objects should either never be deleted, or never be updated once created. ' Examples of this maybe Log entries. To do this, in your model object do the following :

@PreRemove
protected void jpaPreventDelete(){
    throw(new UnsupportedOperationException("Remove is not allowed for this type of item."));
}

@PreUpdate
protected void jpaPreventUpdate(){
    throw(new UnsupportedOperationException("Update is not allowed for this type of item."));
}

The @PreRemove and @PreUpdate tags are JPA standard tags; however, they are not always well defined. There are rumours that Hibernate and OpenJpa act slightly differently. The rumour is that OpenJpa calls @PrePersist only on persists, and @PreUpdate only on updates where Hibernate calls @PrePersist, and @PreUpdate on a persist and @PreUpdate and @PreRemove on removes. Apparently it's not well defined in the JPA spec. The above example works in OpenJpa but it should be tested on Hibernate before usage as I haven't verified that myself and or the behaviour may have been corrected.