MyBatis 概述
- MyBatis是优秀的持久层框架
- MyBatis使用XML将SQL与程序解耦,便于维护
- MyBatis学习简单,执行高效,是JDBC的延伸
MyBatis 开发流程
文档目录结构:
1. 引入MyBatis依赖
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.5</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.22</version>
</dependency>
通过下面设置可以解决同外网下载速度慢的问题:
<!-- 优先从设置的阿里云镜像仓库下载 -->
<repositories>
<repository>
<id>aliyun</id>
<name>aliyun</name>
<url>https://maven.aliyun.com/repository/public</url>
</repository>
</repositories>
2. 创建核心配置文件
<?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>
<!-- goods_id -> goodsId 驼峰命名转换-->
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<environments default="dev"> <!--default指出数据源,方便切换 -->
<environment id="dev"> <!--开发环境-->
<!-- 采用JDBC对数据库事务进行 commit、rollback -->
<transactionManager type="JDBC"></transactionManager>
<!-- 采用连接池方式管理数据库链接 注意&-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/babytun?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai"/>
<!-- serverTimezone=Asia/Shanghai 不加时区会失败!-->
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
<environment id="prd"> <!--线上生产环境-->
<!-- 采用JDBC对数据库事务进行 commit、rollback -->
<transactionManager type="JDBC"></transactionManager>
<!-- 采用连接池方式管理数据库链接 注意&-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://presentx.top:3306/babytun?useUnicode=true&characterEncoding=UTF-8"/>
<property name="username" value="present"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mappers/goods.xml"></mapper>
</mappers>
</configuration>
前四行的代码抄上即可
设置驼峰命名法转换:
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
default 指出默认数据源,方便切换不同数据库()
开发环境和线上生产环境
<environments default="dev"> <!--default指出数据源,方便切换 -->
Mybatis的配置文件里,&符号后面要加上amp;
3. 创建实体(Entity)类
对应数据库的列名,严格按照驼峰命名法的规则声明entity实体类的成员变量。
代码略。
4. 创建Mapper映射文件
基本的增删改查,XML代码示例:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="goods">
<select id="selectAll" resultType="com.br.mybatis.entity.Goods" useCache="false">
select * from t_goods order by goods_id desc limit 10
</select>
<!-- 单参数传递,使用parameterType指定参数的数据类型即可,SQL中#{value}提取参数-->
<select id="selectById" parameterType="Integer" resultType="com.br.mybatis.entity.Goods">
select * from t_goods where goods_id = #{value}
</select>
<!-- 多参数传递时,使用parameterType指定Map接口,SQL中#{key}提取参数 -->
<select id="selectByPriceRange" parameterType="java.util.Map" resultType="com.br.mybatis.entity.Goods">
select * from t_goods
where
current_price between #{min} and #{max}
order by current_price
limit 0,#{limt}
</select>
<!-- 利用LinkedHashMap保存多表关联结果
MyBatis会将每一条记录包装为LinkedHashMap对象
key是字段名 value是字段对应的值 , 字段类型根据表结构进行自动判断
优点: 易于扩展,易于使用
缺点: 太过灵活,无法进行编译时检查
-->
<select id="selectGoodsMap" resultType="java.util.LinkedHashMap" flushCache="true">
select g.* , c.category_name from t_goods g , t_category c
where g.category_id = c.category_id
</select>
<!--结果映射-->
<resultMap id="rmGoods" type="com.br.mybatis.dto.GoodsDTO">
<!--设置主键字段与属性映射-->
<id property="goods.goodsId" column="goods_id"></id>
<!--设置非主键字段与属性映射-->
<result property="goods.title" column="title"></result>
<result property="goods.originalCost" column="original_cost"></result>
<result property="goods.currentPrice" column="current_price"></result>
<result property="goods.discount" column="discount"></result>
<result property="goods.isFreeDelivery" column="is_free_delivery"></result>
<result property="goods.categoryId" column="category_id"></result>
<result property="category.categoryId" column="category_id"></result>
<result property="category.categoryName" column="category_name"></result>
<result property="category.parentId" column="parent_id"></result>
<result property="category.categoryLevel" column="category_level"></result>
<result property="category.categoryOrder" column="category_order"></result>
<result property="test" column="test"/>
</resultMap>
<select id="selectGoodsDTO" resultMap="rmGoods">
select g.* , c.*,'1' as test from t_goods g , t_category c
where g.category_id = c.category_id
</select>
<!--flushCache="true"在sql执行后强制清空缓存-->
<insert id="insert" parameterType="com.br.mybatis.entity.Goods" flushCache="true"
useGeneratedKeys="true" keyProperty="goodsId">
INSERT INTO t_goods(title, sub_title, original_cost, current_price, discount, is_free_delivery, category_id)
VALUES (#{title} , #{subTitle} , #{originalCost}, #{currentPrice}, #{discount}, #{isFreeDelivery}, #{categoryId})
<!-- 给实体类回填 主键id (因为主键是mysql自动生成的) -->
<!-- <selectKey resultType="Integer" keyProperty="goodsId" order="AFTER">-->
<!-- select last_insert_id()-->
<!-- </selectKey>-->
</insert>
<update id="update" parameterType="com.br.mybatis.entity.Goods">
UPDATE t_goods
SET
title = #{title} ,
sub_title = #{subTitle} ,
original_cost = #{originalCost} ,
current_price = #{currentPrice} ,
discount = #{discount} ,
is_free_delivery = #{isFreeDelivery} ,
category_id = #{categoryId}
WHERE
goods_id = #{goodsId}
</update>
<!--delete from t_goods where goods_id in (1920,1921)-->
<delete id="delete" parameterType="Integer">
delete from t_goods where goods_id = #{value}
</delete>
<!--${order} 原文传值(常用来SQL语句的拼接,不要让用户输入,防止SQL注入)-->
<select id="selectByTitle" parameterType="java.util.Map" resultType="com.br.mybatis.entity.Goods">
select * from t_goods where title = #{title}
${order}
</select>
</mapper>
说明:前四行固定
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
指定命名空间:
<mapper namespace="goods">
</mapper>
然后里面就是按照约定写SQL了。后面再说
5. 初始化SessionFactory
@Test
public void testSqlSessionFactory() throws IOException {
//利用Reader加载 classpath 下的 mybatis-config.xml 核心配置文件
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
//初始化 SqlSessionFactory 对象,同时解析 mybatis-config.xml 配置文件
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
System.out.println("SqlSessionFactory加载成功");
//创建SqlSession对象(是JDBC 的扩展类,用于与数据库交互)
SqlSession sqlSession = sqlSessionFactory.openSession();
Connection connection = sqlSession.getConnection();
System.out.println(connection);
}
上面这段代码我们会进行封装。通过工具类完成对 SqlSession 对象的创建与销毁。
封装类的代码如下:
/**
* 使用 MyBatisUtils工具类,创建全局唯一的SQLSessionFactory对象
*/
public class MyBatisUtils {
//静态成员 属于类不属于对象,全局唯一
private static SqlSessionFactory sqlSessionFactory = null;
static {//静态块,初始化静态对象(在类加载时就会执行这块代码)
Reader reader = null;
try {
reader = Resources.getResourceAsReader("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (IOException e) {
e.printStackTrace();
throw new ExceptionInInitializerError(e); //捕捉异常 抛出
}
}
public static SqlSession openSession() {
return sqlSessionFactory.openSession();
}
public static void closeSqlSession(SqlSession sqlSession) {
if(sqlSession!=null){
sqlSession.close();
}
}
}
6. 利用SqlSession对象操作数据
对 SqlSessionFactory 和 SqlSession 对象的几点说明:
增删改查
1.查询
1.1 没有参数传递的 查询
<select id="selectAll" resultType="com.br.mybatis.entity.Goods" useCache="false">
select * from t_goods order by goods_id desc limit 10
</select>
select标签中指明了返回值的类型
里面写 SQL 语句
/**
* 没有传参数的查询
*/
@Test
public void testSelectAll(){
SqlSession sqlSession = null;
try{
sqlSession = MyBatisUtils.openSession();
List<Goods> goodsList = sqlSession.selectList("goods.selectAll");
for (Goods good:goodsList){
System.out.println(good);
}
}catch (Exception e){
throw e;
}finally {
MyBatisUtils.closeSqlSession(sqlSession);
}
}
如果 selectAll 名称唯一,可以省略命名空间 goods
1.2 传递单个参数查询
<!-- 单参数传递,使用parameterType指定参数的数据类型即可,SQL中#{value}提取参数-->
<select id="selectById" parameterType="Integer" resultType="com.br.mybatis.entity.Goods">
select * from t_goods where goods_id = #{value}
</select>
parameterType 指明传入的参数类型
resultType 返回值的类型
参数名称 默认为 value
@Test
public void testSelectById(){
SqlSession sqlSession = null;
try{
sqlSession = MyBatisUtils.openSession();
Goods good = sqlSession.selectOne("goods.selectById",1603);
System.out.println(good.getTitle());
}catch (Exception e){
throw e;
}finally {
MyBatisUtils.closeSqlSession(sqlSession);
}
}
1.3 传递多个参数查询
<!-- 多参数传递时,使用parameterType指定Map接口,SQL中#{key}提取参数 -->
<select id="selectByPriceRange" parameterType="java.util.Map" resultType="com.br.mybatis.entity.Goods">
select * from t_goods
where
current_price between #{min} and #{max}
order by current_price
limit 0,#{limt}
</select>
传入的参数类型为Map parameterType="java.util.Map"
@Test
public void testSelectByPriceRange(){
SqlSession sqlSession = null;
try{
sqlSession = MyBatisUtils.openSession();
Map map = new HashMap();
map.put("min",100);
map.put("max",500);
map.put("limt",10);
List<Goods> goodsList = sqlSession.selectList("selectByPriceRange",map);
for (Goods goods:goodsList){
System.out.println(goods);
}
}catch (Exception e){
throw e;
}finally {
MyBatisUtils.closeSqlSession(sqlSession);
}
}
1.4 多表关联查询
<!-- 利用LinkedHashMap保存多表关联结果
MyBatis会将每一条记录包装为LinkedHashMap对象
key是字段名 value是字段对应的值 , 字段类型根据表结构进行自动判断
优点: 易于扩展,易于使用
缺点: 太过灵活,无法进行编译时检查
-->
<select id="selectGoodsMap" resultType="java.util.LinkedHashMap" flushCache="true">
select g.* , c.category_name from t_goods g , t_category c
where g.category_id = c.category_id
</select>
MyBatis会将每一条记录包装为LinkedHashMap对象
key是字段名 value是字段对应的值 , 字段类型根据表结构进行自动判断
@Test
public void testSelectGoodsMap(){
SqlSession sqlSession = null;
try{
sqlSession = MyBatisUtils.openSession();
List<Map> mapList= sqlSession.selectList("selectGoodsMap");
for (Map map:mapList){
System.out.println(map.keySet());
//[goods_id, title, sub_title, original_cost, current_price, discount, is_free_delivery, category_id, category_name]
System.out.println(map);
//{goods_id=739, title=亲润 孕妇护肤品豆乳大米盈润保湿胶原蚕丝面膜(18片装), sub_title=卓效对抗孕期干燥,15分钟快速补水,补充胶原蛋白,幼滑肌肤。天然豆乳配方,温和低敏,孕产期、所有肤质适用。, original_cost=198.0, current_price=88.0, discount=0.444444, is_free_delivery=1, category_id=43, category_name=米粉}
}
}catch (Exception e){
throw e;
}finally {
MyBatisUtils.closeSqlSession(sqlSession);
}
}
这里把结果存在了map而不是实体类中,对以后的使用不方便。
可以创建一个新的实体类来接受多表关联查询的结果
GoodsDTO的成员变量:
private Goods goods = new Goods();
private Category category = new Category();
private String test;
于是就有了下面的方案
1.5 多表关联查询(改进)
<!--结果映射-->
<resultMap id="rmGoods" type="com.br.mybatis.dto.GoodsDTO">
<!--设置主键字段与属性映射-->
<id property="goods.goodsId" column="goods_id"></id>
<!--设置非主键字段与属性映射-->
<result property="goods.title" column="title"></result>
<result property="goods.originalCost" column="original_cost"></result>
<result property="goods.currentPrice" column="current_price"></result>
<result property="goods.discount" column="discount"></result>
<result property="goods.isFreeDelivery" column="is_free_delivery"></result>
<result property="goods.categoryId" column="category_id"></result>
<result property="category.categoryId" column="category_id"></result>
<result property="category.categoryName" column="category_name"></result>
<result property="category.parentId" column="parent_id"></result>
<result property="category.categoryLevel" column="category_level"></result>
<result property="category.categoryOrder" column="category_order"></result>
<result property="test" column="test"/>
</resultMap>
<select id="selectGoodsDTO" resultMap="rmGoods">
select g.* , c.*,'1' as test from t_goods g , t_category c
where g.category_id = c.category_id
</select>
与前面不同的是,出了写SQL语句,这里还需要指明映射。
resultMap=“rmGoods” 指出使用上面刚配置的那个映射。
@Test
public void testSelectGoodsDTO(){
SqlSession session = null;
try {
session = MyBatisUtils.openSession();
List<GoodsDTO> goodsDTOList = session.selectList("selectGoodsDTO");
for (GoodsDTO goodsDTO:goodsDTOList){
System.out.println(goodsDTO.getGoods().getTitle()+"--"+goodsDTO.getCategory().getCategoryName()+"--"+goodsDTO.getTest());
}
}catch (Exception e){
e.printStackTrace();
}finally {
MyBatisUtils.closeSqlSession(session);
}
}
写入操作有:插入,删除,更新。
先看一下数据库事务:
为了保证数据的一致性,要注意 commit 和 rollback
2.插入
<!--flushCache="true"在sql执行后强制清空缓存-->
<insert id="insert" parameterType="com.br.mybatis.entity.Goods" flushCache="true"
useGeneratedKeys="true" keyProperty="goodsId">
INSERT INTO t_goods(title, sub_title, original_cost, current_price, discount, is_free_delivery, category_id)
VALUES (#{title} , #{subTitle} , #{originalCost}, #{currentPrice}, #{discount}, #{isFreeDelivery}, #{categoryId})
<!-- 给实体类回填 主键id (因为主键是mysql自动生成的) -->
<!-- <selectKey resultType="Integer" keyProperty="goodsId" order="AFTER">-->
<!-- select last_insert_id()-->
<!-- </selectKey>-->
</insert>
指明传入的参数来源 parameterType="com.br.mybatis.entity.Goods"
ps:因为在插入数据的时候 主键id 是数据库自己生成的,所以我们需要把这个数据库自动生成的值回填到实体类中。
方便的方法 标签中加入useGeneratedKeys=“true” keyProperty=“goodsId” (只适用于数据库主键自增)
<insert id="insert" parameterType="com.br.mybatis.entity.Goods" flushCache="true"
useGeneratedKeys="true" keyProperty="goodsId">
稍微麻烦但是通用的方法:
<selectKey resultType="Integer" keyProperty="goodsId" order="AFTER">
select last_insert_id()
</selectKey>
/**
* 新增数据
* @throws Exception
*/
@Test
public void testInsert() throws Exception {
SqlSession session = null;
try{
session = MyBatisUtils.openSession();
Goods goods = new Goods();
goods.setTitle("测试商品");
goods.setSubTitle("测试子标题");
goods.setOriginalCost(200f);
goods.setCurrentPrice(100f);
goods.setDiscount(0.5f);
goods.setIsFreeDelivery(1);
goods.setCategoryId(43);
//insert()方法返回值代表本次成功插入的记录总数
int num = session.insert("goods.insert", goods);
session.commit();//提交事务数据
System.out.println(goods);
}catch (Exception e){
if(session != null){
session.rollback();//回滚事务
}
throw e;
}finally {
MyBatisUtils.closeSqlSession(session);
}
}
记得 commit 和 rollback
3.更新
<update id="update" parameterType="com.br.mybatis.entity.Goods">
UPDATE t_goods
SET
title = #{title} ,
sub_title = #{subTitle} ,
original_cost = #{originalCost} ,
current_price = #{currentPrice} ,
discount = #{discount} ,
is_free_delivery = #{isFreeDelivery} ,
category_id = #{categoryId}
WHERE
goods_id = #{goodsId}
</update>
数据类源于实体类Goods
/**
* 更新数据
*/
@Test
public void testUpdate(){
SqlSession session = null;
try {
session = MyBatisUtils.openSession();
Goods goods = session.selectOne("goods.selectById",739);
goods.setTitle("更新的标题");
session.update("goods.update",goods);
session.commit(); //提交事务
}catch (Exception e){
if(session!=null){
session.rollback(); //回滚事务
}
}finally {
MyBatisUtils.closeSqlSession(session);
}
}
4.删除
<!--delete from t_goods where goods_id in (1920,1921)-->
<delete id="delete" parameterType="Integer">
delete from t_goods where goods_id = #{value}
</delete>
根据 id 进行删除,指明接受的数据类型
parameterType=“Integer”
/**
* 删除数据
*/
@Test
public void testDelete(){
SqlSession session = null;
try {
session = MyBatisUtils.openSession();
session.update("goods.delete",739);
session.commit(); //提交事务
}catch (Exception e){
if(session!=null){
session.rollback(); //回滚事务
}
}finally {
MyBatisUtils.closeSqlSession(session);
}
}
预防SQL注入
<!--${order} 原文传值(常用来SQL语句的拼接,不要让用户输入,防止SQL注入)-->
<select id="selectByTitle" parameterType="java.util.Map" resultType="com.br.mybatis.entity.Goods">
select * from t_goods where title = #{title}
${order}
</select>
#{title} 预编译传递参数,可以防止SQL注入。
${order} 原文传递,可用于拼接SQL语句。(不推荐使用,如果必须使用,传递的参数不能通过前台输入获得)