一对多关联映射

前提

以顾客和车票为例
一个顾客可以有多张车票
一张车票只能对应一个顾客

• 创建需要的数据表

  DROP TABLE IF EXISTS t_customer;
  CREATE TABLE t_customer(
  customerId INT PRIMARY KEY AUTO_INCREMENT,
  customerName VARCHAR(20) NOT NULL,
  customerTel INT NOT NULL
  )ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  DROP TABLE IF EXISTS t_ticket;
  CREATE TABLE t_ticket(
  ticketId INT PRIMARY KEY  AUTO_INCREMENT,
  ticketAddress VARCHAR(50) NOT NULL,
  ticketPrice INT NOT NULL,
  ticketCId INT NOT NULL
  )ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  insert into t_customer values(1,'小王',188832765);
  insert into t_customer values(2,'天天',345654635);
  insert into t_customer values(3,'阿大',123345566);
  
  insert into  t_ticket values(1,'武汉到重庆',100,1);
  insert into  t_ticket values(2,'北京到上海',200,1);
  insert into  t_ticket values(3,'深圳到广州',50,1);
  

• 对应的 POJO JAVA 类

• 顾客类

      public class Customer {
  
          private Integer customerId;  
          private String customerName;  
          private Integer customerTel;  
          private List<Ticket> tickets;//使用一个List来表示车票
  
          public Integer getCustomerId() {
              return customerId;
          }
          public void setCustomerId(Integer customerId) {
              this.customerId = customerId;
          }
          public String getCustomerName() {
              return customerName;
          }
          public void setCustomerName(String customerName) {
              this.customerName = customerName;
          }
          public Integer getCustomerTel() {
              return customerTel;
          }
          public void setCustomerTel(Integer customerTel) {
              this.customerTel = customerTel;
          }
          public List<Ticket> getTickets() {
              return tickets;
          }
          public void setTickets(List<Ticket> tickets) {
              this.tickets = tickets;
          }
  
          @Override  
          public String toString() {  
              return "Customer [customerId=" + customerId + ", customerName="  
                      + customerName + ", customerTel=" + customerTel+",tickets.size() = "+ tickets.size()+"]";  
          }
  
      }
  

  • 车票类

    public class Ticket {
    
        private Integer ticketId;  
        private String ticketAddress;  
        private Integer ticketPrice;  
        private Integer ticketCId;  
        private Customer customer;//使用一个customer来表示顾客
    
        public Integer getTicketId() {
            return ticketId;
        }
        public void setTicketId(Integer ticketId) {
            this.ticketId = ticketId;
        }
        public String getTicketAddress() {
            return ticketAddress;
        }
        public void setTicketAddress(String ticketAddress) {
            this.ticketAddress = ticketAddress;
        }
        public Integer getTicketPrice() {
            return ticketPrice;
        }
        public void setTicketPrice(Integer ticketPrice) {
            this.ticketPrice = ticketPrice;
        }
        public Integer getTicketCId() {
            return ticketCId;
        }
        public void setTicketCId(Integer ticketCId) {
            this.ticketCId = ticketCId;
        }
        public Customer getCustomer() {
            return customer;
        }
        public void setCustomer(Customer customer) {
            this.customer = customer;
        }
    
        @Override  
        public String toString() {  
            return "Ticket [ticketId=" + ticketId + ", ticketAddress="  
                    + ticketAddress + ", ticketPrice=" + ticketPrice  
                    + ", ticketCId=" + ticketCId + "]";  
        }
    
    }
    

• 对应的xml映射文件配置

  <mapper namespace="com.sun.useMyBatis3.useMyBatis3.model.impl.CustomerImpl">
  
      <!-- 定义一个查询结果映射的类型 -->
      <resultMap type="Customer" id="customerBean">   
          <id column="customerId" property="customerId"/>
          <result column="customerName" property="customerName"/>  
          <result column="customerTel"  property="customerTel"/>
  
          <!-- 映射一对多属性 -->
          <collection property="tickets" ofType="Ticket">
              <id column="ticketId" property="ticketId"/>
              <result column="ticketAddress" property="ticketAddress"/>
              <result column="ticketPrice" property="ticketPrice"/>
              <result column="ticketCId" property="ticketCId"/>
          </collection>
      </resultMap>
  
      <!-- 根据id查询Person, 关联将Orders查询出来 -->  
      <select id="selectCustomerByName" parameterType="string" resultMap="customerBean">  
          select c.*,t.* from t_customer c,t_ticket t  where  c.customerId=t.ticketCId and c.customerName =#{customerName};
      </select>
  </mapper>
  

• 对应的接口方法

  public interface CustomerImpl {
      public Customer selectCustomerByName(String customerName);
  }
  

• 测试

  public class Test {
  
      private static SqlSessionFactory sqlSessionFactory;
      private static Reader reader;
  
      static {
          try{
              //加载配置文件中的配置
              reader = Resources.getResourceAsReader("Configuration.xml");
              //初始SqlSession 工厂类
              sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
          } catch (Exception e) {
              e.printStackTrace();
          }
      }
  
      public static SqlSessionFactory getSqlSessionFactory(){
          return sqlSessionFactory;
      }
  
      public static void main(String [] args) {
          SqlSession sqlSession = getSqlSessionFactory().openSession();
          try{
              //测试一对多
              CustomerImpl customerImpl = sqlSession.getMapper(CustomerImpl.class);
              Customer customer = customerImpl.selectCustomerByName("小王");
              System.out.println(customer);
          } finally {
              sqlSession.close();
          }
      }
  }