Error message here!

Hide Error message here!

忘记密码?

Error message here!

请输入正确邮箱

Hide Error message here!

密码丢失?请输入您的电子邮件地址。您将收到一个重设密码链接。

Error message here!

返回登录

Close

Mybatis关系映射

风沙迷了眼 2019-02-04 15:48:00 阅读数:150 评论数:0 点赞数:0 收藏数:0

一、一对一关系映射

使用resultType+包装类实现

1、假设问题背景是要求在某一个购物平台的后台程序中添加一个这样的功能:查询某个订单的信息和下该订单的用户信息。首先我们可以知道,一般这样的平台上面,某一笔订单只属于某一个用户,从这个角度来看,可以作为一对一的参考模型

①首先创建数据表user(用户表)CREATE TABLE user ( uidINT(11) NOT NULLAUTOINCREMENT, usernameVARCHAR(255) DEFAULT NULL, passwordVARCHAR(255) DEFAULT NULL, sexVARCHAR(255) DEFAULT NULL,PRIMARY KEY(uid) ) ENGINE=INNODB AUTOINCREMENT=9 DEFAULT CHARSET=utf8

创建orders表(所用的订单表)

CREATE TABLEorders ( oidINT(11) NOT NULLAUTOINCREMENT, totalDOUBLE DEFAULT NULL, ordertimeDATETIME DEFAULT NULL, nameVARCHAR(20) DEFAULT NULL, uidINT(11) DEFAULT NULL,PRIMARY KEY(oid),KEYFKC3DF62E5AA3D9C7 (uid),CONSTRAINT FKC3DF62E5AA3D9C7 FOREIGN KEY (uid) REFERENCES user (uid) ) ENGINE=INNODB AUTOINCREMENT=9004 DEFAULT CHARSET=utf8

  如下图所示:

②创建User和Order的实体类

1 packagecn.mybatis.po;2 3 public classUser {4 private intuid;5 privateString username;6 privateString password;7 privateString address;8 privateString sex;9 10 public intgetUid() {11 returnuid;12 }13 14 public void setUid(intuid) {15 this.uid =uid;16 }17 18 publicString getUsername() {19 returnusername;20 }21 22 publicString getPassword() {23 returnpassword;24 }25 26 publicString getAddress() {27 returnaddress;28 }29 30 publicString getSex() {31 returnsex;32 }33 34 public voidsetUsername(String username) {35 this.username =username;36 }37 38 public voidsetPassword(String password) {39 this.password =password;40 }41 42 public voidsetAddress(String address) {43 this.address =address;44 }45 46 public voidsetSex(String sex) {47 this.sex =sex;48 }49 50 publicUser(String username, String password, String address, String sex) {51 this.username =username;52 this.password =password;53 this.address =address;54 this.sex =sex;55 }56 57 publicUser() {58 }59 60 @Override61 publicString toString() {62 return "User{" + 63 "uid=" + uid + 64 ", username='" + username + ''' + 65 ", password='" + password + ''' + 66 ", address='" + address + ''' + 67 ", sex='" + sex + ''' + 68 '}';69 }70 }User类

1 packagecn.mybatis.po;2 3 importjava.util.Date;4 5 public classOrder {6 7 private intoid;8 private doubletotal;9 privateDate ordettime;10 privateString name;11 12 public intgetOid() {13 returnoid;14 }15 16 public void setOid(intoid) {17 this.oid =oid;18 }19 20 public doublegetTotal() {21 returntotal;22 }23 24 public void setTotal(doubletotal) {25 this.total =total;26 }27 28 publicDate getOrdettime() {29 returnordettime;30 }31 32 public voidsetOrdettime(Date ordettime) {33 this.ordettime =ordettime;34 }35 36 publicString getName() {37 returnname;38 }39 40 public voidsetName(String name) {41 this.name =name;42 }43 44 @Override45 publicString toString() {46 return "Order{" + 47 "oid=" + oid + 48 ", total=" + total + 49 ", ordettime=" + ordettime + 50 ", name='" + name + ''' + 51 '}';52 }53 }Order类

 

③用于需要同时查询User和Order的信息,所以需要用到上一篇中讲到的POJO输出映射类型。具体来说就是,需要自定义一个OrderPoJo,其中包含我们要查询的Order和User信息,定义的OrderPoJo类型如下(这里我们可以使用继承的方式,如果我们查询结果中哪一个类的要查询结果多就继承该类,可以简便包装类的编写)1 packagecn.mybatis.po;2 3 public class OrderPoJo extendsOrder{4 5 privateString username;6 privateString address;7 privateString sex;8 9 publicString getUsername() {10 returnusername;11 }12 13 public voidsetUsername(String username) {14 this.username =username;15 }16 17 publicString getAddress() {18 returnaddress;19 }20 21 public voidsetAddress(String address) {22 this.address =address;23 }24 25 publicString getSex() {26 returnsex;27 }28 29 public voidsetSex(String sex) {30 this.sex =sex;31 }32 33 @Override34 publicString toString() {35 return "OrderPoJo{" + 36 super.toString() + 37 "username='" + username + ''' + 38 ", address='" + address + ''' + 39 ", sex='" + sex + ''' + 40 '}';41 }42 }

