两表多属性联查

两表多属性联查

1. 一方表

@JsonIgnoreProperties(value = { "hibernateLazyInitializer", "handler" })
public class CityEntity implements Serializable{

	  private static final long serialVersionUID = 1L;
	
      // 城市ID
	  private Long id;
      // 城市名
	  private String cityname;

	  /**
	   * 设置：城市ID
	   */
	  public void setId(Long id) {
		  this.id = id;
	  }
	  /**
	   * 获取：城市ID
	   */
	  public Long getId() {
	   	  return id;
	  }
	  /**
	   * 设置：城市名
	   */
	  public void setCityname(String cityname) {
		  this.cityname = cityname;
	  }
	  /**
	   * 获取：城市名
	   */
	  public String getCityname() {
	   	  return cityname;
	  }
	 
	  @Override
	  public String toString() {
		  return  ReflectionToStringBuilder.toString(this);
	  }
}

2. 多方表（关联一方表）

@JsonIgnoreProperties(value = { "hibernateLazyInitializer", "handler" })
public class FlightEntity implements Serializable{

	  private static final long serialVersionUID = 1L;
	
      //航班ID
	  private Long id;
      //航班编号
	  private String flightno;
      //起飞城市ID
	  private Long departurecity;
      //起飞时间
      @JsonFormat(pattern = "yyyy/MM/dd HH:mm:ss" ,timezone = "GMT+8")
	  private Date departuretime;
      //到达城市ID
	  private Long arrivalcity;
      //到达时间
      @JsonFormat(pattern = "yyyy/MM/dd HH:mm:ss" ,timezone = "GMT+8")
	  private Date arrivaltime;

	 // 关联城市表 起飞城市
      private CityEntity startCity;
	// 关联城市表 到达城市
      private CityEntity endCity;

	  /**
	   * 设置：航班ID
	   */
	  public void setId(Long id) {
		  this.id = id;
	  }
	  /**
	   * 获取：航班ID
	   */
	  public Long getId() {
	   	  return id;
	  }
	  /**
	   * 设置：航班编号
	   */
	  public void setFlightno(String flightno) {
		  this.flightno = flightno;
	  }
	  /**
	   * 获取：航班编号
	   */
	  public String getFlightno() {
	   	  return flightno;
	  }
	  /**
	   * 设置：起飞城市ID
	   */
	  public void setDeparturecity(Long departurecity) {
		  this.departurecity = departurecity;
	  }
	  /**
	   * 获取：起飞城市ID
	   */
	  public Long getDeparturecity() {
	   	  return departurecity;
	  }
	  /**
	   * 设置：起飞时间
	   */
	  public void setDeparturetime(Date departuretime) {
		  this.departuretime = departuretime;
	  }
	  /**
	   * 获取：起飞时间
	   */
	  public Date getDeparturetime() {
	   	  return departuretime;
	  }
	  /**
	   * 设置：到达城市ID
	   */
	  public void setArrivalcity(Long arrivalcity) {
		  this.arrivalcity = arrivalcity;
	  }
	  /**
	   * 获取：到达城市ID
	   */
	  public Long getArrivalcity() {
	   	  return arrivalcity;
	  }
	  /**
	   * 设置：到达时间
	   */
	  public void setArrivaltime(Date arrivaltime) {
		  this.arrivaltime = arrivaltime;
	  }
	  /**
	   * 获取：到达时间
	   */
	  public Date getArrivaltime() {
	   	  return arrivaltime;
	  }
	  	 
	  @Override
	  public String toString() {
		  return  ReflectionToStringBuilder.toString(this);
	  }

	public CityEntity getStartCity() {
		return startCity;
	}

	public void setStartCity(CityEntity startCity) {
		this.startCity = startCity;
	}

	public CityEntity getEndCity() {
		return endCity;
	}

	public void setEndCity(CityEntity endCity) {
		this.endCity = endCity;
	}
}

3. 多方mapper映射文件配置

	<!--自定义结果集-->
    <resultMap type="FlightEntity" id="flightMap">
        <result property="id" column="id"/>
        <result property="flightno" column="flightNo"/>
        <result property="departurecity" column="departureCity"/>
        <result property="departuretime" column="departureTime"/>
        <result property="arrivalcity" column="arrivalCity"/>
        <result property="arrivaltime" column="arrivalTime"/>
    </resultMap>

    <!--配置嵌套查询-->
    <resultMap id="fightSelCityMap" type="FlightEntity" extends="flightMap">
        <!--查询起点城市-->
        <association property="startCity" column="departureCity" select="com.java.zd.provider.mapper.CityMapper.queryObjectById"/>
        <!--查询到达城市-->
        <association property="endCity" column="arrivalCity" select="com.java.zd.provider.mapper.CityMapper.queryObjectById"/>
    </resultMap>
    
    <!--要查询的字段-->  
  <sql id="sel_FlightColumns_List" > 
  	  id ,        
      flightNo  ,
      departureCity  ,
      departureTime  ,
      arrivalCity  ,
      arrivalTime
    </sql>
    
    <!--根据条件（无条件）查询多个对象-->
	<select id="queryManyByPramas" resultMap="flightMap">
		select  <include refid="sel_FlightColumns_List" />  from flight <include refid="sel_FlightPramas_List" />
	</select>
	
	<!--根据条件（无条件）分页查询-->
	<select id="queryListByPramas" resultMap="fightSelCityMap">
		select  <include refid="sel_FlightColumns_List" />  from flight <include refid="sel_FlightPramas_List" /> order by id desc
	</select>

4. 一方mapper配置文件

	<!--自定义结果集-->
    <resultMap type="CityEntity" id="cityMap">
        <result property="id" column="id"/>
        <result property="cityname" column="cityName"/>
    </resultMap>
  
   <!--要查询的字段-->  
   <sql id="sel_CityColumns_List" > 
  	  id ,        
      cityName
   </sql>
   
   <!--根据主键id查询单个对象-->
	<select id="queryObjectById" resultMap="cityMap">
		select  <include refid="sel_CityColumns_List" />  from city where id = #{id}
	</select>

5. 调用

XX.startCity.cityname
XX.endCity.cityname