javaEE(SSM)-4：动态SQL
动态SQL引入
【主要作用】：根据不同条件，或者不同数据，拼接SQL子句，在XML文件中实现类似编程的效果。
【主要元素（标记）】：
查询条件：单分支 if；多分支choose… when… other
特殊处理：where /trim
更新数据：set
复杂查询与更新：foreach
条件查询操作
1. 元素
根据条件拼接SQL语句，满足条件即包含SQL子句。
（1）语法
<if test="条件表达式"> SQL子句</if>1.条件表达式：基本都是比较运算或者逻辑运算，结果为true或false
2.运算符： 大于；大于等于；等于；不等于；小于；小于等于，由于 为XML的特殊字符，不能直接使用，因此mybatis使用以下对应的运算符：
比较运算符：gt 、 gte 、 == 、 != 、 lt 、 lte ；
逻辑运算符：使用 and 和 or 代替 && 和 ||
（2）示例1：模糊匹配
【需求】根据输入的姓名或职业模糊查询客户表中的客户信息：
1.如果姓名不为空，则按姓名查询；
2.如果职业不为空，则按职业查询；
3.如果两者都不为空，则必须满足指定姓名和职业的条件；
4.如果两者都为空，则显示全部数据；
【分析】：该需求适合以下各种情况
条件1：select * from customer条件2：select * from customer where username like concat('%',#{username},'%')条件3：select * from customer where jobs like concat('%',#{jobs},'%')条件4：select * from customer where username like concat('%',#{username},'%') AND jobs like concat('%',#{jobs},'%')情况1：无条件查询，没有输入用户名和职业;
情况2：输入了用户名但没有输入职业
情况3：输入了职业但没有输入用户名
情况4：两者都有输入，满足之一都查询出来
【具体实现】：
1.创建数据库mybatis并添加数据表customer
2.创建maven项目，并添加必须的两个依赖：mybatis和connectorJ;为了方便调试，加入log4j依赖，同时在项目src/main/java根目录下添加log4j.properties文件。以后这个都是mybatis框架项目的标配依赖。 — 具体操作见javaEE(SSM)学习笔记3：Mybatis核心对象和配置文档。
3.添加mybatis.xml配置文件和依赖属性文件db.properties (src/main/resource)
4.添加POJO类：Customer.java (位置：src/main/java/pojo包)
5.添加CustomerMapper.xml映射文件（位置：src/main/java/mapper包）
6.测试运行
7.扩展：使用接口方式执行映射语句
CustomerMapper.xml内容
select * from customer where 1=1 and username like concat('%',#{username},'%')and jobs like concat('%',#{jobs},'%')【注意】参数类型是pojo类，test中直接使用其属性来判断
【测试核心代码】：
//1参数为null :显示全部数据 //sqlSession.selectList("getCustomer"); //2.姓名不为空 cust.setUsername("张三"); sqlSession.selectList("getCustomer",cust); //3物业不为空 cust.setUsername(""); cust.setJobs("物业"); sqlSession.selectList("getCustomer",cust); //4.两者不为空 cust.setUsername("张三"); cust.setJobs("老板"); sqlSession.selectList("getCustomer",cust); 【运行结果，类似如下】：
DEBUG [main] - ==> Preparing: select * from customer where 1=1 and username like concat('%',?,'%')DEBUG [main] - ==> Parameters: 张三(String)TRACE [main] - <== Columns: id, username, jobs, phone, createtimeTRACE [main] - <== Row: 58, 测试张三, 物业, 136, 2022-01-09 09:42:57TRACE [main] - <== Row: 59, 测试张三, 工人, 136, 2022-01-16 19:57:16DEBUG [main] - Preparing: select * from customer where 1=1 and jobs like concat('%',?,'%')DEBUG [main] - ==> Parameters: 物业(String)TRACE [main] - <== Columns: id, username, jobs, phone, createtimeTRACE [main] - <== Row: 58, 测试张三, 物业, 136, 2022-01-09 09:42:57DEBUG [main] - Preparing: select * from customer where 1=1 and username like concat('%',?,'%') and jobs like concat('%',?,'%')DEBUG [main] - ==> Parameters: 张三(String), 老板(String)DEBUG [main] - <== Total: 0(无数据)扩展：使用接口方式。
（3）示例2：test运算符使用 和sql运算符
【需求】：输入id,如果id<20,那么选择id<5的数据输出
【注意】：在test中的使用，以及在xml中的如何使用运算符
CustomerMapper.xml内容
select * from customer where 1=1 and id >= 5 and id <= #{id} 1.在test中使用一个普通类型参数数据，使用_parameter获取该参数；如果使用多个参数，使用POJO类型参数，那么在test中可以直接使用属性名(本例情况，如果是范围值，则可以是使用数组—后面foreach复杂查询，也可以使用接口方式，使用注解@param指定参数)；
2.在test属性中使用运算符：gt(>) 、 gte(>=) 、 == 、 != 、 lt(<) 、 lte (<= )
而逻辑运算：使用 and 和 or 代替 && 和 ||
3.在sql中使用（XML文档中），需要使用HTML的转义字符；
> 表示大于 ，那么大于等于就是：>=； <表示小于,那么小于等于就是<= 注意分号
=等号不是特殊字符。
【测试结果，类似如下】：
DEBUG [main]==> Preparing: select * from customer where 1=1 and id >= 5 and id Parameters: 20(Integer)TRACE [main] - <== Columns: id, username, jobs, phoneTRACE [main] - <== Row: 8, 孙六六, 管理员, 133333334444TRACE [main] - <== Row: 9, 孙六, 工人, 44444444444444444444TRACE [main] - <== Row: 10, 孙六, 工人, 44444444444444444444DEBUG [main] - <== Total: 32. 元素
作用：在给出的条件中，仅仅拼接满足条件的第一个子句。
（1）语法
SQL子句1 SQL子句2 ... SQL子句3 按顺序判断，只要满足其中一个条件，则不再往下判断；当所有条件不满足，执行otherwise中的子句。
（2）示例：查询客户信息
【需求】：
1.如果给出客户名称，则按客户名称进行模糊查询；
2.如果给出客户职业，按照客户职业进行模糊查询；
3.如果两者都不给出，则查询所有联系电话不为空的客户信息。
CustomerMapper.xml内容
select * from customerwhere 1=1 and username like concat('%',#{username},'%')and jobs like concat('%',#{jobs},'%')and phone is not null与if别在于，choose是当第一个条件满足时，不再继续判断；而if会继续判断下一个if
otherwise 也可以是 and id /& gt; 0
3. where元素
【作用】根据是否有条件子句在进行拼接。根据标记内是否包含SQL子句，决定是否使用where关键字拼接查询子句，同时它会去除多余的连接运算符，比如AND 和 OR等。有了这个标记，上例的示例中的子句就不要这么写了：where 1=1
示例：使用where 元素重新实现上例
select * from customerand username like concat('%',#{username},'%')and jobs like concat('%',#{jobs},'%')and phone is not null当没有输入用户名和职业时，otherwise中的and 会自动去除，从而构成完整的sql语句。
4. trim元素
【作用】能够自动添加前缀，并去除子句前面多余的、指定的连接字符(串)；或者添加后缀，自动去除子句后面多余的、指定的连接字符或字符串。用法非常灵活。
其包含以下属性：
1.prefix : 为子句添加的前缀，如 ： where等 。实际上是任意的，只要你指定什么都会添加到子句前面
2.prefixOverrides（忽略前缀）：去除子句前面多余的字符，如And ,Or ，只要你指定，都会删除
3.suffix:为子句添加的后缀 ，如 右括号 ），只要你指定什么，都会添加到子句之后。
4.suffixOverrides：去除子句后面多余的字符，如逗号，实际上你要删除什么都会删除在子句最后的内容
实际上，就是在子句之前、后是否要添加SQL连接关键字，是否要删除子句前后的多余关键字，目的是自动构成正确的SQL语句。
示例1：使用trim代替where元素
如果有子句，自动加上指定的where前缀，然后删除多余的、指定的连接符
【需求】：根据客户名或者职业来查询客户信息
【思路】：在子句前加入WHERE，去掉子句前面多余的OR
CustomerMapper.xml内容
select * from customerOR username like concat('%',#{username},'%')OR jobs like concat('%',#{jobs},'%')【 测试】：
@Testpublic void test3() {sqlSession = ssf.openSession();Customer c=new Customer();c.setJobs("工人");c.setUsername("孙");sqlSession.selectList("getCustomer_trim",c);}【结果类似】：
DEBUG [main] - ==> Preparing: select * from customer WHERE username like concat('%',?,'%') or jobs like concat('%',?,'%')DEBUG [main] - ==> Parameters: 孙(String), 工人(String)TRACE [main] - <== Columns: id, username, jobs, phone, createtimeTRACE [main] - <== Row: 8, 孙六六, 管理员, 133333334444, 2019-10-16 20:59:08TRACE [main] - <== Row: 9, 孙六1, 工人, 44444444444444444444, 2022-01-16 23:09:50TRACE [main] - <== Row: 10, 孙六2, 工人, (Null), 2022-01-16 23:10:57TRACE [main] - <== Row: 59, 测试张三, 工人, 136, 2022-01-16 19:57:16DEBUG [main] - <== Total: 4如果子句不存在，那么不会加前、后缀，即使设置了prefix和suffix。
示例2：使用trim，代替更新子句set
如果有子句，自动加上指定的set前缀，然后删除多余的、指定的连接符
【需求】：根据id更新客户信息，如果参数存在客户名称，则更新客户名称；如果存在职业，则同时更新职业。即只更新包含值的字段。
【注意】：这种情况不能一个值没有，这样无法构成正确的SQL语句（前端去判断）。
update customer username=#{username},jobs=#{jobs},where id=#{id}【注意】：需要去除最后一个逗号：suffixOverrides=","
传递参数必须有数据，调用者（服务层或应用层）必须验证数据正确性才调用该映射语句。不应该在XML验证数据有效性。
【测试】
@Testpublic void test4() {sqlSession = ssf.openSession();Customer c=new Customer();c.setId(8);c.setJobs("工人");c.setUsername("孙明");sqlSession.update("updateCustomer",c);sqlSession.commit();//需要提交}【结果类似】
DEBUG [main] - ==> Preparing: update customer set username=?, jobs=? where id=?DEBUG [main] - ==> Parameters: 孙明(String), 工人(String), 8(Integer)DEBUG [main] - <== Updates: 15. 复杂查询：foreach
列举出输入参数的每一个元素。类似java的循环结果，依次从传入的数组Array、列表List或Map中取出数据，拼接成SQL子句。
【建议】：先在MySQL写出完整的sql，再去修改对应的动态SQL。
【应用场景】：
1.从一组指定的下标中查询客户信息，使用数组或列表作为输入参数。
2.输入参数同时包含多种类型，如普通类型、POJO类型、数组或列表类型，即多参数查询，使用map。例如使用用户名、职业进行模糊查询，并使用一组下标进行查询客户信息。
3.插入一组数据。
（1）语法1：使用数组和列表
... SQL子语句 【作用】：根据数组或列表元素个数进行循环，每循环一次，将元素保存在item属性变量中，并将标记的内容按指定的分隔符号进行拼接为子句，最后在子句前后加上指定的前缀和后缀字符。
1.如果是数组和列表， 指定的输入参数类型parameterType可以是arraylist或者list都可以，它是mybatis内置别名，不区分大小写.
2.item属性值类似循环变量，注意，值是变量名。
3.collection：参数类型，如果参数是数组，那么collection值使用array ；如果参数的是List，collection必须使用list
4.index当前数组或列表的下标，从0开始；open为前缀符号，如左括号，close后缀符号，如右括号
5.separator：指定每个循环的子句使用什么符号分隔，如逗号
（2）语法1应用示例：查找一组id对应的数据
【需求】：从一组指定的id中查询客户信息，使用数组和列表分别实现
CustomerMapper.xml内容
<select id="byArrays" parameterType="arraylist" resultType="customer">select * from customer where id in<foreach item="id" collection="array" open="(" close=")"separator=",">#{id}</foreach></select><select id="byList" parameterType="list" resultType="customer">select * from customer where id in<foreach item="id" collection="list" open="(" close=")"separator=",">#{id}</foreach></select>【测试】
@Testpublic void test5() {sqlSession = ssf.openSession();int[] ids= {5,8,10};sqlSession.selectList("byArrays",ids); }@Testpublic void test6() { sqlSession = ssf.openSession(); List<Integer> ids=new ArrayList<Integer>(); ids.add(5); ids.add(8); ids.add(10); sqlSession.selectList("byList",ids); }【结果类似如下】：
DEBUG [main] - ==> Preparing: select * from customer where id in ( ? , ? , ? )DEBUG [main] - ==> Parameters: 5(Integer), 8(Integer), 10(Integer)TRACE [main] - <== Columns: id, username, jobs, phone, createtimeTRACE [main] - <== Row: 8, 孙明, 工人, 133333334444, 2022-01-17 07:14:33TRACE [main] - <== Row: 10, 孙六2, 工人, (Null), 2022-01-16 23:10:57DEBUG [main] - Preparing: select * from customer where id in ( ? , ? , ? )DEBUG [main] - ==> Parameters: 5(Integer), 8(Integer), 10(Integer)TRACE [main] - <== Columns: id, username, jobs, phone, createtimeTRACE [main] - <== Row: 8, 孙明, 工人, 133333334444, 2022-01-17 07:14:33TRACE [main] - <== Row: 10, 孙六2, 工人, (Null), 2022-01-16 23:10:57DEBUG [main] - <== Total: 2（3）语法2：使用Map
当需要根据多个不同类型参数查询时，可以使用map类型参数
【语法】：
<select id="" parameterType="map" resultType=""> ... <foreach item="循环变量" collection="key名" index="当前循环下标"open="前缀" close="后缀" seperator="分隔符" > 组合的SQL语句 </foreach></select>map是mybatis内置别名，不需要全限定名，别名大小写都可以。
与语法1不同的是，collection=“key关键字名的集合”，
（4）语法2应用示例：根据一组id和其他字段查询
【需求】：根据一组指定的id，和职业进行查询客户信息
CustomerMapper.xml内容
select * from customerwhere jobs like concat('%',#{jobs},'%')and id in #{id}collection=“ids” 这里ids是Map集合元素的关键字。此处index还是下标，从0开始
【测试】
@Testpublic void test7() {sqlSession = ssf.openSession();List<Integer> ids=new ArrayList<Integer>();ids.add(5);ids.add(8);ids.add(10);Map<String,Object> map=new HashMap<String,Object>(); map.put("jobs", "工人");//普通字符串map.put("ids", ids);//集合或者数组 int[] ids= {5,8,10};sqlSession.selectList("byMap",map); }【扩展】
map中可以是普通类型，在SQL主句中使用map关键字获取；如上例的jobs–>这样，可以传递多个参数，如最大值，最小值,从而代替去循环数组。map.put("min", 1); map.put("max",100); select * from customer id > #{min} and id < #{max}也可以是对象pojo类型，SQL主句中使用“对象名.属性名”获取，对象名就是map关键字
map.put("cust", customer); select * from customer where username like concat('%',#{cust.username},'%')
6. foreach扩展应用
【扩展内容】主要是了解foreach还有哪些用法。
（1）使用数组，传递数组范围
【需求】：传递范围id(两个整数id，上限和下限)，查询id在该范围的客户信息
【分析】：使用数组 int[] ={1,10} 封装数据，在foreach中，根据下标不同，拼接SQL子句
select * from customerand id >= #{id}and id <=#{id}普通类型数组，既可以使用arraylist 和list，也可以直接使用数组对应的类型；
在foreach标记中，根据需要，仍然可以使用其他元素；要注意的是运算符在XML中的写法。
【测试】
@Testpublic void test8() {sqlSession = ssf.openSession();int[] ids= {1,10};sqlSession.selectList("byRange",ids); }【结果类似】
DEBUG [main] - ==> Preparing: select * from customer WHERE id >= ? and id Parameters: 1(Integer), 10(Integer)TRACE [main] - <== Columns: id, username, jobs, phone, createtimeTRACE [main] - <== Row: 2, 李四, 老板, 111111111111111, 2022-01-16 20:28:23TRACE [main] - <== Row: 3, 王五, 经理, 111111111111111, 2022-01-16 20:28:33TRACE [main] - <== Row: 8, 孙明, 工人, 133333334444, 2022-01-17 07:14:33TRACE [main] - <== Row: 9, 孙六1, 工人, 44444444444444444444, 2022-01-16 23:09:50TRACE [main] - <== Row: 10, 孙六2, 工人, (Null), 2022-01-16 23:10:57DEBUG [main] - <== Total: 5（2）同时插入一组客户信息
【需求】：同时插入多条记录
【分析】：多条记录可以使用List方式封装数据，在映射SQL中，使用foreach获取逐个对象，再拼接SQL子句
insert into customer(username,jobs,phone) values( #{c.username},#{c.jobs},#{c.phone} )主要要先理解，插入多条数据的SQL写法是：
insert into customer(username,jobs,phone) values（记录1），（记录2）item属性可以是普通类型，也可以是对象
【测试】
@Testpublic void test9() {sqlSession = ssf.openSession(); List cs=new ArrayList(); Customer c=new Customer();c.setUsername("A1");c.setJobs("管理工人");c.setPhone("1111");cs.add(c);//插入记录1c=new Customer();c.setUsername("A2");c.setJobs("管理");c.setPhone("1111");cs.add(c);//插入记录2 sqlSession.insert("addCustomers",cs); sqlSession.commit();//别忘记了}【结果类似】
DEBUG [main] - ==> Preparing: insert into customer(username,jobs,phone) values ( ?,?,? ) , ( ?,?,? )DEBUG [main] - ==> Parameters: A1(String), 管理工人(String), 1111(String), A2(String), 管理(String), 1111(String)DEBUG [main] - <== Updates: 2【重点记住】
1.parameterType:
1.普通类型
2.POJO类型
3.List类型 ：list和数组
4.Map：Map类型
2.foreach中
collection：array / list / map的key名