动态SQL 动态SQL==根据不同的条件生成不同的SQL语句==ifchoose (when, otherwise)trim (where, set)foreach搭建环境CREATE TABLE `blog`( `id` VARCHAR(50) NOT NULL COMMENT 'id', `title` VARCHAR(100) NOT NULL COMMENT '标题', `author` VARCHAR(50) NOT NULL COMMENT '作者', `create_time` datetime NOT NULL COMMENT '创建时间', `views` int(11) NOT NULL COMMENT '浏览量' )ENGINE=INNODB DEFAULT CHARSET=utf8;1、导包2、编写配置文件3、创建实体类package com.sw.pojo; import lombok.Data; import java.io.Serializable; import java.util.Date; /** * @Author suaxi * @Date 2020/12/13 16:20 */ @Data public class blog implements Serializable { private String id; private String title; private String author; private Date createTime; private int views; } 4、编写实体类对应的Mapper接口和Mapper.xml文件IF<select id="findBlogIf" parameterType="map" resultType="Blog"> select * from blog where 1=1 <if test="title != null"> and title = #{title} </if> <if test="author != null"> and author = #{author} </if> </select>chose(when,otherwise)<select id="findBlogChoose" parameterType="map" resultType="Blog"> select * from blog <where> <choose> <when test="title != null"> title = #{title} </when> <when test="author != null"> and author = #{author} </when> <otherwise> and views = #{views} </otherwise> </choose> </where> </select>trim(where,set) <select id="findBlogIf" parameterType="map" resultType="Blog"> select * from blog <where> <if test="title != null"> title = #{title} </if> <if test="author != null"> and author = #{author} </if> </where> </select><update id="UpdateBlog" parameterType="map"> update blog <set> <if test="title != null"> title = #{title}, </if> <if test="author != null"> author = #{author} </if> </set> where id = #{id} </update>SQL片段将部分SQl抽取出来，方便复用1、使用SQL标签抽取公共部分<sql id="if-title-author"> <if test="title != null"> title = #{title} </if> <if test="author != null"> and author = #{author} </if> </sql>2、在需要使用的地方用include标签引用、<select id="findBlogIf" parameterType="map" resultType="Blog"> select * from blog <where> <include refid="if-title-author"></include> </where> </select>注：最好基于单表查询，不要存在where标签Foreach<!--select * from bolg where 1=1 and (id=1 or id=2 or id=3)--> <select id="findBlogForeach" parameterType="map" resultType="Blog"> select * from blog <where> <foreach collection="ids" item="id" open="and (" close=")" separator="or"> id = #{id} </foreach> </where> </select>==动态SQL就是在拼接SQL语句，先确保SQL的正确性，再按照SQL格式去排列组合==先在MySQL中写出完整的sql，再去修改成动态sql实现通用

