mybatis動(dòng)態(tài)SQL

僅為關(guān)鍵代碼的記錄和學(xué)習(xí)，自己摸索的，很可能會(huì)有錯(cuò)。。

一、改造訂單表的查詢功能，使用SQL完善此功能（if）

查詢條件：商品名稱、供應(yīng)商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()+"訂單編號(hào)：" + b.getBillCode() +
            " 商品名稱：" + b.getProductName() + " 供應(yīng)商名稱：" + b.getProviderName() +
            " 賬單金額：" + b.getTotalPrice() + " 是否付款：" + b.getIsPayment() +
            " 創(chuàng)建時(shí)間：" + b.getCreationDate());
    }
}

二、改造供應(yīng)商表的查詢功能，使用動(dòng)態(tài)SQL完善此功能（if where）

查詢條件：供應(yīng)商編碼、供應(yīng)商名稱

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("供應(yīng)商id："+b.getId()+"供應(yīng)商編碼：" + b.getProCode() +
                " 供應(yīng)商名稱：" + b.getProName() + " 聯(lián)系人：" + b.getProContact() +
                " 聯(lián)系電話：" + b.getProPhone() + " 傳真：" + b.getProFax() +
                " 創(chuàng)建時(shí)間：" + b.getCreationDate());
    }
}

三、改造供應(yīng)商表的修改功能（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("測(cè)試供應(yīng)商修改");
        provider.setProContact("聯(lián)絡(luò)人修改");
        provider.setProAddress("測(cè)試供應(yīng)商地址修改");
        provider.setModifyBy(1);
        provider.setModifyDate(new Date());
        sqlSession=MyBatisUtil.getSession();
        count = sqlSession.getMapper(ProviderMapper.class).update(provider);
        //模擬異常，進(jìn)行回滾
        sqlSession.commit();
    } catch (Exception e){
        e.printStackTrace();
        sqlSession.rollback();
        count = 0;
    } finally {
        MyBatisUtil.closeSqlSession(sqlSession);
    }
    logger.debug("testAddPro count: " + count);
}

四、供應(yīng)商列表，獲取其下的訂單列表

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()+"訂單編號(hào)：" + b.getBillCode() +
                " 商品名稱：" + b.getProductName() + " 供應(yīng)商名稱：" + b.getProviderName() +
                " 賬單金額：" + b.getTotalPrice() + " 是否付款：" + b.getIsPayment() +
                " 創(chuàng)建時(shí)間：" + b.getCreationDate());
    }
}

五、根據(jù)訂單編碼和指定供應(yīng)商列表獲取其下的訂單列表

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()+"訂單編號(hào)：" + b.getBillCode() +
                " 商品名稱：" + b.getProductName() +
                " 賬單金額：" + b.getTotalPrice() + " 是否付款：" + b.getIsPayment() +
                " 創(chuàng)建時(shí)間：" + b.getCreationDate());
    }
}

六、實(shí)現(xiàn)按條件查詢供應(yīng)商表（when、otherwise）

查詢條件：供應(yīng)商編碼、供應(yīng)商名稱、供應(yīng)商聯(lián)系人、創(chuàng)建時(shí)間范圍

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);
        //模擬異常，進(jìn)行回滾
        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("供應(yīng)商id："+b.getId()+"供應(yīng)商編碼：" + b.getProCode() +
                " 供應(yīng)商名稱：" + b.getProName() + " 聯(lián)系人：" + b.getProContact() +
                " 創(chuàng)建時(shí)間：" + b.getCreationDate());
    }
}

感覺是會(huì)有很多問題啦。。不過總算是把學(xué)到目前為止的這些東西搞明白了，mybatis-config的配置、xml文件的配置和java類的分開又結(jié)合，不得不說確實(shí)這個(gè)思路很厲害，覺得發(fā)明這個(gè)的人確實(shí)很厲害。。。

posted @ 2022-03-27 00:41 水煮小白菜閱讀(479) 評(píng)論(0) 收藏舉報(bào)

刷新頁面返回頂部

codesslave

mybatis動(dòng)態(tài)SQL

公告