Dynamic SQL is the dynamic generation of SQL.
if tag
Suppose there is a requirement: when querying the user, when the user name does not equal "admin", we also need a password of 123456.
The data in the database is:
MyBatisConfig.xml
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration><!--Defining alias note that typeAlias must be before environments --><typeAliases><typeAlias type="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 mapperPUBLIC "-//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.jikeuserWHERE 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.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: * Idea: */public class TestSQL {public static void main(String[] args) {// resource path String 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 running result is:
Choose tag
Suppose we currently have a requirement: query the user, if the user name is not empty, add the user name condition, if the id is not empty, add the id condition, otherwise, set the password not empty, which is a multiple choice.
MyBatisConfig.xml does not change, add:
<select id="selectJiKeUserChoose" resultType="JiKeUser" parameterType="JiKeUser">select * from jikeuser where 1=1<choose><w 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: Assume that the username 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.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: * Idea: */public class TestSQL {public static void main(String[] args) {// resource path String 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 result is:
Assuming that the username condition is not set, that is, comment out jiKeUser.setUserName("YEN");:
Where marker, set marker
The above query in the choice is that we cannot determine whether and in the sub-connection condition is written or not, so we add a 1=1. And where we can only judge whether it should be added.
<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 intelligent assignment will automatically remove the excess ","
<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 operation:
operate:
Operation results:
<update id="updateUserTrim" parameterType="JiKeUser">UPDATE JiKeUser<trim prefix="SET" suffixOverrides="," suffix="WHERE id = #{id}" ><if test="userName != null and userName != '' ">userName = #{userName},</if><if test="password != null and password != '' ">password=#{password},</if></trim></update>foreach tag
Usually used for loop query or loop assignment
<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 detailed explanation of the Mybatis dynamic SQL if, choose, where, set, trim, and foreach tag examples introduced to you by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply you in time. Thank you very much for your support to Wulin.com website!