16mybatis_ Dynamic sql_if judgment

The core of MyBatis is dynamic SQL.

What is dynamic SQL: Flexible operation of SQL statements, judgment through expressions, flexible splicing and assembly of SQL.

This article explains the IF statement in SQL. It can judge the query condition, if the input parameter is not empty to make the query condition stitching. The core of this article is the following code.

 <!--
<where></where> corresponds to the WHERE keyword in the SQL statement.
Another feature of this tag is that the first and is removed automatically, such as the following two lines of SQL code, and if the first one is satisfied then the keyword is removed.





-
<where>        <ifTest= "Usercustom!=null">            <ifTest= "Usercustom.sex!=null and usercustom.sex!=">and user.sex= #{usercustom.sex}</if>            <ifTest= "Usercustom.username!=null and usercustom.username!=" >and User.username=#{usercustom.username}</if>                                </if>                    </where>

The following is a specific case for u:

The case code is structured as follows:

In addition to Usermapper.xml and Mybatis_mappertest.java to modify, the other code does not have to change.

The Usermapper.xml code is as follows:

<?XML version= "1.0" encoding= "UTF-8"?><!DOCTYPE mapperpublic "-//mybatis.org//dtd Mapper 3.0//en" "Http://mybatis.org/dtd/mybatis-3-mapper.dtd "><!--nanmespace: Name space. The function is to classify the SQL management, understand SAL separation Note: Using Mapper Proxy method, namespace has a special important role -<Mappernamespace= "Cn.itcast.mybatis.mapper.userMapper">    <!--The ID here is identical to the function name in the Mapp interface. ParameterType represents the type of input, Resulttype represents the type of output -    <SelectID= "Finduserlist"ParameterType= "Cn.itcast.mybatis.po.UserQueryVo"Resulttype= "Cn.itcast.mybatis.po.UserCustom">SELECT ID, username from USER WHERE user.sex= #{usercustom.sex} and User.username=#{usercustom.username} /c9></Select>        <!--The definition Resultmap makes a mapping relationship between the Select ID id_,username username_ from the user and the attributes in the user class Type:resultmap the final mapped Java object type, which can be     To use alias ID: A unique identifier for the Resultmap.  -    <Resultmaptype= "User"ID= "Userresultmap">        <!--ID: Represents a unique identity in a query result set.                         Column: The name of the query Property:type the attribute name in the specified Pojo type finally resultmap a mapping relationship between column and property (correspondence)  -        <IDcolumn= "Id_" Property= "id"/>        <resultcolumn= "Username_" Property= "username"/>    </Resultmap>            <SelectID= "Finduserbyresultmap"ParameterType= "Cn.itcast.mybatis.po.UserQueryVo"Resultmap= "Userresultmap">SELECT ID id_,username username_ from USER<!--where can automatically remove the first and in a condition -    <where>        <ifTest= "Usercustom!=null">            <ifTest= "Usercustom.sex!=null and usercustom.sex!=">and user.sex= #{usercustom.sex}</if>            <ifTest= "Usercustom.username!=null and usercustom.username!=" >and User.username=#{usercustom.username}</if>                                </if>                    </where>        </Select>       </Mapper>

The test code Mybatis_mappertest.java code is as follows:

 PackageCn.itcast.mybatis.first;Importjava.io.IOException;ImportJava.io.InputStream;Importjava.util.Date;Importjava.util.List;Importorg.apache.ibatis.io.Resources;Importorg.apache.ibatis.session.SqlSession;Importorg.apache.ibatis.session.SqlSessionFactory;ImportOrg.apache.ibatis.session.SqlSessionFactoryBuilder;ImportOrg.junit.Before;Importorg.junit.Test;ImportCn.itcast.mybatis.mapper.userMapper;ImportCn.itcast.mybatis.po.User;ImportCn.itcast.mybatis.po.UserCustom;ImportCn.itcast.mybatis.po.UserQueryVo; Public classMybatis_mappertest {Privatesqlsessionfactory sqlsessionfactory; @Before Public voidSetup ()throwsIOException {String resource= "Sqlmapconfig.xml"; InputStream InputStream=Resources.getresourceasstream (Resource); //The main generation is sqlsessionfactory.          This. sqlsessionfactory=NewSqlsessionfactorybuilder (). Build (InputStream); } @Test Public voidTestmaper () {sqlsession sqlsession=NULL; Sqlsession=sqlsessionfactory.opensession (); //Generate proxy classUsermapper Usermapper=sqlsession.getmapper (Usermapper.class); //Create wrapper objects, set query criteriaUserqueryvo userqueryvo=NewUserqueryvo (); Usercustom Usercustom=NewUsercustom ();  // Note that I have canceled the sex here, that is, sex has no value, then we are using a dynamic SQL statement, that is, sex will not be stitched in it          //usercustom.setsex ("1");Usercustom.setusername ("Wang Wang");                Userqueryvo.setusercustom (Usercustom);                    Usermapper.finduserbyresultmap (USERQUERYVO); }}

Run as follows:

DEBUG [main]-==> preparing:select ID id_,username username_ from USER WHERE user.username=?

See, there's no such statement in the SQL statement with and user.sex= #{usercustom.sex} . That means no stitching up.

Let's change it: Simply put the whole usercustom in.

The results are as follows:

DEBUG [main]-==> preparing:select ID id_,username username_ from USER

See, there is nothing in the SQL statement here where the statements in the back are not stitched up.

16mybatis_ Dynamic sql_if judgment