分享知识-快乐自己：Mybatis 基础动态语句

目录：

User：

package mlq.bean;

/**
 * 用户实体类
 */
public class User {

  private Integer uId;
  private String userName;
  private String userPwd;
  private String realName;

  public User(Integer uId, String userName, String userPwd, String realName) {
    this.uId = uId;
    this.userName = userName;
    this.userPwd = userPwd;
    this.realName = realName;
  }

  public User(Integer uId) {
    this.uId = uId;
  }

  public User() {
  }

  @Override
  public String toString() {
    return "User{" +
            "uId=" + uId +
            ", userName='" + userName + '\'' +
            ", userPwd='" + userPwd + '\'' +
            ", realName='" + realName + '\'' +
            '}';
  }

}

UserMappers：

package mlq.Mapper;

import mlq.bean.User;
import org.apache.ibatis.annotations.Param;

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

/**
 * 用户接口
 */
public interface UserMappers {

    /**
     * 查询用户信息
     */
    public List<User> userAllList(User user);
    public List<User> userAllListTow(User user);
    public List<User> userAllListChoose(User user);
    /**
     * 更新用户信息
     */
    public int updUser(User user);
    public int updUserTow(User user);

    /**
     * 使用数组入参Foreach遍历
     * @param ars
     * @return
     */
    public List<User> userArrays(int[] ars);

    /**
     * 使用List普通入参Foreach遍历
     * @param ars
     * @return
     */
    public List<User> userListForeach(List<String> ars);

    /**
     * 使用List对象入参Foreach遍历
     * @param ars
     * @return
     */
    public List<User> userListForeachs(List<User> ars);

    /**
     * 使用Map+List集合入参Foreach遍历
     * @param ars
     * @return
     */
    public List<User> userMapForeach(Map<String,Object> ars);

    /**
     * 使用Map入参Foreach遍历
     * @param ars
     * @return
     */
    public List<User> userMap(Map<String,Object> ars);
    public List<User> userMapTow(@Param("ars") Map<String,User> ars);

    /**
     * 同时插入多条数据
     * @param list
     * @return
     */
    public int inserUsers(List<User> list);
}

MyBatis.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文件-->
    <properties resource="DatabaseConfig.properties" />

    <!--设置类别名-->
    <typeAliases>
        <package name ="mlq.bean" />
    </typeAliases>

    <!--设置Mybatis运行环境-->
    <environments default="mysql"><!--default找到环境的id，只能用一个-->
        <environment id="mysql"><!--可以定义多个环境-->
            <transactionManager type="JDBC"/><!--后续事务的管理 统一交给spring容器-->
            <dataSource type="POOLED"><!--数据源 Tomcat：JNDI Mybatis：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>
    </environments>

    <mappers>
        <mapper resource="mapper/UserMapper.xml"/>
    </mappers>
</configuration>

MyBatisTool：

package mlq.util;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.Reader;

/**
 * 获取连接对象工具类
 */
public class MyBatisTool {

    //建立私有构造
    private  MyBatisTool(){}

    //SQLSessionFactory 简单的理解就是创建SQLSession 实例的工厂。
    private final static SqlSessionFactory sqlSessionFactory;

    //静态代码块初始化sqlSessionFactory对象
    static
    {
        //获取Mybatis.xml核心配置文件路径
        String path="MyBatis.xml";
        Reader reader=null;
        try {
            //将文件信息读取到内存中
             reader = Resources.getResourceAsReader(path);
        } catch (IOException e) {
            e.printStackTrace();
        }
        //创建实例
        sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
    }

    /**
     * 获取工厂函数SQLSessionFactory
     * @return
     */
   public static SqlSessionFactory getSqlSessionFactory() { return sqlSessionFactory; }

    /**
     * 获取SqlSession连接对象
     * @return
     */
   public static SqlSession getSqlSession(){return sqlSessionFactory.openSession();}

