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); } }