mybatis-210721-02---动态sql-trim自定义字符串截取

mybatis-210721-02—动态sql-trim自定义字符串截取

• EmployeeMapper.java
• EmployeeMapper.xml(笔记在这儿哦)
• MybatisTest.java

---

EmployeeMapper.java

package com.bgy.mybatis.dao;
import java.util.List;
import com.bgy.mybatis.bean.Employee;

public interface EmployeeMapper {
	
	public List<Employee> getEmpsByConditionTrim(Employee employee);
}

EmployeeMapper.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="com.bgy.mybatis.dao.EmployeeMapper">
	
	<!--
		if
		choose（when，otherwise）
		trim（where，set）
		foreach
	-->
	<!--
		查询员工
			要求：
			携带了那个字段，查询条件就带上这个字段的值
	-->
	<select id="getEmpsByConditionTrim" resultType="com.bgy.mybatis.bean.Employee">
		select 
			* 
		from
			tbl_employee
			<!-- 
				解决将后面多出来的and或or，，where标签不能解决
				
				prefix：	
					前缀，trim标签体中是整个字符串  拼串后  的结果
					prefix给  拼串后  的整个字符串加一个前缀
				
				prefixOverrides：
					前缀覆盖，去掉整个字符串前面多余的字符
				
				suffix：
					后缀，给  拼串后  的整个字符串加一个后缀
					
				suffixOverrides：
					后缀覆盖，去掉整个字符串后面多余的字符
			-->
			<trim prefix="where" suffixOverrides="and">
				<if test="id!=null and id!=''">
					id=#{id} and
				</if>
				<if test="lastName!=null and lastName!=&quot;&quot;">
					last_name like #{lastName} and
				</if>
				<if test="email!=null and email.trim()!=&quot;&quot;">
					email like #{email} and
				</if>
				<!-- ognl会自动进行字符串和数字的转换判断	"0"==0 -->
				<if test="gender==0 or gender==1">
					gender=#{gender}
				</if>
			</trim>
	</select>
</mapper>

MybatisTest.java

package com.bgy.mybatis.test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

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 org.junit.jupiter.api.Test;

import com.bgy.mybatis.bean.Department;
import com.bgy.mybatis.bean.Employee;
import com.bgy.mybatis.dao.DepartmentMapper;
import com.bgy.mybatis.dao.EmployeeMapper;

class MybatisTest {
	
	public SqlSessionFactory getSqlSessionFactory() throws IOException {
		String resource = "mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		return new SqlSessionFactoryBuilder().build(inputStream);
	}

	
	/**
	 * 测试sql——trim
	 * @throws IOException
	 */
	@Test
	public void test() throws IOException{
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession sqlSession = sqlSessionFactory.openSession();
		
		try {
			EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
			
			Employee employee = new Employee(null,"","","1");
			List<Employee> emps = mapper.getEmpsByConditionTrim(employee);
			
			System.out.println(emps);
		} finally {
			sqlSession.close();
		}
	}
}