SpringMybatis3

练习1：
1.数据访问层接口：BillMapper

package com.leisong.dao;

import com.leisong.domain.Bill;
import java.util.List;

public interface BillMapper {
    //根据商品名称，供应商id，是否付款  进行查询
    public List<Bill> getBillByProName(Bill bill);
    //根据供应商id获取这些对应商的订单列表信息,数组作为参数
    public List<Bill> getBillByProviderIdArray(Integer[] arrs);

    //根据供应商id获取这些对应商的订单列表信息,List作为参数
    public List<Bill> getBillByProviderIdList(List<Integer> list);
}

2.BillMapper的映射文件：BillMapper.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="com.leisong.dao.BillMapper">
    <select id="getBillByProName" resultMap="billList">
    select b.billCode,b.productName,b.totalPrice,b.isPayment,b.creationDate,p.proName
          from smbms_bill b,smbms_provider p where b.productName like concat('%',#{productName},'%') and b.isPayment=#{isPayment}
          and b.providerId=p.id
  </select>
    <!--     遍历数组参数-->
    <select id="getBillByProviderIdArray" resultMap="billList">

        select * from smbms_bill where 1=1
        and providerId in
        <foreach collection="array" item="arrs" open="(" separator="," close=")">
            #{arrs}
        </foreach>


    </select>
    <!--遍历list集合-->
    <select id="getBillByProviderIdList" resultMap="billList">

        select * from smbms_bill where 1=1
        and providerId in
        <foreach collection="list" item="list" open="(" separator="," close=")">
            #{list}
        </foreach>


    </select>

    <resultMap id="billList" type="com.leisong.domain.Bill">
        <id property="id" column="id"></id>
        <result property="providerName" column="proName"></result>
    </resultMap>
</mapper>

3.测试

package com.leisong.test;

import com.leisong.dao.BillMapper;
import com.leisong.domain.Bill;
import com.leisong.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.ArrayList;
import java.util.List;

public class TestBill {
    @Test
    public void test(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        Bill bill = new Bill();
        bill.setProductName("油");
        bill.setProviderId(3);
        bill.setIsPayment(5);
        List<Bill> list = sqlSession.getMapper(BillMapper.class).getBillByProName(bill);

        for(Bill b : list){
            System.out.println(b.getBillCode()+"\t"+b.getProductName()+"\t"+b.getProviderName());
        }
        MybatisUtil.closeSqlsession(sqlSession);

    }
    @Test
    public void test1(){
        //测试数组入参
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        Integer[] arrs = new Integer[]{1,2,3};
        List<Bill> billByProviderArray = sqlSession.getMapper(BillMapper.class).getBillByProviderIdArray(arrs);

        for (Bill bill : billByProviderArray){
            System.out.println(bill.getProviderId()+"\t"+bill.getProductName());
        }

        MybatisUtil.closeSqlsession(sqlSession);
    }

    @Test
    public void test2(){
        //测试list入参
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        List<Integer> list = new ArrayList<Integer>();
        list.add(1);
        list.add(2);
        list.add(3);
        List<Bill> billByProviderArray = sqlSession.getMapper(BillMapper.class).getBillByProviderIdList(list);

        for (Bill bill : billByProviderArray){
            System.out.println(bill.getProviderId()+"\t"+bill.getProductName());
        }

        MybatisUtil.closeSqlsession(sqlSession);
    }

}

练习2
1.数据访问层接口：ProviderMapper

package com.leisong.dao;

import com.leisong.domain.Provider;
import org.apache.ibatis.annotations.Param;

import java.util.Date;
import java.util.List;

public interface ProviderMapper {
    public int count();

    public List<?> getProviderAll();
    //模糊查询（供应商编码，供应商名称，供应商联系人，） 时间范围（在本年）
    public List<Provider> getProvider(@Param("proCode")String proCode, @Param("proName")String proName, @Param("proContact")String  proContact, @Param("stratDate") Date date1, @Param("overDate")Date date2);

}

2.ProviderMapper的映射文件：ProviderMapper.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="com.leisong.dao.ProviderMapper">
    <select id="count" resultType="Integer">
    select count(1) from smbms_provider
  </select>

    <select id="getProviderAll" resultType="com.leisong.domain.Provider">
        select * from smbms_provider;
    </select>
    <select id="getProvider" resultType="com.leisong.domain.Provider">
        SELECT * FROM smbms_provider WHERE 1=1
        <choose>
            <when test="proCode!=null">
                AND proCode like concat('%',#{proCode},'%')
            </when>
            <when test="proName != null">
                AND proName like concat('%',#{proName},'%')
            </when>
            <when test="proContact != null">
                and proContact like concat('%',#{proContact},'%')
            </when>
            <when test="startDate != null and overDate !=null  ">
                and creationDate between #{startDate} and #{overDate}
            </when>
            <otherwise>
                AND featured = 1
            </otherwise>
        </choose>
    </select>


</mapper>

3.测试

package com.leisong.test;

import com.leisong.dao.ProviderMapper;
import com.leisong.domain.Provider;
import com.leisong.util.MybatisUtil;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

public class TestProvider {
    @Test
    public void test(){
        String str = "mybatis_config.xml";
        SqlSession sqlSession = null;

        try {
            InputStream is = Resources.getResourceAsStream(str);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
            sqlSession = sqlSessionFactory.openSession();
            int  count = sqlSession.selectOne("com.leisong.dao.ProviderMapper.count");

            System.out.println("用户个数："+count);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {

            if(sqlSession != null ){
                sqlSession.close();
            }
        }
    }
    @Test
    public void test2(){

        SqlSession sqlSession = MybatisUtil.getSqlSession();

        List<Provider> list = sqlSession.selectList("com.leisong.dao.ProviderMapper.getProviderAll");

        for(Provider p : list){
            System.out.println(p.toString());
        }
        MybatisUtil.closeSqlsession(sqlSession);
    }
    @Test
    public void test3(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();

        Date startDate = null;
        Date overDate = null;
        try {
            startDate = new SimpleDateFormat("yyyy-MM-dd").parse("2020-02-022");

            overDate = new SimpleDateFormat("yyyy-MM-dd").parse("2020-07-22");

        } catch (ParseException e) {
            e.printStackTrace();
        }
        List<Provider> list = sqlSession.getMapper(ProviderMapper.class).getProvider("A", "深", "王",startDate,overDate);

        for (Provider p : list){
            System.out.println(p.toString());
        }
        MybatisUtil.closeSqlsession(sqlSession);
    }

}