day112 - mybatis的查询与特殊sql语句

/**
 * 根据id查询用户信息
 * @param id
 * @return
 */
User getUserById(@Param("id") Integer id);
​
<!--   User getUserById(@Param("id") Integer id);  -->
<select id="getUserById" resultType="com.gu.mybatis.pojo.User">
    select * from t_user where id =#{id}
    </select>
        
@Test
    public void getUserById(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        User userById = mapper.getUserById(1);
        System.out.println(userById);
    }

/**
 * 1. 若查询语句为多条时，一定不能以实体类类型作为返回值
 * 否则会抛出异常
 * 2. 若查询语句为一条时，可以使用实体类型，也可以使用list集合类型作为方法的返回值
 */
<!--  List<User> getAllUser();  -->
<select id="getAllUser" resultType="com.gu.mybatis.pojo.User">
    select * from t_user
    </select>
@Test
    public void getAllUser(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        List<User> userById = mapper.getAllUser();
        userById.forEach(System.out::println);
    }

<!--  Integer getCount();  -->
<!--
    Mybatis中为Java常用的类型设置了类型别名
    Integer：Integer，int
    int：_int,_integer
    Map:map
    String: string
  -->
<select id="getCount" resultType="java.lang.Integer">
    select count(*) from t_user
    </select>

/**
 * 根据id查询用户信息为一个map集合
 * @param id
 * @return
 */
Map< String, Object>getUserBtIdToMap(@Param("id") Integer id);
​
<!--  Map< String, Object>getUserBtIdToMap(@Param("id") Integer id);  -->
<select id="getUserBtIdToMap" resultType="java.util.Map">
    select * from t_user where id = #{id}
    </select>
        
@Test
    public void getUserByIdToMap(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        Map<String, Object> map = mapper.getUserBtIdToMap(1);
        //{password=123456, gender=男, id=1, age=23, email=222333, username=admin}
        System.out.println(map);
        //若值为null，则不放入map集合中
    }

/**
 * 查询所有的用户集合，通过map集合的方式
 *
 * 若查询的数据有多条时，并将每条数据转化为map集合
 * 1. 将mapper接口方法返回值设置为list
 *    List<Map< String, Object>>getAllUserByMap();
 * 2. 将每条数据的map集合放在一个大的map中，通过@MapKey("字段")作为大的map的键
 *     @MapKey("id")
 *     Map< String, Object>getAllUserByMap();
 * @return
 */
//List<Map< String, Object>>getAllUserByMap();
@MapKey("id")
Map< String, Object>getAllUserByMap();
​
<!--  Map< String, Object>getAllUserByMap();  -->
<select id="getAllUserByMap" resultType="java.util.Map">
    select * from t_user
    </select>
    
  @Test
    public void getAllUserByMap(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        //List<Map<String, Object>> map = mapper.getAllUserByMap();
        Map<String, Object> map = mapper.getAllUserByMap();
        System.out.println(map);
    }   

/**
 * 通过用户名模糊查询用户信息
 * @param name
 * @return
 */
List<User> getUserByLike(@Param("name") String name);
<!--  List<User> getUserByLike(@Param("name") String name);  -->
<select id="getUserByLike" resultType="com.gu.mybatis.pojo.User">
<!--select * from t_user where username like '%${name}%'-->
<!--select * from t_user where username like concat('%',#{name},'%')-->
    select * from t_user where username like "%"#{name}"%"
    </select>

<!--void deleteMoreUser(@Param("ids") String ids);-->
<delete id="deleteMoreUser">
    delete from t_user where id in(${ids})
    </delete>

<!--List< User> getUserList(@Param("tablename") String tablename);-->
<select id="getUserList" resultType="com.gu.mybatis.pojo.User">
    select * from ${tablename}
    </select>

<!--void insertUser(User user);-->
<!--
    useGeneratedKeys:表示当前添加功能使用了自增的主键
    keyProperty：将添加的数据的自增主键为实体类的参数属性赋值，因为sql语句只返回受影响的行数
-->
<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
    insert into t_user values(null,#{username},#{password},#{age},#{gender},#{email})
​
    </insert>

@Test
public void testGetUserByLike(){
    SqlSession sqlSession = SqlSessionUtil.getSqlSession();
    SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
    List<User> like = mapper.getUserByLike("a");
    like.forEach(System.out::println);
}
​
@Test
public void testDeleteUser(){
    SqlSession sqlSession = SqlSessionUtil.getSqlSession();
    SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
    mapper.deleteMoreUser("9,10");
}
​
@Test
public void testGetUserList(){
    SqlSession sqlSession = SqlSessionUtil.getSqlSession();
    SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
    List<User> user = mapper.getUserList("t_user");
    user.forEach(System.out::println);
}
​
@Test
public void testInsertUser(){
    SqlSession sqlSession = SqlSessionUtil.getSqlSession();
    SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
    User user = new User(null,"xiaoming","123456",23,"男","2233@qq.com");
    mapper.insertUser(user);
    System.out.println(user);
}