Mybatis | 基础

//mapper.xml文件中的sql可以使用任意字符获取
<select id="selectEmp" resultType="Emp">
    select id,username from emp where id=#{ahdad} 
</select>

//mapper.xml文件中的sql可以使用：
//id=====> #{arg0}或者#{param1}
//username=====>#{arg1}或者#{param2}

<select id="selectEmp" resultType="Emp">
    select id,username from emp where id=#{arg0} and username=#{param2}
</select>

//当使用了@Param,mapper.xml文件中的sql可以使用：
//id=====> #{×××}或者#{param1}
//username=====>#{username}或者#{param2}

<select id="selectEmp" resultType="Emp">
    select id,username from emp where id=#{×××} and username=#{param2}
</select>

<resultMap id="emp_map" type="Emp"> //id是resultMap唯一标识，type是pojo对象
    <id column="id" property="id"></id> //只能是主键，column是表字段，property是实体中的字段
    <result column="user_name" property="username"></result>
    <result column="create_date" property="cjsj"></result>
</resultMap>

<select id="selectEmp" resultMap="emp_map">
    select id,user_name,create_date from emp where id=#{id}
</select>

//嵌套结果,association是多对一中的一
public class Emp{
    private Integer id;
    private String userName;
    private Dept dept;
}
public class Dept{
    private Integer id;
    private String deptName
}

<resultMap id="QueryEmp_Map" type="Emp">
    <id column="e_id" property="id"></id>
    <result column="user_name" property="userName"></result>
    <association property="dept">
       <id column="d_id" property="id"></id>
       <result column="dept_name" property="deptName"></result>
    </association>
</resultMap>

 <select id="QueryEmp" resultMap="QueryEmp_Map">
    select t1.id as e_id,t1.user_name,t2.id as d_id,t2.dept_name from emp t1 inner join dept t2 on t1.dept_id=t2.id where t1.id=#{id}
</select>

//嵌套查询(先查一张表，再查另一张表)，association是多对一中的一
<resultMap id="QueryEmp_Map2" type="Emp">
    <id column="id" property="id"></id>
    <result column="user_name" property="userName"></result>
    //association中的column作为参数传到分步查询的sql中
    <association property="dept" column="dept_id" select="DeptMapper.selectDept">
    </association>
</resultMap>

<select id="QueryEmp2" resultMap="QueryEmp_Map2">
    select * from emp where id=#{id}
</select>

<select id="selectDept" resultType="Dept">
    select * from dept where id=#{id}
</select>

public class Emp{
    private Integer id;
    private String userName;
};
public class Dept{
    private Integer id;
    private String deptName;
    private List<Emp> emps;
};

<resultMap id="selectDeptAndEmps_Map" type="Dept">
    <id column="d_id" property="id"></id>
    <result column="dept_name" property="deptName"></result>
    <collection property="emps" ofType="Emp">
        <id column="e_id" property="id"></id>
        <result column="user_name" property="userName"></result>
    </collection>


<select id="selectDeptAndEmps" resultMap="selectDeptAndEmps_Map">
    select t1.id as d_id,t1.dept_name,t2.id as e_id,t2.user_name from dept t1 left join emp t2 on t1.id=t2.dept_id where t1.id=#{id}
</select>

//<where>标签，一般会配合<if>标签使用，有条件时它会自动在所有条件的前面加上where关键字，还会去掉所有条件前面的AND/OR，使用where标签的时候，每个语句最好都写上and或者or前缀
<where>
    <if> and 条件1</if>
    <if> and 条件2</if>
</where>

//choose、when、otherwise
<select id="QueryMap" resultType="Emp">
        select * from emp
        <where>
           <choose>
              <when test="dept_name=='经理'">
                 dept_id=1
              </when>
              <when test="dept_name=='普通员工'">
                 dept_id=2
              </when>
              <otherwise>
                 dept_id=#{id}
              </otherwise>
            </choose>
         </where>
</select>  

//foreach,collection是需要循环的list或array的参数名字，item 每次循环使用的接收变量，separator 分割符设置（每次循环在结尾添加什么分隔符，会自动去除最后一个结尾的分隔符），open 循环开始拼接的字符串，close循环结束添加的字符串，index 循环的下标的变量
<select id="QueryMap" resultType="Emp">
       select * from emp
       <where>
            <foreach collection="usernames" item="username" separator="," open=" user_name in (" close=")" index="i">
                  #{username}
            </foreach>
       </where>
</select>

//set,set用在update语句上面的，会自动加上set关键字，会自动去掉最后一个字段的逗号
<update id="update">
       update emp
       <set>
             <if test="username!=null and username!=''">
                 user_name=#{username},
             </if>
             <if test="deptId!=null and deptId!=''">
                 dept_id=#{deptId},
             </if>    
       </set>
       where id=#{id}
</update>

//模糊查询，空格可以拼接字符串或者可以拼接好再传进来或者CONCAT函数
 <select id="QueryEmp" resultType="Emp">
    select * from emp where user_name like '%' #{username} '%'
 </select>
//通过bind实现模糊查询
 <select id="QueryEmp" resultType="Emp">
    <bind name="_username" value="'%'+username+'%'" />
    select * from emp where user_name like #{_username} 
 </select>   


 //sql片段
 <sql id="selectEmp">
     select * from emp
 </sql>
 <select id="QueryEmp" resultType="Emp">
     <bind name="_username" value="'%'+username+'%'" />
    <include refid="selectEmp"></include> where user_name like #{_username} 
 </select>   

//sql片段2,通过include中的property来实现动态列的查询，这里是美元符号
<sql id="selectEmp">
     select ${columns} from emp
 </sql>
 <select id="QueryEmp" resultType="Emp">
     <bind name="_username" value="'%'+username+'%'" />
     <include refid="selectEmp">
         <property name="columns" value="id"/>
     </include> where user_name like #{_username} 
 </select> 

 //insert批量插入
<insert id="insertBatch">
     insert into emp (user_name,dept_id)
     values
     <foreach collection="emps" item="emp" separator=",">
     (#{emp.userName},#{emp.deptId})
     </foreach>
 </insert>