mybatis动态sql-(1)-多次条件判断If和OGNL

1、搭建数据库

tbl_employee表【d_id 是外键 对应 tbl_dept表】

id	last_name    gender	  email       d_id
1	   mike	       0      [email protected]      1
2          book        0      [email protected]    2
3          tom         1      [email protected]    2
4          jerry       1       [email protected]       1

tbl_dept表:

id    dept_name
1      开发部
2      测试部

2、编辑 javabean

特别要提醒:因为是在测试代码中,需要用到自定义Employee构造函数,所以这里为了能让mybatis正常使用反思,一定要添加Employee的无参构造函数。

Employee.java

package com.mybatis.bean;

public class Employee {
	private int id;
	private String lastName;
	private String email;
	private String gender;
	private Department department;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public String getGender() {
		return gender;
	}

	public void setGender(String gender) {
		this.gender = gender;
	}
	
	public Department getDepartment() {
		return department;
	}

	public void setDepartment(Department department) {
		this.department = department;
	}

	@Override
	public String toString() {
		return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + "]";
	}

       /*****  关键是 这个地方啊 出了 问题 ****/
	public Employee() {
		super();
	}
	/*****/
	public Employee(int id,String lastName, String  email, String gender){
		this.id =id;
		this.lastName =lastName;
		this.email =email;
		this.gender =gender;
	}

}

Department.java

package com.mybatis.bean;

import java.util.List;

public class Department {
	
	private Integer id;
	private String departmentName;
        private List<Employee> employees;
        
        public List<Employee> getEmployees() {
		return employees;
	}
	public void setEmployees(List<Employee> employees) {
		this.employees = employees;
	}

	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getDepartmentName() {
		return departmentName;
	}
	public void setDepartmentName(String departmentName) {
		this.departmentName = departmentName;
	}
	@Override
	public String toString() {
		return "Department [id=" + id + ", departmentName=" + departmentName
				+ "]";
	}
	
	

}

三、编辑接口文件

EmployeeMapperDynamicSQL.java

package com.mybatis.mapper;

import java.util.List;

import com.mybatis.bean.Employee;

public interface EmployeeMapperDynamicSQL {

	//携带了哪个字段查询条件就带上这个字段的值
	public List<Employee> getEmpsByConditionIf(Employee employee);
	
}

四、SQL映射文件

<?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.mybatis.mapper.EmployeeMapperDynamicSQL">
<!-- 
上面的 namespace:名称空间;指定为接口的全类名
下面的 id:唯一标识被规定为接口方法名 【public Employee getEmpById(Integer id);】
下面的 resultType:返回值类型
下面的 #{id}:从传递过来的参数中取出id值
 -->
	
<!-- 
• if:判断
• choose (when, otherwise):分支选择;带了break的swtich-case
	如果带了id就用id查,如果带了lastName就用lastName查;只会进入其中一个
• trim 字符串截取(where(封装查询条件), set(封装修改条件))
• foreach 遍历集合
	 -->
	 <!-- 查询员工,要求,携带了哪个字段查询条件就带上这个字段的值 -->
	 <!-- public List<Employee> getEmpsByConditionIf(Employee employee); -->
<select id="getEmpsByConditionIf" resultType="com.mybatis.bean.Employee">
	 	select * from tbl_employee
	 	where
                <!-- where -->
	 	
		 	<!-- test:判断表达式(OGNL)
		 	OGNL参照PPT或者官方文档。
		 	  	 c:if  test
		 	从参数中取值进行判断
		 	
		 	遇见特殊符号应该去写转义字符:
		 	&&:
		 	-->
		 	<if test="id!=null">
		 		id=#{id}
		 	</if>
		 	<if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;">
		 		and last_name like #{lastName}
		 	</if>
		 	<if test="email!=null and email.trim()!=&quot;&quot;">
		 		and email=#{email}
		 	</if> 
		 	<!-- ognl会进行字符串与数字的转换判断  "0"==0 -->
		 	<if test="gender==0 or gender==1">
		 	 	and gender=#{gender}
		 	</if>
	 	
	 </select>


</mapper>

五、全局配置文件

添加了,驼峰映射 大小写 自动转换 配置 setting。
因为从数据库中,选择出来了的就 只有5个字段,这5个字段,自动映射到Employee中,然后 d_id字段无法和bean中的department 属性映射,所以被废弃。department就为null了。

<?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>

    <settings>
		<setting name="mapUnderscoreToCamelCase" value="true"/>
	</settings>

	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver" />
				<property name="url" value="jdbc:mysql://localhost:3306/mybatis" />
				<property name="username" value="root" />
				<property name="password" value="Cool123!" />
			</dataSource>
		</environment>
	</environments>
	<!-- 将我们写好的sql映射文件(EmployeeMapper.xml)一定要注册到全局配置文件(mybatis-config.xml)中 
	如果 数据库全局文件 和 子配置文件 不在同一个目录 ,就需要 /目录/目录/.../EmployeeMapper_old.xml
	-->
	<mappers>
	
	    <!-- 新方法操作mybatis 需要 的配置文件 -->
		<mapper resource="EmployeeMapperDynamicSQL.xml" />
	</mappers>
</configuration>

六、Junit单元测试

department属性,是没有赋值的,这里要注意一下。
sql语句的条件判断是OK的。

package com.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.Test;

import com.mybatis.bean.Employee;
import com.mybatis.mapper.EmployeeMapperDynamicSQL;


/**
 * 1、接口式编程
 * 	原生:		Dao		====>  DaoImpl
 * 	mybatis:	Mapper	====>  xxMapper.xml
 * 
 * 2、SqlSession代表和数据库的一次会话;用完必须关闭;
 * 3、SqlSession和connection一样她都是非线程安全。每次使用都应该去获取新的对象。(就是不要放在共享成员变量里面,A线程用完释放,B线程再用就为空了)
 * 4、mapper接口没有实现类,但是mybatis会为这个接口生成一个代理对象。
 * 		(将接口和xml进行绑定)
 * 		EmployeeMapper empMapper =	sqlSession.getMapper(EmployeeMapper.class);
 * 5、两个重要的配置文件:
 * 		mybatis的全局配置文件:包含数据库连接池信息,事务管理器信息等...系统运行环境信息
 * 		sql映射文件:保存了每一个sql语句的映射信息:
 * 					将sql抽取出来。	
 *
 */

public class MybatisTest {


	public SqlSessionFactory getSqlSessionFactory() throws IOException {
		String resource = "mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		return new SqlSessionFactoryBuilder().build(inputStream);
	}
	
	@Test
	public void test_new() throws IOException {
		// 1、获取sqlSessionFactory对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		// 2、获取sqlSession对象
		SqlSession openSession = sqlSessionFactory.openSession();
		try {
			// 3、获取接口的实现类对象
			//会为接口自动的创建一个代理对象,代理对象去执行增删改查方法
			EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
			Employee employee = new Employee(3,"%m%","[email protected]","1");
			List<Employee> list = mapper.getEmpsByConditionIf(employee);
			for(Employee emp :list){
				System.out.println(emp);
				System.out.println(emp.getDepartment());

			}
		} finally {
			openSession.close();
		}

	}

}

 

0 0 votes
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments