MyBatis可根据查询的结果类型、查询条件的不同进行统一处理。

1 查询数据

1.1 根据查询数据条数来分析不同的情况

1.1.1 查询单条数据

可以通过实体类、list集合、map等处理查询结果。

• 通过实体类查询单条数据

   User queryUserById(@Param("id") Integer id);

   <select id="queryUserById" resultType="User">select * from t_user where id = #{id}</select>

• 通过map接收

 Map<String, Object> queryUserMapById(@Param("id") Integer id);

 <select id="queryUserMapById" resultType="map">select * from t_user where id = #{id}</select>

1.1.2 查询多条数据

• 通过类型为Map的list集合接收

    List<Map<String, Object>> queryUserMapList();

    <select id="queryUserMapList" resultType="map">select * from t_user</select>

• 通过类型为实体的list集合接收

    List<User> selectUserList();

    <select id="selectUserList" resultType="User">select * from t_user</select>

• 通过map接收

一条数据对应一个map，此时需要通过@MapKey注解设置map集合的键，值是每条数据对应的map集合

注意：此时一定不能使用实体类对象接收，会抛出TooManyResultsException

    @MapKey("id")Map<String, Object> getAllUserToMap();

    <select id="getAllUserToMap" resultType="map">select * from t_user</select>

1.2 其他情形

1.2.1 模糊查询

    List<User> queryUserByLike(@Param("username") String username);

    <select id="queryUserByLike" resultType="User"><!-- select * from t_user where username like '%${username}%' --><!-- select * from t_user where username like concat('%', #{username}, '%') -->select * from t_user where username like "%"#{username}"%"</select>

1.2.2 动态表名

    List<User> queryAllUser(@Param("tableName") String tableName);

    <select id="queryAllUser" resultType="User">select * from ${tableName}</select>

2 新增数据

2.1 通过实体类新增

    @Testpublic void testInsertUser(){SqlSession sqlSession = SqlSessionUtil.getSqlSession();UserMapper userMapper = sqlSession.getMapper(UserMapper.class);User user = new User();user.setUsername("中华");user.setPassword("123456");user.setAge(32);user.setSex("男");int i = userMapper.insertUser(user);System.out.println(i);}

    int insertUser(User user);

    <insert id="insertUser">insert into t_user values(null, #{username}, #{password}, #{age}, #{sex})</insert>

此处，表的主键设计为自增。

也可以明确设置主键为自增：

    <!--useGeneratedKeys:设置使用自增主键keyProperty:自增主键放在传所参数对象的字段属性名，会将生成的自增主键设置到入参的字段中keyColumn:自增主键对应表的字段名--><insert id="insertUser" useGeneratedKeys="true" keyProperty="id" keyColumn="id" parameterType="User">insert into t_user values(null, #{username}, #{password}, #{age}, #{sex})</insert>

2.2 通过map新增

    @Testpublic void testQueryByMapParam(){SqlSession sqlSession = SqlSessionUtil.getSqlSession();UserMapper userMapper = sqlSession.getMapper(UserMapper.class);Map<String, Object> map = new HashMap<>();map.put("username", "李斯");map.put("age", 23);map.put("password", "000000");map.put("sex", "女");List<User> admin = userMapper.insertUser(map);admin.stream().forEach(System.out::println);}

    int insertUser(Map<String, Object> map);

    <insert id="insertUser" useGeneratedKeys="true" keyProperty="id" keyColumn="id" parameterType="map">insert into t_user values(null, #{username}, #{password}, #{age}, #{sex})</insert>

3 修改数据

    int udpateUserById(User user);

    <update id="udpateUserById" parameterType="User">update t_user set username = #{username}, password = #{password} where id = #{id}</update>

4 删除数据

4.1 单个删除

    int deleteUserById(@Param("id") String id);

    <delete id="deleteUserById">delete from t_user where id = #{id}</delete>

4.2 批量删除

    int deleteUserByIds(@Param("ids") String ids);

    <delete id="deleteUserByIds">delete from t_user where id in (${ids})</delete>

    int result = deleteMapper.deleteUserByIds("5,6,7");