Mybatis之Mybatis的各种查询功能和特殊SQL的执行

/*** 根据用户id查询用户信息* @param id* @return*/UsergetUserById(@Param("id") intid);

<!--UsergetUserById(@Param("id") intid);--><selectid="getUserById"resultType="User">select*fromt_userwhereid=#{id}
</select>

@TestpublicvoidtestGetUserById() {
SqlSessionsqlSession=SqlSessionUtils.getSqlSession();
UserMapperuserMapper=sqlSession.getMapper(UserMapper.class);
Useruser=userMapper.getUserById(1);
System.out.println(user);
    }

/*** 查询所有用户信息* @return*/List<User>getUserList();

<!--List<User>getUserList();--><selectid="getUserList"resultType="User">select*fromt_user</select>

@TestpublicvoidtestGetUserByList() {
SqlSessionsqlSession=SqlSessionUtils.getSqlSession();
UserMapperuserMapper=sqlSession.getMapper(UserMapper.class);
List<User>list=userMapper.getUserList();
System.out.println(list);
    }

/**  * 查询用户的总记录数  * @return  * 在MyBatis中，对于Java中常用的类型都设置了类型别名  * 例如：java.lang.Integer-->int|integer  * 例如：int-->_int|_integer  * 例如：Map-->map,List-->list  */intgetCount();

<!--intgetCount();--><selectid="getCount"resultType="_integer">selectcount(id) fromt_user</select>

@TestpublicvoidtestGetUserByCount() {
SqlSessionsqlSession=SqlSessionUtils.getSqlSession();
UserMapperuserMapper=sqlSession.getMapper(UserMapper.class);
intl=userMapper.getCount();
System.out.println(l);
    }

/**  * 根据用户id查询用户信息为map集合  * @param id  * @return  */Map<String, Object>getUserToMap(@Param("id") intid);

<!--Map<String, Object>getUserToMap(@Param("id") intid);--><selectid="getUserToMap"resultType="map">select*fromt_userwhereid=#{id}
</select><!--结果：{password=123456, sex=男, id=1, age=23, username=admin}-->

@TestpublicvoidtestGetUserByToMap() {
SqlSessionsqlSession=SqlSessionUtils.getSqlSession();
UserMapperuserMapper=sqlSession.getMapper(UserMapper.class);
Mapmap=userMapper.getUserToMap(1);
System.out.println(map);
    }

/**  * 查询所有用户信息为map集合  * @return  * 将表中的数据以map集合的方式查询，一条数据对应一个map；若有多条数据，就会产生多个map集合，此时可以将这些map放在一个list集合中获取  */List<Map<String, Object>>getAllUserToMap();

<!--Map<String, Object>getAllUserToMap();--><selectid="getAllUserToMap"resultType="map">select*fromt_user</select>

@TestpublicvoidtestGetUserByAllToMap() {
SqlSessionsqlSession=SqlSessionUtils.getSqlSession();
UserMapperuserMapper=sqlSession.getMapper(UserMapper.class);
List<Map<String, Object>>list=userMapper.getAllUserToMap();
System.out.println(list);
    }

/*** 查询所有用户信息为map集合* @return* 将表中的数据以map集合的方式查询，一条数据对应一个map；若有多条数据，就会产生多个map集合，并且最终要以一个map的方式返回数据，此时需要通过@MapKey注解设置map集合的键，值是每条数据所对应的map集合*/@MapKey("id")
Map<String, Object>getAllUserToMap2();

<!--Map<String, Object>getAllUserToMap();--><selectid="getAllUserToMap2"resultType="map">select*fromt_user</select>

@TestpublicvoidtestGetUserByAllToMap2() {
SqlSessionsqlSession=SqlSessionUtils.getSqlSession();
UserMapperuserMapper=sqlSession.getMapper(UserMapper.class);
Map<String, Object>map=userMapper.getAllUserToMap2();
System.out.println(map);
    }

/*** 根据用户名进行模糊查询* @param username * @return java.util.List<com.atguigu.mybatis.pojo.User>*/List<User>getUserByLike(@Param("username") Stringusername);

<!--List<User>getUserByLike(@Param("username") Stringusername);--><selectid="getUserByLike"resultType="User"><!--select*fromt_userwhereusernamelike'%${mohu}%'--><!--select*fromt_userwhereusernamelikeconcat('%',#{mohu},'%')-->select*fromt_userwhereusernamelike"%"#{mohu}"%"</select>

/*** 根据id批量删除* @param ids * @return int*/intdeleteMore(@Param("ids") Stringids);

<deleteid="deleteMore">deletefromt_userwhereidin (${ids})
</delete>

//测试类@TestpublicvoiddeleteMore() {
SqlSessionsqlSession=SqlSessionUtils.getSqlSession();
SQLMappermapper=sqlSession.getMapper(SQLMapper.class);
intresult=mapper.deleteMore("1,2,3,8");
System.out.println(result);
}

/*** 查询指定表中的数据* @param tableName * @return java.util.List<com.atguigu.mybatis.pojo.User>*/List<User>getUserByTable(@Param("tableName") StringtableName);

<!--List<User>getUserByTable(@Param("tableName") StringtableName);--><selectid="getUserByTable"resultType="User">select*from${tableName}
</select>

/*** 添加用户信息* @param user */voidinsertUser(Useruser);

<!--voidinsertUser(Useruser);--><insertid="insertUser"useGeneratedKeys="true"keyProperty="id">insertintot_uservalues (null,#{username},#{password},#{age},#{sex},#{email})
</insert>

//测试类@TestpublicvoidinsertUser() {
SqlSessionsqlSession=SqlSessionUtils.getSqlSession();
SQLMappermapper=sqlSession.getMapper(SQLMapper.class);
Useruser=newUser(null, "ton", "123", 23, "男", "123@321.com");
mapper.insertUser(user);
System.out.println(user);
//输出：User(id=3, username=ton, password=123, age=23, sex=男, email=123@321.com)，自增主键存放到了user的id属性中}