Mybatis之动态SQL中的元素

1.简介

1.if元素

简单举例
Mapper文件配置

	<!-- if元素的使用 -->
	<select id="findCustomerByNameAndJobs"
		parameterType="com.lin.po.Customer" resultType="com.lin.po.Customer">
		select * from t_customer where 1=1
		<if test="username!=null and username!=''">
			and username like
			concat('%',#{username},'%')
		</if>
		<if test="jobs!=null and jobs !=''">
			and jobs=#{jobs}
		</if>
	</select>

单元测试

// if元素的测试
	// 根据客户姓名和职业组合条件查询客户信息列表
	@Test
	public void findCunstomerByNameAndJobs() {
		SqlSession sqlSession = MybatisUtils.getSqlSession();
		Customer customer = new Customer();
		customer.setUsername("Tom");
		//customer.setJobs("teacher");
		List<Customer> list = sqlSession.selectList("com.lin.mapper.CustomerMapper.findCustomerByNameAndJobs",
				customer);
		//输出查询结果信息
		for (Customer customer2 : list) {
			System.out.println(customer2);
		}	
		sqlSession.close();
	}

mybatis-config.xml中添加mapper依赖

<mappers>
		<mapper resource="com/lin/po/CustomerMapper.xml" />
		<mapper resource="com/lin/po/UserMapper.xml" />
		<mapper resource="com/lin/mapper/CustomerMapper.xml"/>
	</mappers>

2.choose及其子元素

代码演示
Mapper文件配置

<!-- <choose>(<when>,<otherwise>)元素使用 -->
	<select id="findCustomerByNameOrJobs"
		parameterType="com.lin.po.Customer" resultType="com.lin.po.Customer">
		select * from t_customer where 1=1
		<choose>
			<when test="username!=null and username!=''">
				and username like
				concat('%',#{username},'%')
			</when>
			<when test="jobs!=null and jobs !=''">
				and jobs=#{jobs}
			</when>
			<otherwise>
				and phone is not null
			</otherwise>
		</choose>
	</select>

单元测试

@Test
	// choose元素的测试
	public void findCustomerByNameOrJobs() {
		SqlSession sqlSession = MybatisUtils.getSqlSession();
		Customer customer = new Customer();
		customer.setUsername("Tom");
		customer.setJobs("teacher");
		List<Customer> list = sqlSession.selectList("com.lin.mapper.CustomerMapper.findCustomerByNameOrJobs", customer);
		// 输出查询结果信息
		for (Customer customer2 : list) {
			System.out.println(customer2);
		}

		sqlSession.close();

	}

3.where ,trim元素

where元素

<!-- <choose>(<when>,<otherwise>)元素使用 -->
		<!-- 写法二,使用where -->
	<select id="findCustomerByNameAndJobs2"
		parameterType="com.lin.po.Customer" resultType="com.lin.po.Customer">
		select * from t_customer
		<where>
			<if test="username!=null and username!=''">
				and username like
				concat('%',#{username},'%')
			</if>
			<if test="jobs!=null and jobs !=''">
				and jobs=#{jobs}
			</if>
		</where>
	</select>

trim元素

	<!-- <choose>(<when>,<otherwise>)元素使用 -->
	<select id="findCustomerByNameOrJobs2"
		parameterType="com.lin.po.Customer" resultType="com.lin.po.Customer">
		select * from t_customer
		<trim prefix="where" prefixOverrides="and">
			<choose>
				<when test="username!=null and username!=''">
					and username like
					concat('%',#{username},'%')
				</when>
				<when test="jobs!=null and jobs !=''">
					and jobs=#{jobs}
				</when>
				<otherwise>
					and phone is not null
				</otherwise>
			</choose>
		</trim>
	</select>

4.set元素

1.简介

代码演示

set元素的使用

	<!-- <set>元素 -->
	<update id="updateCustomer" parameterType="com.lin.po.Customer">
		update t_customer
		<set><!-- 会去掉多余的逗号 -->
			<if test="username!=null and username!=''">
				username=#{username},
			</if>
			<if test="jobs!=null and jobs !=''">
				jobs=#{jobs},

			</if>
			<if test="phone !=null and phone!=''">
				phone=#{phone},
			</if>
		</set>
		where id=#{id}
	</update>

单元测试

@Test
	public void updateCustomerTest2() {
		SqlSession sqlSession=	MybatisUtils.getSqlSession();
		Customer customer = new Customer();
		customer.setId(3);
		customer.setPhone("123456789");
		//执行SqlSession的更新方法，返回的是SQL受影响的行数
		int rows=	sqlSession.update("com.lin.mapper.CustomerMapper.updateCustomer", customer);
		if (rows>0) {
			System.out.println("修改了"+rows+"条数据！");
		} else {
			System.out.println("更新操作执行失败");
		}
		sqlSession.commit();
		sqlSession.close();
	}

5.foreach元素

foreach元素的使用

<!--foreach元素的使用 -->
	<select id="findCustomerByIds" parameterType="List"
		resultType="com.lin.po.Customer">
		select * from t_customer where id in
		<foreach item="id" index="index" collection="list" open="("
			separator="," close=")">
			#{id}
		</foreach>

	</select>

单元测试

//foreach元素
	//根据客户编号，批量查询客户信息
	@Test
	public void findCustomerByIdsTest() {
		SqlSession sqlSession=MybatisUtils.getSqlSession();
		List<Integer> ids=new ArrayList<Integer>();
		ids.add(1);
		ids.add(2);
	List<Customer> customers=	sqlSession.selectList("com.lin.mapper.CustomerMapper.findCustomerByIds", ids);
		for (Customer customer : customers) {
			System.out.println(customer);
		}
		
		sqlSession.close();
	}
	

注意事项

6.bind元素

代码演示

<!-- <bind>元素的使用：根据客户名模糊查询客户信息 -->
	<select id="findCustomerByNames2"
		parameterType="com.lin.po.Customer" resultType="com.lin.po.Customer">
		<!--_parameter.getUsername()也可以直接写成闯入的字段属性名，即username -->
		<bind name="pattern_username"
			value="'%'+_parameter.getUsername()+'%'" />
		select * from t_customer where username like #{pattern_username}
	</select>

单元测试

//bind元素
	@Test
	public void findCustomerByNameTests2() {
		SqlSession sqlSession=MybatisUtils.getSqlSession();
		//创建Customer对象，封装查询的条件
		Customer customer=new Customer();
		customer.setUsername("j");
	List<Customer> customers=sqlSession.selectList("com.lin.mapper.CustomerMapper.findCustomerByNames2", customer);
	for (Customer customer2 : customers) {
		System.out.println(customer2);
	}
	sqlSession.close();
	}

小结