Mybatis XML配置总结

1、#{ } 和 ${ }区别

select * from user where name = #{name}; 

#{} 在动态解析的时候， 会解析成一个参数标记符。就是解析之后的语句是：

select * from user where name = ?;  

那么我们使用 ${}的时候

select * from user where name = '${name}'; 

${}在动态解析的时候，会将我们传入的参数当做String字符串填充到我们的语句中，就会变成下面的语句

select * from user where name = "dato"; 

预编译之前的 SQL 语句已经不包含变量了，完全已经是常量数据了。相当于我们普通没有变量的sql了。

综上所得， ${ } 变量的替换阶段是在动态 SQL 解析阶段，而 #{ }变量的替换是在 DBMS 中。

这是 #{} 和 ${} 我们能看到的主要的区别，除此之外，还有以下区别：

• #方式能够很大程度防止sql注入。
• $方式无法防止Sql注入。
• $方式一般用于传入数据库对象，例如传入表名.
• 一般能用#的就别用$.

所以我们在使用mybatis的时候，尽量的使用#方式！！！

2、通用查询结果列

<sql id="Base_Column_List">
        id, name, reason_leave AS reasonLeave, age, phone, status, organization_id AS organizationId,job_number AS jobNumber,job_agent AS jobAgent,application_time AS applicationTime,leave_totaltime AS leaveTotaltime,leave_reason_detail AS leaveReasonDetail,start_date AS startDate,end_date AS endDate,start_date_concrete AS startDateConcrete,end_date_concrete AS endDateConcrete,project_owership AS projectOwership
    </sql>

设置了通用查询结果列后，在<select><update>等标签中就可使用设置好的别名代替数据库列名

<select id="selectLeaveDetailVoById" resultMap="LeaveDetailVoResultMap" parameterType="java.lang.Long">
        SELECT
        t.id,
        t.`name`,
        reasonLeave,
        t.`status`,
        organizationId,
        jobNumber,
        jobAgent,
        applicationTime,
        leaveTotaltime,
        leave_reason_detail,
        start_date,
        start_date_concrete,
        end_date,
        end_date_concrete,
        s.`name` AS organizationName,
        o.id AS roleId,
        o.`name` AS roleName,
        t.project_owership
        FROM
        leave_detail t
        LEFT JOIN user_role r ON t.id = r.user_id
        LEFT JOIN role o ON r.role_id = o.id
        LEFT JOIN organization s ON s.id = t.organization_id
        <where>
            t.id = #{id}
        </where>
    </select>

3、<![CDATA[ ]]> XML语法。在CDATA内部的所有内容都会被解析器忽略。

sql中有一些特殊的字符的话，在解析xml文件的时候会被转义，如果不希望他被转义，可以通过使用<![CDATA[ ]]>来解决。

如果文本包含了很多的"<"字符 <=和"&"字符，那么最好把他们都放到CDATA部件中。但是有个问题那就是 <if test="">   </if>   <where>   </where>  <choose>  </choose>  <trim>  </trim> 等这些标签都不会被解析，所以我们只把有特殊字符的语句放在 <![CDATA[   ]]>  尽量缩小 <![CDATA[  ]]> 的范围。

<select> select w.name,job_number as employeeNo,reason_for_overtime as reasonLeave,
              work_overtime_hour as manhours,start_date as startDate,end_date as endDate,
              application_date as applicationTime,w.item_no as teamName,o.pid,w.branch as organizationId
        from work_overtime_hour w
         left join organization o on w.branch=o.id
        <where>
        
                <if test=" employeeNo != null and employeeNo != '' ">
                    job_number = #{employeeNo}
                </if>
                <if test=" startDate != null and startDate != ''">
                    <![CDATA[ and w.start_date >= #{startDate} ]]>
                </if>
                <if test=" endDate != null and endDate != ''">
                    <![CDATA[ and w.end_date <= #{endDate} ]]>
                </if>
        </where>
        order by startDate
	  </select>

4、mybatis 中xml配置模糊查询like的语法 concat()

SELECT  * from staff_info where employeeNo like concat('%',#{employeeNo },'%') 

5、mybatis中<foreach>标签

需求：按姓名查询时，可以输入多个姓名进行查询，且以逗号分隔（可以是半角也可是全角）

解决思路：接收到参数name后使用split分隔成字符串数组，再将数组put到hashmap中，在mybatis xml文件中通过<foreach>标签循环出name的多个值

实例：

Map<String, Object> condition = new HashMap<String, Object>();
        String[] names = {};
        if (StringUtils.isNotBlank(manHourSummary.getName())) {
            String nameStr = manHourSummary.getName();
            if(nameStr.contains(ManhoursConstants.CH_COMMA)){
                nameStr =  nameStr.replaceAll(ManhoursConstants.CH_COMMA, ManhoursConstants.EN_COMMA);//将所有全角逗号转为半角逗号
            }
            names = nameStr.split(ManhoursConstants.EN_COMMA);//通过逗号分隔name成多个姓名的字符串数组
        }
        

condition.put("name", names);

manHourSummaryService.selectManHoursVoPage(condition);

<select id="selectManHoursVoPage" resultMap="MHSVoResultMap">
         SELECT a.name,a.employee_no as employeeNo,ifnull(t2.leaveTotaltime,0) as leaveTotaltime,ifnull(t1.workOvertimeHour,0) as workOvertimeHour,(ifnull(t1.workOvertimeHour,0)+ifnull(t2.leaveTotaltime,0)) as vacationTime,t3.graduationTime,t3.entryDate,t3.isLaborContract
         FROM staff_info a
         left join (select sum(b.work_overtime_hour) as workOvertimeHour,b.name from work_overtime_hour b group by b.name) t1 on a.name=t1.name 
         left join (select sum(-c.leave_totaltime) as leaveTotaltime ,c.name from leave_detail c  group by c.name) t2 on a.name=t2.name
         left join (select s.name,employee_no as employeeNo,graduation_time as graduationTime,entry_date as entryDate,is_labor_contract as isLaborContract from staff_info s left join leave_detail l on s.name = l.name and s.employee_no = l.job_number group by name) t3 on a.name = t3.name

         <where>
            <if test=" name != null and name != '' ">
             <foreach collection="name" item="item" index="key" separator="or">   
			    <if test="null != name[key]">    
			        a.name like concat('%',#{item},'%') 
			    </if>  
			</foreach>
			</if> 
        </where>
    </select>

collection="name" 对应 condition.put("name", names);hashmap 的key; index的值必须写key表示循环map的key;separator 为连接符，

表示每句a.name like concat('%',#{item},'%') 都用or连接，最后形成

select * from staff_info where a.name like concat('%',#{item},'%') or a.name like concat('%',#{item},'%').....