在xml中写SQL语句，可能会遇到 = like in or 这几种条件语句，还会遇到时间相关的比较，年份相关的比较等等，做为记录后续参考

一 parameterType入参 类UserRegister

**UserRegister用于parameterType入参**
@EqualsAndHashCode(callSuper = true)
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class UserRegister extends BaseEntity {
    private static final long serialVersionUID = 1L;

    /**
     * 主键，自动编号
     */
    private Long id;
    private String flowNo;
	/**
     * 用于in条件的过滤 
     */
    private String wantGoToSchool;
}

**UserRegisterDTO用于parameterType 返回映射**
@EqualsAndHashCode(callSuper = true)
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class UserRegisterDTO extends BaseEntity {
    private static final long serialVersionUID = 1L;

    /**
     * 主键，自动编号
     */
    private Long id;
    private String flowNo;
}

二 SQL查询

1 使用 resultMap的情况

<resultMap type="com.wycms.hgrxsb.domain.UserRegister" id="UserRegisterResult">
        <result property="id" column="id"/>

        <result property="userName" column="user_name"/>
        <result property="idNumber" column="id_number"/>
        <result property="phone" column="phone"/>
        <result property="workUnit" column="work_unit"/>
        <result property="studentType" column="student_type"/>
        <result property="studentHouseholdAddress" column="student_household_address"/>
        .....
    </resultMap>

<sql id="selectUserRegisterVo">
        select user_register.id,
        user_name,
        house_info.hy_fc_status,house_info.hy_fc_msg,house_info.hy_fc_time,
        sc1.school_name want_go_to_school_name,
        sc2.school_name expect_school_name

        from user_register
        left join house_info  on user_register.id=house_info.user_id
        left join school_config sc1 on user_register.want_go_to_school=sc1.school_code
        left join school_config sc2 on user_register.expect_school=sc2.id
    </sql>

3 使用resultMap来返回结果集

    <select id="selectUserRegisterList" parameterType="UserRegister"  resultMap="UserRegisterResult">
        <include refid="selectUserRegisterVo"/>
		<where>
            year(user_register.create_time)=year(now())
            <if test="userName != null  and userName != ''">
                and user_name like concat('%', #{userName}, '%')
            </if>
            <if test="wantGoToSchool != null  and wantGoToSchool != ''">
                and want_go_to_school in
                <foreach item="item" collection="wantGoToSchool.split(',')" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        order by user_register.create_time desc
    </select>

    <select id="selectDataList" parameterType="com.wycms.hgrxsb.domain.UserRegisterDTO"
            resultType="com.wycms.hgrxsb.domain.UserRegisterDTO">
        select
        a.id,
        a.kinship,
        a.kinship as kinshipremark,
        a.create_time
        from user_register a
        <where>
            year(a.create_time)=year(now())
            <if test="kinship != null and kinship > 0">
                and a.kinship = #{kinship}
            </if>
            <if test="userName != null  and userName != ''">
                and a.user_name like concat('%', #{userName}, '%')
            </if>
            <if test="studentName != null  and studentName != ''">
                and a.student_name like concat('%', #{studentName}, '%')
            </if>
        </where>

4 使用resultType来返回结果集

<select id="selectDataList" parameterType="com.wycms.hgrxsb.domain.UserRegisterDTO"
            resultType="com.wycms.hgrxsb.domain.UserRegisterDTO">
        select
        a.id,
        a.kinship,
        a.kinship as kinshipremark,
        a.status,
        a.fail_message as failMessage,

        a.create_time
        from user_register a
        <where>
            year(a.create_time)=year(now())
            <if test="userName != null  and userName != ''">
                and a.user_name like concat('%', #{userName}, '%')
            </if>
            <if test="studentName != null  and studentName != ''">
                and a.student_name like concat('%', #{studentName}, '%')
            </if>
        </where>
    </select>

** 为了演示的需要，这里在配置mybatis时没有启用自动驼峰 mybatis-config.xml的配置 **

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <settings>
        <setting name="cacheEnabled" value="true"/>  <!-- 全局映射器启用缓存 -->
        <setting name="useGeneratedKeys" value="true"/>  <!-- 允许 JDBC 支持自动生成主键 -->
        <setting name="defaultExecutorType" value="REUSE"/> <!-- 配置默认的执行器 -->
        <setting name="logImpl" value="SLF4J"/> <!-- 指定 MyBatis 所用日志的具体实现 -->
        <!-- <setting name="mapUnderscoreToCamelCase" value="true"/>  驼峰式命名 -->
    </settings>

</configuration>