Mybatis关联查询
关联映射一对多
创建CountryMapper.java接口如下:
//一对多 SmbmsRoleEntity roleEntity(Integer id);
创建CountryMapper.xml文件如下
<!--一对多--> <!--由于是关联查询 返回的是多张表中的结果集,必须定义resultMap映射--> <resultMap id="countryMap" type="SmbmsRoleEntity"> <id property="rid" column="rid"></id> <result property="roleName" column="roleName"></result> <!-- 此处使用的是collection节点,由于在Country类中插入的是List集合 ofType:为集合中的泛型--> <collection property="userEntityList" ofType="SmbmsUserEntity" select="roleEntitys" column="rid"> <!--在collection中声明Provincial中的属性与表中列的映射--> <id column="id" property="id"></id> <result column="userName" property="userName"></result> </collection> </resultMap> <select id="roleEntity" resultMap="countryMap"> SELECT * from smbms_role where rid=#{id} </select> <select id="roleEntitys" resultType="SmbmsUserEntity"> SELECT * from smbms_user where userRole=#{rid} </select>
测试程序:
//一对多 SmbmsRoleEntity smbmsRoleEntity = sqlSession.getMapper(iuserdao.class).roleEntity(3); for (SmbmsUserEntity user:smbmsRoleEntity.getUserEntityList()){ System.out.println("用户:"+user.getUserName()); }
自连接一对多
接口:
List<Provincial> getTwo();
编写xml文件:
<mapper namespace="com.pr.dao.CountryDao"> <resultMap id="ProvincialMap" type="com.pr.entity.Provincial"> <id column="pid" property="pid"></id> <result column="pname" property="pname"></result> <!--当关联对象为普通对象时,使用association标签 javaType:为关联对象的类型--> <association property="country" javaType="com.pr.entity.Country"> <id column="cid" property="cid"></id> <result column="cname" property="cname"></result> </association> </resultMap> <select id="getTwo" resultMap="ProvincialMap"> select * from provincial p inner join country c on p.cid=c.cid; </select> </mapper>
测试:
public class Demo4 { SqlSession session=null; CategoryMapper mapper=null; @Before public void before(){ session= SqlSessionFactoryUtils.getSession(); mapper=session.getMapper(CategoryMapper.class); } @Test public void test01(){ Category cate = mapper.getOneById(548); //重写Category类中的toString方法 直接打印 System.out.println(cate); } @After public void after(){ session.commit();//事务提交 session.close();//释放session } }
关联映射多对一
创建接口:
//多对一 List<SmbmsUserEntity> userentity();
创建CountryMapper.xml文件如下:
<!--多对一--> <resultMap id="countryMap" type="SmbmsUserEntity"> <id property="id" column="id"></id> <result property="userName" column="userName"></result> <association property="roleEntity" javaType="SmbmsRoleEntity" select="roleEntitys" column="userRole"> <id column="rid" property="rid"></id> <result column="roleName" property="roleName"></result> </association> </resultMap> <select id="userentity" resultMap="countryMap"> SELECT * from smbms_user </select> <select id="roleEntitys" resultType="SmbmsRoleEntity"> SELECT * from smbms_role where id=#{userRole} </select>
测试程序:
//多对一 List<SmbmsUserEntity> userentity = sqlSession.getMapper(iuserdao.class).userentity(); for (SmbmsUserEntity user:userentity){ System.out.println("用户:"+user.getUserName()+"角色:"+user.getRoleEntity().getRoleName()); }
关联映射多对多
接口:
<!--多对多-->
List<Teacher> getOne;
编写.xml文件:
<mapper namespace="com.pr.dao.TeacherDao"> <resultMap id="teacherMap" type="com.pr.entity.Teacher"> <id column="teaid" property="teaid"></id> <result column="teaname" property="teaname"></result> <!--为students集合设置关联属性--> <collection property="students" ofType="com.pr.entity.Student"> <id column="stuid" property="stuid"></id> <result column="stuname" property="stuname"></result> </collection> </resultMap> <select id="getOne" resultMap="teacherMap"> SELECT s.*,t.* FROM teacher t,student s,stutea ts WHERE t.`teaid`=ts.`teaid` AND s.`stuid`=ts.`stuid` </select> </mapper>
测试程序:
List<Teacher> one mapper02. getOne(); for(Teacher te:one){ System. out. print1n(te. getStuname()) for(Student stu:te.getStudents()){ System. out. print1n(stu. getStuname()) } }