Mybatis的动态sql-----xml

►MyBatis的强大特性之一便是它的动态SQL。如果你有使用JDBC 或其他类似框架的经验，你就能体会到根据不同条件拼接SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格，还要注意省掉列名列表最后的逗号。利用动态SQL 这一特性可以彻底摆脱这种痛苦。

►通常使用动态SQL 不可能是独立的一部分,MyBatis当然使用一种强大的动态 SQL 语言来改进这种情形,这种语言可以被用在任意的 SQL 映射语句中。

►动态SQL 元素和使用JSTL 或其他类似基于XML 的文本处理器相似。在MyBatis之前的版本中,有很多的元素需要来了解。MyBatis 3 大大提升了它们,现在用不到原先一半的元素就可以了。MyBatis采用功能强大的基于OGNL 的表达式来消除其他元素。

案例

实体类

package cn.easytop.lesson04.xml;


public class Student {
	private int sid;
	private String sname;
	private Integer age;
	private Integer sex;
	private String address;
	
	
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}
	public Integer getSex() {
		return sex;
	}
	public void setSex(Integer sex) {
		this.sex = sex;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public int getSid() {
		return sid;
	}
	public void setSid(int sid) {
		this.sid = sid;
	}
	public String getSname() {
		return sname;
	}
	public void setSname(String sname) {
		this.sname = sname;
	}
	@Override
	public String toString() {
		return "Student [address=" + address + ", age=" + age + ", sex=" + sex
				+ ", sid=" + sid + ", sname=" + sname + "]";
	}
}

接口

package cn.easytop.lesson04.xml;

import java.util.List;

import org.apache.ibatis.annotations.Param;


public interface StudentMapper {
	
	//查询学生
	public List<Student> queryStudent(Student student);
	
	
	//根据性别查
	public List<Student> queryBySex(@Param("sex") Integer sex);
	
	
	//更新
	public void updateStudent(Student student);
	
	//范围查询
	public List<Student> queryStudentbyAnyGrade(@Param("gradeList") List<String> gradeList);
}

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>
	<!-- mybatis的核心配置文件
		1.数据库的连接的信息（连接池）
	 -->
	<properties resource="oracle.properties"></properties>
	<!-- 取别名 -->
	<typeAliases>
		<!-- 在此包下取别名    默认为类名的首字母小写 -->
		<package name="cn.easytop.lesson04.xml"/>
	</typeAliases>
  <environments default="development">
    <environment id="development">
    <!-- 事务管理器  默认使用jdbc事务 -->
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
        <property name="driver" value="${driverClass}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${username1}"/>
        <property name="password" value="${password}"/>
      </dataSource>
    </environment>
  </environments>
  <mappers>
  	<mapper resource="cn/easytop/lesson04/xml/StudentMapper.xml"/>
  </mappers>
</configuration>

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

<!-- 
	namespace="cn.easytop.lesson04.xml.StudentMappe" 与接口绑定 

