MyBatis Dynamic SQL If, choose, where, set, trim, foreach tag instances detailed _java

Dynamic SQL is the dynamic generation of SQL.

If tag

Suppose there is such a requirement: Query the user, when the user name is not equal to "admin", we also need a password of 123456.

The data in the database is:

Mybatisconfig.xml

<?xml version= "1.0" encoding= "UTF-8"?> <!
DOCTYPE configuration Public "-//mybatis.org//dtd Config 3.0//en" "Http://mybatis.org/dtd/mybatis-3-config.dtd" > <configuration> <!--definition alias Note typealiases must be--> <typeAliases> <typealias type= before environments. Jike.book.pojo.JiKeUser "alias=" Jikeuser "/> <typealias type=" Jike.book.pojo.Author "alias=" Author "/> </ typealiases> <environments default= "Development" > <environment id= "Development" > < TransactionManager type= "JDBC" > </transactionManager> <datasource type= "Pooled" > <property name= " Driver "value=" Com.mysql.jdbc.Driver "/> <property name=" url "value=" Jdbc:mysql://localhost:3306/jikebook "/ > <property name= "username" value= "root"/> <property name= "password" value= "* * *"/> </datasource > </environment> </environments> <mappers> <mapper resource= "Jike/book/map/jikeuser.xml"/ > </mappers> </configuration>

Jikeuser.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= "/" > <select id= "selectsql" resulttype= "Jikeuser
" parametertype= "JiKeUser" >
SELECT * from Jikebook.jikeuser
WHERE 1=1
<if test= "username!= ' admin '" > and
password=#{ Password}
</if>
</select>
</mapper>

Test class:

Package jike.book.test;
Import Jike.book.pojo.JiKeUser;
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.Reader;
Import java.util.List; /** * DATETIME:2016/9/6 13:36 * Function: * Thinking: * * public class Testsql {public static void main (string[] args) {//resource path Stri
ng resource= "Jike/book/map/mybatisconfig.xml";
Reader Reader=null;
Sqlsession session;
try {reader= Resources.getresourceasreader (Resource);} catch (IOException e) {e.printstacktrace ();}
Sqlsessionfactory sqlmapper=new Sqlsessionfactorybuilder (). build (reader);
Session=sqlmapper.opensession ();
Jikeuser jikeuser=new Jikeuser ();
Jikeuser.setpassword ("123456");
List<jikeuser> userlist=session.selectlist ("Selectsql", Jikeuser);
for (Jikeuser user:userlist) {System.out.println ("UserName:" +user.getusername ());}
Session.close (); }
}

The results of the operation are:

Choose Mark

Suppose we currently have such a demand: Query the user, if the user name is not empty and add the user name this condition, if the ID is not empty also add ID this condition, otherwise the words is to set the password is not empty, this is a multi-channel choice.

Mybatisconfig.xml does not change, add in Jikeuser.xml:

<select id= "Selectjikeuserchoose" resulttype= "Jikeuser" parametertype= "Jikeuser" >
select * from Jikeuser where 1=1
<choose>
<when test= "Username!=null" > and
userName like #{username}
</when >
<when test= "id!=0" > and
ID =#{id}
</when>
<otherwise> and
password is Not null
</otherwise>
</choose>
</select>

Test class: Suppose the user name is not empty:

Package jike.book.test;
Import Jike.book.pojo.JiKeUser;
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.Reader;
Import java.util.List; /** * DATETIME:2016/9/6 13:36 * Function: * Thinking: * * public class Testsql {public static void main (string[] args) {//resource path Stri
ng resource= "Jike/book/map/mybatisconfig.xml";
Reader Reader=null;
Sqlsession session;
try {reader= Resources.getresourceasreader (Resource);} catch (IOException e) {e.printstacktrace ();}
Sqlsessionfactory sqlmapper=new Sqlsessionfactorybuilder (). build (reader);
Session=sqlmapper.opensession ();
Jikeuser jikeuser=new Jikeuser ();
Jikeuser.setusername ("yen");
List<jikeuser> userlist=session.selectlist ("Selectjikeuserchoose", Jikeuser);
for (Jikeuser user:userlist) {System.out.println ("UserName:" +user.getusername ());}
Session.close ();
}} 

The results are:

Suppose you do not set the user name this condition, that is, comment out jikeuser.setusername ("yen");

where tag, set tag

Above we are in the Choose query is not sure whether the and is write or not in the child join condition, so a 1=1 is added. And where is the only way to judge whether or not to add.

<select id= "Selectjikeuserwhere" resulttype= "Jikeuser" parametertype= "Jikeuser" >
select * from Jikeuser
<where>
<if test= "Username!=null" > and
userName like #{username}
</if>
< If test= "Id!=null" > and
ID =#{id}
</if>
</where>
</select>

The set tag intelligently assigns a value that automatically removes the redundant ",".

<update id= "Updatejikeuserset" parametertype= "Jikeuser" >
update jikeuser
<set>
<if test = "UserName!= null" >userName=#{userName},</if>
<if test= "password!= null" >password=#{password}, </if>
</set>
where id=#{id}
</update>

Data before the operation:

Operation:

Operation Result:

<update id= "Updateusertrim" parametertype= "Jikeuser" >
update jikeuser <trim
"SET" Suffixoverrides= "," suffix= "Where id = #{id}" >
<if test= "userName!= null and UserName!= '" ">
Userna me = #{username},
</if>
<if test= "password!= null and password!= '" ">
Password=#{password} ,
</if>
</trim>
</update>

foreach tag

Commonly used for looping queries or looping assignments

<select id= "Selectjikeuserforeach" resulttype= "Jikeuser" parametertype= "List" >
select * from Jikeuser
<where>
ID
in <foreach item= "item" index= "index" collection= "list"
open= "(" separator= "," close = ")" >
#{item}
</foreach>
</where>
</select>

Test:

The above is a small set up to introduce the mybatis dynamic SQL if, choose, where, set, trim, foreach mark examples of detailed, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!