Where sentence as parameter
In converting legacy application, we need to convert the named query to nhibernate. The problem is the where clause is being set.
here display
<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 C # code:
String query = "person.LAST_NAME = 'Johnson'";
HibernateTemplate.FindByNamedQueryAndNamedParam("PersonQuery", "value", query);
Error:
Where?]; Error code []; A non-boolean expression specified in the context where the condition is expected, next to '@ p0'.
a source to share
It doesn't work because you are trying to replace :value
with "person.LAST_NAME = 'Johnson'"
so that the request becomes
SELECT person.ID, person.SSN, person.LAST_NAME, person.MIDDLE_NAME, person.FIRST_NAME
FROM PERSONS as person
where person.LAST_NAME = 'Johnson'
It won't work. You can only dynamically replace the "Johnson" part with not all state. Thus, it is indeed generated
SELECT person.ID, person.SSN, person.LAST_NAME, person.MIDDLE_NAME, person.FIRST_NAME
FROM PERSONS as person
where 'person.LAST_NAME = \'Johnson\''
This is obviously not a valid condition for the WHERE part, since there is only a literal, but no column and no operator to compare the field with.
If you only need to match with person.LAST_NAME
rewrite 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 your C # codebase
String query = "Johnson";
If you need to dynamically filter different columns or even multiple columns, use time filters. for example (I made some assumptions about the 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 even more dynamic queries (for example, even changing the operator (=,! =, Like,>, <, ...) or logically combine constraints (where lastname = "foo" OR firstname "=" foobar "), then it's definitely time to watch
a source to share
I'm not familiar with this HibernateTemplate syntax, but it looks like you are asking for the original field name in SQL, not an alias. Try the following:
String query = "person.LastName = 'Johnson'";
or maybe:
String query = "[person.LastName] = 'Johnson'";
or perhaps:
String query = "{person.LastName} = 'Johnson'";
Depends on what preprocessing goes on before the final SQL query is sent to the server.
a source to share
This is because: value is the bind variable in the request; you cannot just replace it with a string containing an arbitrary string (which will become part of the query) with only the actual value. In your case, this is the value "person.LAST_NAME =" Johnson "", which is actually a string, not a boolean value. Booleans would be true or false, both of which are useless for what you are trying to archive.
Variable variables more or less replace literals, not complex expressions.
a source to share