动态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); } ~~~