Mybatis动态sql语句案例

（1）因为在之前两个案例已经详细说明了mybatis-con.xml的配置了，这里就不一一例举了

　　下面是关于用mybatis怎样去动态的去映射sql，在案例中我都标明了怎么使用，我就把主要的一些实现给大家例出来 

1.接口类

package edu.nf.mybatis2.dao;

import edu.nf.mybatis2.entity.StudentInfo;

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

/**
 * @Author lance
 * @Date 2018/9/13 0013
 */
public interface StuDaoInf {
   
    /**
     *批量删除
     */
    void deleteStudent(List<Integer> list);
    /**
     *批量添加
     */
    void addBachStudent(List<StudentInfo> parms);
    /**
     *动态查询
     */
    List<StudentInfo> dynamicQuery(Map<String,Object> parms);
    /**
     *动态选择条件查询
     */
    List<StudentInfo> dynamicQuery2(Map<String,Object> parms);
}

2、接口实现类：

package edu.nf.mybatis2.dao.Impl;

import edu.nf.mybatis2.dao.StuDaoInf;
import edu.nf.mybatis2.entity.StudentInfo;
import edu.nf.mybatis2.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;

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

/**
 * 描述:
 * 实现类
 *
 * @author lance
 * @create 2018-09-13 11:40
 */
public class StuDaoImpl implements StuDaoInf {
  
    @Override
    public void deleteStudent(List<Integer> list) {
        SqlSession session = MyBatisUtils.getSqlSession();
        try {
            StuDaoInf dao = session.getMapper(StuDaoInf.class);
            //批量删除
            dao.deleteStudent(list);
            session.commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.rollback();
        }finally {
            session.close();
        }
    }

    @Override
    public void addBachStudent(List<StudentInfo> parms) {
        SqlSession session = MyBatisUtils.getSqlSession();
        try {
            StuDaoInf daoInf = session.getMapper(StuDaoInf.class);
            //批量添加用户信息
            daoInf.addBachStudent(parms);
            session.commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.rollback();
        }finally {
            session.close();
        }
    }

    @Override
    public List<StudentInfo> dynamicQuery(Map<String, Object> parms) {
        SqlSession session = MyBatisUtils.getSqlSession();
        List<StudentInfo> list = null;
        try {
            StuDaoInf daoInf = session.getMapper(StuDaoInf.class);
            //动态查询用户信息
            list = daoInf.dynamicQuery(parms);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            session.close();
        }
        return list;
    }

    @Override
    public List<StudentInfo> dynamicQuery2(Map<String, Object> parms) {
        SqlSession session = MyBatisUtils.getSqlSession();
        List<StudentInfo> list = null;
        try {
            StuDaoInf daoInf = session.getMapper(StuDaoInf.class);
            return  list = daoInf.dynamicQuery2(parms);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            session.close();
        }
        return list;
    }
}

3、mapper配置映射（主要是看这里）

