I have this question posted in the Hibernate Users form
Please scroll down to see my question/problem.

[b]Hibernate version:[/b]
3.0.5

[b]Mapping documents:[/b]
Mapping file:
<hibernate-mapping default-lazy="false">
<class name="EMailAddress" table="email">
<id name="id" type="int">
<column name="EMAIL_ID" not-null="true"/>
<generator class="native">
<param name="sequence">EMAIL_SEQ</param>
</generator>
</id>

<property name="addrLine" column="ADDR_LINE" type="string"/>
<property name="emailType" column="EMAIL_TYPE" type="int"/>
<property name="prefEMailAddr" column="IS_PREFFERED_EMAIL" type="boolean"/>
<property name="undeliverableInd" column="IS_UNDELIVERABLE" type="boolean"/>
<property name="solicitationInd" column="ALLOW_SOLICITATION" type="boolean"/>
<property name="attachmentInd" column="HAS_ATTACHMENT" type="boolean"/>
<property name="startDate" column="START_DATE" type="timestamp"/>
<property name="endDate" column="END_DATE" type="timestamp"/>
<property name="duplicatePartyId" column="DUPLICATE_PARTY_ID" type="long"/>

<many-to-one name="party" column="PARTY_PARTY_ID" fetch="join" class="Party" not-null="true"/>

</class>

<!-- TODO: Using duplicate partyid instead of party.id. Using party.id in the query doesnt return expected values.
Needs to be fixed -->
<query name="getEMailAddressesByParty"><![CDATA[
from EMailAddress emailAddress
where emailAddress.party.id = :id and emailAddress.party.status = 1
]]></query>
</hibernate-mapping>

POJO:
public class EMailAddress extends BaseModel
{
/**
*
*/
private static final long serialVersionUID = 3257288032683177776L;

private Integer id;

private String addrLine;

private Integer emailType;

private Boolean prefEMailAddr;

private Boolean undeliverableInd;

private Boolean solicitationInd;

private Boolean attachmentInd;

private Date startDate;

private Date endDate;

private Long duplicatePartyId;

private Party party;

//storage item - not mapped to database object
private String emailTypeCodeString;

/**
* @return Returns the addrLine.
*/
public String getAddrLine() {
return addrLine;
}

/**
* @param addrLine The addrLine to set.
*/
public void setAddrLine(String addrLine) {
this.addrLine = addrLine;
}

/**
* @return Returns the attachmentInd.
*/
public Boolean getAttachmentInd() {
return attachmentInd;
}

/**
* @param attachmentInd The attachmentInd to set.
*/
public void setAttachmentInd(Boolean attachmentInd) {
this.attachmentInd = attachmentInd;
}

/**
* @return Returns the duplicatePartyId.
*/
public Long getDuplicatePartyId() {
return duplicatePartyId;
}

/**
* @param duplicatePartyId The duplicatePartyId to set.
*/
public void setDuplicatePartyId(Long duplicatePartyId) {
this.duplicatePartyId = duplicatePartyId;
}

/**
* @return Returns the emailType.
*/
public Integer getEmailType() {
return emailType;
}

/**
* @param emailType The emailType to set.
*/
public void setEmailType(Integer emailType) {
this.emailType = emailType;
}

/**
* @return Returns the endDate.
*/
public Date getEndDate() {
return endDate;
}

/**
* @param endDate The endDate to set.
*/
public void setEndDate(Date endDate) {
this.endDate = endDate;
}

/**
* @return Returns the id.
*/
public Integer getId() {
return id;
}

/**
* @param id The id to set.
*/
public void setId(Integer id) {
this.id = id;
}

/**
* @return Returns the party.
*/
public Party getParty() {
return party;
}

/**
* @param party The party to set.
*/
public void setParty(Party party) {
this.party = party;
}

/**
* @return Returns the prefEMailAddr.
*/
public Boolean getPrefEMailAddr() {
return prefEMailAddr;
}

/**
* @param prefEMailAddr The prefEMailAddr to set.
*/
public void setPrefEMailAddr(Boolean prefEMailAddr) {
this.prefEMailAddr = prefEMailAddr;
}

/**
* @return Returns the solicitationInd.
*/
public Boolean getSolicitationInd() {
return solicitationInd;
}

/**
* @param solicitationInd The solicitationInd to set.
*/
public void setSolicitationInd(Boolean solicitationInd) {
this.solicitationInd = solicitationInd;
}

/**
* @return Returns the startDate.
*/
public Date getStartDate() {
return startDate;
}

/**
* @param startDate The startDate to set.
*/
public void setStartDate(Date startDate) {
this.startDate = startDate;
}

/**
* @return Returns the undeliverableInd.
*/
public Boolean getUndeliverableInd() {
return undeliverableInd;
}

/**
* @param undeliverableInd The undeliverableInd to set.
*/
public void setUndeliverableInd(Boolean undeliverableInd) {
this.undeliverableInd = undeliverableInd;
}

/**
* Returns the value of field 'attachmentInd'.
*
* @return the value of field 'attachmentInd'.
*/
public Boolean isAttachmentInd()
{
return this.attachmentInd;
}

/**
* Returns the value of field 'prefEMailAddr'.
*
* @return the value of field 'prefEMailAddr'.
*/
public Boolean isPrefEMailAddr()
{
return this.prefEMailAddr;
}
/**
* Returns the value of field 'solicitationInd'.
*
* @return the value of field 'solicitationInd'.
*/
public Boolean isSolicitationInd()
{
return this.solicitationInd;
}

/**
* Returns the value of field 'undeliverableInd'.
*
* @return the value of field 'undeliverableInd'.
*/
public Boolean isUndeliverableInd()
{
return this.undeliverableInd;
}

/**
* @return Returns the emailTypeCodeString.
*/
public String getEmailTypeCodeString() {
return emailTypeCodeString;
}

/**
* @param emailTypeCodeString The emailTypeCodeString to set.
*/
public void setEmailTypeCodeString(String emailTypeCodeString) {
this.emailTypeCodeString = emailTypeCodeString;
}

}

