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

最新推荐文章于 2022-04-03 19:59:35 发布

大胡子叔叔_

最新推荐文章于 2022-04-03 19:59:35 发布

阅读量686

点赞数

分类专栏： MyBatis框架

本文链接：https://blog.csdn.net/panhaigang123/article/details/78639321

版权

MyBatis框架专栏收录该内容

10 篇文章 0 订阅

订阅专栏

►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;
	}
	@Override
	public 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;
		}
		
		
		//传入一个集合查询2
		public 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.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("龙大炮");
		student.setAddress("杭州");
		fm.updateStudent(student);
		session.commit();
	}
	
	@org.junit.Test
	public 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);
	}
}

============================================================================

package cn.et.wk.manager.manageruser.dao;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;

import cn.et.wk.manager.usermanager.entity.Mutualrecord;


@Mapper
public interface MutualrecordMapper {
	

	/**
	 * 查询数据
	 * 映射到 MutualrecordProvider中queryMutualrecord方法此方法是动态sql
	 * @param map
	 * @return
	 */
	@SelectProvider(type=MutualrecordProvider.class,method="queryMutualrecord")
	List<Mutualrecord> queryMutualrecord(@Param("map")Map<String,String> map);
	
	
	/**
	 * 查询总记录数
	 * @return
	 */
	@Select("select count(rowid) from tb_usermanagement_mutualrecord")
	public int queryMutualrecordCount();
	
	/**
	 * 删除
	 * @param falg
	 * @return
	 */
	@Delete("delete from tb_usermanagement_mutualrecord where MUTUALID=#{0}")
	int deleteByPrimaryKey(int falg);
	
	
}

package cn.et.wk.manager.manageruser.dao;

import java.util.Map;

import org.apache.ibatis.jdbc.SQL;


public class MutualrecordProvider {
	
	
	public String queryMutualrecord(Map<String,Map<String,String>> map){
		Map<String, String> map2 = map.get("map");
		
		//开始位置和结束位置
		String page = map2.get("page");
		String rows = map2.get("rows");
		int start=(Integer.parseInt(page)-1)*(Integer.parseInt(rows))+1;
		int end=(Integer.parseInt(page))*(Integer.parseInt(rows));
		
		String beform=map2.get("beform");
		String mutuali=map2.get("mutualid");
		String sort=map2.get("sort");
		String as=map2.get("as");
		
		SQL sql=new SQL();
		sql.SELECT("ttt.mutualid as mutualId");
		sql.SELECT("tot.OBJECT_TYPE_NAME as mid");
		sql.SELECT("ttt.username as beFrom");
		sql.SELECT("ttt.bb as PrimaryUser");
		sql.SELECT("ttt.evaluate as evaluate");
		sql.SELECT("ttt.abilityvalue as abilityValue");
		sql.SELECT("ttt.edittime as timeBouns");
		sql.FROM("(select tt.belongstotype,t.mutualid,tt.username,t.username as bb,tt.edittime,tt.abilityvalue,tt.evaluate,rownum rn from "+
"(select * from tb_usermanagement_mutualrecord tum inner join TB_USER_INFO tui on tum.bycritics=tui.userid) t "+
   "inner join (select * from tb_usermanagement_mutualrecord tum inner join TB_USER_INFO tui on tum.commentator=tui.userid) tt "+
     "on t.mutualid=tt.mutualid) ttt inner join tb_object_type tot on ttt.belongstotype=tot.OBJECT_TYPE_ID");
		sql.WHERE("ttt.rn>="+start+" and ttt.rn<="+end);
		if(beform==null&&mutuali==null&&sort==null&&as==null){
			sql.ORDER_BY("ttt.mutualid asc");
		}
		//评论人查询
		if(beform!=null&&!"".equals(beform)){
			sql.AND();
			sql.WHERE("ttt.username='"+beform+"'");
		}
		//id查询
		if(mutuali!=null&&!"".equals(mutuali)){
			sql.AND();
			sql.WHERE("ttt.mutualid='"+mutuali+"'");
		}
		
		
		
		//默认降序升序排序
		if(sort!=null&&!"".equals(sort)&&sort.equals("mtid")&&"dp".equals(as)){
			sql.ORDER_BY("ttt.mutualid desc");
		}
		if(sort!=null&&!"".equals(sort)&&sort.equals("mtid")&&"pi".equals(as)){
			sql.ORDER_BY("ttt.mutualid asc");
		}
		//互评时间降序升序排序
		if(sort!=null&&!"".equals(sort)&&sort.equals("MT")&&"dp".equals(as)){
			sql.ORDER_BY("ttt.edittime desc");
		}
		if(sort!=null&&!"".equals(sort)&&sort.equals("MT")&&"pi".equals(as)){
			sql.ORDER_BY("ttt.edittime asc");
		}
		
		//评价降序升序排序
		if(sort!=null&&!"".equals(sort)&&sort.equals("SOM")&&"dp".equals(as)){
			sql.ORDER_BY("ttt.evaluate desc");
		}
		if(sort!=null&&!"".equals(sort)&&sort.equals("SOM")&&"pi".equals(as)){
			sql.ORDER_BY("ttt.evaluate asc");
		}
		//互评值降序升序排序
		if(sort!=null&&!"".equals(sort)&&sort.equals("MV")&&"dp".equals(as)){
			sql.ORDER_BY("ttt.abilityvalue desc");
		}
		if(sort!=null&&!"".equals(sort)&&sort.equals("MV")&&"pi".equals(as)){
			sql.ORDER_BY("ttt.abilityvalue asc");
		}
		
		return sql.toString();
	}
}

大胡子叔叔_

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mybatis的动态sql-----注解

►MyBatis的强大特性之一便是它的动态SQL。如果你有使用JDBC 或其他类似框架的经验，你就能体会到根据不同条件拼接SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格，还要注意省掉列名列表最后的逗号。利用动态SQL 这一特性可以彻底摆脱这种痛苦。►通常使用动态SQL 不可能是独立的一部分,MyBatis当然使用一种强大的动态 SQL 语言来改进这种情形,这种语言可以被用在任意的 SQ...
复制链接

扫一扫