How to use NOT IN (or IN) in Ibatis/MyBatis without iterate?

Question

I've something like this:

<select id="group" parameterClass="HashMap" resultMap="group">
       SELECT *
       FROM GROUP
       WHERE ID_USER_GROUP NOT IN (
               SELECT GS.ID_USER_GROUP
               FROM GROUP_SYSTEM GS, GROUP_USER GU
               WHERE GS.ID_USER_GROUP = GU.ID_USER_GROUP
               <dynamic prepend="WHERE">            
                     <isNotEmpty prepend="AND" property="role">
                          GU.ID_ROLE = #role#
                     </isNotEmpty>                            
                     <isNotEmpty prepend="AND" property="userID">
                           GS.ID_USER = #userID#
                     </isNotEmpty>  
               </dynamic>        
               )
   </select>

but this is throwing a "missing parenthesis" or something like this. The query in Toad is working properly (without the dynamic stuff of course).

I've seen this post but I cannot understand how to merge the open/close clauses. Do I need to call the select from somewhere else and iterate through the results?

Thanks in advance!

score 0 · Answer 1 · answered Feb 08 '13 at 12:19

0

you are lacking closing parenthesis on your subquery

SELECT *
FROM GROUP
WHERE ID_USER_GROUP NOT IN 
    (
        SELECT GS.ID_USER_GROUP
        FROM GROUP_SYSTEM GS, GROUP_USER GU
        WHERE GS.ID_USER_GROUP = GU.ID_USER_GROUP
    ) -- <<=== HERE

but I'd rather use JOIN over IN

SELECT  *
FROM    GROUP a
        LEFT JOIN
        (
            SELECT GS.ID_USER_GROUP
            FROM GROUP_SYSTEM GS, GROUP_USER GU
            WHERE GS.ID_USER_GROUP = GU.ID_USER_GROUP
        ) b ON a.ID_USER_GROUP = b.ID_USER_GROUP
WHERE   b.ID_USER_GROUP IS NULL

answered Feb 08 '13 at 12:19

John Woo

258,903
69
498
492

Oh, really? I'm going to try this right now. Thanks (that's lame..), ahahaha – Enrichman Feb 08 '13 at 12:21
Hm, it's telling me "command unexpected finish" (or seomthing). Could you tell me where to put the dynamic part, please? [lunchtime! brb] – Enrichman Feb 08 '13 at 12:24

score 0 · Answer 2 · answered Feb 14 '13 at 14:43

Enable myBatis debugging and see which select statement it generates.. Usually it really helps to find mistakes.

At this sample seems that your sample will generate query like this:

   SELECT *
   FROM GROUP
   WHERE ID_USER_GROUP NOT IN (
           SELECT GS.ID_USER_GROUP
           FROM GROUP_SYSTEM GS, GROUP_USER GU
           WHERE GS.ID_USER_GROUP = GU.ID_USER_GROUP
           WHERE            
                 AND
                      GU.ID_ROLE = #role#
                 AND
                      GS.ID_USER = #userID#       
           )

Try to remove <dynamic prepend="WHERE"> block. As i remember how correctly myBatis puts prepend parameters in query is related to used version.

How to use NOT IN (or IN) in Ibatis/MyBatis without iterate?

2 Answers2