Mybatis配置及动态SQL语句标签

Mybatis

resources:

db.properties:

jdbc.driver = com.mysql.jdbc.Driverjdbc.url = jdbc:mysql://localhost:3306/db0602?useSSL=truejdbc.username = rootjdbc.password = 111111env_choosen=deveteam.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 * FROM blog AND title = #{btitle} AND author = #{bauthor}

    <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. 实现效果：SELECT * FROM blog WHERE state = “active” AND title = #{title} AND author = #{author} AND id = 1

    <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

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 标签：

是where和set标签的实现基础

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>