6、MyBatis的各种查询功能
6.1、查询一个实体类对象
SelectMapper接口:
//根据用户的id查询用户信息
User getUserById(@Param("id") Integer Id);
SelectMapper.xml
<!--User getUserById(@Param("id") Integer Id);-->
<select id="getUserById" resultType="user">
select * from t_user where id = #{id}
</select>
测试:
@Test
public void testGetUserById(){
SqlSession sqlSession = sqlSessionUtil.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
User userById = mapper.getUserById(1);
System.out.println(userById);
}
6.2、查询一个list集合
SelectMapper接口
//查询所有用户信息
List<User> getAllUser();
SelectMapper.xml
<!--List<User> getAllUser();-->
<select id="getAllUser" resultType="user">
select * from t_user
</select>
测试
@Test
public void testGetAllUser(){
SqlSession sqlSession = sqlSessionUtil.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
// for (User user : mapper.getAllUser()) {
// System.out.println(user);
// }
List<User> allUser = mapper.getAllUser();
allUser.forEach(System.out::println);
}
小结:
当查询的数据为多条时,不能使用实体类作为返回值,否则会抛出异常TooManyResultsException;但是若查询的数据只有一条,可以使用实体类或集合作为返回值。
6.3、查询单个数据
SelectMapper接口
//查询用户的总数量
Integer getCount();
SelectMapper.xml
<!--Integer getCount();-->
<!--
mybatis中为java中常用类型设置了类型别名
Integer:Integer,int
int:_int,_integer
Map:map
String :string
resultType="java.lang.Integer"
resultType="int" | "INT"
resultType="integer"
-->
<select id="getCount" resultType="int">
select count(*) from t_user
</select>
测试
@Test
public void testGetCount(){
SqlSession sqlSession = sqlSessionUtil.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
Integer count = mapper.getCount();
System.out.println(count);
}
6.4、查询一条数据为map集合
SelectMapper接口
//查询用户(map)
Map<String,Object> getUserByIdToMap(@Param("id") Integer id);
SelectMapper.xml
<!--Map<String,Object> getUserByIdToMap(@Param("id") Integer id);-->
<select id="getUserByIdToMap" resultType="map">
select * from t_user where id = #{id}
</select>
测试
@Test
public void testGetUserByIdToMap(){
SqlSession sqlSession = sqlSessionUtil.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
Map<String, Object> userByIdToMap = mapper.getUserByIdToMap(1);
//数据为null,将不会放在map集合中
System.out.println(userByIdToMap);
}
6.5、查询多条数据为map集合
/*
* 查询所有的用户信息为map集合
* 如果查询到的数据有多条的时候,并且要将每条数据转换为map集合
* 此时有两种方案:
* 1.将mapper接口方法的返回值设置为泛型是map的list集合
* List<Map<String,Object>> getAllUserToMap();
* 2.可以将每条数据转换的map集合放在一个大map集合,但是必须通过@MapKey注解
* 将查询到的某个字段的值作为大的map的键
* @MapKey("id")
Map<String,Object> getAllUserToMap();
* */
SelectMapper接口
//查询所有用户(map)
// List<Map<String,Object>> getAllUserToMap();
@MapKey("id")
Map<String,Object> getAllUserToMap();
SelectMapper.xml
<!--List<Map<String,Object>> getAllUserToMap();-->
<select id="getAllUserToMap" resultType="map">
select * from t_user
</select>
测试
@Test
public void testGetAllUserToMap(){
SqlSession sqlSession = sqlSessionUtil.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
// for (Map<String, Object> stringObjectMap : mapper.getAllUserToMap()) {
// System.out.println(stringObjectMap);
// }
// List<Map<String, Object>> allUserToMap = mapper.getAllUserToMap();
// allUserToMap.forEach(System.out::println);
Map<String, Object> allUserToMap = mapper.getAllUserToMap();
System.out.println(allUserToMap);
//{1={password=123456, gender=男, id=1, age=23, email=123456@qq.com, username=admin},
// 2={password=123, gender=男, id=2, age=23, email=123456@qq.com, username=root},
// 4={password=111111, gender=女, id=4, age=33, email=1234@qq.com, username=root}}
}