mybatis学习笔记（九）一对一关联表查询

工程目录结构：

POM.XML

<project xmlns="http://maven.apache.org/POM/4.0.0" 
		 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
		 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>org.liyb.mybatis.stud</groupId>
  <artifactId>mybatisHelloWorld</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>war</packaging>
  
  <dependencies>
  	<dependency>
    	<groupId>org.mybatis</groupId>
    	<artifactId>mybatis</artifactId>
    	<version>3.4.0</version>
	</dependency>

	<dependency>
    	<groupId>org.mybatis.generator</groupId>
    	<artifactId>mybatis-generator-core</artifactId>
    	<version>1.3.2</version>
	</dependency>
	
	<dependency>
    	<groupId>mysql</groupId>
    	<artifactId>mysql-connector-java</artifactId>
    	<version>5.1.38</version>
	</dependency>
	
  </dependencies>
  
  <build>
  	<plugins>
  		<plugin>
	       	<groupId>org.apache.maven.plugins</groupId>
	       	<artifactId>maven-compiler-plugin</artifactId>
	      		<configuration>
	      			<source>1.7</source>
	      			<target>1.7</target>
	      			<encoding>UTF-8</encoding>
			</configuration>
		</plugin>
		
		<!-- mybatis-generator -->
		<plugin>
        	<groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-maven-plugin</artifactId>
            <version>1.3.2</version>
            	<configuration>
                    <verbose>true</verbose>
                    <overwrite>true</overwrite>
                </configuration>
         </plugin>
         
  	</plugins>
  </build>
</project>

需求

乘客和车票既存在一对一的关系也存在一对多的关系，首先一张车票只能属于一名乘客，这是一个一对一的关系，一名乘客可以拥有多张车票，这是一对多的关系，先来看一对弈的关系。

乘客POJO：

package com.mybaits.onetomany.model;

import java.util.List;

public class Customer {  
    private Integer customerId;  
    private String customerName;  
    private Integer customerTel;  
    private List<Ticket> tickets;//使用一个List来表示车票  
  
    public List<Ticket> getTickets() {  
        return tickets;  
    }  
  
    public void setTickets(List<Ticket> tickets) {  
        this.tickets = tickets;  
    }  
  
    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;  
    }  
  
    @Override  
    public String toString() {  
        return "Customer [customerId=" + customerId + ", customerName="  
                + customerName + ", customerTel=" + customerTel+"]";  
    }  
  
}  

车票POJO：

package com.mybaits.onetomany.model;
 
public class Ticket {
	
    private Integer ticketId;  
    private String ticketAddress;  
    private Integer ticketPrice;  
    private Integer ticketCId;  
    private Customer customer;//使用一个customer来表示顾客  
  
    public Customer getCustomer() {  
        return customer;  
    }  
  
    public void setCustomer(Customer customer) {  
        this.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;  
    }  
  
    @Override  
    public String toString() {  
        return "Ticket [ticketId=" + ticketId + ", ticketAddress="  
                + ticketAddress + ", ticketPrice=" + ticketPrice  
                + ", ticketCId=" + ticketCId + "]";  
    }  
  
}  

TicketMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">  

<mapper namespace="com.mybaits.onetomany.model.TicketMapper">

	<!-- 定义数据库字段与实体对象的映射关系 -->
	<resultMap id="TicketBean" type="Ticket">
		<id column="ticketId" property="ticketId"/>
		<result column="ticketAddress" property="ticketAddress"/>
		<result column="ticketPrice" property="ticketPrice"/>
		<result column="ticketCId" property="ticketCId"/>
		<!-- 一对一关联 -->
		<association property="customer" javaType="Customer">
			<id column="customerId" property="customerId" />
			<result column="customerName" property="customerName"/>
			<result column="customerTel" property="customerTel"/>
		</association>
	</resultMap>
	<!-- 根据id查询ticket，关联的custom被查询出来 -->
	<select id="selectTicketById" parameterType="int" resultMap="TicketBean">
		  select c.*,t.* from t_customer c,t_ticket t 
		  	where c.customerId=t.ticketCId and t.ticketId =#{ticketId}
	</select>
</mapper> 

mybaits配置文件：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration  
	PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
	
<configuration>
	<!-- 别名 -->
	<typeAliases>
		<typeAlias alias="Customer" type="com.mybaits.onetomany.model.Customer"/>
		<typeAlias alias="Ticket" type="com.mybaits.onetomany.model.Ticket"/>
	</typeAliases>
	<!-- 配置数据源信息 -->
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC"/>
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver"/>
				<property name="url" value="jdbc:mysql://localhost:3306/mybatis?characterEncoding=UTF-8"/>
				<property name="username" value="mysql"/>
				<property name="password" value="mysql"/>
			</dataSource>
			
		</environment>
	</environments>
	<mappers>
		<mapper resource="com/mybaits/onetomany/model/TicketMapper.xml" />  
	</mappers>
</configuration> 

TicketMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">  

<mapper namespace="com.mybaits.onetomany.model.TicketMapper">

	<!-- 定义数据库字段与实体对象的映射关系 -->
	<resultMap id="TicketBean" type="Ticket">
		<id column="ticketId" property="ticketId"/>
		<result column="ticketAddress" property="ticketAddress"/>
		<result column="ticketPrice" property="ticketPrice"/>
		<result column="ticketCId" property="ticketCId"/>
		<!-- 一对一关联 -->
		<association property="customer" javaType="Customer">
			<id column="customerId" property="customerId" />
			<result column="customerName" property="customerName"/>
			<result column="customerTel" property="customerTel"/>
		</association>
	</resultMap>
	<!-- 根据id查询ticket，关联的custom被查询出来 -->
	<select id="selectTicketById" parameterType="int" resultMap="TicketBean">
		  select c.*,t.* from t_customer c,t_ticket t 
		  	where c.customerId=t.ticketCId and t.ticketId =#{ticketId}
	</select>
</mapper> 

测试：

package com.mybatis.test;

import java.io.Reader;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.mybaits.onetomany.model.Ticket;

public class Test {

	private static SqlSessionFactory sqlSessionFactory;  
	private static Reader reader;
	private static String resource ="config/mybatis-config.xml";
    static {  
        try {  
            reader = Resources.getResourceAsReader(resource);  
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
    }  

    public static void selectTicketById(int id) {  

    	SqlSession session = null;  
	    try {  
	        session = sqlSessionFactory.openSession();  
	        Ticket ticket = (Ticket) session.selectOne(  
	                "com.mybaits.onetomany.model.TicketMapper.selectTicketById", id);  
	        if (ticket == null)  
	            System.out.println("null");  
	        else {  
	            System.out.println("车票信息："+ticket);  
	            System.out.println("乘客信息："+ticket.getCustomer());  
	        }  
	    } finally {  
	    	session.close();  
	    }  
    }  
	  
	    public static void main(String[] args) {  
	        selectTicketById(1);  
	  
	    }  
}

在写这篇文章的时候，我忽然想了hibernate一个多对多的关联查询，时光飞逝。