Java学习-MyBatis学习(三)

MyBatis中各种查询

查询一个实体类对象

查询一个list集合

查询单个数据

查询一条数据为map集合

package com.lotus.mybatis.mapper;
import com.lotus.mybatis.pojo.User;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface SelectMapper {
    /**
     * 根据id查询用户信息
     */
    User getUserById(@Param("id") Integer id);
    /**
     * 查询所有数据
     */
    List<User> getAllUsers();
    /**
     * 查询用户信息的总记录数
     */
    Integer getCount();
    /**
     * 根据id查询用户信息为一个map集合
     */
    Map<String,Object> getUserByIdToMap(@Param("id") Integer id);
    /**
     * 查询所有用户信息为map集合
     * 将唯一字段做为键
     */
    @MapKey("id")
//    List<Map<String,Object>> getAllUsersToMap();
    Map<String,Object> getAllUsersToMap();
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lotus.mybatis.mapper.SelectMapper">
    <!--User getUserById(@Param("id") Integer id);-->
    <select id="getUserById" resultType="User">
        select * from t_user where id = #{id}
    </select>
    <!--List<User> getAllUsers();-->
    <select id="getAllUsers" resultType="User">
        select * from t_user
    </select>
    <!--Integer getCount();-->
    <select id="getCount" resultType="java.lang.Integer">
        select count(*) from t_user;
    </select>
    <!--Map<String,Object> getUserByIdToMap(Integer id);-->
    <select id="getUserByIdToMap" resultType="map">
        select * from t_user where id=#{id}
    </select>
    <!--Map<String,Object> getAllUsersToMap();-->
    <select id="getAllUsersToMap" resultType="map">
        select * from t_user
    </select>
</mapper>

MyBatis设置了默认的类别名称

package com.lotus.mybatis.mapper;

import com.lotus.mybatis.pojo.User;
import com.lotus.mybatis.utils.SqlSessionUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;
import java.util.Map;
public class SelectMapperTest {
    /**
     * 若查询出的数据只有一条，
     * ①则通过实体类对象
     * ②集合接收
     * ③map接收---字段为键，值为值
     * 若查询出的数据有多条，
     * ①可以通过集合接收，不能通过实体类接收，否则会抛出异常TooManyResultsException
     * ②通过map集合接收List<map<String,Object>>
     * ③可以在mapper接口的方法上添加@MapKey注解，此时就可以将每条数据转换的map集合帮为值，以某个字段作为键放在同一个map集合中，键需唯一
     */
    @Test
    public void testGetUserById() {
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        User user = mapper.getUserById(4);
        System.out.println(user);
    }
    @Test
    public void testGetAllUsers() {
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        List<User> allUsers = mapper.getAllUsers();
        System.out.println(allUsers);
    }
    @Test
    public void testGetCount() {
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        Integer count = mapper.getCount();
        System.out.println(count);
    }
    @Test
    public void testGetUserByIdToMap() {
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        Map<String, Object> map = mapper.getUserByIdToMap(4);
        System.out.println(map.keySet());
    }
    @Test
    public void testGetUsersToMap() {
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        //List<Map<String, Object>> map = mapper.getAllUsersToMap();
        Map<String, Object> map = mapper.getAllUsersToMap();
        System.out.println(map);
    }
}

模糊查询

批量删除

动态设置表名

public interface SQLMapper {
    /**
     * 根据用户名模糊查询用户信息
     */
    List<User> getUserByLike(@Param("username") String username);
    /**
     * 批量删除
     */
    void deleteMore(@Param("ids") String ids);
    /**
     * 查询指定表中数据
     */
    List<User> getUserByTableName(@Param("tableName") String tableName);
    /**
     * 添加用户信息
     */
    void insertUser(User user);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lotus.mybatis.mapper.SQLMapper">
<!--List<User> getUserByLike(@Param("username") String username);-->
    <select id="getUserByLike" 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>
    <!--int deleteMore(@Param("ids") String ids);-->
    <delete id="deleteMore">
        <!-- 此处必须使用${} -->
        delete from t_user where id in (${ids})
    </delete>
    <!--List<User> getUserByTableName(@Param("tableName") String tableName);-->
    <select id="getUserByTableName" resultType="User">
        <!-- 此处必须使用${} -->
        select * from ${tableName}
    </select>
    <!--void insertUser(User user);-->
    <!--
         useGeneratedKeys：设置当前标签中的SQL使用了自增的主键
         keyProperty：将自增的主键的值赋值给传输到映射文件中参数的某个属性
    -->
    <insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
        insert into t_user values(null,#{username},#{password},#{age},#{sex},#{email})
    </insert>
</mapper>

public class SQLMapperTest {
    @Test
    public void getUserByLike(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
        List<User> list = mapper.getUserByLike("d");
        System.out.println(list);
    }
    @Test
    public void testDeleteMore(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
        mapper.deleteMore("1,2,3,4,5");
        //System.out.println(res);
    }
    @Test
    public void testGetUserByTableName(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
        List<User> users = mapper.getUserByTableName("t_user");
        System.out.println(users);
    }
    @Test
    public void testInsertUser(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
        User user = new User(null, "edison", "123", 15, "男", "edison@qq.com");
        System.out.println(user.getId());
        mapper.insertUser(user);
        System.out.println(user.getId());
    }
}