MyBatis query related to advanced query

3 Related queries

Before making a query, modify several configurations first. Mapper.xml is specified in the Mybatis-config.xml, then each additional mapper we add a configuration, very troublesome. In order to simplify the configuration. The Mapper interface and the mapper.xml need to be placed under the same file, and the interface and the XML file are named consistent. Automatic scanning using mybatis:. Thus, when we add an interface, the interface and the corresponding XML file are created directly:

<mappers>        <!--<mapper resource= "Com.test.mapper.dao/authormapper.xml"/>-->        < Package Name= "Com.test.mapper.dao"/></mappers>

　　

3.1 Prepare

Add a table blog:

CREATE TABLE ' blog ' (  ' id ' int (one) not null auto_increment,  ' name ' varchar (255) DEFAULT NULL,  ' author_id ' int (one) default NULL,  PRIMARY KEY (' id ')) engine=innodb auto_increment=9 default Charset=utf8;

To create an entity class Com.test.mapper.model.Blog:

Package com.test.mapper.model;/** * Created by Miaorf on 2016/7/20. */public class Blog {    private Integer ID;    private String name;    Private Author Author;    Public Integer getId () {        return ID;    }    public void SetId (Integer id) {        this.id = ID;    }    Public String GetName () {        return name;    }    public void SetName (String name) {        this.name = name;    }    Public Author Getauthor () {        return Author;    }    public void Setauthor (Author Author) {        this.author = Author;    }    @Override public    String toString () {        return "blog{" +                "id=" + ID +                ", name= ' + name + ' \ ' +                ", Author= "+ author +                '} ';    }}

Create interface: Com/test/mapper/dao/blogmapper.xml

Package Com.test.mapper.dao;import com.test.mapper.model.blog;import java.util.list;/** * Created by Miaorf on 2016/7/ */public interface Blogmapper {    list<blog> selectblog (Integer ID);}

Create Xml:com/test/mapper/dao/blogmapper.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= "Com.test.mapper.dao.BlogMapper" >    <resultmap id= "Blogresult" type= "blog" >        <association property= "author" column= "author_id" javatype= "Author" select= " Com.test.mapper.dao.AuthorMapper.selectAuthorById "/>    </resultMap>    <select id=" Selectblog " resultmap= "Blogresult" >        select * FROM blog where id = #{id}    </select></mapper>

　

3.2 Meanings

First, the mapper scan configuration in the MyBatis configuration file has been modified so that interfaces and XML files can be added directly under the scan package.

Second, with regard to the use of the MyBatis namespace namespace, this is unique and can represent the XML file itself. So, when I want to refer to author's query, I can use the ID of the Authormapper.xml's namespace point select directly to uniquely determine the select fragment. That

select= "Com.test.mapper.dao.AuthorMapper.selectAuthorById"

Then, associate the query, the author_id field of the blog, and the ID field of the author association. Therefore, the author as a property of the blog, first query the blog, and then according to the AUTHOR_ID field to query author. That is, the query was made two times. Here is also my confusion, why mybatis to do so, the name can be a query to obtain data two times query.

Note: In the association node

• The column field is author_id, which is a query that is passed to the query author as a parameter, and if there are multiple arguments to the query statement: column= "{prop1=col1,prop2=col2}" is passed to the nested query statement. This causes PROP1 and PROP2 to be set to the target nested query statement in the form of a Parameter object.
• The property represents the corresponding attribute in the blog class.

We have two query statements: one to load the blog, the other to load the author, and the result map of the blog describes what the "Selectauthor" statement should be used to load its Author property.

All other properties will be loaded automatically, assuming their columns and property names match.

This is a simple approach, but will not perform well for large data collections and lists. The problem is the "n+1 query problem" that we are familiar with. To summarize, the n+1 query problem can be caused by:

• You execute a separate SQL statement to get a list of results (that is, "+1").
• For each record returned, you execute a query statement to load the details for each (that is, "N").

This problem can result in hundreds of thousands of SQL statements being executed. This is not usually expected.

MyBatis can delay loading such queries is a benefit, so you can scatter the consumption of these statements running concurrently. However, if you load a list and then quickly iterate to access the nested data, you'll call all the lazy loads, and this behavior can be pretty bad.

So there's another way.

3.3 correlating Query Results

The correlation query is mainly for delay loading, do cache, if you do not call Blog.getauthor () to get author, then MyBatis will not go to query author. In other words, mybatis the blog and the author's query as a two-step implementation. It is useful to implement information fragmentation loading in some situations. However, in the blog here, obviously not very suitable, because we see the blog at the same time will see the author, then will inevitably lead to query database two times. Below, to test another way, like a SQL Association query, to isolate the results one at a time.

<select id= "Selectblogwithauthor" resultmap= "Blogresultwithauthor" > select b.ID as blog_id, B.name as Blog_title, b.author_id as blog_author_id, a.id as author_id, a.u        Sername as Author_username, A.password as Author_password, A.email as Author_email, A.bio As Author_bio from blog B left OUTER JOIN author A on b.author_id=a.id WHERE b.id = #{id} </sel        ect> <resultmap id= "blogresultwithauthor" type= "blog" > <id property= "id" column= "blog_id"/> <result property= "name" column= "Blog_title"/> <association property= "Author" column= "blog_author_id" JAV        Atype= "Author" resultmap= "Authorresult"/> </resultMap> <resultmap id= "Authorresult" type= "Author" >        <id property= "id" column= "author_id"/> <result property= "username" column= "Author_username"/> <result property= "Password "column=" Author_password "/> <result property=" email "column=" author_email "/> <result pr operty= "Bio" column= "Author_bio"/> </resultMap>

and 3.1 in the same query, are querying out the blog and author. But this only queries the database once, that is, to implement our associated query. These lines of code are a bit complicated at first glance, and it's clear that you can analyze them carefully.

1> first saw the select Tag, which represents the query. Where the ID represents the method name of the corresponding interface; the value of RESULTMAP is the ID of a Resultmap node, which represents how the select query results are mapped.

2> Select in the middle is I am familiar with the associated query statement, here do not repeat

The 3> is then the node blogresultwithauthor that Resultmap points to.

• The ID of the RESULTMAP node is the one that uniquely identifies the node, and the type represents the entity class blog that the Resultmap eventually maps to.
• The ID is the primary key mapping, which is related to the MyBatis cache.
• Result
• Association:
• Authorresult.

You can see the logs printed by the console:

2016-07-22 23:01:00,148 DEBUG [Org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Opening JDBC connection2016-07-22 23:01:11,017 DEBUG [Org.apache.ibatis.datasource.pooled.PooledDataSource]- Created connection 1893960929. 2016-07-22 23:01:19,281 DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction ]-Setting autocommit to False on JDBC Connection [[Email protected]]2016-07-22 23:01:27,018 DEBUG [COM.TEST.MAPPER.D Col Blogmapper.selectblogwithauthor]-==> preparing:select b.id as blog_id, b.name as Blog_title, b.author_id as Blog_au thor_id, a.id as author_id, a.username as Author_username, A.password as Author_password, A.email as Author_email, A.bio a s Author_bio from blog B left OUTER JOIN author A on b.author_id=a.id WHERE b.id =? 2016-07-22 23:01:29,697 DEBUG [Com.test.mapper.dao.BlogMapper.selectBlogWithAuthor]-==> parameters:1( Integer) 2016-07-22 23:01:30,091 DEBUG [Com.test.mapper.dao.BlogMapper.selectBlogWithAuthor]-<== total:1 

MyBatis associated query for advanced queries