MyBatis基本使用
声明：基于《基于Maven工程下的MyBatis框架+MySQL+连接池的数据查询操作》进一步拓展，相关配置文件、数据文件可阅上篇。
SQL传单/多参
在goods.xml新增两个<select>：
<!--单参数传参，使用paramterType指定的数据类型即可，SQL中#{value}提取参数-->
<select id="selectById" parameterType="Integer" resultType="com.imooc.mybatis.entity.Goods">
select * from t_goods where goods_id=#{value}
</select>
<!--多参数传参，使用paramterType指定Map接口，SQL中#{value}提取参数，这里的value为Map的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>在MyBatisTest.java测试类中新增两个方法：
@Test
public void testSelectById(){
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtils.openSession();
// 进行查询，传入的参数取的是在goods.xml文件的namespace名称和select id
Goods goods=sqlSession.selectOne("goods.selectById",1602);
System.out.println(goods);
} catch (Exception e) {
throw e;
} finally {
MyBatisUtils.closeSession(sqlSession);
}
}
@Test
public void testSelectByPriceRange(){
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtils.openSession();
// 进行查询，传入的参数取的是在goods.xml文件的namespace名称和select id
Map param = new HashMap();
param.put("min",100);
param.put("max",500);
param.put("limit",10);
List<Goods> list=sqlSession.selectList("goods.selectByPriceRange",param);
for (Goods goods : list) {
System.out.println(goods.getTitle()+"--"+goods.getCurrentPrice());
}
} catch (Exception e) {
throw e;
} finally {
MyBatisUtils.closeSession(sqlSession);
}
}多表关联查询【Map--KEY对应数据库中的字段名】
新增t_category种类表
create table t_category
(
category_id int auto_increment comment '产品分类'
primary key,
category_name varchar(32) not null comment '分类名称',
parent_id int null comment '上级分类',
category_level int not null comment '级别',
category_order int not null comment '排序'
) 在goods.xml新增两个<select>，随用其一，两者区别是让返回的Map集合无序/有序
<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>
<select id="selectGoodsLinkedHashMap" resultType="java.util.LinkedHashMap">
select g.*,c.category_name from t_goods g,t_category c
where g.category_id=c.category_id
</select>在MyBatisTest.java测试类中新增两个方法：
//java.util.Map
@Test
public void testSelectGoodsMap(){
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtils.openSession();
// 进行查询，传入的参数取的是在goods.xml文件的namespace名称和select id
List<Map> list=sqlSession.selectList("goods.selectGoodsMap");
for (Map map : list) {
System.out.println(map);
}
} catch (Exception e) {
throw e;
} finally {
MyBatisUtils.closeSession(sqlSession);
}
}
//java.util.LinkedHashMap
@Test
public void testSelectGoodsLinkedHashMap(){
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtils.openSession();
// 进行查询，传入的参数取的是在goods.xml文件的namespace名称和select id
List<Map> list=sqlSession.selectList("goods.selectGoodsLinkedHashMap");
for (Map map : list) {
System.out.println(map);
}
} catch (Exception e) {
throw e;
} finally {
MyBatisUtils.closeSession(sqlSession);
}
}
多表关联查询【Entity--KEY对应实体类中的字段名】
因为要用到实体类Goods，而多表查询的category_name属性值不被Goods包含，所以这里要对Goods进行扩展，但不在原有的实体类上修改，而是新建拓展类GoodsDTO.java，其中test属性是我测试属性，用AS命令添加的属性
package com.imooc.mybatis.dto;
import com.imooc.mybatis.entity.Goods;
/**
* @Auther 徐士成
* @Date 2021-06-22 14:18
*/
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;
}
@Override
public String toString() {
return "GoodsDTO{" +
"goods=" + goods +
", categoryName='" + categoryName + '\'' +
", test='" + test + '\'' +
'}';
}
}
这里借助resultMap结果集完成实体类映射：
<resultMap id="rmGoods" type="com.imooc.mybatis.dto.GoodsDTO">
<id column="goods_id" property="goods.goodsId"></id>
<result column="title" property="goods.title"></result>
<result column="original_cost" property="goods.originalCost"></result>
<result column="current_price" property="goods.currentPrice"></result>
<result column="discount" property="goods.discount"></result>
<result column="is_free_delivery" property="goods.isFreeDelivery"></result>
<result column="category_id" property="goods.categoryId"></result>
<result column="category_name" property="categoryName"></result>
<result column="test" property="test"></result>
</resultMap>
<select id="selectGoodsDTO" 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> @Test
public void testSelectGoodsDTO(){
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtils.openSession();
// 进行查询，传入的参数取的是在goods.xml文件的namespace名称和select id
List<GoodsDTO> list=sqlSession.selectList("goods.selectGoodsDTO");
for (GoodsDTO goodsDTO : list) {
System.out.println(goodsDTO);
}
} catch (Exception e) {
throw e;
} finally {
MyBatisUtils.closeSession(sqlSession);
}
}
