Hibernate version: 5.045

Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="com.ehe.beans">
<class name="EMRPatient" table="patient_info" lazy="false">
<id
name="patientID"
column="patientID">
<generator class="assigned"/>
</id>
...
<property
name="state"
column="state"
type="com.ehe.test.framework.EncryptedStringType"
/>
</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
StringBuilder queryStr = new StringBuilder();

// The below HQL returns the CallQDetails object and also the operator Object
String currentDate = EMRDateUtils.getCurrentDate(EMRDateUtils.SQL_DATE_FORMAT);
queryStr.append("select d,pat ");
queryStr.append("from CallQOperator oprs, CallQDetails d, CallQPatient p, PatientCallQInfo i, PatientProcedureEligibility e, ClientContactPrefs cp, EMRPatient pat, StateTimezoneMap st ");
queryStr.append("where d.active=1 and ("+EMRSQLUtil.buildDateBetween("d.unFormattedStartDate", "d.unFormattedStopDate", currentDate)+") and ");
queryStr.append("pat.is_eligible=1 and pat.epms_pid=p.epmsPId and pat.epms_pid=i.epmsPId and pat.epms_pid=e.epmsPId and " +
"(oprs.callTimeZoneId=0 or oprs.callTimeZoneId=st.timezoneId) and " +
" (pat.state=st.stateAbbr or pat.state=st.state ) ");
queryStr.append("and p.clientId=cp.clientId and e.epmsPId=p.epmsPId and d.id=p.callQId and d.id=oprs.callQId and e.procedureId=d.procedureId and i.epmsPId=p.epmsPId and ");
queryStr.append("cp.phoneContactAllowed=1 and (cp.maxCallsAllowed=0 or i.successCallCount!=cp.maxCallsAllowed) and (cp.maxFailureCallsAllowed=0 or i.failureCallCount!=cp.maxFailureCallsAllowed) and oprs.operatorId="+adminID+" and i.contactPreference not in ("+PatientConstants.DONT_CALL_ME+","+PatientConstants.DONT_CALL_BAD_TELEPHONE+") and ");
queryStr.append("("+EMRSQLUtil.buildDateBetween("oprs.unFormattedStartDate", "oprs.unFormattedStopDate", currentDate)+") ");
queryStr.append("and (i.nextCallDateTime='"+EMRDateUtils.SQL_DEFAULT_DATE+"' or i.nextCallDateTime<'"+EMRDateUtils.getCurrentDate(EMRDateUtils.SQL_DATE_TIME24_FORMAT)+"') ");
queryStr.append("and p.apptBooked=0 ");
queryStr.append("and p.epmsPId not in " +
"(select lock.epmsPId from PatientRecordLock lock where " +
"lock.lockedByUser!=oprs.operatorId and lock.lockedByApp in "+
"("+EMRAppConstants.SCHEDULING+","+EMRAppConstants.CALL_QUEUE+")) ");
queryStr.append(" order by e.nextEligibleDate desc ");
Query query = session.createQuery(queryStr.toString());

Question:
My questions is how can I perform the join and involve my usertype too? In other words, the 'pat.state' column is of my special encrypted type and the 'st.state' column is a plain string.
I want to have my UserType class decrypt the value in the join by 'decorating' the sql string like ... aes_decrypt(pat.state, 'key phrase')=st.stateAbbr or aes_decrypt(pat.state, 'key phrase')=st.state

Any help would be appreciated.
Thanks.

It would be nice of the property mapping class did something like this ...

class : AbstractPropertyMapping
public String[] toColumns(String alias, String propertyName) throws QueryException {
//TODO: *two* hashmap lookups here is one too many...
String[] columns = (String[]) columnsByPropertyPath.get(propertyName);
if ( columns == null ) {
throw propertyException( propertyName );
}
String[] templates = (String[]) formulaTemplatesByPropertyPath.get(propertyName);
String[] result = new String[columns.length];
for ( int i=0; i<columns.length; i++ ) {
if ( columns[i]==null ) {
result[i] = StringHelper.replace( templates[i], Template.TEMPLATE, alias );
}
else {
String qualifiedString = StringHelper.qualify( alias, columns[i] );
Type type = typesByPropertyPath.get(propertyName);
result[i] = type.toColumnString(qualifiedString);
}
}
return result;
}


This would allow the type to decorate the sql string representation making it particularly useful for joins where one or both of the columns if of a custom type.