目录
前置准备
controller层
@RestController
@RequestMapping("emp")
@Api(value = "提供员工相关管理", tags = "Emp管理")
public class EmpController {
@Autowired
private EmpService empService;
@GetMapping("/")
@ApiOperation(value = "查找给定条件下的员工")
@ApiImplicitParams(value = {
@ApiImplicitParam(name = "name", value = "姓名",required = false,dataType = "String"),
@ApiImplicitParam(name = "salary", value = "工资",required = false,dataType = "Double"),
@ApiImplicitParam(name = "addr", value = "住址",required = false,dataType = "String"),
@ApiImplicitParam(name = "iphone", value = "电话",required = false,dataType = "String")
})
public List<Emp> getEmp(@RequestParam(value = "name") String name,
@RequestParam(value = "salary") String salary,
@RequestParam(value = "addr") String addr,
@RequestParam(value = "iphone") String iphone){
return empService.getEmp(name,salary,addr,iphone);
}
}
service业务接口层
public interface EmpService {
/**
* 查找给定条件下的员工信息
* @param name
* @param salary
* @param addr
* @param iphone
*/
List<Emp> getEmp(String name, String salary, String addr, String iphone);
}
service业务实现层
@Service
public class EmpServiceImpl implements EmpService {
@Autowired
private EmpMapper empMapper;
@Override
public List<Emp> getEmp(String name, String salary, String addr, String iphone) {
return empMapper.getEmp( name, salary, addr, iphone);
}
}
mapper接口层
@Repository
public interface EmpMapper {
/**
* 查找特定条件下的员工
*/
List<Emp> getEmp(@Param("name") String name,
@Param("salary") String salary,
@Param("addr") String addr,
@Param("iphone") String iphone);
}
1、<if>的使用
<?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="com.example.mybatis.mapper.EmpMapper">
<select id="getEmp" resultType="com.example.mybatis.pojo.Emp">
select * from emp where
<if test="name!=null and name!='' ">
name =#{name}
</if>
<if test="salary!=null and salary!= '' ">
and salary =#{salary}
</if>
<if test="addr!=null and addr!='' ">
and addr =#{addr}
</if>
<if test="iphone!=null and iphone!='' ">
and iphone =#{iphone}
</if>
</select>
</mapper>
注:条件为name=zhangsan时,查询到两条记录
注:条件为name=zhangsan且addr=广东深圳时,查询到一条记录
注意:当第一个参数为空时(name=""),就会报错,因为<if>标签原理是字符串拼接,当第一个参数为空,自动拼接第二个参数,就会出现这种情况,具体运行后执行的sql如下图所示:
解决办法:where 后面加1=1恒成立条件。如下所示
<?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="com.example.mybatis.mapper.EmpMapper">
<select id="getEmp" resultType="com.example.mybatis.pojo.Emp">
select * from emp where 1=1
<if test="name!=null and name!='' ">
name =#{name}
</if>
<if test="salary!=null and salary!= '' ">
and salary =#{salary}
</if>
<if test="addr!=null and addr!='' ">
and addr =#{addr}
</if>
<if test="iphone!=null and iphone!='' ">
and iphone =#{iphone}
</if>
</select>
</mapper>
2、<where>标签
<?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="com.example.mybatis.mapper.EmpMapper">
<select id="getEmp" resultType="com.example.mybatis.pojo.Emp">
select * from emp
<where>
<if test="name!=null and name!='' ">
name =#{name}
</if>
<if test="salary!=null and salary!= '' ">
and salary =#{salary}
</if>
<if test="addr!=null and addr!='' ">
and addr =#{addr}
</if>
<if test="iphone!=null and iphone!='' ">
and iphone =#{iphone}
</if>
</where>
</select>
</mapper>
以上<where>标签可以解决上述遇见的问题,理由是:
<where>标签里面有内容时,会自动生成where关键字,并去掉内容前多余的and 或 or
- 如果将and放到<if>标签内容后,会怎么样呢?
当and放到<if>标签内容后,会报错,上图指定表示and拼接到了后面。所以<where>标签里面有内容时,会自动生成where关键字,并去掉内容前多余的and 或 or,但是不能去掉后面的and或or;
3、<trim>标签
<?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="com.example.mybatis.mapper.EmpMapper">
<select id="getEmp" resultType="com.example.mybatis.pojo.Emp">
select * from emp
<trim prefix="where" suffixOverrides="and | or">
<if test="name!=null and name!='' ">
name =#{name} and
</if>
<if test="salary!=null and salary!= '' ">
salary =#{salary} and
</if>
<if test="addr!=null and addr!='' ">
addr =#{addr} and
</if>
<if test="iphone!=null and iphone!='' ">
iphone =#{iphone}
</if>
</trim>
</select>
</mapper>
完美解决上述问题。
4、<choose><when><otherwise>标签
<?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="com.example.mybatis.mapper.EmpMapper">
<select id="getEmp" resultType="com.example.mybatis.pojo.Emp">
select * from emp
<where>
<choose>
<when test="name!=null and name!='' ">
name = #{name}
</when>
<when test="salary!=null and salary!=''">
salary = #{salary}
</when>
<when test="addr!=null and addr!='' ">
addr = "%" #{addr} "%"
</when>
<when test="iphone!=null and iphone!='' ">
iphone = #{iphone}
</when>
<otherwise>
emp_id = 1
</otherwise>
</choose>
</where>
</select>
</mapper>
<choose><when><otherwise>标签像极了java里面得 if——else if——else
当<choose>标签里面的<when>标签有一个成立则只会拼接成立的<when>标签内容,如果<choose>标签里面的<when>标签内容都不成立,则拼接<otherwise>里面的内容。
5、<foreach>标签
举例一:批量删除(前端传递的是ids数组)
//controller层
@DeleteMapping("/")
@ApiOperation(value = "批量删除员工信息")
@ApiImplicitParam(name = "eids", value = "员工id数组",required = true,dataType = "Integer[]")
public int getEmp(@RequestParam(value = "eids") Integer[] eids ){
return empService.deleteEmps(eids);
}
//service业务接口层
/**
* 批量删除员工信息
* @param eids
* @return
*/
int deleteEmps(Integer[] eids);
//service业务实现层
@Override
public int deleteEmps(Integer[] eids) {
return empMapper.deleteEmps(eids);
}
//mapper接口层
/**
* 批量删除员工信息
*/
int deleteEmps(@Param("eids") Integer[] eids);
//mapper.xml层(方式一)
<!--批量删除员工信息-->
<delete id="deleteEmps">
delete from emp where emp_id in
<foreach collection="eids" item="eid" separator="," open="(" close=")">
#{eid}
</foreach>
</delete>
方式一:实际执行的sql为:delete from emp where emp_id in (1,2)
//mapper.xml层(方式二)
<!--批量删除员工信息-->
<delete id="deleteEmps">
delete from emp where
<foreach collection="eids" item="eid" separator="or">
emp_id = #{eid}
</foreach>
</delete>
方式二:实际执行的sql为:delete from emp where emp_id =1 or emp_id = 2
举例二:批量添加(前端传递的是集合list)
//controller层
@DeleteMapping("/")
@ApiOperation(value = "批量删除员工信息")
@ApiImplicitParam(name = "eids", value = "员工id数组",required = true,dataType = "Integer[]")
public int getEmp(@RequestParam(value = "eids") Integer[] eids ){
return empService.deleteEmps(eids);
}
//service业务接口层
/**
* 批量删除员工信息
* @param eids
* @return
*/
int deleteEmps(Integer[] eids);
//service业务实现层
@Override
public int deleteEmps(Integer[] eids) {
return empMapper.deleteEmps(eids);
}
//mapper接口层
/**
* 批量删除员工信息
*/
int deleteEmps(@Param("eids") Integer[] eids);
mapper.xml持久层
<insert id="insertManyEmp">
insert into emp values
<foreach collection="emps" item="emp" separator=",">
(#{emp.empId},#{emp.name},#{emp.salary},#{emp.addr},#{emp.iphone})
</foreach>
</insert>
这个循环操作实际执行的sql是:insert into emp values (?,?,?,?),(?,?,?,?),(?,?,?,?)
6、<sql>标签
<sql id="empColumns"> emp_id,name,salary,addr,iphone</sql>
<!--查找特定条件下的员工-->
<select id="getEmp" resultType="com.example.mybatis.pojo.Emp">
select <include refid="empColumns"></include> from emp
<where>
<choose>
<when test="name!=null and name!='' ">
name = #{name}
</when>
<when test="salary!=null and salary!=''">
salary = #{salary}
</when>
<when test="addr!=null and addr!='' ">
addr = "%" #{addr} "%"
</when>
<when test="iphone!=null and iphone!='' ">
iphone = #{iphone}
</when>
<otherwise>
emp_id = 1
</otherwise>
</choose>
</where>
</select>
使用<sql>标签的好处:单表复用。
<sql id="empColumns"> emp_id,name,salary,addr,iphone</sql>
<include refid="empColumns"></include>代替*进行查询