作业

 

 

 

直接上代码:

DemoMapper接口:

package com.mapper;

import com.pojo.Goods;

import java.util.List;
import java.util.Map;

public interface DemoMapper {

    /**
     * 通过 模糊商品名称，供应商ID,是否付款 来查询goods表的所有信息和provider表的名称
     * @param map
     * @return
     */

    List<Goods> getGoodsListBy(Map<String,String> map);
}

 

DemoMapper.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">
<!--这里写接口的路径是因为光靠ID，是无法找到SQL语句的，必须通过namespace写接口路径才行-->
<mapper namespace="com.mapper.DemoMapper">

    <resultMap id="userList" type="Goods">
        <result column="goods_code" property="goods_code"></result>
        <result column="goods_name" property="goods_name"></result>
        <result column="money" property="money"></result>
        <result column="pay_state" property="pay_state"></result>
        <result column="start_time" property="start_time"></result>
        <association property="provider" javaType="Provider">
            <result column="provider_id" property="provider_id"></result>
            <result column="provider_name" property="provider_name"></result>
        </association>
    </resultMap>

    <select id="getGoodsListBy" resultMap="userList">

SELECT g.goods_code,g.goods_name,p.provider_id,p.provider_name ,
g.money,g.pay_state,g.start_time FROM goods g , provider p
<trim prefix="where" prefixOverrides="AND |OR" suffix="AND g.provider_id = p.provider_id">
    <if test="userName !=null and userName!=''">
        g.goods_name LIKE concat('%',#{userName},'%')
    </if>
    <if test="user_pay_state!=null and user_pay_state !=''">
        AND g.pay_state =#{user_pay_state}
    </if>
  <if test="user_provider_id!=null and user_provider_id !=''">
      AND p.provider_id = #{user_provider_id}
  </if>

</trim>

    </select>
</mapper>

 

测试类:

import com.mapper.DemoMapper;
import com.pojo.Goods;
import util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Junit {

    @Test
    public void test01(){


        SqlSession sqlSession = MyBatisUtil.createSqlSession();

        DemoMapper mapper = sqlSession.getMapper(DemoMapper.class);

        Map<String,String> map = new HashMap<String, String>();
        map.put("userName","");
        map.put("user_pay_state","");
        map.put("user_provider_id","");
        List<Goods> list = mapper.getGoodsListBy(map);
        for (Goods goods : list) {

            System.out.println(goods.getGoods_code()+" "+goods.getGoods_name()+" "+goods.getProvider().getProvider_id()+" "+goods.getProvider().getProvider_name()+" "+goods.getMoney()+" "+goods.getPay_state()+" "+goods.getStart_time());
        }
        MyBatisUtil.closeSqlSession(sqlSession);
    }
}

这里的测试我就不一一说了，我已经验证过，4中情况下都可以输入成功:

1、全部都写   2、全部都没写（查询所有） 3、任一写一个  4、任一写两个