MyBatis基础知识笔记【中】

本篇博客内容：

1. SQL语句的参数传递
2. MyBatis 实现 “一对一” 关联查询
3. MyBatis 实现 “一对一” 分步查询

---

5 SQL语句的参数传递

5.1 单个普通(基本/包装+String)参数

• 这种情况 MyBatis 可直接使用这个参数，不需要经过任何处理。
• 取值：#{随便写}

例如我们add的代码中直接用 #{id}

5.2 POJO（bean对象）

• 当这些参数属于我们业务POJO时，我们直接传递POJO
• 取值: #{POJO的属性名}

例如delete，search其他例子

5.3 多个参数

5.3.1 Mybatis自动包装成 map

• 任意多个参数，都会被MyBatis重新包装成一个Map传入。Map的key是param1，param2，或者arg0，arg1…，就是参数的值
• 取值: #{arg0 arg1 arg2 … / param1 param2 …}

注意：arg 从0开始，param从1开始

例子：

定义接口方法

public Member getMemberByIdAndName(Integer memberId,String name);

配置文件

<!-- public Member getMemberByIdAndName(Integer memberId,String name); -->
<select id="getMemberByIdAndName" resultType="Member">
	SELECT * FROM ssm.tbl_member WHERE member_id = #{param1} AND name = #{param2}
</select>

测试

@Test
void testSearchByIdAndName() throws Exception {
	try {
		MemberMapper dao = session.getMapper(MemberMapper.class);
		Member member = dao.getMemberByIdAndName(1, "Chen");
		System.out.println(member);
	} finally {
		session.close();
	}
}

5.3.2 直接传入Map

public Member getMemberByMap(Map<String, Object> map);

配置文件

<!-- public Member getMemberByMap(Map<String, Object> map); -->
<select id="getMemberByMap" resultType="Member">
	SELECT * FROM ssm.tbl_member WHERE member_id = #{id} AND name = #{name}
</select>

测试

@Test
void testSearchByMap() throws Exception {
	try {
		MemberMapper dao = session.getMapper(MemberMapper.class);
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("id", 2);
		map.put("name", "Noel");
		Member member = dao.getMemberByMap(map);
		System.out.println(member);
	} finally {
		session.close();
	}
}

5.4 命名参数

• 为参数使用@Param起一个名字，MyBatis就会将这些参数封装进map中，key就是我们自己指定的名字
• 取值: #{自己指定的名字 / param1 param2 … paramN}

public List<Member> getMemberBetweenBalances(@Param("min") Integer min,@Param("max")Integer max);

配置文件

