StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHibernate Entities from Multiple Databases
text
Body
copied!<p>Our data model is separated into schemas on two databases. The schemas are used in isolation except for a few single-key relationships that bridge between the two. There are no write transactions that will span both databases.</p> <p>Similar to this question <a href="https://stackoverflow.com/questions/3552330">Doing a join over 2 tables in different databases using Hibernate</a>, we want to use Hibernate to handle joining the entities. We cannot use the database solution (Federated views on DB2).</p> <p>We have set up Hibernate with two separate database configurations (Doctor and Patient), which works perfectly when using DAOs to explicitly access a particular session.</p> <p>We want to use Hibernate to automatically retrieve the entity when we call <code>DoctorBO.getExam().getPatient()</code> Where examination contains an id pointing to the Patient table on the other database.</p> <p>One way I've tried doing this is using a custom UserType:</p> <pre><code>public class DistributedUserType implements UserType, ParameterizedType { public static final String CLASS = "CLASS"; public static final String SESSION = "SESSION"; private Class<? extends DistributedEntity> returnedClass; private String session; /** {@inheritDoc} */ @Override public int[] sqlTypes() { // The column will only be the id return new int[] { java.sql.Types.BIGINT }; } /** {@inheritDoc} */ @Override public Class<? extends DistributedEntity> returnedClass() { // Set by typedef parameter return returnedClass; } /** {@inheritDoc} */ @Override public boolean equals(Object x, Object y) throws HibernateException { if (x == y) { return true; } if ((x == null) || (y == null)) { return false; } Long xId = ((DistributedEntity) x).getId(); Long yId = ((DistributedEntity) y).getId(); if (xId.equals(yId)) { return true; } else { return false; } } /** {@inheritDoc} */ @Override public int hashCode(Object x) throws HibernateException { assert (x != null); return x.hashCode(); } /** {@inheritDoc} */ @Override public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException { Long id = rs.getLong(names[0]); return HibernateUtils.getSession(session).get(returnedClass, id); } /** {@inheritDoc} */ @Override public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException { DistributedEntity de = (DistributedEntity) value; st.setLong(index, de.getId()); } /** {@inheritDoc} */ @Override public Object deepCopy(Object value) throws HibernateException { return value; } /** {@inheritDoc} */ @Override public boolean isMutable() { return false; } /** {@inheritDoc} */ @Override public Serializable disassemble(Object value) throws HibernateException { return (Serializable) value; } /** {@inheritDoc} */ @Override public Object assemble(Serializable cached, Object owner) throws HibernateException { return cached; } /** {@inheritDoc} */ @Override public Object replace(Object original, Object target, Object owner) throws HibernateException { return original; } /** {@inheritDoc} */ @Override public void setParameterValues(Properties parameters) { String clazz = (String) parameters.get(CLASS); try { returnedClass = ReflectHelper.classForName(clazz); } catch (ClassNotFoundException e) { throw new IllegalArgumentException("Class: " + clazz + " is not a known class type."); } session = (String) parameters.get(SESSION); } } </code></pre> <p>Which would then be used:</p> <pre><code>@TypeDef(name = "testUserType", typeClass = DistributedUserType.class, parameters = { @Parameter(name = DistributedUserType.CLASS, value = PatientBO.CLASSNAME), @Parameter(name = DistributedUserType.SESSION, value = HibernateUtils.PATIENT_SESS) }) @Type(type = "testUserType") @Column(name = "PATIENT_ID") private PatientBO patient; </code></pre> <p>The UserType works - the data is loaded correctly with only the Id of the field persisted to the database. I have tested very simple examples of <code>doctor.getExam().getPatient()</code> and <code>doctor.getExam().setPatient()</code> and both seem to work great, however I think this is a terrible hack and I do not have adequate knowledge of Hibernate to know if this is safe to use.</p> <p>Is there a better way to achieve what we want? Is the way I've described here adequate, or will it cause difficulties in the future?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload