若SQL语句查询的结果是多条时,不能用实体类类型作为方法的返回值,否则会抛出异常; 若SQL语句查询的结果只有一条,此时可以使用实体类类型或集合类型作为方法的返回值。
1. 查询一个实体类对象
SelectMapper.java接口:
/**
* 根据id查询用户信息
* @param id
* @return
*/
User getUserById(@Param("id") Integer id);
SelectMapper.xml映射文件:
<!--getUserById-->
<select id="getUserById" resultType="User">
select * from t_user where id = #{id};
</select>
测试代码:
@Test
public void testGetUserById(){
// SqlSessionUtil是提前创建的一个工具类,文末给出SqlSessionUtil完整代码
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
User user = mapper.getUserById(1);
System.out.println(user);
sqlSession.close();
}
2. 查询一个List集合
SelectMapper.java接口:
/**
* 查询所有用户信息
* @return
*/
List<User> getAllUsers();
SelectMapper.xml映射文件:
<!--getAllUsers-->
<select id="getAllUsers" resultType="User">
select * from t_user;
</select>
测试代码:
@Test
public void testGetAllUsers(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
List<User> list = mapper.getAllUsers();
list.forEach(System.out::println); // 遍历输出List
sqlSession.close();
}
3. 查询单个数据
SelectMapper.java接口:
<!--getCount-->
<!--
MyBatis中为Java中常用的类型设置了类型别名:
Integer:Integer、int
int:_int、_Integer
Map:map
String:string
-->
<select id="getCount" resultType="integer">
select count(*) from t_user;
</select>
SelectMapper.xml映射文件:
<!--getUserByIdToMap-->
<select id="getUserByIdToMap" resultType="map">
select * from t_user where id = #{id};
</select>
测试代码:
@Test
public void testGetCount(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
Integer count = mapper.getCount();
System.out.println(count);
sqlSession.close();
}
4. 查询一条数据为Map集合
SelectMapper.java接口:
/**
* 根据Id查询用户信息为一个Map集合
* @param id
* @return
*/
Map<String, Object> getUserByIdToMap(@Param("id") Integer id);
SelectMapper.xml映射文件:
<!--getUserByIdToMap-->
<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);
// {password=123, gender=女, id=1, age=23, email=2436@qq.com, username=张三}
Map<String, Object> map = mapper.getUserByIdToMap(1);
System.out.println(map);
sqlSession.close();
}
5. 查询多条数据为Map集合
若查询的数据有多条时,要将每条数据转换为Map集合,此时有两种解决方案
(1)将Mapper接口的返回值设置为范型为map的List集合
(2)可以将每条数据转换的map集合放在一个大的map中,通过@MapKey注解实现,例如:@MapKey("id"),将id作为大map的键
方法1:
SelectMapper.java接口:
/**
* 查询所有的用户信息为Map集合
* @return
*/
List<Map<String, Object>> getAllUsersToMap();
SelectMapper.xml映射文件:
<!--getAllUsersToMap-->
<select id="getAllUsersToMap" resultType="map">
select * from t_user;
</select>
测试代码:
@Test
public void testGetAllUsersToMap(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
// 结果:{password=123, gender=女, id=1, age=23, email=2436@qq.com, username=张三}
List<Map<String, Object>> list = mapper.getAllUsersToMap();
list.forEach(System.out::println);
sqlSession.close();
方法2:
SelectMapper.java接口:
/**
* 查询所有的用户信息为Map集合
* @return
*/
@MapKey("id")
Map<String, Object> getAllUsersToMap();
SelectMapper.xml映射文件:
<!--getAllUsersToMap-->
<select id="getAllUsersToMap" resultType="map">
select * from t_user;
</select>
测试代码:
@Test
public void testGetAllUsersToMap(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
Map<String, Object> map = mapper.getAllUsersToMap();
System.out.println(map);
sqlSession.close();
}
补充:
SqlSessionUtil工具类
import java.io.IOException;
import java.io.InputStream;
public class SqlSessionUtil {
public static SqlSession getSqlSession() {
SqlSession sqlSession = null;
try {
// 获取核心配置文件的输入流
InputStream is = Resources.getResourceAsStream("application.xml");
// 获取SqlSessionFactoryBuilder
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
// 获取sqlSessionFactoryBuilder对象
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
// 获取SqlSession对象
sqlSession = sqlSessionFactory.openSession(true);
} catch (IOException e) {
e.printStackTrace();
}
return sqlSession;
}
}