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

mybatis查询与特殊语句

查询

普通语句

/**
 * 根据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);
    }

2.

<!--  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>

map集合方式

1. 查询一条结果放入map集合中

/**
 * 根据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集合中
    }

2. 查询多条结果放入list集合中，list的类型为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);
    }   

结果：

{1={password=123456, gender=男, id=1, age=23, email=222333, username=admin}, 2={password=123456, gender=男, id=2, age=22, email=22224444, username=gugu}, 3={password=123456, gender=女, id=3, age=33, email=hahahaha, username=root},

4={id=4, username=a},

5={id=5, username=a},

6={id=6, username=a},

7={id=7, username=a},

8={id=8, username=a},

11={id=11, username=a},

12={password=123456, gender=男, id=12, age=23, email=[email protected], username=xiaoming}}

以@MapKey("id")的id为标识符，返回一个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,"男","[email protected]");
    mapper.insertUser(user);
    System.out.println(user);
}

over