Mybatis框架学习（二）Mapper配置基础解读+动态SQL

Mapper配置
1.SQL块

<sql id="columnSQL">
		employee_id,first_name,last_name,email,
			phone_number,hire_date,job_id,salary,commission_pct,
			manager_id,department_id
	</sql>

目的是简化SQL语句，下面为简化前后对比
简化前：

<select id = "select" resultType = "Employee">
		select employee_id,first_name,last_name,email,
			phone_number,hire_date,job_id,salary,commission_pct,
			manager_id,department_id 
		from employee
	</select>

简化后：

<select id = "select" resultType = "Employee">
		select <include refid="columnSQL"/> 
		from employee
	</select>

2.insert元素

<insert id = "insert" parameterType = "Employee"
 useGeneratedKeys="true" keyProperty="employee_id">

在执行insert语句时，获得自增长得主键值，将自增长的主键值存入到Employee对象的employee_id的属性中,Keyproperty=“对应的主键对象”

3.动态SQL

set元素：代表set关键字，并能自动去掉最后一个逗号
if元素：判断语句test属性中直接写#{}中的内容，test中写的是兑现的属性名或key名。

<update id = "update" parameterType = "Employee">
		update employee
		<!-- 代表set关键字，并能自动去掉最后一个逗号 -->
		<set>
			<!-- test中直接写${}中的内容 -->
			<if test="first_name!=null and first_name !=''">
				first_name=#{first_name},
			</if>
			<if test="last_name!=null and last_name !=''">
				last_name=#{last_name},
			</if>
			<if test="email!=null and email !=''">
				email=#{email},
			</if>
			<if test="phone_number!=null and phone_number !=''">
				phone_number=#{phone_number},
			</if>
			<if test="hire_date!=null">
				hire_date=#{hire_date},
			</if>
			<if test="job_id!=null and job_id !=''">
				job_id=#{job_id},
			</if>
			<if test="salary!=0">
				salary=#{salary},
			</if>
			<if test="commission_pct!=null and commission_pct !=''">
				commission_pct=#{commission_pct},
			</if>
			<if test="manager_id!=null and manager_id !=''">
				manager_id=#{manager_id},
			</if>
			<if test="dept!=null">
				department_id=#{department_id},
			</if>
		</set>
		where employee_id = #{employee_id}
	</update>

where元素：相当于where 1=1

<select id = "select" resultType = "Employee" parameterType="java.util.Map">
		select <include refid="columnSQL"/>
		from employee
		<!-- 相当于where1=1 -->
		<where>
			<if test="first_name!=null and first_name!=''">
				and first_name = #{first_name}
			</if>
			<if test="job_id != null and job_id !=''">
				and job_id = #{job_id}
			</if>
			<if test="salary1!= null">
				and salary &gt;= #{salary1}
			</if>
			<if test ="salary2 != null">
				and salary &lt;= #{salary2}
			</if>
		</where>
	</select>

模糊查询：利用元素
EmployeeMapper.XML中的select方法

<select id = "select" resultType = "Employee" parameterType="java.util.Map">
		select <include refid="columnSQL"/>
		from employee
		<!-- 相当于where1=1 -->
		<where>
		
		<!-- 匹配查询 -->
			<if test="first_name!=null and first_name!=''">
				<bind name="firstName" value="'%'+first_name+'%'"></bind>
				and first_name like #{firstName}
			</if>
			<if test="job_id != null and job_id !=''">
				and job_id = #{job_id}
			</if>
			<if test="salary1!= null">
				and salary &gt;= #{salary1}
			</if>
			<if test ="salary2 != null">
				and salary &lt;= #{salary2}
			</if>
		</where>
	</select>

test方法：搜索数据库Employee表中姓名带‘明’字的姓名

Map map = new HashMap();
			map.put("first_name", "tom");
			map.put("first_name", "%明%");
			List<Employee> list = sqlSession.selectList("Employee.select",map);
			for (Employee e : list) {
				System.out.println(e.getFirst_name());
			}

foreach元素：迭代数组或集合
collection属性：array或list
open属性：循环开始先拼的字符串
separator:元素分隔符
close:循环结束拼的字符串

<!-- 批量删除 -->
	<delete id ="deleteBatch" parameterType="int[]">
		delete from employee where employee_id in
		<foreach coloection="array" open="(" clos=")" separator=","item="item" >
			#{item}
		</foreach>
	</delete>