Mybatis的進階使用

繼上篇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 ></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.模糊查詢

• ${}
  • **$??:\mathrm{原}\mathrm{樣}\mathrm{輸}\mathrm{出}stuNamelik{e}^{\mathrm{\prime }}$??:原樣輸出stuNamelike′{_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" >
          <property name="driverClass" value="https://blog.csdn.net/weixin_45262118/article/details/${jdbc.driver}"></property>
          <property name="jdbcUrl" value="https://blog.csdn.net/weixin_45262118/article/details/${jdbc.url}"></property>
          <property name="user" value="https://blog.csdn.net/weixin_45262118/article/details/${jdbc.username}"></property>
          <property name="password" value="https://blog.csdn.net/weixin_45262118/article/details/${jdbc.password}"></property>
      </bean>
  
      <!--事務管理器-->
      <bean id="dataSourceTransactionManager" >
          <property name="dataSource" ref="dataSource"></property>
      </bean>
  
      <tx:annotation-driven transaction-manager="dataSourceTransactionManager"></tx:annotation-driven>
  
      <!--MyBatis
      <bean id="sqlSessionFactory" >
          <property name="dataSource" ref="dataSource"></property>
          <property name="configLocation" value="https://blog.csdn.net/weixin_45262118/article/details/classpath:mybatis.xml"></property>
          <property name="typeAliasesPackage" value="https://blog.csdn.net/weixin_45262118/article/details/com.xm.entity"></property>
      </bean>-->
      
      <!--MyBatis plus-->
      <bean id="sqlSessionFactory" >
          <property name="dataSource" ref="dataSource"></property>
          <property name="configLocation" value="https://blog.csdn.net/weixin_45262118/article/details/classpath:mybatis.xml"></property>
          <property name="typeAliasesPackage" value="https://blog.csdn.net/weixin_45262118/article/details/com.xm.entity"></property>
      </bean>
  
      <!--MyBatis只寫介面不寫實作類 studentMapper物件 -->
      <bean >
          <property name="basePackage" value="https://blog.csdn.net/weixin_45262118/article/details/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>20
    QueryWrapper<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,2
    Page<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 {

    @Override
    public 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 {
    @Override
    public 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 {
    @Override
    public 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之中