Can anyone tell me whats wrong with the following HQL?

FROM test.UserOTK AS otk WHERE (otk.name = 'email' OR otk.name = 'xmpp') AND NOT otk.used

otk.used is a Boolean and if I leave out "AND NOT otk.used", it works.

Here is the Exception I get:

Incorrect query syntax [FROM test.UserOTK AS otk WHERE (otk.name = 'email' OR otk.name = 'xmpp') AND NOT otk.used]

you should use

AND otk.used=false

Tried that, now I get

Column not found, message from server: "Unknown column 'false' in 'where clause'"

Here is the complete SQL statement that is passed to MySQL:

select userotk0_.id as id, userotk0_.`key` as y2_, userotk0_.user_name as user_name, user otk0_.attribute as attribute, userotk0_.value as value, userotk0_.used as used, userotk0_.created_at as created_at, userotk0_.valid_until as valid_un8_, userotk0_.last_reminder_at as last_rem9_ from user_one_time_keys userotk0_ where ((userotk0_.attribute='email' )OR(usero
tk0_.attribute='xmpp' ))AND(userotk0_.used=false )

Please show the class and the mapping.

/**
* @hibernate.class table="user_one_time_keys"
* @hibernate.cache usage="nonstrict-read-write"
*/
public class UserOTK
{
private int id;
private String key;
private User user;
private String name;
private String value;
private boolean used;
private Date createdAt;
private Date validUntil;
private Date lastReminderAt;

public UserOTK()
{
}

public UserOTK(String name, String value) throws HibernateException
{
setKey(generateKey());
setName(name);
setValue(value);
setCreatedAt(new Date());
}

/**
* @hibernate.id column="id" type="integer" generator-class="identity"
*/
public int getId()
{
return id;
}
public void setId(int id)
{
this.id = id;
}

/**
* @hibernate.property column="`key`" type="string"
*/
public String getKey()
{
return key;
}
public void setKey(String key)
{
this.key = key;
}

/**
* @hibernate.many-to-one column="user_name" type="de.schildbach.user.business.User" not-null="false" outer-join="false"
*/
public User getUser()
{
return user;
}
public void setUser(User user)
{
this.user = user;
}

/**
* @hibernate.property column="attribute" type="string"
*/
public String getName()
{
return name;
}
public void setName(String name)
{
this.name = name;
}

/**
* @hibernate.property column="value" type="string"
*/
public String getValue()
{
return value;
}
public void setValue(String value)
{
this.value = value;
}

/**
* @hibernate.property column="used" type="boolean"
*/
public boolean isUsed()
{
return used;
}
public void setUsed(boolean used)
{
this.used = used;
}

/**
* @hibernate.property column="created_at" type="timestamp"
*/
public Date getCreatedAt()
{
return createdAt;
}
public void setCreatedAt(Date createdAt)
{
this.createdAt = createdAt;
}

/**
* @hibernate.property column="valid_until" type="timestamp"
*/
public Date getValidUntil()
{
return validUntil;
}
public void setValidUntil(Date validUntil)
{
this.validUntil = validUntil;
}

/**
* @hibernate.property column="last_reminder_at" type="timestamp"
*/
public Date getLastReminderAt()
{
return lastReminderAt;
}
public void setLastReminderAt(Date lastReminderAt)
{
this.lastReminderAt = lastReminderAt;
}

...
}

<hibernate-mapping>
<class
name="de.schildbach.user.business.UserOTK"
table="user_one_time_keys"
dynamic-update="false"
dynamic-insert="false"
>
<cache usage="nonstrict-read-write" />

<id
name="id"
column="id"
type="integer"
>
<generator class="identity">
</generator>
</id>

<property
name="key"
type="string"
update="true"
insert="true"
column="`key`"
/>

<many-to-one
name="user"
class="de.schildbach.user.business.User"
cascade="none"
outer-join="false"
update="true"
insert="true"
column="user_name"
not-null="false"
/>

<property
name="name"
type="string"
update="true"
insert="true"
column="attribute"
/>

<property
name="value"
type="string"
update="true"
insert="true"
column="value"
/>

<property
name="used"
type="boolean"
update="true"
insert="true"
column="used"
/>

<property
name="createdAt"
type="timestamp"
update="true"
insert="true"
column="created_at"
/>

<property
name="validUntil"
type="timestamp"
update="true"
insert="true"
column="valid_until"
/>

<property
name="lastReminderAt"
type="timestamp"
update="true"
insert="true"
column="last_reminder_at"
/>

</class>

</hibernate-mapping>

make sure you put

<property name="hibernate.query.substitutions">true 1, false 0, yes 'Y', no 'N'</property>


in your configuration XML

I usually use a named parameter and assign Boolean(true) to it...


" .... where otk.used = :used"

q.setParameter("used", new Boolean(true), Hibernate.BOOLEAN);

This is not needed really.
The problem here is "false" which is not a HQL keyword. To make it work the only thing you need is to tell Hibernate to replace it with zero. That is what configuration line I pasted above does.

This should maybe mentioned in the HQL section of the Hibernate Documentation.

Done.