MyBatis基本使用

声明：基于《基于Maven工程下的MyBatis框架+MySQL+连接池的数据查询操作》进一步拓展，相关配置文件、数据文件可阅上篇。

SQL传单/多参

copy
在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>

copy
在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对应数据库中的字段名】

copy
新增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 '排序'
)

copy
在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>

copy
在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命令添加的属性

copy
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结果集完成实体类映射：

copy
<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>

copy
@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);
        }
    }