<!-- public List<Member> getMemberBetweenBalances  -->
<!-- 虽然要获取多个 Member，组成 List 的形式，但 resultType 依然写 Member -->
<select id="getMemberBetweenBalances" resultType="Member">
	SELECT * FROM ssm.tbl_member 
	WHERE balance >= #{min}
	AND balance <![CDATA[<=#{max}]]>
</select>

这里小于号是特殊字符，使用 CDATA

测试

@Test
void testSearchBetweenBalance() throws Exception {
	try {
		MemberMapper dao = session.getMapper(MemberMapper.class);
		List<Member> members = dao.getMemberBetweenBalances(200, 300);
		System.out.println(members);
	} finally {
		session.close();
	}
}

---

6 一对一关联查询

以下关于查询的相关类图和EER图

6.1 级联(Cascade)封装

6.1.1 初始常规配置

1. 添加依赖
2. 创建 db.properties，log4j.xml
3. 配置 mybatis-config.xml

6.1.2 创建 bean

public class User {
	private Integer userId;
	private String name;
	private Integer gender;
	private IdCard idCard;

和 IdCard 类为关联关系

public class IdCard {
	private Integer cardNo;
	private String address;
	private String issuer;

6.1.3 Dao 和 Mapper

// UserMapper.java
public User getUserWithIdCardById(Integer userId);

<!-- public User getUserWithIdCardById(Integer userId); -->
<select id="getUserWithIdCardById" resultMap="getUserWithIdCard">
	select u.user_id, u.name, u.gender, c.card_no, c.address, c.issuer
	from t_user u left join t_idcard c
	on u.idCard = c.card_no
	where u.user_id = #{id};
</select>

<resultMap type="cyt.mybatis.bean.User" id="getUserWithIdCard">
	<id column="user_id" property="userId"/>
	<result column="name" property="name"/>
	<result column="gender" property="gender"/>
	<result column="card_no" property="idCard.cardNo"/>
	<result column="address" property="idCard.address"/>
	<result column="issuer" property="idCard.issuer"/>
</resultMap>

注意！！
这里应使用左外连接，因为会出现有user没有idcard的情况，也要保证该种情况的查询不会出错

6.1.4 测试

@Test
void testGetUser() {
	try {
		UserMapper dao = session.getMapper(UserMapper.class);
		User user = dao.getUserWithIdCardById(1);
		System.out.println(user);
	} finally {
		session.close();
	}
}

6.2 association 封装

public User getUserWithIdCardById(Integer userId);

<resultMap type="cyt.mybatis.bean.User" id="getUserWithIdCard">
	<id column="user_id" property="userId"/>
	<result column="name" property="name"/>
	<result column="gender" property="gender"/>
	<association property="idCard" javaType="cyt.mybatis.bean.IdCard">
		<id column="card_no" property="cardNo"/>
		<result column="address" property="address"/>
		<result column="issuer" property="issuer"/>
	</association>
</resultMap>

测试省略

7 一对一分布查询

7.1 添加接口方法

// UserMapper.java 接口中
public User getUserWithIdCardByIdStep(Integer userId);

// IdCardMapper.java 接口中
public IdCard getIdCardByCardNo(Integer cardNo);

7.2 配置xml

IdCardMapper.xml

<!-- public IdCard getIdCardByCardNo(Integer cardNo); -->
<select id="getIdCardByCardNo" resultType="cyt.mybatis.bean.IdCard">
	select * from t_idcard where card_no=#{id}
</select>

UserMapper.xml

<!-- public User getUserWithIdCardByIdStep(Integer userId); -->
<select id="getUserWithIdCardByIdStep" resultMap="getUserWithIdCardStep">
	select * from t_user where user_id = #{id}
</select>
<resultMap type="cyt.mybatis.bean.User" id="getUserWithIdCardStep">
	<id column="user_id" property="userId"/>
	<result column="name" property="name"/>
	<result column="gender" property="gender"/>
	<association property="idCard" select="cyt.mybatis.dao.IdCardMapper.getIdCardByCardNo" column="idCard">
	</association>
</resultMap>

7.3 添加总 config.xml 的设置 settings

<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>

7.4 测试

@Test
void testUser() {
	try {
		UserMapper dao = session.getMapper(UserMapper.class);
		User user = dao.getUserWithIdCardByIdStep(2);
		System.out.println(user.getName());
		System.out.println("===============");
		System.out.println(user.getIdCard().getAddress());
	} finally {
		session.close();
	}
}

结果：

DEBUG 10-21 15:32:54,630 ==>  Preparing: select * from t_user where user_id = ?  (BaseJdbcLogger.java:137) 
DEBUG 10-21 15:32:54,660 ==> Parameters: 2(Integer)  (BaseJdbcLogger.java:137) 
DEBUG 10-21 15:32:54,709 <==      Total: 1  (BaseJdbcLogger.java:137) 
Kwong
===============
DEBUG 10-21 15:32:54,711 ==>  Preparing: select * from t_idcard where card_no=?  (BaseJdbcLogger.java:137) 
DEBUG 10-21 15:32:54,712 ==> Parameters: 1002(Integer)  (BaseJdbcLogger.java:137) 
DEBUG 10-21 15:32:54,713 <==      Total: 1  (BaseJdbcLogger.java:137) 
GD_DG

可以看出分布查询是需要的时候才会去执行分布语句，当我们第一次使用user并没有使用IdCard的属性，故只执行第一条语句，使用时才执行第二条语句
注意!!不要忘记设置mybatis-config.xml 中的settings元素。
注意!!不要忘记设置mybatis-config.xml 中的settings元素。
注意!!不要忘记设置mybatis-config.xml 中的settings元素。

<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>

由于篇幅太长 ╮(￣▽￣)╭

下一节继续 “一对多” 和 “多对多” 查询