Java持久层之MyBatis：03_MyBatis动态SQL

于 2020-08-10 21:57:14 发布
阅读量131 收藏
点赞数
分类专栏： Java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/MOOG007/article/details/107922493
版权

文章目录

什么是动态SQL

  • MyBatis的核心：对SQL语句进行灵活操作，通过表达式进行判断，对SQL进行灵活拼接、组装

动态SQL的入门案例

  • 需求：用户信息综合查询列表和用户信息查询列表总数这两个statement的定义使用动态SQL
  • 需要对查询条件进行判断：如果输入参数不为空才进行查询条件拼接
    在这里插入图片描述
    在这里插入图片描述
  • 数据库的配置文件
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis
jdbc.username=root
jdbc.password=root
  • 全局配置文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
	
	<!-- 第一部分: 配置数据源 -->
	<properties resource="db.properties"></properties>
	<!-- 与Spring整合后 environments配置都将废除-->
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" /><!-- 使用JDBC事务管理: 事务控制由MyBatis管理-->
			<dataSource type="POOLED"><!-- 数据库连接池: 由MyBatis管理 -->
				<property name="driver" value="${jdbc.driver}" />
				<property name="url" value="${jdbc.url}" />
				<property name="username" value="${jdbc.username}" />
				<property name="password" value="${jdbc.password}" />
			</dataSource>
		</environment>
	</environments>
	
	<mappers>
		<package name="com.ycom1024.mybatis.mapper"/>
	</mappers>
</configuration>
  • mapper接口的代码
public interface UserMapper {
	/**
	 * 根据查询添加查询用户：
	 * (1) 如果性别存则需要比较性别; 
	 * (2) 如果姓名存在就模糊匹配姓名; 
	 * (3) 如果性别与姓名都没有则查询所有
	 * @param vo 封装查询条件
	 * @return 如果能够查询到用户就返回用户列表; 如果一个满足条件的用户也没有就返回空列表
	 * @throws Exception SQL执行异常
	 */
	List<UserCustom> findUserList(UserQueryVo vo) throws Exception;
}
  • 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="com.ycom1024.mybatis.mapper.UserMapper">
	<select id="findUserList"
			parameterType="com.ycom1024.mybatis.po.UserQueryVo"
			resultType="com.ycom1024.mybatis.po.UserCustom">
		<!-- SELECT * FROM USER WHERE sex = 1 AND username LIKE '%i%' -->
		SELECT * FROM USER WHERE 1 = 1   
		<if test="userCustom != null">
			<if test="userCustom.sex != null and userCustom.sex != ''">
				AND sex = #{userCustom.sex}
			</if>
			<if test="userCustom.username != null and userCustom.username != ''">
				AND username LIKE '%${userCustom.username}%'
			</if>
		</if>
	</select>
</mapper>
  • 测试代码
