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

mybatis-07-单表一对多查询

2019-02-03 18:14:55【个人日记】78人已围观

简介一张表对应多个数据

单表一对多查询

文件结构

文件系统

配置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. }

另一个Orders.java信息

  1. import java.util.Date;
  2. public class Orders {
  3. private Integer id;
  4. private String userId;
  5. private Date createTime;
  6. @Override
  7. public String toString() {
  8. return "Orders{" +
  9. "id=" + id +
  10. ", userId='" + userId + '\'' +
  11. ", createTime=" + createTime +
  12. '}';
  13. }
  14. public Integer getId() {
  15. return id;
  16. }
  17. public void setId(Integer id) {
  18. this.id = id;
  19. }
  20. public String getUserId() {
  21. return userId;
  22. }
  23. public void setUserId(String userId) {
  24. this.userId = userId;
  25. }
  26. public Date getCreateTime() {
  27. return createTime;
  28. }
  29. public void setCreateTime(Date createTime) {
  30. this.createTime = createTime;
  31. }
  32. }

Orders接口

注意

如果封装另一张表,需要使用List来放入信息

  1. import java.util.List;
  2. public interface OrderMapper {
  3. /**
  4. * 通过id查询
  5. *
  6. * @param orderId
  7. * @return
  8. */
  9. List<Orders> orders(Integer orderId);
  10. }

User接口

  1. package com.huangxin.order.mapper;
  2. import com.huangxin.order.model.User;
  3. public interface UserMapper {
  4. /**
  5. * 通过id查询
  6. *
  7. * @param id
  8. * @return
  9. */
  10. User getById(Integer id);
  11. }

配置OrderMapper.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. <resultMap id="order" type="com.huangxin.order.model.Orders">
  8. <id column="oid" property="id"></id>
  9. <result column="user_id" property="userId"></result>
  10. <result column="create_time" property="createTime"></result>
  11. </resultMap>
  12. <select id="orders" parameterType="java.lang.Integer" resultMap="order">
  13. SELECT * FROM orders WHERE user_id=#{orderId};
  14. </select>
  15. </mapper>

注意命名


重点


配置UserMapper.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.UserMapper">
  7. <resultMap id="user" type="com.huangxin.order.model.User">
  8. <id column="id" property="id"></id>
  9. <result column="user_name" property="userName"></result>
  10. <result column="user_telephone" property="userTelephone"></result>
  11. <result column="user_sex" property="userSex"></result>
  12. <collection property="orders" select="com.huangxin.order.mapper.OrderMapper.orders" column="id">
  13. </collection>
  14. </resultMap>
  15. <select id="getById" parameterType="java.lang.Integer" resultMap="user">
  16. SELECT * FROM user WHERE id=#{id};
  17. </select>
  18. </mapper>

注意

1) 注意看collection便签中的column属性,这里需要与SQL语句中查询的列明相对应, 可以忽略大小写,但是必须要相同 ,像上面的column=" id "与SELECT * FROM user WHERE id =#{id},这两个必须相同,这样才会查询到与id相同的信息
2) select="com.huangxin.order.mapper.OrderMapper.orders"这句是另一张对应表的映射信息, com.huangxin.order.mapper.OrderMapper 为namespace, orders 则为id命名

最后是测试

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

控制台输出为

  1. D:\Java\bin\java.exe -ea -Didea.test.cyclic.buffer.size=1048576 -javaagent:D:\IntelliJ\lib\idea_rt.jar=4751: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:\mybatis05\target\test-classes;D:\mybatis05\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,getById
  2. 2019-01-02 21:57:10 [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 21:57:10 [DEBUG] PooledDataSource forcefully closed/removed all connections.
  9. 2019-01-02 21:57:10 [DEBUG] PooledDataSource forcefully closed/removed all connections.
  10. 2019-01-02 21:57:10 [DEBUG] PooledDataSource forcefully closed/removed all connections.
  11. 2019-01-02 21:57:10 [DEBUG] PooledDataSource forcefully closed/removed all connections.
  12. 2019-01-02 21:57:10 [DEBUG] Opening JDBC Connection
  13. 2019-01-02 21:57:11 [DEBUG] Created connection 37981645.
  14. 2019-01-02 21:57:11 [DEBUG] Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@2438dcd]
  15. 2019-01-02 21:57:11 [DEBUG] > Preparing: SELECT * FROM user WHERE user_sex=?;
  16. 2019-01-02 21:57:11 [DEBUG] > Parameters: 1(Integer)
  17. 2019-01-02 21:57:11 [DEBUG] > Preparing: SELECT * FROM orders WHERE user_id=?;
  18. 2019-01-02 21:57:11 [DEBUG] > Parameters: 1(Integer)
  19. 2019-01-02 21:57:11 [DEBUG] < Total: 2
  20. 2019-01-02 21:57:11 [DEBUG] < Total: 1
  21. User{id=7, userName='1', userTelephone='1', userSex='1', orders=[Orders{id=1, userId='1', createTime=Tue Jan 01 19:49:58 CST 2019}, Orders{id=2, userId='1', createTime=Tue Jan 01 19:50:46 CST 2019}]}
  22. 2019-01-02 21:57:11 [DEBUG] Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@2438dcd]
  23. 2019-01-02 21:57:11 [DEBUG] Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@2438dcd]
  24. 2019-01-02 21:57:11 [DEBUG] Returned connection 37981645 to pool.
  25. Process finished with exit code 0

可以看出User对象中封装这Orders信息,反映出一对多的信息

Tags: JavaWeb  

上一篇: mybatis-05-动态SQL

下一篇: 创建博客心得

评论区

    2019-10-20 05:26:23

    站长

    欢迎各位评论!


文章评论



给自个选个头像吧!






站点信息

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