MyBatis第四篇：动态SQL及关联查询

最新推荐文章于 2024-04-26 10:00:39 发布

sky白云悠悠

最新推荐文章于 2024-04-26 10:00:39 发布

阅读量432

点赞数

CC 4.0 BY-SA版权

分类专栏： MyBatis 文章标签： MyBatis MyBatis动态SQL MyBatis关联查询

本文链接：https://blog.youkuaiyun.com/shadow_1010/article/details/94989075

MyBatis 专栏收录该内容

6 篇文章

订阅专栏

本文详细介绍了MyBatis的动态SQL，包括IF、Where、Set、Sql和Foreach标签的使用，以及它们在处理不同场景下的功能。同时，文章还深入探讨了一对一和一对多的关联查询，提供了映射文件和测试案例，帮助读者理解如何在实际操作中应用这些概念。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

7. 动态SQL

当映射文件中sql语句为：select * from `user` where username like '%${username}%' and sex='#{sex}'时，传入的参数没有经过验证，有可能会出现null或者‘’的情况，因此，结果会偏离我们的预期，所以我们要动态的使用sql，以避免此种情况。

7.1 IF标签

if标签相当于sql语句中的if关键字

作用：根据传入参数的情况，拼装sql语句

1. UserMapper.java

	List<User> queryUserByUsernameAndSex(User user);

