mybatis(动态sql)
1.动态sql-if
当有值输入才进行sql的动态拼接
<!-- 动态sql-if--> <select id="selectPersonByName" resultType="Person"> select * from `person` where `age` > #{age} <if test="value != null and value != ''"> and `name` like '%${value}%' </if> </select>
/*动态sql-if测试*/ @Test public void selectPersonByName() { SqlSession sqlSession = MybatisUtil.getSqlSession(); PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class); List<Person> persons = personMapper.selectPersonByName(5,"四"); sqlSession.close(); System.out.println(persons); }
2.choose-when-otherwise
<!-- 动态sql choose-when-otherwise --> <!-- when 当有匹配时候执行拼接, 都没有匹配时,拼接otherwise (和 switch case 意思相近)--> <select id="selectPersonByAgeOrName" parameterType="Person" resultType="Person"> select * from `person` where `id` > 0 <choose> <when test="name != null and name != ''"> and name like #{name} </when> <when test="age != null and age != ''"> and age > #{age} </when> <otherwise> and `age`>`id` </otherwise> </choose> </select>
/*<!-- 动态sql choose-when-otherwise -->*/ @Test public void selectPersonByAgeOrName() { SqlSession sqlSession = MybatisUtil.getSqlSession(); PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class); Person person = new Person(); //person.setName("%四%"); List<Person> persons = personMapper.selectPersonByAgeOrName(person); sqlSession.close(); System.out.println(persons); }
3.sql-where (前面写的where `id` > 0比较无脑, 但是 不写会出现sql语法错误)
<!-- 动态sql where--> <!-- 第一个if中 不写and 后面的if中必须写 --> <select id="selectPersonByNameOrAge" parameterType="Person" resultType="Person"> select * from person <where> <if test="name != null and name != ''"> `name` like #{name} </if> <if test="age != null and age != 0"> and `age` = #{age} </if> </where> </select>
/*<!-- 动态sql where -->*/ @Test public void selectPersonByNameOrAge() { SqlSession sqlSession = MybatisUtil.getSqlSession(); PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class); Person person = new Person(); person.setName("%五%"); person.setAge(11); Person persons = personMapper.selectPersonByNameOrAge(person); sqlSession.close(); System.out.println(persons); }
4.动态sql-set
<!-- 动态sql set--> <!-- 避免先查询再修改,只修改指定列 --> <update id="updatePersonByCondition" parameterType="Person"> UPDATE `person` <set> <if test="name != null and name != ''">`name` = #{name} ,</if> <if test="age != null and age != 0"> `age`=#{age} </if> </set> WHERE (`id`=#{id}) </update>
/*<!-- 动态sql set-->*/ @Test public void updatePersonByCondition() { SqlSession sqlSession = MybatisUtil.getSqlSession(); PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class); Person person = new Person("王炸",50); person.setId(3); int count = personMapper.updatePersonByCondition(person); sqlSession.commit(); sqlSession.close(); System.out.println("更新了 count = " + count + "条记录"); }
5.sql-trim (加前缀,去掉多余的部分 可以替换sql-set 特可以替换 sql-where 使用灵活)
<!-- 动态sql trim--> <select id="selectPersonByNameOrAgeTrim" parameterType="Person" resultType="Person"> select * from person <trim prefix="where" suffixOverrides="or | and"> <!-- 与where作用一样 prefix 加前缀,suffixOverrides 去掉多余的 --> <if test="name != null and name != ''"> `name` like #{name} </if> <if test="age != null and age != 0"> and `age` = #{age} </if> </trim> </select>
@Test public void selectPersonByNameOrAgeTrim() { SqlSession sqlSession = MybatisUtil.getSqlSession(); PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class); Person person = new Person(); person.setName("%h%"); List<Person> persons = personMapper.selectPersonByNameOrAgeTrim(person); sqlSession.close(); System.out.println(persons); }
6.sql-foreach
<!-- 动态sql foreach--> <delete id="deletePersonList" parameterType="list"> delete from `person` where id in <foreach collection="list" item="id" open="(" separator="," close=")"> <!--<foreach collection="传递进来的参数类型 如list arra" item="迭代变量,随意" open="开始符号" separator="分割符号" close="结束符号"> 执行结果 (1,2,3) --> #{id} </foreach> </delete>
@Test public void deletePersonList() { SqlSession sqlSession = MybatisUtil.getSqlSession(); PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class); List<Integer> idList = new ArrayList<>(); idList.add(2); idList.add(5); int count = personMapper.deletePersonList(idList); sqlSession.commit(); sqlSession.close(); System.out.println("删除记录 count = " + count); }