Mybatis查询（二）

范例：定义实现个人信息(密码)修改的功能
1、接口方法

//定义实现个人信息(密码)修改的功能
	public int updatePwd(@Param("uid") Integer id,@Param("userPassword")String pwd);

2、映射文件

 <!-- 实现个人密码修改 -->
	<update id="updatePwd">
	update smbms_user set userPassword=#{userPassword} where id=#{uid}
	
	</update>

测试

@Test
    public void test10() {
    	SqlSession sqlSession=SqlSessionUtil.creatSqlSession();
    	

    	int result = 0;
		try {
			result = sqlSession.getMapper(UserMapper.class).updatePwd(23, "12345886");
			sqlSession.commit();
		} catch (Exception e) {
			sqlSession.rollback();
		}

		
		if(result>0) {
			System.out.println("用户密码修改成功");
		}else {
			System.out.println("用户密码修改失败");
		}
    	SqlSessionUtil.closSqleSession(sqlSession);
    }

范例：实现指定角色的所有用户信息查询（类型嵌套）
1、接口方法

// 实现指定角色的所有用户信息查询
    public List<User> getUserListByRoleId(@Param("rid") Integer id);

2、映射文件

<!-- 实现指定角色的所有用户信息查询 -->
	<select id="getUserListByRoleId" parameterType="Integer" resultType="User" resultMap="userListRole">
	select u.*,r.id as
		r_id,r.roleCode,r.roleName from smbms_user u,smbms_role r
		where
		u.userRole=#{rid} and u.userRole=r.id
	</select>
	<resultMap type="User" id="userListRole">
	<id property="id" column="id"/>
	<result property="userName" column="userName" />
	<result property="userPassword" column="userPassword" />
	<result property="phone" column="phone" />
	<!-- <嵌套类属性>映射引用类型属性 -->
	<association property="role" javaType="Role">
	<id property="id" column="r_id" /> 
	<result property="roleCode" column="roleCode" /> 
	<result property="roleName" column="roleName" />
	</association>
	</resultMap>

优化

<!-- 实现指定角色的所有用户信息查询 -->
	<select id="getUserListByRoleId" parameterType="Integer"
	resultType="User" resultMap="userListRole">
	select u.*,r.id as
	r_id,r.roleCode,r.roleName from smbms_user u,smbms_role r
	where
	u.userRole=#{rid} and u.userRole=r.id
</select>
	<resultMap type="User" id="userListRole">
		<id property="id" column="id" />
		<result property="userName" column="userName" />
		<result property="userPassword" column="userPassword" />
		<result property="phone" column="phone" />
		<!-- <嵌套类属性>映射引用类型属性 -->
		<association property="role" javaType="Role" resultMap="RoleResult"/>
	</resultMap>

	<resultMap type="Role" id="RoleResult">
		<id property="id" column="r_id" />
		<result property="roleCode" column="roleCode" />
		<result property="roleName" column="roleName" />
	</resultMap>

测试

 @Test
    public void test11() {
    	SqlSession sqlSession=SqlSessionUtil.creatSqlSession();
    	

    	List<User> list = null;
		try {
			list = sqlSession.getMapper(UserMapper.class).getUserListByRoleId(3);
			sqlSession.commit();
		} catch (Exception e) {
			sqlSession.rollback();
		}

		//遍历结果集
		for(User u:list){
			System.out.println(u.getUserName()+"\t"+u.getUserPassword()+"\t"+u.getPhone() +u.getRole().getRoleCode()+"\t"+u.getRole().getRoleName());
			
		}
    	SqlSessionUtil.closSqleSession(sqlSession);
    }

范例：实现查询指定用户的多个地址
编写实体类Address
给User类增加集合类型属性 List

声明接口
映射
测试
1、接口方法

//实现查询指定用户的多个地址
    public List<User> getAddressListByUserId(@Param("uid") Integer id);

2、映射文件

<!-- 查询用户多个地址 -->
	<select id="getAddressListByUserId" resultType="User"
		parameterType="Integer" resultMap="userListAddress">
		select u.*,a.id as a_id ,a.contact,a.addressDesc,a.postCode from smbms_user
		u left join smbms_address a on u.id=a.userId where u.id=#{uid}
	</select>
	<resultMap type="User" id="userListAddress">
		<id property="id" column="id" />
		<result property="userCode" column="userCode" />
		<result property="userPassword" column="userPassword" />
		<result property="userName" column="userName" />
		<result property="phone" column="phone" />
		<!-- 一对多 集合属性 -->
		<collection property="addressList" ofType="Address"
			resultMap="addressList" />
	</resultMap>
	<resultMap type="Address" id="addressList">
		<id property="id" column="a_id" />
		<result property="contact" column="contact" />
		<result property="addressDesc" column="addressDesc" />
	</resultMap>

测试

 @Test
    public void test12() {
    	SqlSession sqlSession=SqlSessionUtil.creatSqlSession();
    	

    	List<User> list = null;
		try {
			list = sqlSession.getMapper(UserMapper.class).getAddressListByUserId(1);
			sqlSession.commit();
		} catch (Exception e) {
			sqlSession.rollback();
		}

		//遍历结果集
				for(User u:list){
					System.out.print(u.getUserName()+"\t"+u.getUserPassword()+"\t"+u.getPhone()+"\t，性别：" +u.getGender());
					System.out.println();
					for(Address a :u.getAddressList()){
						System.out.println(a.getContact()+"\t"+a.getAddressDesc()+"\t"+a.getPostCode());
					}
					
				}
    	SqlSessionUtil.closSqleSession(sqlSession);
    }