Hibernate returning NULL entities?

Ever got NULL entity entries from Hibernate? I did.

Having a table V_MEAS_SITE with following attributes:

MEAS_SITE_ID:     NUMBER(8)
INSTALLATION_ID  NUMBER(8)
NAME_TX          VARCHAR2(10)
...

The "V_" in the table name indicates that this is a Oracle view and not a table. This name follows our naming conventions. As the name of the view indicates, the view is holding all information about measurement sites (I actually work for the national weather service). This table is mapped to the following Java class:

@Entity(table="V_MEAS_SITE")
public class MeasurementSite {

    @Id
    @Column(name="MEAS_SITE_ID")
    private int measurementSiteId;

    @Column(name="INSTALLATION_ID")
    private int installationId;

    @Column(name="NAME_TX")
    private String name;

    // getter/setter are ommited for brevity

    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((this.getMeasurementSiteId() == null) ? 0 : this.getMeasurementSiteId().hashCode());
        return result;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj) {
            return true;
        }
        if (obj == null) {
            return false;
        }
        if (!(obj instanceof MeasurementSite)) {
            return false;
        }
        final MeasurementSite other = (MeasurementSite)obj;
        if (this.getInstallationId() == null) {
            if (other.getInstallationId() != null) {
                return false;
            }
        } else if (!this.getMeasurementSiteId().equals(other.getMeasurementSiteId())) {
            return false;
        }
        return true;
    }
}

Let's write an EJB session bean reading some data:

@Session
public class MeasurementSiteService implements MeasurementSiteLocal {
    
    @PersistenceContext(unitName="DefaultEntityManager")
    private EntityManager em;

    public List<MeasurementSite> findByName(String name) {
        return em.createQuery("select m from MeasurementSite where m.name like :name")
            .setParameter("name", name)
            .list();
    }

    // used for testing
    public void setEntityManager(EntityManager em) {
        this.em = em;
    }

}

That's easy. OK, let's test this mapping with a JUnit test class:

    
public class MeasurementSiteServiceTest {
    private EntityManager em = ....; // set up the entity manager   
    
    @Test
    public void findByName() {
         final MeasurementSiteService s = new MeasurementSiteService();
         s.setEntityManager(em);

         final List<MeasurementSite> list = s.findByName("Z%");
         for (MeasurementSite m: list) {
             Assert.notNull(m); 
         }
    }
   
}

However, this test fails. There are indeed some NULL entity entries in the list. Hibernate must be broken!

Of course, Hibernate is not broken. Do you see what went wrong? I did after 3 hours of debugging. The view catches content of different tables and is therefore de-normalized. The primary key of the V_MEAS_SITE view is not MEAS_SITE_ID but INSTALLATION_ID! As a view has no explicit contraints there is no primary key constraint neither and therefore you have no explicit indications about the "logic" in the data. In this case it means that there are more than one entry for a given measurement site ID. And that's why Hibernate returned the NULL values. Of course it would have been nice, if Hibernate would have thrown some Exceptions to help a careless Java developer...

By the way, Oracle DB allows to query the DLL statement:

select dbms_metadata.get_ddl('VIEW', 'V_MEAS_SITE') as dll from dual