<?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="edu.nf.mybatis2.dao.StuDaoInf">
    <resultMap id="stuMap" type="studentInfo">
        <id property="sid" column="s_id"></id>
        <result property="stuName" column="s_name"></result>
        <result property="age" column="s_age"/>
        <result property="sex" column="s_sex"/>
        <result property="orgin" column="s_origin"/>
        <result property="tel" column="s_tel"/>
        <!--如果一个对象包含有另一个对象的引用时，要使用association引用，
        property指向实体类中的字段名，resultMap指向另一个实体类的resultMap的
        id-->
        <association property="clazz" resultMap="classMap"/>
    </resultMap>
    <!--classMap-->
    <resultMap id="classMap" type="classInfo">
        <id property="cid" column="c_id"></id>
        <result property="claName" column="c_name"/>
    </resultMap>

    <!--批量添加用户,
    参数是一个List集合的实体类型，遍历集合将一个实体数据插入到数据库中-->
    <insert id="addBachStudent"  parameterType="java.util.List" >
      insert into stu_info(s_name,s_age,s_sex,s_origin,s_tel,c_id)values
      <foreach collection="list" item="stu" separator=",">
          (#{stu.stuName},#{stu.age},#{stu.sex},#{stu.orgin},#{stu.tel},#{stu.clazz.cid})
      </foreach>
    </insert>
    <!--动态查询拼接sql语句,根据map集合中的key值与数据库中的值来进行匹配（test中的值就是集合中的
    key值），如果key的值不为空，就根据对应的value值来进行查询，如果为空就忽略if里面的语句-->
    <select id="dynamicQuery" resultMap="stuMap" parameterType="java.util.Map">
        select s_id ,s_name,s_age,s_sex,s_origin,s_tel,c_id from stu_info
        <where>
            <if test="sid !=null and sid !=''">
                and s_id = #{sid}
            </if>
            <if test="stuName !=null and stuName !=''">
                and s_name = #{stuName}
            </if>
            <if test="age !=null and age !=''">
                and s_age = #{age}
            </if>
            <if test="sex !=null and sex !=''">
                and s_sex = #{sex}
            </if>
            <if test="origin !=null and origin !=''">
                and s_origin = #{origin}
            </if>
        </where>
    </select>

    <!--动态选择条件查询,otherwise是可选参数-->
    <select id="dynamicQuery2" parameterType="java.util.Map" resultMap="stuMap">
        select s_id ,s_name,s_age,s_sex,s_origin,s_tel,c_id from stu_info
        <choose>
            <when test="sid != null">
                where s_id = #{sid}
            </when>
            <when test="stuName !=null and stuName!=''">
                where s_name = #{stuName}
            </when>
            <when test="age != null and age!=''">
                where s_age = #{age}
            </when>
            <otherwise>
                order by s_id desc
            </otherwise>
        </choose>
    </select>

    <!--根据集合中的所有ID来批量删除
    完整语句 ：delete from stu_info where 1=1 and s_id in(1,2,3,4)
    open:开始遍历时的拼接字符串
    close:结束时拼接的字符串
    separator:用逗号隔开1，2，3条件，在最后一个条件时会自动删除最后一个逗号-->
    <delete id="deleteStudent" parameterType="java.util.List" >
        delete from stu_info
        <where>
        <foreach collection="list" item="sid"  open="and s_id in (" close=") " separator=",">
         #{sid}
        </foreach>
        </where>
    </delete>


</mapper>

测试类

package edu.nf.mybatis2.dao.studaotest;

import edu.nf.mybatis2.dao.Impl.StuDaoImpl;
import edu.nf.mybatis2.dao.StuDaoInf;
import edu.nf.mybatis2.entity.ClassInfo;
import edu.nf.mybatis2.entity.StudentInfo;
import org.junit.Test;

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

/**
 * 描述:
 * 测试类
 *
 * @author lance
 * @create 2018-09-13 14:40
 */
public class StuDaoImplTest {

   
    //批量删除
    @Test
    public void testDeleteBachStudent(){
        List<Integer> list = new ArrayList<>();
        list.add(4);
        list.add(5);
        list.add(6);
        new StuDaoImpl().deleteStudent(list);
    }

    @Test
    public void testAddBachStudent(){
        //批量添加
        List<StudentInfo> list = new ArrayList<>();
       StudentInfo stu1 = new StudentInfo();
       stu1.setStuName("admin");
       stu1.setAge(19);
       stu1.setSex(1);
       stu1.setOrgin("广东信宜");
       stu1.setTel("1110");
        ClassInfo classInfo = new ClassInfo();
        classInfo.setCid(1);
       stu1.setClazz(classInfo);
        StudentInfo stu2 = new StudentInfo();
        stu2.setStuName("test");
        stu2.setAge(19);
        stu2.setSex(1);
        stu2.setOrgin("广东信宜电白");
        stu2.setTel("1110");
        ClassInfo classInfo1 = new ClassInfo();
        classInfo.setCid(1);
        stu2.setClazz(classInfo);
        list.add(stu1);
        list.add(stu2);
        StuDaoInf stuDaoInf = new StuDaoImpl();
        stuDaoInf.addBachStudent(list);
    }
    @Test
    public void testDynamicQuery(){
        Map<String,Object> map = new HashMap<>();
        map.put("stuName","stu1");
        StuDaoInf daoInf = new StuDaoImpl();
        //动态查询
        List<StudentInfo> list = daoInf.dynamicQuery(map);
        for (StudentInfo stu : list) {
            System.out.println(stu);
        }
    }

    @Test
    public void testDynamicQuery2(){
        Map<String,Object> map = new HashMap<>();
        map.put("stuName","stu1");
        StuDaoInf daoInf = new StuDaoImpl();
        //动态选择条件查询
        List<StudentInfo> list = daoInf.dynamicQuery2(map);
        System.out.println(list.size());
        for (StudentInfo stu : list) {
            System.out.println(stu);
        }
    }
}