實(shí)現(xiàn)多表聯(lián)合查詢(xún)
還是在david.mybatis.model包下面新建一個(gè)Website類(lèi)，用來(lái)持久化數(shù)據(jù)之用，重寫(xiě)下相應(yīng)toString()方法，方便測(cè)試程序之用。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
|
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下面分別新建相應(yīng)的操作接口：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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映射文件，分別參照上一張所說(shuō)的把增刪改查的單表操作配置分別放進(jìn)去，這樣你可以建造一點(diǎn)測(cè)試數(shù)據(jù)。如下
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
|
<?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> |
這里今天主要說(shuō)的就是那個(gè)查，現(xiàn)在我們想要查詢(xún)網(wǎng)站的同時(shí)分別把相應(yīng)的訪問(wèn)者信息一起拿出來(lái)，怎么做呢，大家可以參照配置中的query,寫(xiě)下聯(lián)表查詢(xún)的SQL，
這里主要要注意的是，Website實(shí)體與Visit的實(shí)體里面Id與Name這2個(gè)屬性都是一樣的，所以為了避免映射出現(xiàn)出錯(cuò)現(xiàn)象，把相應(yīng)的查詢(xún)結(jié)果列起上不一樣的別名，這樣綁定的時(shí)候就可以避免。
假如我像下面一樣配置會(huì)得到什么呢?
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
<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> |
有木有發(fā)覺(jué)，Visitor的Id也變成2了，這個(gè)其實(shí)它默認(rèn)映射了Website的ID，因?yàn)镾QL語(yǔ)句查詢(xún)出來(lái)的結(jié)果2個(gè)ID都是變成2了，有人會(huì)問(wèn)為什么不是4呢，因?yàn)樗J(rèn)匹配第一個(gè)如果你把Website.Id與Visit.Id的位置，相互換下就會(huì)發(fā)現(xiàn)結(jié)果又神奇的變了
所以需要起個(gè)別名避免這種情況，這樣你就會(huì)發(fā)現(xiàn)真相其實(shí)只有一個(gè)就是下面的：
大家可以看到其實(shí)多表處理resultMap的方式和單表是一致的，也無(wú)非是吧列明與Javabean屬性名成對(duì)應(yīng)上去，可以看到在Website的<resultMap>節(jié)點(diǎn)里面前臺(tái)另外一個(gè)resultMap，他就是代表Visit實(shí)體所需要映射的實(shí)體，可以使用以下方式進(jìn)行關(guān)聯(lián)
|
1
|
<association property="visitor" javaType="Visitor" resultMap="visitorRs" /> |
其中的visitor就是Website實(shí)體中的visit字段名，必須保證名稱(chēng)一致，否則就會(huì)拋出There is no getter for property named 'XXX' in 'class david.mybatis.model.Website'的異常，這在上幾章已經(jīng)講述了，當(dāng)然如果你覺(jué)得不用嵌套resultMap也行，嵌套也是出于其他地方可以還要用到這個(gè)配置那就提煉出來(lái)的過(guò)程，也是抽象出來(lái)的一種思想。具體使用<resultMap>中的ID與Result可以從官網(wǎng)查找相應(yīng)區(qū)別說(shuō)明：http://mybatis.github.io/mybatis-3/sqlmap-xml.html#Result_Maps
這樣，一個(gè)簡(jiǎn)單的多表聯(lián)合查詢(xún)就出來(lái)啦~，如果還有更加復(fù)雜的查詢(xún)業(yè)務(wù)費(fèi)是在這個(gè)基礎(chǔ)上些許的變通修改。
分頁(yè)效果邏輯
下面要講的是關(guān)于一個(gè)業(yè)務(wù)問(wèn)題中我們常碰到的分頁(yè)問(wèn)題。在開(kāi)發(fā)web項(xiàng)目的時(shí)候我們經(jīng)常會(huì)使用到列表顯示，一般我們都會(huì)用一些常用的列表控件例如，datatables（個(gè)人感覺(jué)十分不錯(cuò)），easy ui下面的那些封裝好的表格控件。
思路：在這些控件里要達(dá)到分頁(yè)的效果，一般都會(huì)傳2個(gè)參數(shù)，第一個(gè)是表示當(dāng)前頁(yè)的索引（一般從0開(kāi)始），第二個(gè)表示當(dāng)前頁(yè)展示多少條業(yè)務(wù)記錄，然后將相應(yīng)的參數(shù)傳遞給List<T> getList(PagenateArgs args)方法，最終實(shí)現(xiàn)數(shù)據(jù)庫(kù)中的分頁(yè)時(shí)候我們可以使用limit關(guān)鍵詞（針對(duì)mysql）進(jìn)行分頁(yè)，如果是oracle或者sql server他們都有自帶的rownum函數(shù)可以使用。
針對(duì)上述思路，首先我們需要還是一如既往的在demo.mybatis.model下面新建一個(gè)名為PagenateArgs的分頁(yè)參數(shù)實(shí)體類(lèi)與一個(gè)名為SortDirectionEnum的枚舉類(lèi)，里面包含當(dāng)前頁(yè)面索引pageIndex, 當(dāng)前頁(yè)展示業(yè)務(wù)記錄數(shù)pageSize， pageStart屬性表示從第幾條開(kāi)始，（pageStart=pageIndex*pageSize）因?yàn)閘imit關(guān)鍵詞用法是表示【limit 起始條數(shù)（不包含），取幾條】，orderFieldStr排序字段，orderDirectionStr 排序方向，所以具體創(chuàng)建如下：
package david.mybatis.model;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
|
/* * 分頁(yè)參數(shù)實(shí)體類(lèi) */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} |
完成上面的步驟以后我們?cè)贗VisitorOperation接口類(lèi)中繼續(xù)添加一個(gè)方法public List<Visitor> getListByPagenate(PagenateArgs args)，前幾章中我們其實(shí)已經(jīng)有g(shù)etList方法了，這次的分頁(yè)其實(shí)也就是在這個(gè)的基礎(chǔ)上稍加改動(dòng)即可，IVisitorOperation接口類(lèi)改動(dòng)后如下所示：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
|
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 { /* * 基礎(chǔ)查詢(xún) */ public Visitor basicQuery(int id); /* * 添加訪問(wèn)者 */ public int add(Visitor visitor); /* * 刪除訪問(wèn)者 */ public int delete(int id); /* * 更新訪問(wèn)者 */ public int update(Visitor visitor); /* * 查詢(xún)?cè)L問(wèn)者 */ public Visitor query(int id); /* * 查詢(xún)List */ public List<Visitor> getList(); /* * 分頁(yè)查詢(xún)List */ public List<Visitor> getListByPagenate(PagenateArgs args); } |
接下來(lái)我們就要開(kāi)始動(dòng)手改動(dòng)我們的VisitorMapper.xml配置文件了，新增一個(gè)<select>節(jié)點(diǎn)id與參數(shù)類(lèi)型參照前幾章的方式配置好，如下此處新增的id就為getListByPagenate，配置好以后如下
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
|
<?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"代表是否使用自增長(zhǎng)序列, keyProperty="Id"指定自增長(zhǎng)列是哪一列, parameterType="Visitor"指定IVisitorOperation接口類(lèi)中定義中所傳的相應(yīng)類(lèi)型 --> <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> <!-- 以下為新增部分用來(lái)分頁(yè)，orderBySql這個(gè)提取出來(lái)是為了后面有示例復(fù)用 --> <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 <!-- #{}表示參數(shù)化輸出，${}表示直接輸出不進(jìn)行任何轉(zhuǎn)義操作，自己進(jìn)行轉(zhuǎn)移 --> <if test="pageStart>-1 and pageSize>-1"> limit #{pageStart}, #{pageSize} </if> </select> <sql id="orderBySql"> order by ${orderFieldStr} ${orderDirectionStr} </sql></mapper> |
在上面你會(huì)發(fā)現(xiàn)有類(lèi)似，下圖中的配置，這里面的字段屬性都是針對(duì)PagenateArgs參數(shù)類(lèi)中的屬性名，保持一致。
|
1
2
3
|
<if test="pageStart>-1 and pageSize>-1"> limit #{pageStart}, #{pageSize}</if> |
在DemoRun類(lèi)中創(chuàng)建測(cè)試方法：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
/* * 分頁(yè)參數(shù) */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());} |
|
1
|
DemoRun.queryVisitorListWithPagenate(0, 100, "id", SortDirectionEnum.DESC.toString()); |
運(yùn)行后下測(cè)試結(jié)果，先按Id倒序排列，查的Visitor表一共有14條記錄，
假設(shè)我們?nèi)≡诘?頁(yè)取5條，執(zhí)行下面也就是6-10條數(shù)據(jù)，這樣傳參數(shù)就行了
|
1
|
DemoRun.queryVisitorListWithPagenate(1, 5, "id", SortDirectionEnum.DESC.toString()); |
結(jié)果如下：
這樣就自己實(shí)現(xiàn)了的一個(gè)分頁(yè)邏輯啦~^0^，這里需要注意的就是我這邊orderFieldStr字段是沒(méi)有做過(guò)任何判斷的，理論上要處理下防止錯(cuò)誤了列名傳進(jìn)去，不過(guò)現(xiàn)在網(wǎng)上應(yīng)該有現(xiàn)成封裝好的東西，大家也可以去google下，這里只是給個(gè)思路演示下怎么用mybatis分頁(yè)。
完成這個(gè)后，因?yàn)槭荕ysql的關(guān)系所以在查詢(xún)結(jié)果里他沒(méi)有自帶rownum序列ID，所以查看測(cè)試數(shù)據(jù)是第幾條的時(shí)候可能不明顯，不zao急，我們可以自己動(dòng)手豐衣足食改造下上面的方法，這里我重新在model包里新建一個(gè)一模一樣的VisitorWithRn實(shí)體里面多帶一個(gè)rownum參數(shù)持久化返回的RownumID，如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
|
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里面在新建一個(gè)名為 public List<VisitorWithRn> getListByPagenateWithRn(PagenateArgs args)的方法，同樣我們需要在VisitorMapper中配置下相應(yīng)<select>節(jié)點(diǎn)與腳本，此處唯一的不同就是需要改下sql腳本，如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
|
<?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"代表是否使用自增長(zhǎng)序列, keyProperty="Id"指定自增長(zhǎng)列是哪一列, parameterType="Visitor"指定IVisitorOperation接口類(lèi)中定義中所傳的相應(yīng)類(lèi)型 --> <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 <!-- #{}表示參數(shù)化輸出，${}表示直接輸出不進(jìn)行任何轉(zhuǎn)義操作，自己進(jìn)行轉(zhuǎn)移 --> <if test="pageStart>-1 and pageSize>-1"> limit #{pageStart}, #{pageSize} </if> </select> <!--提煉出來(lái)為了2個(gè)示例共用下 --> <sql id="orderBySql"> order by ${orderFieldStr} ${orderDirectionStr} </sql> <!-- 帶rownum的SQL腳本書(shū)寫(xiě)方式 --> <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"> <!-- #{}表示參數(shù)化輸出，${}表示直接輸出不進(jìn)行任何轉(zhuǎn)義操作，自己進(jìn)行轉(zhuǎn)移 --> 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> |
接下來(lái)剩下的就是如剛才在DemoRun下面添加測(cè)試方法，這里就不貼圖了，完成后你可以看到剛剛的6-10條數(shù)據(jù)會(huì)變成如下
