继上篇mybatis基础
https://blog.csdn.net/weixin_45262118/article/details/108482350

1.版本切换

• 切换环境 (environment)

<environments default="development">//dafault 默认使用的数据库id<environment id="development">//数据库id<transactionManager type="JDBC"/><dataSource type="POOLED"><property name="driver" value="${driver}"/><property name="url" value="${url}"/><property name="username" value="${username}"/><property name="password" value="${password}"/></dataSource></environment></environments>

• 设置别名(大小写不能变)

  <!--别名  配置数据库支持类-->
  <databaseIdProvider type="DB_VENDOR"><property name="MySQL" value="mysql"/>//mysql为别名<property name="Oracle" value="oracle"/>
  </databaseIdProvider>
  

• z哎mapper.xml中写不同的数据库的SQL语句，并表明要使用的数据库别名

  <select id="queryStudentById" resultType="student" parameterType="int" databaseId="mysql">//通过databaseId 调用数据库别名select * from student where stuNo = #{id}
  </select>
  

如果既有不带databaseId的标签和带databaseId的标签，则数据库会优先使用带databaseId的标签

2.注解方式

• 在接口中，将要使用的SQL语句方法上写上注解**@Xxx("")**

@Select("select * from student where stuNo=#{stuNo}")
Student queryStudentById(int stuNo);

• 将接口的全类名写在mapper中，让MyBatis知道SQL语句此时是存储在接口中

注解/xml都支持批量引入

<mappers><!--<mapper resource="com/xiaoming/mapper/StudentMapper.xml"/><mapper class="com.xiaoming.mapper.StudentMapper"></mapper>--><package name="com.xiaoming.mapper"/>//写这句话就够了
</mappers>

3.增删改的返回值问题

返回值可以是void/Integer/Long/Boolean：只需要在接口中将返回值改变

4.事务的自动提交（推荐手工提交）

SqlSession sqlSession = sqlSessionFactory.openSession();
sqlSession.commit();//手动提交 进行增删改(dml)就要手动提交SqlSession sqlSession = sqlSessionFactory.openSession(true);//自动提交 不需要commit

5.自增问题

• MySQL支持自增，ID数据类型为Integer 因为int默认 没数据时为 0

<insert id="addStudent" parameterType="student" useGeneratedKeys="true" keyProperty="stuNo">//加入这两个属性即可insert into student values(#{stuNo},#{stuName},#{stuAge},#{graName} )
</insert>

• Oracle不支持自增: 通过序列模拟实现 序列自带两个属性：

  • nextval:序列中下一个值

  • currval:当前值

    创建一个序列

create sequence myseq increment by 1 start with 1;

方式一：Befor(推荐) 创建SQL语句

<insert id="addStudent" parameterType="student" databaseId="oracle"><selectKey keyProperty="stuNo" resultType="Integer" order="BEFORE">select myseq.nextval from dual //dual 虚拟表 </selectKey>insert into student values(#{stuNo},#{stuName},#{stuAge},#{graName} )
</insert>

方式二:After

