Mybatis(三) 动态SQL

if + where 用法

　　1. if 元素来实现多条件查询

　　　　1.1 UserMapper.xml配置文件

　　<!--查询用户列表 (if)-->
    <select id="getUserList2" resultType="User">
        select u.*,r.roleName from smbms_user u,smbms_role r where u.userRole = r.id
        <if test="null != userRole">
            and u.userRole = #{userRole}
        </if>
        
        <if test="null != userName and '' != userName">
            and u.userName like CONCAT ('%',#{userName},'%')
        </if>
    </select>

　　　　1.2UserMapper接口

　　/**
     * 以@Param注解传入参数查询
     * @param userName
     * @param roleId
     * @return
     */
    public List<User> getUserList2(@Param("userName")String userName,
                                   @Param("userRole")Integer roleId);

　　　　1.3测试方法

　　/**
     * 以@Param注解传入参数查询
     */
    @Test
    public void getUserList(){
        List<User> users = new ArrayList<User>();
        sqlSession = MyBatisUtil.createSqlSession();
        String userName = "孙";
//        Integer roleId = 3;
        Integer roleId = null;
        users = sqlSession.getMapper(UserMapper.class).getUserList2(userName,roleId);

        for(User u: users){
            logger.debug("testGetUserList userCode: " + u.getUserCode() +
                    " and userName: " + u.getUserName() +
                    " and userRole: " + u.getUserRole() +
//                    " and userRoleName: " + u.getUserRoleName() +
                    " and age: " + u.getAge() +
                    " and address: " + u.getAddress());
        }
    }

　　2. where

　　　　2.1 UserMapper.xml配置文件

　　<!--查询用户列表 (if + where) where 会自动拼接and 和 or-->
    <select id="getUserList2" resultType="User">
        select * from smbms_user
        <where>
            <if test="userName != null and userName != ''">
                and userName like CONCAT ('%',#{userName},'%')
            </if>
            <if test="userRole != null">
                and userRole = #{userRole}
            </if>
        </where>
    </select>

　　　　2.2 UserMapper接口

　　/**
     * 以@Param注解传入参数查询
     * @param userName
     * @param roleId
     * @return
     */
    public List<User> getUserList2(@Param("userName")String userName,
                                   @Param("userRole")Integer roleId);

　　　　2.3 测试方法

　　/**
     * 以@Param注解传入参数查询
     */
    @Test
    public void getUserList(){
        List<User> users = new ArrayList<User>();
        sqlSession = MyBatisUtil.createSqlSession();
        String userName = "";
        Integer roleId = 3;
        users = sqlSession.getMapper(UserMapper.class).getUserList2(userName,roleId);

        logger.debug("userlist.size ----> " + users.size());
        for(User u: users){
            logger.debug("testGetUserList userCode: " + u.getUserCode() +
                    " and userName: " + u.getUserName() +
                    " and userRole: " + u.getUserRole() +
                    " and userRoleName: " + u.getUserRoleName() +
                    " and age: " + u.getAge() +
                    " and address: " + u.getAddress());
        }
    }

使用动态SQL语句if来查询操作

　　1. BillMapper.xml配置文件

　　<!--根据多参条件查询订单表-->
    <select id="getBillList2" resultMap="billList">
        SELECT p.`id` as p_id,b.`billCode`,b.`productName`,p.`proCode`,p.`proName`,p.`proContact`,p.`proPhone`,b.`totalPrice`,b.`isPayment`
            FROM smbms_bill b,smbms_provider p
            WHERE b.providerId = p.id
            <if test="productName != null and productName != ''">
                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>

　　2. BillMapper接口

　　/**
     * 根据条件查询订单
     * @param productName
     * @param providerId
     * @param isPayment
     * @return
     */
    public List<Bill> getBillList2(@Param("productName")String productName,
                                   @Param("providerId")Integer providerId,
                                   @Param("isPayment")Integer isPayment);

　　3. 测试方法

　　private Logger logger = Logger.getLogger(BillMapperTest.class);

    /**
     * 根据多参条件查询订单表
     */
    @Test
    public void testGetBillList2(){
        SqlSession session1 = null;
        List<Bill> bills = new ArrayList<Bill>();
        try {
            session1 = MyBatisUtil.createSqlSession();
            String productName = "油";
            Integer providerId = 7;
            Integer isPayment = 2;
            bills = session1.getMapper(BillMapper.class).getBillList2(productName, providerId, isPayment);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MyBatisUtil.closeSqlSession(session1);
        }
        for (Bill bill : bills){
            logger.debug("testGetBillList id: " + bill.getId() +
                    " and BillCode: " + bill.getBillCode() +
                    " and ProductName: " + bill.getProductName() +
                    " and totalPrice: " + bill.getTotalPrice() +
                    " and isPayment: " + bill.getIsPayment() +
                    " , Provider : " + bill.getProvider().getId() +
                    " and providerCode: " + bill.getProvider().getProCode() +
                    " and providerName: " + bill.getProvider().getProName()+
                    " and proContact: " + bill.getProvider().getProContact()+
                    " and proPhone:" + bill.getProvider().getProPhone());
        }

    }

使用动态SQL if +where

　　1. ProviderMapper.xml配置文件

　　<!-- 查询供应商列表 -->
    <select id="getProviderList" resultType="Provider">
        select * from smbms_provider
        <where>
            <if test="proCode != null and proCode != ''">
                and proCode like CONCAT ('%',#{proCode},'%')
            </if>
            <if test="proName != null and proName != ''">
                and proName like CONCAT ('%',#{proName},'%')
            </if>
        </where>
    </select>

　　2. ProviderMapper接口

　　/**
    * 根据供应商编号或名称查询供应商列表(模糊查询)
    */
   public List<Provider>  getProviderList(@Param("proCode")String proCode,
                                          @Param("proName")String proName);

　　3. 测试方法

　　//模糊查询商品列表
    @Test
    public void testGetProviderListByProviderName(){
        List<Provider> providers = new ArrayList<Provider>();
        session = MyBatisUtil.createSqlSession();
        String proCode = "BJ";
        String proName = null;

        providers = session.getMapper(ProviderMapper.class).getProviderList(proCode,proName);
        for (Provider provider : providers){
            logger.debug("testGetProviderList id: " + provider.getId() +
                    " and proCode: " + provider.getProCode() +
                    " and proName: " + provider.getProName()+
                    " and proPhone: " + provider.getProPhone()+
                    " and proContact: " + provider.getProContact()+
                    " and proFax: " + provider.getProFax()+
                    " and creationDate: " + new SimpleDateFormat("yyyy-MM-dd").format(provider.getCreationDate()));
        }
    }

if + trim 用法 比where 更强

　　1. UserMapper.xml配置文件

　　<!--查询用户列表 (if + trim)-->
    <select id="getUserList2" resultType="User">
        select * from smbms_user
        <trim prefix="where" prefixOverrides="and | or">
            <if test="userName != null and userName != ''">
                and userName like CONCAT ('%',#{userName},'%')
            </if>
            <if test="userRole != null">
                and userRole = #{userRole}
            </if>
        </trim>
    </select>

　　2. UserMapper接口

　　/**
     * 以@Param注解传入参数查询
     * @param userName
     * @param roleId
     * @return
     */
    public List<User> getUserList2(@Param("userName")String userName,
                                   @Param("userRole")Integer roleId);

　　3. 测试方法

　　/**
     * 以@Param注解传入参数查询
     */
    @Test
    public void getUserList(){
        List<User> users = new ArrayList<User>();
        sqlSession = MyBatisUtil.createSqlSession();
        String userName = "孙";
        Integer roleId = 3;
        users = sqlSession.getMapper(UserMapper.class).getUserList2(userName,roleId);

        logger.debug("userlist.size ----> " + users.size());
        for(User u: users){
            logger.debug("testGetUserList userCode: " + u.getUserCode() +
                    " and userName: " + u.getUserName() +
                    " and userRole: " + u.getUserRole() +
                    " and userRoleName: " + u.getUserRoleName() +
                    " and age: " + u.getAge() +
                    " and address: " + u.getAddress());
        }
    }

if + set 用法 更新操作

　　1. UserMapper.xml配置文件

　　<!--修改用户信息-->
    <update id="modify" parameterType="User">
        update smbms_user
        <set>
            <if test="userCode != null">userCode = #{userCode},</if>
            <if test="userName != null">userName=#{userName},</if>
            <if test="userPassword != null">userPassword=#{userPassword},</if>
            <if test="gender != null">gender=#{gender},</if>
            <if test="birthday != null">birthday=#{birthday},</if>
            <if test="phone != null">phone=#{phone},</if>
            <if test="address != null">address=#{address},</if>
            <if test="userRole != null">userRole=#{userRole},</if>
            <if test="modifyBy != null">modifyBy=#{modifyBy},</if>
            <if test="modifyDate != null">modifyDate=#{modifyDate}</if>
        </set>
        where id = #{id}
    </update>

　　2. UserMapper接口

　　/**
     * 修改用户
     * @param user
     * @return
     */
    public int modify(User user);

　　3. 测试方法

　　//修改用户
    @Test
    public void testModify() throws ParseException {
        int count = 0;
        User user = new User();
        user.setId(19);
        user.setUserCode("testmodify");
        user.setUserName("测试用户修改");
        user.setUserPassword("0000000");
        Date birthday = new SimpleDateFormat("yyyy-MM-dd").parse("1980-10-10");
        user.setBirthday(birthday);
        user.setCreationDate(new Date());
        user.setAddress("地址测试修改");
        user.setGender(2);
        user.setPhone("13600002222");
        user.setUserRole(2);
        user.setModifyBy(1);
        user.setModifyDate(new Date());
        sqlSession = MyBatisUtil.createSqlSession();
        count = sqlSession.getMapper(UserMapper.class).modify(user);
        logger.debug("testModify count: " + count);
    }

 使用动态SQL语句 if + set 修改操作

　　1. ProviderMapper.xml配置文件

　　<!--根据供应商ID修改供应商信息-->
    <update id="updateProvider" parameterType="Provider">
        update smbms_provider
        <set>
            <if test="proName != null">proName = #{proName}</if>
        </set>
        where id = #{id}
    </update>

　　2. ProviderMapper接口

　　/**
    * 根据供应商ID修改供应商
    * @param provider
    * @return
    */
   public int updateProvider(Provider provider);

　　3. 测试方法

　　 /**
     * 根据ID修改供应商
     */
    @Test
    public void updateProvider(){
        session = MyBatisUtil.createSqlSession();
        Provider provider = new Provider();
        provider.setId(15);
        provider.setProName("供应商");
        int count = session.getMapper(ProviderMapper.class).updateProvider(provider);

        logger.debug(count);
    }

 使用动态SQL语句 if + trim 修改操作 等同于 if + set

　　1. ProviderMapper.xml配置文件

　　<!-- 修改供应商信息 -->
    <update id="modify" parameterType="Provider">
        update smbms_provider 
         <trim prefix="set" suffixOverrides="," suffix="where id = #{id}">
             <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="modifyBy != null">modifyBy=#{modifyBy},</if>
            <if test="modifyDate != null">modifyDate=#{modifyDate},</if>
         </trim>
    </update>

　　2. ProviderMapper接口

　　/**
     * 修改供应商信息
     * @param provider
     * @return
     */
    public int modify(Provider provider);

　　3. 测试方法

　　/**
     * 修改供应商
     */
    @Test
    public void updateProvider(){
        session = MyBatisUtil.createSqlSession();
        Provider provider = new Provider();
        provider.setId(15);
        //provider.setProCode("BJ_GYS123");
        //provider.setProName("供应商测试修改");
        provider.setProContact("张扬");
        provider.setProAddress("供应商测试地址修改");
        //provider.setProPhone("13500002222");
        provider.setModifyBy(1);
        provider.setModifyDate(new Date());
        //provider.setProFax("010-588876565");
        //provider.setProDesc("供应商测试描述修改");
        int count = session.getMapper(ProviderMapper.class).modify(provider);

        logger.debug(count);
    }