搜索到
1
篇与
笔记fen'x
的结果
-
动态SQL 基于OGNL表达式完成多条件查询等逻辑实现用于实现动态SQL的元素主要有iftrimwheresetchoose(when.otherwise)foreach /** * 动态sql查询 * @return */ List<User> selBylike(User user);<select id="selBylike" resultType="User"> select * from smbms_user where 1=1 <if test="userRole!=null and userRole!=0"> and userRole = #{userRole} </if> <if test="userName != null"> and userName like concat('%',#{userName},'%'); </if> </select>@Test public void selBylike(){ SqlSession openSession = MybatisUtil.openSession(); UserMapper mapper = openSession.getMapper(UserMapper.class); User user = new User(); user.setUserRole(3); user.setUserName("张"); List<User> selBylike = mapper.selBylike(user); for (User user2 : selBylike) { System.out.println(user2); } }where简化SQL语句中where条件判断智能处理and和or<select id="selBylike" resultType="User"> select * from smbms_user <!-- where 1=1 --> <where> <if test="userRole!=null and userRole!=0"> and userRole = #{userRole} </if> <if test="userName != null"> and userName like concat('%',#{userName},'%'); </if> </where> </select>set 去逗号演示代码：<update id="upd"> update smbms_user <set> <if test="userCode!=null and userCode!=0"> userCode = #{userCode}, </if> <if test="userName!=null"> userName = #{userName}, </if> <if test="userPassword!=null"> userPassword = #{userPassword}, </if> <if test="userRole!=null and userCode!=0"> userRole = #{userRole}, </if> </set> where id = #{id} </update>trim属性 Prefix （加前缀）suffix （加后缀）prefixOverrides （去前缀）suffixOverrides （去后缀）更灵活地去除多余关键字替换where和set<update id="upd"> update smbms_user <trim prefix="set" suffixOverrides="," suffix="where id=#{id}"> <if test="userCode!=null and userCode!=0"> userCode = #{userCode}, </if> <if test="userName!=null"> userName = #{userName}, </if> <if test="userPassword!=null"> userPassword = #{userPassword}, </if> <if test="userRole!=null and userCode!=0"> userRole = #{userRole}, </if> </trim> </update><select id="selBylike" resultType="User"> select * from smbms_user <trim prefix="where" prefixOverrides="and|or"> <if test="userRole!=null and userRole!=0"> and userRole = #{userRole} </if> <if test="userName != null"> and userName like concat('%',#{userName},'%'); </if> </trim> </select>foreach迭代一个集合，通常用于in条件属性itemindexcollection：必须指定listarraymap-keyopenseparatorclose/** * 使用集合查询 * @return */ List<User> selByidList(@Param("idlist")List<Integer> idlist);<select id="selByidList" resultType="User"> select * from smbms_user <foreach collection="idlist" item="idOne" open="where id in(" close=")" separator=","> #{idOne} </foreach> </select>分页查询/** * 分页查询 * @param start * @param pageSize * @return */ List<User> selBypage(@Param("start")int start,@Param("pageSize")int pageSize);<select id="selBypage" resultType="USer"> select * from smbms_user limit #{start},#{pageSize} </select>@Test //测试类 public void selBypage(){ SqlSession session = MybatisUtil.openSession(); UserMapper mapper = session.getMapper(UserMapper.class); List<User> selectList = mapper.selBypage((1-1)*5,5); for (User userss : selectList) { System.out.println(userss); } }
