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");
)