2. UserMapper.xml

	<select id="queryUserByUsernameAndSex" parameterType="user" resultType="user">
		select * from user where
		
		<!-- if标签：判断用户名称不为空，且不为空字符串，才作为查询条件 -->
		<if test="username != null and username != ''">
			username like CONCAT('%', #{username}, '%')
		</if>
		
		<!-- if标签：判断用户性别不为空，且不为空字符串，才作为查询条件  -->
		<if test="sex != null and sex != ''">
			and sex = #{sex}
		</if>
	</select>

3. 测试

	@Test
	public void queryUserByUsernameAndSex() {
		
		SqlSession sqlSession = sqlSessionFactory.openSession();
		
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		
		User user = new User();
		user.setUsername("Y");
		
		List<User> users = userMapper.queryUserByUsernameAndSex(user);
		
		System.out.println(users);
		
		sqlSession.close();
	}

4. 结果

7.2 Where标签

where标签相当于sql语句中的where关键字

作用：

根据传入的参数情况，智能的去掉多余的where关键字
根据传入的参数情况，智能的去掉多余的and，or关键字

1. UserMap.xml

	<select id="queryUserByUsernameAndSex" parameterType="user" resultType="user">
		select * from user <!-- where -->
		
		<where>
		
			<!-- if标签：判断用户名称不为空，且不为空字符串，才作为查询条件 -->
			<if test="username != null and username != ''">
				username like CONCAT('%', #{username}, '%')
			</if>
			
			<!-- if标签：判断用户性别不为空，且不为空字符串，才作为查询条件  -->
			<if test="sex != null and sex != ''">
				and sex = #{sex}
			</if>
			
		</where>
	</select>

2. 测试

	@Test
	public void queryUserByUsernameAndSex() {
		
		SqlSession sqlSession = sqlSessionFactory.openSession();
		
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		
		User user = new User();
//		user.setUsername("Y");
		
		List<User> users = userMapper.queryUserByUsernameAndSex(user);
		
		System.out.println(users);
		
		sqlSession.close();
	}

结果：

7.3 Set标签

Set标签相当于sql语句中的set关键字

作用：根据传入的参数情况，智能的去掉多余的逗号：“,”

1. UserMapper.java

	void update(User user);

2. UserMapper.xml

	<update id="update" parameterType="user" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
		
		update user 
		<set>
			<if test="username != null and username != ''">
				username = #{username},
			</if>
			<if test="sex != null and sex != ''">
				sex = #{sex}
			</if>
		</set>
		<where>
			id = #{id}
		</where>
	
	</update>

3. 测试

	@Test
	public void update() {
		
		SqlSession sqlSession = sqlSessionFactory.openSession(true);
		
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		
		User user = new User();
		user.setId(1);
		user.setUsername("Jing");
		
		userMapper.update(user);
		
		System.out.println(user);
		
		sqlSession.close();
	}

结果：

7.4 Sql标签

作用：提取公共的sql语句片段。

1. UserMapper.java

	List<User> queryAllUser();

2. UserMapper.xml

	<!-- sql标签：提取公共的sql语句片段
			id属性：唯一标识，通过id引用该sql片段 -->
	<sql id="user_properties">
		username, sex, birthday, mobilephone, email, password
	</sql>
		
	<select id="queryAllUser" resultType="user">
	
		<!-- include标签：引用sql片段
				refid属性：要引用的sql语句片段的id -->
		select <include refid="user_properties"></include> from user
	</select>

3. 测试

	@Test
	public void queryAll() {
		
		SqlSession sqlSession = sqlSessionFactory.openSession();
		
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		
		List<User> users = userMapper.queryAllUser();
		
		System.out.println(users);
		
		sqlSession.close();
	}

结果：

7.5 Foreach标签

作用：循环处理参数集合（list，数组）。

7.5.1 批量新增

1. UserMapper.java

	void batchAdd(List<User> users);

2. UserMapper.xml

	<insert id="batchAdd" parameterType="list">
		insert into user ( <include refid="user_properties"></include> ) values 
		
		<!--foreach标签：循环处理参数集合
		 	  collection属性：参数集合，这里是list
		 	  item属性：当前遍历的对象 
		 	  separator属性：指定分割符-->
		<foreach collection="list" item="user" separator=",">
			(#{user.username}, #{user.sex}, #{user.birthday}, #{user.mobilephone}, #{user.email}, #{user.password})
		</foreach>
	</insert>

3. 测试

	@Test
	public void batchAdd() {
		
		SqlSession sqlSession = sqlSessionFactory.openSession(true);
		
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		
		List<User> users = new ArrayList<User>();
		for (int i = 0; i < 10; i++) {
			User user = new User("Yong"+i, "男", new Date(), "186*******"+i, i+"110***@qq.com", "123456");
			users.add(user);
		}
		
		userMapper.batchAdd(users);
		
		sqlSession.close();
	}

结果：

7.5.2 批量删除

1. UserMapper.java

	void batchDelete(Integer[] ids);

2. UserMapper.xml

	<!-- parameterType：当参数传递的是集合或者数组，都需要使用list -->
	<delete id="batchDelete" parameterType="list">
		delete from user
		<where>
			<!--foreach标签：循环处理参数集合
			 	 collection属性：参数集合，这里是数组array 
			 	 item属性：当前遍历的元素
			 	 open属性：拼装的sql语句片段的开始部分
			 	 close属性：拼装的sql语句片段的结束部分
			 	 separator属性：元素之间的分割符-->
			<foreach collection="array" item="id" open=" id in (" close=")" separator=",">
				#{id}
			</foreach>
		</where>
	</delete>

3. 测试

	@Test
	public void batchDelete() {
		
		SqlSession sqlSession = sqlSessionFactory.openSession(true);
		
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		
		Integer[] ids = {23, 24, 25, 26, 27};
		
		userMapper.batchDelete(ids);
		
		sqlSession.close();
	}

结果：

8. 关联查询

一对一关系、一对多关系、多对多关系（看作两个一对多关系）

8.1 一对一关联查询

1. orders表

CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL,
  `number` varchar(255) DEFAULT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

2. Orders.java

package com.yong.po;

public class Orders {

	private Integer id; // int(11) NOT NULL AUTO_INCREMENT,
	private Integer userId; // int(11) DEFAULT NULL,
	private String number; // varchar(255) DEFAULT NULL,
	private String remark; // varchar(255) DEFAULT NULL,

	private User user;

	public Orders() {
		super();
	}

	public Orders(Integer userId, String number, String remark, User user) {
		super();
		this.userId = userId;
		this.number = number;
		this.remark = remark;
		this.user = user;
	}

	public Orders(Integer id, Integer userId, String number, String remark, User user) {
		super();
		this.id = id;
		this.userId = userId;
		this.number = number;
		this.remark = remark;
		this.user = user;
	}

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public Integer getUserId() {
		return userId;
	}

	public void setUserId(Integer userId) {
		this.userId = userId;
	}

	public String getNumber() {
		return number;
	}

	public void setNumber(String number) {
		this.number = number;
	}

	public String getRemark() {
		return remark;
	}

	public void setRemark(String remark) {
		this.remark = remark;
	}

	public User getUser() {
		return user;
	}

	public void setUser(User user) {
		this.user = user;
	}

	@Override
	public String toString() {
		return "Orders [id=" + id + ", userId=" + userId + ", number=" + number + ", remark=" + remark + ", user="
				+ user + "]";
	}

}

3. OrdersMapper.java

package com.yong.mapper;

import java.util.List;

import com.yong.po.Orders;

public interface OrdersMapper {
	
	List<Orders> queryOrdersAndUser();

}

4. OrdersMapper.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">
<!-- namespace属性：名称空间，相当于java中package，用于防止sql语句名称冲突 -->
<!-- mapper代理开发方法中，namespace属性值必须是mapper接口的全路径 -->
<mapper namespace="com.yong.mapper.OrdersMapper">

	<!-- 配置订单到用户的一对一关联关系
			 type属性：映射的类型
			 id属性：唯一标识，通过id引用该resultMap-->
	<resultMap type="orders" id="Orders_properties">
		<!--配置主键对应关系  -->
		<id column="id" property="id"/>
		
		<!-- 配置普通字段对应关系 -->
		<result column="userId" property="userId"/>
		<result column="number" property="number"/>
		<result column="remark" property="remark"/>
		
		<!-- association标签：配置一对一关联关系
				 property属性：要映射的属性名称
				 javaType属性：要映射的属性的类型（必须要指定）-->
		<association property="user" javaType="user">
			<!--配置主键对应关系  -->
			<id column="userId" property="id"/>
			
			<!-- 配置普通字段对应关系 -->
			<result column="username" property="username"/>
			<result column="sex" property="sex"/>
			<result column="birthday" property="birthday"/>
			<result column="mobilephone" property="mobilephone"/>
			<result column="email" property="email"/>
			<result column="password" property="password"/>
		</association>
		
	</resultMap>

	<select id="queryOrdersAndUser" resultMap="Orders_properties">
		SELECT
			o.id,
			o.userId,
			o.number,
			o.remark,
			u.username,
			u.sex,
			u.birthday,
			u.mobilephone,
			u.email 
		FROM
			orders o
			LEFT JOIN USER u ON o.userId = u.id
	</select>
	
</mapper>

5. 测试

package com.yong.test;

import java.io.IOException;
import java.io.InputStream;
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.Before;
import org.junit.Test;

import com.yong.mapper.OrdersMapper;
import com.yong.po.Orders;

public class MyBatisTest04 {

	private SqlSessionFactory sqlSessionFactory = null;
	
	@Before
	public void initSqlSessionFactory() throws IOException {
		
		InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
		
		SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
		
		sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
	}
	
	@Test
	public void queryOrdersAndUser() {
		
		SqlSession sqlSession = sqlSessionFactory.openSession();
		
		OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
		
		List<Orders> list = ordersMapper.queryOrdersAndUser();
		
		System.out.println(list);
		
		sqlSession.close();
	}
	
}

结果：

8.2 一对多关联查询

1. User.java

package com.yong.po;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

public class User implements Serializable {

	private Integer id; // int(11) NOT NULL AUTO_INCREMENT,
	private String username; // varchar(255) DEFAULT NULL,
	private String sex; // varchar(255) DEFAULT NULL,
	private Date birthday; // date DEFAULT NULL,
	private String mobilephone; // varchar(255) DEFAULT NULL,
	private String email; // varchar(255) DEFAULT NULL,
	private String password; // varchar(255) DEFAULT NULL,

	private List<Orders> ordersList;

	public User() {
		super();
	}

	public User(String username, String sex, Date birthday, String mobilephone, String email, String password) {
		super();
		this.username = username;
		this.sex = sex;
		this.birthday = birthday;
		this.mobilephone = mobilephone;
		this.email = email;
		this.password = password;
	}

	public User(Integer id, String username, String sex, Date birthday, String mobilephone, String email,
			String password) {
		super();
		this.id = id;
		this.username = username;
		this.sex = sex;
		this.birthday = birthday;
		this.mobilephone = mobilephone;
		this.email = email;
		this.password = password;
	}

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public Date getBirthday() {
		return birthday;
	}

	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}

	public String getMobilephone() {
		return mobilephone;
	}

	public void setMobilephone(String mobilephone) {
		this.mobilephone = mobilephone;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public List<Orders> getOrdersList() {
		return ordersList;
	}

	public void setOrdersList(List<Orders> ordersList) {
		this.ordersList = ordersList;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", sex=" + sex + ", birthday=" + birthday
				+ ", mobilephone=" + mobilephone + ", email=" + email + ", password=" + password + ", ordersList="
				+ ordersList + "]";
	}

}

2. UserMapper.java

	List<User> queryUserAndOrders();

3. UserMapper.xml

	<resultMap type="user" id="user_properties_orders">
	
		<!--id标签：配置主键的对应关系
				column属性：主键字段（表）
				property属性：主键属性（pojo）  -->
		<id column="id" property="id"/>
		
		<!--result标签：配置普通字段对应关系  -->
		<result column="username" property="username"/>
		<result column="sex" property="sex"/>
		<result column="birthday" property="birthday"/>
		<result column="mobilephone" property="mobilephone"/>
		<result column="email" property="email"/>
		
		<!--collection标签：配置一对多关联关系
			 	 property属性：要映射的属性名称
			 	 javaType属性：要映射的属性类型（可以指定，也可以不指定，建议指定） 
			 	 ofType属性：集合中存放的类型（必须要指定）-->
		<collection property="ordersList" javaType="list" ofType="orders">
			
			<!-- 配置普通字段对应关系 -->
			<result column="number" property="number"/>
			<result column="remark" property="remark"/>
		</collection>
	</resultMap>
	
	<select id="queryUserAndOrders" resultMap="user_properties_orders">
		SELECT
			u.id,
			u.username,
			u.sex,
			u.birthday,
			u.mobilephone,
			u.email,
			o.number,
			o.remark 
		FROM
			USER u
			LEFT JOIN orders o ON u.id = o.userId
	</select>

4. 测试

	@Test
	public void queryUserAndOrders() {
		
		SqlSession sqlSession = sqlSessionFactory.openSession();
		
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		
		List<User> users = userMapper.queryUserAndOrders();
		
		System.out.println(users);
		
		sqlSession.close();
	}

结果：