Mybatis的进阶使用

最新推荐文章于 2024-07-19 00:12:38 发布

____小明同学i

最新推荐文章于 2024-07-19 00:12:38 发布

阅读量779

点赞数 2

文章标签： mybatis 数据库

本文链接：https://blog.csdn.net/weixin_45262118/article/details/108482431

版权

继上篇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>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之中

____小明同学i

关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mybatis的进阶使用

继上篇mybatis基础https://blog.csdn.net/weixin_45262118/article/details/1084823501.版本切换切换环境 (environment)<environments default="development">//dafault 默认使用的数据库id <environment id="development">//数据库id <transactionManager t
复制链接

扫一扫