<insert id="addStudent" parameterType="student" databaseId="oracle"><selectKey keyProperty="stuNo" resultType="Integer" order="AFTER">select myseq.currval from dual</selectKey>insert into student values(myseq.next ,#{stuName},#{stuAge},#{graName} )
</insert>

6.增加null

• oracle: 如果插入的字段时Null.提示错误： other 而不是 null；

• mysql:如果插入的字段时Null 可以执行(没有约束)

  • 原因：各个数据库 在MyBatis中 对各种数据类型的默认值不一样
  • mybatis中，jdbcTypeForNull(如果是null),则默认为other ,other对mysql能处理(null)oracle不能处理

<insert id="addStudent" parameterType="student" databaseId="oracle">
insert into student values(myseq.next ,#{stuName,jdbcType=NULL},#{stuAge},#{graName} )</insert>

#{stuName,jdbcType=NULL}，stuName既可以输入NULL，也可以不为NULL.

<settings><setting name="jdbcTypeForNull" value="NULL"/>
</settings>//推荐  在conf.xml中配置

7.鉴别器

相当于if判断 在SQL语句里面进行判断

<resultMap id="discriminator" type="com.xiaoming.entity.Student"><discriminator javaType="string" column="graName"><case value="G1" resultType="com.xiaoming.entity.Student"><result column="stuName" property="stuName1"></result></case>//如果graName=“G1” 则将stuName的值赋给stuName1<case value="G2" resultType="com.xiaoming.entity.Student"><result column="stuName" property="stuName2"></result></case>//如果graName=“G2” 则将stuName的值赋给stuName2</discriminator>
</resultMap>

8.SQL语句的拼接 动态SQL语句

只能处理【开头】第一个and

可以处理【开头或结尾】第一个and

​ 开头：

select stuNo,stuName,stuAge from student<trim prefix="where" prefixOverrides="and"><if test="stuAge != null and stuAge != 0 ">and stuAge=#{stuAge}</if><if test="stuName != null and stuName != ''">and stuName=#{stuName}</if></trim>

prefix=“where” 给拼接的SQL语句加where

prefixOverrides=“and” 处理拼接的第一个and

后面的拼接：

select stuNo,stuName,stuAge from student<trim prefix="where"suffixOverrides="and"><if test="stuAge != null and stuAge != 0 ">stuAge=#{stuAge} and</if><if test="stuName != null and stuName != ''">stuName=#{stuName} and</if></trim>

9.内置参数

• _parameter:代表mybatis的输入参数

  <select id="queryStuByLike" parameterType="student" resultType="student">select * from student<trim prefix="where" suffixOverrides="and"><if test="_parameter.stuName != null and _parameter.stuName != ''">stuName like '%${_parameter.stuName}%' and</if><if test="graName != null and graName !=''">graName like '%${graName}%' and</if><if test="stuAge != null and stuAge != ''">stuAge like '%${stuAge}'</if></trim>
  

• _databaseId:代表当前使用数据库的名

10.模糊查询

• ${}
  • **${**} : 原样输出 stuName like '${_parameter.stuName}%’ 会存在SQL注入
  • #{} : 自动拼接引号 字符串自动添加引号
• 直接传 %x%： stuName like #{_parameter.stuName} student.setstuName("%s%") 可以防止SQL注入
• bind 参数:

将传入的值放置在stuName 进行字符串拼接 --> ‘%s%’ 再将**’%s%‘赋值给_queryName ** 以后用**’%s%'就可以使用_queryName **

11.MyBatis Plus

• 导入依赖

<dependencies><!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus --><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus</artifactId><version>3.3.1</version></dependency><!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.47</version></dependency><!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-core --><dependency><groupId>log4j</groupId><artifactId>log4j</artifactId><version>1.2.17</version></dependency><!-- https://mvnrepository.com/artifact/com.mchange/c3p0 --><dependency><groupId>com.mchange</groupId><artifactId>c3p0</artifactId><version>0.9.5.2</version></dependency><!-- https://mvnrepository.com/artifact/org.springframework/spring-context --><dependency><groupId>org.springframework</groupId><artifactId>spring-context</artifactId><version>5.2.4.RELEASE</version></dependency><!-- https://mvnrepository.com/artifact/org.springframework/spring-orm --><dependency><groupId>org.springframework</groupId><artifactId>spring-orm</artifactId><version>5.2.4.RELEASE</version></dependency>
</dependencies>

• 数据表 类

• MyBatis 配置文件 mybatis.xml

  <settings><!--开启日志--><setting name="logImpl" value="LOG4J"/>
  </settings>
  

• 日志log4j信息

  # Global logging configuration 开发时候建议使用 debug
  log4j.rootLogger=DEBUG, stdout
  # Console output...
  log4j.appender.stdout=org.apache.log4j.ConsoleAppender
  log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
  log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
  

• 数据库连接信息 db.properties

  jdbc.driver=com.mysql.jdbc.Driver
  jdbc.url=jdbc:mysql://localhost:3306/mybatisplus
  jdbc.username=root
  jdbc.password=123456
  

• spring 配置信息 applicationContext.properties

  <?xml version="1.0" encoding="UTF-8"?>
  <beans xmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd"><context:property-placeholder location="classpath:db.properties"/><bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"><property name="driverClass" value="${jdbc.driver}"></property><property name="jdbcUrl" value="${jdbc.url}"></property><property name="user" value="${jdbc.username}"></property><property name="password" value="${jdbc.password}"></property></bean><!--事务管理器--><bean id="dataSourceTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"><property name="dataSource" ref="dataSource"></property></bean><tx:annotation-driven transaction-manager="dataSourceTransactionManager"></tx:annotation-driven><!--MyBatis<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"><property name="dataSource" ref="dataSource"></property><property name="configLocation" value="classpath:mybatis.xml"></property><property name="typeAliasesPackage" value="com.xm.entity"></property></bean>--><!--MyBatis plus--><bean id="sqlSessionFactory" class="com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean"><property name="dataSource" ref="dataSource"></property><property name="configLocation" value="classpath:mybatis.xml"></property><property name="typeAliasesPackage" value="com.xm.entity"></property></bean><!--MyBatis只写接口不写实现类 studentMapper对象 --><bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"><property name="basePackage" value="com.xm.mapper"></property></bean></beans>
  

1.CRUD操作

TMapper extends BaseMapper<T>
//Mapper 继承该接口后，无需编写 mapper.xml 文件，即可获得CRUD功能
<p>这个 Mapper 支持 id 泛型</p>public interface StudentMapper extends BaseMapper<Student> {
}

建议表里属性写_形式 例如 stu_no stu_name 表里ID值勾选自动递增

相应的类与之对应

@TableName("tb_student") //类名与表名不对应的时候
public class Student {@TableId(value="stuno",type = IdType.AUTO)//不插入ID值让其自增 在数据库里要勾选自增private int stuNo;@TableField(value = "stuname")//MyBatis Plus 将stuName这种形式自动转化成stu_name private String stuName;@TableField(value = "stuage")private int stuAge;

方法:

public static void testInsert() {ClassPathXmlApplicationContext context =new ClassPathXmlApplicationContext("applicationContext.xml");StudentMapper studentMapper = context.getBean("studentMapper", StudentMapper.class);//StudentMapper.class 将Object 类型转换成 StudentMapper相当于强转Student student = new Student("zs",12);int count = studentMapper.insert(student);//System.out.println(count);}

在MyBatis.xml中配置 关闭下划线和骆驼峰之间的自动转换关闭

<setting name="mapUnderscoreToCamelCase" value="false"/>

出现 stu_no自动转stuNo 的情况导致查询结果为null wapper相当于where语句

public static void testQuery() {ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");StudentMapper studentMapper = context.getBean("studentMapper", StudentMapper.class);//select ... from ... where ... stu_no between 3 and 5 and stu_age>20QueryWrapper<Student> wrapper = new QueryWrapper<>();wrapper.between("stu_age", 2, 20).ge("stu_no",2);List<Student> students = studentMapper.selectList(wrapper);// Student student = studentMapper.selectById(2);System.out.println(students);
}

2.wapper ---- where

wapper 相当于where

• 查询 QueryWapper

  QueryWrapper<Student> wrapper = new QueryWrapper<>();
  wrapper.between("stu_age", 12, 20).or(i->i.ge("stu_no",4).le("stu_no",6));
  //SELECT stu_no,stu_name,stu_age FROM student WHERE (stu_age BETWEEN ? AND ? OR stu_no >= ?)
  

wrapper.between(“stu_age”, 12, 20).ge(“stu_no”,4) 默认用and连接 如果要用或的关系

wrapper.between(“stu_age”, 12, 20).or().ge(“stu_no”,4) or里面有多个语句

wrapper.between(“stu_age”, 12, 20).or(i->i.ge(“stu_no”,4).le(“stu_no”,6));

• 增删改(DML) UpdataWapper 同上

预加载:MP启动时，会指定加载所有常见的CRUD语句(来自于MP提供的BaseMapper接口)并将这些够封装到了MapperStatement对象中。

12.AR(activeRecoder)编程

形式 直接通过实行了进行增删改查(不需要借助Mapper对象)

• 实体类继承Model类

  public class Student extends Model<Student> {
  

• 方法

  new ClassPathXmlApplicationContext("ApplicationContext.xml");//里面配置了数据库信息必须打开IOC容器才能进行操作
  Student student = new Student("张三",23);
  student.insert();
  

MP将主键设置成了Serializable类型 目的:可以接受常见的类型:8个基本类型+String 他们的父类都是Serializable

面向对象 lambda 查询的对象是"类的属性"

wapper.lambda().like(Student::getStuName,“a”);

面向SQL 查询的是表的字段

wapper.like(“stu_name”,“a”);

13.逆向工程（代码生成器）

https://mp.baomidou.com/

14.分页查询

在ApplicationContext.xml中的MyBatis Plus 的配置MybatisSqlSessionFactoryBean中配置

<property name="plugins" ><list><!--分页插件--><bean class="com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor"></bean></list></property>

public static void testPage(){ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");StudentMapper studentMapper = context.getBean("studentMapper", StudentMapper.class);//select * from student limit 2,2Page<Student> page = studentMapper.selectPage(new Page<>(2, 2), null);System.out.println("当前页的数据:"+page.getRecords());System.out.println("当前页页码:"+page.getCurrent());System.out.println("总数据量:"+ page.getTotal());System.out.println("每页数据量:"+ page.getSize());}

阻断器: 防止恶意删除或修改全表数据

<list><!--分页插件--><bean class="com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor"><property name="sqlParserList" ><list><bean class="com.baomidou.mybatisplus.extension.parsers.BlockAttackSqlParser"></bean></list></property></bean>
</list>

乐观锁：CAS算法 总以为不会冲突 在修改的瞬间 检验一下

悲观锁 总以为会冲突 并发–> 串行 (影响效率)

15.SQL注入

1.自定义方法

在 com.xm.injector.methods 包中写自己的方法 extends AbstractMethod 可以模仿例句修改

public class MyDelete extends AbstractMethod {@Overridepublic MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {String sql;sql = "delete from student where stu_no>6";SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);return this.addDeleteMappedStatement(mapperClass, "deleteAll", sqlSource);}
}
在接口 StudentMapper中写自己的方法-->**void deleteAll();**

2.自定义SQL注入器

在injector包下创建 MyInjector 类 com.xm.injector.MyInjector

extends AbstractSqlInjector

将17个默认的方法和自定义的方法加入

public class MyInjector extends AbstractSqlInjector {@Overridepublic List<AbstractMethod> getMethodList(Class<?> mapperClass) {return Stream.of(new Insert(),new Delete(),new DeleteByMap(),new DeleteById(),new DeleteBatchByIds(),new Update(),new UpdateById(),new SelectById(),new SelectBatchByIds(),new SelectByMap(),new SelectOne(),new SelectCount(),new SelectMaps(),new SelectMapsPage(),new SelectObjs(),new SelectList(),new SelectPage(),new MyDelete()      //自定义方法).collect(toList());}
}

或者直接将自定义类加入到默认的方法数组中 变成自定义的18个方法数组

public class MyInjector extends AbstractSqlInjector {@Overridepublic List<AbstractMethod> getMethodList(Class<?> mapperClass) {List<AbstractMethod> methodList = new DefaultSqlInjector().getMethodList(Student.class);methodList.add(new MyDelete());return methodList;}
}

3.在配置中配置使用自己的BaseMapper

<bean id="globalConfig" class="com.baomidou.mybatisplus.core.config.GlobalConfig"><property name="sqlInjector"><bean class="com.xm.injector.MyInjector"></bean></property><bean id="sqlSessionFactory" class="com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean"><property name="dataSource" ref="dataSource"></property><property name="configLocation" value="classpath:mybatis.xml"></property><property name="typeAliasesPackage" value="com.xm.entity"></property><!--SQL注入--><property name="globalConfig" ref="globalConfig"></property>//要写在MyBatisPlus的bean之中