@Test
void testFindUserList() throws Exception {
	SqlSessionFactory sqlSessionFactory = MyBatisUtils.getSqlSessionFactory();
	SqlSession sqlSession = sqlSessionFactory.openSession();
	
	UserCustom userCustom = new UserCustom();
	// userCustom.setSex("1");
	// userCustom.setUsername("i");
	UserQueryVo vo = new UserQueryVo();
	vo.setUserCustom(userCustom);
	
	UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
	List<UserCustom> userCustoms = userMapper.findUserList(vo);
	
	System.out.println(userCustoms);
	System.out.println(userCustoms.size());
	
	sqlSession.close();
}
------------------------------------------------------------------------
[User [id=1, username=王五, birthday=null, sex=2, address=null], User [id=10, username=张三, birthday=Thu Jul 10 00:00:00 CST 2014, sex=1, address=北京市], User [id=16, username=张小明, birthday=null, sex=1, address=河南郑州], User [id=22, username=陈小明, birthday=null, sex=1, address=河南郑州], User [id=24, username=张三丰, birthday=null, sex=1, address=河南郑州], User [id=25, username=陈小明, birthday=null, sex=1, address=河南郑州], User [id=26, username=王五, birthday=null, sex=null, address=null], User [id=27, username=rose, birthday=null, sex=null, address=null], User [id=28, username=jim, birthday=null, sex=1, address=null], User [id=29, username=kitty, birthday=null, sex=2, address=null], User [id=32, username=Admin, birthday=null, sex=1, address=China], User [id=33, username=administrator, birthday=null, sex=null, address=China]]
12
********************************************************************************
@Test
void testFindUserList() throws Exception {
	SqlSessionFactory sqlSessionFactory = MyBatisUtils.getSqlSessionFactory();
	SqlSession sqlSession = sqlSessionFactory.openSession();
	
	UserCustom userCustom = new UserCustom();
	userCustom.setSex("1");
	// userCustom.setUsername("i");
	UserQueryVo vo = new UserQueryVo();
	vo.setUserCustom(userCustom);
	
	UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
	List<UserCustom> userCustoms = userMapper.findUserList(vo);
	
	System.out.println(userCustoms);
	System.out.println(userCustoms.size());
	
	sqlSession.close();
}
------------------------------------------------------------------------
[User [id=10, username=张三, birthday=Thu Jul 10 00:00:00 CST 2014, sex=1, address=北京市], User [id=16, username=张小明, birthday=null, sex=1, address=河南郑州], User [id=22, username=陈小明, birthday=null, sex=1, address=河南郑州], User [id=24, username=张三丰, birthday=null, sex=1, address=河南郑州], User [id=25, username=陈小明, birthday=null, sex=1, address=河南郑州], User [id=28, username=jim, birthday=null, sex=1, address=null], User [id=32, username=Admin, birthday=null, sex=1, address=China]]
7
********************************************************************************
@Test
void testFindUserList() throws Exception {
	SqlSessionFactory sqlSessionFactory = MyBatisUtils.getSqlSessionFactory();
	SqlSession sqlSession = sqlSessionFactory.openSession();
	
	UserCustom userCustom = new UserCustom();
	//userCustom.setSex("1");
	userCustom.setUsername("i");
	UserQueryVo vo = new UserQueryVo();
	vo.setUserCustom(userCustom);
	
	UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
	List<UserCustom> userCustoms = userMapper.findUserList(vo);
	
	System.out.println(userCustoms);
	System.out.println(userCustoms.size());
	
	sqlSession.close();
}
------------------------------------------------------------------------
[User [id=28, username=jim, birthday=null, sex=1, address=null], User [id=29, username=kitty, birthday=null, sex=2, address=null], User [id=32, username=Admin, birthday=null, sex=1, address=China], User [id=33, username=administrator, birthday=null, sex=null, address=China]]
4
********************************************************************************
@Test
void testFindUserList() throws Exception {
	SqlSessionFactory sqlSessionFactory = MyBatisUtils.getSqlSessionFactory();
	SqlSession sqlSession = sqlSessionFactory.openSession();
	
	UserCustom userCustom = new UserCustom();
	userCustom.setSex("1");
	userCustom.setUsername("i");
	UserQueryVo vo = new UserQueryVo();
	vo.setUserCustom(userCustom);
	
	UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
	List<UserCustom> userCustoms = userMapper.findUserList(vo);
	
	System.out.println(userCustoms);
	System.out.println(userCustoms.size());
	
	sqlSession.close();
}
------------------------------------------------------------------------
[User [id=28, username=jim, birthday=null, sex=1, address=null], User [id=32, username=Admin, birthday=null, sex=1, address=China]]
2
  • 楼上的案例说明了：我们只在mapper映射文件中编写了一次SQL代码：但是在Java代码中传入不同状态的查询条件得到的结果是不同的：SQL语句能够适应Java代码动态的生成：动态SQL
  • 在楼上的mapper配置文件中的SQL语句中我们使用where 1 = 1的写法：改写法是在原生JDBC程序中用来动态拼接SQL的时候使用的：但是在mybatis中可以不用这么写：解决方案如下：使用where标签：where标签可以自动去掉查询条件中的第一个AND
<?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.ycom1024.mybatis.mapper.UserMapper">
	<select id="findUserList"
			parameterType="com.ycom1024.mybatis.po.UserQueryVo"
			resultType="com.ycom1024.mybatis.po.UserCustom">
		<!-- SELECT * FROM USER WHERE sex = 1 AND username LIKE '%i%' -->
<!-- 		SELECT * FROM USER WHERE 1 = 1   
		<if test="userCustom != null">
			<if test="userCustom.sex != null and userCustom.sex != ''">
				AND sex = #{userCustom.sex}
			</if>
			<if test="userCustom.username != null and userCustom.username != ''">
				AND username LIKE '%${userCustom.username}%'
			</if>
		</if> -->
		
		SELECT * FROM USER<!--  WHERE 1 = 1  -->
		<!-- where标签可以自动去掉查询条件中的第一个AND -->
		<where>
			<if test="userCustom != null">
				<if test="userCustom.sex != null and userCustom.sex != ''">
					AND sex = #{userCustom.sex}
				</if>
				<if test="userCustom.username != null and userCustom.username != ''">
					AND username LIKE '%${userCustom.username}%'
				</if>
			</if> 
		</where> 
	</select>
