MyBatis动态SQL语句

关键字

if

where

trim

foreach

set

【if】

　　　　<select id="selectmany1" parameterType="Map" resultMap="users">
  		select * from test t where 1=1
  		
  			<if test="p1!=null">
  				and username like #{p1}
  			</if>
  			<if test="p2!=null">
  				and password like #{p2}
  			</if>
  		
  	</select>

@Test
	public void selectmany1(){
		Map<String,Object> map = new HashMap<String, Object>();
		
		map.put("p1","张%");
		map.put("p2","%2%");
		//map.put("p2", "%2%");
		List<TestId> list=ss.selectList("com.dao.UsersMapper.selectmany1",map);
		for(TestId t:list){
			System.out.println(t);
			
		}
	}

【where】

<select id="selectmany2" parameterType="Map" resultMap="users">
  		select * from test t 
  		<where>
  			<if test="p1!=null">
  				and username like #{p1}
  			</if>
  			<if test="p2!=null">
  				and password like #{p2}
  			</if>
  		</where>
  	</select>

@Test
	public void selectmany2(){
		Map<String,Object> map = new HashMap<String, Object>();
		
		map.put("p1","张%");
		map.put("p2","%2%");
		//map.put("p2", "%2%");
		List<TestId> list=ss.selectList("com.dao.UsersMapper.selectmany2",map);
		for(TestId t:list){
			System.out.println(t);
			
		}
	}

　　【trim】

<select id="selectmany2" parameterType="Map" resultMap="users">
  		select * from test t 
  		<trim prefix="where" prefixOverrides="and|or">
  			<if test="p1!=null">
  				and username like #{p1}
  			</if>
  			<if test="p2!=null">
  				and password like #{p2}
  			</if>
  		</trim>
  	</select>

　　【foreach】

<select id="selectmany3" parameterType="Map" resultMap="users">
  		select * from test t 
  		<if test="ll!=null">
  			<where>
  				t.username in 
  				<foreach item="u" collection="ll" open="(" separator="," close=")">
  					#{u}
  				</foreach>
  			</where>
  		</if>
  	</select>

public void selectmany3(){
		Map<String,Object> map = new HashMap<String, Object>();
		List<String> l=new ArrayList<String>();
		l.add("张三");
		l.add("六六");
		map.put("ll",l);
		//map.put("p2", "%2%");
		List<TestId> list=ss.selectList("com.dao.UsersMapper.selectmany3",map);
		for(TestId t:list){
			System.out.println(t);
			
		}
	}

　　【set】

<update id="update1" parameterType="testId">
  		update test t 
		<set>
			<if test="username!=null">
				username=#{username},
			</if>
			<if test="password!=null">
				password=#{password},
			</if>
		</set>
		where id=#{id}
  	</update>

public void update1(){
		TestId ti = new TestId();
		ti.setId(new BigDecimal(41));
		ti.setUsername("哈哈嘿嘿");
		ti.setPassword("654321");
		int n = ss.update("com.dao.UsersMapper.update1",ti);
		System.out.println(n);
	}

　　【oracle实现分页效果】

<!-- 实现分页 -->
  	<select id="fenye" parameterType="Map" resultMap="users">
  		select * from 
  		<trim prefix="(" suffix=") b">
  			select a.*,rownum rn from
  			<trim prefix="(" suffix=") a">
  				select * from test t order by t.id desc 
  			</trim>
  			<!-- <if test="size!=null">
  				rownum<=#{size}
  			</if> -->
  		</trim>
  		<where>
  			b.rn between #{start} and #{size}
  		</where>
  	</select>

/**
	 * 实现分页效果
	 */
	
	public void select(){
		Map<String,Object> map = new HashMap<String, Object>();
		map.put("start", 3);
		map.put("size", 3);
		List<TestId> list=ss.selectList("com.dao.UsersMapper.fenye",map);
		for(TestId t:list){
			System.out.println(t);
			
		}
	}

MyBatis动态SQL语句

标签：esc   like   rri   print   for   obj   over   item   put