摘要：
=''“˃andname=#｛name｝Test 1//测试动态SQLif@TestpublicvoidtestGetStuByIf（）throwsIOException｛Stringresource=“mybatis config.xml”；InputStreaminputStream=Resources.getResourceAsStream；SqlSessionFactorysqlSessionFactory=newSqlSessionFactoryBuilder（）.build；SqlSessionsqlSession=sqlSessionFactory.openSession（）；StudentMappersudentMapper=sqlSession.getMapper；Studentstudent=studentMapper.getStuByIf；System.out.println；sqlSession.close（）；｝测试结果语句：Test 2//测试动态sqlif@TestpublicvoidtestGetStuByIf（）throwsIOException｛Stringresource=“mybatis config.xml”；InputStreaminputStream=Resources.getResourceAsStream；SqlSessionFactorysqlSessionFactory=newSqlSessionFactoryBuilder（）.build；SqlSessionsqlSession=sqlSessionFactory.openSession（）；StudentMappersudentMapper=sqlSession.getMapper；Studentstudent=studentMapper.getStuByIf；System.out.println；sqlSession.close（）；｝测试结果语句为：测试方法中的参数ID为空时存在问题//测试动态SQLif@TestpublicvoidtestGetStuByIf（）throwsIOException｛Stringresource=“mybatis config.xml”；InputStreaminputStream=Resources.getResourceAsStream；SqlSessionFactorysqlSessionFactory=newSqlSessionFactoryBuilder（）.build；SqlSessionsqlSession=sqlSessionFactory.openSession（）；StudentMappersudentMapper=sqlSession.getMapper；Studentstudent=studentMapper.getStuByIf；System.out.println；sqlSession.close（）；｝测试结果显示SQL语句此时出错，因此我们需要解决这个问题：解决方案1：在其中添加1=1，即StudentMapper配置更改为select*from studenthere1andname=#｛name｝˂/select
Student.java:
StudentMapper接口定义方法：
StudentMapper配置文件进行配置
<select id="getStuByIf" resultType="student"> select * from student where <!-- test:判断表达式；里面使用的是OGNL表达式，可百度查询OGNL的使用 OGNL会自动进行字符串与数字的转换判断；字符串"0"和数字0是一样的 从参数中取值判断;遇见特殊符号应该写转义字符例如&（&）符号 --> <if test="id!=null"> id = #{id} </if> <if test="name!=null && name.trim()!=''"> and name = #{name} </if> </select>
测试1（id和name都有值）
//测试动态sql的if @Test public void testGetStuByIf() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); SqlSession sqlSession = sqlSessionFactory.openSession(); StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class); Student student = studentMapper.getStuByIf(new Student(1, "小明")); System.out.println(student); sqlSession.close(); }
测试结果的语句：
测试2（当id有值，name为空时）
//测试动态sql的if @Test public void testGetStuByIf() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); SqlSession sqlSession = sqlSessionFactory.openSession(); StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class); Student student = studentMapper.getStuByIf(new Student(1, "")); System.out.println(student); sqlSession.close(); }
测试结果语句为：
此时有一个问题当测试方法中传参的id为空时
//测试动态sql的if @Test public void testGetStuByIf() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); SqlSession sqlSession = sqlSessionFactory.openSession(); StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class); Student student = studentMapper.getStuByIf(new Student(null, "小明")); System.out.println(student); sqlSession.close(); }
测试结果为
此时sql语句出错（通过查看StudentMapper的sql配置可找到原因）
所以我们需要解决此问题：
方案一：在where后面加一个1=1
即StudentMapper配置改为
<select id="getStuByIf" resultType="student"> select * from student where 1=1 <!-- test:判断表达式；里面使用的是OGNL表达式，可百度查询OGNL的使用 OGNL会自动进行字符串与数字的转换判断；字符串"0"和数字0是一样的 从参数中取值判断;遇见特殊符号应该写转义字符例如&符号和'符号 --> <if test="id!=null"> id = #{id} </if> <if test="name!=null && name.trim()!=''"> and name = #{name} </if> </select>
此时测试结果为
方案二：将条件包括在<where>标签中
mybatis中的<where>标签会把多出来的and或者or自动去掉
即StudentMapper的配置文件改为：
<select id="getStuByIf" resultType="student"> select * from student <where> <!-- test:判断表达式；里面使用的是OGNL表达式，可百度查询OGNL的使用 OGNL会自动进行字符串与数字的转换判断；字符串"0"和数字0是一样的 从参数中取值判断;遇见特殊符号应该写转义字符例如&符号和'符号 --> <if test="id!=null"> id = #{id} </if> <if test="name!=null && name.trim()!=''"> and name = #{name} </if> </where> </select>
测试结果
方案三：通过<trim>标签去掉条件中前面或者后面多余的字符
为了测试方便在StudentMapper接口中再定义一个方法
StudentMapper的配置文件改为：
<select id="getStuByTrim" resultType="student"> select * from student <!-- 后面多出来的and或者or <where>标签不能解决 则此时使用<trim>标签,<trim>标签体中是整个字符串拼串后的结果 prefix=""：给拼串后的字符串加一个前缀 prefixOverrides=""：去掉整个字符串前面多余的字符 同理也有 suffix=""：给拼串后的字符串加一个后缀 suffixOverrides="" 去掉整个字符串后面多余的字符--> <trim prefix="where" prefixOverrides="and" > <if test="id!=null"> id = #{id} </if> <if test="name!=null && name.trim()!=''"> and name = #{name} </if> </trim> </select>
测试方法：
//测试动态sql的<trim> @Test public void testGetStuByTrim() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); SqlSession sqlSession = sqlSessionFactory.openSession(); StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class); Student student = studentMapper.getStuByTrim(new Student(null, "小明")); System.out.println(student); sqlSession.close(); }
测试结果
