`

mybatis复杂查询例子

 
阅读更多

最近用到了mybatis框架，对于多表联合查询将结果集转化为复杂java对象。

现在写下这个例子，基本包括了多对多，一对多，多对一的复杂情况。

 

  • 表结构和javabean

5张表：博客表blog，作者表author，文章表post，标签表tag，文章标签关联表post_tag。

每个博客对应一个作者，和多个文章。每个文章对应一个作者。文章和标签是多对多的关系。

建表sql语句

CREATE TABLE `blog` (
  `blog_id` int(11) NOT NULL AUTO_INCREMENT,
  `blog_title` varchar(100) DEFAULT NULL,
  `blog_author_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`blog_id`)
)
CREATE TABLE `author` (
  `author_id` int(11) NOT NULL AUTO_INCREMENT,
  `author_username` varchar(100) DEFAULT NULL,
  `author_password` varchar(100) DEFAULT NULL,
  `author_email` varchar(100) DEFAULT NULL,
  `author_bio` varchar(100) DEFAULT NULL,
  `author_favourite_section` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`author_id`)
)
CREATE TABLE `post` (
  `post_id` int(11) NOT NULL AUTO_INCREMENT,
  `post_subject` varchar(100) DEFAULT NULL,
  `post_body` varchar(100) DEFAULT NULL,
  `blog_id` int(11) DEFAULT NULL,
  `post_author_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`post_id`)
)
CREATE TABLE `tag` (
  `tag_id` int(11) NOT NULL AUTO_INCREMENT,
  `tag_name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`tag_id`)
)
CREATE TABLE `post_tag` (
  `post_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  PRIMARY KEY (`post_id`,`tag_id`)
)

 实体类，省略了所有get和set方法

public class Blog {
	private int id;
	private String title;
	private Author author;
	private ArrayList<Post> posts;

        public Blog(Integer id){
		this.id=id;
	}
}

 

public class Author {
	private int id;
	private String username;
	private String password;
	private String email;
	private String bio;
	private String favouriteSection;
}

 

public class Post {
	private int id;
	private String subject;
	private String body;
	private Author author;
	private ArrayList<Tag> tags;
}

 

public class Tag {
	private int id;
	private String name;
}

 

  • mapper接口和配置文件

mapper接口定义

public interface BlogMapper {
	public Blog findById(int id);
}

BlogMapper.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="org.myBatis.mapper.BlogMapper">
	<select id="findById" parameterType="int" resultMap="detailedBlogResultMap">
	SELECT
		b.blog_id,
		b.blog_title,
		a.author_id,
		a.author_username,
		a.author_password,
		a.author_email,
		a.author_bio,
		a.author_favourite_section,
		p.post_id,
		p.post_subject,
		p.post_body,
		t.tag_id,
		t.tag_name
	FROM
		blog b
		LEFT JOIN author a ON b.blog_author_id = a.author_id
		LEFT JOIN post p ON b.blog_id = p.blog_id
		LEFT JOIN post_tag pt ON pt.post_id=p.post_id
		LEFT JOIN tag t ON pt.tag_id=t.tag_id
	WHERE b.blog_id = #{id}
	</select>
	<resultMap id="detailedBlogResultMap" type="Blog">
		<constructor>
			<idArg column="blog_id" javaType="int" />
		</constructor>
		<result property="title" column="blog_title" />
		<association property="author" column="blog_author_id"
			javaType="Author">
			<id property="id" column="author_id" />
			<result property="username" column="author_username" />
			<result property="password" column="author_password" />
			<result property="email" column="author_email" />
			<result property="bio" column="author_bio" />
			<result property="favouriteSection" column="author_favourite_section" />
		</association>
		<collection property="posts" ofType="Post">
			<id property="id" column="post_id" />
			<result property="subject" column="post_subject" />
			<result property="body" column="post_body" />
			<association property="author" column="post_author_id"
				javaType="Author">
				<id property="id" column="author_id" />
				<result property="username" column="author_username" />
				<result property="password" column="author_password" />
				<result property="email" column="author_email" />
				<result property="bio" column="author_bio" />
				<result property="favouriteSection" column="author_favourite_section" />
			</association>
			<collection property="tags" column="post_id" ofType="Tag">
				<id property="id" column="tag_id" />
				<id property="name" column="tag_name" />
			</collection>
		</collection>
	</resultMap>
</mapper>

 根据id返回唯一的blog

id为detailedBlogResultMap的resultMap将sql语句返回的结果集转换为blog对象。

constructor标签：定义了Blog类的构造方法

result标签：根据返回的结果集列对应blog对象的属性

association标签：一个blog对应一个author，定义了author的映射关系

collection标签：一个blog有多个post，定义了post的映射关系，该标签下面可以继续嵌套association和collection

 

  • 测试类MyBatisTest
public class MyBatisTest {
	public static void main(String[] args) {
		SqlSessionFactory sessionFactory = null;  
        String resource = "configuration.xml";  
        try {  
            sessionFactory = new SqlSessionFactoryBuilder().build(Resources  
                    .getResourceAsReader(resource));  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
        
        SqlSession session = sessionFactory.openSession();  
        BlogMapper mapper = session.getMapper(BlogMapper.class);  
        Blog blog = mapper.findById(1); 
        session.close();
	}
}

 

  • BlogMapper.xml结构简化

可以将author、post、tag等对象单独定义成resultMap

<?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="org.myBatis.mapper.BlogMapper">
	<select id="findById" parameterType="int" resultMap="detailedBlogResultMap">
		SELECT
		b.blog_id,
		b.blog_title,
		a.author_id,
		a.author_username,
		a.author_password,
		a.author_email,
		a.author_bio,
		a.author_favourite_section,
		p.post_id,
		p.post_subject,
		p.post_body,
		t.tag_id,
		t.tag_name
		FROM
		blog b
		LEFT JOIN author a ON b.blog_author_id =
		a.author_id
		LEFT JOIN
		post p ON b.blog_id = p.blog_id
		LEFT JOIN post_tag
		pt ON
		pt.post_id=p.post_id
		LEFT JOIN tag t ON pt.tag_id=t.tag_id
		WHERE
		b.blog_id = #{id}
	</select>
	<resultMap id="authorResultMap" type="Author">
		<id property="id" column="author_id" />
		<result property="username" column="author_username" />
		<result property="password" column="author_password" />
		<result property="email" column="author_email" />
		<result property="bio" column="author_bio" />
		<result property="favouriteSection" column="author_favourite_section" />
	</resultMap>
	<resultMap id="tagResultMap" type="Tag">
		<id property="id" column="tag_id" />
		<id property="name" column="tag_name" />
	</resultMap>
	<resultMap id="postResultMap" type="Post">
		<id property="id" column="post_id" />
		<result property="subject" column="post_subject" />
		<result property="body" column="post_body" />
		<association property="author" column="post_author_id"
			javaType="Author" resultMap="authorResultMap" />
		<collection property="tags" column="post_id" ofType="Tag"
			resultMap="tagResultMap" />
	</resultMap>
	<resultMap id="detailedBlogResultMap" type="Blog">
		<constructor>
			<idArg column="blog_id" javaType="int" />
		</constructor>
		<result property="title" column="blog_title" />
		<association property="author" column="blog_author_id"
			javaType="Author" resultMap="authorResultMap" />
		<collection property="posts" ofType="Post" resultMap="postResultMap" />
	</resultMap>
</mapper>

 

 

分享到：
| lucene入门例子
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

Magicbox
Global site tag (gtag.js) - Google Analytics