I have a bit of code that I cant get to work when I use parameters but when I hard code the value the query works correctly. Below is my code. As written the result is an empty list. If I replace the '?' with a hardcoded '1' it works correctly. I verified that the dynamic value passed in is also a '1' it just doesnt seem to get set on the query. Any help?

******* CODE *******
StringBuffer select = new StringBuffer(
"SELECT person.events as event FROM PersonImpl as person");
StringBuffer where = new StringBuffer(" WHERE ");
int initLength = where.length();
int paramIndex = -1;
String joiner = " AND ";
if (person != null) {
where.append("person.id = ?" + joiner);
paramIndex++;
}
if (description != null) {
where.append("event.description = ?" + joiner);
paramIndex++;
}
int currLength = where.length();
if (currLength > initLength) {
select.append(where.toString().substring(0, currLength - joiner.length()));
}
Query query = sm.getSession().createQuery(select.toString());
if (person != null) {
// Long id = (Long)getPerson().getId();
// query.setParameter(0, id);
query.setLong(0, (Long)getPerson().getId());
}
if (description != null) {
query.setString(paramIndex, getDescription());
}
List results = query.list();
return results;
***** END CODE *****


ENVIRONMENT
Hibernate Version 3

Mysql 4.1

Drivers mysql-connector-java-3.1.8

Hibernate query log:
Hibernate: select eventimpl2_.id as id2_, eventimpl2_.firstOccurrence as firstOcc2_2_, eventimpl2_.lastOccurrence as lastOccu3_2_, eventimpl2_.reoccurrenceType as reoccurr4_2_, eventimpl2_.description as descript5_2_, eventimpl2_.person_fk as person6_2_ from person personimpl0_ inner join person_event events1_ on personimpl0_.id
=events1_.person_id inner join event eventimpl2_ on events1_.events_id=eventimpl2_.id where personimpl0_.id=?

Mysql query log:
060629 9:31:26 26 Prepare [3] select eventimpl2_.id as id2_, eventimpl2_.firstOccurrence as firstOcc2_2_, eventimpl2_.lastOccurrence as lastOccu3_2_, eventimpl2_.reoccurrenceType as reoccurr4_2_, eventimpl2_.description as descript5_2_, eventimpl2_.person_fk as person6_2_ from person personimpl0_ inner join person_event events1_ on personimpl0_.id=events1_.person_id inner join event eventimpl2_ on events1_.events_id=eventimpl2_.id where personimpl0_.id=?
26 Execute [3] select eventimpl2_.id as id2_, eventimpl2_.firstOccurrence as firstOcc2_2_, eventimpl2_.lastOccurrence as lastOccu3_2_, eventimpl2_.reoccurrenceType as reoccurr4_2_, eventimpl2_.description as descript5_2_, eventimpl2_.person_fk as person6_2_ from person personimpl0_ inner join person_event events1_ on personimpl0_.id=events1_.person_id inner join event eventimpl2_ on events1_.events_id=eventimpl2_.id where personimpl0_.id=?
26 Query commit



Thanks,
Kevin

That didnt solve it. I'm still getting the same behavior.

Though I thank you for your input thats defiantely a better way to write the query. I don't have to keep track of parameter indices that way.

K

New Code Same results:

******* CODE *******
StringBuffer select = new StringBuffer(
"SELECT person.events as event FROM PersonImpl as person");
StringBuffer where = new StringBuffer(" WHERE ");
int initLength = where.length();
String joiner = " AND ";
if (person != null) {
where.append("person.id = :personId" + joiner);
}
if (description != null) {
where.append("event.description = :description" + joiner);
}
int currLength = where.length();
if (currLength > initLength) {
select.append(where.toString().substring(0, currLength - joiner.length()));
}
Query query = sm.getSession().createQuery(select.toString());
if (person != null) {
query.setLong("personId", (Long)getPerson().getId());
}
if (description != null) {
query.setString("description", getDescription());
}
List results = query.list();
return results;

***** END CODE *****

Found the answer after some more searching on the Fourms:

http://forum.hibernate.org/viewtopic.ph ... uery+mysql

I dislike compatibility issues but its good that the forums can be helpful.

Kevin