Hi emmanuel! i tried to do your advice using elements but unfortunately im getting an error.here's my hql:
Query query = session.createQuery("from com.eon.cms.vo.Contact contact " +
"where :industryList = some elements(contact.industries)");
List list = new ArrayList();
list.add("1");
query.setParameterList("industryList", list);
Util.getLogger(this).debug("elements of list " +query.list());
...................
here's my class
/*
* Created on Dec 19, 2003
*
* To change the template for this generated file go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
package com.eon.cms.vo;
import java.util.Date;
import java.util.Set;
/**
* @author ryadao
*
* To change the template for this generated type comment go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
public class Contact extends Lookup {
public static final int PERSON = 1;
public static final int ORGANIZATION = 2;
private static final String PERSON_TYPE = "Person";
private static final String ORGANIZATION_TYPE = "Organization";
private String telephoneNo = "";
private String email = "";
private String address1 = "";
private String address2 = "";
private Sector sector;
private String remark = "";
private Date createdDate = null;
private Date modifiedDate = null;
private String modifiedBy = "";
private String faxNo = "";
private String mobileNo = "";
private String secretaryNo = "";
private String referredBy = "";
private int type = 0;
private Set industries = null;
private String typeName = null;
/**
* @return
*/
public String getAddress1() {
return address1;
}
/**
* @return
*/
public String getAddress2() {
return address2;
}
/**
* @return
*/
public Date getCreatedDate() {
return createdDate;
}
/**
* @return
*/
public String getEmail() {
return email;
}
/**
* @return
*/
public String getFaxNo() {
return faxNo;
}
/**
* @return
*/
public String getMobileNo() {
return mobileNo;
}
/**
* @return
*/
public Date getModifiedDate() {
return modifiedDate;
}
/**
* @return
*/
public String getReferredBy() {
return referredBy;
}
/**
* @return
*/
public String getRemark() {
return remark;
}
/**
* @return
*/
public String getSecretaryNo() {
return secretaryNo;
}
/**
* @return
*/
public Sector getSector() {
return sector;
}
/**
* @return
*/
public String getTelephoneNo() {
return telephoneNo;
}
/**
* @param string
*/
public void setAddress1(String string) {
address1 = string;
}
/**
* @param string
*/
public void setAddress2(String string) {
address2 = string;
}
/**
* @param date
*/
public void setCreatedDate(Date date) {
createdDate = date;
}
/**
* @param string
*/
public void setEmail(String string) {
email = string;
}
/**
* @param string
*/
public void setFaxNo(String string) {
faxNo = string;
}
/**
* @param string
*/
public void setMobileNo(String string) {
mobileNo = string;
}
/**
* @param date
*/
public void setModifiedDate(Date date) {
modifiedDate = date;
}
/**
* @param string
*/
public void setReferredBy(String string) {
referredBy = string;
}
/**
* @param string
*/
public void setRemark(String string) {
remark = string;
}
/**
* @param string
*/
public void setSecretaryNo(String string) {
secretaryNo = string;
}
/**
* @param sector
*/
public void setSector(Sector sector) {
this.sector = sector;
}
/**
* @param string
*/
public void setTelephoneNo(String string) {
telephoneNo = string;
}
/** Getter for property modifiedBy.
* @return Value of property modifiedBy.
*
*/
public java.lang.String getModifiedBy() {
return modifiedBy;
}
/** Setter for property modifiedBy.
* @param modifiedBy New value of property modifiedBy.
*
*/
public void setModifiedBy(java.lang.String modifiedBy) {
this.modifiedBy = modifiedBy;
}
/**
* @return
*/
/**
* @return
*/
public Set getIndustries() {
return industries;
}
/**
* @param set
*/
public void setIndustries(Set set) {
industries = set;
}
/**
* @return
*/
public int getType() {
return type;
}
/**
* @param i
*/
public void setType(int i) {
type = i;
}
/**
* @return
*/
public String getTypeName() {
return (type == PERSON)?PERSON_TYPE:ORGANIZATION_TYPE;
}
}
then my mapping
<class name="com.eon.cms.vo.Contact" table="contact">
<id name="id" type="long" unsaved-value="0">
<generator class="native"/>
</id>
<property name="name" length="200" />
<property name="telephoneNo" column="telephone_no" length="20" />
<property name="email" length="100"/>
<property name="address1" length="100"/>
<property name="address2" length="100"/>
<property name="remark" length="300"/>
<property name="createdDate" />
<property name="modifiedDate" />
<property name="modifiedBy" />
<property name="type" />
<property name="faxNo" column="fax_no" length="20" />
<property name="mobileNo" column="mobile_no" length="20" />
<property name="secretaryNo" column="secretary_no" length="20" />
<property name="referredBy" column="referred_by" length="200" />
<many-to-one name="sector" column="sector_id" cascade="none"/>
<set name="industries" table="contact_industry" cascade="none">
<key column="contact_id"/>
<many-to-many column="industry_id" class="com.eon.cms.vo.Industry" />
</set>
.......................
here's the error.
6859 [Finalizer] DEBUG net.sf.hibernate.impl.SessionImpl - running Session.finalize()
6910 [main] DEBUG net.sf.hibernate.impl.SessionImpl - find: from com.eon.cms.vo.Contact contact where :industryList0_ = some elements(contact.industries)
6920 [main] DEBUG net.sf.hibernate.engine.QueryParameters - named parameters: {industryList0_=1}
6970 [main] DEBUG net.sf.hibernate.hql.QueryTranslator - compiling query
7060 [main] DEBUG net.sf.hibernate.impl.SessionImpl - flushing session
7060 [main] DEBUG net.sf.hibernate.impl.SessionImpl - Flushing entities and processing referenced collections
7060 [main] DEBUG net.sf.hibernate.impl.SessionImpl - Processing unreferenced collections
7060 [main] DEBUG net.sf.hibernate.impl.SessionImpl - Scheduling collection removes/(re)creates/updates
7060 [main] DEBUG net.sf.hibernate.impl.SessionImpl - Flushed: 0 insertions, 0 updates, 0 deletions to 0 objects
7070 [main] DEBUG net.sf.hibernate.impl.SessionImpl - Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
7070 [main] DEBUG net.sf.hibernate.impl.SessionImpl - Dont need to execute flush
7070 [main] DEBUG net.sf.hibernate.hql.QueryTranslator - HQL: from com.eon.cms.vo.Contact contact where :industryList0_ = some elements(contact.industries)
7070 [main] DEBUG net.sf.hibernate.hql.QueryTranslator - SQL: select contact0_.id as id, case when contact0__1_.contact_id is not null then 1 when contact0__2_.contact_id is not null then 2 when contact0_.id is not null then 0 end as clazz_, contact0_.name as name11_, contact0_.telephone_no as telephon3_11_, contact0_.email as email11_, contact0_.address1 as address111_, contact0_.address2 as address211_, contact0_.remark as remark11_, contact0_.createdDate as createdD8_11_, contact0_.modifiedDate as modified9_11_, contact0_.modifiedBy as modifiedBy11_, contact0_.type as type11_, contact0_.fax_no as fax_no11_, contact0_.mobile_no as mobile_no11_, contact0_.secretary_no as secreta14_11_, contact0_.referred_by as referre15_11_, contact0_.sector_id as sector_id11_, contact0__1_.prof_id as prof_id13_, contact0__1_.first_name as first_name13_, contact0__1_.last_name as last_name13_, contact0__1_.middle_name as middle_n5_13_, contact0__1_.nick_name as nick_name13_, contact0__1_.gender as gender13_, contact0__1_.title_id as title_id13_, contact0__1_.birthdate as birthdate13_, contact0__1_.spouse_name as spouse_10_13_, contact0__2_.size as size14_, contact0__2_.annual_revenue as annual_r3_14_, contact0__2_.anniversary_date as annivers4_14_, contact0__2_.website as website14_ from contact contact0_ left outer join person contact0__1_ on contact0_.id=contact0__1_.contact_id left outer join organization contact0__2_ on contact0_.id=contact0__2_.contact_id where (?=some(select industries1_.industry_id from contact_industry industries1_ where contact0_.id=industries1_.contact_id))
7070 [main] DEBUG net.sf.hibernate.impl.BatcherImpl - about to open: 0 open PreparedStatements, 0 open ResultSets
7070 [main] DEBUG net.sf.hibernate.SQL - select contact0_.id as id, case when contact0__1_.contact_id is not null then 1 when contact0__2_.contact_id is not null then 2 when contact0_.id is not null then 0 end as clazz_, contact0_.name as name11_, contact0_.telephone_no as telephon3_11_, contact0_.email as email11_, contact0_.address1 as address111_, contact0_.address2 as address211_, contact0_.remark as remark11_, contact0_.createdDate as createdD8_11_, contact0_.modifiedDate as modified9_11_, contact0_.modifiedBy as modifiedBy11_, contact0_.type as type11_, contact0_.fax_no as fax_no11_, contact0_.mobile_no as mobile_no11_, contact0_.secretary_no as secreta14_11_, contact0_.referred_by as referre15_11_, contact0_.sector_id as sector_id11_, contact0__1_.prof_id as prof_id13_, contact0__1_.first_name as first_name13_, contact0__1_.last_name as last_name13_, contact0__1_.middle_name as middle_n5_13_, contact0__1_.nick_name as nick_name13_, contact0__1_.gender as gender13_, contact0__1_.title_id as title_id13_, contact0__1_.birthdate as birthdate13_, contact0__1_.spouse_name as spouse_10_13_, contact0__2_.size as size14_, contact0__2_.annual_revenue as annual_r3_14_, contact0__2_.anniversary_date as annivers4_14_, contact0__2_.website as website14_ from contact contact0_ left outer join person contact0__1_ on contact0_.id=contact0__1_.contact_id left outer join organization contact0__2_ on contact0_.id=contact0__2_.contact_id where (?=some(select industries1_.industry_id from contact_industry industries1_ where contact0_.id=industries1_.contact_id))
Hibernate: select contact0_.id as id, case when contact0__1_.contact_id is not null then 1 when contact0__2_.contact_id is not null then 2 when contact0_.id is not null then 0 end as clazz_, contact0_.name as name11_, contact0_.telephone_no as telephon3_11_, contact0_.email as email11_, contact0_.address1 as address111_, contact0_.address2 as address211_, contact0_.remark as remark11_, contact0_.createdDate as createdD8_11_, contact0_.modifiedDate as modified9_11_, contact0_.modifiedBy as modifiedBy11_, contact0_.type as type11_, contact0_.fax_no as fax_no11_, contact0_.mobile_no as mobile_no11_, contact0_.secretary_no as secreta14_11_, contact0_.referred_by as referre15_11_, contact0_.sector_id as sector_id11_, contact0__1_.prof_id as prof_id13_, contact0__1_.first_name as first_name13_, contact0__1_.last_name as last_name13_, contact0__1_.middle_name as middle_n5_13_, contact0__1_.nick_name as nick_name13_, contact0__1_.gender as gender13_, contact0__1_.title_id as title_id13_, contact0__1_.birthdate as birthdate13_, contact0__1_.spouse_name as spouse_10_13_, contact0__2_.size as size14_, contact0__2_.annual_revenue as annual_r3_14_, contact0__2_.anniversary_date as annivers4_14_, contact0__2_.website as website14_ from contact contact0_ left outer join person contact0__1_ on contact0_.id=contact0__1_.contact_id left outer join organization contact0__2_ on contact0_.id=contact0__2_.contact_id where (?=some(select industries1_.industry_id from contact_industry industries1_ where contact0_.id=industries1_.contact_id))
7080 [main] DEBUG net.sf.hibernate.impl.BatcherImpl - preparing statement
7090 [main] DEBUG net.sf.hibernate.type.StringType - binding '1' to parameter: 1
7120 [main] DEBUG net.sf.hibernate.loader.Loader - processing result set
7120 [main] DEBUG net.sf.hibernate.type.LongType - returning '19' as column: id
7120 [main] DEBUG net.sf.hibernate.loader.Loader - result row: 19
7120 [main] DEBUG net.sf.hibernate.type.IntegerType - returning '1' as column: clazz_
7120 [main] DEBUG net.sf.hibernate.loader.Loader - Initializing object from ResultSet: 19
7130 [main] DEBUG net.sf.hibernate.loader.Loader - Hydrating entity: com.eon.cms.vo.Person#19
7130 [main] DEBUG net.sf.hibernate.type.LongType - returning '12' as column: prof_id13_
7130 [main] DEBUG net.sf.hibernate.type.StringType - returning 'Smith' as column: first_name13_
7140 [main] DEBUG net.sf.hibernate.type.StringType - returning 'Magadia' as column: last_name13_
7140 [main] DEBUG net.sf.hibernate.type.StringType - returning 'Cuento' as column: middle_n5_13_
7140 [main] DEBUG net.sf.hibernate.type.StringType - returning 'Smith' as column: nick_name13_
7140 [main] DEBUG net.sf.hibernate.type.CharacterType - returning 'M' as column: gender13_
7140 [main] DEBUG net.sf.hibernate.type.LongType - returning '16' as column: title_id13_
7270 [main] DEBUG net.sf.hibernate.type.TimestampType - returning '04 November 1976 00:00:00' as column: birthdate13_
7270 [main] DEBUG net.sf.hibernate.type.StringType - returning '' as column: spouse_10_13_
7280 [main] DEBUG net.sf.hibernate.type.StringType - returning 'Magadia Smith Smith' as column: name11_
7280 [main] DEBUG net.sf.hibernate.type.StringType - returning '34123' as column: telephon3_11_
7280 [main] DEBUG net.sf.hibernate.type.StringType - returning
'smithmagadia@yahoo.com' as column: email11_
7280 [main] DEBUG net.sf.hibernate.type.StringType - returning 'Polaris St.' as column: address111_
7280 [main] DEBUG net.sf.hibernate.type.StringType - returning 'Bel-Air, Makati City' as column: address211_
7280 [main] DEBUG net.sf.hibernate.type.StringType - returning 'whatz up!' as column: remark11_
7290 [main] DEBUG net.sf.hibernate.type.TimestampType - returning '03 March 2004 15:28:48' as column: createdD8_11_
7290 [main] DEBUG net.sf.hibernate.type.TimestampType - returning '03 March 2004 15:28:48' as column: modified9_11_
7290 [main] DEBUG net.sf.hibernate.type.StringType - returning 'xxx' as column: modifiedBy11_
7290 [main] DEBUG net.sf.hibernate.type.IntegerType - returning '1' as column: type11_
7300 [main] DEBUG net.sf.hibernate.type.StringType - returning '453456' as column: fax_no11_
7300 [main] DEBUG net.sf.hibernate.type.StringType - returning '34234' as column: mobile_no11_
7300 [main] DEBUG net.sf.hibernate.type.StringType - returning '45345' as column: secreta14_11_
7300 [main] DEBUG net.sf.hibernate.type.StringType - returning 'Nicole Kidman' as column: referre15_11_
7300 [main] DEBUG net.sf.hibernate.type.LongType - returning '14' as column: sector_id11_
7320 [main] DEBUG net.sf.hibernate.type.LongType - returning '20' as column: id
7320 [main] DEBUG net.sf.hibernate.loader.Loader - result row: 20
7320 [main] DEBUG net.sf.hibernate.type.IntegerType - returning '1' as column: clazz_
7330 [main] DEBUG net.sf.hibernate.loader.Loader - Initializing object from ResultSet: 20
7330 [main] DEBUG net.sf.hibernate.loader.Loader - Hydrating entity: com.eon.cms.vo.Person#20
7330 [main] DEBUG net.sf.hibernate.type.LongType - returning '11' as column: prof_id13_
7330 [main] DEBUG net.sf.hibernate.type.StringType - returning 'Nicole' as column: first_name13_
7330 [main] DEBUG net.sf.hibernate.type.StringType - returning 'Kidman' as column: last_name13_
7330 [main] DEBUG net.sf.hibernate.type.StringType - returning 'ewan' as column: middle_n5_13_
7340 [main] DEBUG net.sf.hibernate.type.StringType - returning 'coly' as column: nick_name13_
7340 [main] DEBUG net.sf.hibernate.type.CharacterType - returning 'F' as column: gender13_
7340 [main] DEBUG net.sf.hibernate.type.LongType - returning '18' as column: title_id13_
7340 [main] DEBUG net.sf.hibernate.type.TimestampType - returning '03 September 1977 00:00:00' as column: birthdate13_
7340 [main] DEBUG net.sf.hibernate.type.StringType - returning '' as column: spouse_10_13_
7340 [main] DEBUG net.sf.hibernate.type.StringType - returning 'Kidman Nicole coly' as column: name11_
7350 [main] DEBUG net.sf.hibernate.type.StringType - returning '34234' as column: telephon3_11_
7350 [main] DEBUG net.sf.hibernate.type.StringType - returning
'smithmagadia@yahoo.com' as column: email11_
7350 [main] DEBUG net.sf.hibernate.type.StringType - returning 'sdfasdf' as column: address111_
7350 [main] DEBUG net.sf.hibernate.type.StringType - returning 'sdfasdf' as column: address211_
7350 [main] DEBUG net.sf.hibernate.type.StringType - returning 'dfasdf' as column: remark11_
7350 [main] DEBUG net.sf.hibernate.type.TimestampType - returning '03 March 2004 15:30:39' as column: createdD8_11_
7410 [main] DEBUG net.sf.hibernate.util.JDBCExceptionReporter - SQL Exception
Bad Timestamp Format at 23 in 2004-03-04 15:19:10.039+08
at org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:517)
at org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:675)
at net.sf.hibernate.type.TimestampType.get(TimestampType.java:22)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:62)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:53)
at net.sf.hibernate.type.AbstractType.hydrate(AbstractType.java:66)
at net.sf.hibernate.loader.Loader.hydrate(Loader.java:611)
at net.sf.hibernate.loader.Loader.loadFromResultSet(Loader.java:552)
at net.sf.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:511)
at net.sf.hibernate.loader.Loader.getRow(Loader.java:426)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:209)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:950)
at net.sf.hibernate.loader.Loader.list(Loader.java:941)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:834)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1512)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at TestTool$1.transact(TestTool.java:62)
at com.eon.common.SQLManager$Transaction.run(SQLManager.java:118)
at TestTool.testSearch(TestTool.java:67)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:392)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:276)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:167)
7420 [main] WARN net.sf.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: null