3.mybatis下的查询

1.查询数据的条数 返回值int值
UserDao.java

//查询记录条数方法
	public int selectCount();

UserMapper.xml

<!-- 查询时,必须设置返回值类型 -->
	 <select id="selectCount" resultType="int">
	 	select count(id) from smbms_user
	 </select>

UserService.java

//查询记录条数
	public int selectCount() {
		SqlSession session = MyBatisUtil.getSqlSession();
		int ret = 0;
		ret = session.getMapper(UserDao.class).selectCount();
		MyBatisUtil.closeSqlSession(session);
		return ret;
	}

---

2.查询所有用户对象 返回值是List集合
UserDao.java

//查询全部
	public List<User> selectAll();

UserMapper.xml

<select id="selectAll" resultType="User">
	 	select id,userCode,userName,userPassword,gender,phone,address,userRole from smbms_user
	 </select>

UserService.java

	//查询全部
	public List<User> selectAll(){
		SqlSession session = MyBatisUtil.getSqlSession();
		List<User> lists = session.getMapper(UserDao.class).selectAll();
		MyBatisUtil.closeSqlSession(session);
		return lists;
	}

---

3.按条件模糊查询(单表)
如果方法的参数是多个:

• 把多个参数作为实体对象来传递
• 把方法的参数放在Map集合中,在SQL中#{}获取的是Map集合中的key
• 使用注解@Param 在接口中声明多个参数，
  在映射文件中，无需设置属性parameterType(不常用)

UserDao.java

//按条件模糊查询,一个参数
	public List<User> getUserBy(String name);
	
	//按条件模糊查询2,多个参数,放在一个对象中
	public List<User> getUserBy2(User user);
	
	//按条件模糊查询3,多个参数,放在一个Map集合中
	public List<User> getUserBy3(Map<String, String> para);
	
	//方法多个参数  注解查询(不常用)
	public List<User> getUserBy4(@Param("uname")String name,@Param("role")int userRole);

UserMapper.xml

 	 <select id="getUserBy" resultType="User" parameterType="String">
	 	select id,userCode,userName,userPassword,gender,phone,address,userRole
	 	from smbms_user
	 	where userName like CONCAT('%',#{name},'%')
	 </select>
	 <select id="getUserBy2" resultType="User" parameterType="User">
	 	select id,userCode,userName,userPassword,gender,phone,address,userRole
	 	from smbms_user
	 	where userName like CONCAT('%',#{userName},'%') and userRole=#{userRole}
	 </select>
	 <select id="getUserBy3" resultType="User" parameterType="Map">
	 	select id,userCode,userName,userPassword,gender,phone,address,userRole
	 	from smbms_user
	 	where userName like CONCAT('%',#{name},'%') and userRole=#{role}
	 </select>
	 <!-- 不常用-->
	 <select id="getUserBy4" resultType="User">
		select id,userCode,userName,userPassword,gender,phone,address,userRole
	 	from smbms_user
	 	where userName like CONCAT('%',#{uname},'%') and userRole=#{role}
	 </select>

UserService.java

//按条件模糊查询,一个参数
	public List<User> getUserBy(String name){
		SqlSession session = MyBatisUtil.getSqlSession();
		List<User> lists = session.getMapper(UserDao.class).getUserBy(name);
		MyBatisUtil.closeSqlSession(session);
		return lists;
	}
	
	//按条件模糊查询2,一个对象
	public List<User> getUserBy2(User user){
		SqlSession session = MyBatisUtil.getSqlSession();
		List<User> lists = session.getMapper(UserDao.class).getUserBy2(user);
		MyBatisUtil.closeSqlSession(session);
		return lists;
	}
	
	//按条件模糊查询3,多个参数放在Map集合中
	public List<User> getUserBy3(Map<String, String> para){
		SqlSession session = MyBatisUtil.getSqlSession();
		List<User> lists = session.getMapper(UserDao.class).getUserBy3(para);
		MyBatisUtil.closeSqlSession(session);
		return lists;
	}
	
	//方法多个参数  查询(不常用)
	public List<User> getUserBy4(String uname,int role){
		SqlSession session = MyBatisUtil.getSqlSession();
		List<User> lists = session.getMapper(UserDao.class).getUserBy4(uname, role);
		MyBatisUtil.closeSqlSession(session);
		return lists;
	}

4.参数是一个数组和参数是一个集合

需要使用迭代标签foreach遍历

UserDao.java

	//参数是数组类型
	public List<User> getAllUsersByAry(int[] aryRole);
	
	//参数是集合类型
	public List<User> getAllUsersByList(List<Integer> roleList);

UserMapper.xml

	 <!-- 参数是数组类型 -->
	 <select id="getAllUsersByAry" resultType="User">
	 	select id,userCode,userName,userPassword,userRole
	 	from smbms_user
	 	where userRole in
	 	<!-- 需要使用迭代标签遍历数组
	 		 array:关键字,参数是数组,即遍历的是数组
	 	 -->
	 	 <foreach collection="array" item="aryRole"
	 	 open="(" close=")" separator=",">
	 	 #{aryRole}
	 	 </foreach>
	 </select>
	 
	 <!-- 参数是集合类型 -->
	 <select id="getAllUsersByList" resultType="User">
	 	select id,userCode,userName,userPassword,userRole
	 	from smbms_user
	 	where userRole in
	 	<!-- 需要使用迭代标签遍历集合
	 		 list:关键字,参数是集合,即遍历的是集合
	 	 -->
	 	 <foreach collection="list" item="roleList"
	 	 open="(" close=")" separator=",">
	 	 #{roleList}
	 	 </foreach>
	 </select>

---

6.实体类的属性和表中的列 名字不一致(重点!)

• 1)、给查询的列命名别名 别名和属性名一致
• 2)、自定义resultMap 映射 属性和列 (推荐) ---------多表联查(一对一和一对多)

例1:(不常用)
pojo

UserService.java

	//参数是数组类型
	public List<User> getAllUsersByAry(int[] aryRole){
		SqlSession session = MyBatisUtil.getSqlSession();
		List<User> lists = session.getMapper(UserDao.class).getAllUsersByAry(aryRole);
		MyBatisUtil.closeSqlSession(session);
		return lists;
	}

	//参数是集合类型
	public List<User> getAllUsersByList(List<Integer> roleList){
		SqlSession session = MyBatisUtil.getSqlSession();
		List<User> l = session.getMapper(UserDao.class).getAllUsersByList(roleList);
		MyBatisUtil.closeSqlSession(session);
		return l;
	}

UserDao.java

	//实体类属性名和列名不一致时,查询
	public List<User> getAllUsers2();

UserMapper.xml

	 <!-- 不常用 -->
	 <resultMap type="User" id="userMap">
	 	<!-- 列名,属性名的对应关系 -->
	 	<!-- 主键通过id标签映射 -->
	 	<id property="id" column="uid"></id>
	 	<result property="userCode" column="userCode"></result>
	 	<result property="userName" column="userName"></result>
	 	<result property="userRoleName" column="roleName"></result>
	 </resultMap>
	 <select id="getAllUsers2" resultMap="userMap">
	 	select u.id as uid,userCode,userName,userPassword,gender,phone,address,userRole,r.roleName
	 	from smbms_user u
	 	inner join smbms_role r on u.userRole=r.id
	 </select>

UserService.java

	//实体类属性名和列名不一致时,查询
	public List<User> getAllUsers2(){
		SqlSession session = MyBatisUtil.getSqlSession();
		List<User> lists = session.getMapper(UserDao.class).getAllUsers2();
		MyBatisUtil.closeSqlSession(session);
		return lists;
	}