Mybatis基础（四）

Mybatis基础（四）

标签与动态SQL

常用标签

• if 标签

  <if test="stuNos!=null and stuNos.size>0"></if>
  <!-- 
  类似于 Java 中的 if 语句 
  test: 逻辑判断表达式
  -->
  

• where标签

  <select id="queryStudentsWithNosInGrade" parameterType="grade" resultType="student">
      select * from student 
      <where>
          <if test="stuNos!=null and stuNos.size>0">
              <foreach collection="stuNos" open=" and  stuno in (" close=")" 
                       item="stuNo" separator=",">   
                  #{stuNo}
              </foreach>
          </if>
      </where>
  </select>
  <!-- 
  select * from student 
  open:
  select * from student and  stuno in (
  item:
  select * from student and  stuno in (1253
  close:
  select * from student and  stuno in (1,2,53) 
  
  <where>标签 会自动处理<if>标签中的第一个 and，不会处理之后<if>中的and
  -->
  

• foreach标签

  <select id="queryStudentsWithNosInGrade" parameterType="grade" resultType="student">
      select * from student 
      <where>
          <if test="stuNos!=null and stuNos.size>0">
              <foreach collection="stuNos" open=" and  stuno in (" close=")" 
                       item="stuNo" separator=",">
                  #{stuNo}
              </foreach>
          </if>
      </where>
  </select>
  <!-- 
  select * from student 
  open:
  select * from student and  stuno in (
  item:
  select * from student and  stuno in (1253
  close:
  select * from student and  stuno in (1,2,53) 
  
  foreach 参数：
  collection：要迭代的集合或数组
  open：左边
  item：集合或数组的每一项
  separator：每一项的分隔符
  close：右边
  实质：SQL的拼接
  -->
  

动态SQL

---

输入参数为对象的属性

• Student.java

  public class Student {
      private int stuNo ; //学号
      private String stuName ;
      private int stuAge ;
      private String graName ;
  }
  

  Grade.java

  public class Grade {
      //学号
      private List<Integer> stuNos ;
      //...
  
      public List<Integer> getStuNos() {
          return stuNos;
      }
  
      public void setStuNos(List<Integer> stuNos) {
          this.stuNos = stuNos;
      }
  }
  

  Mapper.xml

  <!-- 将多个元素值 放入对象的属性中 -->
  <select id="queryStudentsWithNosInGrade" parameterType="grade" resultType="student">
      select * from student
      <where>
          <if test="stuNos!=null and stuNos.size>0">
              <foreach collection="stuNos" open=" and  stuno in (" close=")" 
                       item="stuNo" separator=",">   
                  #{stuNo}
              </foreach>
          </if>
      </where>
  </select>
  

  接口方法

  List<Student> queryStudentsWithNosInGrade(Grade grade);
  

  StudentTest.java

  //查询全部学生
  public static void queryStudentsWithNosInGrade() throws IOException {
      //Connection -  SqlSession操作MyBatis
      //conf.xml - > reader
      Reader reader = Resources.getResourceAsReader("conf.xml") ;
      //reader  ->SqlSession
      //可以通过build的第二参数 指定数据库环境
      SqlSessionFactory sessionFacotry = new SqlSessionFactoryBuilder().build(reader,"development") ;
      SqlSession session = sessionFacotry.openSession() ;
  
      StudentMapper studentMapper = session.getMapper( StudentMapper.class) ;
      Grade grade = new Grade();
      List<Integer> stuNos = new ArrayList<>();
      stuNos.add(1) ;
      stuNos.add(2) ;
      stuNos.add(53) ;
  
      grade.setStuNos(stuNos);
      List<Student> students = studentMapper.queryStudentsWithNosInGrade(grade) ;	
  
      System.out.println(students);
      session.close();
  }
  

输入参数为简单数组

• Mapper.xml

  <!-- 将多个元素值 放入数组中 int[] stuNos = {1,2,53} -->
  <select id="queryStudentsWithArray"  parameterType="int[]" resultType="student">
      select * from student 
      <where>
          <if test="array!=null and array.length">
              <foreach collection="array" open=" and  stuno in (" close=")" 
                       item="stuNo" separator=",">   
                  #{stuNo}
              </foreach>
          </if>
      </where>
  </select>
  

  • 小结

    1、输入参数为简单数组时，if标签中的test属性为array，如： 
    <if test="array!=null and array.length">
    
    2、输入参数为简单数组时，foreach标签中的collection属性值为 array，如：
    <foreach collection="array" open=" and  stuno in (" close=")" 
    	item="stuNo" separator=",">   
    	#{stuNo}
    </foreach>
    

• 接口方法

  List<Student> queryStudentsWithArray(int[] stuNos);
  

• StudentTest.java

  StudentMapper studentMapper = session.getMapper( StudentMapper.class) ;
  int[] stuNos = {1,2,53};
  List<Student> students = studentMapper.queryStudentsWithArray(stuNos) ;
  //接口的方法->SQL
  System.out.println(students);
  session.close();
  

输入参数为对象数组

• Mapper.xml

  <!-- 将多个元素值 放入对象数组中Student[] students = {student0,student1,student2}  每个studentx包含一个学号属性 -->
  <select id="queryStudentsWithObjectArray"  parameterType="Object[]" resultType="student">
      select * from student
      <!--如果sql片段和  引用处不在同一个文件中，则需要 在refid 引用时  加上namespace:   namespace.id
      <include refid="org.lanqiao.mapper.abcMapper.objectArrayStunos"></include> -->
      <!-- 此处涉及SQL片段，下面有讲 -->
      <include refid="objectArrayStunos"></include>
  </select>
  

• 接口方法

  List<Student> queryStudentsWithObjectArray(Student[] students);
  

• StudentTest.java

  StudentMapper studentMapper = session.getMapper( StudentMapper.class) ;
  Student stu1 = new Student();
  stu1.setStuNo(1); 
  Student stu2 = new Student();
  stu2.setStuNo(2); 
  Student stu53 = new Student();
  stu53.setStuNo(53); 
  Student[] stus = new Student[] {stu1,stu2,stu53};
  List<Student> students = studentMapper.queryStudentsWithObjectArray(stus) ;
  //接口的方法->SQL
  
  System.out.println(students);
  session.close();
  

输入参数为集合

• Mapper.xml

  <!-- 将多个元素值 放入数组中 List<Integer> stuNos 值 {1,2,53} -->
  <select id="queryStudentsWithList"  parameterType="list" resultType="student">
      select * from student 
      <where>
          <if test="list!=null and list.size>0">
              <foreach collection="list" open=" and  stuno in (" close=")" 
                       item="stuNo" separator=",">   
                  #{stuNo}
              </foreach>
          </if>
      </where>
  </select>
  

• 接口方法

  List<Student> queryStudentsWithList(List<Integer> stuNos);
  

• StudentTest.java

  StudentMapper studentMapper = session.getMapper( StudentMapper.class) ;
  //int[] stuNos = {1,2,53};
  List<Integer> stuNos = new ArrayList<>();
  stuNos.add(1) ;
  stuNos.add(2) ;
  stuNos.add(53) ;
  
  List<Student> students = studentMapper.queryStudentsWithList(stuNos) ;
  //接口的方法->SQL
  
  System.out.println(students);
  session.close();
  

SQL片段

• Mapper.xml

  • 用法：在 <sql id=""></sql>中声明，在需要应用的地方，通过id进行引用。

    <mapper namespace="org.lanqiao.mapper.StudentMapper">
        <!-- SQL片段 -->
        <sql id="objectArrayStunos">
            <where>
                <if test="array!=null and array.length>0">
                    <foreach collection="array" open=" and  stuno in (" close=")" 
                             item="student" separator=",">   
                        #{student.stuNo}
                    </foreach>
                </if>
            </where>
        </sql>
    
        <!-- 将多个元素值 放入对象数组中Student[] students = {student0,student1,student2}  每个studentx包含一个学号属性 -->
        <select id="queryStudentsWithObjectArray"  parameterType="Object[]" resultType="student">
            select * from student 
            <!--如果sql片段和  引用处不在同一个文件中，则需要 在refid 引用时  加上namespace:   namespace.id
        <include refid="org.lanqiao.mapper.abcMapper.objectArrayStunos"></include> -->
            <include refid="objectArrayStunos"></include>
        </select>
    
    </mapper>
    

---

一对一、一对多的处理（关联查询）

多条件联合查询（关联查询、联合查询），实现方式有两种：
	方式一：
		业务扩展类
		核心：用 resultType 指定类的属性 包含 多表查询的所有字段，用类的继承实现
		适合涉及的表（类）较少的场景
	方式二（推荐使用）：
		resultMap关系映射
		通过 属性成员（成员变量） 将2个类建立起关系
		适合复杂的场景，涉及较多的表（类）

备注：表与表之间是通过外键建立起关系的，类和类之间是通过成员属性（成员变量）建立起关系的

• 一对一的处理

  • 方式一：业务扩展类

    • studentMapper.xml

      <!-- 利用业务扩展类实现一对一 -->
      <select id="queryStudentByNoWithOO" parameterType="int" resultType="StudentBusiness" >
          select s.*,c.* from student s  inner join studentcard c
          on s.cardid=c.cardid
          where s.stuno = #{stuNo}
      </select>
      

    • 实体类（业务扩展类）

      • StudentBusiness.java

        public class StudentBusiness  extends Student{	//学生业务扩展类
            private int cardId;
            private String cardInfo ;
            public int getCardId() {
                return cardId;
            }
            public void setCardId(int cardId) {
                this.cardId = cardId;
            }
            public String getCardInfo() {
                return cardInfo;
            }
            public void setCardInfo(String cardInfo) {
                this.cardInfo = cardInfo;
            }
            @Override
            public String toString() {
                return super.toString() + "," + this.cardId + ","+this.cardInfo;
            }
        }
        

      • 接口方法

        //采用业务扩展类实现
        StudentBusiness queryStudentByNoWithOO(int stuno);
        

      • StudentTest

        //查询单个学生（采用业务扩展类）
        public static void queryStudentByNoWithOO() throws IOException {
            //Connection -  SqlSession操作MyBatis
            //conf.xml - > reader
            Reader reader = Resources.getResourceAsReader("conf.xml");
            //reader  ->SqlSession
        
            //可以通过build的第二参数 指定数据库环境
            SqlSessionFactory sessionFacotry = new SqlSessionFactoryBuilder().build(reader,"development");
            SqlSession session = sessionFacotry.openSession();
        
            StudentMapper studentMapper = session.getMapper(StudentMapper.class);
            StudentBusiness studentBusiness = studentMapper.queryStudentByNoWithOO(2);
            //接口中的方法->SQL语句
        
            System.out.println(studentBusiness);
            session.close();
        }
        

    • 方式二：resultMap关系映射实现

      • studentMapper.xml

        <!-- 利用resultMap实现一对一 -->
        <select id="queryStudentByNoWithOO2" 	parameterType="int"  	resultMap="student_card_map" >
            select s.*,c.* from student s  inner join studentcard c
            on s.cardid=c.cardid
            where s.stuno = #{stuNo}
        </select>
        
        <resultMap type="student" id="student_card_map">
            <!-- 学生的信息 -->
            <id  property="stuNo" column="stuNo"/>
            <result property="stuName" column="stuName" />
            <result property="stuAge" column="stuAge" />
            <!-- 一对一时，对象成员使用 association映射; javaType指定该属性的类型 -->
            <association property="card" javaType="StudentCard" >
                <id property="cardId" column="cardId"/>
                <result property="cardInfo" column="cardInfo"/>
            </association>
        </resultMap>
        

      • 实体类

        • StudentCard.java

          //学生证
          public class StudentCard {
              private int cardId;
              private String cardInfo ;
          
              public int getCardId() {
                  return cardId;
              }
              public void setCardId(int cardId) {
                  this.cardId = cardId;
              }
              public String getCardInfo() {
                  return cardInfo;
              }
              public void setCardInfo(String cardInfo) {
                  this.cardInfo = cardInfo;
              }
          }
          

        • Student.java

          //学生类 包含：1 学生信息    2 学生证信息
          public class Student {
              // 1 学生信息
              private int stuNo ;
              private String stuName ;
              private int stuAge ;
          
              // 2 学生证信息
              private StudentCard card ;
          
              public StudentCard getCard() {
                  return card;
              }
          
              public void setCard(StudentCard card) {
                  this.card = card;
              }
          
              public int getStuNo() {
                  return stuNo;
              }
          
              public void setStuNo(int stuNo) {
                  this.stuNo = stuNo;
              }
          
              public String getStuName() {
                  return stuName;
              }
          
              public void setStuName(String stuName) {
                  this.stuName = stuName;
              }
          
              public int getStuAge() {
                  return stuAge;
              }
              public void setStuAge(int stuAge) {
                  this.stuAge = stuAge;
              }
          
              @Override
              public String toString() {
                  return stuNo + "-" + this.stuName+"-" + this.stuAge+"-" + this.card.getCardId() + "-"+this.card.getCardInfo();
              }
          }
          

      • 接口方法

        //resultMap关系映射
        Student queryStudentByNoWithOO2(int stuNo);
        

      • StudentTest

        //一对一关联查询，resultMap
        public static void queryStudentByNoWithOO2() throws IOException {
            //Connection -  SqlSession操作MyBatis
            //conf.xml - > reader
            Reader reader = Resources.getResourceAsReader("conf.xml");
            //reader  ->SqlSession
        
            //可以通过build的第二参数 指定数据库环境
            SqlSessionFactory sessionFacotry = new SqlSessionFactoryBuilder().build(reader,"development");
            SqlSession session = sessionFacotry.openSession();
        
            StudentMapper studentMapper = session.getMapper(StudentMapper.class);
            Student student = studentMapper.queryStudentByNoWithOO2(2);
            //接口中的方法->SQL语句
        
            System.out.println(student);
            session.close();
        }
        

• 一对多的处理

  • 实现方式：resultMap关系映射

    • studentMapper.xml

      <!-- 一对多 resultMap -->
      <select id="queryClassAndStudents" parameterType="int" resultMap="class_student_map">
          <!-- 查询 g1 班的班级信息，和 g1 班的所有学生信息 -->
          select  c.*,s.* from student s
          inner join studentclass c 
          on c.classid = s.classid
          where c.classid = #{classId}
      </select>
      
      <!-- 类-表的对应关系 -->
      <resultMap type="studentClass" id="class_student_map">
          <!-- 因为 type的主类是班级，因此先配置班级的信息-->
          <id  property="classId" column="classId"/>
          <result  property="className" column="className"/>
      
          <!-- 配置成员属性学生，一对多;属性类型：javaType，属性的元素类型ofType -->
          <collection property="students" ofType="student">
              <id  property="stuNo" column="stuNo"/>
              <result  property="stuName" column="stuName"/>
              <result  property="stuAge" column="stuAge"/>
          </collection>
      
      </resultMap>
      

      • 小结

        配置成员属性 students，学生和班级是一对多的关系; 
        如果学生这个属性（成员变量）的类型不是一个集合，即是一个对象类型，则属性类型用：javaType，
        如果学生这个属性（成员变量）的类型是一个集合，属性的元素类型：ofType
        

    • 实体类

      • StudentClass.java

        import java.util.List;
        
        public class StudentClass {
            private int classId;
            private String className;
        
            //增加学生属性 (通过该字段 让Student类和StudentClass类建立起关联)
            List<Student> students;
        
            public List<Student> getStudents() {
                return students;
            }
        
            public void setStudents(List<Student> students) {
                this.students = students;
            }
        
            public int getClassId() {
                return classId;
            }
        
            public void setClassId(int classId) {
                this.classId = classId;
            }
        
            public String getClassName() {
                return className;
            }
        
            public void setClassName(String className) {
                this.className = className;
            }
        }
        

      • 接口方法

        //根据班级 id 查询 班级信息 和 班级的所有学生信息
        StudentClass queryClassAndStudents(int classId);
        

      • StudentTest

        //查询班级 和班级对应的学生，一对多
        public static void queryClassAndStudents() throws IOException {
            //Connection -  SqlSession操作MyBatis
            //conf.xml - > reader
            Reader reader = Resources.getResourceAsReader("conf.xml") ;
            //reader  ->SqlSession
        
            //可以通过build的第二参数 指定数据库环境
            SqlSessionFactory sessionFacotry = new SqlSessionFactoryBuilder().build(reader,"development") ;
            SqlSession session = sessionFacotry.openSession() ;
        
            StudentMapper studentMapper = session.getMapper(StudentMapper.class) ;
            //班级
            StudentClass studentClass  = studentMapper.queryClassAndStudents(1) ;
        
            System.out.println(studentClass.getClassId() + ","+studentClass.getClassName());
            //班级对应的学生
            List<Student> students = studentClass.getStudents();
            for(Student student:students) {
                System.out.println(student.getStuNo() + ","+student.getStuName() + "," + student.getStuAge());
            }
            session.close();
        }
        

---

延迟加载（懒加载）

• 定义

  所谓延迟加载就是当在真正需要数据的时候，才真正执行数据加载操作。
  延迟加载，可以简单理解为，只有在使用的时候，才会发出sql语句进行查询。
  

• 作用

  延迟加载机制避免了一些无谓的性能开销。
  

延迟加载（一对一）

• studentMapper.xml

  <!-- 利用resultMap实现一对一 ，延迟加载-->
  <select id="queryStudentWithOOLazyLoad" 	parameterType="int"  	resultMap="student_card_lazyLoad_map" >
      <!-- 先查学生 -->
      select * from student
  </select>
  
  <resultMap type="student" id="student_card_lazyLoad_map">
      <!-- 学生的信息 -->
      <id  property="stuNo" column="stuNo"/>
      <result property="stuName" column="stuName" />
      <result property="stuAge" column="stuAge" />
      <!-- 一对一时，对象成员使用 association映射；javaType指定该属性的类型
      此次采用延迟加载：在查询学生时，并不立即加载 学生证信息
      -->
      <!-- 学生证  ,通过 select 在需要的时候再查学生证 -->
      <association property="card" javaType="StudentCard"  select="org.lanqiao.mapper.StudentCardMapper.queryCardById"  column="cardid"  >
      </association>
  </resultMap>
  

• StudentCardMapper.xml

  <!-- namespace: 该mapper.xml映射文件的 唯一标识 -->
  <mapper namespace="org.lanqiao.mapper.StudentCardMapper">
      <!-- 查询学生证信息 -->	
      <select id="queryCardById" parameterType="int"  resultType="studentCard">
          <!-- 查询学生对应的学生证 -->
          select * from studentCard  where cardid = #{cardId}
      </select>
      <!-- 根据cardid查询学生证的SQL： org.lanqiao.mapper.StudentCardMapper.queryCardById -->
  </mapper>
  

• conf.xml

  <configuration>
      <settings>
          <!-- 开启延迟加载 -->
          <setting name="lazyLoadingEnabled" value="true"/>
          <!-- 关闭立即加载 -->
          <setting name="aggressiveLazyLoading" value="false"/>
      </settings>
      
      <mappers>
  		<!-- 加载映射文件 -->
  		<mapper resource="org/lanqiao/mapper/studentMapper.xml"/>
  		<mapper resource="org/lanqiao/mapper/studentCardMapper.xml"/>
  	</mappers>
      
  </configuration>
  

  • 注意：新增的Mapper.xml文件要在conf.xml中加载

• StudentMapper.java

  //查询所有学生，延迟加载学生证
  List<Student> queryStudentWithOOLazyLoad();
  

• StudentTest.java

  //查询全部学生，并延迟加载 每个学生对应的学生证
  public static void queryStudentWithOOLazyLoad() throws IOException {
      //Connection -  SqlSession操作MyBatis
      //conf.xml - > reader
      Reader reader = Resources.getResourceAsReader("conf.xml") ;
      //reader  ->SqlSession
  
      //可以通过build的第二参数 指定数据库环境
      SqlSessionFactory sessionFacotry = new SqlSessionFactoryBuilder().build(reader,"development");
      SqlSession session = sessionFacotry.openSession() ;
  
      //接口对应的Mapper文件
      StudentMapper studentMapper = session.getMapper(StudentMapper.class);
      //学生
      List<Student> students = studentMapper.queryStudentWithOOLazyLoad();
      //接口中的方法 -> SQL语句
  
      for(Student student:students) {
          System.out.println(student.getStuNo()+","+student.getStuName());
  
          //获取学生证
          StudentCard card = student.getCard() ;
          System.out.println(card.getCardId()+","+card.getCardInfo());
      }
      session.close();
  }
  

延迟加载（一对多）

• studentMapper.xml

  <!-- 一对多,延迟加载需要的： 查询班级中的所有学生 -->
  <select id="queryStudentsByClassId" parameterType="int" resultType="student">
      select * from student where classId = #{classId}
  </select>
  

• StudentClassMapper.xml

  <!-- namespace:该mapper.xml映射文件的 唯一标识 -->
  <mapper namespace="org.lanqiao.mapper.StudentClassMapper">
  
      <!-- 一对多，带延迟加载 -->
      <select id="queryClassAndStudents"   resultMap="class_student_lazyLoad_map">
          <!-- 11111 先查询班级 -->
          select  c.* from studentclass c
      </select>
  
      <!-- 类-表的对应关系 -->
      <resultMap type="studentClass" id="class_student_lazyLoad_map">
          <!-- 因为 type的主类是班级，因此先配置班级的信息-->
          <id  property="classId" column="classId"/>
          <result  property="className" column="className"/>
          <!-- 配置成员属性学生，一对多;属性类型：javaType，属性的元素类型ofType -->
          <!-- 22222 再查班级对应的学生 -->
          <collection property="students" ofType="student" select="org.lanqiao.mapper.StudentMapper.queryStudentsByClassId" column="classid">
          </collection>
      </resultMap>
  </mapper>
  

  • 注意：新增的Mapper.xml文件要在conf.xml中加载

• StudenClasstMapper.java

  //操作Mybatis的接口
  public interface StudentClassMapper {
      //查询全部班级的接口方法
      List<StudentClass> queryClassAndStudents();
  }
  

• StudentTest.java

  //一对多，延迟加载
  //接口对应的Mapper文件
  StudentClassMapper studentClassMapper = session.getMapper(StudentClassMapper.class);
  //班级
  List<StudentClass> studentClasses  = studentClassMapper.queryClassAndStudents();
  
  //班级信息
  for(StudentClass stuClass :studentClasses) {
      System.out.println(stuClass.getClassId()+","+stuClass.getClassName());
  
      System.out.println("-----------");
      for(Student student:  stuClass.getStudents()) {
          System.out.println(student.getStuNo()+","+student.getStuName());
      }	
  

日志

概述

网络设备、系统及服务程序等，在运作时都会产生一个叫log的事件记录；每一行日志都记载着日期、时间、使用者及动作等相关操作的描述。

分类

应用程序日志，安全日志、系统日志、Scheduler服务日志、DNS服务器日志等。

日志级别

级别从低到高为：
DEBUG（调试信息）< INFO（提示信息）< WARN（警告信息）< ERROR（错误信息）

如果设置为info，则只显示 info及以上级别的信息，即：WARN、ERROR；
建议：在开发时设置debug，在运行时设置为info或以上。

使用步骤（以 log4j 为例）

• 导入log4j.jar （ mybatis.zip 中 lib 中包含此j ar ）

• 在conf.xml中指定和开启日志

  <configuration>
      <settings>
          <!-- 开启日志，并指定使用的具体日志 -->
          <setting name="logImpl" value="LOG4J"/>
      </settings>
  </configuration>
  

  • 注意：如果不指定日志，MyBatis 则按以下顺序查找日志

    SLF4J → Apache Commons Logging → Log4j 2 → Log4j → JDK logging
    

• 编写配置日志输出文件log4j.properties

  # 日志级别为 debug，在控制台输出日志信息（一般只配置这个）
  log4j.rootLogger=DEBUG, stdout
  log4j.appender.stdout=org.apache.log4j.ConsoleAppender
  log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
  log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n