mybatis--动态语句查询

--核心配置文件

<?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>
	<properties resource="jdbc.properties"></properties>
	<settings>
		<!--开启数据库下划线与Java驼峰式映射对应 -->
		<setting name="mapUnderscoreToCamelCase" value="true" />
		<!-- 懒加载 -->
		<setting name="lazyLoadingEnabled" value="true" />
		<!-- 当开启时，任何方法的调用都会加载该对象的所有属性。否则，每个属性会按需加载 -->
		<setting name="aggressiveLazyLoading" value="false" />
		<!-- 设置jdbc空值的类型,oracle默认插入的是OTHER -->
		<setting name="jdbcTypeForNull" value="NULL" />
	</settings>
	<typeAliases>
		<package name="com.hwua.entity" />
	</typeAliases>
	<environments default="oracle_dev">
		<environment id="mysql_dev">
			<!-- 使用jdbc事务管理 -->
			<transactionManager type="JDBC" />
			<!-- 数据库连接池 -->
			<dataSource type="POOLED">
				<property name="driver" value="${mysql.driverClass}" />
				<property name="url" value="${mysql.url}" />
				<property name="username" value="${mysql.username}" />
				<property name="password" value="${mysql.password}" />
			</dataSource>
		</environment>

		<environment id="oracle_dev">
			<!-- 使用jdbc事务管理 -->
			<transactionManager type="JDBC" />
			<!-- 数据库连接池 -->
			<dataSource type="POOLED">
				<property name="driver" value="${oracle.driverClass}" />
				<property name="url" value="${oracle.url}" />
				<property name="username" value="${oracle.username}" />
				<property name="password" value="${oracle.password}" />
			</dataSource>
		</environment>
	</environments>
	<!-- 支持多厂商特性 -->
	<databaseIdProvider type="DB_VENDOR">
		<!-- 为不同数据库厂商取别名 -->
		<property name="SQL Server" value="sqlserver" />
		<property name="DB2" value="db2" />
		<property name="Oracle" value="oracle" />
		<property name="MySQL" value="mysql" />
	</databaseIdProvider>

	<mappers>
		<package name="com.hwua.dao" />
	</mappers>
</configuration>

--2映射文件

