动态SQL
1.项目目录
2.EmpMapper.java
package com.atguigu.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.atguigu.bean.Emp;
public interface EmpMapper {
//根据eid,ename,age,sex多条件查询员工信息
List<Emp> getEmpListByMoreTJ(Emp emp);
//根据eid,ename,age,sex其中一个查询员工信息
List<Emp> getEmpListByChoose(Emp emp);
//添加员工信息，将0|1-->女|男
void insertEmp(Emp emp);
//根据eid所组成的字符串进行批量删除
void deleteMoreEmp(String eids);
//通过list集合实现批量删除
//void deleteMoreByList(List<Integer> eids);
void deleteMoreByList(@Param("eids")List<Integer> eids);
//批量添加
void insertMoreByArray(Emp[] emps);
//批量修改
void updateMoreByArray(@Param("emps")Emp[] emps);
}
3.EmpMapper.xml
<?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.atguigu.mapper.EmpMapper">
<!--
<sql id=""></sql>:设置一段SQL片段，即公共SQL，可以被当前映射文件中所有的SQL语句所访问
-->
<sql id="empColumns">
select eid,ename,age,sex,did from emp
</sql>
<!-- List<Emp> getEmpListByMoreTJ(); -->
<!--
多条件查询：若页面中没有设置此条件，SQL语句中一定不能有该条件
-->
<!--
<if test=""></if>:通过test表达式拼接SQL
<where>:添加where关键字并去掉多余的and
<trim prefix="" suffix="" prefixOverrides="" suffixOverrides="">:截取并拼接
prefix:在操作的SQL语句前加入某些内容
suffix:在操作的SQL语句后加入某些内容
prefixOverrides:把操作的SQL语句前的某些内容去掉
suffixOverrides:把操作的SQL语句后的某些内容去掉
-->
<select id="getEmpListByMoreTJ" resultType="Emp">
<!-- select eid,ename,age,sex,did from emp
where 1 = 1
<if test="eid != null">
and eid=#{eid}
</if>
<if test="ename != null and ename != ''">
and ename=#{ename}
</if>
<if test="age != null">
and age=#{age}
</if>
<if test="sex == '1' or sex == '0'">
and sex=#{sex}
</if> -->
<!-- select eid,ename,age,sex,did from emp
<where>
<if test="eid != null">
and eid=#{eid}
</if>
<if test="ename != null and ename != ''">
and ename=#{ename}
</if>
<if test="age != null">
and age=#{age}
</if>
<if test="sex == '1' or sex == '0'">
and sex=#{sex}
</if>
</where> -->
<!-- select eid,ename,age,sex,did from emp -->
<include refid="empColumns"></include>
<trim prefix="where" suffixOverrides="and|or">
<if test="eid != null">
eid=#{eid} and
</if>
<if test="ename != null and ename != ''">
ename=#{ename} and
</if>
<if test="age != null">
age=#{age} or
</if>
<if test="sex == '1' or sex == '0'">
sex=#{sex}
</if>
</trim>
</select>
<!-- List<Emp> getEmpListByChoose(Emp emp); -->
<!--
<choose>选择某一个when或otherwise执行
<when test=""></when>：通过test表达式拼接SQL
.
.
.
<otherwise></otherwise>:当when都不符合条件，就会选择otherwise拼接SQL语句
</choose>
-->
<select id="getEmpListByChoose" resultType="Emp">
select eid,ename,age,sex from emp
where
<choose>
<when test="eid != null">
eid = #{eid}
</when>
<when test="ename != null and ename != ''">
ename = #{ename}
</when>
<when test="age != null">
age = #{age}
</when>
<otherwise>
sex = #{sex}
</otherwise>
</choose>
</select>
<!-- void insertEmp(Emp emp); -->
<insert id="insertEmp">
insert into emp(eid,ename,age,sex) values(
null,
#{ename},
#{age},
<choose>
<when test="sex == 0">'女'</when>
<when test="sex == 1">'男'</when>
<otherwise>'不详'</otherwise>
</choose>
)
</insert>
<!-- void deleteMoreEmp(String eids); -->
<delete id="deleteMoreEmp">
delete from emp where eid in (${value})
</delete>
<!-- void deleteMoreByList(List<Integer> eids); -->
<!--
<foreach collection="eids" item="" close="" open="" separator="" index=""></foreach>
对一个数组或集合进行遍历
collection:指定要遍历的集合或数组
item:设置别名
close:设置循环体的结束内容
open:设置循环体的开始内容
separator:设置每一次循环之间的分隔符
index:若遍历的是list集合，index代表下标；若遍历的是map结合，index代表键
-->
<!--
delete from emp where eid in ();
delete from emp where eid = 1 or eid = 2 or eid = 3
-->
<delete id="deleteMoreByList">
<!-- delete from emp where eid in
<foreach collection="list" item="eid" separator="," open="(" close=")">
#{eid}
</foreach> -->
delete from emp where
<!-- <foreach collection="list" item="eid" separator="or">
eid = #{eid}
</foreach> -->
<foreach collection="eids" item="eid" separator="or">
eid = #{eid}
</foreach>
</delete>
<!--
delete:
delete from emp where eid in ();
delete from emp where eid = 1 or eid = 2 or eid = 3
select:
select * from emp where eid in ();
select * from emp where eid = 1 or eid = 2 or eid = 3
update:
把每条数据修改成相同内容
update emp set ... where eid in ();
update emp set ... where eid = 1 or eid = 2 or eid = 3
把每条数据修改成对应内容,注意必须在链接地址（url）后添加?allowMultiQueries=true
update emp set ... where eid=1;
update emp set ... where eid=2;
update emp set ... where eid=3;
insert:
insert into emp values(),(),()
-->
<!-- void insertMoreByArray(Emp[] emps); -->
<insert id="insertMoreByArray">
insert into emp values
<foreach collection="array" item="emp" separator=",">
(null,#{emp.ename},#{emp.age},#{emp.sex},1)
</foreach>
</insert>
<!-- void updateMoreByArray(@Param("emps")Emp[] emps); -->
<update id="updateMoreByArray">
<foreach collection="emps" item="emp">
update emp set ename=#{emp.ename}, age=#{emp.age}, sex=#{emp.sex} where eid = #{emp.eid};
</foreach>
</update>
</mapper>
4.TestDynamicSQl.java
package com.atguigu.test;
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;
import org.junit.Test;
import com.atguigu.bean.Emp;
import com.atguigu.mapper.EmpMapper;
public class TestDynamicSQL {
@Test
public void TestMore() throws Exception{
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession(true);
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
//批量删除
// List<Integer> eids = new ArrayList<>();
// eids.add(10);
// eids.add(16);
// eids.add(17);
// mapper.deleteMoreByList(eids);//只能删除id为1的员工
//批量添加
//Emp[] emps = new Emp[3];
//Emp[] emps = new Emp[]{};
//Emp[] emps = {};
// Emp emp1 = new Emp(null, "a",23,"男");
// Emp emp2 = new Emp(null, "aa",23,"男");
// Emp emp3 = new Emp(null, "aaa",23,"男");
// Emp[] emps = {emp1, emp2, emp3};
// mapper.insertMoreByArray(emps);
//批量修改(需要在jdbc.url后加?allowMultiQueries=true)
//jdbc.url=jdbc:mysql://localhost:3306/ssm?allowMultiQueries=true
Emp emp1 = new Emp(20, "a1",233,"女");
Emp emp2 = new Emp(21, "aa1",233,"女");
Emp emp3 = new Emp(22, "aaa1",233,"女");
Emp[] emps = {emp1, emp2, emp3};
mapper.updateMoreByArray(emps);
}
@Test
public void TestChoose() throws Exception{
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession(true);
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
//测试1
//Emp emp = new Emp();
// emp.setEid(1);
// emp.setEname("张三");
// emp.setAge(12);
//emp.setSex("1");
// List<Emp> list = mapper.getEmpListByChoose(emp);
// for (Emp e : list) {
// System.out.println(e);
// }
//测试2
// Emp emp = new Emp();
// emp.setEname("张三");
// emp.setAge(12);
// emp.setSex("1");
// mapper.insertEmp(emp);
Emp emp = new Emp();
emp.setEname("张三三");
emp.setAge(12);
emp.setSex("123");
mapper.insertEmp(emp);
}
@Test
public void TestIf() throws Exception{
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession(true);
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp emp = new Emp();
//emp.setEid(1);
emp.setEname("张三");
emp.setAge(12);
//emp.setSex("1");
List<Emp> list = mapper.getEmpListByMoreTJ(emp);
for (Emp e : list) {
System.out.println(e);
}
}
public SqlSessionFactory getSqlSessionFactory() throws Exception{
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
return sqlSessionFactory;
}
}
