您现在的位置是:首页 > 个人日记个人日记

mybatis-06-映射关系

2019-02-03 18:11:49【个人日记】115人已围观

简介一对一,一对多等多种对应关系查询数据

对应关系

一对一映射

表示两个表同时查询,获取相对应的数据

主要的表封装了另一张表的数据

  1. package com.huangxin.model;
  2. import java.math.BigDecimal;
  3. public class Product {
  4. private Integer id;
  5. private String productName;
  6. private BigDecimal salePrice;
  7. private String supplier;
  8. private String brand;
  9. private BigDecimal costPrice;
  10. private Message message;//主要的表封装了其它表的信息
  11. @Override
  12. public String toString() {
  13. return "Product{" +
  14. "id=" + id +
  15. ", productName='" + productName + '\'' +
  16. ", salePrice=" + salePrice +
  17. ", supplier='" + supplier + '\'' +
  18. ", brand='" + brand + '\'' +
  19. ", costPrice=" + costPrice +
  20. ", message=" + message +
  21. '}';
  22. }
  23. public Message getMessage() {
  24. return message;
  25. }
  26. public void setMessage(Message message) {
  27. this.message = message;
  28. }
  29. public Integer getId() {
  30. return id;
  31. }
  32. public void setId(Integer id) {
  33. this.id = id;
  34. }
  35. public String getProductName() {
  36. return productName;
  37. }
  38. public void setProductName(String productName) {
  39. this.productName = productName;
  40. }
  41. public BigDecimal getSalePrice() {
  42. return salePrice;
  43. }
  44. public void setSalePrice(BigDecimal salePrice) {
  45. this.salePrice = salePrice;
  46. }
  47. public String getSupplier() {
  48. return supplier;
  49. }
  50. public void setSupplier(String supplier) {
  51. this.supplier = supplier;
  52. }
  53. public String getBrand() {
  54. return brand;
  55. }
  56. public void setBrand(String brand) {
  57. this.brand = brand;
  58. }
  59. public BigDecimal getCostPrice() {
  60. return costPrice;
  61. }
  62. public void setCostPrice(BigDecimal costPrice) {
  63. this.costPrice = costPrice;
  64. }
  65. }

被封装的另一个对象信息

  1. package com.huangxin.model;
  2. public class Message {
  3. private int id;
  4. private String name;
  5. private int age;
  6. @Override
  7. public String toString() {
  8. return "Message{" +
  9. "id=" + id +
  10. ", name='" + name + '\'' +
  11. ", age=" + age +
  12. '}';
  13. }
  14. public int getId() {
  15. return id;
  16. }
  17. public void setId(Integer id) {
  18. this.id = id;
  19. }
  20. public String getName() {
  21. return name;
  22. }
  23. public void setName(String name) {
  24. this.name = name;
  25. }
  26. public int getAge() {
  27. return age;
  28. }
  29. public void setAge(Integer age) {
  30. this.age = age;
  31. }
  32. }

映射文件

resultMap中可以设置表中列名和对象属性的对应关系,但是用对应映射需加上另一张表封装的信息,需用association标签封装另一张表的对应关系

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3. PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4. "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <!--接口的全限定名-->
  6. <mapper namespace="com.huangxin.mapper.ProductMapper">
  7. <resultMap id="product" type="com.huangxin.model.Product">
  8. <id column="id" property="id"></id>
  9. <result column="costPrice" property="costPrice"></result>
  10. <result column="brand" property="brand"></result>
  11. <result column="supplier" property="supplier"></result>
  12. <result column="salePrice" property="salePrice"></result>
  13. <result column="productName" property="productName"></result>
  14. <association property="message" javaType="com.huangxin.model.Message">
  15. <id column="id" property="id"></id>
  16. <result column="name" property="name"></result>
  17. <result column="age" property="age"></result>
  18. </association>
  19. </resultMap>
  20. <select id="getById" parameterType="java.lang.Integer" resultMap="product">
  21. SELECT * FROM product a,message b WHERE a.id=#{b.id}
  22. </select>
  23. </mapper>

注意:namespace的值一定要为对象的全限定名,否则无法使用动态代理,且报错

Junit测试

  1. package com.huangxin.mapper;
  2. import org.apache.ibatis.io.Resources;
  3. import org.apache.ibatis.session.SqlSession;
  4. import org.apache.ibatis.session.SqlSessionFactory;
  5. import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  6. import org.junit.Before;
  7. import org.junit.Test;
  8. import java.io.IOException;
  9. import java.io.InputStream;
  10. public class ProductMapperTest {
  11. private SqlSessionFactory factory;
  12. /**
  13. * 初始化SqlSession工厂
  14. */
  15. @Before
  16. public void init() throws IOException {
  17. //将全局配置文件读取
  18. InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
  19. factory = new SqlSessionFactoryBuilder().build(in);
  20. }
  21. @Test
  22. public void getById() {
  23. SqlSession sqlSession = factory.openSession();
  24. ProductMapper mapper = sqlSession.getMapper(ProductMapper.class);
  25. System.out.println(mapper.getById(2));
  26. sqlSession.close();
  27. }
  28. }

