where clause as a parameter

Question

IN converting over a legacy application we need to convert named query to nhibernate. The problem is that the where clause is being set.

here is the mapping

<resultset name="PersonSet">
<return alias="person" class="Person">
  <return-property column="id" name="Id" />
  <return-property column="ssn" name="Ssn" />
  <return-property column="last_name" name="LastName" />
  <return-property column="first_name" name="FirstName"/>
  <return-property column="middle_name" name="MiddleName" />
</return>
</returnset>

<sql-query name="PersonQuery" resultset-ref="PersonSet" read-only="true" >
  <![CDATA[
  SELECT
  person.ID as {person.Id},
  person.SSN as {person.Ssn},
  person.LAST_NAME as {person.LastName},
  person.MIDDLE_NAME as {person.MiddleName},
  person.FIRST_NAME as {person.FirstName},
  FROM PERSONS as person
  where :value
  ]]>
</sql-query>

and the c# code:

String query = "person.LAST_NAME = 'Johnson'";
HibernateTemplate.FindByNamedQueryAndNamedParam("PersonQuery", "value", querry);

the error:

where ?]; ErrorCode [<no error code>]; An expression of non-boolean type specified in a context where a condition is expected, near '@p0'.

Answer 1

I'm unfamiliar with this HibernateTemplate syntax, but it looks like you're querying the original fieldname in SQL rather than the alias. Try this:

String query = "person.LastName = 'Johnson'";

or, maybe:

String query = "[person.LastName] = 'Johnson'";

or, possibly:

String query = "{person.LastName} = 'Johnson'";

Depends what sort of pre-processing is going on before the final SQL query is sent to the server.

Answer 2

That's because :value is a bind variable in the query; you cannot simply replace it with a string that contains an arbitrary string (that would become part of the query), only with an actual value. In your case, the value is "person.LAST_NAME = 'Johnson'", which is actually a string, not a boolean value. Boolean values would be true or false, both of which are rather useless for what you try to archive.

Bind variables more-or-less replace literals, not complex expressions.

Answer 3

This doesn't work because you try to replace :value with "person.LAST_NAME = 'Johnson'" wanting that the query becomes

SELECT person.ID, person.SSN, person.LAST_NAME, person.MIDDLE_NAME, person.FIRST_NAME
FROM PERSONS as person
where person.LAST_NAME = 'Johnson'

This won't work. You can only replace the 'Johnson' part dynamically not the whole condition. Thus what really gets generated is

SELECT person.ID, person.SSN, person.LAST_NAME, person.MIDDLE_NAME, person.FIRST_NAME
FROM PERSONS as person
where 'person.LAST_NAME = \'Johnson\''

Which obviously isn't a valid condition for the WHERE-part as there is only a literal but no column and operator to compare the field to.

If you only have to match against person.LAST_NAME rewrite the xml-sql-query to

<sql-query name="PersonQuery" resultset-ref="PersonSet" read-only="true" >
  <![CDATA[
  SELECT
  ...
  FROM PERSONS as person
  where person.LAST_NAME = :value
  ]]>
</sql-query>

And in the C# code set

String query = "Johnson";

If you need to dynamically filter by different columns or even multiple columns at a time use filters. e.g. like this (i made a few assumptions on you hibernate-mapping file)

<hibernate-mapping>
  ...
  <class name="Person">
    <id name="id" type="int">
      <generator class="increment"/>
    </id>
    ...
    <filter name="ssnFilter" condition="ssn = :ssnValue"/>
    <filter name="lastNameFilter" condition="lastName = :lastNameValue"/>
    <filter name="firstNameFilter" condition="firstName = :firstNameValue"/>
    <filter name="middleNameFilter" condition="middleName = :middleNameValue"/>
  </class>
  ...
  <sql-query name="PersonQuery" resultset-ref="PersonSet" read-only="true" >
  ...
    FROM PERSONS as person
  ]]>
  </sql-query>
  <!-- note the missing WHERE clause in the PersonQuery -->
  ...
  <filter-def name="ssnFilter">
    <filter-param name="ssnValue" type="int"/>
  </filter-def>
  <filter-def name="lastNameFilter">
    <filter-param name="lastNameValue" type="string"/>
  </filter-def>
  <filter-def name="middleNameFilter">
    <filter-param name="midlleNameValue" type="string"/>
  </filter-def>
  <filter-def name="firstNameFilter">
    <filter-param name="firstNameValue" type="string"/>
  </filter-def>
</hibernate-mapping>

Now in your code you should be able to do

String lastName = "Johnson";
String firstName = "Joe";

//give me all persons first
HibernateTemplate.FindByNamedQuery("PersonQuery");

//just give me persons WHERE FIRST_NAME = "Joe" AND LAST_NAME = "Johnson"
Filter filter = HibernateTemplate.enableFilter("firstNameFilter");
filter.setParameter("firstNameValue", firstName);
filter = HibernateTemplate.enableFilter("lastNameFilter");
filter.setParameter("lastNameValue", lastName);
HibernateTemplate.FindByNamedQuery("PersonQuery");

//oh wait. Now I just want all Johnsons
HibernateTemplate.disableFilter("firstNameFilter");
HibernateTemplate.FindByNamedQuery("PersonQuery");

//now again give me all persons
HibernateTemplate.disableFilter("lastNameFilter");
HibernateTemplate.FindByNamedQuery("PersonQuery");

If you need still more dynamic queries (e.g. even changing the operator (=, !=, like, >, <, ...) or you have to combine restrictions logically (where lastname = "foo" OR firstname" = "foobar") then it's definitly time to look into the

Hibernate Criteria API