Hi,

I have join problem between two tables.

Cod status table is:
K CO_BANK
K CO_STATUS
DE_STATUS
MacroOutcome and Cod status table is:
K CO_BANK
K CO_MACRO_OUTCOME
K CO_STATUS
CO_STATUS_NEXT

I success to join CO_STATUS using a Set definition, but I have a problem to join CO_STATUS_NEXT, probabily because this is not a key in our table.

I can't change the table definition.
I'd like to have the MacroOutcomeStatusContact like with this fields:
private KeyFieldOutcomeStatusContact id = new KeyFieldOutcomeStatusContact();
private String bankId = new String();
private String codeMacroOutcome = new String();
private String codeStatusContact = new String();
private String codeStatusContactNext = new String();
private Set statusContactOuts = new HashSet();
private Set statusContactOutsNext = new HashSet();

Some can help me?

Hibernate version:
3.0
Mapping documents:
<hibernate-mapping package="macroOutcomeStatusContact">
<class name="MacroOutcomeStatusContact" table="OUTCOMES">
<composite-id name="id" class="KeyFieldOutcomeStatusContact">
<key-property name="bankId" type="string" column="CO_BANK"/>
<key-property name="codeStatusContact" type="string" column="CO_STATUS"/>
</composite-id>
<property name="bankId" type="string" column="CO_BANK" insert="false" update="false"/>
<property name="codeMacroOutcome" type="string" column="CO_MACRO_OUTCOME"/>
<property name="codeStatusContact" type="string" column="CO_STATUS" insert="false" update="false"/>
<property name="codeStatusContactNext" type="string" column="CO_STATUS_NEXT"/>
<set name="statusContactOuts" inverse="true" lazy="false">
<key>
<column name="CO_BANK"/>
<column name="CO_STATUS"/>
</key>
<one-to-many class="StatusContact"/>
</set>
</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
private List getQueryString(Session session, MacroOutcomeStatusContact macroOutcomeStatusContact) throws MisSevereException, XFRException
{
Map parameters = new HashMap();
boolean firstClause = true;
StringBuffer queryBuf
= new StringBuffer("from MacroOutcomeStatusContact as ch ");

if (macroOutcomeStatusContact.getBankId() != null && !"".equals(macroOutcomeStatusContact.getBankId())) {
queryBuf.append(firstClause ? " where " : " and ");
queryBuf.append("ch.bankId = :bankId");
parameters.put("bankId",macroOutcomeStatusContact.getBankId());
firstClause = false;
}
if (macroOutcomeStatusContact.getCodeMacroOutcome() != null && !"".equals(macroOutcomeStatusContact.getCodeMacroOutcome())) {
queryBuf.append(firstClause ? " where " : " and ");
queryBuf.append("ch.codeMacroOutcome = :codeMacroOutcome");
parameters.put("codeMacroOutcome",macroOutcomeStatusContact.getCodeMacroOutcome());
firstClause = false;
}
if (macroOutcomeStatusContact.getCodeStatusContact() != null && !"".equals(macroOutcomeStatusContact.getCodeStatusContact())) {
queryBuf.append(firstClause ? " where " : " and ");
queryBuf.append("ch.codeStatusContact = :codeStatusContact");
parameters.put("codeStatusContact",macroOutcomeStatusContact.getCodeStatusContact());
firstClause = false;
}
if (macroOutcomeStatusContact.getCodeStatusContactNext() != null && !"".equals(macroOutcomeStatusContact.getCodeStatusContactNext())) {
queryBuf.append(firstClause ? " where " : " and ");
queryBuf.append("ch.codeStatusContactNext = :codeStatusContactNext");
parameters.put("codeStatusContactNext",macroOutcomeStatusContact.getCodeStatusContactNext());
firstClause = false;
}

String hqlQuery = queryBuf.toString();
Query query = session.createQuery(hqlQuery);

// Set query parameter values

Iterator iter = parameters.keySet().iterator();
while (iter.hasNext()) {
String name = (String) iter.next();
Object value = parameters.get(name);
query.setParameter(name,value);
}

// Execute the query

return query.list();

Full stack trace of any exception that occurs:

Name and version of the database you are using:
DB2 9.n
The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:


Problems with Session and transaction handling?

Read this: http://hibernate.org/42.html