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字段是没有做过任何判断的,理论上要处理下防止错误了列名传进去,不过现在网上应该有现成封装好的东西,大家也可以去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) { 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条数据会变成如下