多表联合查询
一个根据机场查询航线的例子。有两张表,机场表包含机场信息:机场id、机场名字、机场城市。
航班包含航线信息:航班id、飞机编号、飞行时间、票价、起飞机场id、降落机场id.
需要查询的结果表包含:飞机编号、起飞机场、起飞城市、>降落机场、降落城市、航行时间、票价。
数据库里新建两个表,在新建几条测试数据:
/*
机场信息表
*/
CREATE TABLE airport(
id INT(10) PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
portname VARCHAR(20) COMMENT '机场名字',
cityname VARCHAR(20) COMMENT '城市名字'
)ENGINE=INNODB;
-- 增加数据
INSERT INTO airport VALUES(DEFAULT,'延安机场','延安');
INSERT INTO airport VALUES(DEFAULT,'北京机场','北京');
INSERT INTO airport VALUES(DEFAULT,'西安机场','西安');
/*
飞机信息表
*/
CREATE TABLE airplane(
id INT(10) PRIMARY KEY AUTO_INCREMENT COMMENT '主键id',
airno VARCHAR(20) COMMENT '飞机编号',
times INT(5) COMMENT '飞行时间',
price DOUBLE COMMENT '票价',
takeid INT(10) COMMENT '起飞机场id',
landid INT(10) COMMENT '降落机场id'
)ENGINE=INNODB;
INSERT INTO airplane VALUES(DEFAULT,'海军一号',100,1200,1,2);
INSERT INTO airplane VALUES(DEFAULT,'空军一号',200,1000,3,2);
INSERT INTO airplane VALUES(DEFAULT,'陆军一号',300,1100,2,1);
根据起飞机场id和降落机场id查询需要的结果。(id后边案例分析在具体解释,如何获取起降机场id),
需要用到 LEFT JOIN 关键字,如下示例就可以查出结果了。
SELECT a.*,p.portname takeportname,p.cityname,t.portname,t.cityname
FROM airplane a
LEFT JOIN airport p ON a.takeid=p.id
LEFT JOIN airport t ON t.id=a.landid
WHERE takeid = 1 AND landid = 2
takeportnam是别名,多表查询可能要用到别名,不然结果容易混淆。
mybatis动态 SQL多表联合查询小案例:
案例就是上面这条sql的具体实现,首先建两个表的实体类。然后建映射xml文件:
机场实体类:
public class Airport {
private int id;
private String portName;
private String cityName;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getPortName() {
return portName;
}
public void setPortName(String portName) {
this.portName = portName;
}
public String getCityName() {
return cityName;
}
public void setCityName(String cityName) {
this.cityName = cityName;
}
}
航班实体类,航班实体类中包含机场实体类对象:
public class Airplane {
private int id;
private String airNo;
private int time;
private double price;
private Airport takePort;
private Airport landPort;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getAirNo() {
return airNo;
}
public void setAirNo(String airNo) {
this.airNo = airNo;
}
public int getTime() {
return time;
}
public void setTime(int time) {
this.time = time;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public Airport getTakePort() {
return takePort;
}
public void setTakePort(Airport takePort) {
this.takePort = takePort;
}
public Airport getLandPort() {
return landPort;
}
public void setLandPort(Airport landPort) {
this.landPort = landPort;
}
}
接口及xml配置文件:
public interface AirplaneMapper {
//条件查询接口,param映射。
List<Airplane> selByTakeidLandid(@Param("takeid")int takeid,@Param("landid")int landid);
}
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.*******.mapper.AirplaneMapper">
<!-- //返回结果映射 -->
<resultMap type="airplane" id="mymap">
<!-- 前边数据库,后边属性。 -->
<id column="id" property="id" />
<result column="time" property="time" />
<result column="price" property="price" />
<result column="airno" property="airNo" />
<association property="takePort" javaType="airport">
<id column="takeid" property="id" />
<result column="takecityname" property="cityName" />
<result column="takeportname" property="portName" />
</association>
<association property="landPort" javaType="airport">
<id column="landid" property="id" />
<result column="landcityname" property="cityName" />
<result column="landportname" property="portName" />
</association>
</resultMap>
<select id="selByTakeidLandid" resultMap="mymap">
select a.*,p.id takeid,p.portname takeportname,p.cityname
takecityname,t.id landid,t.portname landportname,t.cityname
landcityname
from airplane a LEFT JOIN airport p on a.takeid=p.id
LEFT
JOIN airport t on t.id=a.landid
<where>
<if test="takeid>0">
and takeid=#{takeid}
</if>
<if test="landid>0">
and landid=#{landid}
</if>
</where>
</select>
</mapper>
上边是返回结果的映射,下边是查询语句。重点是这个查询语句,就是将sql语句写在这里。然后在Service里调用这个方法传入takeid和landid就可以查询出结果了。id可以从机场表中获取,通过下拉菜单获取id。
熟读唐诗三百首,不会作诗也会吟。