Java in the MyBatis framework to implement a multiple table join query and query results paging _java

Implementing a multiple Table joint query

or under the David.mybatis.model package to create a new website class, used to persist data use, rewrite the corresponding ToString () method to facilitate the test program.

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;
  The 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;
  The 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;
  The public void Setvisitorid (int visitorid) {This.visitorid = VisitorID; @Override public String toString () {StringBuilder sb = new StringBuilder (String.Format ("website=> {id:%d, N
    ame:%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 ();

 }
}

Under David.mybatis.demo, create a new operating interface, respectively:

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 ();
  
}

Under the Mapper folder, create a new websitemapper.xml mapping file, respectively, referring to the previous one said to add or delete the single table operation configuration separately, so you can build a little test data. As follows

<?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, Visitor Id, status, Createtime from Website where status>0 </sql> <insert id= "Add" parametertype= "Website" use Generatedkeys= "true" keyproperty= "Id" > INSERT into Website (Name, VisitorID, Status, Createtime) VALUES (#{ Name}, #{visitorid}, #{status}, #{createtime}) </insert> <delete id= "delete" parametertype= "int" > Dele Te 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 We Bsite INNER JOIN Visitor on website.visitorid = visitor.id where website.status>0 and Website.id=#{id} &L t;/select> <resultmap type= "Website" id= "websiters" > <id column= "siteId" property= "id"/> <res Ult column= "SiteName" property= "name"/> <result "column= sitestatus" property= "status"/> <result Mn= "Sitecreatetime" property= "Createtime"/> <association property= "visitor" javatype= "visitor" Visitorrs "/> </resultMap> <resultmap type=" Visitor "id=" Visitorrs "> <id column=" VisitorID "prop erty= "id"/> <result column= "visitorname" property= "name"/> </resultMap> <select id= "GetList" R

 esultmap= "Websitebyvisitoridrs" > <include refid= "getlistsql"/> </select> </mapper>

Here today is mainly said that check, now we want to query the site at the same time the corresponding visitors to the information taken out together, how to do it, you can refer to the configuration of query, write the list of queries SQL,

The main thing to note here is that the website entity and visit entity ID and name of the 2 attributes are the same, so in order to avoid the mapping error phenomenon, the corresponding query results are listed as a different alias, so binding can be avoided.

What would I get if I had the same configuration as the following?

<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< C8/>website.id=#{id}
</select>
<resultmap type= "Website" id= "websiters" >
  <id column= "id" property= "id"/>
  <result column= "SiteName" property= "name"/> <result "column="
  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>

Wood has found that visitor ID also become 2, this is actually the default map of the website ID, because the SQL statement query out of the results of 2 IDs are turned into 2, some people will ask why not 4, Because he defaults to the first one. If you take the position of website.id and visit.id, you will find that the result is magically changed.

So you need to avoid this situation with individual names, so you'll find that the truth is only one of the following:

We can see that in fact, many table processing Resultmap is consistent with a single table, but it is the list of JavaBean attributes with the name of the corresponding up, you can see in the website of the <resultMap> node inside the front desk another resultmap, He is the entity that represents the visit entity that needs to be mapped, and can be associated with the following ways

<association property= "Visitor" javatype= "visitor" resultmap= "Visitorrs"/>

Where the visitor is the visit field name in the website entity, the name must be guaranteed to be the same, otherwise it will throw there is no getter for the property named ' XXX ' in ' class David.mybatis.model . Website ' anomaly, which has been described in the previous chapters, of course, if you feel that you do not have to nest Resultmap Also, nesting is also out of the other place can also use the configuration that is refined out of the process, but also an abstraction out of a thought. Specific use of <resultMap> ID and result can be from the official website to find the corresponding difference description: Http://mybatis.github.io/mybatis-3/sqlmap-xml.html#Result_Maps

In this way, a simple multiple table joint query comes out ~, if there are more complex query business fees are a few modifications on this basis.

Page-effect Logic
here is a question about paging that we often encounter in a business problem. When developing a Web project, we often use the list to display, we usually use some commonly used list controls such as DataTables (personal feeling is very good), easy UI below the packaged table control.

Train of thought: in these controls to achieve the effect of paging, the general will pass 2 parameters, the first is to represent the index of the current page (typically starting from 0), the second represents how many business records the current page shows, and then pass the corresponding parameters to list<t> getlist ( Pagenateargs args) method, the final implementation of the page in the database when we can use the Limit keyword (for MySQL) paging, if it is Oracle or SQL Server they have their own rownum functions can be used.

In view of the above ideas, First of all, we need to, as always, under Demo.mybatis.model, create a new page parameter entity class named Pagenateargs and an enumeration class named Sortdirectionenum that contains the current page index pageindex, The current page shows the number of business records pageSize, Pagestart attribute represents starting from the first few, (pagestart=pageindex*pagesize) because the Limit keyword usage is the "Limit starting bar number (not included), take a few", Orderfieldstr sort fields, orderdirectionstr the sort direction, so you create the following:
Package David.mybatis.model;

* * Pagination Parameter entity class * * 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; /* Sort enumeration/public enum Sortdirectionenum {/* Ascending/ASC, * * Descending/DESC}

 

After completing the above steps, we continue to add a method public list<visitor> getlistbypagenate (Pagenateargs args) in the Ivisitoroperation interface class, In the first few chapters we actually already have getlist method, this page is in fact on this basis slightly changes can, Ivisitoroperation interface class changes as follows:

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 {
   * * * Basic
   query
  /public Visitor basicquery (int id);
   * * Add visitor
   *
  /public int Add (Visitor Visitor);
   * * Delete Visitor
   *
  /public int delete (int id);
   * * Update visitor
   *
  /public int update (Visitor Visitor);
   * * Query visitor
  /public Visitor query (int id);
   * * Query List
   *
  /Public list<visitor> getlist ();
   * * Paging query List *
  /public list<visitor> getlistbypagenate (Pagenateargs args);  
}

Next we are going to start to change our visitormapper.xml configuration file, and add a <select> node ID and parameter type to be configured in the same way as the previous chapters, where the new ID is getlistbypagenate. When configured, the following

<?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" represents whether to use the self-growth sequence. Keyproperty= "Id" specifies the column from which the growth column is, parametertype= "Visitor" specifies the corresponding type that is passed in the definition in the Ivisitoroperation interface class--> <insert id= "Add" Parametertype= "Visitor" usegeneratedkeys= "true" keyproperty= "Id" > INSERT into Visitor (Name, Email, Status, Cre Atetime) VALUES (#{name}, #{email}, #{status}, #{createtime}) </insert> <delete id= "Delete" ParameterType = "int" > Delete from Visitor where status>0 and id = #{id} </delete> <update id= "Update" Paramet Ertype= "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 the ORDER by ID </select> <select id= "Basicque  Ry "parametertype=" int "resulttype=" Visitor "> select * from Visitor where id=#{id} and status>0 Id </select> <select id= "getlist" resultmap= "Visitorrs" > <include refid= "getlistsql"/> </ select> <sql id= "Getlistsql" > select * from Visitor where status>0 </sql> <!--The following is the new section To pagination, orderbysql this is extracted for the following example multiplexing--> <resultmap type= "Visitor" id= "Visitorrs" > <id column= "id" property= "I D "/> <result column=" name "property=" name "/> <result column=" email "property=" email "/> <r Esult column= "status" property= "status"/> <result column= "createtime" property= "Createtime"/>
    map> <select id= "getlistbypagenate" parametertype= "Pagenateargs" resulttype= "Visitor" > select * FROM ( <include refid= "GEtlistsql "/> <include refid=" orderbysql "/>" T <!--#{} represents parameterized output, ${} indicates that direct output does not carry out any escape operations and transfers itself--> <i
  F test= "pagestart>-1 and pagesize>-1" > Limit #{pagestart}, #{pagesize} </if> </select>

 <sql id= "Orderbysql" > Order by ${orderfieldstr} ${orderdirectionstr} </sql> </mapper>

On top you'll notice a similar configuration in the following figure, where the field properties are consistent for the property names in the Pagenateargs parameter class.

<if test= "pagestart>-1 and Pagesize>-1" >
  limit #{pagestart}, #{pagesize}
</if>

To create a test method in the Demorun class:

 * * Paging parameter *
/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, "id", SortDirectionEnum.DESC.toString ());

After running the test results, first by the ID in reverse order, check the visitor table a total of 14 records,

Let's say we take 5 on page 2nd and execute the following 6-10 data, so we can pass the parameters.

Demorun.queryvisitorlistwithpagenate (1, 5, "id", SortDirectionEnum.DESC.toString ());

The results are as follows:

This is the realization of their own a paging logic ~^0^, here need to pay attention to is my side orderfieldstr field is not done any judgment, theoretically to deal with the wrong column name, but now there should be a ready-made package of things, we can go to Google, Here is just a way to demonstrate how to use mybatis pagination.

After this, because it is a MySQL relationship in the query results he did not have the rownum sequence ID, so to see the test data is the number of times may not be obvious, not zao urgent, we can do their own clothing to reform the above method, Here I'm back in the model bag to create an identical visitorwithrn entity inside with a rownum parameter persisted return Rownumid, as follows:

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;
  The public void setrownum (int rownum) {this.rownum = rownum; @Override public String toString () {//TODO auto-generated a stub return String.Format ("{rownum:%d, I D:%d, Name:%s, Createtime:%s} ", rownum, ID, name, new SimpleDateFormat (" Yyyy-mm-dd HH:mm:ss "). Format (createtime
  ));

 }
}

In Ivisitoroperation to create a new method named Public list<visitorwithrn> Getlistbypagenatewithrn (Pagenateargs args), Also we need to configure the corresponding <select> node and script in Visitormapper, the only difference here is that we need to change the SQL script, as follows:

<?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" represents whether to use the self-growth sequence. Keyproperty= "Id" specifies the column from which the growth column is, parametertype= "Visitor" specifies the corresponding type that is passed in the definition in the Ivisitoroperation interface class--> <insert id= "Add" Parametertype= "Visitor" usegeneratedkeys= "true" keyproperty= "Id" > INSERT into Visitor (Name, Email, Status, Cre Atetime) VALUES (#{name}, #{email}, #{status}, #{createtime}) </insert> <delete id= "Delete" ParameterType = "int" > Delete from Visitor where status>0 and id = #{id} </delete> <update id= "Update" Paramet Ertype= "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 the ORDER by ID </select> <select id= "Basicque  Ry "parametertype=" int "resulttype=" Visitor "> select * from Visitor where id=#{id} and status>0 Id </select> <select id= "getlist" resultmap= "Visitorrs" > <include refid= "getlistsql"/> </ select> <sql id= "Getlistsql" > select * from Visitor where status>0 </sql> <resultmap ty Pe= "Visitor" id= "Visitorrs" > <id column= "id" property= "id"/> <result "name" column= "name" property=
    Gt <result column= "Email" property= "email"/> <result column= "status" property= "status"/> <result Umn= "Createtime" property= "Createtime"/> </resultMap> <select id= "Getlistbypagenate" Pagenateargs "resulttype=" Visitor "> select * FROM (<include refid=" Getlistsql "/> <include refid=
   "Orderbysql"/> T <!--#{} represents parameterized output, ${} indicates that direct output does not carry out any escape operations and transfers itself--> <if test= "pagestart>-1 and pagesize>-1" > li MIT #{pagestart}, #{pagesize} </if> </select> <!--distilled for 2 examples--> <sql id= "Orderbysql"
    ; ORDER by ${orderfieldstr} ${orderdirectionstr </sql> <!--SQL script writing with rownum--> <resultmap type= "Vis Itorwithrn "id=" Visitorwithrnrs "> <id column=" id "property=" id "/>" <result "Name" column= "nam E "/> <result column=" email property= "email"/> <result column= "status" property= "status"/> & Lt;result column= "Createtime" property= "Createtime"/> <result column= "rownum" property= "Rownum"/> </re sultmap> <select id= "Getlistbypagenatewithrn" resultmap= "Visitorwithrnrs" > <!--#{} represents parameterized output, ${} indicates that direct output does not Any escape operations, transfer themselves--> select T.rownum, T.id, T.name, T.email, T.status, t.createtime from (<include refid=) getlistsq LCONTAINSRN "/><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.* F

 Rom Visitor where status>0 result </sql> </mapper>

The next thing left is to add a test method just below the Demorun, no mapping here, and when you're done you can see that just 6-10 data will turn into the following