多表联合查询
在mybatis添加mapper映射，需要按顺序，
<?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>
<!--
引入db的配置文件信息，后面用到的四个连接字符串就可以直接使用 ${}的方式来动态引入
-->
<properties resource="db.properties">
<property name="pass" value="123456"></property>
</properties>
<!--
给当前mybatis项目添加日志功能，该STDOUT_LOGGING值的好处是不用添加第三方jar包就可以有日志的输出
-->
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<typeAliases>
<!--<typeAlias type="com.qfedu.pojo.Order" alias="abc" />-->
<package name="com.qfedu.pojo" />
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${user}"/>
<property name="password" value="${pass}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/qfedu/mapper/OrderMapper.xml"/>
<mapper resource="com/qfedu/mapper/UserMapper.xml"/>
<mapper resource="com/qfedu/mapper/DetailMapper.xml"/>
<mapper resource="com/qfedu/mapper/ProductMapper.xml"/>
<mapper resource="com/qfedu/mapper/TypeMapper.xml"/>
</mappers>
</configuration>
association 是对一的关系，collection 是对多的关系
OrderMapper.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，
每个方法对应自己的sql语句，每个sql语句对应有一个id
整个项目中所有的namespace.id必须是唯一的
-->
<mapper namespace="com.qfedu.pojo.OrderMapper">
<select id="getOrderByOid" resultMap="orderMap">
select * from orders where oid = #{oid}
</select>
<!--<resultMap id="orderMap" type="com.qfedu.pojo.Order">-->
<resultMap id="orderMap" type="order">
<!--
id代表主键，分别设置列和属性的对应关系
-->
<id property="oid" column="oid" ></id>
<!--
result代表普通字段的映射，分别指定列与属性的对应
如果字段名和属性名一致，可以省略
属性名叫做payType，字段名pay_type;
<result column="pay_type" property="payType" />
-->
<result column="price" property="price" />
<result column="addr" property="addr" />
<result column="payType" property="payType" />
<!--
association关联，只要是"对一"的关系都可以使用association，代表关联
property代表Order类中的属性名u
column代表Orders表与Users表之间的关联字段
select代表要使用该查询完成两表的联合查询得出user对象
-->
<association property="u" column="uid" select="com.qfedu.pojo.UserMapper.getUserByUid"></association>
<!--
collection设置集合，只要是"对多"多关系，都可以使用collection，代表集合
column代表orders表与details表之间的关联字段
-->
<!--<collection property="details" column="oid" select="com.qfedu.pojo.DetailMapper.getDetailsByOid" ofType="com.qfedu.pojo.Detail" />-->
<collection property="details" column="oid" select="com.qfedu.pojo.DetailMapper.getDetailsByOid" ofType="detail" />
</resultMap>
</mapper>
UserMapper.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，
每个方法对应自己的sql语句，每个sql语句对应有一个id
整个项目中所有的namespace.id必须是唯一的
-->
<mapper namespace="com.qfedu.pojo.UserMapper">
<!--<select id="getUserByUid" resultType="com.qfedu.pojo.User">-->
<select id="getUserByUid" resultType="User">
select * from users where uid = #{uid}
</select>
</mapper>
DetailMapper.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，
每个方法对应自己的sql语句，每个sql语句对应有一个id
整个项目中所有的namespace.id必须是唯一的
-->
<mapper namespace="com.qfedu.pojo.DetailMapper">
<select id="getDetailsByOid" resultMap="detailMap">
select * from details where oid = #{oid}
</select>
<resultMap id="detailMap" type="com.qfedu.pojo.Detail">
<id column="did" property="did"></id>
<result property="count" column="count" />
<association property="pro" column="pid" select="com.qfedu.pojo.ProductMapper.getProductByPid" />
</resultMap>
</mapper>
ProductMapper.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，
每个方法对应自己的sql语句，每个sql语句对应有一个id
整个项目中所有的namespace.id必须是唯一的
-->
<mapper namespace="com.qfedu.pojo.ProductMapper">
<select id="getProductByPid" resultMap="productMap">
select * from products where pid = #{uid}
</select>
<resultMap id="productMap" type="com.qfedu.pojo.Product">
<id column="pid" property="pid"></id>
<association property="t" column="tid" select="com.qfedu.pojo.TypeMapper.getTypesByTid" />
</resultMap>
</mapper>
TypeMapper.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，
每个方法对应自己的sql语句，每个sql语句对应有一个id
整个项目中所有的namespace.id必须是唯一的
-->
<mapper namespace="com.qfedu.pojo.TypeMapper">
<select id="getTypesByTid" resultType="com.qfedu.pojo.Types">
select * from types where tid = #{tid}
</select>
</mapper>
Order.java
package com.qfedu.pojo;
import java.util.List;
public class Order {
private String oid;
private double price;
private String addr;
private String payType;
private User u;
private List<Detail> details;
@Override
public String toString() {
return "Order{" +
"oid='" + oid + '\'' +
", price=" + price +
", addr='" + addr + '\'' +
", payType='" + payType + '\'' +
", u=" + u +
", details=" + details +
'}';
}
public String getOid() {
return oid;
}
public void setOid(String oid) {
this.oid = oid;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public String getAddr() {
return addr;
}
public void setAddr(String addr) {
this.addr = addr;
}
public String getPayType() {
return payType;
}
public void setPayType(String payType) {
this.payType = payType;
}
public User getU() {
return u;
}
public void setU(User u) {
this.u = u;
}
public List<Detail> getDetails() {
return details;
}
public void setDetails(List<Detail> details) {
this.details = details;
}
}
User.java
package com.qfedu.pojo;
public class User {
private int uid;
private String name;
private String pass;
private String phone;
@Override
public String toString() {
return "User{" +
"uid=" + uid +
", name='" + name + '\'' +
", pass='" + pass + '\'' +
", phone='" + phone + '\'' +
'}';
}
public int getUid() {
return uid;
}
public void setUid(int uid) {
this.uid = uid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPass() {
return pass;
}
public void setPass(String pass) {
this.pass = pass;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
}
Detail.java
package com.qfedu.pojo;
public class Detail {
private String did;
private int count;
private Product pro;
@Override
public String toString() {
return "Detail{" +
"did='" + did + '\'' +
", count=" + count +
", pro=" + pro +
'}';
}
public String getDid() {
return did;
}
public void setDid(String did) {
this.did = did;
}
public int getCount() {
return count;
}
public void setCount(int count) {
this.count = count;
}
public Product getPro() {
return pro;
}
public void setPro(Product pro) {
this.pro = pro;
}
}
Product.java
package com.qfedu.pojo;
public class Product {
private String pid;
private String name;
private String img;
private double price;
private Types t;
@Override
public String toString() {
return "Product{" +
"pid='" + pid + '\'' +
", name='" + name + '\'' +
", img='" + img + '\'' +
", price=" + price +
", t=" + t +
'}';
}
public String getPid() {
return pid;
}
public void setPid(String pid) {
this.pid = pid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getImg() {
return img;
}
public void setImg(String img) {
this.img = img;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public Types getT() {
return t;
}
public void setT(Types t) {
this.t = t;
}
}
Types.java
package com.qfedu.pojo;
public class Types {
private String tid;
private String name;
@Override
public String toString() {
return "Types{" +
"tid=" + tid +
", name='" + name + '\'' +
'}';
}
public String getTid() {
return tid;
}
public void setTid(String tid) {
this.tid = tid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
