【SSM - MyBatis篇10】动态SQL语句案例实现
程序员文章站
2022-05-16 23:50:10
案例描述 创建customer用户表，有三个属性字段，id、username、job，通过动态SQL语句对其进行增删查改。创建数据库表customer-- ------------------------------ customer表-- ----------------------------DROP TABLE IF EXISTS `customer`;CREATE TABLE `customer` ( `id` int(11) NOT NULL AUTO_INCREMENT,...
文章目录
- 案例描述
- 准备条件
- 动态SQL语句测试
- 1. 测试getCustomerByNameAndJob()不用动态sql实现查询
- 2. 通过if标签实现查询
- 3. 通过choose标签查询
- 4. where标签查询
- 5. trim标签查询
- 6. set用于更新
- 7. trim+set实现更新数据
- 8. foreach标签查询
- 9. bind标签
- 10. 通过foreach和insert，实现foreach循环批量插入
- 代码地址：[https://github.com/strive-xgf/SSM/commit/0ca1f098051de82289d59e67aa2a5c88aacffcff](https://github.com/strive-xgf/SSM/commit/0ca1f098051de82289d59e67aa2a5c88aacffcff)
案例描述
创建customer用户表，有三个属性字段，id、username、job，通过动态SQL语句对其进行增删查改。
准备条件
1. 创建数据库表customer
-- ----------------------------
-- customer表
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`job` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
-- ----------------------------
-- 初始数据
-- ----------------------------
INSERT INTO `customer` VALUES ('1', '张三', '程序员');
INSERT INTO `customer` VALUES ('2', '李四', '项目经理');
INSERT INTO `customer` VALUES ('3', '王五', '测试员');
INSERT INTO `customer` VALUES ('4', '赵六', '开发人员');
2. 创建javabean对象
public class Customer {
private Integer id; //id主键自动增长
private String username; //用户名
private String job; //工作
//省略getter/setter方法
}
3. 创建dao层接口
public interface CustomerMapper {
// 不用标签，普通方法测试- 通过name和job查询Customer
List<Customer> getCustomerByNameAndJob(Customer customer);
// 通过if标签实现查询
List<Customer> getCustomerByNameAndJobForIf(Customer customer);
// 通过choose标签实现查询
List<Customer> getCustomerByNameAndJobForChoose(Customer customer);
// where标签查询
List<Customer> getCustomerByNameAndJobForWhere(Customer customer);
// trim标签
List<Customer> getCustomerByNameAndJobForTrim(Customer customer);
// set标签更新数据
int updateForSet(Customer customer);
// 通过trim+set实现更新数据，添加忽略前后缀实现
int updateForTrim(Customer customer);
// 通过foreach标签查询 - 参数是list集合id (相当于select * from tableName where id in {1,2,3} 查询结果为集合)
List<Customer> getCustomerByIdsForeach(List<Integer> ids);
// bind标签，解决可移植性问题 ，解决不同数据库拼接函数或连接符号的不同(防止sql注入)
List<Customer> getCustomerByNameAndJobForBind(Customer customer);
// 通过foreach和insert，实现foreach循环批量插入
boolean insertCustomerList(List<Customer> customerList);
}
4. 创建连接数据库的属性文件db.properties(键值对形式)
jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8&useSSL=false
jdbc.username = root
jdbc.password = 861221293
5. spring整合MyBatis，核心配置文件applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:mybatis="http://mybatis.org/schema/mybatis-spring" xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">
<!--1. 引入jdbc的属性文件，在配置中通过占位使用 -->
<context:property-placeholder location="classpath*:db.properties" />
<!--2. <context:component-scan>扫描包中注解所标注的类(@Component、@Service、@Controller、@Repository) -->
<context:component-scan base-package="com.xgf.dynamic_sql"/>
<!--3. 由spring管理 配置数据源数据库连接(从jdbc属性文件中读取参数) -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
<property name="url" value="${jdbc.url}"/>
<property name="driverClassName" value="${jdbc.driver}"/>
</bean>
<!-- 通过spring来管理Mybatis的sqlSessionFactory对象创建 -->
<!--4. 通过完全限定名匹配查找 创建SqlSessionFactoryBean -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
</bean>
<!-- 5. mybatis提供的一个注解扫描标签（搜索映射器 Mapper 接口），通过自动扫描注解的机制，创建每个dao接口定义的bean -->
<mybatis:scan base-package="com.xgf.dynamic_sql.dao"/>
</beans>
6. 创建测试类，加载核心配置文件
public class CustomerMapperTest {
private static ApplicationContext applicationContext = null;
private static CustomerMapper customerMapper = null;
private static Customer customer = new Customer("赵六","开发人员");//初始化customer数据
//只加载一次 @BeforeClass@BeforeClass只在类中执行一次, 必须声明为public static
@BeforeClass
public static void init(){
//加载配置文件
applicationContext = new ClassPathXmlApplicationContext("com/xgf/dynamic_sql/config/applicationContext.xml");
//获取bean的两种方式
// 1.类名首字母小写
// customerMapper = (CustomerMapper) applicationContext.getBean("customerMapper");
// 2.类.class
customerMapper = (CustomerMapper) applicationContext.getBean(CustomerMapper.class);
}
}
动态SQL语句测试
1. 测试getCustomerByNameAndJob()不用动态sql实现查询
// 不用标签，普通方法测试- 通过name和job查询Customer
@Test
public void getCustomerByNameAndJob() {
System.out.println(customerMapper.getCustomerByNameAndJob(customer));;
}
<!--正常查询 多条件存在查询-->
<select id="getCustomerByNameAndJob" parameterType="com.xgf.dynamic_sql.bean.Customer" resultType="com.xgf.dynamic_sql.bean.Customer">
select id,username,job
from customer
where username like concat('%',#{username},'%') and job = #{job}
</select>
运行sql：Preparing: select id,username,job from customer where username like concat(’%’,?,’%’) and job = ?
运行参数：Parameters: 赵六(String), 开发人员(String)
运行结果：[Customer{id=4, username=‘赵六’, job=‘开发人员’}]
2. 通过if标签实现查询
@Test
public void getCustomerByNameAndJobForIf() {
System.out.println(customerMapper.getCustomerByNameAndJobForIf(customer));
}
<!-- if 查询，满足test条件就执行里面的代码，程序拼接and/or需要有一个where 1=1 的前提来拼接 -->
<select id="getCustomerByNameAndJobForIf" parameterType="com.xgf.dynamic_sql.bean.Customer" resultType="com.xgf.dynamic_sql.bean.Customer">
select id,username,job
from customer
where 1=1
<if test="username!=null and username!=''">
and username like concat('%',#{username},'%')
</if>
<if test="job!=null and job!=''">
and job=#{job}
</if>
</select>
运行sql：Preparing: select id,username,job from customer where 1=1 and username like concat(’%’,?,’%’) and job=?
运行参数：Parameters: 赵六(String), 开发人员(String)
运行结果： [Customer{id=4, username=‘赵六’, job=‘开发人员’}]
3. 通过choose标签查询
// 通过choose标签实现查询 这里讲customer的job和username设为空值，然后choose中的when条件都不满足，执行otherwise
@Test
public void getCustomerByNameAndJobForChoose() {
customer.setJob(null);
customer.setUsername(null);
System.out.println(customerMapper.getCustomerByNameAndJobForChoose(customer));
}
<!-- choose 查询 满足一个条件，就忽略后面的条件，没有一个条件满足，就输出所有-->
<select id="getCustomerByNameAndJobForChoose" parameterType="com.xgf.dynamic_sql.bean.Customer" resultType="com.xgf.dynamic_sql.bean.Customer">
select id,username,job
from customer
where 1=1
<choose>
<when test="username!=null and username!=''">
and username like concat('%',#{username},'%')
</when>
<when test="job!=null and job!=''">
and job=#{job}
</when>
<otherwise>
order by username desc
</otherwise>
</choose>
</select>
运行的sql：Preparing: select id,username,job from customer where 1=1 order by username desc **
运行参数：(空值，没有参数)**
运行结果： [Customer{id=4, username=‘赵六’, job=‘开发人员’}, Customer{id=3, username=‘王五’, job=‘测试员’}, Customer{id=2, username=‘李四’, job=‘项目经理’}, Customer{id=1, username=‘张三’, job=‘程序员’}]
因为条件都不满足，所以按照username的desc倒序输出所有customer(排序输出不能对汉字排序，但是会将汉字识别为拼音，进行字母排序)
4. where标签查询
// where标签查询
@Test
public void getCustomerByNameAndJobForWhere() {
System.out.println(customerMapper.getCustomerByNameAndJobForWhere(customer));
}
<!--where标签，如果后面没有条件(条件都不满足)不加where，如果后面有条件，会自动增加一个where
会将多余的and、or去掉，会自动填充第一个缺失的and、or-->
<select id="getCustomerByNameAndJobForWhere"
parameterType="com.xgf.dynamic_sql.bean.Customer"
resultType="com.xgf.dynamic_sql.bean.Customer" useCache="true">
select id,username,job
from customer
<where>
<if test="username!=null and username!=''">
and username like concat('%',#{username},'%')
</if>
<if test="job!=null and job!=''">
and job=#{job}
</if>
</where>
</select>
运行sql：Preparing: select id,username,job from customer WHERE username like concat(’%’,?,’%’) and job=?
运行参数：Parameters: 赵六(String), 开发人员(String)
运行结果：[Customer{id=4, username=‘赵六’, job=‘开发人员’}]
5. trim标签查询
//trim标签查询
@Test
public void getCustomerByNameAndJobForTrim() {
System.out.println(customerMapper.getCustomerByNameAndJobForTrim(customer));
}
<!--trim 可以添加前后缀(prefix和suffix) 覆盖前后缀(prefixOverrides和suffixOverrides)
这里覆盖了前缀and|or，添加了前缀where
-->
<select id="getCustomerByNameAndJobForTrim" parameterType="com.xgf.dynamic_sql.bean.Customer" resultType="com.xgf.dynamic_sql.bean.Customer">
select id,username,job
from customer
<trim prefix="where" prefixOverrides="and|or">
<if test="username!=null and username!=''">
or username like concat('%',#{username},'%')
</if>
<if test="job!=null and job!=''">
and job=#{job}
</if>
</trim>
</select>
运行sql：Preparing: select id,username,job from customer where username like concat(’%’,?,’%’) and job=?
运行参数：Parameters: 赵六(String), 开发人员(String)
运行结果：[Customer{id=4, username=‘赵六’, job=‘开发人员’}]
6. set用于更新
//set标签更新数据
@Test
public void updateForSet() {
customer.setId(4);//前面初始化数据没有id
customer.setUsername("赵六数据更新");
customer.setJob("赵六当老板");
System.out.println(customerMapper.updateForSet(customer));
}
<!--set在动态update语句中，可以使用<set>元素动态更新列 ->
set 元素可以用于动态包含需要更新的列，忽略其它不更新的列(如果其它列数据为null，就不更新)。
(返回结果是整数，更新删除增加不需要写resultType)
-->
<update id="updateForSet" parameterType="com.xgf.dynamic_sql.bean.Customer" flushCache="false">
update customer
<set>
<if test="username!=null and username!=''">
username=#{username},
</if>
<if test="job!=null and job!=''">
job=#{job},
</if>
</set>
where id=#{id}
</update>
运行sql：Preparing: update customer SET username=?, job=? where id=?
运行参数：Parameters: 赵六数据更新(String), 赵六当老板(String), 4(Integer)
运行结果：Updates: 1 (更新影响数据1条，更新成功)
7. trim+set实现更新数据
//通过trim+set实现更新数据，通过添加和忽略前后缀实现
@Test
public void updateForTrim() {
customer.setId(4);//前面初始化数据没有id
customer.setUsername("赵六通过trim更新");
customer.setJob("赵六董事长了哟");
System.out.println(customerMapper.updateForTrim(customer));
}
<!--通过trim+set实现更新数据，通过添加和忽略前后缀实现 添加前缀set，忽略后缀的逗号,-->
<update id="updateForTrim" parameterType="com.xgf.dynamic_sql.bean.Customer">
update customer
<trim prefix="set" suffixOverrides=",">
<if test="username!=null and username!=''">
username=#{username},
</if>
<if test="job!=null and job!=''">
job=#{job},
</if>
</trim>
where id=#{id}
</update>
运行sql：Preparing: update customer set username=?, job=? where id=?
运行参数：Parameters: 赵六通过trim更新(String), 赵六董事长了哟(String), 4(Integer)
运行结果：Updates: 1(更新数据一条，更新成功)
8. foreach标签查询
//通过foreach标签查询 - 参数是list集合id (相当于select * from tableName where id in {1,2,3} 查询结果为集合)
@Test
public void getCustomerByIdsForeach() {
ArrayList<Integer> idList = new ArrayList<>();
idList.add(2);
idList.add(3);
idList.add(4);
System.out.println(customerMapper.getCustomerByIdsForeach(idList));
}
<!--foreach标签
collection：传入的集合 item：循环出来的数据起别名 open：以什么开始
colose：以什么结束 separator： 分隔符 index： 循环计数器
通过where标签和if标签 判断如果传入的列表为空，则后面的sql语句不添加，就查询所有customer
-->
<select id="getCustomerByIdsForeach" parameterType="list" resultType="com.xgf.dynamic_sql.bean.Customer">
select id,username,job
from customer
<where>
<if test="list!=null and list.size()>0">
id in
<foreach collection="list" item="id" open="(" close=")" separator="," index="index" >
#{id}
</foreach>
</if>
</where>
</select>
运行sql：Preparing: select id,username,job from customer WHERE id in ( ? , ? , ? )
运行参数：Parameters: 2(Integer), 3(Integer), 4(Integer)
运行结果：[Customer{id=2, username=‘李四’, job=‘项目经理’}, Customer{id=3, username=‘王五’, job=‘测试员’}, Customer{id=4, username=‘赵六通过trim更新’, job=‘赵六董事长了哟’}]
9. bind标签
//bind标签，解决可移植性问题 ，解决不同数据库拼接函数或连接符号的不同(防止sql注入)
@Test
public void getCustomerByNameAndJobForBind() {
customer.setUsername("李四");
customer.setJob("项目经理");
System.out.println(customerMapper.getCustomerByNameAndJobForBind(customer));
}
<!-- bind标签 解决可移植性问题 解决不同数据库拼接函数或连接符号的不同
concat()数据库连接，将字符串拼接成一个字符串，是MySQL数据库独有的，在别的数据库中不能识别
bind标签，name取名给其它地方调用，可以随便取， _parameter.getXXX()调用get方法
-->
<select id="getCustomerByNameAndJobForBind" parameterType="com.xgf.dynamic_sql.bean.Customer" resultType="com.xgf.dynamic_sql.bean.Customer">
<if test="username!=null and username!=''">
<bind name="pattern" value="'%' + _parameter.getUsername() + '%'" />
</if>
select id,username,job
from customer
<where>
<if test="username!=null and username!=''">
and username like #{pattern}
</if>
<if test="job!=null and job!=''">
and job=#{job}
</if>
</where>
</select>
运行sql：Preparing: select id,username,job from customer WHERE username like ? and job=?
运行参数：Parameters: %李四%(String), 项目经理(String)
运行结果：[Customer{id=2, username=‘李四’, job=‘项目经理’}]
10. 通过foreach和insert，实现foreach循环批量插入
//通过foreach和insert，实现foreach循环批量插入
@Test
public void insertCustomerList() {
//customer表的主键id自动增长所以不需要赋值
Customer customer1 = new Customer("钱一","大数据工程师");
Customer customer2 = new Customer("孙二","AI工程师");
Customer customer3 = new Customer("马三","运维工程师");
ArrayList<Customer> customerList = new ArrayList<>();
customerList.add(customer1);
customerList.add(customer2);
customerList.add(customer3);
System.out.println(customerMapper.insertCustomerList(customerList));
}
<!--foreach和insert共同使用 实现foreach循环批量插入
item是每次从集合中取出一个对象的别名-->
<insert id="insertCustomerList" parameterType="list">
insert into customer(username,job) values
<foreach collection="list" separator="," item="customer">
(#{customer.username},#{customer.job})
</foreach>
</insert>
运行sql：Preparing: insert into customer(username,job) values (?,?) , (?,?) , (?,?)
运行参数：Parameters: 钱一(String), 大数据工程师(String), 孙二(String), AI工程师(String), 马三(String), 运维工程师(String)
运行结果：true(Updates: 3) (更新了三条sql语句，实现循环批量出入成功)
运行sql：Preparing: select id,username,job from customer WHERE username like ? and job=?
运行参数：Parameters: %李四%(String), 项目经理(String)
运行结果：[Customer{id=2, username=‘李四’, job=‘项目经理’}]
代码地址：https://github.com/strive-xgf/SSM/commit/0ca1f098051de82289d59e67aa2a5c88aacffcff
本文地址：https://blog.csdn.net/qq_40542534/article/details/108838091