对应的控制台输出为

  1. 2018-12-29 21:09:00 [DEBUG] PooledDataSource forcefully closed/removed all connections.
  2. 2018-12-29 21:09:00 [DEBUG] PooledDataSource forcefully closed/removed all connections.
  3. 2018-12-29 21:09:00 [DEBUG] PooledDataSource forcefully closed/removed all connections.
  4. 2018-12-29 21:09:00 [DEBUG] PooledDataSource forcefully closed/removed all connections.
  5. 2018-12-29 21:09:00 [DEBUG] Opening JDBC Connection
  6. 2018-12-29 21:09:01 [DEBUG] Created connection 1885922916.
  7. 2018-12-29 21:09:01 [DEBUG] Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7068e664]
  8. 2018-12-29 21:09:01 [DEBUG] > Preparing: SELECT * FROM product a,message b WHERE a.id=?
  9. 2018-12-29 21:09:01 [DEBUG] > Parameters: 2(Integer)
  10. 2018-12-29 21:09:01 [DEBUG] < Total: 4
  11. [Product{id=2, productName='椅子', salePrice=100.00, supplier='未知', brand='未知', costPrice=50.00, message=Message{id=2, name='354', age=45345}}]
  12. 2018-12-29 21:09:01 [DEBUG] Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7068e664]
  13. 2018-12-29 21:09:01 [DEBUG] Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@7068e664]
  14. 2018-12-29 21:09:01 [DEBUG] Returned connection 1885922916 to pool.

隔开的则为控制台输出的信息

一对多映射

配置接口

  1. import com.huangxin.order.model.User;
  2. public interface OrderMapper {
  3. /**
  4. * 查询符合信息的数据
  5. * @return id
  6. */
  7. User getAll(Integer id);
  8. }

例如:一个用户可以有多个商品订单,这种关系为一对多

配置Mapper.xml文件

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3. PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4. "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <!--接口的全限定名-->
  6. <mapper namespace="com.huangxin.order.mapper.OrderMapper">
  7. <!--Java对象的类型-->
  8. <!--<resultMap id="名称随意" type="需要与数据库映射的model对象">-->
  9. <!--&lt;!&ndash;id为主键,result为普通列名称&ndash;&gt;-->
  10. <!--<id column="数据库列的名称" property="对象属性的名称"></id>-->
  11. <!--<result column="普通列的名称" property="对象属性的名称"></result>-->
  12. <!--</resultMap>-->
  13. <resultMap id="user" type="com.huangxin.order.model.User">
  14. <id column="id" property="id"></id>
  15. <result column="user_name" property="userName"></result>
  16. <result column="user_telephone" property="userTelephone"></result>
  17. <result column="user_sex" property="userSex"></result>
  18. <collection property="orders" ofType="com.huangxin.order.model.Orders">
  19. <id column="oid" property="id"></id>
  20. <result column="user_id" property="userId"></result>
  21. <result column="create_time" property="createTime"></result>
  22. </collection>
  23. </resultMap>
  24. <select id="getAll" resultMap="user">
  25. SELECT
  26. user.id,user.user_name,user.user_telephone,user.user_sex,
  27. orders.oid,orders.user_id,orders.create_time
  28. FROM user ,orders WHERE user.id=orders.user_id AND user_id=5;
  29. </select>
  30. </mapper>

注意:

1) collection和association应该分场合
2) collection应用于 一对多 映射
3) association应用于 一对一 映射
4) collection中的property属性集合的命名,ofType是集合中存放对象的数据类型

JavaBean多了对另一个表单储存的集合

配置User.java文件

  1. import java.util.List;
  2. public class User {
  3. private Integer id;
  4. private String userName;
  5. private String userTelephone;
  6. private String userSex;
  7. //这里表示对另一张表的查询结果,反映出一对多
  8. "private List<Orders> orders;"
  9. @Override
  10. public String toString() {
  11. return "User{" +
  12. "id=" + id +
  13. ", userName='" + userName + '\'' +
  14. ", userTelephone='" + userTelephone + '\'' +
  15. ", userSex='" + userSex + '\'' +
  16. ", orders=" + orders +
  17. '}';
  18. }
  19. public List<Orders> getOrders() {
  20. return orders;
  21. }
  22. public void setOrders(List<Orders> orders) {
  23. this.orders = orders;
  24. }
  25. public Integer getId() {
  26. return id;
  27. }
  28. public void setId(Integer id) {
  29. this.id = id;
  30. }
  31. public String getUserName() {
  32. return userName;
  33. }
  34. public void setUserName(String userName) {
  35. this.userName = userName;
  36. }
  37. public String getUserTelephone() {
  38. return userTelephone;
  39. }
  40. public void setUserTelephone(String userTelephone) {
  41. this.userTelephone = userTelephone;
  42. }
  43. public String getUserSex() {
  44. return userSex;
  45. }
  46. public void setUserSex(String userSex) {
  47. this.userSex = userSex;
  48. }
  49. }

