preface

The last article analyzed the use of configuration in MyBatis, and the function of dynamic tags in MyBatis is very powerful, this article will not introduce all tags, mainly for resultMap to introduce how to configure dynamic SQL using SQL tags for complex queries.

Fixed parameter query

Add two new methods to usermapper.java:

 List<LwUser> listUserByUserName(@Param("userName") String userName);

 List<LwUser> listUserByTable(@Param("tableName") String tableName);
Copy the code

The SQL statement in usermapper. XML is:

  <select id="listUserByUserName" resultType="lwUser">
        select user_id,user_name from lw_user where user_name=#{userName}
    </select>

    <select id="listUserByTable" resultType="lwUser">
        select user_id,user_name from ${tableName}
    </select>
Copy the code

Then execute the query:

package com.lonelyWolf.mybatis; import com.alibaba.fastjson.JSONObject; import com.lonelyWolf.mybatis.mapper.UserMapper; import com.lonelyWolf.mybatis.model.LwUser; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import java.io.IOException; import java.io.InputStream; import java.util.List; public class MyBatisQueryByParam { public static void main(String[] args) throws IOException { String resource = "mybatis-config.xml"; / / read mybatis config - configuration file InputStream InputStream = Resources. The getResourceAsStream (resource); SqlSessionFactory SqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); / / create the SqlSession objects SqlSession session. = sqlSessionFactory openSession (); / * * * in comparison to the session. The selectList (" com. XXX. UserMapper. ListAllUser ") to implement the query, */ UserMapper UserMapper = session.getmapper (usermapper.class); A List < LwUser > userList = userMapper. ListUserByUserName (" lone Wolf 1 "); System.out.println(null == userList ? "": JSONObject.toJSONString(userList)); List<LwUser> userList2 = userMapper.listUserByTable("lw_user"); System.out.println(null == userList2 ? "": JSONObject.toJSONString(userList2)); }}Copy the code

The following output is displayed:

