一步步学Mybatis-实现多表联合查询（4）

　　上一章节中我们已经完成了对单表的CRUD操作，接下来今天这一讲讲述的是关于Mybatis在多表查询时候的应用，毕竟实际业务中也是多表的联合查询比较多嘛~

还记得最一开始我们新建过一张Website表吗，在那张表里有个VisitorId字段，表示访问者访问过哪些网站，现在我们先按照上一张中的要求把关于Website的JavaBean实体先建立出来。

还是在david.mybatis.model包下面新建一个Website类，用来持久化数据之用，重写下相应toString()方法，方便测试程序之用。

package david.mybatis.model;

import java.text.SimpleDateFormat;
import java.util.Date;

public class Website {
    private int id;
    private String name;
    private int visitorId;
    private int status;
    private Date createTime;
    private Visitor visitor;

    public Website() {
        // TODO Auto-generated constructor stub
        createTime = new Date();
        visitor = new Visitor();
    }

    public Website(String name, int visitorId) {
        this.name = name;
        this.visitorId = visitorId;
        visitor = new Visitor();
        status = 1;
        createTime = new Date();
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public Visitor getVisitor() {
        return visitor;
    }

    public void setVisitor(Visitor visitor) {
        this.visitor = visitor;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getStatus() {
        return status;
    }

    public void setStatus(int status) {
        this.status = status;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public int getVisitorId() {
        int id = 0;
        if (visitor == null)
            id = visitorId;
        else
            id = visitor.getId();
        return id;
    }

    public void setVisitorId(int visitorId) {
        this.visitorId = visitorId;
    }

    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder(String.format("Website=> {Id：%d, Name：%s, CreateTime：%s}
", id, name,
                new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(createTime)));
        if (visitor != null)
            sb.append(String.format("Visitor=> %s", visitor.toString()));
        return sb.toString();
    }
}

Website实体类

package david.mybatis.demo; import java.util.List; import david.mybatis.model.Website; public interface IWebsiteOperation { public int add(Website website); public int delete(int id); public int update(Website website); public Website query(int id); public List<Website> getList(); }

<?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="david.mybatis.demo.IWebsiteOperation"> <sql id="getListSql"> select id, name, VisitorId, status, createTime from Website where status>0 </sql> <insert id="add" parameterType="Website" useGeneratedKeys="true" keyProperty="Id"> insert into Website (Name, VisitorId, Status, CreateTime) values (#{name}, #{visitorId}, #{status}, #{createTime}) </insert> <delete id="delete" parameterType="int"> delete from website where status>0 and id = #{id} </delete> <update id="update" parameterType="Website"> update website set name=#{name} where status>0 and id=#{id} </update> <select id="query" parameterType="int" resultMap="websiteRs"> select Website.id siteId, Website.name siteName, Visitor.Id visitorId, Visitor.name visitorName, Website.status siteStatus, Website.createtime siteCreateTime from Website inner join Visitor on Website.visitorid = Visitor.id where Website.status>0 and Website.id=#{id} </select> <resultMap type="Website" id="websiteRs"> <id column="siteId" property="id" /> <result column="siteName" property="name" /> <result column="siteStatus" property="status" /> <result column="siteCreateTime" property="createTime" /> <association property="visitor" javaType="Visitor" resultMap="visitorRs" /> </resultMap> <resultMap type="Visitor" id="visitorRs"> <id column="visitorId" property="id" /> <result column="visitorName" property="name" /> </resultMap> <select id="getList" resultMap="websiteByVisitorIdRs"> <include refid="getListSql" /> </select> </mapper>

<select id="query" parameterType="int" resultMap="websiteRs"> select Website.id, Website.name siteName, Visitor.Id, Visitor.name visitorName, Website.status siteStatus, Website.createtime siteCreateTime from Website inner join Visitor on Website.visitorid = Visitor.id where Website.status>0 and Website.id=#{id} </select> <resultMap type="Website" id="websiteRs"> <id column="id" property="id" /> <result column="siteName" property="name" /> <result column="siteStatus" property="status" /> <result column="siteCreateTime" property="createTime" /> <association property="visitor" javaType="Visitor" resultMap="visitorRs" /> </resultMap> <resultMap type="Visitor" id="visitorRs"> <id column="id" property="id" /> <result column="visitorName" property="name" /> </resultMap>

一步步学Mybatis-实现多表联合查询（4）

相关推荐