<mapper namespace="com.hwua.dao.EmpMapper">
	<!--1.使用if动态sql【拼接查询条件】 List<Employee> findByConditionIf(Employee emp); -->
	<select id="findByConditionIf" parameterType="Employee"
		resultType="Employee">
		select id,last_name,email,gender from t_emps
		<!-- where标签可以去掉多余的连接条件：and，or等 -->
		<where>
			<!-- <if test="id !=null"> and id=#{id} </if> -->
			<!-- <if test="lastName !=null and lastName !=''"> -->
			<!-- 方法一：new Employee(null, "王%", "", "") -->
			<!-- and last_name like #{lastName} -->
			<!--方法二 new Employee(null, "王", "", "") -->
			<!-- and last_name like "%"#{lastName}"%" -->
			<!--方法三 new Employee(null, "王", "", "") ， concat('%',concat(#{lastName},'%'))前后都有% -->
			<!-- and last_name like concat('%',concat(#{lastName},'%')) -->
			<!-- </if> -->
			<!-- <if test="email !=null and email !=''"> and email=#{email} </if> -->
			<!-- <if test=' gender=="男" or gender=="女" ' > -->
			<if test=" gender==&quot;男&quot;  or gender==&quot;女&quot;">
				and gender=#{gender}
			</if>
		</where>
	</select>

	<!-- 2.使用Choose动态sql【when otherwise】 List<Employee> findByConditionChoose(Employee 
		emp); -->
	<select id="findByConditionChoose" parameterType="Employee"
		resultType="Employee">
		select id,last_name,email,gender from t_emps
		<where>
			<!-- choose自带break,当有条件满足就不用下面的了 -->
			<choose>
				<when test="id != null">
					id=#{id}
				</when>
				<when test="lastName != null and lastName !=''">
					last_name=#{lastName}
				</when>
				<when test="email != null and email !=''">
					email=#{email}
				</when>
				<when test="gender != null">
					gender=#{gender}
				</when>
				<when test=" dept!=null and dept.id != null">
					dept_id=#{dept.id}
				</when>
				<otherwise>
					1=1
				</otherwise>
			</choose>

		</where>
	</select>
	
	<!-- //3.使用trim（字符串截取）动态sql【update表】
	List<Employee> updateByConditionTrim(Employee emp); -->
	<update id="updateByConditionTrim" parameterType="Employee">
	update t_emps
	<!-- <set>可以去掉语句后的逗号 -->
	<set>
		<if test="lastName !=null and lastName !=''">
			last_name=#{lastName},
		</if>
		<if test="email !=null and email !='' ">
			email=#{email},
		</if>
		<if test="gender !=null and gender !='' ">
			gender=#{gender},
		</if>
		<if test=" dept!=null and dept.id != null">
			dept_id=#{dept.id}
		</if>
	</set>
	where id=#{id}
</update>
	 <!-- //4.插入
	Integer addByCondition(Employee emp); -->
	<insert id="addByCondition" parameterType="Employee">
	INSERT INTO  t_emps (last_name,email,gender,dept_id) 
    VALUES (#{lastName},#{email},#{gender},#{dept.id})    
	</insert>

	 <!-- 5,批量插入
	Integer bathByCondition(List<Employee> emp); -->
	<insert id="bathByCondition">
	INSERT INTO  t_emps (last_name,email,gender,dept_id) 
    VALUES 
    <foreach collection="emps" item="emp" separator=",">
    (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
    </foreach>        
	</insert>
	<!-- 方法2 ：要在数据库url路径加后面这句话：allowMultiQueries=true -->
	<insert id="bathByCondition2" databaseId="mysql">	
    <foreach collection="emps" item="emp" separator=";" close=";">
    INSERT INTO  t_emps (last_name,email,gender,dept_id) 
    VALUES (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
    </foreach>        
	</insert>
	<!-- oracle：方法一  数据库插入数据 -->
	<insert id="bathByCondition2" databaseId="oracle">	
    <foreach collection="emps" item="emp" separator=";" open="begin" close=";end;">
    INSERT INTO  t_emp
    VALUES ( emp_seq.nextval,#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
    </foreach>        
	</insert>
	<!-- oracle：方法二  数据库插入数据:
	 INSERT INTO t_emp  (id, last_name, email, gender) 
   select emp_seq.nextval, last_name, email, gender from   
   ( select 'hh'last_name,'122cm' email,'男' gender from dual
     union
    select 'qq'last_name,'333cm' email,'男' gender from dual )
    
    close=")" 不能加分号《close=";)"是错的》 
    或者可以加close=";)"不过要在开头和结尾加begin end-->
	<insert id="bathByCondition3" databaseId="oracle">
	
	 INSERT INTO t_emp  (id, last_name, email, gender,dept_id) 
    select emp_seq.nextval, last_name, email, gender,dept_id from 	
    <foreach collection="emps" item="emp" separator="union" open="(" close=")">
    select #{emp.lastName} last_name,#{emp.email} email,
    	#{emp.gender} gender,#{emp.dept.id} dept_id from dual
     </foreach> 
	</insert>
	
	
	<!-- //6.批量查询
	List<Employee> findByIds(List<Integer> ids); -->
	<!-- open="(" close=")" 是在拼接好字符串后，增加括号 -->
	<select id="findByIds" resultType="Employee">	
	select id,last_name,email,gender  from t_emps where id in 
	<foreach collection="list" item="id" separator="," open="(" close=")">
	#{id}
	</foreach>
	</select>
	
	<!--//7.查看使用不同的数据库环境之下相同的方法
	//接口就一个（可以动态选择不同数据库，根据databaseId选择数据库 ）
	在全局配置中需要配置
	List<Employee> findAll(); -->
	<!-- 如果同时找到带有 databaseId 和不带 databaseId 的相同语句，则后者会被舍弃。
	但是如果没有找到对象的databaseId，则会使用不带databaseId这个查询 -->	
	<select id="findAll" resultType="Employee">
	select id,last_name,email,gender  from t_emps
	</select>
	<select id="findAll" resultType="Employee" databaseId="mysql">
	select id,last_name,email,gender  from t_emps
	</select>
	<select id="findAll" resultType="Employee" databaseId="oracle">
	select id,last_name,email,gender  from t_emp where gender='女'
	</select>
</mapper>

--接口类

public interface EmpMapper {
	//1.使用if动态sql【拼接查询条件】
	List<Employee> findByConditionIf(Employee emp); 
	// 2.使用Choose动态sql【when otherwise】
	List<Employee> findByConditionChoose(Employee emp);
	//3.使用trim（字符串截取）动态sql【update表】
	Integer  updateByConditionTrim(Employee emp);
	//4.插入
	Integer addByCondition(Employee emp);
	//5,批量插入
	Integer bathByCondition(@Param("emps")List<Employee> emp);
	Integer bathByCondition2(@Param("emps")List<Employee> emp);
	Integer bathByCondition3(@Param("emps")List<Employee> emp);
	//6.批量查询
	List<Employee> findByIds(List<Integer> ids);	
	//7.查看使用不同的数据库环境之下相同的方法
	//接口就一个（可以动态选择不同数据库，根据databaseId选择数据库 ）
	List<Employee> findAll();
}

--测试类

public class EmpTest {
	SqlSession sqlSession;
	@Before
	public void init() {
		try {
			InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
			SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
			sqlSession = factory.openSession();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	@Test
	public void demo() {
		// 1.使用if动态sql【拼接查询条件】
		EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
		List<Employee> employee = mapper.findByConditionIf(new Employee(null, "五", "ww.com", "女"));
		for (Employee employee2 : employee) {
			System.out.println(employee2);
		}
		// 2.使用Choose动态sql【when otherwise】
		Employee emp = new Employee();
		emp.setId(1l);
		List<Employee> list = mapper.findByConditionChoose(emp);
		for (Employee list1 : list) {
			System.out.println(list1);
		}
	}
	@Test
	public void demo2() {
		// 3.使用trim（字符串截取）动态sql【update表】
		EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
		Employee emp = new Employee();
		Department dept = new Department();
		emp.setId(1L);
		emp.setLastName("鹅鹅鹅");
		emp.setEmail("110.com");
		emp.setGender("女");
		dept.setId(2l);
		emp.setDept(dept);
		Integer row = mapper.updateByConditionTrim(emp);
		sqlSession.commit();
		System.out.println(row > 0 ? "成功" : "失败");
	}	
	@Test
	public void demo3() {
		//4.插入
		EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
		Employee emp = new Employee();
		Department dept = new Department();
		emp.setId(1L);
		emp.setLastName("凄凄切切");
		emp.setEmail("123.com");
		emp.setGender("女");
		dept.setId(2l);
		emp.setDept(dept);
		Integer row = mapper.addByCondition(emp);
		sqlSession.commit();
		System.out.println(row > 0 ? "成功" : "失败");
	}	
	@Test
	public void demo4() {
		//5,批量插入
		EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);		 
		ArrayList<Employee> list = new ArrayList<>();
		list.add(new Employee("武松1","33.com","男",new Department(2l)));
		//list.add(new Employee("武松3","33.com","男",new Department(1l)));
		//方法一，insert into table values(),values(),values();
		//Integer row = mapper.bathByCondition(list);
		//方法二；oracle和MySQL：insert into table values();insert into table values();
		//Integer row = mapper.bathByCondition2(list);
		//方法三：Oracle批量查询
		Integer row = mapper.bathByCondition3(list);

		sqlSession.commit();
		System.out.println(row > 0 ? "成功" : "失败");
	}	
	@Test
	public void demo5() {
		//6.批量查询
		EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);			
		 List<Employee> list = mapper.findByIds(Arrays.asList(1,3,2));
		 for (Employee employee : list) {
			System.out.println(employee);
		}
	}
	@Test
	public void demo6() {
		EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);	
		List<Employee> findAll = mapper.findAll();
		for (Employee employee : findAll) {
			System.out.println(employee);
		}
	}
}