Hi,

I have a relation many to many between users and projects:

@ManyToMany ( fetch = FetchType.EAGER )//Tipo de busqueda
@JoinTable(name="USERPROJECTS" //Tabla de intercambio
, joinColumns={@JoinColumn(name="IDUSER") //Llave foránea
}
, inverseJoinColumns={@JoinColumn(name="IDPROJECT") //Llave foránea
})
@Where( clause = "DELETIONDATE is null" )
private List<Project> projects;

and I need to create a criteria to get some users, and one of the conditions is that the user participates in one or more projects. That's how I was trying it:

criteria.add(Restrictions.in("projects", (List<Project>)projects));

and projects contains the projects that should be in the IN clausule.

But I become the next SQLException:


org.hibernate.exception.GenericJDBCException
could not execute query
SQL
select this_.id as id1_7_, this_.deletionDate as deletion2_1_7_, this_.email as email1_7_, this_.lastNames as lastNames1_7_, this_.name as name1_7_, this_.newPasswordRequested as newPassw6_1_7_, this_.IDORGANIZATION as IDORGAN10_1_7_, this_.password as password1_7_, this_.role as role1_7_, this_.userName as userName1_7_, organizati2_.id as id0_0_, organizati2_.certifications as certific3_0_0_, organizati2_.comContact as comContact0_0_, organizati2_.comEmail as comEmail0_0_, organizati2_.comPhone as comPhone0_0_, organizati2_.deletionDate as deletion7_0_0_, organizati2_.name as name0_0_, organizati2_.techContact as techCont9_0_0_, organizati2_.techEmail as techEmail0_0_, organizati2_.techPhone as techPhone0_0_, organizati2_.address as address0_0_, organizati2_.cif as cif0_0_, organizati2_.DTYPE as DTYPE0_0_, projects3_.IDUSER as IDUSER1_9_, project4_.id as IDPROJECT9_, project4_.id as id3_1_, project4_.complianceRequestingReason as complian2_3_1_, project4_.complianceResolutionReason as complian3_3_1_, project4_.deletionDate as deletion4_3_1_, project4_.description as descript5_3_1_, project4_.expedientNo as expedien6_3_1_, project4_.finishDate as finishDate3_1_, project4_.lastVersionForCompliance_id as lastVer12_3_1_, project4_.name as name3_1_, project4_.IDORGANIZATION as IDORGAN13_3_1_, project4_.IDPOLICY as IDPOLICY3_1_, project4_.startDate as startDate3_1_, project4_.state as state3_1_, project4_.tentativeFinishDate as tentati11_3_1_, version5_.id as id8_2_, version5_.classes as classes8_2_, version5_.CREATORID as CREATORID8_2_, version5_.description as descript3_8_2_, version5_.errorReport as errorRep4_8_2_, version5_.functions as functions8_2_, version5_.highSeverityErrorCount as highSeve6_8_2_, version5_.internalFileName as internal7_8_2_, version5_.javadocs as javadocs8_2_, version5_.javadocsLines as javadocs9_8_2_, version5_.lineCount as lineCount8_2_, version5_.lowSeverityErrorCount as lowSeve11_8_2_, version5_.mediumSeverityErrorCount as mediumS12_8_2_, version5_.multipleComment as multipl13_8_2_, version5_.name as name8_2_, version5_.observations as observa15_8_2_, version5_.packages as packages8_2_, version5_.PROJECTID as PROJECTID8_2_, version5_.reviewDate as reviewDate8_2_, version5_.singleComment as singleC18_8_2_, version5_.state as state8_2_, pacouser6_.id as id1_3_, pacouser6_.deletionDate as deletion2_1_3_, pacouser6_.email as email1_3_, pacouser6_.lastNames as lastNames1_3_, pacouser6_.name as name1_3_, pacouser6_.newPasswordRequested as newPassw6_1_3_, pacouser6_.IDORGANIZATION as IDORGAN10_1_3_, pacouser6_.password as password1_3_, pacouser6_.role as role1_3_, pacouser6_.userName as userName1_3_, project7_.id as id3_4_, project7_.complianceRequestingReason as complian2_3_4_, project7_.complianceResolutionReason as complian3_3_4_, project7_.deletionDate as deletion4_3_4_, project7_.description as descript5_3_4_, project7_.expedientNo as expedien6_3_4_, project7_.finishDate as finishDate3_4_, project7_.lastVersionForCompliance_id as lastVer12_3_4_, project7_.name as name3_4_, project7_.IDORGANIZATION as IDORGAN13_3_4_, project7_.IDPOLICY as IDPOLICY3_4_, project7_.startDate as startDate3_4_, project7_.state as state3_4_, project7_.tentativeFinishDate as tentati11_3_4_, organizati8_.id as id0_5_, organizati8_.certifications as certific3_0_5_, organizati8_.comContact as comContact0_5_, organizati8_.comEmail as comEmail0_5_, organizati8_.comPhone as comPhone0_5_, organizati8_.deletionDate as deletion7_0_5_, organizati8_.name as name0_5_, organizati8_.techContact as techCont9_0_5_, organizati8_.techEmail as techEmail0_5_, organizati8_.techPhone as techPhone0_5_, organizati8_.address as address0_5_, organizati8_.cif as cif0_5_, organizati8_.DTYPE as DTYPE0_5_, policy9_.id as id2_6_, policy9_.criticalViolations as critical2_2_6_, policy9_.deletionDate as deletion3_2_6_, policy9_.description as descript4_2_6_, policy9_.majorViolations as majorVio5_2_6_, policy9_.minorViolations as minorVio6_2_6_, policy9_.name as name2_6_ from PacoUser this_ left outer join Organization organizati2_ on this_.IDORGANIZATION=organizati2_.id left outer join USERPROJECTS projects3_ on this_.id=projects3_.IDUSER left outer join Project project4_ on projects3_.IDPROJECT=project4_.id and ( project4_.DELETIONDATE is null) left outer join Version version5_ on project4_.lastVersionForCompliance_id=version5_.id left outer join PacoUser pacouser6_ on version5_.CREATORID=pacouser6_.id left outer join Project project7_ on version5_.PROJECTID=project7_.id left outer join Organization organizati8_ on project7_.IDORGANIZATION=organizati8_.id left outer join Policy policy9_ on project7_.IDPOLICY=policy9_.id where this_.deletionDate is null and this_.id in (?)
errorCode
17041
java.sql.SQLException

Falta el parámetro IN o OUT en el índice:: 1 -- that means : In or OUT parameter are missing in index 1.

Can someone help me? What am I doing wrong?

Thank you very much in advance!!!

I've found the solution:

Criteria criteria = session.createCriteria(User.class);
criteria.add(Restrictions.isNull(COLUMN_DELETIONDATE));

criteria.add(Restrictions. ......);

....

criteria.createCriteria("projects", Criteria.INNER_JOIN).add(Restrictions.in("id",ids));

Being "ids" a Long[] with the ids of the projects