Mybatis配置及动态SQL语句标签

#Mybatis

resources:

###db.properties:

jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/db0602?useSSL=true
jdbc.username = root
jdbc.password = 111111
env_choosen=deve
team.url = jdbc:mysql://localhost:3306/teams?useSSL=true

###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>
    <!--这个配置文件中,所有的标签都有严格的顺序-->
    <properties resource="db.properties"/>

    <typeAliases>
        <!--给javabean起别名-->
        <!--<typeAlias type="com.lanou.team.bean.Team" alias="Team"/>-->
        <!--如果使用package设定,类名就是相应的别名-->
        <package name="com.lanou.team.bean"/>
        <package name="com.lanou.order.bean"/>
        <package name="com.lanou.perhou.bean"/>
    </typeAliases>

    <environments default="${env_choosen}">
        <environment id="deve">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>

        <!--一对一的环境配置-->
        <environment id="team">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${team.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    <mappers>
        <!--<mapper resource="com/lanou/mapper/StudentMapper.xml"></mapper>-->
        <!--自动读取mapper包下的所有xml文件-->
        <package name="com.lanou.anno.mapper"/>
        <package name="com.lanou.team.mapper"/>
    </mappers>
</configuration>

##src.main下创建java包，更改为Sources Root类型
java包下创建bean / mapper包

• bean
  • 实体类
• mapper
  • BlogMapper接口
  • BlogMapper.xml文件

BlogMapper.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lanou.blog.mapper.BlogMapper">

    <resultMap id="BaseMap" type="Blog">
        <id column="id" property="id"/>
        <result column="state" property="state"/>
        <result column="title" property="title"/>
        <result column="content" property="content"/>
        <result column="author" property="author"/>
        <result column="des" property="des"/>
    </resultMap>
    // <select>SQL语句标签...</select>
</mapper>

##动态SQL语句标签
1.if 标签：

1. 实现效果：判断作用

    <select id="findAll" resultMap="BaseMap">
        SELECT * FROM blog
        <if test="btitle != null">
        AND title = #{btitle}
        </if>
        <if test="bauthor ! = null">
        AND author = #{bauthor}
        </if>
    </select>

2.choose 标签：
两个字标签：when otherwise

1. 实现效果：按顺序判断其内部when标签中的test条件出否成立，如果有一个成立，则 choose 结束

    <select id="findBlogs" resultMap="BaseMap">
        SELECT * FROM blog WHERE state = "active"
        <choose>
            <when test="title != null">
                AND title = #{title}
            </when>
            <when test="author != null">
                AND author = #{author}
            </when>
            <otherwise>
                AND id = 1
            </otherwise>
        </choose>
    </select>

3.where 标签：
用于任何需要使用where的地方 取代where的sql语句，会自动调整内部的条件语法，剪掉开头的AND或者OR，用于不确定某个参数是否为null的语句

1. 实现效果：SELECT * FROM blog WHERE state = “active” AND state = #{state} AND title = #{title}

    <select id="findBlogsWithWhere" resultMap="BaseMap">
        SELECT * FROM blog WHERE state = "active"
        <where>
            <if test="state != null">
                AND state = #{state}
            </if>
            <if test="title != null">
                AND title = #{title}
            </if>
        </where>
    </select>

4.set 标签：
一般用于update语法 替代SET语句，将条件列表的最后的逗号剪掉

1. 实现效果：UPDATE blog SET #{title},#{author} WHERE id = #{id}

    <update id="updateBlog">
        UPDATE blog
        <set>
            <if test="title != null">
                title = #{title},
            </if>
            <if test="author != null">
                author = #{author},
            </if>
        </set>
        WHERE id = #{id}
    </update>

5.trim 标签：

1. prefix：整体sql内容的前缀：( values (
2. suffix：整体sql内容的后缀： ） )
3. suffixOverrides：需要剪掉的后缀内容： ，
4. prefixOverrides：需要剪掉的前缀内容：
5. 实现效果：INSERT INTO blog (title,author) VALUES (#{title},#{author})

    <insert id="insertBlog">
        INSERT INTO blog
        <trim prefix= "(" suffix= ")" suffixOverrides=",">
            <if test="title != null">
                title,
            </if>
            <if test="author != null">
                author,
            </if>
        </trim>
        
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="title != null">
                #{title},
            </if>
            <if test="author != null">
                #{author},
            </if>
        </trim>
    </insert>

6.foreach 标签：
用于循环参数

1. collection：参数名
2. item：参数循环后的值
3. index：角标
4. open：前缀： (
5. close：后缀： )
6. separator：SQL语句中的中间符号： ,
7. 实现效果：SELECT * FROM blog WHERE id IN (item1,item2,item,…)

    <select id="findBlogsWithIds" resultMap="BaseMap">
        SELECT * FROM blog WHERE id IN

        <foreach collection="ids" item="item" index="index"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>