动态SQL语句
动态SQL是什么？
就是相对与固定SQL。就是通过传入的参数不一样，可以组成不同结构的SQL语句。这种根据参数的条件修改SQL结构的SQL语句，我们称之为动态SQL语句
动态SQL有什么用？
1.根据条件组装不同结构的SQL语句，可以提高SQL代码的重用性
2.满足某些特定需求，如，条件判断查询
基于XML的实现
标签包括
<sql>用于声明公有的SQL语句块，在操作标签中使用<include>调用
<if>类似java if(){},用于判断
<foreach>：类似Java的foreach循环，一般用户批量处理的SQL语句
<trim>：切割标签，主要用于切割关键字的头和尾的字符，新版的Mybatis使用的几率很少
<set>：使用set标签就是SQL语言的set关键字，可以在update的时候set关键字后面的，逗号可以自动省略
<where>：使用where标签作为SQL语言的where关键字，好处如果where后面的条件都不成立，忽略where关键字
<choose><when><otherwise>：Java的switch case
接口文件
package cn.xc.mybatis.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import cn.xc.mybatis.pojo.User;
public interface UserMapper {
/**
* 根据条件查询结果
* @param user
* @return
*/
List<User> selectByCondition(User user);
/**
* 根据提交查询总数
* @param user
* @return
*/
Long selectTotalByConditin(User user);
/**
* 修改用户
* @param user
* @return
*/
int updateUserByNotNull(User user);
/**
* 批量删除用户
* @param ids
* (@Param("ids")Integer[] ids)==> 相当与Map 的 key + 参数 + 实参
* @return
*/
int deleteByIds(@Param("ids")Integer[] ids);
/**
* 批量插入
* Batch (分批处理)
* @param users
* @return
*/
int insertByBatch(@Param("users")List<User> users);
}
映射文件
<?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=""> 映射的根元素 namespace属性：命名空间 （唯一），必须是当前对应映射接口的全限定名
全限定名 ： 包名+简单类名/接口名 cn.xc.mybatis.mapper.UserMapper -->
<mapper namespace="cn.xc.mybatis.mapper.UserMapper">
<!-- 多行查询 resultType : 只是返回的当行数据封装的对象类型，无论单行还是多行查询都必须返回对应的 实体类型User -->
<select id="selectByCondition" parameterType="cn.xc.mybatis.pojo.User" resultType="cn.xc.mybatis.pojo.User">
<!-- 错误写法 select * from user name like '%#{name}%' or age = #{age} -->
<!--
select * from user where name like concat('%',#{name},'%') or age = #{age}
上述SQL语句的语义来说，是一个静态的SQL语句，一开始已经确定SQL的语义
不管有没有数据，都会对全部数据进行修改，如果某一个数据没有，name会自动设置为null
不符合实际场景
解决方案：使用Mybatis的动态SQL语句
-->
select * from user
<!--
<include refid="condition_sql"/>
包含引入sql片段
refid ：被引入的sql片段的id值
-->
<include refid="condition_sql"/>
</select>
<!--
<sql id=""></sql>
抽取sql片段
id属性：片段的唯一标识，以供其他地方使用
-->
<!-- <sql id="condition_sql">
动态SQL语句
<where>标签
在where内部编写条件
1.如果只要满足一个条件<where>标签会自动拼接 WHERE 关键字拼接上对应的条件
2.如果条件前面有 OR|AND 关键字，但是是第一个条件，那么会自动删除这个关键字，以保证语法正确
3.如果一个条件都没有，那么就相当于查询所有的数据
<where>
<if test="name != null">
name like concat('%',#{name},'%')
</if>
<if test="age != null">
or age = #{age}
</if>
</where>
</sql> -->
<sql id="condition_sql">
<!--
<trim>标签，开发者可以自定义条件，既可以指定where条件也可以指定set关键字条件
<trim prefix="where" prefixOverrides="AND|OR">
prefix ：前缀，
当前如果是 条件就用 where
如果使用修改 就用 set
prefixOverrides ：如果在where关键字后面第一个条件，如果是AND|OR会自动去掉
-->
<trim prefix="where" prefixOverrides="AND|OR">
<if test="name != null">
name like concat('%',#{name},'%')
</if>
<if test="age != null">
or age = #{age}
</if>
</trim>
</sql>
<select id="selectTotalByCondition" parameterType="cn.xc.mybatis.pojo.User" resultType="long">
select count(*) from user
<!--
<include refid="condition_sql"/>
包含引入sql片段
refid ：被引入的sql片段的id值
-->
<include refid="condition_sql"></include>
</select>
<!-- 修改操作 -->
<update id="updateUserByNotNull" parameterType="cn.xc.mybatis.pojo.User">
<!--
update user set name = #{name},password = #{password},age = #{age} where id = #{id}
上述SQL语句的语义来说，是一个静态的SQL语句，一开始已经确定SQL的语义
不管有没有数据，都会对全部数据进行修改，如果某一个数据没有，name会自动设置为null
不符合实际场景
解决方案：使用MyBatis的动态SQL语句
-->
<!--
动态sql语句 <set>标签
<trim prefix="WHERE" prefixOverrides="AND | OR">
-->
update user
<!-- <set>
<if test="name != null">
name = #{name},
</if>
<if test="password != null">
password = #{password},
</if>
<if test="age != null">
age = #{age}
</if>
</set> -->
<!--
prefix : 前缀，
当前如果是 条件就用 WERHE
如果使用修改 就用 SET
prefixOverrides ：如果在 WHRE 关子健 后面的第一个条件，如果是 AND|OR 会自动去掉
suffixOverrides :如果是最后一个条件， 如果是多余的逗号（，） 会自动去掉
-->
<trim prefix="set" suffixOverrides=",">
<if test="name !=null">name = #{name},</if>
<if test="password !=null">password = #{password},</if>
<if test="age !=null">age = #{age}</if>
</trim>
where id = #{id}
</update>
<!-- 批量删除 -->
<delete id="deleteByIds" parameterType="list">
<!-- delete from user where id in (1,2,3) -->
<!--
<foreach collection="" open="" close="" item="" separator="">标签体内容</foreach>
MyBatis的for循环标签
collection：循环集合
open：起始括号(
close：结束括号 )
item：集合每次循环的数据对应的变量
separator：分割符号： (1,2,3) 数值与数值之间的分隔符 ，逗号
-->
delete from user where id in
<foreach collection="ids" open="(" close=")" item="id" separator=",">
#{id}
</foreach>
</delete>
<insert id="insertByBatch">
<!-- insert into user (name,password,age)values -->
insert into user (name,password,age)values
<foreach collection="users" item="user" separator=",">
(#{user.name},#{user.password},#{user.age})
</foreach>
</insert>
</mapper>
测试代码
package cn.xc.mybatis.test;
import java.util.Arrays;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import cn.xc.mybatis.mapper.UserMapper;
import cn.xc.mybatis.pojo.User;
import cn.xc.mybatis.utils.MyBatisUtil;
public class UserMapperTest {
// 多行查询
@Test
public void testSelectByCondition() throws Exception {
// 1.创建SqlSession对象
SqlSession session = MyBatisUtil.openSession();
// 2.创建UserMapper 映射接口的代理对象（接口不能直接创建对象，底层创建代理对象）
UserMapper userMapper = session.getMapper(UserMapper.class);
//条件对象
User conditionUser = new User();
conditionUser.setName("哥");
//conditionUser.setAge(30);
// 3. 执行多行查询方法
List<User> users = userMapper.selectByCondition(conditionUser);
Long total = userMapper.selectTotalByCondition(conditionUser);
for (User user : users) {
System.out.println(user);
}
System.out.println(total);
session.close();
}
// 修改功能
@Test
public void testUpdateUserByNotNull() throws Exception {
// 1.创建SqlSession对象
SqlSession session = MyBatisUtil.openSession();
// 2.创建UserMapper 映射接口的代理对象（接口不能直接创建对象，底层创建代理对象）
UserMapper userMapper = session.getMapper(UserMapper.class);
// 3.执行update方法
User user = new User();
user.setId(7);
user.setName("杰克");
user.setPassword("xjg");
int row = userMapper.updateUserByNotNull(user);
// 4.手动提交事务
session.commit();
// 5.关闭Session
session.close();
}
// 批量删除
@Test
public void testDeleteByBatch() throws Exception {
// 1.创建SqlSession对象
SqlSession session = MyBatisUtil.openSession();
// 2.创建UserMapper 映射接口的代理对象（接口不能直接创建对象，底层创建代理对象）
UserMapper userMapper = session.getMapper(UserMapper.class);
Integer[] ids = {1,2,3,4};
int row = userMapper.deleteByIds(ids);
// 4.手动提交事务
//session.commit();
// 5.关闭Session
session.close();
}
// 批量插入
@Test
public void testInsertByBatch() throws Exception {
// 1.创建SqlSession对象
SqlSession session = MyBatisUtil.openSession();
// 2.创建UserMapper 映射接口的代理对象（接口不能直接创建对象，底层创建代理对象）
UserMapper userMapper = session.getMapper(UserMapper.class);
User u1 = new User(null,"TOM","tom",50);
User u2 = new User(null,"Jerry","jerry",50);
List<User> users = Arrays.asList(u1,u2);
int row = userMapper.insertByBatch(users);
// 4.手动提交事务
session.commit();
// 5.关闭Session
session.close();
}
}
基于注解方式实现
动态sql除了支持xml方式以外，还是支持使用纯注解的方式
主要一下四个注解+对应动态sql语句的类文件
1.@SelectProvider 动态查询SQL语句对应注解
2.@InsertProvider 动态插入SQL语句对应注解
3.@UpdateProvider 动态修改SQL语句对应注解
4.@DeleteProvider 动态删除SQL语句对应注解
接口映射文件
package cn.xc.mybatis.mapper;
import java.util.List;
import org.apache.ibatis.annotations.DeleteProvider;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.UpdateProvider;
import cn.xc.mybatis.pojo.User;
import cn.xc.mybatis.pojo.UserSQLProvider;
public interface UserMapper {
/**
* @SelectProvider(type = UserSQLProvider.class,method = "selectByCondition")
* 查询动态sql语句的注解
* type ：拼接动态sql语句的类的字节码
* method ：UserSqlProvider 类中返回动态查询sql语句的方法名
* 注意：USerSQLProvider中对应的方法的参数必须和UserMapper中对应的方法参数一样
* 方法名理论上可以不限，但是建议一般和UserMapper接口中的方法名一致，（阅读更直观清晰）
*/
@SelectProvider(type = UserSQLProvider.class,method = "selectByCondition")
List<User> selectByCondition(User user);
/**
* 根据提交查询总数
* @param user
* @return
*/
@SelectProvider(type = UserSQLProvider.class,method = "selectTotalByCondition")
Long selectTotalByCondition(User user);
/**
* 修改用户
* @param user
* @return
*/
@UpdateProvider(type = UserSQLProvider.class,method="updateUserByNotNull")
int updateUserByNotNull(User user);
/**
* 批量删除用户
* @param ids
* (@Param("ids")Integer[] ids)==> 相当与Map 的 key + 参数 + 实参
* @return
*/
@DeleteProvider(type = UserSQLProvider.class,method="deleteByIds")
int deleteByIds(@Param("ids")Integer[] ids);
/**
* 批量插入
* Batch (分批处理)
* @param users
* @return
*/
@InsertProvider(type = UpdateProvider.class,method="insertByBatch")
int insertByBatch(@Param("users")List<User> users);
}
动态sql语句文件
(在查询上有些bug)
UserSQLProvider.java
package cn.xc.mybatis.pojo;
import java.util.List;
import org.apache.ibatis.annotations.Param;
//拼接动态sql语句的类
public class UserSQLProvider {
/*
* 返回查询的动态SQL语句
*/
public String selectByCondition(User user) {
StringBuilder sb = new StringBuilder();
sb.append("select * from user where ");
// 注意：返回动态SQl语句不是纯粹的SQL字符串，直接的拼接一定是 使用OGMNL 表达式
if (user.getName() != null) {
sb.append("name like concat('%',#{name},'%')");
}
if (user.getAge() != null) {
sb.append("or age = #{age}");
}
return sb.toString();
}
public String selectTotalByCondition(User user) {
StringBuilder sb = new StringBuilder();
sb.append("select count(*) from user where ");
// 注意：返回动态SQl语句不是纯粹的SQL字符串，直接的拼接一定是 使用OGMNL 表达式
if (user.getName() != null) {
sb.append("name like concat('%',#{name},'%')");
}
if (user.getAge() != null) {
sb.append("or age = #{age}");
}
return sb.toString();
}
public String updateUserByNotNull(User user) {
StringBuilder sb = new StringBuilder();
sb.append("update user set ");
if (user.getName() != null) {
sb.append("name = #{name},");
}
if (user.getPassword() != null) {
sb.append("password = #{password},");
}
if (user.getAge() != null) {
sb.append("age = #{age},");
}
// 删除最后一个多余的逗号
sb.deleteCharAt(sb.length() - 1);
sb.append(" where id = #{id}");
return sb.toString();
}
// 批量删除
public String deleteByIds(@Param("ids") Integer[] ids) {
StringBuilder sb = new StringBuilder();
/*
* delete from user where id in (#{ids[0],#{ids[1]}})
*/
sb.append("delete from user where id in (");
for (int i = 0; i < ids.length; i++) {
sb.append("#{ids["+i+"]},");
}
// 删除最后一个多余的逗号
sb.deleteCharAt(sb.length() - 1);
sb.append(")");
System.out.println("sql:"+sb.toString());
return sb.toString();
}
// 批量插入
public String insertByBatch(@Param("users") List<User> users) {
StringBuilder sb = new StringBuilder();
/*
* insert into user (name,password,age)values
('露SEI','lusei',18),('丽丽','lili',20)
insert into user (name,password,age)values
(#{users[0].name},#{users[0].password},#{users[0].age }),(#users[1].name,#users[1].password,#{users[1].age })
*/
sb.append("insert into user (name,password,age)values ");
for (int i = 0; i < users.size(); i++) {
sb.append("(");
sb.append("#{users["+i+"].name},");
sb.append("#{users["+i+"].password},");
sb.append("#{users["+i+"].age}");
sb.append("),");
}
// 删除最后一个多余的逗号
sb.deleteCharAt(sb.length() - 1);
System.out.println("sql:"+sb.toString());
return sb.toString();
}
}
测试代码
package cn.xc.mybatis.test;
import java.util.Arrays;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import cn.xc.mybatis.mapper.UserMapper;
import cn.xc.mybatis.pojo.User;
import cn.xc.mybatis.utils.MyBatisUtil;
public class UserMapperTest {
// 多行查询
@Test
public void testSelectByCondition() throws Exception {
// 1.创建SqlSession对象
SqlSession session = MyBatisUtil.openSession();
// 2.创建UserMapper 映射接口的代理对象（接口不能直接创建对象，底层创建代理对象）
UserMapper userMapper = session.getMapper(UserMapper.class);
//条件对象
User conditionUser = new User();
conditionUser.setName("哥");
//conditionUser.setAge(30);
// 3. 执行多行查询方法
List<User> users = userMapper.selectByCondition(conditionUser);
Long total = userMapper.selectTotalByCondition(conditionUser);
for (User user : users) {
System.out.println(user);
}
System.out.println(total);
session.close();
}
// 修改功能
@Test
public void testUpdateUserByNotNull() throws Exception {
// 1.创建SqlSession对象
SqlSession session = MyBatisUtil.openSession();
// 2.创建UserMapper 映射接口的代理对象（接口不能直接创建对象，底层创建代理对象）
UserMapper userMapper = session.getMapper(UserMapper.class);
// 3.执行update方法
User user = new User();
user.setId(7);
user.setName("杰克");
user.setPassword("xjg");
int row = userMapper.updateUserByNotNull(user);
// 4.手动提交事务
session.commit();
// 5.关闭Session
session.close();
}
// 批量删除
@Test
public void testDeleteByBatch() throws Exception {
// 1.创建SqlSession对象
SqlSession session = MyBatisUtil.openSession();
// 2.创建UserMapper 映射接口的代理对象（接口不能直接创建对象，底层创建代理对象）
UserMapper userMapper = session.getMapper(UserMapper.class);
Integer[] ids = {1,2,3,4};
int row = userMapper.deleteByIds(ids);
// 4.手动提交事务
//session.commit();
// 5.关闭Session
session.close();
}
// 批量插入
@Test
public void testInsertByBatch() throws Exception {
// 1.创建SqlSession对象
SqlSession session = MyBatisUtil.openSession();
// 2.创建UserMapper 映射接口的代理对象（接口不能直接创建对象，底层创建代理对象）
UserMapper userMapper = session.getMapper(UserMapper.class);
User u1 = new User(null,"TOM","tom",50);
User u2 = new User(null,"Jerry","jerry",50);
List<User> users = Arrays.asList(u1,u2);
int row = userMapper.insertByBatch(users);
// 4.手动提交事务
session.commit();
// 5.关闭Session
session.close();
}
}
扫一扫
172
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