-->
<mapper namespace="cn.easytop.lesson04.xml.StudentMapper">
 	<!-- 
 		查询
	 	<select id="queryStudent" resultType="student">
	 		select * from student where 1=1
	 		<if test="sname!=null">
	 		 	and sname like '%${sname}%'
	 		</if>
	 		<if test="address!=null">
				and address like '%${address}%' 		
	 		</if>
	 	</select>
	 	
 		trim  灵活性更高
 				prefix配置的参数会被添加在sql语句的开始的地方
 				prefixOverrides sql语句开始的出现的参数 会被prefix配置的参数 覆盖
 		<select id="queryStudent" resultType="student">		
	 		select * from student
	 		<trim prefix="where" prefixOverrides="and">     //第一次出现and的地方会被where替代  以后出现的and的地方就追加在后面
		 		<if test="sname!=null">
		 		 	and sname like '%${sname}%'  //${}会在执行前替代掉    #{}先用?替代，在语句执行之后 会执行and sname like '%?%'
		 		</if>
		 		<if test="address!=null">
					and address like '%${address}%' 		
		 		</if>
	 		</trim>
 	</select>       //select * from student where sname like '%xxx%'and address like '%xxx%'
 	 -->
 	<select id="queryStudent" resultType="student">
 		select * from student
 		<where>
	 		<if test="sname!=null">
	 		 	and sname like '%${sname}%'
	 		</if>
	 		<if test="address!=null">
				and address like '%${address}%' 		
	 		</if>
 		</where>
 	</select>
 	
 	
 	<!-- 
 		两者选其一
 		<choose><when><otherwise>====>if()else{}
 		
 	 -->
 	<select id="queryBySex" resultType="student">
 		select * from student where 1=1
 		<choose>
	 		<when test="sex!=null">
	 		 	and sex =#{sex}
	 		</when>
	 		<otherwise>
				and sex =1
	 		</otherwise>
 		</choose>
 	</select>
 	
 	
 	<!-- 修改
	 	<update id="updateStudent">
	 		update student
	 		把前空格替换成  set   把后逗号替换成空格
	 		<trim prefix="set" prefixOverrides="" suffix="" suffixOverrides=",">
		 		<if test="sname!=null">
		 			sname=#{sname},
		 		</if>
		 		<if test="age!=null">
		 			age=#{age},
		 		</if>
		 		<if test="sex!=null">
		 			sex=#{sex}
		 		</if>
	 		</trim>
	 		where sid=#{sid}
	 	</update>
	 	sql语句：update student set xxx=xxx,yyy=yyy,zzz=zzz where sid=2
 	 -->
 	<update id="updateStudent">
 		update student
 		<set>     //会自动加一个set 并智能的根据条件判断是否需要逗号
	 		<if test="sname!=null">
	 			sname=#{sname},
	 		</if>
	 		<if test="age!=null">
	 			age=#{age},
	 		</if>
	 		<if test="sex!=null">
	 			sex=#{sex}
	 		</if>
 		</set>
 		where sid=#{sid}
 	</update>
 	
 	
 	
 	<!-- 
 		传入班级集合查询学生
 		collection="gradeList"   传入的集合
 		open="where gid in("   循环开始之前追加的sql
 		close=")"    循环开始之后追加的sql
 		separator="," 非最后一次的循环的值上添加逗号
 		item="gid"  指定变量名
 		sql语句：select * from student where gid in(xx,xx,xx)
 	 -->
 	 <select id="queryStudentbyAnyGrade" resultType="student">
 	 	select * from student
 	 	<foreach item="gid" collection="gradeList" open="where gid in(" close=")" separator=",">
 	 		${gid}
 	 	</foreach>
 	 </select>
</mapper>

测试类

package cn.easytop.lesson04.xml;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
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;


public class Test {
	public static SqlSession getSession() throws IOException{
		String resource = "cn/easytop/lesson04/xml/mybatis.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		//工厂类
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		SqlSession openSession = sqlSessionFactory.openSession();
		//session操作的是 指向sql语句的一个唯一表示符
		return openSession;
	}
	
	//模糊查询
	@org.junit.Test
	public void testXmlInterface() throws IOException{
		SqlSession session=getSession();
		//获取FoodMapper的实现类
		StudentMapper fm=session.getMapper(StudentMapper.class);
		Student student=new Student();
		student.setSname("小");
		List<Student> queryFood = fm.queryStudent(student);
		System.out.println(queryFood);
	}
	
	//根据性别查询（二者选其一）
	@org.junit.Test
	public void testChooseInterface() throws IOException{
		SqlSession session=getSession();
		//获取FoodMapper的实现类
		StudentMapper fm=session.getMapper(StudentMapper.class);
		Integer sex=0;
		List<Student> queryFood = fm.queryBySex(sex);
		System.out.println(queryFood);
	}
	
	//修改
	@org.junit.Test
	public void testUpdateInterface() throws IOException{
		SqlSession session=getSession();
		//获取FoodMapper的实现类
		StudentMapper fm=session.getMapper(StudentMapper.class);
		Student student =new Student();
		student.setSid(1);
		student.setSname("龙大炮1111");
		fm.updateStudent(student);
		session.commit();
	}
	
	//出入集合 范围查询
	@org.junit.Test
	public void testForEachInterface() throws IOException{
		SqlSession session=getSession();
		//获取FoodMapper的实现类
		StudentMapper fm=session.getMapper(StudentMapper.class);
		List list=new ArrayList();
		list.add("1");
		list.add("2");
		List<Student> l=fm.queryStudentbyAnyGrade(list);
		System.out.println(l);
	}
	
}