1.动态SQL

根据用户的条件动态创建SQL

2.动态SQL使用案例

2.1 创建实体类

2.1.1 Member

public class Member {
    private int memberId;
    private String memberNick;
    private String memberGender;
    private int memberAge;
    private String memberCity;
}

2.1.2 MemberSearchCondition

public class MemberSearchCondition {
    private String gender;
    private Integer minAge;
    private Integer maxAge;
    private String city;
}

2.2 创建MemberDAO

public interface MemberDAO {
    //1.在多条件查询中，如果查询条件不确定，可以直接使用HashMap作为参数
   //缺点是要手动维护key的一致性
   // public abstract List<Member> searchMember(HashMap<String, Object> params);
   //2.还可以创建查询条件类 MemberSearchCondition
    public  List<Member> searchMember(MemberSearchCondition memberSearchCondition);
}

2.3 创建MemberMapper

动态SQL语句

>大于
<小于
<where></where>标签

<mapper namespace="com.lxr.dao.MemberDAO">
    <resultMap id="memberMap" type="Member">
        <id column="member_id" property="memberId" />
        <result column="member_nick" property="memberNick" />
        <result column="member_gender" property="memberGender" />
        <result column="member_age" property="memberAge" />
        <result column="member_city" property="memberCity" />
    </resultMap>
<select id="searchMember" resultMap="memberMap">
        select member_id,member_nick,member_gender,member_age,member_city
        from members
        <where>
            <if test="gender != null">
                and member_gender = #{gender}
            </if>
            <if test="minAge != null">
                and member_age &gt;= #{minAge}           <!--&gt;大于-->
            </if>
            <if test="maxAge != null">
                and member_age &lt;= #{maxAge}          <!--&lt;小于-->
            </if>
            <if test="city != null">
                and member_city = #{city}
            </if>
        </where>
</select>

2.4 测试动态SQL查询

2.4.1 where

public void searchMember() {
        HashMap<String,Object> params = new HashMap<String,Object>();

//        params.put("gender","女");
//        params.put("minAge",null);
//        params.put("MaxAge",null);
//        params.put("city",null);
//
//        MemberDAO memberDAO = MyBatisUtil.getMapper(MemberDAO.class);
//        List<Member> members = memberDAO.searchMember(params);
//
//        System.out.println(members);

        MemberSearchCondition params1 = new MemberSearchCondition();
        params1.setGender("女");
//        params1.setMinAge(20);
//        params1.setMaxAge(22);
//        params1.setCity("武汉");
        MemberDAO memberDAO = MyBatisUtil.getMapper(MemberDAO.class);
        List<Member> members = memberDAO.searchMember(params1);

        for(Member i:members){
            System.out.println(i);
        }
    }

2.4.2 trim

<mapper namespace="com.lxr.dao.MemberDAO">
        <resultMap id="memberMap" type="Member">
            <id column="member_id" property="memberId" />
            <result column="member_nick" property="memberNick" />
            <result column="member_gender" property="memberGender" />
            <result column="member_age" property="memberAge" />
            <result column="member_city" property="memberCity" />
        </resultMap>
    <select id="searchMember" resultMap="memberMap">
            select member_id,member_nick,member_gender,member_age,member_city
            from members
            <trim prefix="where" prefixOverrides="and | or" suffix="order by member_age">
                <if test="gender != null">
                    and member_gender = #{gender}
                </if>
                <if test="minAge != null">
                    and member_age &gt;= #{minAge}           <!--&gt;大于-->
                </if>
                <if test="maxAge != null">
                    and member_age &lt;= #{maxAge}          <!--&lt;小于-->
                </if>
                <if test="city != null">
                    and member_city = #{city}
                </if>
            </trim>
        </select>
</mapper>

2.4.3 foreach

按城市条件查询

select member_id,member_nick,member_gender,member_age,member_city
from members
where memberIcity in
<foreach collection="list" item="cityName" separator="," open="(" close=")">
      #{cityName}
</foreach>

2.模糊查询

${key} 表示获取参数，先获取参数的值，拼接到SQL语句中，再编译执行SQL语句
#{key} 表示获取参数，先完成SQL语句的编译，预编译之后再将获取的参数设置到SQL语句中 可以避免SQL注入问题

2.1 定义查询方法

 public List<Member> searchMemberByNick(@Param("keyword") String keyword);

2.2 定义SQL语句

like 
1.  '%'  任意字符 
2.  '_'  任意一个字符

如果参数是String类型，需要使用 parameterType 声明参数类型
<select id="searchMemberByNick" parameterType="java.lang.String" resultMap="memberMap">
        select member_id,member_nick,member_gender,member_age,member_city
        from members
        where member_nick like '%${keyword}%'
</select>

2.3 测试

public void searchMemberByNick() {
        MemberDAO memberDAO = MyBatisUtil.getMapper(MemberDAO.class);
        List<Member> members = memberDAO.searchMemberByNick("花");
        System.out.println(members);
    }

3.MaBatis日志配置

MyBatis作为一个封装好的ORM框架，其运行过程我们没有办法跟踪，为了让开发者了解MyBatis执行流程及每个执行步骤所完成的工作，MyBatis框架本身继承了log4j日志框架，对运行的过程进行跟踪记录。
我们只需对MyBatis进行相关的日志配置，就可以看到MyBatis运行过程中的日志信息。

3.1 添加日志框架依赖

<dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
</dependency>

3.2 添加日志配置文件

在resources目录下创建名为 log4j.properties 文件
在 log4j.properties 文件配置日志输出的方式

#声明日志的输出级别及输出方式
log4j.rootLogger=DEBUG,stdout
log4j.logger.org.mybatis.example.BlogMapper=TRACE
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
#定义日志的打印格式
# %t 表示线程名称  %5p 表示输出日志级别 
log4j.appender.stdout.layout.ConversionPattern=[%t] %5p - %n%m

整合Druid连接池