Mybatis
简介
什么是 MyBatis?
MyBatis 是一款优秀的持久层框架,它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO(Plain Old Java Objects,普通老式 Java 对象)为数据库中的记录。
如果Maven文件扫描不到
<build>
<resources>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
</resources>
</build>
工具类
package com.zyl.utils;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
try {
String resource="mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
public static SqlSession getSqlsession(){
return sqlSessionFactory.openSession();
}
}
执行增删改之前需要提交事务
起别名
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
//起别名
<typeAliases>
<typeAlias type="com.zyl.pojo.Login" alias="Login"></typeAlias>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/db1?useSSL=false&useUnicode=true&characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/zyl/mapper/LoginMapper.xml"/>
</mappers>
</configuration>
结果集映射
<resultMap id="UserMap" type="com.zyl.pojo.Login">
<id column="id" property="id"></id>
<id column="username" property="username"></id>
<id column="password" property="pwd"></id>
</resultMap>
一对多关系(Collection)
多对一关系(association)
动态sql
1.if
使用动态 SQL 最常见情景是根据条件包含 where 子句的一部分。比如:
<select id="getVisibleBlogListByLabel" resultType="com.company.org.pojo.Blog">
select * from blog
where visible = 1
<if test="label != null and label != ''">
AND label = #{label}
</if>
</select>
这条语句提供了可选的查找文本功能。如果不传入label,那么只返回visible=1的博客;如果传入了label,那么就会对title一列进行查找并返回对应的结果。
<select id="getBlogListByLabelAndTitle" parameterType="map" resultType="com.company.org.pojo.Blog">
select * from blog
where
<if test="label != null">
label = #{label}
</if>
<if test="title != null">
AND title like #{title}
</if>
</select>
但是,这会产生一个问题,当label为空时,sql语句会变成:
select * from blog where and title like ‘xxx’
甚至当label和title都为空时,sql语句则变成:
select * from blog where
2.where
上面提到的语句就可以改为以下形式:
<select id="getBlogListByLabelAndTitle2" parameterType="map" resultType="com.company.org.pojo.Blog">
select * from blog
<where>
<if test="label != null">
label = #{label}
</if>
<if test="title != null">
AND title like #{title}
</if>
</where>
</select>
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
如果 where 元素与你期望的不太一样,你也可以通过自定义 trim 元素来定制 where 元素的功能。比如,和 where 元素等价的自定义 trim 元素为:
<select id="getBlogListByLabelAndTitle3" parameterType="map" resultType="com.company.org.pojo.Blog">
select * from blog
<trim prefix="where" prefixOverrides="and |or ">
<if test="label != null">
label = #{label}
</if>
<if test="title != null">
AND title like #{title}
</if>
</trim>
</select>
3.set
用于动态更新语句的类似解决方案叫做 set。set 元素可以用于动态包含需要更新的列,忽略其它不更新的列。比如:
<update id="updateBlogById">
update blog
<set>
<if test="title != null">title = #{title},</if>
<if test="label != null">label = #{label},</if>
<if test="visible != null">visible = #{visible},</if>
</set>
where id = #{id}
</update>
4.foreach
动态 SQL 的另一个常见使用场景是对集合进行遍历(尤其是在构建 IN 条件语句的时候)。
<select id="getBlogListByIds" parameterType="map" resultType="com.company.org.pojo.Blog">
select * from blog
<where>
id in
<foreach collection="ids" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</where>
</select>
相当于sql语句:
select * from blog where id IN (116012859,117374430,118058442)
5.choose、when、otherwise
有时候,我们不想使用所有的条件,而只是想从多个条件中选择一个使用。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。如:
<select id="getBlogListByTitleOrLabel" parameterType="map" resultType="com.company.org.pojo.Blog">
select * from blog
<where>
<choose>
<when test="label != null">label = #{label}</when>
<when test="title != null">and title like #{title}</when>
<otherwise>and visible = #{visible}</otherwise>
</choose>
</where>
</select>
其中,when可以有多个,otherwise必须存在!
缓存
一级缓存
只在一次会话有效(默认自动开启)
二级缓存
一级缓存失效(手动设置在mapper.xml中配置)
Mybatis-Plus
- 使用时需要加上包扫描
package com.zyl;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
//扫描包
@MapperScan("com.zyl.mapper")
@SpringBootApplication
public class DemoMpApplication {
public static void main(String[] args) {
SpringApplication.run(DemoMpApplication.class, args);
}
}
配置日志
server:
port: 80
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/db2?useSSL=false&useUnicode=true&characterEncoding=utf-8
username: root
password: 123456
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
主键生成策略
- 主键自增
- 雪花算法
分页插件
package com.zyl.config;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;
@Configuration
public class MpConfig {
@Bean
public PaginationInterceptor paginationInterceptor(){
return new PaginationInterceptor();
}
}
@Test
public void testpage() {
Page<Buy> page = new Page<>(0,5);
Page<Buy> buyPage = userMapper.selectPage(page, null);
}
条件构造器(wrapper)
public class MpConfig {
@Bean
public PaginationInterceptor paginationInterceptor(){
return new PaginationInterceptor();
}
}
```java
@Test
public void testpage() {
Page<Buy> page = new Page<>(0,5);
Page<Buy> buyPage = userMapper.selectPage(page, null);
}