Mybatis学习笔记(二)——通过xml文件进行CRUD操作

简单的CRUD操作

UserMapper：

package dao;

import pojo.User;

import java.util.List;

public interface UserMapper {
    public List<User> getUserList();

    public User getUserById(int id);

    public int addUser(User user);

    public int updateUser(User user);

    public int deleteUserById(int id);
}

UserMapper.xml文件：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="dao.UserMapper">
    <!--查询-->
    <select id="getUserList" resultType="pojo.User">
        select * from user
    </select>
    <select id="getUserById" parameterType="int" resultType="pojo.User">
        select * from user where id = #{id}
    </select>

    <!--插入-->
    <insert id="addUser" parameterType="pojo.User">
        insert into user (id,name,password) values (#{id},#{name},#{password}) 
    </insert>

    <!--修改-->
    <update id="updateUser" parameterType="pojo.User">
        update user set name=#{name},password=#{password} where id=#{id}
    </update>

    <!--删除-->
    <delete id="deleteUserById" parameterType="int">
        delete from user where id=#{id}
    </delete>
</mapper>

namespace命名空间必须与Dao/Mapper接口的包名一致
id ：就是namespace对应接口的方法，必须与相应方法名一致
resultType ：是SQL语句执行的返回值类型
parameterType ：是对应方法的参数类型

SQL语句中的参数使用 #{}
设置参数时，User实体中的属性可直接取出，不用使用getter/setter方法
增删改操作不用设置返回值类型

在核心配置文件mybatis-config.xml中配置UserMapper.xml映射：

<mappers>
    <mapper resource="dao/UserMapper.xml"/>
</mappers>

测试：

	@Test
    public void getUserListTest(){
        //第一步：获得SqlSession对象
        SqlSession sqlSession = MybatisUtils.getSqlSession();
      
        UserMapper userDao = sqlSession.getMapper(UserMapper.class);
        List<User> list = userDao.getUserList();
        for (User user:list) {
            System.out.println(user.toString());
        }
        
        // 关闭SqlSession
        sqlSession.close();
    }

	@Test
    public void getUserByIdTest(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.getUserById(1);
        System.out.println(user.toString());

        sqlSession.close();
    }

	 @Test
    public void addUserTest(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int i = mapper.addUser(new User(4, "赵六", "123456"));
        if (i>0)
            System.out.println("插入成功！");
        // 增删改操作必须提交事务
        sqlSession.commit();

        sqlSession.close();
    }

	@Test
    public void updateUserTest(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int i = mapper.updateUser(new User(4, "夏洛", "111111"));
        if (i>0)
            System.out.println("修改成功！");
        // 增删改操作必须提交事务
        sqlSession.commit();

        sqlSession.close();
    }

	@Test
    public void deleteUserByIdTest(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int i = mapper.deleteUserById(4);
        if (i>0)
            System.out.println("删除成功！");
        // 增删改操作必须提交事务
        sqlSession.commit();

        sqlSession.close();
    }

增删改操作必须提交事务 sqlSession.commit()
sqlSession使用完必须关闭 sqlSession.close()

通过Map传递参数

当方法中传递的参数较多时，可以使用Map来传递参数！

Mapper.xml文件：

<!--通过Map传递参数-->
<update id="updateUserByMap" parameterType="map">
    update user set name=#{userName},password=#{userPassword} where id=#{userId}
</update>

测试方法：

@Test
public void updateUserByMap(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    Map<String,Object> map = new HashMap<String, Object>();
    map.put("userName","夏洛");
    map.put("userPassword","111111");
    map.put("userId",1);
    mapper.updateUserByMap(map);
    sqlSession.commit();
    
    sqlSession.close();
}

Map传递参数，可直接在SQL中取出Key
对象传递参数，可直接在SQL中取出对象的属性

只有一个基本参数类型的时候，写SQL语句时可不用写参数类型parameterType

模糊查询

方式一：

List<User> list = mapper.getUserByName("%李%");

select * from user where name like #{name}

方式二：

List<User> list = mapper.getUserByName("李");

select * from user where name like "%"#{name}"%"