1.Resultmap (结果集映射)
1.1、问题:
-
属性名和字段名不一致?
数据库 id name pwd 实体类 id name password
1.2、解决:
<!-- 结果集映射 -->
<resultMap id="UserMap" type="User">
<!-- column数据库中的字段,property实体类中的属性 -->
<result column="id" property="id"/>
<result column="name" property="name"/>
<result column="pwd" property="password"/>
</resultMap>
<!--查询语句-->
<select id="getById" resultMap="UserMap" >
select * from mybatis.user where id=#{id};
</select>
1.3、作用:
resultMap
元素是 MyBatis 中最重要最强大的元素- ResultMap 的设计思想是,对简单的语句做到零配置,对于复杂一点的语句,只需要描述语句之间的关系就行了。
ResultMap
的优秀之处——你完全可以不用显式地配置它们。
1.4、高级映射
1.创建表
CREATE TABLE `teacher` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO teacher(`id`, `name`) VALUES (1, '吴老师'),(2,'张老师'),(3,'彭老师');
DELETE FROM teacher WHERE id=1;
CREATE TABLE `student` (
`id` INT(10) NOT NULL,`teacher`
`name` VARCHAR(30) DEFAULT NULL,
`tid` INT(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fktid` (`tid`),
CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1'),('2', '小红', '1'),('3', '小张', '1'), ('4', '小李', '1');
2.测试搭建环境
-
导入lombok
<dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.12</version> </dependency>
-
新建Teacher,Student实体类
@Data @AllArgsConstructor @NoArgsConstructor public class StudentUser { private int id; private String name; //多个学生可以是同一个老师,即多对一 private TeacherUser teacher; }
@Data @AllArgsConstructor @NoArgsConstructor public class TeacherUser { private int id; private String name; }
-
建立Mapper接口
public interface StudentMapper { }
public interface TeacherMapper { @Select("select * from teacher") List<TeacherUser> getTeacherUser(); }
-
建立Mapper.xml文件
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.mapper.StudentMapper"> </mapper>
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.mapper.TeacherMapper"> </mapper>
-
注册Mapper
<mappers> <mapper class="com.mapper.StudentMapper"/> <mapper class="com.mapper.TeacherMapper"/> </mappers>
-
测试查询
@Test public void getTeachertest(){ SqlSession sqlSession = MybatisUtil.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); List<TeacherUser> teacherUser = mapper.getTeacherUser(); for (TeacherUser user : teacherUser) { System.out.println(user); } sqlSession.close(); }
3.多对一处理
3.1、按查询嵌套处理
-
给StudentMapper接口增加方法
List<Student> getStudent();
-
编写StudentMapper.xml文件
<select id="getStudent" resultMap="StudentTeacher"> select * from student </select> <resultMap id="StudentTeacher" type="student"> <result column="id" property="id"/> <result column="name" property="name"/> <!--association关联属性 property属性名 javaType属性类型 column在多的一方的表中的列名--> <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/> </resultMap> <select id="getTeacher" resultType="teacher"> select * from teacher where id=#{id} </select>
-
测试
@Test public void getStudent(){ SqlSession sqlSession = MybatisUtil.getSqlSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List<Student> studentUser = mapper.getStudent(); for (Student student : studentUser) { System.out.println(student); } sqlSession.close(); }
3.2、按结果嵌套处理
-
给StudentMapper接口增加方法
List<Student> getStudent2();
-
编写StudentMapper.xml文件
<select id="getStudent" resultMap="StudentTeacher"> select s.id sid, s.name sname , t.name tname from student s,teacher t where s.tid = t.id </select> <resultMap id="StudentTeacher" type="student"> <result column="id" property="sid"/> <result column="name" property="sname"/> <!--关联对象property 关联对象在Student实体类中的属性--> <association property="teacher" javaType="Teacher"> <result property="name" column="tname"/> </association> </resultMap>
-
测试
@Test public void getStudent2(){ SqlSession sqlSession = MybatisUtil.getSqlSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List<Student> studentUser = mapper.getStudent2(); for (Student student : studentUser) { System.out.println("学生名"+student.getName()+"\t老师名:" + student.getTeacher().getName()); } sqlSession.close(); } }
-
小结
- 按照查询进行嵌套处理就像SQL中的子查询
- 按照结果进行嵌套处理就像SQL中的联表查询
4.一对多处理
4.1实体类编写
@Data
public class Student {
private int id;
private String name;
private int tid;
}
@Data
public class Teacher {
private int id;
private String name;
//一个老师多个学生
private List<Student> students;
4.2、按查询嵌套处理
-
增加接口
Teacher getTeacher(int id);
-
编写TeacherMapper.xml文件
<select id="getTeacher" resultMap="StudentTeacher"> select * from teacher where id=#{id} </select> <resultMap id="StudentTeacher" type="teacher"> <result column="id" property="id"/> <result column="name" property="name"/> <!--column是一对多的外键 , 写的是一的主键的列名--> <collection property="students" column="id" ofType="student" javaType="ArrayList" select="getStudent"/> </resultMap> <select id="getStudent" resultType="student"> select * from student where tid=#{id} </select>
-
测试
@Test public void getStudent(){ SqlSession sqlSession = MybatisUtil.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacherList = mapper.getTeacher(1); System.out.println(teacherList.getName()); System.out.println(teacherList.getStudents()); sqlSession.close(); }
4.3、按结果嵌套处理
-
增加接口
Teacher getTeacher2(int id);
-
编写TeacherMapper.xml文件
<!-- 按结果嵌套处理 --> <select id="getTeacher2" resultMap="StudentTeacher2"> select s.id sid,s.name sname,t.name tname from student s,teacher t where s.tid=t.id and t.id=#{id} </select> <resultMap id="StudentTeacher2" type="teacher"> <result column="tname" property="name"/> <!--关联对象property 关联对象在Student实体类中的属性--> <collection property="students" ofType="student"> <result column="sid" property="id"/> <result column="sname" property="name"/> </collection> </resultMap>
-
测试
@Test public void getStudent2(){ SqlSession sqlSession = MybatisUtil.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacherList = mapper.getTeacher2(1); System.out.println(teacherList.getName()); System.out.println(teacherList.getStudents()); sqlSession.close(); }
4.4、小结
1、关联-association
2、集合-collection
3、所以association是用于一对一和多对一,而collection是用于一对多的关系
4、JavaType和ofType都是用来指定对象类型的
- JavaType是用来指定pojo中属性的类型
- ofType指定的是映射到list集合属性中pojo的类型。
4.5、注意说明:
1、保证SQL的可读性,尽量通俗易懂
2、根据实际要求,尽量编写性能更高的SQL语句
3、注意属性名和字段不一致的问题
4、注意一对多和多对一 中:字段和属性对应的问题
5、尽量使用Log4j,通过日志来查看自己的错误
2.日志
2.1、日志工厂
- 如果一个数据库操作,出现了异常,我们需要排错。日志就是最好的助手!
- 曾经:sout、debug
- 现在:日志工厂
- 在mybatis中具体使用那一个日志实现,在设置中设定!
2.2、种类
- SLF4J
- LOG4J
- LOG4J2
- JDK_LOGGING
- COMMONS_LOGGING
- STDOUT_LOGGING
- NO_LOGGING
2.3、STDOUT_LOGGING 标准日志输出
-
配置:
<!-- 标准日志输出 --> <settings> <setting name="logImpl" value="STDOUT_LOGGING"/> </settings>
-
控制台输出:
Opening JDBC Connection Created connection 811760110. Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@306279ee] ==> Preparing: select * from mybatis.user where id=?; ==> Parameters: 1(Integer) <== Columns: id, name, pwd <== Row: 1, 吴明, 147258 <== Total: 1 User{id=1, name='吴明', password='147258'} Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@306279ee] Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@306279ee] Returned connection 811760110 to pool.
2.4、LOG4J
-
什么是LOG4J
-
导入包
<!-- https://mvnrepository.com/artifact/log4j/log4j --> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency>
-
编写配置文件
#将等级为DEBUG的日志信息输出到console和file这两个目的地,console和file的定义在下面的代码 log4j.rootLogger=DEBUG,console,file #控制台输出的相关设置 log4j.appender.console = org.apache.log4j.ConsoleAppender log4j.appender.console.Target = System.out log4j.appender.console.Threshold=DEBUG log4j.appender.console.layout = org.apache.log4j.PatternLayout log4j.appender.console.layout.ConversionPattern=[%c]-%m%n #文件输出的相关设置 log4j.appender.file = org.apache.log4j.RollingFileAppender log4j.appender.file.File=./log/wu.log log4j.appender.file.MaxFileSize=10mb log4j.appender.file.Threshold=DEBUG log4j.appender.file.layout=org.apache.log4j.PatternLayout log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n #日志输出级别 log4j.logger.org.mybatis=DEBUG log4j.logger.java.sql=DEBUG log4j.logger.java.sql.Statement=DEBUG log4j.logger.java.sql.ResultSet=DEBUG log4j.logger.java.sql.PreparedStatement=DEBUG
-
setting设置日志实现
<settings> <setting name="logImpl" value="LOG4J"/> </settings>
-
lod4j的简单使用
static Logger logger = Logger.getLogger(MybatisUtilsTest.class); @Test public void Log4jTest(){ logger.info("info:进入log4j"); logger.debug("debug:进入log4j"); logger.error("error:进入log4j"); }
- 注意导包:org.apache.log4j.Logger
-
结果
-
控制台
[com.mapper.MybatisUtilsTest]-info:进入log4j [com.mapper.MybatisUtilsTest]-debug:进入log4j [com.mapper.MybatisUtilsTest]-error:进入log4j
-
wu.log为文件
[INFO][20-08-25][com.mapper.MybatisUtilsTest]info:进入log4j [DEBUG][20-08-25][com.mapper.MybatisUtilsTest]debug:进入log4j [ERROR][20-08-25][com.mapper.MybatisUtilsTest]error:进入log4j
-
3.分页
分页的作用:减少数据的处理量
3.1、limit实现
-
mappe接口
//分页 User getUserByLimit(Map<String ,Object> map);
-
mapper文件
<select id="getUserByLimit" resultMap="UserMap" parameterType="map"> select * from mybatis.user limit #{startindex},#{pagesize} ; </select>
-
测试
@Test public void getUserByLimit(){ SqlSession sqlSession = MybatisUtil.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); HashMap<String, Object> map = new HashMap<String, Object>(); map.put("startindex",0); map.put("pagesize",2); List<User> list = mapper.getUserByLimit(map); for (User user : list) { System.out.println(user); } sqlSession.close(); }
3.2、Rowbounds实现
-
mapper接口
List<User> getUserByRowBounds();
-
mapper文件
<select id="getUserByRowBounds" resultType="user"> select * from user </select>
-
测试
@Test public void getUserByRowBounds(){ SqlSession sqlSession = MybatisUtil.getSqlSession(); int currentPage = 2; //第几页 int pageSize = 2; //每页显示几个 RowBounds rowBounds = new RowBounds((currentPage-1)*pageSize,pageSize); //通过java代码层面实现 List<Object> userList = sqlSession.selectList("com.mapper.UserMapper.getUserByRowBounds", null, rowBounds); for (Object user : userList) { System.out.println(user); } sqlSession.close(); }
3.3、分页插件实现PageHelper
地址:https://pagehelper.github.io