十四、MyBatis入门
2022-07-22 本文已影响0人
东方奇迹
截屏2022-07-21 17.10.09.png
官网：https://mybatis.org/mybatis-3/zh/index.html
截屏2022-07-21 17.11.48.png
1、MyBatis开发流程六步骤
截屏2022-07-21 17.24.29.png
2、MyBatis使用细则
截屏2022-07-22 09.19.40.png
截屏2022-07-22 09.23.11.png
截屏2022-07-22 11.19.35.png
截屏2022-07-22 11.22.12.png
截屏2022-07-22 14.56.41.png
截屏2022-07-22 16.07.48.png
截屏2022-07-22 15.29.25.png
截屏2022-07-22 16.55.36.png
截屏2022-07-22 下午9.57.20.png
截屏2022-07-22 下午9.57.41.png
截屏2022-07-22 下午9.48.21.png
截屏2022-07-22 下午9.48.54.png
截屏2022-07-22 下午9.42.48.png
截屏2022-07-22 下午9.43.58.png
截屏2022-07-22 下午9.45.07.png
截屏2022-07-22 下午9.47.20.png
截屏2022-07-22 下午10.04.38.png
截屏2022-07-22 下午10.12.24.png
截屏2022-07-22 下午10.15.38.png
MybatisUtils.java工具类：
/**
* MybatisUtils工具类，创建全局唯一的SqlSessionFactory对象
*/
public class MybatisUtils {
//利用static（静态）属于类不属于对象，且全局唯一
private static SqlSessionFactory sqlSessionFactory = null;
//利用静态块在初始化类是实例化sqlSessionFactory
static {
Reader reader = null;
try {
reader = Resources.getResourceAsReader("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (IOException e) {
e.printStackTrace();
//初始化错误时，通过抛出异常ExceptionInInitializerError通知调用者
throw new ExceptionInInitializerError(e);
}
}
/**
* openSession创建一个新的SqlSession对象
* @return SqlSession对象
*/
public static SqlSession openSession() {
return sqlSessionFactory.openSession();
}
/**
* 释放一个有效的SqlSession对象
* @param session 准备释放SqlSession对象
*/
public static void closeSession(SqlSession session) {
if (session != null) {
session.close();
}
}
}
mybatis-config.xml配置文件：
<?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>
<settings>
<!-- goods_id == goodsId 驼峰命名转换-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<!-- 设置默认指向的数据库-->
<environments default="dev">
<!-- 配置环境，不同的环境不同的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/babytun?useUnicode=true&characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mappers/goods.xml"/>
</mappers>
</configuration>
goods.xml创建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.imooc.mybatis.entity.Goods">
select * from t_goods order by goods_id desc limit 10
</select>
<!-- 单参数传递，使用parameterType指定参数的数据类型即可，SQL中#{value}提取参数-->
<select id="selectById" parameterType="Integer" resultType="com.imooc.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.imooc.mybatis.entity.Goods">
select * from t_goods
where
current_price between #{min} and #{max}
order by current_price
limit 0,#{limit}
</select>
<!-- 利用LinkedHashMap保存多表关联结果
MyBatis会将每一条记录包装为LinkedHashMap对象
key是字段名 value是字段对应的值，字段类型根据表结构进行自行判断
优点：易于扩展，易于使用
缺点：太过灵活，无法进行编译时检查
-->
<select id="selectGoodsMap" resultType="java.util.LinkedHashMap">
select g.*,c.category_name from t_goods g join t_category c on g.category_id = c.category_id
</select>
<!-- 结果映射-->
<resultMap id="rmGoods" type="com.imooc.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="categoryName" column="category_name"></result>
<result property="test" column="test"></result>
</resultMap>
<select id=" " resultMap="rmGoods">
select g.*,c.category_name,'1' as test from t_goods g join t_category c on g.category_id = c.category_id
</select>
<insert id="insert" parameterType="com.imooc.mybatis.entity.Goods"
useGeneratedKeys="true"
keyProperty="goodsId"
keyColumn="goods_id">
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})
<!-- <selectKey resultType="int" keyProperty="goodsId" order="AFTER">-->
<!-- <!– 当前连接中最后产生的id号–>-->
<!-- select last_insert_id()-->
<!-- </selectKey>-->
</insert>
<update id="update" parameterType="com.imooc.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 id="delete" parameterType="Integer">
delete from t_goods where goods_id = #{value}
</delete>
</mapper>
MyBatisTestor.java测试类：
public class MyBatisTestor {
@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 sqlSession = null;
try {
//创建SqlSession对象，SqlSession是JDBC的扩展类，用于与数据库交互
sqlSession = sqlSessionFactory.openSession();
//创建数据库连接（测试用）
Connection conn = sqlSession.getConnection();
System.out.println(conn);
}catch (Exception e) {
e.printStackTrace();
}finally {
if (sqlSession != null) {
//如果type="POOLED",代表使用连接池，close则是将连接回收到连接池中
//如果type="UNPOOLED",代表直连，close则会调用Connection.close关闭连接
sqlSession.close();
}
}
}
@Test
public void testMyBatisUtils() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
Connection connection = sqlSession.getConnection();
System.out.println(connection);
} catch (Exception e) {
throw e;
} finally {
MybatisUtils.closeSession(sqlSession);
}
}
@Test
public void testSelectAll() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
List<Goods> list = sqlSession.selectList("goods.selectAll");
for (Goods g : list) {
System.out.println(g.getTitle() + g.getSubTitle());
}
} catch (Exception e) {
throw e;
} finally {
MybatisUtils.closeSession(sqlSession);
}
}
@Test
public void testselectById() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
Goods g = sqlSession.selectOne("goods.selectById",1903);
if (g != null) {
System.out.println(g.getTitle() + g.getSubTitle());
}else {
System.out.println("未找到您查找的数据");
}
} catch (Exception e) {
throw e;
} finally {
MybatisUtils.closeSession(sqlSession);
}
}
@Test
public void testSelectByPriceRange() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
Map<String, Integer> param = new HashMap<>();
param.put("min",100);
param.put("max", 500);
param.put("limit", 1);
List<Goods> list = sqlSession.selectList("goods.selectByPriceRange", param);
for (Goods g : list) {
System.out.println(g.getTitle() + g.getSubTitle());
}
} catch (Exception e) {
throw e;
} finally {
MybatisUtils.closeSession(sqlSession);
}
}
@Test
public void testSelectGoodsMap() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
List<Map > list = sqlSession.selectList("goods.selectGoodsMap");
for (Map map : list) {
System.out.println(map);
}
} catch (Exception e) {
throw e;
} finally {
MybatisUtils.closeSession(sqlSession);
}
}
@Test
public void testSelectGoodsDTO() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
List<GoodsDTO> list = sqlSession.selectList("goods.selectGoodsDTO");
for (GoodsDTO goodsDTO : list) {
System.out.println(goodsDTO.getGoods().getTitle() + goodsDTO.getCategoryName());
}
} catch (Exception e) {
throw e;
} finally {
MybatisUtils.closeSession(sqlSession);
}
}
@Test
public void testInsert() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
Goods goods = new Goods();
goods.setTitle("小狗子");
goods.setSubTitle("小狗子叫小黑");
goods.setOriginalCost(100F);
goods.setCurrentPrice(99F);
goods.setDiscount(1F);
goods.setIsFreeDelivery(0);
goods.setCategoryId(1);
//insert()返回值代表本次成功插入的记录总数
int num = sqlSession.insert("goods.insert",goods);
System.out.println(num);
//提交事务数据
sqlSession.commit();
} catch (Exception e) {
if (sqlSession != null) {
sqlSession.rollback();//回滚事务
}
throw e;
} finally {
MybatisUtils.closeSession(sqlSession);
}
}
@Test
public void testUpdate() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
Goods goods = sqlSession.selectOne("goods.selectById",1903);
goods.setTitle("二狗子");
//update()返回值代表本次成功更新的记录总数
int num = sqlSession.update("goods.update",goods);
System.out.println(num);
//提交事务数据
sqlSession.commit();
} catch (Exception e) {
if (sqlSession != null) {
sqlSession.rollback();//回滚事务
}
throw e;
} finally {
MybatisUtils.closeSession(sqlSession);
}
}
@Test
public void testDelete() throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtils.openSession();
//delete()返回值代表本次成功删除的记录总数
int num = sqlSession.delete("goods.delete",1903);
System.out.println(num);
//提交事务数据
sqlSession.commit();
} catch (Exception e) {
if (sqlSession != null) {
sqlSession.rollback();//回滚事务
}
throw e;
} finally {
MybatisUtils.closeSession(sqlSession);
}
}
}
