IBatis dynamic query help

Question

I have a table called "users" with following columns userid,name,gender,department,managerid....

I wanted to do this query but was having 2 issues
1. <> in line 4 is is causing problem, which is due to to the XML. I need to use but was not able to get things going even after some permutations.
2. the check department = 'engineering' needs to be done only if the map passed contains department. so this needs to be dynamic.

Can some one scribble as to how I could get this done in Ibatis. thanks for your help !!!

select * from users
where userid=#userid# 
and gender = 'm'
and (managerid ISNULL OR managerid <> #mgrid#)
and department = 'engineering'

I tried this but did not work ....any help??

<select id="getEmployees" parameterClass="java.util.HashMap" resultMap="empResultMap">
<![CDATA[
select * from users
where userid=#userid#
and gender = 'm'
and (managerid ISNULL OR managerid <> #mgrid#)
<dynamic>
    <isNotEmpty property="mgrid">
        ( AND department = #mgrid# )
    </isNotEmpty>
</dynamic>
]]>
</select>

Answer 1

I would try it like this:

<select id="getEmployees" parameterClass="java.util.HashMap" resultMap="empResultMap">
    <![CDATA[
    select * from users
    where userid=#userid#
    and gender = 'm'
    and (managerid IS NULL OR managerid <> #mgrid#)
    ]]>
    <dynamic prepend="AND">
        <isNotEmpty property="mgrid">
            (department = #mgrid#)
        </isNotEmpty>
    </dynamic>
</select>

Please note the syntax correction from "ISNULL" to "IS NULL". Good luck!

Answer 2

Thanks Cory for the quick reply. You suggestion did work great.
Adding further to the same question, if I want to have multiple AND/OR in the dynamic tag, what should be the format.
I tried this but it did not work (especially when mgrid = "")

Once again thanks for the response.

<select id="getEmployees" parameterClass="java.util.HashMap" resultMap="empResultMap">
    <![CDATA[
    select * from users
    where userid=#userid#
    and gender = 'm'
    and (managerid IS NULL OR managerid <> #mgrid#)
    ]]>
    <dynamic prepend="AND">
        <isNotEmpty property="mgrid">
            (department = #mgrid#)
        </isNotEmpty>
        <isNotEmpty property="username" prepend="AND">
            (username = #uName#)
        </isNotEmpty>
    </dynamic>
</select>