目录
一对一查询
当某个字段比较占内存，但不常用时，比如存储一本书book，分很多章节，章节内容不需要在浏览的时候就查询获取到，只需要章节标题标识即可，这中情况下多进行分表存储，采用分布查询的方式获取章节内容。
数据库准备：bookbody 记录章节标题(title)和章节内容(booy)，book记录章节(chapter)、章节标题(title)、该章节是否免费(isfree)和该章节内容是否下载(loaded)
#建表
CREATE TABLE bookbody (
title VARCHAR(255) NOT NULL,
body TEXT NOT NULL
)
#添加数据
INSERT bookbody(title, body)
values
('章节标题1','第一章章节内容...'),
('章节标题2','第二章章节内容...'),
('章节标题3','第三章章节内容...'),
('章节标题4','第四章章节内容...'),
('章节标题5','第五章章节内容...')
#建表
CREATE TABLE book (
chapter VARCHAR(255) NOT NULL,
title VARCHAR(255) NOT NULL,
isfree enum('0','1') DEFAULT '0',
loaded enum('0','1') DEFAULT '0'
)
#添加数据
INSERT book(chapter,title,isfree,loaded)
values
('第一章','章节标题1','1','1'),
('第二章','章节标题2','1','0'),
('第三章','章节标题3','0','0'),
('第四章','章节标题4','0','0'),
('第五章','章节标题5','0','0')
poji对象类定义：Book、BookBody
package Entity;
public class Book {
private String chapter;
private String title;
private Boolean isfree;
private Boolean loaded;
// getter setter toString
}
package Entity;
public class BookBody {
private String title;
private String body;
// getter setter toString
}
一对一立即加载
一对一立即加载可以一步联合查询
@Select("select book.*, bookbody.body from book, bookbody where chapter = #{chapter} and book.title = bookbody.title")
@Results(id="bookMap", value={ //相当于mapper xml文件中的resultMap， 将数据库查询到的字段对实体类属性封装
@Result(column="chapter", property="chapter"),
@Result(column="isfree", property="isfree"),
@Result(column = "loaded",property = "loaded")，
// 对bookbody属性封装数据
@Result(column="title", property="bookbody.title"),
@Result(column="body", property="bookbody.body")
})
Book selectByChapterFrombook(String chapter) ;或使用@One注解分步查询
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({})
public @interface One {
String select() default "";
FetchType fetchType() default FetchType.DEFAULT;
}select引用第二步查询的sql接口方法
fetchType控制延迟加载（即第二步查询立即查询还是等到访问第二步查询结果时才开始查询）
fetchType设置FetchType.EAGER表示立即加载
@Select("select * from bookbody where title = #{title}")
BookBody selectByTitleFrombookbody(String title) ;
@Select("select * from book where chapter = #{chapter}")
@Results(id="bookMap", value={
@Result(column="chapter", property="chapter"),
@Result(column="title", property="bookbody", one=@One(select = "DAO.BookDAO.selectByTitleFrombookbody",fetchType = FetchType.EAGER)),
@Result(column="isfree", property="isfree"),
@Result(column = "loaded",property = "loaded")
})
Book selectByChapterFrombook(String chapter) ;
测试
@Test
public void test() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-conf.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
BookDAO mapper = sqlSession.getMapper(BookDAO.class);
Book book = mapper.selectByChapterFrombook("第二章");
System.out.println(book.getChapter());
System.out.println(book.getIsfree());
System.out.println(book.getLoaded());
System.out.println("============================");
System.out.println(book.getBookbody().getBody());
sqlSession.commit();
sqlSession.close();
}17:03:49.637 [main] DEBUG DAO.BookDAO.selectByChapterFrombook - ==> Preparing: select * from book where chapter = ?
17:03:49.670 [main] DEBUG DAO.BookDAO.selectByChapterFrombook - ==> Parameters: 第二章(String)
17:03:49.720 [main] DEBUG DAO.BookDAO.selectByChapterFrombook - <== Total: 1
17:03:49.721 [main] DEBUG DAO.BookDAO.selectByTitleFrombookbody - ==> Preparing: select * from bookbody where title = ?
17:03:49.722 [main] DEBUG DAO.BookDAO.selectByTitleFrombookbody - ==> Parameters: 章节标题2(String)
17:03:49.729 [main] DEBUG DAO.BookDAO.selectByTitleFrombookbody - <== Total: 1
第二章
true
false
第二章章节内容...一对一延迟加载
延迟加载必须分布查询
在查询book中的章节时，我们如果是需要浏览章节标题，等需要查看章节内容时再执行分布查询的下一步的情况下，可以通过fetchType控制是否使用延迟加载，注意需要先再mybatis全局配置文件中设置开启延迟加载
<!-- 设置-->
<settings>
<!-- 是否开启延迟加载-->
<setting name="lazyLoadingEnabled" value="true"/>
</settings>然后在@One设置fetchType=FetchType.LAZY使用延迟加载
@Select("select * from bookbody where title = #{title}")
BookBody selectByTitleFrombookbody(String title) ;
@Select("select * from book where chapter = #{chapter}")
@Results(id="bookMap", value={
@Result(column="chapter", property="chapter"),
@Result(column="title", property="bookbody",
one=@One(select = "DAO.BookDAO.selectByTitleFrombookbody",fetchType = FetchType.LAZY)),
@Result(column="isfree", property="isfree"),
@Result(column = "loaded",property = "loaded")
})
Book selectByChapterFrombook(String chapter) ;
从结果打印中可以看到，第二步查询直到调用第一步查询的结果book调用getBookbody()方法时才执行，这里体现的延迟加载
17:48:15.193 [main] DEBUG DAO.BookDAO.selectByChapterFrombook - ==> Preparing: select * from book where chapter = ?
17:48:15.232 [main] DEBUG DAO.BookDAO.selectByChapterFrombook - ==> Parameters: 第二章(String)
17:48:15.291 [main] DEBUG DAO.BookDAO.selectByChapterFrombook - <== Total: 1
第二章
true
false
============================
17:48:15.292 [main] DEBUG DAO.BookDAO.selectByTitleFrombookbody - ==> Preparing: select * from bookbody where title = ?
17:48:15.293 [main] DEBUG DAO.BookDAO.selectByTitleFrombookbody - ==> Parameters: 章节标题2(String)
17:48:15.302 [main] DEBUG DAO.BookDAO.selectByTitleFrombookbody - <== Total: 1
第二章章节内容...一对多查询
多表查询中一对多和多对多查询一般借助中间表记录对应关系
比如在购物商城系统中，存在用户账户信息表account和商品信息表commodity, 一个用户可以购买多样商品，假设我们查询用户"huahua"购买的商品，应该通过中间表user_commodity查到"huahua"购买了衣服、裤子和鞋子这三个商品，这种是多表查询中的一对多的情况。
用户账户信息表account
商品信息表commodity
中间表account_commodity
pojo类准备
package Entity;
public class Commodity {
private Integer id;
private String name;
private Double price;
// ...
}
package Entity;
import java.util.Arrays;
public class Account {
private Integer id;
private String name;
private String sex;
private Commodity[] commodities; //一个用户可以购买多件商品
// ...
}
一对多立即加载
一对多需要分步查询，比如这里对"huahua"的购买商品需要在第二步查询时通过Commodity对象接收
使用@Many注解接收第二步查询得到的多条数据
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({})
public @interface Many {
String select() default "";
FetchType fetchType() default FetchType.DEFAULT;
}同样的fetchType设置FetchType.EAGER表示立即加载
@Select("select c.* from commodity c, account_commodity ac where ac.aid = #{id} and ac.cid = c.cid")
@Results(id="commodityMap",value = {
@Result(column = "cid", property = "id"),
@Result(column = "cname", property = "name"),
@Result(column = "cprice", property = "price")
})
public Commodity findById(String id);
@Select("select * from account a where a.aname = #{name}")
@Results(id="accountMap",value = {
@Result(column = "aid", property = "id"),
@Result(column = "aname", property = "name"),
@Result(column = "asex", property = "sex"),
@Result(column = "aid", property = "commodities", many = @Many(select = "DAO.AccountDAO.findById",fetchType = FetchType.EAGER))
})
public Account findByName(String name);测试
@Test
public void test() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-conf.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
AccountDAO mapper = sqlSession.getMapper(AccountDAO.class);
Account account = mapper.findByName("huahua");
System.out.println("-----------------------------------------------");
System.out.println(account.getId());
System.out.println(account.getName());
System.out.println(account.getSex());
for(Commodity c : account.getCommodities())
System.out.println(c);
System.out.println("-----------------------------------------------");
sqlSession.commit();
sqlSession.close();
}20:17:51.371 [main] DEBUG DAO.AccountDAO.findByName - ==> Preparing: select * from account a where a.aname = ?
20:17:51.409 [main] DEBUG DAO.AccountDAO.findByName - ==> Parameters: huahua(String)
20:17:51.434 [main] DEBUG DAO.AccountDAO.findById - ====> Preparing: select c.* from commodity c, account_commodity ac where ac.aid = ? and ac.cid = c.cid
20:17:51.439 [main] DEBUG DAO.AccountDAO.findById - ====> Parameters: 1(String)
20:17:51.441 [main] DEBUG DAO.AccountDAO.findById - <==== Total: 3
20:17:51.441 [main] DEBUG DAO.AccountDAO.findByName - <== Total: 1
-----------------------------------------------
1
huahua
女
Commodity{id=2, name='裤子', price=200.0}
Commodity{id=1, name='衣服', price=100.0}
Commodity{id=3, name='鞋子', price=300.0}
-----------------------------------------------从打印日志中可以看到，两条sql语句紧接着执行
一对多延迟加载
同样的fetchType设置FetchType.LAZY表示延迟加载
@Select("select c.* from commodity c, account_commodity ac where ac.aid = #{id} and ac.cid = c.cid")
@Results(id="commodityMap",value = {
@Result(column = "cid", property = "id"),
@Result(column = "cname", property = "name"),
@Result(column = "cprice", property = "price")
})
public Commodity findById(String id);
@Select("select * from account a where a.aname = #{name}")
@Results(id="accountMap",value = {
@Result(column = "aid", property = "id"),
@Result(column = "aname", property = "name"),
@Result(column = "asex", property = "sex"),
@Result(column = "aid", property = "commodities", many = @Many(select = "DAO.AccountDAO.findById",fetchType = FetchType.LAZY))
})
public Account findByName(String name);20:25:42.535 [main] DEBUG DAO.AccountDAO.findByName - ==> Preparing: select * from account a where a.aname = ?
20:25:42.561 [main] DEBUG DAO.AccountDAO.findByName - ==> Parameters: huahua(String)
20:25:42.623 [main] DEBUG DAO.AccountDAO.findByName - <== Total: 1
-----------------------------------------------
1
huahua
女
20:25:42.631 [main] DEBUG DAO.AccountDAO.findById - ==> Preparing: select c.* from commodity c, account_commodity ac where ac.aid = ? and ac.cid = c.cid
20:25:42.632 [main] DEBUG DAO.AccountDAO.findById - ==> Parameters: 1(String)
20:25:42.635 [main] DEBUG DAO.AccountDAO.findById - <== Total: 3
Commodity{id=2, name='裤子', price=200.0}
Commodity{id=1, name='衣服', price=100.0}
Commodity{id=3, name='鞋子', price=300.0}
-----------------------------------------------从打印日志中可以看到， 第二条sql语句在访问Account对象的commodities属性时才执行，这里体现延迟加载
多对一查询本质是进行多个一对一查询
多对多查询本质是进行多个一对多查询
这里就不举例了。
1026
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
