Implement multi-table joint query
Or create a new Website class under the david.mybatis.model package to persist data and rewrite the corresponding toString() method to facilitate the use of test programs.
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(); }}Create the corresponding operation interfaces under 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(); }Create a new WebsiteMapper.xml mapping file in the mapper folder, and refer to the previous one to add, delete, modify and check the single table operation configuration, so that you can build some 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, 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>What we mainly talk about today is the search. Now we want to query the website and take out the corresponding visitor information together. How to do it? You can refer to the query in the configuration and write down the SQL for the link table query.
The main thing to note here is that the two properties of Id and Name in the Website entity and Visit entity are the same. Therefore, in order to avoid mapping errors, list the corresponding query results with different alias, so that it can be avoided when binding.
What would I get if I configure it like 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 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> Did you notice that the Visitor's ID has also become 2. In fact, it maps the Website ID by default, because the result of SQL statement querying has both become 2. Some people may ask why it is not 4, because it matches the first one by default. If you switch the locations of Website.Id and Visit.Id, you will find that the result has changed magically again.
So you need to give an alias to avoid this situation, so you will find that there is actually only one truth, which is the following:
You can see that the method of multi-table processing resultMap is the same as that of single tables. It is nothing more than listing the name of the Javabean attribute. You can see that there is another resultMap in the front desk in the <resultMap> node of the Website. It represents the entity that needs to be mapped by the Visit entity. You can use the following method to make associations.
<association property="visitor" javaType="visitor" resultMap="visitorRs" />
The visitor is the visit field name in the Website entity. The name must be consistent. Otherwise, an exception of There is no getter for property named 'XXX' in 'class david.mybatis.model.Website' will be thrown. This has been described in the previous chapters. Of course, if you think it is OK to not nest resultMap, nesting is also due to the fact that this configuration can be used elsewhere, then it will be extracted, which is also an abstract idea. Use the ID and Result in <resultMap> to find the corresponding differences from the official website: http://mybatis.github.io/mybatis-3/sqlmap-xml.html#Result_Maps
In this way, a simple multi-table joint query will come out. If there are more complex query business fees, some modifications will be made based on this.
Pagination effect logic
What we want to talk about is the pagination problem we often encounter in a business problem. When developing web projects, we often use list display. Generally, we use some commonly used list controls, such as datatables (I personally feel very good), and the encapsulated table controls under easy ui.
Idea: To achieve the effect of paging in these controls, we usually pass 2 parameters. The first is to represent the index of the current page (usually starting from 0), the second is to represent how many business records are displayed on the current page, and then pass the corresponding parameters to the List<T> getList(PagenateArgs args) method. When finally implementing paging in the database, we can use the limit keyword (for mysql) for paging. If it is an oracle or SQL server, they all have their own rownum function to use.
To address the above ideas, first of all, we need to create a new page parameter entity class named PagenateArgs under demo.mybatis.model as always, and an enum class named SortDirectionEnum, which contains the current page index pageIndex, the current page display business records pageSize, and the pageStart attribute indicates which item to start from. (pageStart=pageIndex*pageSize) Because the usage of the limit keyword is to represent [limit start number (not included), take a few items], orderFieldStr sorting field, orderDirectionStr sorting direction, so the specific creation is as follows:
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 enum */public enum SortDirectionEnum { /* * Ascending */ ASC, /* * descending */ DESC}After completing the above steps, we continue to add a method public List<Visitor> getListByPagenate(PagenateArgs args) to the IVisitorOperation interface class. In the previous chapters, we actually already have the getList method. This pagination is actually just a little change based on this. After the IVisitorOperation interface class is changed, it is 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(); /* * Pagination query List */ public List<Visitor> getListByPagenate(PagenateArgs args); }Next we have to start to change our VisitorMapper.xml configuration file, add a new <select> node id and parameter type to configure it according to the previous chapters. The new id added here is getListByPagenate. After configuration, 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" means whether to use a self-growth sequence, keyProperty="Id" specifies which column is the self-growth column, parameterType="Visitor" specifies the corresponding type passed in the definition in the IVisitorOperation interface class--> <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> <!-- The following is a new part for pagination. The orderBySql is extracted for example reuse later-> <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 <!-- #{} means parameterized output, ${} means direct output does not perform any escape operations, transfer it yourself --> <if test="pageStart>-1 and pageSize>-1"> limit #{pageStart}, #{pageSize} </if> </select> <sql id="orderBySql"> order by ${orderFieldStr} ${orderDirectionStr} </sql></mapper>You will find similar configurations in the figure below. The field attributes here are all consistent with the attribute names in the PagenateArgs parameter class.
<if test="pageStart>-1 and pageSize>-1"> limit #{pageStart}, #{pageSize}</if>Create a test method in the DemoRun class:
/* * Pagination parameters*/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());
After running, the test results are sorted in reverse order in Ids. There are 14 records in the Visitor table.
Suppose we take 5 pieces on page 2, and execute the following 6-10 pieces of data, so just pass the parameters.
DemoRun.queryVisitorListWithPagenate(1, 5, "id", SortDirectionEnum.DESC.toString());
The results are as follows:
This is a paging logic I have implemented by myself~^0^. What you need to note here is that I have not made any judgment on the orderFieldStr field here. Theoretically, it is necessary to deal with it to prevent the wrong column names from being passed in. However, there should be ready-made encapsulation on the Internet now, so you can also go to Google. Here is just a way to demonstrate how to use mybatis paging.
After completing this, because it is Mysql, it does not have its own rownum sequence ID in the query result. Therefore, it may not be obvious when checking the test data. If you don’t have to hurry, we can do it yourself to make up for food and clothing and transform the above method. Here I will create a completely identical VisitorWithRn entity in the model package and bring an additional rownum ID returned by the persistence of the rownum parameter, 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; } 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)); }}In IVisitorOperation, create a new method called public List<VisitorWithRn> getListByPagenateWithRn(PagenateArgs args). Similarly, we need to configure the corresponding <select> node and script in VisitorMapper. The only difference here is that we need to modify 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" means whether to use a self-growth sequence, keyProperty="Id" specifies which column is the self-growth column, parameterType="Visitor" specifies the corresponding type passed in the definition in the IVisitorOperation interface class--> <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 <!-- #{} means parameterized output, ${} means direct output does not perform any escape operations, transfer it yourself --> <if test="pageStart>-1 and pageSize>-1"> limit #{pageStart}, #{pageSize} </if> </select> <!--Refine it for the two examples to share --> <sql id="orderBySql"> order by ${orderFieldStr} ${orderDirectionStr} </sql> <!-- How to write SQL scripts with rownum--> <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"> <!-- #{} means parameterized output, ${} means direct output does not perform any escape operations, transfer it yourself --> 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> The next thing left is to add the test method under DemoRun just now, so I won't stick the map here. After completion, you can see that the 6-10 pieces of data just now will become as follows