Mybatis笔记一

SSM开发框架

• Spring 对象容器框架
• Spring MVC web程序分支接耦
• MyBatis 简化数据交互

MyBatis使用XML将SQL与程序接耦，便于维护。

前言 MyBatis开发流程

• 引入MyBatis依赖
• 创建核心配置文件
• 创建实体（Entity）类
• 创建Mapper映射文件
• 初始化SessionFactory(读取配置文件、加载映射)
• 利用sqlSession对象操作数据

一、引入依赖

可在pom.xml作为依赖引入

<dependencies> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.1</version> </dependency> </dependencies>

二、环境配置

1、/src/main/resource新建xxx.xml文件

<?xml version="1.0" encoding="UTF-8" ?> <!-- dtd可在https://mybatis.net.cn/getting-started.html配置 --> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!-- 便捷设置默认使用dev环境数据--> <environments default="prd"> <!-- id 唯一标识--> <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/study"/> <property name="username" value="root"/> <property name="password" value="gu19950402"/> </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://localhost:3306/babytun"/> <property name="username" value="root"/> <property name="password" value="gu19950402"/> </dataSource> </environment> </environments> </configuration>

三、SqlSessionFactory

• 用于初始化Mybatis，创建SqlSession对象，需要保证sqlSession在全局应用中唯一
• sqlSession使用JDBC方式与数据库交互
• sqlSession对象提供了数据表CRUD对应方法

文件目录/src/test/java/com.gu.mybatis/MybatisTest.java

package com.gu.mybatis; 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.Test; import java.io.IOException; import java.io.Reader; public class MybatisTest { @Test public void testSqlSessionFactory() { SqlSession sqlSession = null; try { // 利用Reader加载classpath下的mybaits-config.xml核心配置文件 Reader reader = Resources.getResourceAsReader("mybatis-config.xml"); // 初始化sqlSessionFactour对象，同时解析mybaits-config.xml文件 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader); // 创建sqlSession对象，sqlsession是JDBC的扩展类，用于数据库交互 sqlSession = sqlSessionFactory.openSession(); System.out.println("SqlSession加载成功"); } catch (IOException e) { e.printStackTrace(); } finally { if(sqlSession!=null) { // 如果type是"POOLED"，代表使用连接池，close则是将连接回收到连接池中 // 如果type是"UNPOOLEED",代表直连，cloes则会调用Connection.close方法关闭 sqlSession.close(); } } } }

创建mybatis的工具类

package com.gu; 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.Reader; public class MybatisUtils { private static SqlSessionFactory sqlSessionFactory = null; static { Reader reader = null; try { reader = Resources.getResourceAsReader("mybatis-config.xml"); } catch (IOException e) { e.printStackTrace(); throw new ExceptionInInitializerError(e); } sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader); } public static SqlSession openSession() { return sqlSessionFactory.openSession(); } public static void closeSession(SqlSession sqlSession) { if(sqlSession!=null){ sqlSession = null; } } public static void main(String[] args) { } }

四、创建mapper映射文件

文件路径/src/main/resource/mappers/goods.xml

mapper的dtd可在https://mybatis.net.cn/getting-started.html复制

<?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"> <!-- namespace 区分不同sql语句--> <mapper namespace="goods"> <!-- id sql名称 resultType 代表返回对象--> <select id="selectAll" resultType="com.gu.entity.Goods"> -- 内容为sql语句 select * from t_goods order by goods_id desc limit 10 </select> </mapper>

在pom.xml中配置映射文件

<mappers> <mapper resource="mappers/goods.xml"></mapper> </mappers>

测试文件查看

@Test public void testSqlsctAll() { SqlSession sqlSession = null; try { sqlSession = MybatisUtils.openSession(); List<Goods> list = sqlSession.selectList("goods.selectAll"); for(Goods goods: list) { System.out.println(goods.getTitle()); } } catch (Exception e) { throw e; } finally { MybatisUtils.closeSession(sqlSession); } }

实体类属性名称（驼峰命名）和数据库名称（下划线）不一样，需要做转换设置

/src/main/resource/Mybatis-config.xml

<settings> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings>

五、SQL传参

5.1 单个参数传参

映射文件

<select id="selectId" parameterType="Integer" resultType="com.gu.entity.Goods"> select * from t_goods where goods_id = #{goods_id} </select>

测试调用

sqlSession = MybatisUtils.openSession(); Goods goods = sqlSession.selectOne("goods.selectId", 1603);

5.2 多个参数传参

<select id="selectByPriceRange" parameterType="java.util.Map" resultType="com.gu.entity.Goods"> select * from t_goods where current_price between #{min} and #{max} order by current_price limit 0, #{limit} </select>

测试调用

sqlSession = MybatisUtils.openSession(); Map params = new HashMap(); params.put("min", 100); params.put("max", 500); params.put("limit", 10); List<Goods> goods = sqlSession.selectList("goods.selectByPriceRange", params); for(Goods good: goods){ System.out.println(good.getTitle()); }

六、多表关联查询

6.1 多表关联查询

<!-- 多表查询--> <select id="selectGoodsMap" resultType="java.util.Map"> select g.*, c.category_name from t_goods g, t_category c where g.category_id = c.category_id </select>

map不保证数据顺序，可食用LinkedHashMap

@Test public void selectGoodsMap() { SqlSession sqlSession = null; try { sqlSession = MybatisUtils.openSession(); List<Map> List = sqlSession.selectList("goods.selectGoodsMap"); for(Map map: List){ System.out.println(map); } } finally { MybatisUtils.closeSession(sqlSession); } }

利用Map保存多表关联结果

• Mybatis会将每一条记录包装为LinkedHashMap对象
• key是字段名 value是字段对应的值，字段类型根据表结构进行自动判断
• 优点 易于拓展，易于使用
• 缺点 太过灵活，无法进行编译时检查

6.2 ResultMap结果映射

• ResultMap可以将查询结果为复杂类型的Java对象
• 适用于Java对象保存多表关联结果
• 支持对象关联查询等高级特性

扩展实体类/src/main/java/com/gu/dto/GoodsDTO

package com.gu.dto; import com.gu.entity.Goods; public class GoodsDTO { private Goods goods = new Goods(); private String categoryName; private String test; public Goods getGoods() { return goods; } public void setGoods(Goods goods) { this.goods = goods; } public String getCategoryName() { return categoryName; } public void setCategoryName(String categoryName) { this.categoryName = categoryName; } public String getTest() { return test; } public void setTest(String test) { this.test = test; } }

配置查询语句

<!-- 结果映射--> <resultMap id="rmGoods" type="com.gu.dto.GoodsDTO"> <!-- property属性名 column字段名--> <id property="goods.goodsId" column="goods_id"></id> <!-- 除了主键的属性--> <result property="goods.title" column="title"></result> <result property="goods.goodsId" column="goods_id"></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="categoryName" column="category_name"></result> <result property="test" column="test"></result> </resultMap> <!-- resultMap 结果映射--> <select id="selectGoodsMapDTO" resultMap="rmGoods"> select g.*, c.category_name, '1' as test from t_goods g, t_category c where g.category_id = c.category_id </select> </mapper>

测试查询

sqlSession = MybatisUtils.openSession(); List<GoodsDTO> List = sqlSession.selectList("goods.selectGoodsMapDTO"); for(GoodsDTO goodsDTO: List){ System.out.println(goodsDTO.getGoods().getTitle()); }

七、MyBatis的数据插入修改和删除

前置知识：数据库事务

• 数据库事务是保证数据操作完整性的基础
• 客户端写操作时，事务日志会记录该操作，全部成功后commit到数据表中，commit才是真正写入
• 数据成功写入数据表后，事务日志记录会清除。
• 数据部分执行不成功，客户端会发送rollback命令，清除当前事务日志的数据。

7.1 新增 insert

映射文件配置

<insert id="insertGoods" parameterType="com.gu.entity.Goods"> 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}) -- 主键回填 last_insert_id获取连接最后产生的id <selectKey resultType="Integer" keyProperty="goodsId" order="AFTER"> select last_insert_id(); </selectKey> </insert>

