MyBatis学习笔记（四）动态SQL语句专题

首发于我的博客 和尚的博客
本文讲解动态的SQL操作，CRUD中，CUD都需要自己手动提交事务，动态的添加语句，批量删除的三种处理方式，提取公共的sql代码，if标签，where标签，trim标签，set标签，choose标签，foreach标签，批量添加

源码获取github

1.trim标签

<trim prefix="当发现有内容的时候,你在内容的最前面想加入什么内容"           
prefixOverrides="当发现有内容的时候,你在内容的最前面想抹掉什么内容"            
suffix="当发现有内容的时候,你在内容的最后面面想加入什么内容"            
suffixOverrides="当发现有内容的时候,你在内容的最后面想抹掉什么内容">        
</trim>

2.set标签

当你发现有内容的时候,在内容的最前面加入 set 当你放下有内容的时候,检查内容的最后面是否有逗号”,” 如果将其抹掉

3.where标签

• 如果发现标签內有内容,那么会在内容的最前面加入关键字 where
• 如果有内容,会检查内容的最前面是否含有 AND空格 或者 OR空格 ,自动将其抹掉

4.foreach标签

• 如果只传了一个数组或者一个集合 collection=”array 或者 list”
• 如果传的是一个Map里装了list 就写map的key，如果是对象里setList，就写对象的list属性,这个属性就对应一个集合
• item相当于，他的遍历的一个值，open=”开始加入” close=”最后加入” separator=”以什么间隔”–>

4.choose标签类似java中的switch

5.代码结构

6.数据库配置文件

同前几天笔记

7.核心配置文件

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>
    <!--加载属性文件 -->
    <properties resource="jdbc.properties"/>
    <!--自定义设置类型的别名，也就是resultMap里的type，避免包名已修改，在映射文件里修改多处地方-->
    <typeAliases>
        <!-- 方式一、com.hs.model.Skill这个类设置了别名hs，之后要用这个的话，直接写hs -->
        <!--<typeAlias type="com.hs.model.Skill" alias="hs"/> -->
        <!-- 方式二、com.hs.model这个包下的类，要用的时候直接写类名，比如用com.hs.model.Skill，直接写Skill -->
        <package name="com.hs.model"/>
    </typeAliases>
    <!--配置数据库的环境-->
    <environments default="development">
        <environment id="development">
            <!--事务管理器:保证数据的完整性和一致性   关键信息  -->
            <!--框架：默认情况下CUD操作需要  手动提交事务
            （如同在Navicat中表中输入了数据，没有点那个小√，就是没有提交事务，
            但是输入insert语句，就自动提交事务了）  -->
            <transactionManager type="JDBC" />
            <!--使用的是连接池：百度java如何实行连接池的原理？  -->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.mysql.driver}" />  <!--获取值，${属性文件里的变量名}，也可以直接写值-->
                <property name="url" value="${jdbc.mysql.url}" />  <!--获取值，${属性文件里的变量名}，也可以直接写值-->
                <property name="username" value="${jdbc.mysql.username}" /> <!--获取值，${属性文件里的变量名}，也可以直接写值-->
                <property name="password" value="${jdbc.mysql.password}" /> <!--获取值，${属性文件里的变量名}，也可以直接写值-->
            </dataSource>
        </environment>
    </environments>

    <!--加载映射文件 ，也就是含sql语句的文件-->
    <mappers>
         <!--告知映射文件方式1，一个一个的配置-->
        <mapper resource="com/hs/model/UserMapper.xml"/>
        <!-- 告知映射文件方式2，自动扫描包内的Mapper接口与配置文件 -->
        <!--<package name="com.hs.model"/>-->
    </mappers>
</configuration>

8.持久化类

User.java

package com.hs.model;

import java.util.Date;

public class User {

    /*本字段名字跟数据库的字段一样*/
    private Integer user_id;
    private String user_name;
    private String sex;
    private Double money;
    private Date birthday;

    public Integer getUser_id() {
        return user_id;
    }

    public void setUser_id(Integer user_id) {
        this.user_id = user_id;
    }

    public String getUser_name() {
        return user_name;
    }

    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Double getMoney() {
        return money;
    }