Junit

  1. import com.huangxin.order.model.User;
  2. import org.apache.ibatis.io.Resources;
  3. import org.apache.ibatis.session.SqlSession;
  4. import org.apache.ibatis.session.SqlSessionFactory;
  5. import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  6. import org.junit.Before;
  7. import org.junit.Test;
  8. import java.io.IOException;
  9. import java.io.InputStream;
  10. public class OrderMapperTest {
  11. private SqlSessionFactory factory;
  12. /**
  13. * 初始化SqlSession工厂
  14. */
  15. @Before
  16. public void init() throws IOException {
  17. //将全局配置文件读取
  18. InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
  19. factory = new SqlSessionFactoryBuilder().build(in);
  20. }
  21. @Test
  22. public void getAll() {
  23. SqlSession sqlSession = factory.openSession();
  24. OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
  25. User all = mapper.getAll();
  26. System.out.println(all);
  27. }
  28. }

控制台输出结果为

  1. D:\Java\bin\java.exe -ea -Didea.test.cyclic.buffer.size=1048576 -javaagent:D:\IntelliJ\lib\idea_rt.jar=2259:D:\IntelliJ\bin -Dfile.encoding=UTF-8 -classpath D:\IntelliJ\lib\idea_rt.jar;D:\IntelliJ\plugins\junit\lib\junit-rt.jar;D:\IntelliJ\plugins\junit\lib\junit5-rt.jar;D:\mybatis04\target\test-classes;D:\mybatis04\target\classes;C:\Users\Administrator\.m2\repository\log4j\log4j\1.2.17\log4j-1.2.17.jar;C:\Users\Administrator\.m2\repository\mysql\mysql-connector-java\5.1.46\mysql-connector-java-5.1.46.jar;C:\Users\Administrator\.m2\repository\org\mybatis\mybatis\3.4.6\mybatis-3.4.6.jar;C:\Users\Administrator\.m2\repository\junit\junit\4.12\junit-4.12.jar;C:\Users\Administrator\.m2\repository\org\hamcrest\hamcrest-core\1.3\hamcrest-core-1.3.jar com.intellij.rt.execution.junit.JUnitStarter -ideVersion5 -junit4 com.huangxin.order.mapper.OrderMapperTest,getAll
  2. 2019-01-02 15:49:50 [DEBUG] Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
  3. WARNING: An illegal reflective access operation has occurred
  4. WARNING: Illegal reflective access by org.apache.ibatis.reflection.Reflector (file:/C:/Users/Administrator/.m2/repository/org/mybatis/mybatis/3.4.6/mybatis-3.4.6.jar) to method java.lang.Class.checkPackageAccess(java.lang.SecurityManager,java.lang.ClassLoader,boolean)
  5. WARNING: Please consider reporting this to the maintainers of org.apache.ibatis.reflection.Reflector
  6. WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
  7. WARNING: All illegal access operations will be denied in a future release
  8. 2019-01-02 15:49:50 [DEBUG] PooledDataSource forcefully closed/removed all connections.
  9. 2019-01-02 15:49:50 [DEBUG] PooledDataSource forcefully closed/removed all connections.
  10. 2019-01-02 15:49:50 [DEBUG] PooledDataSource forcefully closed/removed all connections.
  11. 2019-01-02 15:49:50 [DEBUG] PooledDataSource forcefully closed/removed all connections.
  12. 2019-01-02 15:49:50 [DEBUG] Opening JDBC Connection
  13. 2019-01-02 15:49:50 [DEBUG] Created connection 324169305.
  14. 2019-01-02 15:49:50 [DEBUG] Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@13526e59]
  15. 2019-01-02 15:49:50 [DEBUG] > Preparing: SELECT user.id,user.user_name,user.user_telephone,user.user_sex, orders.oid,orders.user_id,orders.create_time FROM user ,orders WHERE user.id=orders.user_id AND user_id=5;
  16. 2019-01-02 15:49:50 [DEBUG] > Parameters:
  17. 2019-01-02 15:49:50 [DEBUG] < Total: 3
  18. User{id=5, userName='小红', userTelephone='1234567890', userSex='女', orders=[Orders{id=7, userId='5', createTime=Tue Jan 01 19:51:03 CST 2019}, Orders{id=8, userId='5', createTime=Tue Jan 01 19:51:05 CST 2019}, Orders{id=9, userId='5', createTime=Tue Jan 01 19:51:07 CST 2019}]}
  19. Process finished with exit code 0

多对多

多对多 是一对多和一对一进行 嵌套 组合

Tags: JavaWeb  

评论区

    2019-07-21 18:53:01

    站长

    欢迎各位评论!


文章评论



给自个选个头像吧!






站点信息

  • 建站时间:   2019-01-31
  • 网站程序:   Tomcat+nginx
  • 文章统计:   44篇文章
  • 标签管理:   标签云
  • 微信公众号:  扫描二维码,联系我