mybatis动态SQL

仅为关键代码的记录和学习，自己摸索的，很可能会有错。。

一、改造订单表的查询功能，使用SQL完善此功能（if）

查询条件：商品名称、供应商id、是否付款

billMapper

public interface BillMapper {
//    public List<Bill> getBillList(Bill bill);
    public List<Bill> getBillList(@Param("productName")String productName,
                                  @Param("providerId")Integer providerId,
                                  @Param("isPayment") Integer isPayment);
}

billMapper.xml

<select id="getBillList" resultMap="billList" parameterType="Bill">
   select b.*,p.proName from smbms_bill b,smbms_provider p where b.providerId = p.id
     <if test="productName!=null">
   and b.productName like CONCAT ('%',#{productName},'%')
     </if>
     <if test="providerId!=null">
   and b.providerId = #{providerId}
     </if>
     <if test="isPayment!=null">
   and b.isPayment = #{isPayment}
     </if>
</select>

BillMapperTest

public void testGetBillList(){
    SqlSession sqlSession=null;
    List<Bill> billList=new ArrayList<Bill>();
    try {
        sqlSession=MyBatisUtil.getSession();
        String productName=null;
        Integer providerId=13;
        Integer isPayment=2;
        billList=sqlSession.getMapper(BillMapper.class).getBillList(productName,providerId,isPayment);
    }catch (Exception e){
        e.printStackTrace();
    }finally {
        MyBatisUtil.closeSqlSession(sqlSession);
    }
    logger.debug("billlist.size--->"+billList.size());
    for (Bill b:billList){
        logger.debug("订单id："+b.getId()+"订单编号：" + b.getBillCode() +
            " 商品名称：" + b.getProductName() + " 供应商名称：" + b.getProviderName() +
            " 账单金额：" + b.getTotalPrice() + " 是否付款：" + b.getIsPayment() +
            " 创建时间：" + b.getCreationDate());
    }
}

 

二、改造供应商表的查询功能，使用动态SQL完善此功能（if where）

查询条件：供应商编码、供应商名称

ProviderMapper

public interface ProviderMapper {
    //public List<Provider> getProvider(Provider provider);
    public List<Provider> getProvider(@Param("proCode") String proCode,
                                      @Param("proName") String proName);
    public int addPro(Provider provider);
}

ProviderMapper.xml

<select id="getProvider" resultMap="proList" >
   select * from smbms_provider
   <where>
     <if test="proCode!=null">
         and proCode like CONCAT ('%',#{proCode},'%')
     </if>
     <if test="proName!=null">
         and proName like CONCAT ('%',#{proName},'%')
     </if>
   </where>
</select>

ProTest

public void testPro() {
    SqlSession sqlSession=null;
    List<Provider> providerList=new ArrayList<Provider>();
    int count = 0;
    try {
        sqlSession=MyBatisUtil.getSession();
        String proCode="BJ";
        String proName=null;
        providerList=sqlSession.getMapper(ProviderMapper.class).getProvider(proCode,proName);
    }catch (Exception e){
        e.printStackTrace();
    }finally {
        MyBatisUtil.closeSqlSession(sqlSession);
    }
    logger.debug("billlist.size--->"+providerList.size());
    for (Provider b:providerList){
        logger.debug("供应商id："+b.getId()+"供应商编码：" + b.getProCode() +
                " 供应商名称：" + b.getProName() + " 联系人：" + b.getProContact() +
                " 联系电话：" + b.getProPhone() + " 传真：" + b.getProFax() +
                " 创建时间：" + b.getCreationDate());
    }
}

 

三、改造供应商表的修改功能（if set）

ProviderMapper

public int update(Provider provider);

ProviderMapper.xml

<update id="update" parameterType="Provider">
    update smbms_provider
    <set>
        <if test="proCode!=null">proCode=#{proCode},</if>
        <if test="proName!=null">proName=#{proName},</if>
        <if test="proDesc!=null">proDesc=#{proDesc},</if>
        <if test="proContact!=null">proContact=#{proContact},</if>
        <if test="proPhone!=null">proPhone=#{proPhone},</if>
        <if test="proAddress!=null">proAddress=#{proAddress},</if>
        <if test="proFax!=null">proFax=#{proFax},</if>
        <if test="createdBy!=null">createdBy=#{proCode},</if>
        <if test="creationDate!=null">creationDate=#{creationDate},</if>
        <if test="modifyBy!=null">modifyBy=#{modifyBy},</if>
        <if test="modifyDate!=null">modifyDate=#{modifyDate},</if>
    </set>
    where id=#{id}
</update>

ProTest

public void testupdate(){
    SqlSession sqlSession = null;
    int count = 0;
    try {
        Provider provider = new Provider();
        provider.setId(18);
        provider.setProName("测试供应商修改");
        provider.setProContact("联络人修改");
        provider.setProAddress("测试供应商地址修改");
        provider.setModifyBy(1);
        provider.setModifyDate(new Date());
        sqlSession=MyBatisUtil.getSession();
        count = sqlSession.getMapper(ProviderMapper.class).update(provider);
        //模拟异常，进行回滚
        sqlSession.commit();
    } catch (Exception e){
        e.printStackTrace();
        sqlSession.rollback();
        count = 0;
    } finally {
        MyBatisUtil.closeSqlSession(sqlSession);
    }
    logger.debug("testAddPro count: " + count);
}

 

四、供应商列表，获取其下的订单列表

BilMapper

public List<Bill> getBillByProId_foreach_array(Integer[] ProIds);

 

BilMapper.xml

<select id="getBillByProId_foreach_array" resultMap="billMapbyproid">
    select * from smbms_bill where providerId in
    <foreach collection="array" item="ProIds" open="(" separator="," close=")">
        #{ProIds}
    </foreach>
</select>
<resultMap id="billMapbyproid" type="Bill">
    <id property="id" column="id"/>
    <result property="billCode" column="billCode"/>
    <result property="productName" column="productName"/>
    <result property="providerName" column="providerName"/>
    <result property="totalPrice" column="totalPrice"/>
    <result property="isPayment" column="isPayment"/>
    <result property="creationDate" column="creationDate"/>
</resultMap>

BillTest

public void testgetbillbyproid_foreach(){
    SqlSession sqlSession=null;
    List<Bill> billList=new ArrayList<Bill>();
    Integer[] proIds={1,14};
    try{
        sqlSession=MyBatisUtil.getSession();
        billList=sqlSession.getMapper(BillMapper.class).getBillByProId_foreach_array(proIds);
    }catch (Exception e){
        e.printStackTrace();
    }finally {
        MyBatisUtil.closeSqlSession(sqlSession);
    }
    logger.debug("billlist.size--->"+billList.size());
    for (Bill b:billList){
        logger.debug("订单id："+b.getId()+"订单编号：" + b.getBillCode() +
                " 商品名称：" + b.getProductName() + " 供应商名称：" + b.getProviderName() +
                " 账单金额：" + b.getTotalPrice() + " 是否付款：" + b.getIsPayment() +
                " 创建时间：" + b.getCreationDate());
    }
}

 

五、根据订单编码和指定供应商列表获取其下的订单列表

BillMapper

public List<Bill> getbillbymap(Map<String,Object>billmap);

 

BillMapper.xml

<select id="getbillbymap" resultMap="billMapbyproid">
    select * from smbms_bill where billCode like CONCAT ('%',#{billcode},'%') and providerId in
    <foreach collection="proid" item="billmap" open="(" separator="," close=")">
        #{billmap}
    </foreach>
</select>

BillTest

public void testgetbillbyproid_foreach_map(){
    SqlSession sqlSession=null;
    Map<String,Object> conmap=new HashMap<String,Object>();
    List<Bill> billList=new ArrayList<Bill>();
    List<Integer> prolist=new ArrayList<Integer>();
    prolist.add(1);
    prolist.add(14);
    conmap.put("billcode","07");
    conmap.put("proid",prolist);
    try{
        sqlSession=MyBatisUtil.getSession();
        billList=sqlSession.getMapper(BillMapper.class).getbillbymap(conmap);
    }catch (Exception e){
        e.printStackTrace();
    }finally {
        MyBatisUtil.closeSqlSession(sqlSession);
    }
    logger.debug("billlist.size--->"+billList.size());
    for (Bill b:billList){
        logger.debug("订单id："+b.getId()+"订单编号：" + b.getBillCode() +
                " 商品名称：" + b.getProductName() +
                " 账单金额：" + b.getTotalPrice() + " 是否付款：" + b.getIsPayment() +
                " 创建时间：" + b.getCreationDate());
    }
}

 

六、实现按条件查询供应商表（when、otherwise）

查询条件：供应商编码、供应商名称、供应商联系人、创建时间范围

ProviderMapper

public List<Provider> getProvider_choose(@Param("proCode") String proCode,
                                         @Param("proName") String proName,
                                         @Param("proContact") String proContact,
                                         @Param("creationDate") Date creationDate);

 

ProviderMapper.xml

<select id="getProvider_choose" resultMap="proList">
    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>
        <otherwise>
            and YEAR (creationDate)=YEAR (#{creationDate})
        </otherwise>
    </choose>
</select>

ProTest

@Test
public void testchoose(){
    SqlSession sqlSession = null;
    int count = 0;
    List<Provider> providerList=new ArrayList<Provider>();
    try {
        sqlSession=MyBatisUtil.getSession();
        String proCode=null;
        String proName=null;
        String proContact=null;
        Date creationDate=new SimpleDateFormat("yyyy-MM-dd").parse("2016-01-01");
        providerList=sqlSession.getMapper(ProviderMapper.class).getProvider_choose(proCode,proName,proContact,creationDate);
        //模拟异常，进行回滚
        sqlSession.commit();
    } catch (Exception e){
        e.printStackTrace();
        sqlSession.rollback();
        count = 0;
    } finally {
        MyBatisUtil.closeSqlSession(sqlSession);
    }
    logger.debug("billlist.size--->"+providerList.size());
    for (Provider b:providerList){
        logger.debug("供应商id："+b.getId()+"供应商编码：" + b.getProCode() +
                " 供应商名称：" + b.getProName() + " 联系人：" + b.getProContact() +
                " 创建时间：" + b.getCreationDate());
    }
}

 

 

感觉是会有很多问题啦。。不过总算是把学到目前为止的这些东西搞明白了，mybatis-config的配置、xml文件的配置和java类的分开又结合，不得不说确实这个思路很厉害，觉得发明这个的人确实很厉害。。。