mybatis多对一、一对多、多对多、自关联查询

多对一

指的是在多的一端维护关联关系.
用户、 组表

创建对象

用户是多的一段
Group是一的一端

创建接口

public interface UserDao {
     User getUserById(int uid);
}

sql 映射文件

<?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="com.iotek.dao.UserDao">
   
   <resultMap type="User" id="userMapper">
      <id column="uid" property="uid"/>
      <result column="uname" property="uname"/>
      <!--association关联查询-->
      <association property="group" javaType="Group">
        <id column="gid" property="gid"/>
        <result column="gname" property="gname"/>
      </association>
   </resultMap>
   
   <select id="getUserById" parameterType="int" resultMap="userMapper">
     SELECT gid,gname,uid,uname FROM tb_group g,tb_users u WHERE 
     g.gid=u.groupid AND u.uid=#{uid}
   </select>  
</mapper>

测试

	@Test
	public void getUserById() throws IOException {
		SqlSession sqlSession = Utils.getSqlSessionFactory().openSession();
	    UserDao userDao=sqlSession.getMapper(UserDao.class);
	    User user=userDao.getUserById(1);
	    System.out.println(user.getGroup().getGname());
	
	}

方法2：使用单表查询(分布查询)的方式实现多对一

<?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="com.iotek.dao.UserDao">
   <!--第二次查询-->
    <select id="aa" parameterType="int" resultType="Group">
         SELECT gid,gname FROM tb_group WHERE gid=#{groupid}
   </select>
   
   <resultMap type="User" id="userMapper">
      <id column="uid" property="uid"/>
      <result column="uname" property="uname"/>
      <association property="group" javaType="Group"
        select="aa" column="groupid">       
      </association>
   </resultMap>
   <!--第一次查询-->
   <select id="getUserById" parameterType="int" resultMap="userMapper">    
     SELECT uid ,uname, groupid FROM tb_users WHERE uid=#{uid}
   </select>  
</mapper>

一对多
创建po对象
1的一端（在1的一端维护关联关系）

多的一端

创建接口

public interface GroupDao {
    Group getGroupById(int gid);
}

sql语句映射文件

<?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="com.iotek.dao.GroupDao">
   <resultMap type="Group" id="aa">
      <id column="gid" property="gid"/>
      <result column="gname" property="gname"/>
      <collection property="users" ofType="User">
          <id column="uid" property="uid"/>
          <result column="uname" property="uname"/>
      </collection>
   </resultMap>
   
   <select id="getGroupById" parameterType="int" resultMap="aa">    
    SELECT gid,gname,uid,uname FROM tb_group g,tb_users u WHERE 
     g.gid=u.groupid AND g.gid=#{gid}
   </select>
  
</mapper>

测试

	@Test
	public void getUserById() throws IOException {
		SqlSession sqlSession = Utils.getSqlSessionFactory().openSession();
	    GroupDao gd=sqlSession.getMapper(GroupDao.class);
	    Group group= gd.getGroupById(1);
	    System.out.println(group);	
	}

方法2：使用单表查询(分布查询)的方式实现一对多

<?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="com.iotek.dao.GroupDao">
 <!--第二次查询-->
 <select id="aa" parameterType="int" resultType="User">    
     select uid,uname from tb_users where groupid=#{gid}
   </select>
   <resultMap type="Group" id="groupMapper">
      <id column="gid" property="gid"/>
      <result column="gname" property="gname"/>
      <collection property="users" ofType="User" 
        select="aa" column="gid">         
      </collection>
   </resultMap>
   <!--第一次查询-->
   <select id="getGroupById" parameterType="int" resultMap="groupMapper">    
     select gid,gname from tb_group where gid=#{gid}
   </select>
  
</mapper>

多对多
创建po对象

创建接口

public interface StudentDao {
    Student getStudentById(int sid);
}

sql语句映射文件

<?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="com.iotek.dao.StudentDao">
  
  <resultMap type="Student" id="studentMapper">
     <id column="sid" property="sid"/>
     <result column="sname" property="sname"/>
     <collection property="courses" ofType="Course">
       <id column="cid" property="cid"/>
       <result column="cname" property="cname"/>
     </collection>   
  </resultMap>  
  
  <select id="getStudentById" parameterType="int" resultMap="studentMapper">     
   SELECT sid,sname,cid,cname FROM tb_stu s,tb_middle m,tb_course c
   WHERE s.sid=m.studentid AND c.cid=m.courseid AND s.sid=#{sid}
  </select>
  
</mapper>

测试

	@Test
	public void getStudentById() throws IOException {
		SqlSession sqlSession = Utils.getSqlSessionFactory().openSession();
	    StudentDao stuDao=sqlSession.getMapper(StudentDao.class);
	    Student student=stuDao.getStudentById(1);
	    System.out.println(student);
	    Iterator<Course> iter= student.getCourses().iterator();
	     while(iter.hasNext()){
	    	 Course c=iter.next();
	    	 System.out.println(c.getCname());	    	 
	     }	
	}

自关联
po对象

接口

public interface NewsLabelDao {
     /*根据父节点把有子节点查询出来*/
	List<NewsLabel> getChildrenByPid(int pid);
}

sql语句映射文件

<?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="com.iotek.dao.NewsLabelDao">  
   <select id="aaa" resultMap="newslabelMapper1">      
       SELECT id,NAME FROM tb_newslabel WHERE pid=#{id}
   </select>   
   <resultMap type="NewsLabel" id="newslabelMapper1">
       <id column="id" property="id"/>
       <result column="name" property="name"/>
       <collection property="children" ofType="NewsLabel"
        select="aaa" column="id">       
       </collection>       
   </resultMap>
   <select id="getChildrenByPid" resultMap="newslabelMapper1">      
       SELECT id,NAME FROM tb_newslabel WHERE pid=#{pid}
   </select>  
</mapper>

测试

	@Test
	public void getStudentById() throws IOException {
		SqlSession sqlSession = Utils.getSqlSessionFactory().openSession();
		NewsLabelDao newsDao=sqlSession.getMapper(NewsLabelDao.class);
		List<NewsLabel> children=newsDao.getChildrenByPid(2);
		System.out.println(children);   
	
	}

喜欢 (0)赏

[搞代码]

分享 (0)