测试插入

@Test public void insert() { SqlSession sqlSession = null; try { sqlSession = MybatisUtils.openSession(); Goods goods = new Goods(); goods.setTitle("测试商品01"); goods.setSubTitle("测试子标题"); goods.setOriginalCost(200f); goods.setCurrentPrice(100f); goods.setIsFreeDelivery(1); goods.setDiscount(0.5f); goods.setCategoryId(43); int num = sqlSession.insert("goods.insertGoods", goods); sqlSession.commit(); // 提交事物数据 System.out.println("num" + num); System.out.println(goods.getTitle()); } catch (Exception e) { System.out.println(e); if(sqlSession!=null) { sqlSession.rollback(); } } finally { MybatisUtils.closeSession(sqlSession); } }

7.1.1 selectKey和useGeneratedKey的区别

<insert id="insertGoods" parameterType="com.gu.entity.Goods" useGeneratedKeys="true" keyColumn="goods_id" 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}) </insert>

• selectKey标签需要明确编写获取最新组建的SQL语句
• useGeneratedKey属性会自动根据驱动生成对应SQL语句
• selectKey使用所有关系数据库
• useGeneratedKey只支持“自增主键”类型的数据库

7.2 更新和删除

<update id="update" parameterType="com.gu.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 id="delete" parameterType="Integer"> delete from t_goods where goods_id=#{value} </delete>

八、MyBatis注入攻击

• ${}文本替换，未经任何处理对SQL文本替换
• #{}预编译传旨，使用预编译传旨可以预防SQL注入

原义传值必须程序输入，不能由用户输入

<select id="selectByTitle" parameterType="java.util.Map" > select * from t_goods where title=#{title} ${order} </select>