MyBatis insert association

Question

I am a newbie to MyBatis and trying to insert a record into a table having relationship with another table. I followed this tutorial. However, my approach is not exactly what that tutorial explain.

There are two tables called 'blog', 'post'.
CREATE TABLE blog ( blog_id int(10) unsigned NOT NULL auto_increment, blog_name varchar(45) NOT NULL, PRIMARY KEY (blog_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE post (
  post_id int(10) unsigned NOT NULL auto_increment,
  title varchar(45) NOT NULL,
  blog_id int(10) unsigned NOT NULL,
  PRIMARY KEY  (post_id),
  KEY FK_post_blog (blog_id),
  CONSTRAINT FK_post_blog FOREIGN KEY (blog_id) REFERENCES blog (blog_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Followings are mapping pojo classes:

public class Blog {
        private Integer blogId;
        private String blogName;
        private Date createdOn;
        private List<Post> posts = new ArrayList<>();
    //getters and setters
}

public class Post   {
        private Integer postId;
        private String title;
        private String content;
        private Integer blogId; // <= How currently saving instead of 'private Blog blog;'
//getters and setters
}

Following is my PostMapper.xml

<mapper namespace="PostMapper">    
    <resultMap id="result" type="Post">
        <result property="postId" column="post_id"/>
        <result property="title" column="title"/>
        <result property="blogId " column="blog_id "/>
    </resultMap>
    <insert id="insert" parameterType="Post" useGeneratedKeys="true" keyProperty="postId">
        INSERT INTO post (title, blog_id) VALUES (#{title}, #{blogId});
    </insert>
  </mapper>

To explain the requirement:

• One Blog has many 'post' s. 'post' table has foreign key of blog id. 'blog' table has it own values and when a record to 'post' table is to be inserted, currently blog id is saved straight.I think that Post pojo should have the Blog variable instead of blogId. Currentlty I am saving with blogId. However, in this scenario a new record to 'blog' table is not inserted, only a PK of blog table is saved into post table. (Blog table records are like constant).
  I need to know followings:
  Q1.
  How can I insert into 'post' table having the Blog variable in Post pojo.(I think that is the best approach than defining the blogId). Will this need a association ? Any sample Code please. Q2.
  In my application, I have only the Blog name, not the particular Blog's primary key. Is there any way to let the job for Mybatis to find the belonging Blog record for given name and save blogId in 'post' table ?
  For example:

  Blog blog = new Blog();
  b.setName("Blog Name");
  post.setBlog(blog);
  postDao.savePost(post);

  Or do I have to write a inner find query to get the blog id and set in the mapper xml ? Please let me know the workaround. Code examples are highly appreciated.

I use mysql 5.7. Java 8

Answer 1

Storing blogId in the Post object is the standard way to do things. MyBatis does not support nested inserts. You are going to have to have the Blog's id in the database anyway as a foreign key. It's usually easier overall to just do:

Blog blog = blogDao.getByName(name)
blog.SetPosts(postDao.getByBlog(blog))

Where the getByBlog method uses your existing PostMapper ResultMap and a select like SELECT * FROM post WHERE blog_id=#{blog_id}.

If you really want to have the Post have a Blog object field, you can do something like this

<resultMap id="PostWithBlog" type="Post">
  <result property="postId" column="post_id"/>
  <result property="title" column="title"/>
  <association property="blog" column="blog_id" javaType="Blog">
    <id property="id" column="blog_id"/>
    <result property="name" column="blog_name/>
    <result property="created_on" column="blog_created_on"/>
  </association>
</resultMap>

<select id="selectPostWithBlog" parameterType="int" resultMap="PostWithBlog">
  SELECT p.postId AS post_id, p.title AS title, p.blog_id AS blog_id, b.blogName AS blog_name, b.created_on AS blog_created_on
  FROM post p JOIN blog B ON p.blog_id=b.blog_id
  WHERE p.post_id = #{postId}
</select>

<insert id="insertWithBlog" parameterType="Post" useGeneratedKeys="true" keyProperty="postId">
        INSERT INTO post (title, blog_id) VALUES (#{title}, SELECT blog_id FROM blog WHERE name=#{blog.name});
</insert>

Note the <association> in the ResultMap and the use of blog.blogName in the insert with an inner SELECT.

You should also be aware that this select will cause every Post will be built with a separate Blog object. So, performance may be worse if you are selecting a large number of Posts.