Mybatis实现CRUD

╰+攻爆jí腚メ 2023-01-01 13:58 131阅读 0赞

### 查 ###

1、不传参数，查询数据库中的所有记录

在接口中定义方法

List<Employee> selectAll();

mapper文件

<select id="selectAll" resultType="school.xauat.bean.Employee">
        select id,last_name,gender,email from tbl_employee
    </select>

主方法

@Test
     public void selectAll(){
         SqlSession sqlSession = MybatisUtil.getSqlSession();
         //使用的是jdk的动态代理
         EmpDao empDao = sqlSession.getMapper(EmpDao.class);
         System.out.println(empDao);
         List<Employee> empList = empDao.selectAll();
         for(Employee e : empList){
             System.out.println(e);
         }
         sqlSession.close();
     }

2、带一个参数的查找

在接口中定义方法

Employee selectById(int id);

mapper文件

<select id="selectById" resultType="school.xauat.bean.Employee" parameterType="java.lang.Integer">
        select id,last_name,gender,email from tbl_employee where id = #{id}
    </select>

主方法

@Test
    public void selectById(){
        SqlSession sqlSession = MybatisUtil.getSqlSession()
        EmpDao dao = sqlSession.getMapper(EmpDao.class);
        Employee employee = dao.selectById(1);
        System.out.println(employee);
        sqlSession.close();
    }

3、查询参数为多个参数时

查询参数为多个参数的时候在形参前面加上@Param("自定义参数名称")在mapper文件中使用\#\{参数名\}

在接口中定义方法

List<Employee> selectByParams(@Param("id") int id, @Param("last_name") String last_name);

mapper文件

<select id="selectByParams" resultType="school.xauat.bean.Employee">
        select id,last_name,gender,email from tbl_employee where id = #{id} or last_name = #{last_name}
    </select>

主方法

@Test
    public void selectByParams(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpDao dao = sqlSession.getMapper(EmpDao.class);
        List<Employee> empList = dao.selectByParams(1,"pig");
        for(Employee e : empList){
            System.out.println(e);
        }
        sqlSession.close();
    }

4、使用java对象作为传递参数时

使用java对象作为接口中的方法参数在mapper文件中使用\#\{属性名,javaType=类型名称,jdbcType=数据类型\}，javaType 在java对象中的属性名，jdbcType 在数据库中的属性名。简化方式\#\{属性名\}，其余通过反射可以获取

java对象

public class QueryParams {
        private int id;
        private String last_name;
        public QueryParams(int id, String last_name) {
            this.id = id;
            this.last_name = last_name;
        }
    }

在接口中定义方法

List<Employee> selectByQueryParams(QueryParams queryParams);

mapper文件

select id="selectByQueryParams" parameterType="queryParams" resultType="school.xauat.bean.Employee">
            select id,last_name,gender,email from tbl_employee where id = #{id} or last_name = #{last_name}
        </select>

主方法

@Test
    public void selectByQueryParams(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpDao dao = sqlSession.getMapper(EmpDao.class);
        QueryParams queryParams = new QueryParams(1,"Amy");
        List<Employee> employeeList = dao.selectByQueryParams(queryParams);
        for(Employee e : employeeList){
            System.out.println(e);
        }
        sqlSession.close();
    }

5、多个参数，简单类型，按位传参

在mapper文件中使用\#\{arg0\}、\#\{arg1\}

在接口中定义方法

List<Employee> selectByPositions(int id,String last_name);

mapper文件

<select id="selectByPositions" resultType="school.xauat.bean.Employee">
        select id,last_name,gender,email from tbl_employee where id = ${arg0} or last_name = #{arg1}
    </select>

主方法

@Test
    public void selectByPositions(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpDao dao = sqlSession.getMapper(EmpDao.class);
        List<Employee> empList = dao.selectByPositions(12,"pig");
        for(Employee e : empList){
            System.out.println(e);
        }
        sqlSession.close();
    }

6、多个参数使用map传参

在mapper文件中使用\#\{key\}

在接口中定义方法

List<Employee> selectByMap(Map<String,Object> map);

mapper文件

<select id="selectByMap" resultType="school.xauat.bean.Employee">
        select id,last_name,gender,email from tbl_employee where id = ${myId} or last_name = #{myName}
    </select>

主方法

@Test
    public void selectByMap(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpDao dao = sqlSession.getMapper(EmpDao.class);
        Map<String,Object> map = new HashMap<String,Object>();
        map.put("myId",12);
        map.put("myName","pig");
        List<Employee> empList = dao.selectByMap(map);
        for(Employee e : empList){
            System.out.println(e);
        }
        sqlSession.close();
    }

7、查询返回Map集合

查询返回map集合

返回值是map的时候只能返回一行记录

在接口中定义方法

Map<Object,Object> selectMapById(Integer id);

mapper文件

<select id="selectMapById" resultType="java.util.Map">
        select * from tbl_employee where id = #{id}
    </select>

主方法

@Test
    public void selectMapById(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpDao dao = sqlSession.getMapper(EmpDao.class);
        Map<Object,Object> map = dao.selectMapById(1);
        System.out.println("查询结果" + map);
        sqlSession.close();
    }

8、查询结果用ResultMap进行封装

在接口中定义方法

List<Employee> selectResultMap();

mapper文件

<select id="selectResultMap" resultMap="EmpMap">
        select id,last_name,gender,email from tbl_employee
    </select>

主方法

@Test
    public void selectResultMap(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpDao dao = sqlSession.getMapper(EmpDao.class);
        List<Employee> empList = dao.selectResultMap();
        for(Employee e : empList){
            System.out.println(e);
        }
        sqlSession.close();
    }

9、模糊查询

第一种模糊查询，在java代码中指定like的内容

第二种在mapper中拼接like "%"李"%"

在接口中定义方法

List<Employee> selectLikeOne(String name);

mapper文件

<select id="selectLikeOne" parameterType="java.lang.String" resultType="school.xauat.bean.Employee">
        
        select id,last_name,gender,email from tbl_employee where last_name like "%" #{name} "%"
    </select>

主方法

@Test
    public void selectLikeOne(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpDao empDao = sqlSession.getMapper(EmpDao.class);
        /*List<Employee> employeeList = empDao.selectLikeOne("m");*/
        List<Employee> employeeList = empDao.selectLikeOne("m");
        for(Employee e : employeeList){
            System.out.println(e);
        }
        sqlSession.close();
    }

### 增 ###

在接口中定义方法

int addEmp(Employee employee);

mapper文件

<insert id="addEmp" parameterType="school.xauat.bean.Employee">
        insert into tbl_employee (last_name,gender,email) values (#{last_name},#{gender},#{email}
    </insert>

### 删 ###

在接口中定义方法

int delete(int id);

mapper文件

<delete id="delete">
        delete from tbl_employee where id = #{id}
    </delete>

### 改 ###

在接口中定义方法

int update(Employee employee);

mapper文件

<update id="update">
        update tbl_employee set gender = #{gender},email = #{email} where last_name = #{last_name}
    </update>

主方法

@Test
    public void addEmp(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpDao empDao = sqlSession.getMapper(EmpDao.class);
        Employee employee = new Employee("apple","1","apple@qq.com");
        System.out.println(empDao.addEmp(employee));
        sqlSession.close();
    }
    @Test
    public void delete(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpDao empDao = sqlSession.getMapper(EmpDao.class);
        System.out.println(empDao.delete(15));
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void update(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpDao empDao = sqlSession.getMapper(EmpDao.class);
        Employee employee = new Employee("tom","1","tom@google.com");
        System.out.println(empDao.update(employee));
        sqlSession.close();
    }

--------------------