Hibernate version: 3.0

Hi

I cannot seem to work out how to use the Query Substitution! I have been searching the forum to no avail and there is nothing mentioned in Hibernate in Action.

We are working with a legacy database where boolean type values are stored as 1 character Y or N values.

I am using the yes_no type in my mapping document and that is fine.

I understand that HQL does not use the mapping document and what I have seen is the advice to use a query substitution.

But how the heck is it meant to work? Does it work?

It seems to find the substitution:

INFO org.hibernate.cfg.SettingsFactory:204 - Query language substitutions: {true=Y, false=N}

But still binds 'false' to the parameter
DEBUG org.hibernate.type.BooleanType:59 - binding 'false' to parameter: 2

Is my syntax wrong for the substitution?
<property name="hibernate.query.substitutions">true=Y,false=N</property>

I also tried
<property name="hibernate.query.substitutions">true='Y',false='N'</property>

At the moment I want to map a boolean to Y/N or if I cannot use the primitive map Boolean.TRUE=Y and Boolean.FALSE=N .... I did try these also and no go.

Help!

Thanks
Shane

Shane,

I had a similar requirement in my project, but instead of using Query substitution i used boolean variable in the pojo.

The Database type was varchar2(1) and the corresponding variable
in the pojo was Boolean java type.

In case of false the value was stored has "0" and "1" for true.

Hope it helped you.

I am constrained to using Y and N.

If I recollect correctly, JDBC generally does that conversion to 0 and 1 for you from the Boolean. If I was using 0 and 1 I would not have this problem.

Thanks
Shane

I just thought I'd add that I'm having the same problem except that I'm using T and F in my database instead of Y and N.

Like Shane, I have the substitution in my hibernate configuration:

<property name="hibernate.query.substitutions">true T, false F</property>

I also see in the log that hibernate finds the substitution:

22:52:54,750 INFO SettingsFactory:203 - Query language substitutions: {true=T, false=F}

And yet it doesn't do the substitution:

22:53:33,500 DEBUG BooleanType:79 - binding 'false' to parameter: 2

Has anyone reported this as a bug? I'm new to hibernate, so I could be missing something obvious.

Interestingly, I have the parameter mapped as a true_false type and hibernate does the conversion and saves the attribute to the database just fine. The problem seems to be in the Query.

David.

I realized it might be nice to see the actual query and how I'm setting the value.


Query query = session.createQuery(
"from FitsSubmission sub " +
"where sub.dataSet.primaryKey = :dataSetKey " +
"and sub.userInputs.dataUploaded = :uploaded");

query.setInteger("dataSetKey", dataSet.getPrimaryKey());
query.setBoolean("uploaded", dataUploaded);


Also, I'm using hibernate 3.1 RC2

I discovered that the query substitution works if the word "false" is hardcoded into the query instead of being a parameter. For example, the substitution works on:

Query query = session.createQuery(
"from FitsSubmission sub " +
"where sub.dataSet.primaryKey = :dataSetKey " +
"and sub.userInputs.dataUploaded = false");

query.setInteger("dataSetKey", dataSet.getPrimaryKey());

But it does not work on the query in my previous post where false is set using setBoolean()


I didn't find any related bugs in the bug tracker. The work-around is fairly simple though. I can use setCharacter instead of setBoolean as long as I manually convert the boolean to T or F.

Hope this helps someone in the future if they're digging around through the forums

Nate

Try this in properties file,
hibernate.query.substitutions = true 'T', false 'F'