浅入浅出MyBatis(08):获取一个用户的所有blog

January 14th 2015  | Tags: MyBatis, orm

2015-01-14

该文章是《专题:浅入浅出MyBatis》系列文章的一篇。点击进入该专题目录

工具与环境:Intellij IDEA 14,JDK 1.7,MyBatis 3.2,MySQL 5.5。

浅入浅出MyBatis(01):查询id为1的用户的信息的基础上,我们看一下该如何实现。

新建Table

在之前,已经创建了user表,其中的数据有:

mysql> select * from user;
+----+--------+----------------+----------+
| id | name   | email          | password |
+----+--------+----------------+----------+
|  1 | letian | letian@111.com | 123      |
|  2 | xiaosi | xiaosi@111.com | 123      |
+----+--------+----------------+----------+
2 rows in set (0.00 sec)

现在新建一个blog表,

CREATE  TABLE `blog_db`.`blog` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `owner_id` INT NOT NULL ,
  `title` TEXT NOT NULL ,
  `content` TEXT NOT NULL ,
  PRIMARY KEY (`id`) )
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

并插入数据:

INSERT INTO `blog_db`.`blog` (`owner_id`, `title`, `content`) VALUES ('1', '标题1', '文本1');

......

最终如下:

mysql> SELECT * FROM blog_db.blog;
+----+----------+----------+----------+
| id | owner_id | title    | content  |
+----+----------+----------+----------+
|  1 |        1 | 标题1    | 文本1    |
|  2 |        1 | 标题2    | 文本2    |
|  3 |        1 | 标题3    | 文本3    |
|  4 |        1 | 标题4    | 文本4    |
|  5 |        1 | 标题5    | 文本5    |
|  6 |        2 | 标题21   | 文本21   |
+----+----------+----------+----------+
6 rows in set (0.00 sec)

user对blog是一对多的关系。

代码

在letian.mybatis.bean下新建Blog.java

其内容如下:

package letian.mybatis.bean;

public class Blog {
    private int id;
    private int ownerId;
    private String title;
    private String content;

    public int getOwnerId() {
        return ownerId;
    }

    public void setOwnerId(int ownerId) {
        this.ownerId = ownerId;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    @Override
    public String toString() {
        return "Blog{" +
                "id=" + id +
                ", ownerId=" + ownerId +
                ", title='" + title + '\'' +
                ", content='" + content + '\'' +
                '}' + '\n';
    }
}

修改letian.mybatis.bean下的User.java

将其修改为:

package letian.mybatis.bean;

import java.util.List;

public class User {

    private int id;
    private String name;
    private String email;
    private String password;
    private List<Blog> blogs;

    public List<Blog> getBlogs() {
        return blogs;
    }

    public void setBlogs(List<Blog> blogs) {
        this.blogs = blogs;
    }

    public void setId(int id) {
        this.id = id;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public int getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public String getEmail() {
        return email;
    }

    public String getPassword() {
        return password;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", password='" + password + '\'' + '\n' +
                ", blogs=" + blogs +
                '}';
    }
}

就是加了个blogs属性用来保存一个用户的所有blog。

修改letian.mybatis.dao下的UserMapper.java

package letian.mybatis.dao;

import letian.mybatis.bean.User;

public interface UserMapper {

    User findById(int id);

}

其实内容没变化,我把代码贴出来强调一下。

修改letian.mybatis.mapper下的UserMapper.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="letian.mybatis.dao.UserMapper">

    <resultMap id="userResult" type="letian.mybatis.bean.User">
        <result property="id" column="user_id"/>
        <result property="name" column="user_name"/>
        <result property="email" column="user_email"/>
        <result property="password" column="user_password"/>

        <collection property="blogs" ofType="letian.mybatis.bean.Blog">
            <id property="id" column="blog_id"/> <!-- 用result也可以 -->
            <result property="ownerId" column="user_id"/>
            <result property="title" column="blog_title"/>
            <result property="content" column="blog_content"/>
        </collection>

    </resultMap>

    <select id="findById" parameterType="Integer" resultMap="userResult" resultType="letian.mybatis.bean.User">
        SELECT
        user.id AS user_id,
        user.name AS user_name,
        user.email AS user_email,
        user.password AS user_password,
        blog.id AS blog_id,
        blog.title AS blog_title,
        blog.content AS blog_content
        FROM user, blog WHERE user.id = #{id} and user.id=blog.owner_id;
    </select>

</mapper>

<select id="findById"></select>中的sql语句的作用是根据给出的用户id得到用户的信息以及该用户所有的blog。

<select id="findById"></select>resultMap属性指向了<resultMap id="userResult"></resultMap>。下面看一下这个userResult

        <result property="id" column="user_id"/>
        <result property="name" column="user_name"/>
        <result property="email" column="user_email"/>
        <result property="password" column="user_password"/>

上面的代码片段的作用是将sql语句得到的四个column(user_iduser_nameuser_emailuser_password)分别放入letian.mybatis.bean.User类生成的对象的四个属性(idnameemailpassword)中。

letian.mybatis.bean.User类的blogs属性是List<Blog>类型,用来存放多个blog,所以用<collection></collection>来配置:

        <collection property="blogs" ofType="letian.mybatis.bean.Blog">
            <id property="id" column="blog_id"/> <!-- 用result也可以 -->
            <result property="ownerId" column="user_id"/>
            <result property="title" column="blog_title"/>
            <result property="content" column="blog_content"/>
        </collection>

ofType属性指定每个blog的类型。<collection></collection>中的子元素的配置和配置letian.mybatis.bean.Blog类是一样的。这里,将letian.mybatis.bean.Blogid属性与执行sql语句得到的blog_id字段对应,ownerIdtitle等类似。

修改Main.java

import java.io.IOException;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import letian.mybatis.bean.User;
import letian.mybatis.dao.UserMapper;


public class Main {

    public static void main(String[] args) throws IOException {

        SqlSessionFactory sessionFactory;
        sessionFactory = new SqlSessionFactoryBuilder()
                .build(Resources.getResourceAsReader("mybatis-config.xml"));

        SqlSession sqlSession = sessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        User user = userMapper.findById(1);
        System.out.println(user);

    }
}

执行

执行Main.java,结果如下:

User{id=1, name='letian', email='letian@111.com', password='123'
, blogs=[Blog{id=1, ownerId=1, title='标题1', content='文本1'}
, Blog{id=2, ownerId=1, title='标题2', content='文本2'}
, Blog{id=3, ownerId=1, title='标题3', content='文本3'}
, Blog{id=4, ownerId=1, title='标题4', content='文本4'}
, Blog{id=5, ownerId=1, title='标题5', content='文本5'}
]}

补充

如果你想让用户保存自己的Blog,那么你可以这样做:

修改letian.mybatis.dao下的UserMapper.java:

package letian.mybatis.dao;

import letian.mybatis.bean.User;
import letian.mybatis.bean.Blog;

public interface UserMapper {

    User findById(int id);
    void saveBlog(Blog blog);

}

(完)