Java的MyBatis框架中实现多表连接查询和查询结果分页

Java的MyBatis框架中实现多表连接查询和查询结果分页实现多表联合查询
还是在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) {
= 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) {
= 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}\r\n", 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();
}
}
在david.mybatis.demo下⾯分别新建相应的操作接⼝：
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();
}
在mapper⽂件夹下新建WebsiteMapper.xml映射⽂件，分别参照上⼀张所说的把增删改查的单表操作配置分别放进去，这样你可以建造⼀点测试数据。

如下
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-////DTD Mapper 3.0//EN"
"/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, siteName, Visitor.Id visitorId,
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>
这⾥今天主要说的就是那个查，现在我们想要查询⽹站的同时分别把相应的访问者信息⼀起拿出来，怎么做呢，⼤家可以参照配置中的query,写下联表查询的SQL，
这⾥主要要注意的是，Website实体与Visit的实体⾥⾯Id与Name这2个属性都是⼀样的，所以为了避免映射出现出错现象，把相应的查询结果列起上不⼀样的别名，这样绑定的时候就可以避免。

假如我像下⾯⼀样配置会得到什么呢?
<select id="query" parameterType="int" resultMap="websiteRs">
select
Website.id, siteName, Visitor.Id,
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>
有⽊有发觉，Visitor的Id也变成2了，这个其实它默认映射了Website的ID，因为SQL语句查询出来的结果2个ID都是变成2了，有⼈会问为什么不是4呢，因为他默认匹配第⼀个如果你把Website.Id与Visit.Id的位置，相互换下就会发现结果⼜神奇的变了
所以需要起个别名避免这种情况，这样你就会发现真相其实只有⼀个就是下⾯的：
⼤家可以看到其实多表处理resultMap的⽅式和单表是⼀致的，也⽆⾮是吧列明与Javabean属性名成对应上去，可以看到在Website的<resultMap>节点⾥⾯前台另外⼀个resultMap，他就是代表Visit实体所需要映射的实体，可以使⽤以下⽅式进⾏关联
<association property="visitor" javaType="Visitor" resultMap="visitorRs" />
这样，⼀个简单的多表联合查询就出来啦~，如果还有更加复杂的查询业务费是在这个基础上些许的变通修改。

分页效果逻辑
下⾯要讲的是关于⼀个业务问题中我们常碰到的分页问题。

在开发web项⽬的时候我们经常会使⽤到列表显⽰，⼀般我们都会⽤⼀些常⽤的列表控件例如，datatables（个⼈感觉⼗分不错），easy ui下⾯的那些封装好的表格控件。

思路：在这些控件⾥要达到分页的效果，⼀般都会传2个参数，第⼀个是表⽰当前页的索引（⼀般从0开始），第⼆个表⽰当前页展⽰多少条业务记录，然后将相应的参数传递给List<T> getList(PagenateArgs args)⽅法，最终实现数据库中的分页时候我们可以使⽤limit关键词（针对mysql）进⾏分页，如果是oracle或者sql server他们都有⾃带的rownum函数可以使⽤。

