创建一个员工表:emp
CREATE TABLE `studymybatis`.`emp` (
`eid` INT NOT NULL AUTO_INCREMENT,
`emp_name` VARCHAR(20),
`age` INT,
`sex` CHAR(1),
`email` VARCHAR(20),
`did` VARCHAR(20),
PRIMARY KEY (`eid`));
INSERT INTO `studymybatis`.`emp` (`emp_name`, `age`, `sex`, `email`, `did`) VALUES ('张三', '21', '男', 'Zhang@163.com', 'D1');
INSERT INTO `studymybatis`.`emp` (`emp_name`, `age`, `sex`, `email`, `did`) VALUES ('李四', '23', '男', 'Lisi@qq.com', 'D2');
INSERT INTO `studymybatis`.`emp` (`emp_name`, `age`, `sex`, `email`, `did`) VALUES ('王二', '22', '男', 'Wang@163.com', 'D2');
INSERT INTO `studymybatis`.`emp` (`emp_name`, `age`, `sex`, `email`, `did`) VALUES ('小红', '20', '女', 'Hong@163.com', 'D1');
INSERT INTO `studymybatis`.`emp` (`emp_name`, `age`, `sex`, `email`, `did`) VALUES ('王梅', '22', '女', 'Mei@qq.com', 'D3');
在pojo包下编写对应的实体类对象Emp
一. if标签
if标签可以根据test属性所对应的表达式来决定标签中的内容是否需要拼接到SQL中
package mapper;
import pojo.Emp;
import java.util.List;
public interface EmpMapper {
//多条件查询
List<Emp> queryEmpByCondition(Emp emp);
}
<?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="mapper.EmpMapper"> <!--为mapper接口的全类名-->
<resultMap id="empMap" type="Emp">
<id property="eid" column="eid"></id>
<result property="empName" column="emp_name"></result>
<result property="age" column="age"></result>
<result property="sex" column="sex"></result>
<result property="email" column="email"></result>
</resultMap>
<select id="queryEmpByCondition" resultMap="empMap">
select * from emp where 1=1
<if test="empName != null and empName !='' ">
and emp_name = #{empName}
</if>
<if test="age != null and age !='' ">
and age = #{age}
</if>
<if test="sex != null and sex !='' ">
and sex = #{sex}
</if>
<if test="email != null and email !='' ">
and email = #{email}
</if>
</select>
</mapper>
import mapper.EmpMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.Emp;
import utils.SqlSessionUtils;
import java.util.List;
public class MapperTest {
@Test
public void testQuery(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
List<Emp> list1 = mapper.queryEmpByCondition(new Emp(null,"张三",21,'男',"Zhang@163.com"));
System.out.println(list1); //[Emp{eid=1, empName='张三', age=21, sex=男, email='Zhang@163.com', dept=null}]
List<Emp> list2 = mapper.queryEmpByCondition(new Emp(null,"张三",null,'男',""));
System.out.println(list2); //[Emp{eid=1, empName='张三', age=21, sex=男, email='Zhang@163.com', dept=null}]
}
}
二. where标签
当where标签有内容时,会自动生成where关键字,并且将内容前多余的and或or去掉;当where标签没有内容时,此时不会自动生成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="mapper.EmpMapper"> <!--为mapper接口的全类名-->
<resultMap id="empMap" type="Emp">
<id property="eid" column="eid"></id>
<result property="empName" column="emp_name"></result>
<result property="age" column="age"></result>
<result property="sex" column="sex"></result>
<result property="email" column="email"></result>
</resultMap>
<select id="queryEmpByCondition" resultMap="empMap">
select * from emp
<where>
<if test="empName != null and empName !='' ">
emp_name = #{empName}
</if>
<if test="age != null and age !='' ">
and age = #{age}
</if>
<if test="sex != null and sex !='' ">
and sex = #{sex}
</if>
<if test="email != null and email !='' ">
and email = #{email}
</if>
</where>
</select>
</mapper>
三. trim标签
<trim prefix="" prefixOverrides="" suffix="" suffixOverrides=""> </trim>
若trim标签中没有内容时,trim标签没有效果
prefix:将trim标签内容前面添加指定内容;suffix:将trim标签内容后面添加指定内容;
prefixOverrides:将trim标签内容前面去掉指定内容;suffixOverrides:将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="mapper.EmpMapper"> <!--为mapper接口的全类名-->
<resultMap id="empMap" type="Emp">
<id property="eid" column="eid"></id>
<result property="empName" column="emp_name"></result>
<result property="age" column="age"></result>
<result property="sex" column="sex"></result>
<result property="email" column="email"></result>
</resultMap>
<select id="queryEmpByCondition" resultMap="empMap">
select * from emp
<trim prefix="where" prefixOverrides="and|or">
<if test="empName != null and empName !='' ">
emp_name = #{empName}
</if>
<if test="age != null and age !='' ">
and age = #{age}
</if>
<if test="sex != null and sex !='' ">
and sex = #{sex}
</if>
<if test="email != null and email !='' ">
and email = #{email}
</if>
</trim>
</select>
</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="mapper.EmpMapper"> <!--为mapper接口的全类名-->
<resultMap id="empMap" type="Emp">
<id property="eid" column="eid"></id>
<result property="empName" column="emp_name"></result>
<result property="age" column="age"></result>
<result property="sex" column="sex"></result>
<result property="email" column="email"></result>
</resultMap>
<select id="queryEmpByCondition" resultMap="empMap">
select * from emp
<trim prefix="where" suffixOverrides="and|or">
<if test="empName != null and empName !='' ">
emp_name = #{empName} and
</if>
<if test="age != null and age !='' ">
age = #{age} and
</if>
<if test="sex != null and sex !='' ">
sex = #{sex} and
</if>
<if test="email != null and email !='' ">
email = #{email}
</if>
</trim>
</select>
</mapper>
四. choose、when、otherwise标签
choose、when、otherwise标签相当于Java中的 if...else if ... else,只要有一个条件满足就会去执行相应的sql语句
when至少有一个,otherwise最多只能有一个
package mapper;
import pojo.Emp;
import java.util.List;
public interface EmpMapper {
List<Emp> queryEmpByChoose(Emp emp);
}
<?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="mapper.EmpMapper"> <!--为mapper接口的全类名-->
<resultMap id="empMap" type="Emp">
<id property="eid" column="eid"></id>
<result property="empName" column="emp_name"></result>
<result property="age" column="age"></result>
<result property="sex" column="sex"></result>
<result property="email" column="email"></result>
</resultMap>
<select id="queryEmpByChoose" resultMap="empMap">
select * from emp
<where>
<choose>
<when test="empName != null and empName !='' ">
emp_name = #{empName}
</when>
<when test="age != null and age !='' ">
age = #{age}
</when>
<when test="sex != null and sex !='' ">
sex = #{sex}
</when>
<when test="email != null and email !='' ">
email = #{email}
</when>
<otherwise>
did=1
</otherwise>
</choose>
</where>
</select>
</mapper>
import mapper.EmpMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.Emp;
import utils.SqlSessionUtils;
import java.util.List;
public class MapperTest {
@Test
public void testQuery(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
List<Emp> list1 = mapper.queryEmpByChoose(new Emp(null,"张三",21,'男',"Zhang@163.com"));
System.out.println(list1); //[Emp{eid=1, empName='张三', age=21, sex=男, email='Zhang@163.com', dept=null}]
List<Emp> list2 = mapper.queryEmpByChoose(new Emp(null,"张三",null,null,""));
System.out.println(list2); //[Emp{eid=1, empName='张三', age=21, sex=男, email='Zhang@163.com', dept=null}]
}
}
五. foreach标签
<foreach collection=" " item=" " separator=" " open=" " close=" "> </foreach>
collection:设置需要循环的数组或集合;item:表示数组或集合的每一个数据;separator:循环体之间的分隔符;open:foreach标签所循环的所有内容的开始符;close:foreach标签所循环的所有内容的结束符
(1)使用数组实现批量删除
package mapper;
import org.apache.ibatis.annotations.Param;
public interface EmpMapper {
//通过数组实现批量删除
void deleteMoreByArray(@Param("eids") Integer[] eids);
}
<?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="mapper.EmpMapper"> <!--为mapper接口的全类名-->
<!--方法一-->
<delete id="deleteMoreByArray">
delete from emp where eid in
<foreach collection="eids" item="eid" separator="," open="(" close=")">
#{eid}
</foreach>
</delete>
<!--方法二
<delete id="deleteMoreByArray">
delete from emp where
<foreach collection="eids" item="eid" separator="or">
eid = #{eid}
</foreach>
</delete>
-->
</mapper>
import mapper.EmpMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import utils.SqlSessionUtils;
public class MapperTest {
@Test
public void testDeleteMore(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
mapper.deleteMoreByArray(new Integer[]{2,3,4});
}
}
(2)使用集合实现批量添加
package mapper;
import org.apache.ibatis.annotations.Param;
import pojo.Emp;
import java.util.List;
public interface EmpMapper {
//通过集合实现批量添加
void insertMoreByList(@Param("emps") List<Emp> emps);
}
<?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="mapper.EmpMapper"> <!--为mapper接口的全类名-->
<insert id="insertMoreByList">
insert into emp values
<foreach collection="emps" item="emp" separator=",">
(null,#{emp.empName},#{emp.age},#{emp.sex},#{emp.email},null)
</foreach>
</insert>
</mapper>
import mapper.EmpMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.Emp;
import utils.SqlSessionUtils;
import java.util.Arrays;
import java.util.List;
public class MapperTest {
@Test
public void testInsert(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp emp1 = new Emp(null,"李四", 23, '男', "Lisi@qq.com");
Emp emp2 = new Emp(null,"王二", 22, '男', "Wang@163.com");
Emp emp3 = new Emp(null,"小红", 20, '女', "Hong@163.com");
List<Emp> emps = Arrays.asList(emp1,emp2,emp3);
mapper.insertMoreByList(emps);
}
}
六. sql标签
设置SQL片段:<sql id="empColumns">eid,emp_name empName,age,sex,email</sql>
引用SQL片段:<include refid="empColumns"></include>
package mapper;
import pojo.Emp;
import java.util.List;
public interface EmpMapper {
//多条件查询
List<Emp> queryEmpByCondition(Emp emp);
}
<?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="mapper.EmpMapper"> <!--为mapper接口的全类名-->
<sql id="empColumns">eid,emp_name empName,age,sex,email</sql>
<select id="queryEmpByCondition" resultType="Emp">
select <include refid="empColumns"></include> from emp
<where>
<if test="empName != null and empName !='' ">
emp_name = #{empName}
</if>
<if test="age != null and age !='' ">
and age = #{age}
</if>
<if test="sex != null and sex !='' ">
and sex = #{sex}
</if>
<if test="email != null and email !='' ">
and email = #{email}
</if>
</where>
</select>
</mapper>
import mapper.EmpMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.Emp;
import utils.SqlSessionUtils;
import java.util.List;
public class MapperTest {
@Test
public void testQuery(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
List<Emp> list1 = mapper.queryEmpByCondition(new Emp(null,"张三",21,'男',"Zhang@163.com"));
System.out.println(list1); //[Emp{eid=1, empName='张三', age=21, sex=男, email='Zhang@163.com', dept=null}]
List<Emp> list2 = mapper.queryEmpByCondition(new Emp(null,"张三",null,'男',""));
System.out.println(list2); //[Emp{eid=1, empName='张三', age=21, sex=男, email='Zhang@163.com', dept=null}]
}
}