    /**
     * 关闭连接对象
     * @param sqlSession
     */
   public static void closeSqlsession(SqlSession sqlSession){
       if (sqlSession!=null)
       {
           sqlSession.close();
       }
   }
}

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">
<mapper namespace="mlq.Mapper.UserMappers">


    <!--使用Where标签实现动态SQL拼接-->
    <select id="userAllListTow" parameterType="User" resultType="User">
        SELECT * FROM `user` WHERE 1=1
        <if test="userName!=null and userName!=''">
            AND `userName`=#{userName}
        </if>
        <if test="userPwd!=null and userPwd!=''">
            AND `userPwd`=#{userPwd}
        </if>
        <if test="uId!=null and uId!=''">
            AND `uId`=#{uId}
        </if>
    </select>

    <!--使用Where标签实现动态SQL拼接-->
    <select id="userAllList" parameterType="User" resultType="User">
        SELECT * FROM `user`
        <where>
            <if test="userName!=null and userName!=''">
                AND `userName`=#{userName}
            </if>
            <if test="userPwd!=null and userPwd!=''">
                AND `userPwd`=#{userPwd}
            </if>
            <if test="uId!=null and uId!=''">
                AND `uId`=#{uId}
            </if>
        </where>
    </select>

    <!--使用SET标签更新数据:说明此番操作有漏洞。逗号位置不明确：建议使用下述方式-->
    <update id="updUser" parameterType="User">
        UPDATE `user`
        <set>
            <if test="userName!=null and userName!=''">
                `userName`=#{userName},
            </if>
            <if test="userPwd!=null and userPwd!=''">
                `userPwd`=#{userPwd},
            </if>
            <if test="realName!=null and realName!=''">
                `realName`=#{realName},
            </if>
        </set>
        <where>
            `uId`=#{uId}
        </where>
    </update>
    <update id="updUserTow" parameterType="User">
        UPDATE `user`
        <trim prefix="set" suffixOverrides="," suffix="`uId`=#{uId}">
            <if test="userName!=null and userName!=''">
                `userName`=#{userName},
            </if>
            <if test="userPwd!=null and userPwd!=''">
                `userPwd`=#{userPwd},
            </if>
            <if test="realName!=null and realName!=''">
                `realName`=#{realName},
            </if>
        </trim>
    </update>

    <!--公用SQL片段-->
    <sql id="cols"> SELECT * FROM `user`</sql>
    <!--使用choose方式判断：相当于switch结构-->
    <select id="userAllListChoose" parameterType="User" resultType="User">
        <include refid="cols"/>
        <where>
            <choose>
                <when test="userName!=null and userName!=''">
                    AND `userName`=#{userName}
                </when>
                <when test="userPwd!=null and userPwd!=''">
                    AND `userPwd`=#{userPwd}
                </when>
                <otherwise>
                    AND `uId`=#{uId}
                </otherwise>
            </choose>
        </where>
    </select>

    <!--使用数组入参，Foreach遍历-->
    <select id="userArrays" resultType="User">
        <include refid="cols"/>
        <trim prefix="where `uId` in">
            <if test="array.length>0">
                <foreach item="temp" collection="array" open="(" close=")" separator=",">
                    #{temp}
                </foreach>
            </if>
        </trim>
    </select>
<!--使用List普通入参，Foreach遍历-->
    <select id="userListForeach" resultType="User">
        <include refid="cols"/>
        <trim prefix="where `uId` in">
            <if test="list.size>0">
                <foreach item="temp" collection="list" open="(" close=")" separator=",">
                    #{temp}
                </foreach>
            </if>
        </trim>
    </select>
<!--使用List对象入参，Foreach遍历-->
    <select id="userListForeachs" resultType="User">
        <include refid="cols"/>
        <trim prefix="where `uId` in">
            <if test="list.size>0">
                <foreach item="temp" collection="list" open="(" close=")" separator=",">
                    #{temp.uId}
                </foreach>
            </if>
        </trim>
    </select>