    public void setMoney(Double money) {
        this.money = money;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    @Override
    public String toString() {
        return "User{" +
                "user_id=" + user_id +
                ", user_name='" + user_name + '\'' +
                ", sex='" + sex + '\'' +
                ", money=" + money +
                ", birthday=" + birthday +
                '}';
    }
}

9.映射文件

UserMapper.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">
<!--namespace=命名空间，唯一不重复-->
<mapper namespace="com.hs.dao.UserDao"><!--命名空间规则
传统模式：（持久化类（实体化Bean）的类名全路径com.hs.Skill）
接口代理模式：接口的全路径r-->
    <!--见帮助文档http://www.mybatis.org/mybatis-3/zh/dynamic-sql.html-->
    <resultMap id="BaseResultMap" type="com.hs.model.User">
        <id column="user_id" property="user_id"/>
        <result column="user_name" property="user_name"/>
        <result column="sex" property="sex"/>
        <result column="money" property="money"/>
        <result column="birthday" property="birthday"/>
    </resultMap>
    <!--提取公共的SQL代码-->
    <sql id="oa_user_columns">
        user_id,user_name,sex,money,birthday
    </sql>
    <!--别名方式的SQL代码-->
    <sql id="oa_user_columns_alias">
        ${alias}.user_id,${alias}.user_name,${alias}.sex,${alias}.money,${alias}.birthday
    </sql>
    <!-- A.通过恒等式完成动态SQL语句 -->
    <select id="getUserListIf01" parameterType="map" resultMap="BaseResultMap">
        select
        <include refid="oa_user_columns"/>
        from
        oa_user
        where 1=1  <!-- 恒等式 ，但是影响效率-->
        <if test="name != null &amp;&amp; name != ''">
            and user_name like concat('%',#{name},'%')
        </if>
        <if test="sex != null and sex != ''">
            and sex = #{sex}
        </if>          <!-- 这个if里没有&&,只有&amp;&amp;或者and表示，||也只有or表示-->
    </select>
    <!-- B.通过where标签完成动态SQL语句，没有1=1 -->
    <select id="getUserListIf02" parameterType="map" resultMap="BaseResultMap">
        select
        <include refid="oa_user_columns"/>
        from
        oa_user
        <where>
            <if test="name != null &amp;&amp; name != ''">
                and user_name like concat('%',#{name},'%')
            </if>
            <if test="sex != null and sex != ''">
                and sex = #{sex}
            </if>          <!-- 这个if里没有&&,只有&amp;&amp;或者and表示，||也只有or表示-->
        </where>
    </select>
    <!-- C.trim标签实现<trim
     prefix="当发现有内容的时候,你在内容的最前面想加入什么内容"
     prefixOverrides="当发现有内容的时候,你在内容的最前面想抹掉什么内容"
     suffix="当发现有内容的时候,你在内容的最后面面想加入什么内容"
     suffixOverrides="当发现有内容的时候,你在内容的最后面想抹掉什么内容" -->
    <select id="getUserListIf03" parameterType="map" resultMap="BaseResultMap">
        select
        <include refid="oa_user_columns"/>
        from
        oa_user
        <trim prefix="where " prefixOverrides="and || or">
            <if test="name != null &amp;&amp; name != ''">
                and user_name like concat('%',#{name},'%')
            </if>
            <if test="sex != null and sex != ''">
                and sex = #{sex}
            </if>          <!-- 见xml转义字符 这个if里没有&&,只有&amp;&amp;或者and表示，||也只有or表示-->
        </trim>
    </select>

    <!--利用set标签
    当你发现有内容的时候,在内容的最前面加入 set
    当你发现有内容的时候,检查内容的最后面是否有逗号"," 如果有将其抹掉
    -->
    <update id="updateUser01ByPk" parameterType="com.hs.model.User">
        update oa_user
        <set>
            <if test="user_name !=null and user_name != ''">
                user_name = #{user_name},
            </if>
            <if test="sex !=null and sex != ''">
                sex = #{sex},
            </if>
            <if test="money != null"> <!--不是字符串就不要判断是否为空 ='' -->
                money = #{money},
            </if>
        </set>
        where user_id = #{user_id}
    </update>

    <!--利用trim标签，，，，但是不推荐
    -->
    <update id="updateUser02ByPk" parameterType="com.hs.model.User">
        update oa_user
        <trim prefix="set" suffixOverrides=",">
            <if test="user_name !=null and user_name != ''">
                user_name = #{user_name},
            </if>
            <if test="sex !=null and sex != ''">
                sex = #{sex},
            </if>
            <if test="money != null"> <!--不是字符串就不要判断是否为空 ='' -->
                money = #{money},
            </if>
        </trim>
        where user_id = #{user_id}
    </update>

    <!--利用choose标签，类似switch-->
    <select id="getSkillByChoose" parameterType="map" resultMap="BaseResultMap">
        select <include refid="oa_user_columns"/>
        from oa_user
        where
        <choose><!--注意单双引号,when里test='' -->
            <when test='sex!=null and sex=="男"'>
                money > 700
            </when>
            <!--<when test='sex!=null and sex=="女"'>
                money &lt;= 700   &lt;!&ndash;见xml转义字符 小于号(因为小于号<是标签的开头)的解决问题，或者这样写 &ndash;&gt;
            </when>-->
            <when test='sex!=null and sex="女"'>
                <![CDATA[
                money <= 700
                ]]>
            </when>     <!--里面不能包含标签，把<，不当做标签的开头 -->
            <otherwise>   <!--switch中的default-->
                1=1
            </otherwise>
        </choose>
    </select>
    <!--后面两个属性，前者可以不用写，后者是使用该属性，接收这个新添加的数据的主键-->
    <insert id="addSkill01" parameterType="com.hs.model.User" useGeneratedKeys="true" keyProperty="user_id">
        insert into oa_user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="user_name !=null and user_name!=''">
                user_name,
            </if>
            <if test="sex !=null and sex!=''">
                sex,
            </if>
            <if test="money !=null">
                money,
            </if>
            <if test="birthday !=null">
                birthday,
            </if>
        </trim>
        <trim prefix="values(" suffix=")" suffixOverrides=",">
            <if test="user_name !=null and user_name!=''">
                #{user_name},
            </if>
            <if test="sex !=null and sex!=''">
                #{sex},
            </if>
            <if test="money !=null">
                #{money},
            </if>
            <if test="birthday !=null">
                #{birthday},
            </if>
        </trim>
    </insert>

    <!--数组删除，如果数组的话，请不要去设置parameterType,让其自动识别  -->
    <delete id="deleteSkillByArray" >
        delete from oa_user where user_id in
        <!--对数组进行遍历，
        如果只传了一个数组或者一个集合  collection="array 或者 list
        如果传的是一个Map里装了list 就写map的key，如果是对象里setList，就写对象的list属性,这个属性就对应一个集合
        item相当于，他的遍历的一个值，open="开始加入" close="最后加入" separator="以什么间隔"-->
        <foreach collection="array" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </delete>
    <!--List集合删除 -->
    <delete id="deleteSkillByList" parameterType="list">
        delete from oa_user where user_id in
        <foreach collection="list" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </delete>
    <delete id="deleteSkillByMap" parameterType="map">
        delete from oa_user where user_id in
        <foreach collection="id_array" item="id" open="(" close=")" separator=",">  <!-- 等于遍历，id_array对应一个list，item就是这个list里的值  -->
            #{id}
        </foreach>
    </delete>

    <!--批量添加-->
    <insert id="addSkillByAll" parameterType="list">
        insert into oa_user(user_name,sex,money)
        values
        <foreach collection="list" item="user" separator=",">
            (#{user.user_name},#{user.sex},#{user.money})
        </foreach>
    </insert>
</mapper>

10.封装好的工具类

同之前的笔记

11.接口

UserDao.java

package com.hs.dao;

import com.hs.model.User;

import java.util.List;
import java.util.Map;

public interface UserDao {

    List<User> getUserListIf01(Map<String, Object> map);

    List<User> getUserListIf02(Map<String, Object> map);

    List<User> getUserListIf03(Map<String, Object> map);

    int updateUser01ByPk(User user);

    int updateUser02ByPk(User user);

    List<User> getSkillByChoose(Map<String, Object> map);

    int addSkill01(User user);

    int deleteSkillByArray(int[] array);

    int deleteSkillByList(List<Integer> list);

    int deleteSkillByMap(Map<String, Object> map);

    int addSkillByAll(List<User> list);
}

12.日志框架配置

log4j.properties.xml

# 日志配置文件Global logging configuration
log4j.rootLogger=ERROR, stdout
# MyBatis logging configuration...
# 如果要显示SQL语句，那么这个位置需要配置为命名空间log4j.logger.命名空间
log4j.logger.com.hs.dao.UserDao=TRACE
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

13.测试类

MyBatisTest.java

package com.hs.test;

import com.hs.dao.UserDao;
import com.hs.model.User;
import com.hs.util.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 接口代理模式实现操作，在xxDao接口中，定义方法：  sql语句返回的结果类型 sql语句的id(要传的值);
 * 不用写实现类，直接写测试类,具体实现见测试类
 * sql的映射文件的命名空间跟接口的全路径一致
 * 可以根据接口的返回类型自动判断使用selectOne还是selectList eg:返回的是一个对象的为one，返回的是list的就是list，如果是List<Bean>，也是list
 */
public class MyBatisTest {

    /**
     * if标签，进行两个姓名和性别的条件查询，通过恒等式完成动态SQL语句
     */
    @Test
    public void getUserListIf01() {
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlsession();
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("name", "悟");
            map.put("sex", "男");
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            System.out.println(userDao.getUserListIf01(map));
        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

    /**
     * if标签，进行两个姓名和性别的条件查询，通过where标签（推荐！！！）：
     * 如果发现标签內有内容,那么会在内容的最前面加入关键字 where
     * 如果有内容,会检查内容的最前面是否含有 AND空格 或者 OR空格 ,自动将其抹
     */
    @Test
    public void getUserListIf02() {
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlsession();
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("name", "悟");
            map.put("sex", "男");
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            System.out.println(userDao.getUserListIf02(map));
        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

    /**
     * if标签，进行两个姓名和性别的条件查询，通过trim标签：
     * <trim
     * prefix="当发现有内容的时候,你在内容的最前面想加入什么内容"
     * prefixOverrides="当发现有内容的时候,你在内容的最前面想抹掉什么内容"
     * suffix="当发现有内容的时候,你在内容的最后面面想加入什么内容"
     * suffixOverrides="当发现有内容的时候,你在内容的最后面想抹掉什么内容"
     * >      </trim>
     */
    @Test
    public void getUserListIf03() {
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlsession();
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("name", "悟");
            map.put("sex", "男");
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            System.out.println(userDao.getUserListIf03(map));
        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

    /**
     * 更新操作—变更数据库
     * 利用set标签
     * 当你发现有内容的时候,在内容的最前面加入 set
     * 当你发现有内容的时候,检查内容的最后面是否有逗号"," 如果有将其抹掉
     */
    @Test
    public void updateUser01ByPk() throws ParseException {
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlsession();
            //数据
            User user = new User();
            user.setUser_id(3);
            user.setUser_name("天蓬元帅");
            //日期的转换
            String date = "2018-8-3";
            DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            user.setBirthday(dateFormat.parse(date));
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            System.out.println(userDao.updateUser01ByPk(user));

            //重点！！！，CRUD中，除了R（查询），不用提交事务，其余全的自己提交事务，传统和接口模式提交事务都是一样的
            sqlSession.commit();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

    /**
     * 更新操作—变更数据库
     * 利用trim标签
     *
     * @throws ParseException
     */
    @Test
    public void updateUser02ByPk() throws ParseException {
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlsession();
            //数据
            User user = new User();
            user.setUser_id(3);
            user.setUser_name("天蓬元帅");
            user.setMoney(1000.0);
            //日期的转换
            String date = "2018-8-3";
            DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            user.setBirthday(dateFormat.parse(date));
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            System.out.println(userDao.updateUser02ByPk(user));
            //重点！！！，CRUD中，除了R（查询），不用提交事务，其余全的自己提交事务，传统和接口模式提交事务都是一样的
            sqlSession.commit();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

    /**
     * 查询通过choose标签
     * 解决小于号的问题
     */
    @Test
    public void getSkillByChoose() {
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlsession();
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("sex", "女");
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            System.out.println(userDao.getSkillByChoose(map));
        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

    /**
     * 添加一个数据，然后返回这个数据自增长的主键信息
     */
    @Test
    public void addSkill01() throws ParseException {
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlsession();
            User user = new User();
            user.setUser_name("沙僧");
            user.setSex("男");
            user.setMoney(455.0);
            String date = "2018-8-5";
            DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            user.setBirthday(dateFormat.parse(date));
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            System.out.println("影响行数"+userDao.addSkill01(user));
            sqlSession.commit();
            System.out.println("user_id:"+user.getUser_id());
        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

    /**
     * 传递传递数组删除规则
     */
    @Test
    public void deleteSkillByArray(){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlsession();
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            System.out.println(userDao.deleteSkillByArray(new int[]{6,8}));  //静态初始化
            //事务的手动提交
            sqlSession.commit();
        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

    /**
     * 传递List集合删除规则
     */
    @Test
    public void deleteSkillByList(){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlsession();
            List<Integer> list = new ArrayList<Integer>();
            list.add(9);
            list.add(11);
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            System.out.println(userDao.deleteSkillByList(list));
            //事务的手动提交
            sqlSession.commit();
        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }
    /**
     * 传递Map里装list删除规则
     */
    @Test
    public void deleteSkillByMap(){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlsession();
            List<Integer> list = new ArrayList<Integer>();
            list.add(8);
            list.add(10);
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("id_array",list);
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            System.out.println(userDao.deleteSkillByMap(map));
            //事务的手动提交
            sqlSession.commit();
        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

    /**
     * 通过list里装User实现批量添加
     */
    @Test
    public void addSkillByAll(){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlsession();
            List<User> list = new ArrayList<User>();
            User user1 = new User();
            user1.setUser_name("嫦娥");
            user1.setSex("女");
            user1.setMoney(1213.0);
            User user2 = new User();
            user2.setUser_name("月兔");
            user2.setSex("女");
            user2.setMoney(2113.0);
            list.add(user1);
            list.add(user2);
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            System.out.println(userDao.addSkillByAll(list));
            sqlSession.commit();
        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }
}