Call to the query:
public List getEMailAddresses(Party party) throws DataAccessException
{
return getHibernateTemplate().findByNamedQueryAndValueBean("getEMailAddressesByParty", party);
}


[b]Name and version of the database you are using:[/b]
MySql 4.1

[b]The generated SQL (show_sql=true):[/b]

select emailaddre0_.EMAIL_ID as EMAIL1_, emailaddre0_.ADDR_LINE as ADDR2_1_, emailaddre0_.EMAIL_TYPE as EMAIL3_1_, emailaddre0_.IS_PREFFERED_EMAIL as IS4_1_, emailaddre0_.IS_UNDELIVERABLE as IS5_1_, emailaddre0_.ALLOW_SOLICITATION as ALLOW6_1_, emailaddre0_.HAS_ATTACHMENT as HAS7_1_, emailaddre0_.START_DATE as START8_1_, emailaddre0_.END_DATE as END9_1_, emailaddre0_.DUPLICATE_PARTY_ID as DUPLICATE10_1_, emailaddre0_.PARTY_PARTY_ID as PARTY11_1_ from email emailaddre0_, party party1_ where (emailaddre0_.PARTY_PARTY_ID=?)and(party1_.STATUS=1 and emailaddre0_.PARTY_PARTY_ID=party1_.PARTY_ID)

Java 1.5.

******The Problem:

The query doesnt return any values for me when it is supposed to return some values(have some valid data in the database, for the query to return), but the generated SQL returns a valid record when i give in a value for the parameter.
I had a column 'duplicatePartyId' which is nothin but the primary key of the 'Party', i.e., a column without the mapping and changed the query to use duplicatePartyId instead of 'party.id' and the query returns the expected data.
Can someone tell me the reason for this behaviour?

Another similar observation was,

<property name="prefPhone" column="IS_PREFFERED_PHONE" type="boolean"/>

a query on this column was returning expected values to my friend running on java 1.4 but not for me until i changed the mapping file to use int instead of boolean
<property name="prefPhone" column="IS_PREFFERED_PHONE" type="int"/>

Another observation, the same code which doesnt work for me with MYSql works with oracle!!

Additional information:
I've done some more testing using the following,
foo.hbm.xml,:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping SYSTEM "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping default-lazy="false">
<class name="com.party.model.Foo" table="foo">
<id name="id" type="long">
<column name="ID" not-null="true"/>
<generator class="native">
<param name="sequence">FOO_SEQ</param>
</generator>
</id>

<property name="name" column="NAME" type="string"/>
<property name="status" column="STATUS" type="int"/>
<property name="st" column="ST" type="int"/>
</class>

</hibernate-mapping>

bar.hbm.xml:

<hibernate-mapping default-lazy="false">
<class name="com.party.model.Bar" table="bar">
<id name="id" type="int">
<column name="ID" not-null="true"/>
<generator class="native">
<param name="sequence">BAR_SEQ</param>
</generator>
</id>

<property name="name" column="NAME" type="string"/>

<many-to-one name="foo" column="BAR_ID" class="com.icentris.party.model.Foo"/>
</class>

<query name="getBarByFoo"><![CDATA[
from com.party.model.Bar bar
where (bar.foo.id = :id and bar.foo.status = 1)
]]></query>

</hibernate-mapping>


foo.java , bar.java are POJO s with getters and setters.



public List getBarByFoo(Foo foo) throws DataAccessException {
return getHibernateTemplate().findByNamedQueryAndValueBean("getBarByFoo", foo);
}

TestCase:

public void testFooBar()
{
Foo foo = new Foo();
foo.setName("1");
foo.setStatus(new Integer(1));
partyService.storeFoo(foo);
logger.info("****Foo: " + foo.toString());
Bar bar = new Bar();
bar.setName("testBar3");
bar.setFoo(foo);
partyService.storeBar(bar);
logger.info("******Bar: " + bar);
List myList = partyService.getBarByFoo(foo);
Bar bar1 = new Bar();
if (myList.iterator().hasNext())
bar1 = (Bar)myList.get(0);
logger.info("*********Bar from Query: " + bar1.toString());
this.setComplete();
}

This test case should return 'bar1' which is same as 'bar', but it returns bar1 as null.

Now, when Iam using java 1.4 instead of java 1.5 bar1 is same as bar (not null)

When i use java 1.5 with the query changed to:
<query name="getBarByFoo"><![CDATA[
from com.party.model.Bar bar
where (bar.foo.id = :id)
]]></query>

bar1 is same as bar.

The generate sql runs fine for all the cases in both java 1.4 and java 1.5,

So, its Java thatz causing the problem, i will be running some more test on this and will update you guys.

If anyone came across the same problem please share your experiences.

Thanks!!

When i change the query to:
<query name="getBarByFoo"><![CDATA[
from com.icentris.party.model.Bar bar
where (bar.foo.name = :name and bar.foo.status = 1)
]]></query>

bar1 is same as the bar(expected result).
So, when iam using java 1.5, the query doesnt work if, there is more than 1 condition in the 'where' clause. This is just my conclusion from the tests i've ran. Correct me if Iam wrong.

Another observation, the same code which doesnt work for me with MYSql works with oracle!! for both java 1.5 and java 1.4