④编写好对应的实体类和包装类之后就开始写,mapper配置文件和mapper.java。mapper配置文件中我们只需要根据问题背景写好相应的Sql语句就好

  这是编写好Sql的Mapper配置文件1 2 5 6 7 8 9 SELECT orders./*,10 user.username,user.sex,user.address11 FROM orders,USER12 WHERE oid = /#{id} AND user.uid = orders.uid13 14 15

下面是mapper接口中的一个方法,由于我们只需要完成这一个问题,所以OrderMapper接口也比较简单

⑤编写好所有的文件后,使用Junit来测试文件1 packagecn.mybatis.mapper;2 3 4 importcn.mybatis.po.OrderPoJo;5 importorg.apache.ibatis.io.Resources;6 importorg.apache.ibatis.session.SqlSession;7 importorg.apache.ibatis.session.SqlSessionFactory;8 importorg.apache.ibatis.session.SqlSessionFactoryBuilder;9 importorg.junit.Before;10 importorg.junit.Test;11 12 importjava.io.InputStream;13 14 public classOrderMapperTest {15 16 privateSqlSessionFactory sqlSessionFactory;17 18 @Before19 public void setUp() throwsException {20 InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");21 sqlSessionFactory = newSqlSessionFactoryBuilder().build(inputStream);22 }23 24 @Test25 public void testFindOrderAndUser() throwsException {26 SqlSession sqlSession =sqlSessionFactory.openSession();27 OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);28 29 OrderPoJo orderPoJo = orderMapper.findOrderAndUser(3);30 31 System.out.println(orderPoJo);32 33 sqlSession.close();34 35 }36 37 }

⑥我们来通过日志分析一下结果:最终能够按照既定的Sql查询出响应的结果

使用resultMap在配置文件中实现

1、我们使用resultMap来进行测试的时候,首先需要注意的是,由于没有自定义包装类型,所以需要在原始的Order中添加User类型的属性,保证可以在Mapper配置文件中将查询到的User属性配置到user中,从而得到关联查询结果1 packagecn.mybatis.po;2 3 importjava.util.Date;4 5 public classOrder {6 7 private intoid;8 private doubletotal;9 privateDate ordertime;10 privateString name;11 privateUser user;12 13 public intgetOid() {14 returnoid;15 }16 17 public void setOid(intoid) {18 this.oid =oid;19 }20 21 public doublegetTotal() {22 returntotal;23 }24 25 public void setTotal(doubletotal) {26 this.total =total;27 }28 29 publicDate getOrdertime() {30 returnordertime;31 }32 33 public voidsetOrdertime(Date ordertime) {34 this.ordertime =ordertime;35 }36 37 publicString getName() {38 returnname;39 }40 41 publicUser getUser() {42 returnuser;43 }44 45 public voidsetUser(User user) {46 this.user =user;47 }48 49 public voidsetName(String name) {50 this.name =name;51 }52 53 @Override54 publicString toString() {55 return "Order{" + 56 "oid=" + oid + 57 ", total=" + total + 58 ", ordertime=" + ordertime + 59 ", name='" + name + ''' + 60 ", user=" + user + 61 '}';62 }63 }

2、相关类和上面的内容一样,我们使用resultMap来实现,显然需要在Mapper中配置resultMap

1 5 6 7 8 9 10 11 12 13 17 18 19 20 21 22 23

3、然后在Mapper配置文件中使用resultMap类型的statment

SELECT orders./*, user.username,user.sex,user.address FROM orders,USER WHERE oid = /#{id} AND user.uid = orders.uid

4、结果同使用resultType的结果一样

二、一对多关系映射

 1、我们先分析一下orderitem和orders两张数据表的关系,我们能够想到一条订单中包含许多详细的订单条目信息,所以简单的得到下面的关系

2、在上面的基础上,我们再创建一张orderitem数据表CREATE TABLEorderitem ( itemidINT(11) NOT NULLAUTOINCREMENT, count INT(11) DEFAULT NULL, subtotalDOUBLE DEFAULT NULL, pidINT(11) DEFAULT NULL, oidINT(11) DEFAULT NULL,PRIMARY KEY(itemid),KEY(oid),KEY(pid),KEY(oid),FOREIGN KEY (oid) REFERENCESorders (oid),FOREIGN KEY (pid) REFERENCESproduct (pid) ) ENGINE=INNODB AUTOINCREMENT=17 DEFAULT CHARSET=utf8

 

3、我们再创建相应的实体类orderitem

1 packagecn.mybatis.po;2 3 public classOrderItem {4 5 private intitemid;6 private intcount;7 private doublesubtotal;8 private intpid;9 10 public intgetItemid() {11 returnitemid;12 }13 14 public void setItemid(intitemid) {15 this.itemid =itemid;16 }17 18 public intgetCount() {19 returncount;20 }21 22 public void setCount(intcount) {23 this.count =count;24 }25 26 public doublegetSubtotal() {27 returnsubtotal;28 }29 30 public void setSubtotal(doublesubtotal) {31 this.subtotal =subtotal;32 }33 34 public intgetPid() {35 returnpid;36 }37 38 public void setPid(intpid) {39 this.pid =pid;40 }41 42 @Override43 publicString toString() {44 return "OrderItem{" + 45 "itemid=" + itemid + 46 ", count=" + count + 47 ", subtotal=" + subtotal + 48 ", pid=" + pid + 49 '}';50 }51 }orderitem实体类

 

4、类似于上面讲到的使用resultMap的方式,我们在Order中添加上orderitem这一属性,作用就是维护Order和Orderitem的关联关系

1 packagecn.mybatis.po;2 3 importjava.util.Date;4 importjava.util.List;5 6 public classOrder {7 8 private intoid;9 private doubletotal;10 privateDate ordertime;11 privateString name;12 privateUser user;13 14 private ListorderItems;15 16 public intgetOid() {17 returnoid;18 }19 20 public void setOid(intoid) {21 this.oid =oid;22 }23 24 public doublegetTotal() {25 returntotal;26 }27 28 public void setTotal(doubletotal) {29 this.total =total;30 }31 32 publicDate getOrdertime() {33 returnordertime;34 }35 36 public voidsetOrdertime(Date ordertime) {37 this.ordertime =ordertime;38 }39 40 publicString getName() {41 returnname;42 }43 44 publicUser getUser() {45 returnuser;46 }47 48 public voidsetUser(User user) {49 this.user =user;50 }51 52 public voidsetName(String name) {53 this.name =name;54 }55 56 public ListgetOrderItems() {57 returnorderItems;58 }59 60 public void setOrderItems(ListorderItems) {61 this.orderItems =orderItems;62 }63 64 @Override65 publicString toString() {66 return "Order{" + 67 "oid=" + oid + 68 ", total=" + total + 69 ", ordertime=" + ordertime + 70 ", name='" + name + ''' + 71 ", user=" + user + 72 ", orderItems=" + orderItems + 73 '}';74 }75 }修改后的Order类

 

5、我们先配置Mapper文件。使用collection配置实体类中的List属性(List

 

  6、然后将上面配置的resultMap加入到statment中SELECT orders./*, user.username, user.sex, user.address, orderitem.itemid, orderitem.count, orderitem.subtotal FROM orders, USER, orderitem WHERE orders.oid = /#{id} AND user.uid = orders.uid AND orderitem.oid = orders.oid

7、然后在Mapper接口中添加测试方法

//一对多关系测试 public Order findOrderAndOrderItemByResultMap(int id) throws Exception;

8、将查询的结果进行输出可以发现能够正常查询出想要的结果

Order{oid=2, total=32.0, ordertime=Thu Dec 26 21:47:04 CST 2019, name='Lucy', user=User{uid=2, username='Rose', password='null', address='武汉市', sex='women'}, orderItems=[OrderItem{itemid=2, count=21, subtotal=32.0, pid=0}, OrderItem{itemid=4, count=32, subtotal=54.0, pid=0}]}

 

 三、多对多关系映射

1、问题背景就是查询User所购买的商品详细信息,即查询结果包括User信息和Product信息。我们先分析一下整个数据表之间的关系如下图所示

2、在上面分析的基础上,我们创建Product数据表的对应的Product实体类CREATE TABLEproduct ( pidINT(11) NOT NULLAUTOINCREMENT, pnameVARCHAR(255) DEFAULT NULL, `shoppriceDOUBLE DEFAULT NULL,PRIMARY KEY(pid`) ) ENGINE=INNODB AUTO_INCREMENT=80 DEFAULT CHARSET=utf8

1 packagecn.mybatis.po;2 3 public classProduct {4 private intpid;5 privateString pname;6 private doubleshopprice;7 8 public intgetPid() {9 returnpid;10 }11 12 public void setPid(intpid) {13 this.pid =pid;14 }15 16 publicString getPname() {17 returnpname;18 }19 20 public voidsetPname(String pname) {21 this.pname =pname;22 }23 24 public doublegetShopprice() {25 returnshopprice;26 }27 28 public void setShopprice(doubleshopprice) {29 this.shopprice =shopprice;30 }31 32 @Override33 publicString toString() {34 return "Product{" + 35 "pid=" + pid + 36 ", pname='" + pname + ''' + 37 ", shopprice=" + shop_price + 38 '}';39 }40 }Product实体类

 

3、一般编写Mapper配置文件可以使用resultMap或者resultType(+自定义扩展类型)来实现,这里,我们使用resultMap在Mapper配置文件中进行,所以需要在OrderItem类中添加Product类型的属性,修改后的OrderItem类如下

1 packagecn.mybatis.po;2 3 public classOrderItem {4 5 private intitemid;6 private intcount;7 private doublesubtotal;8 9 privateProduct product;10 11 public intgetItemid() {12 returnitemid;13 }14 15 public void setItemid(intitemid) {16 this.itemid =itemid;17 }18 19 public intgetCount() {20 returncount;21 }22 23 public void setCount(intcount) {24 this.count =count;25 }26 27 public doublegetSubtotal() {28 returnsubtotal;29 }30 31 public void setSubtotal(doublesubtotal) {32 this.subtotal =subtotal;33 }34 35 publicProduct getProduct() {36 returnproduct;37 }38 39 public voidsetProduct(Product product) {40 this.product =product;41 }42 43 @Override44 publicString toString() {45 return "OrderItem{" + 46 "itemid=" + itemid + 47 ", count=" + count + 48 ", subtotal=" + subtotal + 49 ", product=" + product + 50 '}';51 }52 }修改的OrderItem类

4、修改相应的实体类后,可以再Mapper配置文件中配置查询结果User信息以及关联的Order、OrderItem和所要的Product信息,配置如下

price"property="shopprice">

5、然后编写响应的Sql,并添加到Mapper中的statment中

SELECT orders./*, user.username, user.sex, user.address, orderitem.itemid, orderitem.count, orderitem.subtotal, product.pid, product.pname, product.shop_price FROM orders, USER, orderitem, product WHERE user.uid = orders.uid AND orderitem.oid = orders.oid

6、在Mapper.java中添加响应的方法

public List findUsersAndProduct() throws Exception;

7、最后使用Junit测试结果如下

 

版权声明
本文为[风沙迷了眼]所创,转载请带上原文链接,感谢
https://www.cnblogs.com/fsmly/p/10339619.html

编程之旅,人生之路,不止于编程,还有诗和远方。
阅代码原理,看框架知识,学企业实践;
赏诗词,读日记,踏人生之路,观世界之行;

支付宝红包,每日可领