Mybatis实现CRUD

2023-11-21 15:07:10

查

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文件

<resultMap id="EmpMap" type="school.xauat.bean.Employee">
    <!--列名和java属性的关系-->
    <!--
        主键列，使用id标签
        column:列名
        property:java类型的属性名
    -->
    <id column="id" property="id"></id>
    <!--非主键列-->
    <result column="last_name" property="last_name"></result>
    <result column="gender" property="gender"></result>
    <result column="email" property="email"></result>
</resultMap>

<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 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();
}

码农公寓

查

增

删

改

相关文章