MyBatis
简介
什么是MyBatis
- MyBatis是一款优秀的持久层框架,用于简化JDBC开发
- MyBatis本是Apache的一个开源项目iBatis。2010年这个项目由apache software foundation迁移到了google code,并且改名为MyBatis。2013年11月迁移到Github
- 官网:https://mybatis.org/mybatis-3/zh/getting-started.html
持久层:
- 负责将数据保存到数据库的那一层代码
- JavaEE三层架构:表现层,业务层,持久层
框架:
- 框架就是一个半成品软件,是一套可重用的、通用的、软件基础代码模型
- 在框架的基础之上构建软件编写更加高效、规范、通用、可扩展
JDBC缺点
MyBatis简化
MyBatis快速入门
案例:查询user表中所有数据
- 创建user表,添加数据
- 创建模块,导入坐标
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.10</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.31</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.20</version>
</dependency>
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.2.3</version>
</dependency>
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-core</artifactId>
<version>1.2.3</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.26</version>
</dependency>
</dependencies>
在resource下导入logback.xml配置
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<appender name="Console" class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<pattern>[%level] %blue(%d{HH:mm:ss.SSS}) %cyan([%thread]) %boldGreen(%logger{15}) - %msg %n</pattern>
</encoder>
</appender>
<logger name="com.xc" level = "DEBUG" additivity="false">
<appender-ref ref="Console" />
</logger>
</configuration>
- 编写MyBatis核心配置文件 --> 替换连接信息,解决硬编码问题
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<!-- 数据库的连接信息-->
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:///test?useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="****"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="UserMapper.xml"/>
</mappers>
</configuration>
- 编写SQL映射文件 --> 统一管理sql语句,解决硬编码问题
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--
namespace: 命名空间
-->
<mapper namespace="test">
<select id="selectAll" resultType="com.xc.pojo.User">
select * from user
</select>
</mapper>
-
编码
-
- 定义POJO类
package com.xc.pojo;
import lombok.Data;
/**
* @author xc
* @date 2023/5/30 18:21
*/
@Data
public class User {
private Long id;
private String name;
private Integer age;
private String role;
}
-
- 加载核心配置文件,获取SqlSessionFactory对象
- 获取SqlSession对象,执行SQL语句
- 释放资源
public static void main(String[] args) throws IOException {
// 加载mybatis核心配置文件
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 获取sqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
// 执行sql
List<User> userList = sqlSession.selectList("test.selectAll");
System.out.println(userList);
// 释放资源
sqlSession.close();
}
结果
Mapper代理开发
-
目的
-
- 解决原生方式中的硬编码
- 简化后期SQL
使用Mapper代理方式完成
- 定义与SQL映射文件同名的Mapper接口,并且将Mapper接口和SQL映射文件放置在同一目录下
- 设置SQL映射文件的namespace属性为Mapper接口全限定名
- 在Mapper接口中定义方法,方法名就是SQL映射文件中sql语句id,并保持参数类型和返回值类型一致
public interface UserMapper {
List<User> selectAll();
}
-
编码
-
- 通过SqlSession的getMapper方法获取Mapper接口的代理对象
- 调用对应方法完成sql执行
public static void main(String[] args) throws IOException {
// 加载mybatis核心配置文件
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 获取sqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获取UserMapper接口的代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
// 执行sql
List<User> userList = mapper.selectAll();
System.out.println(userList);
// 释放资源
sqlSession.close();
}
结果
注意:还可以使用包扫描的方式来加载sql映射文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<!-- 数据库的连接信息-->
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:///test?useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="*****"/>
</dataSource>
</environment>
</environments>
<mappers>
<!-- 加载sql映射文件-->
<!-- <mapper resource="com/xc/mapper/UserMapper.xml"/>-->
<!-- Mapper代理方式-->
<package name="com.xc.mapper"/>
</mappers>
</configuration>
MyBatis核心配置文件
官网:https://mybatis.org/mybatis-3/zh/configuration.html
environments
:配置数据库连接环境信息,可以配置多个environment,通过default属性切换不同的environment
typeAliases
:类型别名可为 Java 类型设置一个缩写名字。 它仅用于 XML 配置,意在降低冗余的全限定类名书写。
配置各个标签时,需要遵守前后顺序
配置文件完成增删改查
查询
查询所有数据
编写Mapper接口
public interface BrandMapper {
List<Brand> selectAll();
}
测试
package com.xc.testUser;
import com.xc.mapper.BrandMapper;
import com.xc.mapper.UserMapper;
import com.xc.pojo.Brand;
import com.xc.pojo.User;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
* @author xc
* @date 2023/5/30 18:24
*/
public class Mybatis {
SqlSession sqlSession;
BrandMapper mapper;
@Before
public void init() throws IOException {
// 加载mybatis核心配置文件
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 获取sqlSession对象
sqlSession = sqlSessionFactory.openSession();
// 获取UserMapper接口的代理对象
mapper = sqlSession.getMapper(BrandMapper.class);
}
@Test
public void testSelectAll() {
List<Brand> brands = mapper.selectAll();
System.out.println(brands);
}
@After
public void end() {
// 释放资源
sqlSession.close();
}
}
结果
发现有的字段为null,因为字段名与数据库对应不上,解决办法:
或使用sql片段(不灵活)
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--
namespace: 命名空间
-->
<mapper namespace="com.xc.mapper.BrandMapper">
<!-- sql片段-->
<sql id="breand_cloumn">
id, brand_name as brandName, company_name as companyName, ordered, description,status
</sql>
<select id="selectAll" resultType="com.xc.pojo.Brand">
select
<include refid="breand_cloumn"/>
from tb_brand
</select>
</mapper>
结果
使用resultMap
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--
namespace: 命名空间
-->
<mapper namespace="com.xc.mapper.BrandMapper">
<!--
id:唯一标识
type:映射的类型,支持别名
-->
<resultMap id="brandResultMap" type="com.xc.pojo.Brand">
<!--
id:完成主键字段的映射
column:表的列名
property:实体类的字段名
result:完成一般字段的映射
-->
<result column="brand_name" property="brandName" />
<result column="company_name" property="companyName" />
</resultMap>
<!-- 使用 resultMap属性 -->
<select id="selectAll" resultMap="brandResultMap">
select
*
from tb_brand
</select>
</mapper>
结果
查看详情
定义Mapper接口
public interface BrandMapper {
List<Brand> selectAll();
Brand selectById(int id);
}
定义xml文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--
namespace: 命名空间
-->
<mapper namespace="com.xc.mapper.BrandMapper">
<resultMap id="brandResultMap" type="com.xc.pojo.Brand">
<result column="brand_name" property="brandName" />
<result column="company_name" property="companyName" />
</resultMap>
<!-- 查id-->
<select id="selectById" resultMap="brandResultMap">
select * from tb_brand where id = #{id}
</select>
</mapper>
查询
package com.xc.testUser;
import com.xc.mapper.BrandMapper;
import com.xc.mapper.UserMapper;
import com.xc.pojo.Brand;
import com.xc.pojo.User;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
* @author xc
* @date 2023/5/30 18:24
*/
public class Mybatis {
SqlSession sqlSession;
BrandMapper mapper;
@Before
public void init() throws IOException {
// 加载mybatis核心配置文件
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 获取sqlSession对象
sqlSession = sqlSessionFactory.openSession();
// 获取UserMapper接口的代理对象
mapper = sqlSession.getMapper(BrandMapper.class);
}
@Test
public void testSelectById() {
Brand brand = mapper.selectById(1);
System.out.println(brand);
}
@After
public void end() {
// 释放资源
sqlSession.close();
}
}
结果
条件查询
定义Mapper接口
public interface BrandMapper {
List<Brand> selectByCondition(@Param("status") int status, @Param("companyName") String companyName, @Param("brandName") String brandName);
List<Brand> selectByCondition(Brand brand);
List<Brand> selectByCondition(Map map);
}
编写映射文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--
namespace: 命名空间
-->
<mapper namespace="com.xc.mapper.BrandMapper">
<resultMap id="brandResultMap" type="com.xc.pojo.Brand">
<result column="brand_name" property="brandName" />
<result column="company_name" property="companyName" />
</resultMap>
<select id="selectByCondition" resultMap="brandResultMap">
select * from tb_brand
where
status = #{status}
and company_name like #{companyName}
and brand_name like #{brandName}
</select>
</mapper>
测试
package com.xc.testUser;
import com.xc.mapper.BrandMapper;
import com.xc.mapper.UserMapper;
import com.xc.pojo.Brand;
import com.xc.pojo.User;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author xc
* @date 2023/5/30 18:24
*/
public class Mybatis {
SqlSession sqlSession;
BrandMapper mapper;
@Before
public void init() throws IOException {
// 加载mybatis核心配置文件
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 获取sqlSession对象
sqlSession = sqlSessionFactory.openSession();
// 获取UserMapper接口的代理对象
mapper = sqlSession.getMapper(BrandMapper.class);
}
@Test
public void testSelectByCondition() {
List<Brand> brands1 = mapper.selectByCondition(1,"%小%","%小米%");
Brand b = new Brand();
b.setStatus(1);
b.setBrandName("%华%");
b.setCompanyName("%华%");
List<Brand> brands2 = mapper.selectByCondition(b);
Map<String,Object> map = new HashMap<>();
map.put("status",0);
map.put("companyName","%三%");
map.put("brandName","%三%");
List<Brand> brands3 = mapper.selectByCondition(map);
System.out.println(brands1);
System.out.println("====================");
System.out.println(brands2);
System.out.println("====================");
System.out.println(brands3);
}
@After
public void end() {
// 释放资源
sqlSession.close();
}
}
结果
动态条件查询
在xml中定义sql语句中添加if标签
<select id="selectByCondition" resultMap="brandResultMap">
select * from tb_brand
where 1 = 1
<if test="status != null">
status = #{status}
</if>
<if test="companyName != null and companyName != '' ">
and company_name like #{companyName}
</if>
<if test="brandName != null and brandName != '' ">
and brand_name like #{brandName}
</if>
</select>
优雅方式
<select id="selectByCondition" resultMap="brandResultMap">
select * from tb_brand
<where>
<if test="status != null">
and status = #{status}
</if>
<if test="companyName != null and companyName != '' ">
and company_name like #{companyName}
</if>
<if test="brandName != null and brandName != '' ">
and brand_name like #{brandName}
</if>
</where>
</select>
多选一
Mapper接口
// 因为不知道要传那个参数,直接用Brand对象
List<Brand> selectByConditionSingle(Brand brand);
或者直接用where标签包裹
添加
主键返回
修改
修改全部字段
修改动态字段
删除
删除一个
批量删除
MyBatis参数传递
注解完成增删改查
@Select("select * from tb_user where id = #{id}")
public User selectById(int id);
- 查询:@Select
- 添加:@Insert
- 修改:@Update
- 删除:@Delete
提示:
- 注解完成简单功能
- 配置文件完成复杂功能