Mybatis第二章——多表同时插入和级联查询
2019-07-25 本文已影响0人
韩克
Mybatis第二章——多表同时插入和级联查询
知识点一：多表同时插入，其中要插入的Blog的数据中的author_id依赖于另一个要插入的author对象的id
此时需要在mapper.xml文件中配置 useGeneratedKeys="true" keyProperty="id"，只需要在被依赖的对象中配置。
<!-- 多表插入 begin -->
<!-- 由于addBlog中的#{author.id}依赖于addAuthor后的id,因此需要authord对象中的id自增 -->
<insert id="addAuthor" parameterType="author" useGeneratedKeys="true" keyProperty="id">
insert into tb_author(username,password,email,address,phone) values(#{username},#{password},#{email},#{address},#{phone});
</insert>
<insert id="addBlog" parameterType="blog" >
insert into tb_blog(title,content,type,author_id) values(#{title},#{content},#{type},#{author.id});
</insert>
<!-- 多表插入 end -->
注意：如果不写useGeneratedKeys="true" keyProperty="id"，那么返回给author对象的id将为默认值'0'，
而数据库的author转换成的该条记录可以自增;
useGeneratedKeys的主键自动增长，不是数据库中的数据primary key自动增长，而是让数据库主键匹配到的对象的主键自动增长。
@org.junit.Test
public void test1(){
SqlSessionFactory sqlSessionFactory = MyBatisUtils.getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession();
TestMapper testMapper = sqlSession.getMapper(TestMapper.class);
Author author = new Author("吴健红", "123456", "wjh@163.com", "甘肃", "18888888888");
Blog blog = new Blog("旅游", "去上海旅游", "游玩", author);
testMapper.addAuthor(author);//需要主键生成s
//testMapper.addBlog(blog); 在不使用主键自动增长的情况下，只插入author对象，这样不会报错，从而来测试author对象的id
sqlSession.commit();
System.out.println(author);
sqlSession.close();
//运行结果：Author [id=0, username=吴健红, password=123456, email=wjh@163.com, address=甘肃, phone=18888888888]
知识点二:Mybatis 实现多表查询方式
1.1 业务装配.对两个表编写单表查询语句,在业务(Service)把查询
的两个结果进行关联.
1.2 使用 Auto Mapping 特性,在实现两表联合查询时通过别名完成
映射.
1.3 使用 MyBatis 的<resultMap>标签进行实现.
知识点三：级联一对一的查询(N+1)
N+1 查询方式,先查询出某个表的全部信息,根据这个表的信息
查询另一个表的信息.需要使用到<resultMap>标签
pojo类——Author
package com.xtkj.pojo;
public class Author {
private int id;
private String username;
private String password;
private String email;
private String address;
private String phone;
public Author() {
super();
// TODO Auto-generated constructor stub
}
public Author(String username, String password, String email,
String address, String phone) {
super();
this.username = username;
this.password = password;
this.email = email;
this.address = address;
this.phone = phone;
}
@Override
public String toString() {
return "Author [id=" + id + ", username=" + username + ", password="
+ password + ", email=" + email + ", address=" + address
+ ", phone=" + phone + "]";
}
get...
set...
}
pojo类——blog
package com.xtkj.pojo;
import java.util.ArrayList;
import java.util.List;
public class Blog {
private int id;
private String title;
private String content;
private String type;
private Author author;//author_id select * from tb_author where id = author_id;-->author
private List<Comment> comments = new ArrayList<Comment>();//id select * from tb_comment where blog_id=id;-->N comment
public Blog() {
super();
// TODO Auto-generated constructor stub
}
public Blog(String title, String content, String type, Author author) {
super();
this.title = title;
this.content = content;
this.type = type;
this.author = author;
}
@Override
public String toString() {
return "Blog [id=" + id + ", title=" + title + ", content=" + content
+ ", type=" + type + ", author=" + author + "]";
}
get...
set...
}
mapper.xml
<!-- 级联对一的查询 findBlogById返回去的 -->
<resultMap type="blog" id="aa">
<id column="id" property="id"/>
<result column="title" property="title"/>
<result column="content" property="content"/>
<result column="type" property="type"/>
<!-- 对一的配置使用association，通过查询出的author_id调用findAuthorById将author对象查询出来并付给blog对象里的author，javaType="author"可以省略 -->
<association column="author_id" select="findAuthorById" javaType="author" property="author"></association>
</resultMap>
<!-- 主查询 -->
<select id="findBlogById" parameterType="int" resultMap="aa">
select * from tb_blog where id=#{id}
</select>
<!-- 主查询 -->
<select id="findAuthorById" parameterType="int" resultType="author">
select * from tb_author where id=#{id}
</select>
大前提使用 N+1 方式.时如果列名和属性名相同可
以不配置,使用 Auto mapping 特性.但是 mybatis 默认只会给列
专配一次
测试
@org.junit.Test
public void test2(){
SqlSessionFactory sqlSessionFactory = MyBatisUtils.getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession();
TestMapper testMapper = sqlSession.getMapper(TestMapper.class);
Blog blog = testMapper.findBlogById(15);
System.out.println(blog);
//sqlSession.commit();
sqlSession.close();
//结果：Blog [id=15, title=旅游, content=去上海旅游, type=游玩, author=Author [id=17, username=吴健红, password=123456, email=wjh@163.com, address=甘肃, phone=18888888888]]
}
知识点四：级联一对多的查询（N+1方式）
mapper.xml
<resultMap type="blog" id="aa">
<id column="id" property="id"/>
<result column="title" property="title"/>
<result column="content" property="content"/>
<result column="type" property="type"/>
<!-- <result column="author_id" property="author"/> -->
<!-- 对多的配置使用collection，通过查询初的blog_id将comment对象查询出来并付给blog对象中的comments集合 -->
<collection column="id" select="findCommentByBlogId" ofType="comment" property="comments"></collection>
</resultMap>
<!-- 主查询 通过id查询blog-->
<select id="findBlogById" parameterType="int" resultMap="aa">
select * from tb_blog where id=#{id}
</select>
<!-- 级联对多的查询，通过comment中的blog_id查询出该blog下的所有comments -->
<select id="findCommentByBlogId" parameterType="int" resultType="comment">
select * from tb_comment where blog_id=#{blog_id}
</select>
测试
@org.junit.Test
public void test3(){
SqlSessionFactory sqlSessionFactory = MyBatisUtils.getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession();
TestMapper testMapper = sqlSession.getMapper(TestMapper.class);
Blog blog = testMapper.findBlogById(15);
System.out.println(blog);
List<Comment> comments = blog.getComments();
for(int i=0;i<comments.size();i++){
System.out.println("content:"+comments.get(i).getContent());
}
//sqlSession.commit();
sqlSession.close();
}
/*
结果：
Blog [id=15, title=旅游, content=去上海旅游, type=游玩, author=Author [id=17, username=吴健红, password=123456, email=wjh@163.com, address=甘肃, phone=18888888888]]
content:niho
content:lala
*/
知识点五：使用resultMap实现加载集合数据(联合查询方式)
<!-- 使用resultMap 进行联合查询 -->
<resultMap type="blog" id="mymap">
<id column="bid" property="id"/>
<result column="btitle" property="title"/>
<result column="btitle" property="title"/>
<result column="bcontent" property="content"/>
<result column="btype" property="type"/>
<association column="aid" select="findAuthorById" javaType="author" property="author"></association>
<collection property="comments" ofType="comment">
<id column="cid" property="id"/>
<result column="ccontent" property="content"/>
</collection>
</resultMap>
<select id="findBlogAndComments" resultMap="mymap" parameterType="int">
select b.id bid,b.title btitle,b.content bcontent,b.type btype,b.author_id aid,
c.id cid,c.content ccontent from tb_blog b left join tb_comment c on b.id = c.blog_id
where b.id=#{0}
</select>
测试
@org.junit.Test
public void test4(){
SqlSessionFactory sqlSessionFactory = MyBatisUtils.getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession();
TestMapper testMapper = sqlSession.getMapper(TestMapper.class);
Blog blog = testMapper.findBlogAndComments(15);
System.out.println(blog);
List<Comment> comments = blog.getComments();
for(int i=0;i<comments.size();i++){
System.out.println("content:"+comments.get(i).getContent());
}
//sqlSession.commit();
sqlSession.close();
}
/*
结果：
Blog [id=15, title=旅游, content=去上海旅游, type=游玩, author=Author [id=17, username=吴健红, password=123456, email=wjh@163.com, address=甘肃, phone=18888888888]]
content:niho
content:lala
*/
