动态sql · JAVA

[TOC] # 简介基于OGNL表达式来简化操作. - if - choose(when, otherwise) - trim(where, set) - foreach **注意:xml中特殊符号如",>,<等这些都需要使用转义字符** # 内置参数 mybatis有两个内置参数 * `_parameter`: 代表整个参数 - 单个参数: `_parameter`就是这个参数 - 多个参数: 参数会封装为一个map, `_parameter`代表就是这个map,用.key获取值 * `_databaseId`: 如果配置了databaseIdProvider标签,这个就代表别名mysql/oracle这些 ~~~ <sql id="insertColumn"> <if test="_databaseId=='oracle'"> employee_id,last_name,email </if> <if test="_databaseId=='mysql'"> last_name,email,gender,d_id </if> </sql> ~~~ # if标签 **注意** ~~~ <if test="status !=null and status != '' or status==0"> AND u.status = #{status} </if> status这个字段如果值为0,那么mybatis会默认status为空,就不会有添加里面的AND 语句了, 所有在if里面加上or status ==0 ,就解决了 ~~~ mybatis会把number类型的0当成false，非0为true，类似于JavaScript中`0!=''`结果为false，从而导致=0的时候判断总是不成立。其实只有String类型才需要判断`!=''`,number类型根本不需要。 ## 数值 ~~~ <if test="value=0"></if> ~~~ ## 字符串 ~~~ <if test='str!=null and str!="" '></if> ~~~ 记得是外面是单引号，里面是双引号。 ## boolean 第一种 ~~~ <if test="boolvalue"> //boolvalue=true 时 </if> ~~~ 第二种 ~~~ <if test="boolvalue==true"> //boolvalue=true 时 </if> ~~~ ## 使用 if标签中不能写`&&`要写and sql ~~~  <select id="selectUserBySexAndUsername" parameterType="pojo.User" resultType="pojo.User"> select * from user where 1=1 <if test="sex != null and sex != ''"> sex = #{sex} </if> <if test="username != null and username != ''"> and username = #{username} </if> </select> ~~~ 另外要注意 where 1=1 的作用~! 防止sql拼接问题的`where and` 1. `where 1=1` 以后条件都`and xxx` 2. 用where标签包裹接口 ~~~ // 根据性别和名称查询用户 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); } ~~~ # choose(when,otherwise)标签 **choose、when、otherwise** 类似于Java中的`switch case default` ~~~ <select id="getEmpByChoose" resultType="Emp" parameterType="Emp"> select * from emp where 1 = 1 <choose> <when test="job != null"> and job = #{job} </when> <when test="deptno != null"> and deptno = #{deptno} </when> <otherwise> and mgr = #{mgr} </otherwise> </choose> </select> ~~~ # set标签 ~~~ <update id="updateEmpBySet" parameterType="Emp"> update emp <set> <if test="ename != null and ename != ''"> ename = #{ename}, </if> <if test="job != null and job != ''"> job = #{job}, </if> </set> where empno = #{empno} </update> ~~~ # trim * prefix：前缀，增加一些指定的内容 * prefixOverrides：前缀重写，删除指定的内容 * suffixOverrides：后缀重写，删除指定的内容 * suffix：后缀，增加一些指定的内容 **注意4个属性值的作用位置** 依次是 prefix、prefixOverrides、suffixOverrides、suffix **例子一** ~~~ select * from user <trim prefix="where" prefixOverrides=" and | or " > <if test=" name!=null and name.length()>0 "> and name=#{name} </if> <if test=" gender!=null and gender.length()>0 " > and gender=#{gender} </if> </trim> ~~~ > prefix：在前面增加 where > prefixOverrides：删除第一个and 或者 or 假如说name和gender的值都不为null的话打印的SQL为： ~~~ select * from user where name = 'xx' and gender = 'xx' ~~~ **例子二** ~~~ update user <trim prefix="set" suffixOverrides="," suffix=" where id = #{id} "> <if test=" name != null and name.length()>0 " > name=#{name} , </if> <if test=" gender != null and gender.length()>0 " > gender=#{gender} , </if> </trim> ~~~ > prefix：在前端增加 set > suffixOverrides：删除最后一个逗号（也可以是其他的标记，就像是上面前缀中的and一样） > suffix：在最后面增加 where id = #{id} 假如说name和gender的值都不为null的话打印的SQL为： ~~~ update user set name='xx' , gender='xx' where id='x' ~~~ # 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> ~~~ # foreach标签 **配合in的时候,要判断in里面是不是空** 标签用于遍历集合，它的属性: * collection:代表要遍历的集合元素，注意编写时不要写#{} * open:代表语句的开始部分 * close:代表结束部分 * item:代表遍历集合的每个元素，生成的变量名 * sperator:代表分隔符 * index: 索引,遍历list是索引,item是当前值.遍历map是key,item是值 **一种`List<Object>`** 向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=")" index="i"> #{id} </foreach> </where> </select> ~~~ 或者写成这样 ~~~ <select id="selectUserByIds" parameterType="pojo.QueryVo" resultType="pojo.Orders"> <include refid="selector" /> <where> <if test="idList != null and idList.size()>0"> <foreach collection="idsList" item="id" separator="," open="and id in (" close=")"> #{id} </foreach> </if> </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); } ~~~ **另一种Array** 写接口 ~~~ 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); } ~~~ # bind bind 元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文。比如: ~~~ <select id="selectBlogsLike" resultType="Blog"> <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" /> SELECT * FROM BLOG WHERE title LIKE #{pattern} </select> ~~~ 值是在value取,取出拼接好,name那做个别名,然后下面就可以用这个别名. 可能这个`value->name->sql的#{}`很多人不适应这个顺序. # sql片段 ~~~  <sql id="insertColumn"> <if test="_databaseId=='oracle'"> employee_id,last_name,email </if> <if test="_databaseId=='mysql'"> last_name,email,gender,d_id </if> </sql> ~~~