Mybatis的动态sql-----注解

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

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

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

案例

实体类

package cn.easytop.lesson04.anno;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;}@Overridepublic String toString() {return "Student [address=" + address + ", age=" + age + ", sex=" + sex+ ", sid=" + sid + ", sname=" + sname + "]";}}

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 class="cn.easytop.lesson04.anno.StudentMapper"/>  </mappers></configuration>

接口    定义注解

package cn.easytop.lesson04.anno;import java.util.List;import java.util.Map;import org.apache.ibatis.annotations.Param;import org.apache.ibatis.annotations.Select;import org.apache.ibatis.annotations.SelectProvider;import org.apache.ibatis.annotations.UpdateProvider;import org.apache.ibatis.jdbc.SQL;public interface StudentMapper {//定义一个内部类  类中方法是注解对应的接口实现static class StudentProvider{//查询方法一public String queryStudentSql(Map map){Student student=(Student)map.get("stu");String sql="select * from student where 1=1 ";if(student.getSname()!=null&&!"".equals(student.getSname())){student.setSname("%"+student.getSname()+"%");sql+=" and sname like #{stu.sname}";}if(student.getAddress()!=null&&!"".equals(student.getAddress())){student.setAddress("%"+student.getAddress()+"%");sql+=" and address like #{stu.address}";}return sql;}//查询方法二public String queryStudentSql1(Map map){Student student=(Student)map.get("stu");SQL sql=new SQL();sql.SELECT("*").FROM("student");if(student.getSname()!=null&&!"".equals(student.getSname())){student.setSname("%"+student.getSname()+"%");sql.WHERE(" sname like #{stu.sname}");}if(student.getAddress()!=null&&!"".equals(student.getAddress())){student.setAddress("%"+student.getAddress()+"%");sql.AND();sql.WHERE(" address like #{stu.address}");}return sql.toString();}//更新的方法 public String updateStudentsql(Map map){Student student =(Student)map.get("stu");SQL sql=new SQL();sql.UPDATE("student");if(student.getSname()!=null&&!"".equals(student.getSname())){sql.SET(" sname=#{stu.sname}");}if(student.getAddress()!=null&&!"".equals(student.getAddress())){sql.SET(" address=#{stu.address}");}sql.WHERE(" sid=#{stu.sid}");return sql.toString();}//传入一个集合查询1  比较复杂public String queryStudentbyAnyGradesql1(Map map){String falg = null;List gradeList=(List)map.get("list");String sql="select * from student where 1=1 ";if(gradeList.size()>=0){int k=0;for(int i=0;i<gradeList.size();i++){if(i==gradeList.size()-1){if(k==0){falg=(String) gradeList.get(i);}else{falg+=gradeList.get(i);}}else{if(k==0){falg=gradeList.get(i)+",";k++;}else{falg+=gradeList.get(i)+",";}}}System.out.println(falg);sql+="and gid in ("+falg+")";}return sql;}//传入一个集合查询2public String queryStudentbyAnyGradesql2(Map map){String falg = "";List gradeList=(List)map.get("list");String sql="select * from student where 1=1 ";if(gradeList.size()>=0){for(int i=0;i<gradeList.size();i++){if(i==gradeList.size()-1){falg+=gradeList.get(i);}else{falg+=gradeList.get(i)+",";}}sql+="and gid in ("+falg+")";}return sql;}}//查询学生@SelectProvider(type=StudentProvider.class,method="queryStudentSql1")public List<Student> queryStudent(@Param("stu") Student student);//根据性别查@Select("<script> select * from student where 1=1 "+"<choose>"+"<when test=\"sex!=null\">"+"and sex=#{sex}"+"</when>"+"<otherwise>"+" and sex=1"+"</otherwise>"+"</choose></script>")public List<Student> queryBySex(@Param("sex") Integer sex);//更新@UpdateProvider(type=StudentProvider.class,method="updateStudentsql")public void updateStudent(@Param("stu") Student student);//传入一个集合查询@SelectProvider(type=StudentProvider.class,method="queryStudentbyAnyGradesql2")public List<Student> queryStudentbyAnyGrade(@Param("list") List<String> gradeList);}

测试类

package cn.easytop.lesson04.anno;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/anno/mybatis.xml";InputStream inputStream = Resources.getResourceAsStream(resource);//工厂类SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);SqlSession openSession = sqlSessionFactory.openSession();//session操作的是 指向sql语句的一个唯一表示符return openSession;}//模糊查询@org.junit.Testpublic 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.Testpublic 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.Testpublic void testUpdateInterface() throws IOException{SqlSession session=getSession();//获取FoodMapper的实现类StudentMapper fm=session.getMapper(StudentMapper.class);Student student =new Student();student.setSid(1);student.setSname("龙大炮");student.setAddress("杭州");fm.updateStudent(student);session.commit();}@org.junit.Testpublic void testForEachInterface() throws IOException{SqlSession session=getSession();//获取FoodMapper的实现类StudentMapper fm=session.getMapper(StudentMapper.class);List<String> list=new ArrayList<String>();list.add("1");list.add("2");list.add("3");List<Student> l=fm.queryStudentbyAnyGrade(list);System.out.println(l);}}