動態sql · java

[TOC] # if標簽 sql ~~~  <select id="selectUserBySexAndUsername" parameterType="pojo.User" resultType="pojo.User"> select * from user where <if test="sex != null and sex != ''"> sex = #{sex} </if> <if test="username != null and username != ''"> and username = #{username} </if> </select> ~~~ 接口 ~~~ // 根據性別和名稱查詢用戶 public List<User> selectUserBySexAndUsername(User user); ~~~ 測試 ~~~ // 加載核心配置文件 String resource = "SqlMapConfig.xml"; InputStream in = Resources.getResourceAsStream(resource); // 創建SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in); // 創建SqlSession SqlSession sqlSession = sqlSessionFactory.openSession(); // SqlSession幫我生成一個實現類 UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = new User(); user.setSex("1"); user.setUsername("張小明"); List<User> users = mapper.selectUserBySexAndUsername(user); for (User user2 : users) { System.out.println(user2); } ~~~ # where 我們把sql改造下 ~~~  <select id="selectUserBySexAndUsername" parameterType="pojo.User" resultType="pojo.User"> select * from user <where> <if test="sex != null and sex != ''"> sex = #{sex} </if> <if test="username != null and username != ''"> and username = #{username} </if> </where> </select> ~~~ # sql片段 ~~~ <sql id="selector"> select * from user </sql>  <select id="selectUserBySexAndUsername" parameterType="pojo.User" resultType="pojo.User"> <include refid="selector"></include> <where> <if test="sex != null and sex != ''"> sex = #{sex} </if> <if test="username != null and username != ''"> and username = #{username} </if> </where> </select> ~~~ # foreach標簽向sql傳遞數組或list,mybatis使用foreach解析根據多個id查詢用戶信息,查詢sql,select * from user where id in (1,10,24) 接口 ~~~ public List<Orders> selectUserByIds(QueryVo vo); ~~~ QueryVo這個pojo類中添加 ~~~ List<Integer> idsList; public List<Integer> getIdsList() { return idsList; } public void setIdsList(List<Integer> idsList) { this.idsList = idsList; } ~~~ sql ~~~ <sql id="selector"> select * from orders </sql>  <select id="selectUserByIds" parameterType="pojo.QueryVo" resultType="pojo.Orders"> <include refid="selector" /> <where> id in <foreach collection="idsList" item="id" separator="," open="(" close=")"> #{id} </foreach> </where> </select> ~~~ 測試 ~~~ // 加載核心配置文件 String resource = "SqlMapConfig.xml"; InputStream in = Resources.getResourceAsStream(resource); // 創建SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in); // 創建SqlSession SqlSession sqlSession = sqlSessionFactory.openSession(); // SqlSession幫我生成一個實現類 // UserMapper mapper = sqlSession.getMapper(UserMapper.class); OrderMapper mapper = sqlSession.getMapper(OrderMapper.class); List<Integer> ids = new ArrayList<>(); ids.add(3); ids.add(4); QueryVo vo = new QueryVo(); vo.setIdsList(ids); List<Orders> rel = mapper.selectUserByIds(vo); for (Orders order : rel) { System.out.println(order); } ~~~ **另一種** 寫接口 ~~~ public List<Orders> selectOrdersByIds(Integer[] ids); ~~~ 配置文件 ~~~  <select id="selectOrdersByIds" parameterType="pojo.QueryVo" resultType="pojo.Orders"> <include refid="selector" /> <where> id in <foreach collection="array" item="id" separator="," open="(" close=")"> #{id} </foreach> </where> </select> ~~~ 這邊要寫array的測試 ~~~ // 加載核心配置文件 String resource = "SqlMapConfig.xml"; InputStream in = Resources.getResourceAsStream(resource); // 創建SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in); // 創建SqlSession SqlSession sqlSession = sqlSessionFactory.openSession(); // SqlSession幫我生成一個實現類 OrderMapper mapper = sqlSession.getMapper(OrderMapper.class); Integer[] ids = new Integer[3]; ids[0] = 3; ids[1] = 4; ids[2] = 5; List<Orders> rel = mapper.selectOrdersByIds(ids); for (Orders order : rel) { System.out.println(order); } ~~~