mybatis动态sql

 This XML file does not appear to have any style information associated with it. The document tree is shown below.


<mapper namespace="com.atguigu.dao.TeacherDao">

<!--  使用mybatis默认二级缓存<cache></cache>  -->
<cache type="org.mybatis.caches.ehcache.EhcacheCache"/>

<resultMap type="com.atguigu.bean.Teacher" id="teacherMap">
<id property="id" column="id"/>
<result property="address" column="address"/>
<result property="birth" column="birth_date"/>
<result property="course" column="class_name"/>
<result property="name" column="teacherName"/>
</resultMap>

<!-- 抽取可重用的sql语句   -->
<sql id="selectSql">select * from t_teacher</sql>

<!-- public Teacher getTeacherById(Integer id);  -->
<!--  是否使用二级缓存的useCache="true"；默认是true  -->
<select id="getTeacherById" resultMap="teacherMap">
<include refid="selectSql"/>
where id=#{id}
</select>




<!--  if：判断  -->
<!-- public List<Teacher> getTeacherByCondition(Teacher teacher);  -->
<select id="getTeacherByCondition" resultMap="teacherMap">
select * from t_teacher

<!--  test=""：编写判断条件 id!=null：取出传入的javaBean属性中的id的值，判断其是否为空  -->

<!--  where可以帮我们去除掉前面的and;  -->

<!--  trim：截取字符串 
			prefix=""：前缀；为我们下面的sql整体添加一个前缀 
			prefixOverrides=""： 取出整体字符串前面多余的字符 
			suffix=""：为整体添加一个后缀 
			suffixOverrides=""：后面哪个多了可以去掉;  -->

<!--  我们的查询条件就放在where标签中；每个and写在前面，
			where帮我们自动取出前面多余的and  -->
			
<trim prefix="where" prefixOverrides="and" suffixOverrides="and">
<if test="id!=null"> id > #{id} and </if>

<!--  空串 "" and； && or: ||； if()：传入非常强大的判断条件；
<!--  绑定一个表达式的值到一个变量  -->
<!--  <bind name="_name" value="'%'+name+'%'"/>  -->
<if test="name!=null && !name.equals("")"> teacherName like #{_name} and </if>

<if test="birth!=null"> birth_date < #{birth} and </if>
</trim>
</select>




<!--  public List<Teacher> getTeacherByIdIn(List<Integer> ids);  -->
<select id="getTeacherByIdIn" resultMap="teacherMap">
SELECT * FROM t_teacher WHERE id IN

<!--  帮我们遍历集合的； collection=""：指定要遍历的集合的key 
		close=""：以什么结束 
		index="i"：索引； 
			如果遍历的是一个list； 
				index：指定的变量保存了当前索引 
				item：保存当前遍历的元素的值 
			如果遍历的是一个map： 
				index：指定的变量就是保存了当前遍历的元素的key 
				item：就是保存当前遍历的元素的值
		item="变量名"：每次遍历出的元素起一个变量名方便引用 
		open=""：以什么开始 
		separator=""：每次遍历的元素的分隔符 
			(#{id_item},#{id_item},#{id_item}  -->
			
<if test="ids.size >0">
<foreach collection="ids" item="id_item" separator="," open="(" close=")"> #{id_item} </foreach>
</if>
</select>



<!-- public List<Teacher> getTeacherByConditionChoose(Teacher teacher);  -->
<select id="getTeacherByConditionChoose" resultMap="teacherMap">
select * from t_teacher
<where>
<choose>
<when test="id!=null"> id=#{id} </when>
<when test="name!=null and !name.equals("")"> teacherName=#{name} </when>
<when test="birth!=null"> birth_date = #{birth} </when>
<otherwise> 1=1 </otherwise>
</choose>
</where>
</select>



<!--  public int updateTeacher(Teacher teacher);  -->
<update id="updateTeacher">
UPDATE t_teacher
<set>
<if test="name!=null and !name.equals("")"> teacherName=#{name}, </if>
<if test="course!=null and !course.equals("")"> class_name=#{course}, </if>
<if test="address!=null and !address.equals("")"> address=#{address}, </if>
<if test="birth!=null"> birth_date=#{birth} </if>
</set>
<where> id=#{id} </where>
</update>


</mapper>

package com.liang.test;

import com.liang.bean.Teacher;
import com.liang.dao.TeacherDao;
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 org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

public class TeacherTest {

    SqlSession session;

    @Before
    public void InitSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        session = sqlSessionFactory.openSession();
    }

    @Test
    public void getTeacherById() throws InterruptedException {

        TeacherDao teacherDao = session.getMapper(TeacherDao.class);
        Teacher teacher = teacherDao.getTeacherById(1);
        System.out.println(teacher);

        Thread.sleep(3000);
        getTeacherById();

    }
    @Test
    public void getTeacherByCondition(){

        TeacherDao teacherDao = session.getMapper(TeacherDao.class);
        Teacher teacher = new Teacher();
        teacher.setId(1);
        teacher.setName("%a%");
        teacher.setBirth(new Date());
        List<Teacher> teachers = teacherDao.getTeacherByCondition(teacher);
        System.out.println(teachers);


    }


    @Test
    public void getTeacherByIn(){

        TeacherDao teacherDao = session.getMapper(TeacherDao.class);
        List<Teacher> teachers = teacherDao.getTeacherByIn(Arrays.asList(1,2,3,4));
        System.out.println(teachers);


    }


    @Test
    public void getTeacherByChoose(){
        TeacherDao teacherDao = session.getMapper(TeacherDao.class);
        Teacher teacher = new Teacher();
        teacher.setId(1);
        teacher.setName("%a%");
        teacher.setBirth(new Date());
        List<Teacher> teachers = teacherDao.getTeacherByChoose(teacher);
        System.out.println(teachers);
    }

    @Test
    public void updateTeacher(){
        TeacherDao teacherDao = session.getMapper(TeacherDao.class);
        Teacher teacher = new Teacher();
        teacher.setId(1);
        teacher.setName("java");
//        teacher.setBirth(new Date());
        teacherDao.updateTeacher(teacher);
        session.commit();

    }


}