mybatis语句

1. 查询时，需要查询结果中的列名与返回值类型中的属性名保持一致

假设在t_user表中添加了新的名为is_delete的字段：

alter table t_user add column is_delete int;

则对应的User类中也应该添加新的属性，以与对应：

	public class User {
	
		private Integer id;
		private String username;
		private String password;
		private Integer age;
		private String phone;
		private String email;
		private Integer isDelete;

当查询数据时，需要自定义别名，使得查询结果中的列名与返回值类型中的属性名保持一致：

	<select id="findById"
		resultType="cn.tedu.mybatis.User">
		SELECT 
			id,username,
			password,age,
			phone,email,
			is_delete AS isDelete
		FROM 
			t_user 
		WHERE 
			id=#{id}
	</select>

其对应关系如图所示：

2. 简单的关联表查询数据

假设需要创建“部门信息表”：

CREATE TABLE t_department (
	id INT AUTO_INCREMENT COMMENT '部门id',
	name VARCHAR(30) UNIQUE NOT NULL COMMENT '部门名称',
	PRIMARY KEY(id)
) DEFAULT CHARSET=utf8;

然后，添加一些模拟数据：

INSERT INTO t_department (name) VALUES ('软件研发部'),('人力资源部'),('财务部'),('销售部');

并且，每个用户都归属于某个部门：

ALTER TABLE t_user ADD COLUMN department_id INT;

最后，为用户分配部门：

UPDATE t_user SET department_id=1 WHERE id IN (21,28,32);
UPDATE t_user SET department_id=2 WHERE id IN (24,25,26);
UPDATE t_user SET department_id=3 WHERE id IN (22,27,30);
UPDATE t_user SET department_id=4 WHERE id IN (23,29,31);

假设存在需求“查询某用户的信息，并显示该用户的部门的名称”，必须通过关联查询才可以得到所需要的结果，需要执行的SQL语句大致是：

select 
	* 
from 
	t_user 
left join 
	t_department 
on 
	t_user.department_id=t_department.id 
where 
	t_user.id=25;

如果需要使用MyBatis开发该功能，首先，目前并没有某个类型可以封装查询结果！因为创建的User类是与t_user表相对应的，例如User这样的类称之为实体类(entity)，实体类肯定不满足多表查询需求的，所以，对于这种情况，需要自行另创建VO类(value object)，这种类的设计应该与查询结果或查询需求相对应：

public class UserVO {
	private Integer id;
	private String username;
	private String password;
	private Integer age;
	private String phone;
	private String email;
	private Integer isDelete;
	private Integer departmentId;
	private String departmentName;
}

VO类的设计方式与实体类基本一致，只是这2种类的定位不同而已，实体类是与数据表对应的，VO类是与查询结果对应的。

然后，可以将此前的findById(Integer id)的返回值类型修改为UserVO：

UserVO findById(Integer id);

且映射的配置：

<select id="findById"
	resultType="cn.tedu.mybatis.UserVO">
	SELECT 
		t_user.id,username,
		password,age,
		phone,email,
		is_delete AS isDelete,
		department_id AS departmentId,
		name AS departmentName
	FROM 
		t_user 
	LEFT JOIN
		t_department
	ON
		t_user.department_id=t_department.id
	WHERE 
		t_user.id=#{id}
</select>

3. 使用处理1对多的关联查询

假设存在需求“查询某部门信息，且显示出该部门所有的员工”，需要执行的SQL语句大致是：

select 
	*
from
	t_department
left join
	t_user
on
	t_department.id=t_user.department_id
where
	t_department.id=?

这类查询可能查到多条结果，取决于该部门的员工数量，但是，查询的需求却是查询“某1个”部门的信息！在设计查询的方法时，就需要某1个类型可以封装多条查询结果，可以：

public class DepartmentVO {
	private Integer id;
	private String name;
	private List<User> users;
}

则对应的抽象方法可以是：

public interface DepartmentMapper {
	DepartmentVO findById(Integer id);
}

此次查询必然可能出现多个结果，MyBatis并不知道如何将多个结果封装到1个对象中，所以，按照此前的查询做法，必然会出错！例如：

Caused by: org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 3

在这种情况下，需要自定义<resultMap>节点，用于指导MyBatis如何将结果进行封装：

	<!-- id：自定义名称 -->
	<!-- type：返回结果的类型 -->
	<resultMap id="DepartmentVO_Map"
		type="cn.tedu.mybatis.DepartmentVO">
		<!-- id节点：用于配置主键 -->
		<!-- result节点：用于配置非主键 -->
		<!-- column：查询结果的列名 -->
		<!-- property：返回结果类型中的属性名 -->
		<!-- 无论哪个节点，都是用于告之MyBatis将查询结果中哪一列的数据放到返回类型中的哪个属性中 -->
		<id column="id" property="id"/>
		<result column="name" property="name"/>
		<!-- collection节点：用于配置1对多关系 -->
		<!-- ofType：集合中的元素类型 -->
		<collection property="users"
			ofType="cn.tedu.mybatis.User">
			<id column="uid" property="id"/>
			<result column="username" property="username"/>
			<result column="password" property="password"/>
			<result column="age" property="age"/>
			<result column="phone" property="phone"/>
			<result column="email" property="email"/>
			<result column="is_delete" property="isDelete"/>
		</collection>
	</resultMap>

	<select id="findById"
		resultMap="DepartmentVO_Map">
		SELECT 
			t_department.id, name,
			t_user.id AS uid, username,
			password, age,
			phone, email,
			is_delete
		FROM
			t_department
		LEFT JOIN
			t_user
		ON
			t_department.id=t_user.department_id
		WHERE
			t_department.id=#{id}
	</select>

查询结果例如：

DepartmentVO [
	id=1, name=软件研发部, 
	users=[
		User [id=21, username=Mike01, password=8888, age=18, phone=13800138001, email=Mike@qq.com, isDelete=0], 
	
		User [id=28, username=Mike08, password=8888, age=25, phone=13800138001, email=Mike@qq.com, isDelete=0], 

		User [id=32, username=Mike11, password=8888, age=26, phone=13800138001, email=Mike@qq.com, isDelete=0]
	]
]

4. 动态SQL

动态SQL指的是可以在配置SQL语句添加一些特殊的标签，例如<if>、<foreach>等，可以根据参数的不同，最终生成不同的SQL语句，则称之为动态SQL。

例如：根据若干个id删除数据，大致的SQL语句是：

delete from t_user where id in (1,3,5,7,9)

在实际应用中，以上IN关键字后侧的括号中的值是不确定的，不光是值本身，值的数量也是不确定，并且各值之间需要使用逗号进行分隔，当值的数量不确定时，逗号的数量也是无法确定的！

首先，需要在UserMapper.java接口中添加抽象方法：

Integer deleteByIds(List<Integer> ids);

在设计参数时，可以使用List集合类型，也可以使用数组类型。

然后，在UserMapper.xml中配置以上抽象方法的映射：

	<delete id="deleteByIds">
		DELETE FROM t_user
		WHERE id IN (
			<foreach collection="list"
				item="id" separator=",">
				#{id}
			</foreach>
		)
	</delete>

在配置<foreach>节点时：

• collection：需要被遍历的集合或数据，如果抽象方法只有1个参数时，如果参数的类型是List集合，则取值为list，如果参数类型是数组，则取值为array；如果抽象方法有多个参数，则该属性取值为@Param("xx")注解中使用的名称。

• item：遍历过程中，集合中的元素的名称，在<foreach>子级位置，可以使用#{item值}表示被遍历到的元素的值。

• separator：分隔符。

• open和close：遍历生成的SQL语句部分的最左侧字符和最右侧字符。

练习1：根据若干个id查询用户列表，例如查询id=26和id=29和id=30的用户的数据：

	List<User> findByIds(Integer[] ids);

	<select id="findByIds"
		resultType="cn.tedu.mybatis.User">
		SELECT
			id, username,
			password, age,
			phone, email,
			is_delete AS isDelete
		FROM
			t_user
		WHERE
			id IN
			<foreach collection="array"
				item="id" separator=","
				open="(" close=")">
				#{id}
			</foreach>
	</select>

练习2：将多个用户的密码改成某个值，例如将id=25、id=27、id=28的用户的密码都改成123456，该功能中，id值是由用户指定的，新密码也是由用户指定的：

	Integer updatePasswordByIds(
		@Param("ids") Integer[] ids, 
		@Param("password") String password);

	<update id="updatePasswordByIds">
		UPDATE 
			t_user
		SET
			password=#{password}
		WHERE
			id IN
			<foreach collection="ids"
				item="id" separator=","
				open="(" close=")">
				#{id}
			</foreach>
	</update>

5. #{}与${}占位符

在MyBatis中，配置SQL语句时，可以使用#{}与${}这2种占位符。

使用#{}占位符，可以用于占位某些值，也就是在SQL中写值的位置，都可以使用这种占位符(此前在学习JDBC时使用?的位置)；而${}可以表示SQL语句的任何部分！

在使用#{}对某个值进行占位时，框架对整个SQL语句是有预编译处理的，无需考虑该值的数据类型的问题；而使用${}占位时，框架的处理方式其实就是非常单纯的字符串拼接，需要考虑数据类型的问题，如果占位的值中包括字符串类型的值，则必须使用''框住值！

由于#{}只能对某个值进行占位，SQL语句本身是相对固定的，所以，这种做法实现的功能的局限性就非常明显，由于是预编译的，没有SQL注入风险，且工作效率较高！而${}可以随意占位，功能可以非常灵活，但是，不是预编译的，有SQL注入风险，工作效率较低。