动态sql(一):xml

create table grade(
   gid number primary key,
   gname varchar2(20),
   sex number(1)
);


-- Add comments to the columns 
comment on column STUDENT.sex
  is '0男 1女';
  
create table student(
   sid number primary key,
   sname varchar2(20),
   gid number references grade(gid)
);


实体类：
public class Student {
private Integer sid;
private String sname;
private Integer gid;
private Integer sex;
public Integer getSid() {
return sid;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public Integer getGid() {
return gid;
}
public void setGid(Integer gid) {
this.gid = gid;
}
public Integer getSex() {
return sex;
}
public void setSex(Integer sex) {
this.sex = sex;
}
}


接口：
public interface StudentMapper {


/**
* 通过编号查询学生
* @param sid
* @return
*/
public List<Student> queryStudent(Student student);
/**
* 根据性别来查所有学生
* 参数中 传入sex 就根据条件查  没有传值  查所有女生
* @param sex
* @return
*/
public List<Student> queryBySex(@Param("sex")Integer sex);
/**
* 更新学生信息
* @param sid
* @return
*/
public void updateStudent(Student student);
/**
* 通过传入的班级查询所有学生
* 0,1,2
* 1,2
* 2,3,4
* 
* @param gradeList
*/
public List<Student> queryStudentbyAnyGrade(@Param("gradeList")List<Integer> gradeList);
}


映射文件：


<?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="cn.et.lesson4.xml.StudentMapper">
  
    <!-- 
      if 判断条件是否满足 满足 就将if中的sql 自动拼接到主sql
     
     where 自动判断第一个条件是否存在where 如果没有 where 追加 
          同时 去掉第一个and
     trim：     
              prefix配置的参数会被添加 在sql语句开始的地方
              prefixOverrides sql语句首次出现的参数会被覆盖
              
         <trim prefix="where" prefixOverrides="and"></trim>========<where>标签  
   -->
   
   <!--student:设置了类型别名-->
  <select id="queryStudent" resultType="student">
  select * from student 
  <where>
  <if test="gid!=null">
  and gid=${gid}
  </if>
  <if test="sname!=null">
  and sname like '%${sname}%'
  </if>
  </where>
  </select>
 
<!-- choose when otherwise :类似于if elseif else-->
  <select id="queryBySex" resultType="student">
  select * from student where 1=1
  <choose>
  <when test="sex!=null">
  and sex=#{sex}
  </when>
  <otherwise>
  and sex=1
  </otherwise>
  </choose>
  </select>
 
    <!-- 
    set标签   自动添加一个set  动态去掉最后一个条件的,
       trim实现set  <trim prefix="set" prefixOverrides="" suffixOverrides="," suffix=""> 
   -->
  <update id="updateStudent">
  update student 
  <set>
  <if test="sname!=null">
  sname=#{sname},
  </if>
  <if test="gid!=null">
  gid=#{gid},
  </if>
  <if test="sex!=null">
  sex=#{sex}
  </if>
  </set>
  where sid=#{sid}
  </update>
 
    <!-- 
     foreach 
       open表示 循环开始之前 追加的sql
       close表示 循环结束之后追加的sql
       collection指定传入的值得集合
       separator 非最后一次的循环 值上 添加 分隔符 ,
       item表示每一次循环的值被存储的变量名
   -->
  <select id="queryStudentbyAnyGrade" resultType="student">
  select * from student 
      <foreach item="gid" collection="gradeList" open="where gid in(" close=")" separator=",">
          #{gid}
      </foreach>
 
  </select>
 
</mapper>



//测试类
public class MyBatisTest {
private  SqlSession getSession() throws IOException {
// mybatis核心配置文件路径
String resource = "cn/et/lesson4/xml/mybatis.xml";


InputStream inputStream = Resources.getResourceAsStream(resource);
// 工厂类
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
.build(inputStream);
// session操作的是指向sql语句的一个唯一标识符
SqlSession openSession = sqlSessionFactory.openSession();
return openSession;
}


@Test
public  void selectStudent() throws IOException {
SqlSession openSession = getSession();
StudentMapper mapper = openSession.getMapper(StudentMapper.class);

Student student=new Student();

student.setGid(2);
//student.setSname("飞");
List<Student> stList = mapper.queryStudent(student);

for(Student stu:stList){
System.out.println(stu.getGid()+"---"+stu.getSname());
}
}
@Test
public  void selectStudentBySex() throws IOException {
SqlSession openSession = getSession();
StudentMapper mapper = openSession.getMapper(StudentMapper.class);

List<Student> stList = mapper.queryBySex(1);

for(Student stu:stList){
System.out.println(stu.getSname());
}
}

@Test
public  void updateStudent() throws IOException {
SqlSession openSession = getSession();
StudentMapper mapper = openSession.getMapper(StudentMapper.class);
Student student=new Student();

student.setSname("远走高飞");
student.setGid(2);
student.setSex(1);

mapper.updateStudent(student);

openSession.commit();
}


@Test
public  void queryStudentbyAnyGrade() throws IOException {
SqlSession openSession = getSession();
StudentMapper mapper = openSession.getMapper(StudentMapper.class);
Student student=new Student();

List<Integer> list=new ArrayList<>();
list.add(1);
list.add(3);

List<Student> sList = mapper.queryStudentbyAnyGrade(list);

for(Student stu:sList){
System.out.println(stu.getSname());
}
}
}