Mybatis动态sql

1、什么是动态SQL

动态sql是指根据传入的参数数据动态组织sql的技术。

2、goods.xml

mybatis动态sql的两种写法：1.使用 where 1=1；2.使用 <where>标签

<!--动态sql-->
<select id="dynamicSQL" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods">
	select  * from  t_goods
	<!--使用 where 1=1 或者 <where>标签  -->
	<where>
		<if test="categoryId != null">
			and category_id = #{categoryId}
		</if>
		<if test="currentPrice != null">
		<!-- &lt;为小于号。 -->
			and current_price &lt; #{currentPrice}
		</if>
	</where>
</select>

3、测试用例

@Test
public void testDynamicSQL() throws Exception{
        SqlSession sqlSession = null;
        try{
            sqlSession = MybatisUtils.openSession();
            Map param = new HashMap();
            param.put("categoryId", 4);
            param.put("currentPrice",500);
            //查询条件
            List<Goods> list = sqlSession.selectList("goods.dynamicSQL", param);
            for (Goods g : list){
                System.out.println(g.getCategoryId()+"--"+g.getTitle()+"--"+g.getCurrentPrice());
            }
        }catch (Exception e){
            throw e;
        }finally {
            MybatisUtils.closeSession(sqlSession);
        }
}

4、Goods类

package com.imooc.mybatis.entity;


public class Goods {
    //商品编号
    private Integer goodsId;
    //标题
    private String title;
    //子标题
    private String subTitle;
    //原始价格
    private Float originalCost;
    //当前价格
    private Float currentPrice;
    //折扣率
    private Float  discount;
    //是否包邮
    private Integer isFreeDelivery;
    //分类编号
    private Integer categoryId;


    public Integer getGoodsId() {
        return goodsId;
    }

    public void setGoodsId(Integer goodsId) {
        this.goodsId = goodsId;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getSubTitle() {
        return subTitle;
    }

    public void setSubTitle(String subTitle) {
        this.subTitle = subTitle;
    }

    public Float getOriginalCost() {
        return originalCost;
    }

    public void setOriginalCost(Float originalCost) {
        this.originalCost = originalCost;
    }

    public Float getCurrentPrice() {
        return currentPrice;
    }

    public void setCurrentPrice(Float currentPrice) {
        this.currentPrice = currentPrice;
    }

    public Float getDiscount() {
        return discount;
    }

    public void setDiscount(Float discount) {
        this.discount = discount;
    }


    public Integer getIsFreeDelivery() {
        return isFreeDelivery;
    }

    public void setIsFreeDelivery(Integer isFreeDelivery) {
        this.isFreeDelivery = isFreeDelivery;
    }

    public Integer getCategoryId() {
        return categoryId;
    }

    public void setCategoryId(Integer categoryId) {
        this.categoryId = categoryId;
    }
}

5、sql语句

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for t_category
-- ----------------------------
DROP TABLE IF EXISTS `t_category`;
CREATE TABLE `t_category` (
  `category_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '分类id',
  `category_name` varchar(64) NOT NULL COMMENT '分类名称',
  `parent_id` int(10) DEFAULT NULL COMMENT '上级分类',
  `category_level` int(2) DEFAULT NULL COMMENT '分类的级别',
  `category_order` int(6) DEFAULT '0' COMMENT '前端顺序  数字大的优先显示',
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8 COMMENT='分类表';

-- ----------------------------
-- Records of t_category
-- ----------------------------
INSERT INTO `t_category` VALUES ('1', '母婴专区', null, '1', '100');
INSERT INTO `t_category` VALUES ('2', '美妆护肤', null, '1', '200');
INSERT INTO `t_category` VALUES ('3', '家装生活', null, '1', '300');
INSERT INTO `t_category` VALUES ('4', '食品营养', null, '1', '400');
INSERT INTO `t_category` VALUES ('5', '面膜', '2', '2', '0');
INSERT INTO `t_category` VALUES ('6', '面部护理', '2', '2', '0');
INSERT INTO `t_category` VALUES ('7', '孕产护肤', '2', '2', '0');
INSERT INTO `t_category` VALUES ('8', '纸尿裤', '1', '2', '0');
INSERT INTO `t_category` VALUES ('9', '彩妆', '2', '2', '0');
INSERT INTO `t_category` VALUES ('10', '身材护理', '2', '2', '0');

-- ----------------------------
-- Table structure for t_goods
-- ----------------------------
DROP TABLE IF EXISTS `t_goods`;
CREATE TABLE `t_goods` (
  `goods_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '商品id',
  `title` varchar(128) NOT NULL COMMENT '标题',
  `sub_title` varchar(128) NOT NULL COMMENT '子标题',
  `original_cost` double(9,2) NOT NULL COMMENT '原始价格',
  `current_price` double(9,2) NOT NULL COMMENT '当前价格',
  `discount` double(5,2) DEFAULT NULL COMMENT '折扣率',
  `is_free_delivery` int(1) DEFAULT '0' COMMENT '是否包邮 1包邮 0不包邮',
  `category_id` int(10) NOT NULL COMMENT '分类编号',
  PRIMARY KEY (`goods_id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8 COMMENT='商品表';

-- ----------------------------
-- Records of t_goods
-- ----------------------------
INSERT INTO `t_goods` VALUES ('16', '数学15', '这个是⾯试专题概要', '9900.00', '500.00', '0.44', '0', '1');
INSERT INTO `t_goods` VALUES ('17', '数学16', '这个是⾯试专题概要', '9900.00', '632.00', '0.44', '0', '2');
INSERT INTO `t_goods` VALUES ('18', '数学17', '这个是⾯试专题概要', '635.00', '600.00', '0.44', '0', '3');
INSERT INTO `t_goods` VALUES ('20', '测试--xml-插入数据2', '这个是⾯试专题概要', '635.00', '555.00', '0.44', '0', '4');
INSERT INTO `t_goods` VALUES ('21', '测试--xml-插入数据3', '这个是⾯试专题概要', '635.00', '588.00', '0.44', '0', '3');
INSERT INTO `t_goods` VALUES ('22', '测试--xml-插入数据4', '这个是⾯试专题概要', '635.00', '566.00', '0.44', '0', '2');
INSERT INTO `t_goods` VALUES ('23', '测试--xml-插入数据5', '这个是⾯试专题概要', '635.00', '577.00', '0.44', '0', '2');
INSERT INTO `t_goods` VALUES ('24', '小滴课堂1', '不偶像', '400.00', '356.00', '0.44', '0', '1');
INSERT INTO `t_goods` VALUES ('25', '小滴课堂23', '不偶像2', '700.00', '666.00', '0.44', '0', '3');
INSERT INTO `t_goods` VALUES ('26', '小滴课堂155', '不偶像', '400.00', '388.00', '0.44', '0', '4');
INSERT INTO `t_goods` VALUES ('27', '小滴课堂26', '不偶像2', '700.00', '635.00', '0.44', '0', '4');