文章目录
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