MyBatis使用XML配置SQL映射器

    MyBatis提供了多种元素来配置不同类型的语句，比如Select、Insert、Update、Delete这样的，之前在写学生信息简单的增删改查时就已经使用过这些语句。而将这些映射都存放在mapper.xml当中，然后通过映射器Mapper接口来调用所对应的语句。而在Mapper当中不只这几个元素存在，还有其他的。

    CRUD：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="xin.work.StudentMapper">
	<insert id="insertStudent" parameterType="xin.work.Student">
		insert into t_student(id,name,age,adress) values(#{id},#{name},#{age},#{adress})
	</insert>
	<delete id="deleteStudent" parameterType="int">
		delete from t_student where id =#{id}
	</delete>
	<update id="updateStudent" parameterType="xin.work.Student">
		update t_student set name = #{name},age = #{age} where id = #{id}
	</update>
	<select id="selectStudent" parameterType="int" resultType="xin.work.Student">
		select id,name,age,adress from t_student where id=#{id}
	</select>
</mapper>

    显然上面的查询语句Select永远只会返回一个xin.work.Student类型的对象，所以在 涉及到多个符合条件的结果存在的时候，上面的配映射就不靠谱了。所以可以利用集合来实现多条数据的读取。

package xin.Logs;

import java.util.List;

public interface employeeMapper {
	public List<Employee> SelectAll();
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="xin.Logs.employeeMapper">
	<select id="SelectAll" resultType="xin.Logs.Employee">
		select id,last_name lastName,first_name firstName,salary,title
		from s_emp
	</select>
</mapper>

package xin.Logs;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import xin.publicUse.MyBatisUtils;

public class EmployTest {
	@Test
	public void SelectAll() {
		SqlSession tk = MyBatisUtils.openSession();
		employeeMapper op = tk.getMapper(employeeMapper.class);
		List<Employee> tki = op.SelectAll();
		for (Employee e : tki) {
			System.out.println(e);
		}
		tk.commit();
		tk.close();
	}
}

    注意上述的SELECT映射定义，我们为所有的映射语句中的last_name和first_name起了别名。我们可以使用ResultMaps，来避免上述的到处重复别名。我们稍后会继续讨论。
    除了java.util.List，你也可以使用其他类型的集合类，如Set,Map，以及（SortedSet）。MyBatis 根据集合的类型，会采用适当的集合实现，如下所示：
    对于List，Collection，Iterable类型，MyBatis将返回java.util.ArrayList 
    对于Map类型，MyBatis 将返回java.util.HashMap  
    对于Set类型，MyBatis 将返回java.util.HashSet 
    对于SortedSet类型，MyBatis将返回java.util.TreeSet 

结果集映射 ResultMaps

    ResultMaps被用来将SELECT语句的结果集映射到java对象的属性中。ResultMaps特性非常强大，你可以使用它将简单的SELECT语句映射到复杂的一对一、一对多关系的SELECT语句上。所以说刚才上面的xml配置文件我可以改为：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="xin.Logs.employeeMapper">
	<resultMap type="xin.Logs.Employee" id="EmployeeResult">
		<id property="id" column="id" />
		<result property="firstName" column="first_name" />
		<result property="lastName" column="last_name" />
		<result property="salary" column="salary" />
		<result property="title" column="title" />
	</resultMap>
	<select id="SelectAll" resultMap="EmployeeResult">
		select id,last_name,first_name,salary,title
		from s_emp
	</select>
</mapper>

    同时也要注意，resultMap在同一命名空间下的id肯定是唯一的，而且Type属性是完全限定类名或者是返回值的别名。<result>子元素被用来将一个resultset列映射到对象的一个属性当中。<id>元素和<result>元素功能相同，不过<id>被用来映射到唯一标识属性，用来区分和比较对象（一般和主键列相对应）。还有就是resultType和resultMap只能出现一个，不能 两个同时出现。

    此刻可以利用resultMap的特性来做更多的事情：当两张或者更多表出现在同一个select当中时，且其字段出现重复的情况下，就不能简单的像以上那样去做处理，还好resultMap给我们提供一个能够解决的办法：

    关联关系映射 设置一对一的映射：

    这里通过两张简单的表，设置一对一的级联

create table wife(
		 wid number constraint wife_wid_pk primary key,
		 wname varchar2(20),
		 age number constraint wife_age_ck check (age>20),
		 gender varchar2(4) constraint wife_gender_ck check (gender in('F','M')),
		 brithday date
	);
	create table hus(
		hid number constraint hus_hid_pk primary key,
		hname varchar2(20),
		age number constraint hus_age_ck check (age > 20),
		gender varchar2(4) constraint hus_gender_ck check (gender in('F','M')),
		brithday date,
		wife_id number constraint hus_wife_id_fk references wife(wid) constraint hus_wife_id_un unique
	);

     在插入数据之后设置映射文件：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="xin.oneTone.oneToneMapper">
	<resultMap type="xin.oneTone.Husband" id="HusbandResultWithWifeTwo">
		<id column="hid" property="id"/>
		<result column="hname" property="name"/>
		<result column="hage" property="age"/>
		<result column="hgender" property="gender"/>
		<result column="hbirthday" property="date"/>
		<association property="wife" resultMap="WifeResult"></association>
	</resultMap>
	<resultMap type="xin.oneTone.Wife" id="WifeResult">
		<id column="wid" property="id"/>
		<result column="wname" property="name"/>
		<result column="wage" property="age"/>
		<result column="wgender" property="gender"/>
		<result column="wbirthday" property="date"/>
		</resultMap>
	<select id="SelectWithWifeId" resultMap="HusbandResultWithWifeTwo">
		select hid,hname,h.age,h.gender,h.brithday,
				  wid,wname,w.age wage,w.gender wgender,w.brithday wbirthday
		from hus h join wife w
		on h.wife_id = w.wid
		where h.hid = #{id}
	</select>
</mapper>

    由此对应写出测试也是老套路，不再赘述。而也有嵌套select的写法：

<resultMap type="xin.oneTone.Husband" id="HusbandResultWithWifeThree">
			<id column="hid" property="id"/>
			<result column="hname" property="name"/>
			<association property="wife" column="wife_id" select="getWifeById"></association>
		</resultMap>
		<select id="SelectWithWifeId" resultMap="HusbandResultWithWifeThree">
			select hid id,hname name,age,gender,brithday,wife_id
			from hus
			where hid = #{id}
		</select>
		<select id="getWifeById" resultType="xin.oneTone.Wife">
			select wid id,wname name,age,gender,brithday
			from wife
			where wid = #{id}
		</select> 

    设置一对多的映射

    同样是写个测试表：

create table s_clz(
	id number primary key,
	name varchar2(20)
);
create table s_stu(
	id number primary key,
	name varchar2(20),
	c_id number references s_clz(id)
);

    插入测试数据之后尝试：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="xin.oneTmany.oneTmanyMapper">
	<!-- 使用collection -->
	<!--  
	<resultMap type="xin.oneTmany.Group" id="clzAndStuResult">
		<id column="cid" property="id"/>
		<result column="cname" property="name"/>
		<collection property="stu" ofType="xin.oneTmany.Student">
			<result column="sid" property="id"/>
			<result column="sname" property="name"/>
		</collection>
		</resultMap>
	<select id="SelectAll" resultMap="clzAndStuResult">
			select c.id cid,c.name cname,s.id sid,s.name sname
			from s_clz c,s_stu s
			where c.id = s.c_id and c.id = #{id}
	</select>
	-->
	<!-- 使用内嵌的ResultMap -->
	<!--  
	<resultMap type="xin.oneTmany.Group" id="clzAndStuResult">
		<id column="cid" property="id"/>
		<result column="cname" property="name"/>
		<collection property="stu" resultMap="stuResult"></collection>
	</resultMap>
	<resultMap type="xin.oneTmany.Student" id="stuResult">
		<result column="sid" property="id"/>
		<result column="sname" property="name"/>
	</resultMap>
	<select id="SelectAll" resultMap="clzAndStuResult">
			select c.id cid,c.name cname,s.id sid,s.name sname
			from s_clz c,s_stu s
			where c.id = s.c_id and c.id = #{id}
	</select>
	-->
	<!-- 使用嵌套实现一对多 -->
	<resultMap type="xin.oneTmany.Group" id="clzAndStuResult">
		<id column="cid" property="id"/>
		<result column="cname" property="name"/>
		<collection property="stu" column="cid" select="SelectAll"></collection>
	</resultMap>
	<resultMap type="xin.oneTmany.Student" id="stuResult">
		<result column="sid" property="id"/>
		<result column="sname" property="name"/>
	</resultMap>
	<select id="selectClzById" resultMap="clzAndStuResult">
		select id cid,name cname
		from s_clz where id = #{id}
	</select>
	<select id="SelectAll" resultMap="stuResult">
		select id sid,name sname
		from s_stu 
		where c_id = #{id}
	</select>
</mapper>

    其中的任意一种都可以查询得到，但是注意下最后一种的返回不太和前两种一样。

    多对多的关联

    依旧是先创建三个表：

create table m_user(
	id number primary key,
	name varchar2(20)
);
create table m_role(
	id number primary key,
	name varchar2(20)
);
create table m_user_role(
	u_id number references m_user(id),
	r_id number references m_role(id),
	primary key(u_id,r_id)
);

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="xin.manyTmany.manyTmanyMapper">
	<resultMap type="xin.manyTmany.User" id="userResult">
		<id column="u_id" property="id"/>
		<result column="uname" property="name"/>
		<collection property="rule" ofType="xin.manyTmany.Ruler">
			<id column="rid" property="id"/>
			<result column="rname" property="name"/>
		</collection>
	</resultMap>
	<select id="findUserAndRoleByUid" resultMap="userResult">
		select u.id u_id,u.name uname,r.id rid,r.name rname
		from m_user u,m_role r,m_user_role mr
		where u.id = mr.u_id and r.id = mr.r_id and u.id = #{id}
	</select>
</mapper>

    在理论层面多对多算是一对多的组合。

    SQL映射器配置暂且到此，更多详情请戳官方文档-=-