一对多 一对多处理一个老师拥有多个学生（对老师而言，就是一对多的关系）1、新建实体类Teacherpackage com.sw.pojo; import lombok.Data; import java.io.Serializable; import java.util.List; /** * @Author suaxi * @Date 2020/12/13 10:55 */ @Data public class Teacher implements Serializable { private int id; private String name; //一个老师对应多个学生 private List<Student> students; } Studentpackage com.sw.pojo; import lombok.Data; import java.io.Serializable; /** * @Author suaxi * @Date 2020/12/13 10:55 */ @Data public class Student implements Serializable { private int id; private String name; private int tid; } 2、TeacherMapper.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.sw.dao.TeacherMapper"> <!--按结果集嵌套处理--> <select id="findTeacherById" resultMap="TeacherStudent"> select s.id sid,s.name sname,t.id tid ,t.name tname from student s,teacher t where s.tid = t.id and t.id = #{tid}; </select> <resultMap id="TeacherStudent" type="teacher"> <result property="id" column="tid"/> <result property="name" column="tname"/> <!-- 集合使用 collection javaType="" 指定属性的类型 集合中的泛型信息，使用 ofType 获取 --> <collection property="students" ofType="Student"> <result property="id" column="sid"/> <result property="name" column="sname"/> <result property="tid" column="tid"/> </collection> </resultMap> <!--按照查询嵌套处理--> <select id="findTeacherById2" resultMap="TeacherStudent2"> select * from teacher where id = #{tid}; </select> <resultMap id="TeacherStudent2" type="Teacher"> <collection property="students" javaType="ArrayList" ofType="Student" select="findStudentByTeacherId" column="id"/> </resultMap> <select id="findStudentByTeacherId" resultType="Student"> select * from student where tid = #{tid}; </select> </mapper>注：1、关联 - association 【多对一】2、集合 - collection 【一对多】3、javaType & ofTypejavaType用来指定实体类中属性的类型ofType用来指定映射到List或者集合中的pojo类型，泛型中的约束类型

多对一 多对一处理多对一：多个学生对应一个老师对学生而言：关联，关联一个老师【多对一】对老师而言，集合，一个老师有很多学生【一对多】Demo实例：CREATE TABLE `teacher`( `id` int(11) NOT NULL, `name` VARCHAR(50) DEFAULT NULL, PRIMARY KEY (id) )ENGINE=INNODB DEFAULT CHARSET=utf8; INSERT INTO teacher(`id`,`name`) VALUES(1,'孙笑川'); CREATE TABLE `student`( `id` int(11) NOT NULL, `name` VARCHAR(50) DEFAULT NULL, `tid` int(11) 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); INSERT INTO student(`id`,`name`,`tid`) VALUES(2,'小红',1); INSERT INTO student(`id`,`name`,`tid`) VALUES(3,'小蓝',1); INSERT INTO student(`id`,`name`,`tid`) VALUES(4,'小绿',1); INSERT INTO student(`id`,`name`,`tid`) VALUES(5,'小华',1);测试环境搭建：1、导入Lombok2、新建实体类Teacher，Student3、建立Mapper接口4、建立Mapper.xml5、在mybatis-config.xml核心配置文件中注册绑定接口6、测试查询是否成功按照嵌套查询处理：<mapper namespace="com.sw.dao.StudentMapper"> <select id="findStudent" resultMap="StudentTeacher"> select * from student; </select> <resultMap id="StudentTeacher" type="Student"> <result property="id" column="id"/> <result property="name" column="name"/> <!--复杂属性需要单独处理 对象：association 集合：collection --> <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/> </resultMap> <select id="getTeacher" resultType="Teacher"> select * from teacher where id = #{id}; </select> </mapper>按照结果嵌套处理：<!--按结果集嵌套处理--> <select id="findStudent2" resultMap="StudentTeacher2"> select s.id sid,s.name sname,t.name tname from student s,teacher t where s.tid = t.id; </select> <resultMap id="StudentTeacher2" type="Student"> <result property="id" column="sid"/> <result property="name" column="sname"/> <association property="teacher" javaType="Teacher"> <result property="name" column="tname"/> </association> </resultMap>

Lombok Lombok使用步骤：1、在IDEA中安装Lombok插件2、在pom.xml导入lombok依赖<dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.12</version> <scope>provided</scope> </dependency>3、在实体类上加注解说明：@Getter and @Setter @FieldNameConstants @ToString @EqualsAndHashCode @AllArgsConstructor, @RequiredArgsConstructor and @NoArgsConstructor @Log, @Log4j, @Log4j2, @Slf4j, @XSlf4j, @CommonsLog, @JBossLog, @Flogger, @CustomLog @Data @Builder @SuperBuilder @Singular @Delegate @Value @Accessors @Wither @With @SneakyThrows @val @var experimental @var @UtilityClass Lombok config system Code inspections Refactoring actions (lombok and delombok)@Data：无参构造，get、set、toString、hashcode、equals @NoArgsConstructor @AllArgsConstructor @EqualsAndHashCode @toString @Getter

CRUD（Mybatis注解开发） CRUD（Mybatis注解）1、在创建工具类时可以设置自动提交事务public static SqlSession getSqlSession(){ return sqlSessionFactory.openSession(true); } 2、接口package com.sw.dao; import com.sw.pojo.User; import org.apache.ibatis.annotations.Delete; import org.apache.ibatis.annotations.Param; import org.apache.ibatis.annotations.Select; import java.util.List; import java.util.Map; /** * @Author suaxi * @Date 2020/12/11 17:00 */ public interface UserMapper { //删除用户 @Delete("delete from user where id = #{uid}") int deleteUserById(@Param("uid")int id); //根据id查询 //注意：有多个参数时必须加上@Param("")注解 //例：根据id和用户名查询 //User finUser(@Param("id")int id,@Param("username")String name) @Select("select * from user where id = #{id}") User findUserById(@Param("id")int id); //查询所有用户 @Select("select * from user") List<User> findUser(); } 3、测试类package com.sw; import com.sw.dao.UserMapper; import com.sw.pojo.User; import com.sw.utils.MybatisUtils; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import java.util.List; /** * @Author suaxi * @Date 2020/12/12 21:49 */ public class UserMapperTest { @Test public void findUser(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); // List<User> userList = mapper.findUser(); // for (User user : userList) { // System.out.println(user); // } // User user = mapper.findUserById(1); // System.out.println(user); mapper.deleteUserById(4); sqlSession.close(); } } 4、注：在mybatis-config.xml中必须注册绑定接口<mappers> <mapper class="com.sw.dao.UserMapper"/> </mappers>5、关于@Parm()注解基本类型的参数或String类型需要加上引用类型不需要加如果只有一个基本类型的话可以忽略（本身就只有它一个值）在SQL中引用的就是@Param()中设定的属性名6、#{}与${}的区别#{}采用预编译的方式，相当于JDBC的占位符PreparedStatement一个#{}就是一个占位符Mybatis在为#{}设置值时会加引号${}采用直接拼接的方式，不对数值做预编译Mybatis在为${}设置值时不加引号存在sql注入的问题

分页 分页使用的Limit分页：--语法：SELECT * FROM USER LIMIT startIndex,pageSize SELECT * FROM USER LIMIT 0,2; --每页显示2条数据，从0开始查使用Mybatis分页：1、接口//分页 List<User> getUserByLimit(Map<String,Integer> map);2、UserMapper.xml<!--分页--> <select id="getUserByLimit" parameterType="map" resultMap="UserMap"> select * from user limit #{startIndex},#{pageSize}; </select>3、测试package com.sw.test; import com.sw.dao.UserMapper; import com.sw.pojo.User; import com.sw.utils.MybatisUtils; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @Author suaxi * @Date 2020/12/11 17:08 */ public class UserDaoTest { @Test public void getUserByLimit(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); Map<String, Integer> map = new HashMap<>(); map.put("startIndex",0); map.put("pageSize",2); List<User> userlist = mapper.getUserByLimit(map); for (User user : userlist) { System.out.println(user); } sqlSession.close(); } }RowBounds分页：不再使用sql实现分页1、接口//分页2 List<User> getUserByRowBounds();2、UserMapper.xml<!--分页2--> <select id="getUserByRowBounds" resultMap="UserMap"> select * from user; </select>3、测试package com.sw.test; import com.sw.dao.UserMapper; import com.sw.pojo.User; import com.sw.utils.MybatisUtils; import org.apache.ibatis.session.RowBounds; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @Author suaxi * @Date 2020/12/11 17:08 */ public class UserDaoTest { @Test public void getUserByRowBounds(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); //RowBounds RowBounds rowBounds = new RowBounds(0, 2); List<User> userList = sqlSession.selectList("com.sw.dao.UserMapper.getUserByRowBounds", null, rowBounds); for (User user : userList) { System.out.println(user); } sqlSession.close(); } } PageHelper链接：https://pagehelper.github.io/

日志（Mybatis） 日志1、日志工厂掌握LOG4J、STDOUT_LOGGINGSTDOUT_LOGGING标准日志输出在mybatis核心配置文件中配置：<settings> <setting name="logImpl" value="STDOUT_LOGGING"/> </settings>2、log4j什么是log4j？是Apache的一个开源项目，可以控制日志信息输送的目的地是控制台、文件、GUI组件可以控制每一条日志的输出格式定义每一条日志信息的级别可以灵活配置配置文件1、导入log4j依赖<!-- https://mvnrepository.com/artifact/log4j/log4j --> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency>2、log4j.properties#将等级为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/mybatis.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简单使用：1.在要使用log4j的类中，导入import org.apache.log4j.Logger;2.日志对象，参数为当前类的classstatic Logger logger = Logger.getLogger(UserDaoTest.class);3.日志级别logger.info("info:进入testLog4j方法"); logger.debug("debug:进入testLog4j方法"); logger.error("error:进入testLog4j方法");