{"id":130,"date":"2016-03-16T23:49:14","date_gmt":"2016-03-16T15:49:14","guid":{"rendered":"http:\/\/www.chenweikang.top\/?p=130"},"modified":"2016-03-21T22:06:03","modified_gmt":"2016-03-21T14:06:03","slug":"mybatis%e5%ad%a6%e4%b9%a0%e4%b9%8b%e8%b7%af-%e5%8a%a8%e6%80%81sql%e6%9f%a5%e8%af%a2","status":"publish","type":"post","link":"https:\/\/www.chenweikang.top\/?p=130","title":{"rendered":"Mybatis\u5b66\u4e60\u4e4b\u8def\u2014\u52a8\u6001SQL\u67e5\u8be2"},"content":{"rendered":"

mybatis\u4e2d \u4f7f\u7528 \u52a8\u6001SQL\uff1aif \/ where \/when\/otherwise\/trim \u7b49\u6761\u4ef6\u914d\u5408\u4f7f\u7528\uff0c\u5b9e\u73b0\u5f3a\u5927\u7684\u52a8\u6001SQL<\/p>\n

\u5b9a\u4e49\u4e00\u4e2a\u7ed3\u679c\u96c6<\/p>\n

<resultMap type=\"Employee\" id=\"empRes\"><\/p>\n

<id property=\"id\" column=\"id\" \/><\/p>\n

<result property=\"username\" column=\"username\" \/><\/p>\n

<result property=\"salary\" column=\"salary\" \/><\/p>\n

<\/resultMap><\/p>\n

parameterType=\"Map\" \u53c2\u6570\u7c7b\u578b\u4e3aMap<\/p>\n

resultMap=\"empRes\" \u4f7f\u7528\u5df2\u7ecf\u5b9a\u4e49\u7ed3\u679c\u96c6<\/p><\/blockquote>\n

\"3\"<\/p>\n

1. \u4f7f\u7528 if \u6761\u4ef6<\/h2>\n

<!-- \u52a8\u6001Sql if\u6761\u4ef6--><\/p>\n

<select id=\"searchEmp\" parameterType=\"Map\" resultMap=\"empRes\"><\/p>\n

select * from \u00a0employee\u00a0where \u00a0deptID=#{deptID}<\/p>\n

<!-- \u52a8\u6001\u67e5\u8be2 \u5224\u65ad\u6761\u4ef6\u62fc\u63a5 --><\/p>\n

<if test=\"username!=null\"><\/p>\n

and username like #{username}<\/p>\n

<\/if><\/p>\n

<if test=\"salary!=null\"><\/p>\n

and salary=#{salary}<\/p>\n

<\/if>
\n<\/select><\/p>\n<\/blockquote>\n

2. \u4f7f\u7528 choose\/when\/otherwise<\/h2>\n

<!-- \u52a8\u6001SQL choose\/when -->
\n<select id=\"searchEmpByType\" parameterType=\"Map\" resultMap=\"empRes\" ><\/p>\n

select * from employee<\/p>\n

<choose><\/p>\n

<when test=\"searchBy=='deptID' \"><\/p>\n

where deptID=#{deptID}<\/p>\n

<\/when><\/p>\n

<when test=\"searchBy=='username' \"><\/p>\n

where username like #{username}<\/p>\n

<\/when><\/p>\n

<when test=\"searchBy=='salary' \"><\/p>\n

where salary=#{salary}<\/p>\n

<\/when><\/p>\n

<\/choose>
\n<\/select><\/p>\n<\/blockquote>\n

3. \u4f7f\u7528 where\/if \u5982\u679c\u53e5\u5b50\u4ee5and\u6216or\u5f00\u5934\u5c06\u81ea\u52a8\u5220\u9664 \u5bb9\u9519<\/h2>\n

<select id=\"searchEmp2\" parameterType=\"Map\" resultMap=\"empRes\" ><\/p>\n

select * from employee<\/p>\n

<where><\/p>\n

<!-- where\u5c06\u81ea\u52a8\u6dfb\u52a0\u6216\u5220\u9664and --><\/p>\n

<if test=\"deptID!=null\"><\/p>\n

deptID=#{deptID}<\/p>\n

<\/if><\/p>\n

<if test=\"username!=null\"><\/p>\n

and username like #{username}<\/p>\n

<\/if><\/p>\n

<if test=\"salary!=null\"><\/p>\n

and salary=#{salary}<\/p>\n

<\/if><\/p>\n

<\/where>
\n<\/select><\/p><\/blockquote>\n

4. \u4f7f\u7528trim\u6761\u4ef6 \u63d0\u4f9b\u524d\u540e\u7f00\u529f\u80fd \u66f4\u52a0\u7075\u6d3b<\/h2>\n

<select id=\"searchEmp3\" parameterType=\"Map\" resultMap=\"empRes\" ><\/p>\n

select * from employee
\n<!--<\/p>\n

prefix=\"where\" \uff1a\u6dfb\u52a0\u524d\u7f00where<\/p>\n

prefixOverrides=\"and | or\" \u81ea\u52a8\u8986\u76d6and | or<\/p>\n

\u529f\u80fd\u548c\u4e0a\u8fb9where\u76f8\u540c
\n-->
\n<trim prefix=\"where\" prefixOverrides=\"and | or\"><\/p>\n

<if test=\"deptID!=null\"><\/p>\n

deptID=#{deptID}<\/p>\n

<\/if><\/p>\n

<if test=\"username!=null\"><\/p>\n

and username like #{username}<\/p>\n

<\/if><\/p>\n

<if test=\"salary!=null\"><\/p>\n

and salary=#{salary}<\/p>\n

<\/if>
\n<\/trim>
\n<\/select><\/p><\/blockquote>\n

 <\/p>\n

5. foreach\u5faa\u73af<\/h2>\n

<!-- \u6d4b\u8bd5foreach\u6761\u4ef6
\nitem=\"deptID\" \u904d\u5386\u7684\u53d6\u503c
\nopen=\"(\" \u5f00\u59cb\u52a0 (
\nseparator=\",\" \u9017\u53f7\u5206\u9694
\nclose=\")\" \u7ed3\u675f\u52a0 )
\n\u6700\u7ec8 \u5b9e\u73b0 (2,3,4,5,6 )
\n-->
\n<select id=\"searchEmp4\" parameterType=\"Map\" resultMap=\"empRes\" ><\/p>\n

select * from employee<\/p>\n

<where><\/p>\n

<if test=\"deptIds!=null\"><\/p>\n

deptID in<\/p>\n

<foreach \u00a0collection=\"deptIds\" \u00a0item=\"deptID\" \u00a0open=\"(\" separator=\",\" close=\")\" \u00a0><\/p>\n

#{deptID}<\/p>\n

<\/foreach><\/p>\n

<\/if><\/p>\n

<\/where>
\n<\/select><\/p><\/blockquote>\n

 <\/p>\n

6. set \u6761\u4ef6(\u4f7f\u7528set\u4f7f\u66f4\u65b0\u65f6\u4e0d\u5fc5\u518d\u6b21\u66f4\u65b0\u5168\u90e8\u6570\u636e)<\/h2>\n

<!--
\nset \u6709\u5bb9\u9519\u529f\u80fd \u81ea\u52a8\u5254\u9664\u6700\u540e\u4e00\u4e2a\u9017\u53f7,
\n-->
\n<update id=\"updateEmp\" parameterType=\"Employee\"><\/p>\n

update employee<\/p>\n

<set><\/p>\n

<if test=\"username!=null\"><\/p>\n

username=#{username},<\/p>\n

<\/if><\/p>\n

<if test=\"salary>0\"><\/p>\n

salary=#{salary}<\/p>\n

<\/if><\/p>\n

<\/set><\/p>\n

where id=#{id}
\n<\/update><\/p><\/blockquote>\n

\u8f6c\u8f7d\u8bf7\u6ce8\u660e\uff1a\u5de6\u624b\u4ee3\u7801\u53f3\u624b\u8bd7<\/a> » Mybatis\u5b66\u4e60\u4e4b\u8def\u2014\u52a8\u6001SQL\u67e5\u8be2<\/a><\/p>

\r\n
\r\n
~\u8c22\u8c22\u6253\u8d4f~<\/div>\r\n
<\/div>
<\/div>
<\/div><\/div>\r\n