Dynamic SQL is a powerful feature of MyBatis.When using JDBC to manipulate data, concatenating conditions into SQL strings can be painful if there are too many query conditions. The usual solution is to write a lot of if-else conditional statements and strings to concatenate, and make sure you don't forget spaces or omit commas at the end of fields.MyBatis uses a powerful dynamic SQL language to improve this situation. Dynamic SQL based on OGNL expressions allows us to easily implement some logic in SQL statements.

Case 1 Query using dynamic sql

sql statement:

<select id="selectByCon" 
	  parameterType="com.obtk.entitys.ConditionEntity" 
	        resultType="StudentEntity">
		select * from student  where  1=1
	
			<if test="gender!=null">
			   and gender=#{gender}
			</if>
			<if test="stuName!=null">
			   and stuName like CONCAT('%',#{theName},'%')
			</if>
			<if test="minAge!=null">
			   and age>=#{minAge}
			</if>
			<if test="maxAge!=null">
			   and age<![CDATA[<=]]>#{maxAge}
			</if>
			<if test="address!=null">
			   and address=#{address}
			</if>
	
	</select>

Code:

package com.obtk.test2;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.obtk.entitys.ConditionEntity;
import com.obtk.entitys.StudentEntity;
import com.obtk.utils.MybatisUtil;

public class TestConQuery {
	public static void main(String[] args) {
		SqlSession session=null;
		try {
			//4. Get session
			session=MybatisUtil.getSession();
			ConditionEntity con=new ConditionEntity();
			con.setMinAge(20);
			con.setMaxAge(44);
			con.setGender("male");
			//5. Execute statements
			List<StudentEntity> stuList=session.
			         selectList("stu.selectByCon",con);
			for(StudentEntity stu : stuList){
				System.out.println(stu.getStuName()+","+stu.getGender()
						+","+stu.getAge());
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			MybatisUtil.closeSession();
		}
	}
}

Or use the where tag as follows:

<select id="selectByCon" 
	  parameterType="com.obtk.entitys.ConditionEntity" 
	        resultType="StudentEntity">
		select * from student
		<where>
			<if test="gender!=null">
			   and gender=#{gender}
			</if>
			<if test="stuName!=null">
			   and stuName like CONCAT('%',#{theName},'%')
			</if>
			<if test="minAge!=null">
			   and age>=#{minAge}
			</if>
			<if test="maxAge!=null">
			   and age<![CDATA[<=]]>#{maxAge}
			</if>
			<if test="address!=null">
			   and address=#{address}
			</if>
		</where>
	</select>

The code is the same as above.

The purpose of the where element is to output a where where where where where element is written.In addition, MyBatis intelligently handles output conditions, and if none of them are met, MyBatis finds all records; if the output starts with and, MyBatis ignores the first and.So you don't need to write a condition like 1 = 1.

Case 2. Update operation using dynamic sql

sql statement

<update id="updateByCon" parameterType="StudentEntity" statementType="PREPARED">
		update student 
		<set>
			<if test="stuName!=null">stuName=#{stuName},</if>
			<if test="gender!=null">gender=#{gender},</if>
			<if test="age!=null">age=#{age},</if>
			<if test="address!=null">address=#{address},</if>
			<if test="deptIdd!=null">deptIdd=#{deptIdd},</if>
		</set>
		where stuId=#{stuId}
	</update>

Code:

package com.obtk.test2;

import org.apache.ibatis.session.SqlSession;

import com.obtk.entitys.StudentEntity;
import com.obtk.utils.MybatisUtil;

public class TestUpdateByCon {
	public static void main(String[] args) {
		SqlSession session=null;
		try {
			//4. Get session
			session=MybatisUtil.getSession();
			StudentEntity theStu=new StudentEntity("Small Green Flower", "female", 23, "Mars");
			theStu.setStuId(129);
			//5. To execute a statement with more parameters, use the object of the entity class to pass parameters
			int theId=session.update("stu.updateByCon", theStu);
			session.commit();
			System.out.println("Successful modification!"+theId);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			MybatisUtil.closeSession();
		}
	}
}