Mybatis使用注解实现三表关联动态SQL、模糊、分页查询

Mybatis实现sql语句的方式主要有两种：
一种是通过xml配置文件实现
另一种是直接在mapper层下接口文件中使用注解实现

1. 实现三表关联查询

1.1 配置文件实现

实体类Emps.java

public class Emps{
	private int empno;
	private String ename;
	private String job;
	private Dept dept;
	private Position position;

	public Emps(){}
	
	public int getEmpno(){return empno;}
	public void setEmpno(){this.empno=empno;}
	public String getEname(){return ename;}
	public void setEname(){this.ename=ename;}
	public String getJob(){return job;}
	public void setJob(){this.job=job;}
	public Dept getDept(){return dept;}
	public void setDept(){this.dept=dept;}
	public Position getPosition(){return position;}
	public void setPosition(){this.position=position;}
	
}

mapper接口

public interface EmpMapper{
	List<Emps> queryEmp();
}

mapper配置文件

<?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="cn.khue.mapper.EmpMapper">
	<resultMap id="rm" type="emp">
		<id column="empno" property="empno"></id>
		<result column="ename" property="ename"></result>
		<result column="job" property="job"></result>
		<association property="dept" javaType="dept">
			<id column="deptno" property="deptno"></id>
			<result column="deptname" property="deptname"></result>
		</association>
		<association property="position" javaType="position">
			<id column="posid" property="posid"></id>
			<result column="pname" property="pname"></result>
		</association>
	</resultMap>
	<select id="queryEmp" resultMap="rm">
		select e.empno, e.ename, e.job, d.deptname, p.pname
		from emp e join dept d on e.deptno=d.deptno join position p on e.posid=p.posid
	</select>
</mapper>

1.2 注解实现

实体类Emps.java

public class Emps{
	private int empno;
	private String ename;
	private String job;
	private String dname;
	private String pname;

	public Emps(){}
	
	public int getEmpno(){return empno;}
	public void setEmpno(){this.empno=empno;}
	public String getEname(){return ename;}
	public void setEname(){this.ename=ename;}
	public String getJob(){return job;}
	public void setJob(){this.job=job;}
	public String getDname(){return dname;}
	public void setDname(){this.dname=dname;}
	public String getPname(){return panme;}
	public void setPname(){this.pname=pname;}
	
}

mapper接口

public interface EmpMapper{
	@Select("select e.empno, e.ename, e.job, d.deptname, p.pname"+
	"from emp e, dept d, position p"+
	"where e.deptno=d.deptno and e.posid=p.posid")
	List<Emps> queryEmp();
}

2. 实现三表动态sql语句、模糊、分页查询

2.1 xml实现

public interface EmpMapper{
	List<Emps> queryEmp(int empno, String ename, String job, String dname, String pname, int startPage, int pageSize);
}

<?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="cn.khue.mapper.EmpMapper">
	<resultMap id="rm" type="emp">
		<id column="empno" property="empno"></id>
		<result column="ename" property="ename"></result>
		<result column="job" property="job"></result>
		<association property="dept" javaType="dept">
			<id column="deptno" property="deptno"></id>
			<result column="deptname" property="deptname"></result>
		</association>
		<association property="position" javaType="position">
			<id column="posid" property="posid"></id>
			<result column="pname" property="pname"></result>
		</association>
	</resultMap>
	<select id="queryEmp" resultMap="rm">
		select e.empno, e.ename, e.job, d.deptname, p.pname
		from emp e join dept d on e.deptno=d.deptno join position p on e.posid=p.posid
		<where>
			<if test="param1 != null and param1 !=''">
				<bind name="empno" value="'%'+param1+'%'"></bind>
				and e.empno like #{empno}
			</if>
			<if test="param2 != null and param2 != ''">
				<bind name="ename" value="'%'+param2+'%'"></bind>
				and e.ename like #{ename}
			</if>
			<if test="param3 != null and param3 != ''">
				<bind name="job" value="'%'+param3+'%'"></bind>
				and e.job like #{job}
			</if>
			<if test="param4 != null and param4 != ''">
				<bind name="deptname" value="'%'+param4+'%'"></bind>
				and d.deptname like #{deptname}
			</if>
			<if test="param5 != null and param5 != ''">
				<bind name="pname" value="'%'+param5+'%'"></bind>
				and p.pname like #{pnmae}
			</if>
		</where>
		limit #{param6}, #{param7}
	</select>
</mapper>

2.2 注解实现一

public interface EmpMapper{
	@Select("<script>select e.empno, e.ename, e.job, d.deptname, p.pname"+
	"from emp e, dept d, position p"+
	"where e.deptno=d.deptno and e.posid=p.posid"+
	"<if test='param1 != null and param1 != \"\"'>and e.empno like concat('%',#{param1},'%')</if>"+
	"<if test='param2 != null and param2 != \"\"'>and e.ename like concat('%',#{param2},'%')</if>"+
	"<if test='param3 != null and param3 != \"\"'>and e.job like concat('%',#{param3},'%')</if>"+
	"<if test='param4 != null and param4 != \"\"'>and d.deptname like concat('%',#{param4},'%')</if>"+
	"<if test='param5 != null and param5 != \"\"'>and p.pname like concat('%',#{param5},'%')</if>"+
	"limit #{param6}, #{param7}</script>")
	List<Emps> queryEmp(String empno, String ename, String job, String dname, String pname, int startPage, int pageSize);
}

2.3 注解实现二（提前封装%）

对于xml实现与注解实现一，都不需要提前将用于模糊查询的%封装进参数中

int empno=1;
String ename="khue";
String job="coding";
String dname="programmer";
Stirng pname="server" 

而对于注解实现中，也可以提前将%封装进参数

String empno="%1%";
String ename="%khue%";
String job="%coding%";
String dname="%programmer%";
Stirng pname="%server%" 

public interface EmpMapper{
	@Select("<script>select e.empno, e.ename, e.job, d.deptname, p.pname"+
	"from emp e, dept d, position p"+
	"where e.deptno=d.deptno and e.posid=p.posid"+
	"<if test='param1 != \"%null%\" and param1 != \"%%\"'>and e.empno like #{param1}</if>"+
	"<if test='param2 != \"%null%\" and param2 != \"%%\"'>and e.ename like #{param2}</if>"+
	"<if test='param3 != \"%null%\" and param3 != \"%%\"'>and e.job like #{param3}</if>"+
	"<if test='param4 != \"%null%\" and param4 != \"%%\"'>and d.deptname like #{param4}</if>"+
	"<if test='param5 != \"%null%\" and param5 != \"%%\"'>and p.pname like #{param5}</if>"+
	"limit #{param6}, #{param7}</script>")
	List<Emps> queryEmp(String empno, String ename, String job, String dname, String pname, int startPage, int pageSize);
}

2.4 注意

在IDEA中使用Mybaits向MySQL传入参数时，MySQL为int、Date类型的皆可以直接使用String类型传入