! [](https://upload-images.jianshu.io/upload_images/17138799-de93710b19795b8d? ImageMogr2 / auto – received/strip | imageView2/2 / w / # / format/webp) and $898

As you can see from the screenshot of the output SQL statement above, if # is used, the SQL statement will first use placeholders in the SQL statement, which is precompiled, corresponding to the PreparedStatement in the JBDC. If $is used, the parameter is directly spelled into a SQL Statement, which is equivalent to a JDBC Statement.

In general, it is not recommended to use $because this direct concatenation method is vulnerable to SQL injection attacks. For example, the SQL statement above:

select user_id,user_name from ${tableName}
Copy the code

Suppose tableName passes lw_user; delete from lw_user; The SQL statement executed at this point will become:

select user_id,user_name from lw_user; delete from lw_user;Copy the code

The entire table is deleted, and if you use #{tableName}, you end up with the following SQL:

select user_id,user_name from 'lw_user; delete from lw_user; 'Copy the code

All it does is query a table that doesn’t exist.

Query dynamic parameters

In the example above, the parameters are fixed. What if we don’t fix the parameters? Let’s say I have two arguments, but I might use neither, I might use only one, or I might use both. How can this be done? As shown in the figure below, we can use the where and if tags together. Both conditions have AND, this is because Mybatis will help us get rid of the extra AND keywords.

<select id="list" parameterType="com.lonelyWolf.mybatis.model.LwUser" resultType="lwUser"> select user_id,user_name from  lw_user <where> <if test="userId ! =null and userId ! =''"> and user_id=#{userId} </if> <if test="userName ! =null and userName ! =''"> and user_name=#{userName} </if> </where> </select>Copy the code

In other words, if you want to concatenate different SQL values for the same parameter, you can use the choose tag to concatenate different SQL values for different parameters

 select user_id,user_name from lw_user
        <where>
            <choose>
                <when test="userId ='1'">
                    and user_id=#{userId}
                </when>
                <when test="userId='2'">
                    and user_id=#{userId}
                </when>
                <otherwise>
                    and user_id=#{userId}
                </otherwise>
            </choose>
        </where>
    </select>
Copy the code

Of course, Mybatis provides many other tags to handle more complex combinations, which I won’t illustrate here.

One-to-one query

If we have two kinds of table, now is a one-to-one relationship, we want to query out at the same time, of course, the easiest way is to write a class, put the results of the two tables attributes are in a class, but there is no doubt that this way can cause a lot of duplicate code, but also no hierarchy, if we have a table lw_user table, storing user information, The other table, lw_user_job, stores the user’s work history. It is obvious that the job class should be included in the user class.

Please look at!

Create a new entity class UserJob to map the attributes of lw_user_job:

package com.lonelyWolf.mybatis.model; public class LwUserJob { private String id; private String userId; // User ID private String companyName; // Private String position; Public String getId() {return id; } public void setId(String id) { this.id = id; } public String getUserId() { return userId; } public void setUserId(String userId) { this.userId = userId; } public String getCompanyName() { return companyName; } public void setCompanyName(String companyName) { this.companyName = companyName; } public String getPosition() { return position; } public void setPosition(String position) { this.position = position; }}Copy the code

Add a reference attribute to the original LwUser class to reference LwUserJob:

package com.lonelyWolf.mybatis.model;

public class LwUser {
    private String userId; //用户id
    private String userName; //用户名称

    private LwUserJob usreJobInfo;//用户工作信息

    public String getUserId() {
        return userId;
    }

    public void setUserId(String userId) {
        this.userId = userId;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public LwUserJob getUsreJobInfo() {
        return usreJobInfo;
    }

    public void setUsreJobInfo(LwUserJob usreJobInfo) {
        this.usreJobInfo = usreJobInfo;
    }
}
Copy the code

Add a new method to usermapper. Java:

List<LwUser> listUserAndJob();
Copy the code

Usermapper. XML needs to customize a ResultMap:

<resultMap id="JobResultMap" type="lwUser"> <result column="user_id" property="userId" jdbcType="VARCHAR" /> <result column="user_name" property="userName" jdbcType="VARCHAR" /> <! -- JavaType here can also define aliases; Corresponding LwUser property in the class attribute name - > < association property = "userJobInfo javaType" = "com. LonelyWolf. Mybatis. Model. LwUserJob" > <result column="id" property="id" jdbcType="VARCHAR" /> <result column="company_Name" property="companyName" jdbcType="VARCHAR" /> <result column="position" property="position" jdbcType="VARCHAR" /> </association> </resultMap> <! <select id="listUserAndJob" resultMap="JobResultMap"> select * from lw_user u inner join lw_user_job j on u.user_id=j.user_id </select>Copy the code

The query results in the following:

[{" userId ":" 1 ", "userJobInfo" : {" companyName ":" freelance ", "id" : "11", "position" : "primary development"}, "userName" : "lone Wolf" 1}]Copy the code

One-to-many query

What about assuming a one-to-many relationship between user information and job information? 1. Change the reference attribute in LwUser to List:

private List<LwUserJob> userJobList;
Copy the code

2. The ResultMap file in Mapper is modified at the same time. The Association label is replaced by collection label, and javaType is changed to ofType:

 <collection property="userJobList" ofType="com.lonelyWolf.mybatis.model.LwUserJob">
            <result column="id" property="id" jdbcType="VARCHAR" />
            <result column="company_Name" property="companyName" jdbcType="VARCHAR" />
            <result column="position" property="position" jdbcType="VARCHAR" />
        </collection>
Copy the code

Executing the query again yields the following result:

[{" userId ":" 1 ", "userJobList" : [{" companyName ":" freelance ", "id" : "11", "position" : "primary development"}], "userName" : "lone Wolf" 1}]Copy the code

You can see that userJobList is already an array.

PS: Remember that someone asked before whether the mapping of attributes must map all attributes in the table. The answer is no. If you need to map a few attributes, you don’t need to map them completely.

Many-to-many query

In fact, the principle of many-to-many is similar to that of one-to-many, which is to use the collection tag, that is, to nest the collection tag inside the collection tag to achieve many-to-many query. I will not give an example here.

Lazy loading (solve N+1 problem)

Let’s first look at another way of writing one-to-many, which supports a nested query:

<resultMap id="JobResultMap2" type="lwUser"> <result column="user_id" property="userId" jdbcType="VARCHAR" /> <result column="user_name" property="userName" jdbcType="VARCHAR" /> <collection property="userJobList" ofType="com.lonelyWolf.mybatis.model.LwUserJob" column="user_id" select="selectJob"> </collection> </resultMap> <! <select id="selectUserAndJob" resultMap="JobResultMap2"> select * from lw_user. - a nested query - > < select id = "selectJob" resultType = "com. LonelyWolf. Mybatis. Model. LwUserJob" > select * from lw_user_job where user_id=#{userId} </select>Copy the code

There are no attributes defined in the collection above, but there are two tags defined in the collection that represent the value of the current query result, user_id, to be passed to the query selectJob. Let’s define a method to execute the external query selectUserAndJob and see what happens:

! [](https://upload-images.jianshu.io/upload_images/17138799-69e5558a8c3c55d0? imageMogr2/auto-orient/strip|imageView2/2/w/1178/format/webp)

As you can see, the internal query is triggered several times as many times as the external query has some data. This is the N+1 problem caused by nested query **. (This is also a problem with the Association tag.)

This is not allowed in scenarios with high performance requirements, which is a waste of resources. MyBatis officially does not recommend us to use this method.

Solve the N+1 problem

Although MyBatis does not recommend us to use such nested query, it also provides a way to solve the N+1 problem, that is, when we execute external query, internal query will not be triggered, and only when we use internal objects, internal query will be triggered to obtain the corresponding results, which is called lazy loading.

Lazy loading needs to be controlled by global properties, which are turned off by default. Enable lazy loading in mybatis-config. XML

<setting name="lazyLoadingEnabled" value="true"/>
Copy the code

Then we will execute the following statement:

List<LwUser> userList = userMapper.selectUserAndJob(); System.out.println(userList.size()); System.out.println(userlist.get (0).getUserJoblist ())); System.out.println(null == userList? "": JSONObject.toJSONString(userList)); / / triggersCopy the code

The output is:

! [](https://upload-images.jianshu.io/upload_images/17138799-fe4b290a966272e5? imageMogr2/auto-orient/strip|imageView2/2/w/1200/format/webp)

Principle of delayed loading

The Javassist dynamic proxy is used by default. It can be controlled by parameters. CGLIB is supported:

<setting name="proxyFactory" value="CGLIB" />
Copy the code

conclusion

This article mainly describes how to use MyBatis to implement one-to-one, one-to-many and many-to-many queries, and explains how to use lazy loading to solve the N+1 problem in nested queries. MyBatis series in the first two relatively basic, did not in-depth analysis of the implementation principle, just explained how to use, the next chapter, will be in-depth analysis of MyBatis source code and some advanced features such as sqlSession execution process, cache, parameters and result set mapping and other functions of the implementation principle.