<!--使用Map+List集合入参-->
    <select id="userMapForeach" resultType="User">
        <include refid="cols"/>
        <where>
            <if test="mapList.size>0">
                `uId` in
                <foreach collection="mapList" open="(" close=")" item="temp" separator=",">
                    #{temp}
                </foreach>
            </if>
        </where>

    </select>

    <select id="userMap" resultType="User">
        <include refid="cols"/>
        <where>
            `uId` in (#{one})
        </where>
    </select>
    <!--使用注解入参Map-->
    <select id="userMapTow" resultType="User">
        <include refid="cols"/>
        <where>
            `uId` in
            <if test="ars.keys.size>0">
                <foreach collection="ars.keys" open="(" separator="," close=")" item="key" index="ind">
                    <!--通过键获取value-->
                    #{ars[${key}].uId}
                    <!--直接获取key-->
                    <!--#{key}-->
                </foreach>
            </if>
        </where>
    </select>


</mapper>

UserText：

package mlq;

import mlq.bean.User;
import mlq.Mapper.UserMappers;
import mlq.util.MyBatisTool;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class UserText {

    //连接SQLSession
    private SqlSession sqlSession = null;
    private UserMappers mapper = null;

    //测试前置增强
    @Before
    public void before() {
        sqlSession = MyBatisTool.getSqlSession();
        mapper = sqlSession.getMapper(UserMappers.class);
    }

    //测试后置增强
    @After
    public void after() {
        sqlSession.commit();
        sqlSession.close();
    }

    /**
     * 查询用户信息
     */
    @Test
    public void userAllList() {
        User user = new User();
        user.setUserName("MLQ");
        user.setUId(20180001);
        List<User> users = mapper.userAllList(user);
        System.out.println(users);
    }

    @Test
    public void userAllListTow() {
        User user = new User();
        user.setUserName("MLQ");
        user.setUId(20180001);
        List<User> users = mapper.userAllListTow(user);
        System.out.println(users);
    }

    @Test
    public void userAllListChoose() {
        User user = new User();
        user.setUserName("FKX");
        user.setUId(20180002);
        List<User> users = mapper.userAllListChoose(user);
        System.out.println(users);
    }

    /**
     * 更新用户信息
     */
    @Test
    public void updUser() {
        User user = new User();
        user.setRealName("MLQMLQ");
        user.setUserPwd("456");
        user.setUserName("MLQ");
        user.setUId(20180001);
        int i = mapper.updUser(user);
        System.out.println((i > 0) ? "更新成功" : "更新失败！！！");
    }

    @Test
    public void updUserTow() {
        User user = new User();
        user.setRealName("MLQ");
        user.setUserPwd("123");
        user.setUserName("MLQ");
        user.setUId(20180001);
        int i = mapper.updUser(user);
        System.out.println((i > 0) ? "更新成功" : "更新失败！！！");
    }

    /**
     * 使用数组入参Foreach遍历
     */
    @Test
    public void arrayList() {
        int[] ars = {20180001, 20180002};
        List<User> users = mapper.userArrays(ars);
        System.err.println(users);
    }

    /**
     * 使用List入参Foreach遍历
     */
    @Test
    public void userListForeach() {
        List<String> list = new ArrayList<String>();
        list.add("20180001");
        list.add("20180002");
        List<User> users = mapper.userListForeach(list);
        System.out.println(users);
    }

    @Test
    public void userListForeachs() {
        List<User> list = new ArrayList<User>();
        User user = new User();
        user.setUId(20180001);
        User user1 = new User();
        user1.setUId(20180002);
        list.add(user);
        list.add(user1);
        List<User> users = mapper.userListForeachs(list);
        System.err.println(users);
    }

    //使用Map集合入参Foreach遍历
    @Test
    public void userMapForeach() {
        Map<String, Object> map = new HashMap<String, Object>();
        List<Integer> list = new ArrayList<Integer>();
        list.add(20180001);
        list.add(20180002);
        map.put("one", 20180001);
        map.put("mapList", list);
        List<User> users = mapper.userMapForeach(map);
        System.err.println(users);
    }

    //使用Map集合入参Foreach遍历
    @Test
    public void userMap() {
        Map<String, Object> map = new HashMap<String, Object>();
        List<Integer> list = new ArrayList<Integer>();
        list.add(20180001);
        list.add(20180002);
        map.put("one", 20180001);
        map.put("mapList", list);
        List<User> users = mapper.userMap(map);
        System.err.println(users);
    }

    /**
     * 入参Map集合使用注解标注信息
     */
    @Test
    public void userMapTow() {
        Map<String, User> map = new HashMap<String, User>();
        User user = new User(20180001);
        User user1 = new User(20180002);
        map.put("20180001", user);
        map.put("20180002", user1);
        List<User> users = mapper.userMapTow(map);
        System.err.println(users);
    }
}

案例Demo下载：Day02中的Maven