</mapper>

SQL片段

  • 需求：将楼上实现的冬天SQL判断代码块抽取出来组成一个SQL片段，其他的statement中就可以引用SQL片段：方便程序猿开发
<?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.ycom1024.mybatis.mapper.UserMapper">
	<!-- 定义SQL片段 
		id属性: SQL片段的标识
	-->
	<sql id="quesy_user_list">
		<!-- userCustom是pojo对象中的一个属性 -->
		<if test="userCustom != null">
			<!-- userCustom.sex是pojo包装类对象中的userCustom属性中的sex属性 -->
			<if test="userCustom.sex != null and userCustom.sex != ''">
				AND sex = #{userCustom.sex}
			</if>
			<if test="userCustom.username != null and userCustom.username != ''">
				AND username LIKE '%${userCustom.username}%'
			</if>
		</if> 
	</sql>
	
	<select id="findUserList"
			parameterType="com.ycom1024.mybatis.po.UserQueryVo"
			resultType="com.ycom1024.mybatis.po.UserCustom">
		SELECT * FROM USER
		<where>
			<!-- 引用SQL片段
				refid属性值是SQL片段的标识,即SQL片段的id属性值
			-->
			<include refid="quesy_user_list"></include>
		</where> 
	</select>
</mapper>
  • 经验：基于单表来定义SQL片段，这样的话这个SQL片段的可重用性才高；在SQL片段中不要包括where

向SQL传递数组或List

  • 向SQL传递数组或List，mybatis需要foreach解析
  • 需求：在用户查询列表中增加多个id，有两个方法：
    （1） SELECT * FROM USER WHERE id = 1 OR id = 10 OR id = 27
    （2） SELECT * FROM USER WHERE id IN (1, 10, 27)
<?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.ycom1024.mybatis.mapper.UserMapper">
	<!-- 定义SQL片段 
		id属性: SQL片段的标识
	-->
	<sql id="quesy_user_list">
		<!-- userCustom是pojo对象中的一个属性 -->
		<if test="userCustom != null">
			<!-- userCustom.sex是pojo包装类对象中的userCustom属性中的sex属性 -->
			<if test="userCustom.sex != null and userCustom.sex != ''">
				AND sex = #{userCustom.sex}
			</if>
			<if test="userCustom.username != null and userCustom.username != ''">
				AND username LIKE '%${userCustom.username}%'
			</if>
		</if> 
	</sql>
	
	<select id="findUserList"
			parameterType="com.ycom1024.mybatis.po.UserQueryVo"
			resultType="com.ycom1024.mybatis.po.UserCustom">
		SELECT * FROM USER
		<where>
			<!-- 引用SQL片段
				refid属性值是SQL片段的标识,即SQL片段的id属性值
			-->
			<include refid="quesy_user_list"></include>
			<!-- ids是pojo的一个属性 -->
			<if test="ids != null">
				<!-- AND (id = 1 OR id = 10 OR id = 27) -->
				<!-- foreach标签属性说明
					collection属性: pojo对象中的集合属性
					item属性：每个遍历生成的对象
					open属性：开始遍历时拼接的串
					close属性：结束遍历时拼接的串
					separator属性：遍历两个对象中需要拼接的串
				-->
				<foreach collection="ids" item="user_id"
					open="AND (" close=")" separator="OR">
						<!-- user_id是foreach标签的item属性值 -->
						id = #{user_id}
				</foreach>
			</if>
		</where> 
	</select>
</mapper>
  • 测试代码
@Test
void testFindUserList() throws Exception {
	SqlSessionFactory sqlSessionFactory = MyBatisUtils.getSqlSessionFactory();
	SqlSession sqlSession = sqlSessionFactory.openSession();
	

	List<Integer> ids = new ArrayList<>();
	ids.add(1);
	ids.add(10);
	ids.add(27);
	UserQueryVo vo = new UserQueryVo();
	vo.setIds(ids);

	
	UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
	List<UserCustom> userCustoms = userMapper.findUserList(vo);
	
	System.out.println(userCustoms);
	System.out.println(userCustoms.size());
	
	sqlSession.close();
}
-----------------------------------------------------------------------------
[User [id=1, username=王五, birthday=null, sex=2, address=null], User [id=10, username=张三, birthday=Thu Jul 10 00:00:00 CST 2014, sex=1, address=北京市], User [id=27, username=rose, birthday=null, sex=null, address=null]]
3
  • 另一种语法
    在这里插入图片描述
点康慕
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值