Mybatis框架下的表连接三种结构及实现

一.OneToOne表连接的建立

1.有两种连接方式

1)Customperson findPersonAndCardByPid(int pid); 建立一个中间类CustomPerson，进行连接.

2)Person findPersonAndCardByPid2(int pid);  将Card对象写入Person类中，此方法是常用方法。

2.配置PersonMapper.xml

<mapper namespace="com.dao.PersonMapper">
<resultMap type="com.po.Person" id="personMap">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<!-- person里关联的card对象 关系是一对一的关系 -->
<association property="card" javaType="com.po.Card">
<result property="no" column="no"/>
</association>
</resultMap>

<select id="findPersonAndCardByPid" parameterType="int"
resultType="customPerson">
SELECT person.id,name,age,no FROM person INNER JOIN
card ON person.id =card.pid WHERE person.id=#{id};
</select>

<select id="findPersonAndCardByPid2" parameterType="int"
resultMap="personMap">
SELECT person.*,card.no FROM person INNER JOIN card ON person.id =card.pid WHERE person.id=#{id};
</select>
</mapper>

3.测试类进行测试

@Test
public void testFind2() {
SqlSession session = sf.openSession();
PersonMapper mapper = session.getMapper(PersonMapper.class);
Person p = mapper.findPersonAndCardByPid2(9);
System.out.println(p.getCard());
session.close();
}

二.OneToMany表连接的建立

1.建立一对多的po类持久化对象 District 类与Street 类

public class District implements Serializable

        private Integer id;
private String name;
List<Street> streets = new ArrayList<Street>();

public class Street implements Serializable

        private Integer id;
private String name;
private Integer did;

2.以查询的方法为例，编写DistrictMapper.java并配置DistrictMapper.xml

List<District> findAll();    接口中查询方法

xml配置与查询的sql

<mapper namespace="com.dao.DistrictMapper">
<resultMap type="com.po.District" id="districtMap">
<id property="id" column="id"/>
<result property="name" column="name"/>
<collection property="streets" javaType="com.po.Street">
<id property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="did" column="did"/>
</collection>
</resultMap>
<select id="findAll" resultMap="districtMap">
SELECT d.*,s.id sid,s.name sname,s.did FROM district d INNER JOIN street s  ON d.id=s.did
</select>
</mapper>

三.ManyToMany表的建立

1.建立多对多的持久化对象，Project类和Employee类

public class Project implements Serializable 
private int id;
private String name;

public class Employee implements Serializable 
private int id;
private String name;
private List<Project> projects = new ArrayList<Project>();

2.实现接口和配置文件

List<Employee> findAll();

       <resultMap type="com.po.Employee" id="employeeMap">
<id property="id" column="id"/>
<result property="name" column="name"/>
<collection property="projects" javaType="com.po.Project">
<id property="id" column="pid"/>
<result property="name" column="pname"/>
</collection>
         </resultMap>
多对多由一个中间表emp_prj连接两张表，实现多对多
<select id="findAll" resultMap="employeeMap">
SELECT e.*,p.id pid,p.name pname FROM employee e INNER JOIN emp_prj ep ON e.id=ep.emp_id INNER JOIN project p ON p.id=ep.prj_id;
</select>