针对上述思路，⾸先我们需要还是⼀如既往的在demo.mybatis.model下⾯新建⼀个名为PagenateArgs的分页参数实体类与⼀个名为SortDirectionEnum的枚举类，⾥⾯包含当前页⾯索引pageIndex, 当前页展⽰业务记录数pageSize， pageStart属性表⽰从第⼏条开始，（pageStart=pageIndex*pageSize）因为limit关键词⽤法是表⽰【limit 起始条数（不包含），取⼏条】，orderFieldStr排序字
段，orderDirectionStr 排序⽅向，所以具体创建如下：
package david.mybatis.model;
/*
* 分页参数实体类
*/
public class PagenateArgs {
private int pageIndex;
private int pageSize;
private int pageStart;
private String orderFieldStr;
private String orderDirectionStr;
public PagenateArgs() {
// TODO Auto-generated constructor stub
}
public PagenateArgs(int pageIndex, int pageSize, String orderFieldStr, String orderDirectionStr) {
this.pageIndex = pageIndex;
this.pageSize = pageSize;
this.orderFieldStr = orderFieldStr;
this.orderDirectionStr = orderDirectionStr;
pageStart = pageIndex * pageSize;
}
public int getPageIndex() {
return pageIndex;
}
public int getPageStart() {
return pageStart;
}
public int getPageSize() {
return pageSize;
}
public String orderFieldStr() {
return orderFieldStr;
}
public String getOrderDirectionStr() {
return orderDirectionStr;
}
}
package david.mybatis.model;
/*
* 排序枚举
*/
public enum SortDirectionEnum {
/*
* 升序
*/
ASC,
/*
* 降序
*/
DESC
}
完成上⾯的步骤以后我们在IVisitorOperation接⼝类中继续添加⼀个⽅法public List<Visitor> getListByPagenate(PagenateArgs args)，前⼏章中我们其实已经有getList⽅法了，这次的分页其实也就是在这个的基础上稍加改动即可，IVisitorOperation接⼝类改动后如下所⽰：
package david.mybatis.demo;
import java.util.List;
import david.mybatis.model.PagenateArgs;
import david.mybatis.model.Visitor;
import david.mybatis.model.VisitorWithRn;
public interface IVisitorOperation {
/*
* 基础查询
*/
public Visitor basicQuery(int id);
/*
* 添加访问者
*/
public int add(Visitor visitor);
/*
* 删除访问者
*/
public int delete(int id);
/*
* 更新访问者
*/
public int update(Visitor visitor);
/*
* 查询访问者
*/
public Visitor query(int id);
/*
* 查询List
*/
public List<Visitor> getList();
/*
* 分页查询List
*/
public List<Visitor> getListByPagenate(PagenateArgs args);
}
接下来我们就要开始动⼿改动我们的VisitorMapper.xml配置⽂件了，新增⼀个<select>节点id与参数类型参照前⼏章的⽅式配置好，如下此处新增的id就为getListByPagenate，配置好以后如下
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-////DTD Mapper 3.0//EN"
"/dtd/mybatis-3-mapper.dtd">
<mapper namespace="david.mybatis.demo.IVisitorOperation">
<!-- useGeneratedKeys="true"代表是否使⽤⾃增长序列, keyProperty="Id"指定⾃增长列是哪⼀列, parameterType="Visitor"指定IVisitorOperation接⼝类中定义中所传的相应类型 --> <insert id="add" parameterType="Visitor" useGeneratedKeys="true"
keyProperty="Id">
insert into Visitor (Name, Email, Status, CreateTime)
values (#{name}, #{email}, #{status}, #{createTime})
</insert>
<delete id="delete" parameterType="int">
delete from Visitor where
status>0 and id = #{id}
</delete>
<update id="update" parameterType="Visitor">
update Visitor set Name =
#{name}, Email=#{email}, Status=#{status} where id=#{id} and Status>0;
</update>
<select id="query" parameterType="int" resultType="Visitor">
select Id,
Name, Email, Status, CreateTime from visitor where id=#{id} and
Status>0 order by Id
</select>
<select id="basicQuery" parameterType="int" resultType="Visitor">
select *
from visitor where id=#{id} and
Status>0 order by Id
</select>
<select id="getList" resultMap="visitorRs">
<include refid="getListSql" />
</select>
<sql id="getListSql">
select * from Visitor where
status>0
</sql>
<!-- 以下为新增部分⽤来分页，orderBySql这个提取出来是为了后⾯有⽰例复⽤ -->
<resultMap type="Visitor" id="visitorRs">
<id column="Id" property="id" />
<result column="Name" property="name" />
<result column="Email" property="email" />
<result column="Status" property="status" />
<result column="CreateTime" property="createTime" />
</resultMap>
<select id="getListByPagenate" parameterType="PagenateArgs"
resultType="Visitor">
select * from (
<include refid="getListSql" /> <include refid="orderBySql"/>
) t <!-- #{}表⽰参数化输出，${}表⽰直接输出不进⾏任何转义操作，⾃⼰进⾏转移 -->
<if test="pageStart>-1 and pageSize>-1">
limit #{pageStart}, #{pageSize}
</if>
</select>
<sql id="orderBySql">
order by ${orderFieldStr} ${orderDirectionStr}
</sql>
</mapper>
在上⾯你会发现有类似，下图中的配置，这⾥⾯的字段属性都是针对PagenateArgs参数类中的属性名，保持⼀致。

<if test="pageStart>-1 and pageSize>-1">
limit #{pageStart}, #{pageSize}
</if>
在DemoRun类中创建测试⽅法：
/*
* 分页参数
*/
public static void queryVisitorListWithPagenate(int pageIndex, int pageSize, String orderField, String orderDire) {
PagenateArgs args = new PagenateArgs(pageIndex, pageSize, orderField, orderDire);
SqlSession session = MybatisUtils.getSqlSession();
IVisitorOperation vOperation = session.getMapper(IVisitorOperation.class);
List<Visitor> visitors = vOperation.getListByPagenate(args);
for (Visitor visitor : visitors) {
System.out.println(visitor);
}
MybatisUtils.closeSession(session);
MybatisUtils.showMessages(CRUD_Enum.List, visitors.size());
}
DemoRun.queryVisitorListWithPagenate(0, 100, "id", SortDirectionEnum.DESC.toString());
运⾏后下测试结果，先按Id倒序排列，查的Visitor表⼀共有14条记录，
假设我们取在第2页取5条，执⾏下⾯也就是6-10条数据，这样传参数就⾏了
DemoRun.queryVisitorListWithPagenate(1, 5, "id", SortDirectionEnum.DESC.toString());
结果如下：
这样就⾃⼰实现了的⼀个分页逻辑啦~^0^，这⾥需要注意的就是我这边orderFieldStr字段是没有做过任何判断的，理论上要处理下防⽌错误了列名传进去，不过现在⽹上应该有现成封装好的东西，⼤家也可以去google下，这⾥只是给个思路演⽰下怎么⽤mybatis分页。

完成这个后，因为是Mysql的关系所以在查询结果⾥他没有⾃带rownum序列ID，所以查看测试数据是第⼏条的时候可能不明显，不zao急，我们可以⾃⼰动⼿丰⾐⾜⾷改造下上⾯的⽅法，这⾥我重新在model包⾥新建⼀个⼀模⼀样的VisitorWithRn实体⾥⾯多带⼀个rownum参数持久化返回的RownumID，如下：
package david.mybatis.model;
import java.text.SimpleDateFormat;
import java.util.Date;
public class VisitorWithRn {
private int id;
private String name;
private String email;
private int status;
private Date createTime;
private int rownum;
public VisitorWithRn() {
// TODO Auto-generated constructor stub
createTime = new Date();
}
public VisitorWithRn(String name, String email) {
= name;
this.email = email;
this.setStatus(1);
this.createTime = new Date();
}
public int getId() {
return id;
}
public void setName(String name) {
= name;
}
public String getName() {
return name;
}
public void setEmail(String email) {
this.email = email;
}
public String getEmail() {
return email;
}
public Date getCreateTime() {
return createTime;
}
public int getStatus() {
return status;
}
public void setStatus(int status) {
this.status = status;
}
public int getRownum() {
return rownum;
}
public void setRownum(int rownum) {
this.rownum = rownum;
}
@Override
public String toString() {
// TODO Auto-generated method stub
return String.format("{Rownum：%d, Id: %d, Name: %s, CreateTime: %s}", rownum, id, name,
new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(createTime));
}
}
在IVisitorOperation⾥⾯在新建⼀个名为 public List<VisitorWithRn> getListByPagenateWithRn(PagenateArgs args)的⽅法，同样我们需要在
VisitorMapper中配置下相应<select>节点与脚本，此处唯⼀的不同就是需要改下sql脚本，如下：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-////DTD Mapper 3.0//EN"
"/dtd/mybatis-3-mapper.dtd">
<mapper namespace="david.mybatis.demo.IVisitorOperation">
<!-- useGeneratedKeys="true"代表是否使⽤⾃增长序列, keyProperty="Id"指定⾃增长列是哪⼀列, parameterType="Visitor"指定IVisitorOperation接⼝类中定义中所传的相应类型 --> <insert id="add" parameterType="Visitor" useGeneratedKeys="true"
keyProperty="Id">
insert into Visitor (Name, Email, Status, CreateTime)
values (#{name}, #{email}, #{status}, #{createTime})
</insert>
<delete id="delete" parameterType="int">
delete from Visitor where
status>0 and id = #{id}
</delete>
<update id="update" parameterType="Visitor">
update Visitor set Name =
#{name}, Email=#{email}, Status=#{status} where id=#{id} and Status>0;
</update>
<select id="query" parameterType="int" resultType="Visitor">
select Id,
Name, Email, Status, CreateTime from visitor where id=#{id} and
Status>0 order by Id
</select>
<select id="basicQuery" parameterType="int" resultType="Visitor">
select *
from visitor where id=#{id} and
Status>0 order by Id
</select>
<select id="getList" resultMap="visitorRs">
<include refid="getListSql" />
</select>
<sql id="getListSql">
select * from Visitor where
status>0
</sql>
<resultMap type="Visitor" id="visitorRs">
<id column="Id" property="id" />
<result column="Name" property="name" />
<result column="Email" property="email" />
<result column="Status" property="status" />
<result column="CreateTime" property="createTime" />
</resultMap>
<select id="getListByPagenate" parameterType="PagenateArgs"
resultType="Visitor">
select * from (
<include refid="getListSql" /> <include refid="orderBySql"/>
) t <!-- #{}表⽰参数化输出，${}表⽰直接输出不进⾏任何转义操作，⾃⼰进⾏转移 -->
<if test="pageStart>-1 and pageSize>-1">
limit #{pageStart}, #{pageSize}
</if>
</select>
<!--提炼出来为了2个⽰例共⽤下 -->
<sql id="orderBySql">
order by ${orderFieldStr} ${orderDirectionStr}
</sql>
<!-- 带rownum的SQL脚本书写⽅式 -->
<resultMap type="VisitorWithRn" id="visitorWithRnRs">
<id column="Id" property="id" />
<result column="Name" property="name" />
<result column="Email" property="email" />
<result column="Status" property="status" />
<result column="CreateTime" property="createTime" />
<result column="Rownum" property="rownum" />
</resultMap>
<select id="getListByPagenateWithRn" resultMap="visitorWithRnRs">
<!-- #{}表⽰参数化输出，${}表⽰直接输出不进⾏任何转义操作，⾃⼰进⾏转移 -->
select t.Rownum, t.Id, , t.Email, t.Status, t.CreateTime from (<include refid="getListSqlContainsRn" /> <include refid="orderBySql"/>) t
<if test="pageStart>-1 and pageSize>-1">
limit #{pageStart}, #{pageSize}
</if>
</select>
<sql id="getListSqlContainsRn">
select @rownum:=@rownum+1 Rownum,
result.id, , result.email, result.status, result.createTime
FROM (
select @rownum:=0, Visitor.* from Visitor where
status>0) result
</sql>
</mapper>
接下来剩下的就是如刚才在DemoRun下⾯添加测试⽅法，这⾥就不贴图了，完成后你可以看到刚刚的6-10条数据会变成如下。