Mybatis入门之动态sql

Mybatis入门之动态sql

　　　　通过mybatis提供的各种标签方法实现动态拼接sql。

　　1、if、where、sql、include标签（条件、sql片段）

<sql id="selector">
        select * from account
</sql>

<!--根据性别和名称查询用户 where标签可以去掉第一个前and -->
    <select id="selectUserBySexAndUsername" parameterType="deep.pojo.Account"
        resultType="deep.pojo.Account">
        <include refid="selector"/>
        <where>
            <if test="sex != null and sex != '' ">
                sex = #{sex}
            </if>

            <if test="username != null and username !=''">
                and username = #{username}
            </if>
        </where>
    </select>

 

2、foreach

生成的sql语句是:    select * from acccount where id in (1,2,3,27);

<!-- 多个ID -->
    <select id="selectUserByIds" parameterType="deep.pojo.QueryVo" resultType="deep.pojo.Account">
        <include refid="selector"/>
        <where>
            id in 
            <foreach collection="idsList" item="id" separator="," open="(" close=")">
                #{id}
            </foreach>    
        </where>
    </select>

 

package deep.pojo;

import java.io.Serializable;
import java.util.List;

public class QueryVo implements Serializable{

    private static final long serialVersionUID = 1L;
    
    private Account account;
    
    
    public List<Integer> getIdsList() {
        return idsList;
    }

    public void setIdsList(List<Integer> idsList) {
        this.idsList = idsList;
    }

    public Integer[] getIds() {
        return ids;
    }

    public void setIds(Integer[] ids) {
        this.ids = ids;
    }

    List<Integer> idsList;
    
    Integer[] ids;
    
    
    

    public Account getAccount() {
        return account;
    }

    public void setAccount(Account account) {
        this.account = account;
    }

}

@Test
    public void testFindUserIDs() throws Exception {
        //加载核心配置文件
        InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
        
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        
        
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        
        List<Integer> ids = new ArrayList<Integer>();
        ids.add(1);
        ids.add(2);
        ids.add(3);
        ids.add(27);
        QueryVo vo = new QueryVo();
        vo.setIdsList(ids);
        
        
        List<Account> accountList = userMapper.selectUserByIds(vo);
        
        for (Account a : accountList) {
            System.out.println(a);
        }
    }