动态SQL 学习

<select id="findUser" parameterType="Map" resultType="User"> Select * from User where 1=1 <if test="id>0"> and id = #{id} </if> <if test="name!=null"> and name like #{name} </if> <if test="pwd!=null"> and pwd = #{pwd} </if> <if test="address_id>0"> and address_id = #{address_id} </if> </select>
private SqlSession sqlSession ; private PersonDao persondao; private Logger log = Logger.getLogger(JunitTest.class); @Before public void setUp() throws Exception { sqlSession = SqlSessionFactoryUtil.openSession(); persondao = sqlSession.getMapper(PersonDao.class); } @After public void tearDown() throws Exception { sqlSession.close(); } @Test public void findUser() { Map<String, Object> map = new HashMap<String, Object>(); // map.put("id", 1); map.put("name", "张%"); // map.put("pwd", "111111"); // map.put("address_id", 1); List<User> list = persondao.findUser(map); for (User user : list) { log.info(user); // System.out.println(user); } }
增删改返回的是影响的行号
/** * by 用法 */ @Test public void findUser2() { Map<String, Object> map = new HashMap<String, Object>(); map.put("by", "name"); map.put("id", 1); map.put("name", "张%"); map.put("pwd", "111111"); map.put("address_id", 1); List<User> list = persondao.findUser2(map); for (User user : list) { // log.info(user); System.out.println(user); } }
<select id="findUser2" parameterType="Map" resultType="User"> select * from User where 1=1 <choose> <when test="by==‘id‘"> and id = #{id} </when> <when test="by==‘name‘"> and name like #{name} </when> <when test="by==‘pwd‘"> and pwd = #{pwd} </when> <otherwise> and address_id = #{address_id} </otherwise> </choose> </select>
Where 用法
select id="findUser3" parameterType="Map" resultType="User"> select * from user <where> <if test="id>0"> and id = #{id} </if> <if test="name!=null"> and name like #{name} </if> <if test="pwd!=null"> and pwd = #{pwd} </if> </where> </select>
Trim 用法
<select id="findUser4" parameterType="Map" resultType="User"> select * from User <trim prefix="where" prefixOverrides="and|or"> <if test="id>0"> and id = #{id} </if> <if test="name!=null"> and name like #{name} </if> <if test="pwd!=null"> and pwd = #{pwd} </if> </trim>
foreach 用法
<select id="findUser5" parameterType="Map" resultType="User"> select * from user <where> <if test="ids!=null"> and id in  <foreach collection="ids" item="id" open="(" close=")" separator=","> #{id} </foreach> </if> </where> </select>
@Test public void findUser5() { Map< String,Object> map = new HashMap<String, Object>(); List<Integer> list = Arrays.asList(1,2,3,4,5); map.put("ids", list); List<User> list1 = persondao.findUser5(map); for (User user : list1) { // log.info(user); System.out.println(user); } }
limit 用法
SELECT * from `user` LIMIT start,size
/** * 逻辑分页处理 */ @Test public void test() { //传递分页参数,offset : 开始位置 limite: 分页大小 RowBounds bounds = new RowBounds(0,10); List<User> list = pagedao.findUserByPage(bounds); for (User user : list) { System.out.println(user); } }
/** * 物理分页处理 */ @Test public void test2() { //传递分页参数,offset : 开始位置 limite: 分页大小 Map<String, Object> map = new HashMap<String, Object>(); map.put("offset", 1); map.put("limit", 10); List<User> list = pagedao.findUserByPage2(map); for (User user : list) { System.out.println(user); } }
<select id="findUserByPage2" resultType="User" parameterType="Map"> select * from user <if test="offset!=null and limit!=null"> limit #{offset} , #{limit} </if> </select>

动态SQL 学习

标签：