实例讲解Java的MyBatis框架对MySQL中数据的关联查询

/* 
 * 通过blogId获取BlogInfo对象 
 */ 
public static BlogInfo ordinaryQueryOnTest(String blogId) 
{ 
 BigDecimal id = new BigDecimal(blogId); 
 SqlSession session = sqlSessionFactory.openSession(); 
 BlogInfo blogInfo = new BlogInfo(); 
 //1.根据blogid 查询Blog对象，将值设置到blogInfo中 
 Blog blog = (Blog)session.selectOne("com.foo.bean.BlogMapper.selectByPrimaryKey",id); 
 blogInfo.setBlogId(blog.getBlogId()); 
 blogInfo.setTitle(blog.getTitle()); 
 //2.根据Blog中的authorId，进入数据库查询Author信息，将结果设置到blogInfo对象中 
 Author author = (Author)session.selectOne("com.foo.bean.AuthorMapper.selectByPrimaryKey",blog.getAuthorId()); 
 blogInfo.setAuthor(author); 
 //3.查询posts对象，设置进blogInfo中 
 List posts = session.selectList("com.foo.bean.PostMapper.selectByBlogId",blog.getBlogId()); 
 blogInfo.setPosts(posts); 
 //以JSON字符串的形式将对象打印出来 
 JSONObject object = new JSONObject(blogInfo); 
 System.out.println(object.toString()); 
 return blogInfo; 
} 

<resultMap type="com.foo.bean.BlogInfo" id="BlogInfo"> 
 <id column="blog_id" property="blogId" /> 
 <result column="title" property="title" /> 
 <association property="author" column="blog_author_id" 
  javaType="com.foo.bean.Author" select="com.foo.bean.AuthorMapper.selectByPrimaryKey"> 
 </association> 
 <collection property="posts" column="blog_id" ofType="com.foo.bean.Post" 
  select="com.foo.bean.PostMapper.selectByBlogId"> 
 </collection> 
</resultMap> 
<select id="queryBlogInfoById" resultMap="BlogInfo" parameterType="java.math.BigDecimal"> 
 SELECT 
 B.BLOG_ID, 
 B.TITLE, 
 B.AUTHOR_ID AS BLOG_AUTHOR_ID 
 FROM LOULUAN.BLOG B 
 where B.BLOG_ID = #{blogId,jdbcType=DECIMAL} 
</select> 

/* 
 * 通过blogId获取BlogInfo对象 
 */ 
public static BlogInfo nestedQueryOnTest(String blogId) 
{ 
 BigDecimal id = new BigDecimal(blogId); 
 SqlSession session = sqlSessionFactory.openSession(); 
 BlogInfo blogInfo = new BlogInfo(); 
 blogInfo = (BlogInfo)session.selectOne("com.foo.bean.BlogMapper.queryBlogInfoById",id); 
 JSONObject object = new JSONObject(blogInfo); 
 System.out.println(object.toString()); 
 return blogInfo; 
} 

<resultMap type="com.foo.bean.BlogInfo" id="BlogInfo"> 
 <id column="blog_id" property="blogId"/> 
 <result column="title" property="title"/> 
 <association property="author" column="blog_author_id" javaType="com.foo.bean.Author"> 
  <id column="author_id" property="authorId"/> 
  <result column="user_name" property="userName"/> 
  <result column="password" property="password"/> 
  <result column="email" property="email"/> 
  <result column="biography" property="biography"/> 
 </association> 
 <collection property="posts" column="blog_post_id" ofType="com.foo.bean.Post"> 
  <id column="post_id" property="postId"/> 
  <result column="blog_id" property="blogId"/> 
  <result column="create_time" property="createTime"/> 
  <result column="subject" property="subject"/> 
  <result column="body" property="body"/> 
  <result column="draft" property="draft"/> 
 </collection> 
</resultMap> 

