Java的MyBatis框架中完成多表銜接查詢和查詢成果分頁。本站提示廣大學習愛好者：（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) {
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}\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 "-//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>
這裡明天重要說的就是誰人查，如今我們想要查詢網站的同時分離把響應的拜訪者信息一路拿出來，怎樣做呢，年夜家可以參照設置裝備擺設中的query,寫下聯表查詢的SQL，
這裡重要要留意的是，Website實體與Visit的實體外面Id與Name這2個屬性都是一樣的，所認為了防止映照湧現失足景象，把響應的查詢成果列起上紛歧樣的別號，如許綁定的時刻便可以免。
假設我像上面一樣設置裝備擺設會獲得甚麼呢?
<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>
有木有覺察，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" />
個中的visitor就是Website實體中的visit字段名，必需包管稱號分歧，不然就會拋出There is no getter for property named 'XXX' in 'class david.mybatis.model.Website'的異常，這在上幾章曾經講述了，固然假如你認為不消嵌套resultMap也行，嵌套也是出於其他處所可以還要用到這個設置裝備擺設那就提煉出來的進程，也是籠統出來的一種思惟。詳細應用<resultMap>中的ID與Result可以從官網查找響應差別解釋：http://mybatis.github.io/mybatis-3/sqlmap-xml.html#Result_Maps
如許，一個簡略的多表結合查詢就出來啦~，假如還有加倍龐雜的查詢營業費是在這個基本上些許的變通修正。
分頁後果邏輯
上面要講的是關於一個營業成績中我們常碰著的分頁成績。在開辟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 "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/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字段是沒有做過任何斷定的，實際上要處置下避免毛病了列名傳出來，不外如今網上應當有現成封裝好的器械，年夜家也能夠去谷歌下，這裡只是給個思緒演示下怎樣用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) {
this.name = name;
this.email = email;
this.setStatus(1);
this.createTime = new Date();
}
public int getId() {
return id;
}
public void setName(String name) {
this.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 "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/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.Name, 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.name, result.email, result.status, result.createTime
FROM (
select @rownum:=0, Visitor.* from Visitor where
status>0) result
</sql>
</mapper>
接上去剩下的就是如適才在DemoRun上面添加測試辦法，這裡就不貼圖了，完成後你可以看到方才的6-10條數據會釀成以下