<select id="queryAllBlogInfo" resultMap="BlogInfo"> 
 SELECT 
  B.BLOG_ID, 
  B.TITLE, 
  B.AUTHOR_ID AS BLOG_AUTHOR_ID, 
  A.AUTHOR_ID, 
  A.USER_NAME, 
  A.PASSWORD, 
  A.EMAIL, 
  A.BIOGRAPHY, 
  P.POST_ID, 
  P.BLOG_ID AS BLOG_POST_ID , 
 P.CREATE_TIME, 
  P.SUBJECT, 
  P.BODY, 
  P.DRAFT 
FROM BLOG B 
LEFT OUTER JOIN AUTHOR A 
 ON B.AUTHOR_ID = A.AUTHOR_ID 
LEFT OUTER JOIN POST P 
 ON P.BLOG_ID = B.BLOG_ID 
</select> 

/* 
 * 获取所有Blog的所有信息 
 */ 
public static BlogInfo nestedResultOnTest() 
{ 
 SqlSession session = sqlSessionFactory.openSession(); 
 BlogInfo blogInfo = new BlogInfo(); 
 blogInfo = (BlogInfo)session.selectOne("com.foo.bean.BlogMapper.queryAllBlogInfo"); 
 JSONObject object = new JSONObject(blogInfo); 
 System.out.println(object.toString()); 
 return blogInfo; 
} 

public class Module { 
 private int id; 
 private String key; 
 private String name; 
 private Module parentModule; 
 private List<Module> childrenModules; 
 private String url; 
 private int sort; 
 private String show; 
 private String del; 
 public int getId() { 
  return id; 
 } 
 public void setId(int id) { 
  this.id = id; 
 } 
 public String getKey() { 
  return key; 
 } 
 public void setKey(String key) { 
  this.key = key; 
 } 
 public String getName() { 
  return name; 
 } 
 public void setName(String name) { 
  this.name = name; 
 } 
 public Module getParentModule() { 
  return parentModule; 
 } 
 public void setParentModule(Module parentModule) { 
  this.parentModule = parentModule; 
 } 
 public String getUrl() { 
  return url; 
 } 
 public void setUrl(String url) { 
  this.url = url; 
 } 
 public int getSort() { 
  return sort; 
 } 
 public void setSort(int sort) { 
  this.sort = sort; 
 } 
 public String getShow() { 
  return show; 
 } 
 public void setShow(String show) { 
  this.show = show; 
 } 
 public String getDel() { 
  return del; 
 } 
 public void setDel(String del) { 
  this.del = del; 
 } 
 public List<Module> getChildrenModules() { 
  return childrenModules; 
 } 
 public void setChildrenModules(List<Module> childrenModules) { 
  this.childrenModules = childrenModules; 
 } 
} 

<mapper namespace="com.sagaware.caraccess.mapper.ModuleMapper"> 
 <resultMap type="Module" id="moduleResultMap"> 
  <id property="id" column="module_id"/> 
  <result property="key" column="module_key"/> 
  <result property="name" column="module_name"/> 
  <result property="url" column="module_url"/> 
  <result property="sort" column="module_sort"/> 
  <result property="show" column="module_show"/> 
  <result property="del" column="module_del"/> 
  <!-- 查询父模块 --> 
  <association property="parentModule" column="module_parent_id" select="getModulesById" /> 
  <!-- 查询子模块 --> 
  <collection property="childrenModules" column="module_id" select="getChildrenModues" /> 
 </resultMap> 
 <select id="getModules" parameterType="String" resultMap="moduleResultMap"> 
  select * from tb_module where module_id=2 
 </select> 
 <select id="getModulesById" parameterType="int" resultMap="moduleResultMap"> 
  select * from tb_module where module_id = #{module_id} 
 </select> 
 <select id="getChildrenModues" parameterType="int" resultMap="moduleResultMap"> 
  select * from tb_module where module_parent_id = #{module_id} 
 </select> 
</mapper>