mybatis CRUD操作

一、添加数据

IUserDao.java

public interface IUserDao {
    /**
     * 查询全部
     * @return
     */
    List<User> findAll();

    /**
     * 添加用户
     * @param user
     */
    void saveUser(User user);
}

IUserDao.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="com.ect.dao.IUserDao">
    <select id="findAll" resultType="com.ect.domain.User">
        SELECT * FROM user
    </select>
    <insert id="saveUser" parameterType="com.ect.domain.User">
        INSERT INTO user(username,birthday,sex,address) VALUES(#{username},#{birthday},#{sex},#{address});
    </insert>
</mapper>

测试类：

  @Test
    public void testSaveUser() throws IOException {
        //1.设置用户属性
        User user = new User();
        user.setUsername("zhouzhou");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setAddress("上海");


        //2.获取配置文件
        InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
        //3.获取SqlSessionFactory对象
        SqlSessionFactory factory = new  SqlSessionFactoryBuilder().build(is);
        //4.获取sqlSession对象
        SqlSession sqlSession = factory.openSession();
        //5.获取Iuser代理对象
        IUserDao userDao = sqlSession.getMapper(IUserDao.class);
        //6.执行添加用户方法
        userDao.saveUser(user);

        //7.提交事务
        sqlSession.commit();
        //8.释放资源
        sqlSession.close();
        is.close();

    }

二、更新数据

IUserDao.java

    /**
     * 更新用户
     * @param user
     */
    void updateUser(User user);

IUserDao.xml

    <update id="updateUser" parameterType="com.ect.domain.User">
        UPDATE user SET username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} WHERE id=#{id}
    </update>

测试类：

    @Test
    public void testUpdateUser(){
        User user = new User();
        user.setUsername("zhouzhou");
        user.setBirthday(new Date());
        user.setAddress("天津");
        user.setSex("女");
        user.setId(60);

        //执行更新操作
        userDao.updateUser(user);
        //提交事务
        sqlSession.commit();

    }

三、删除数据

IUserDao.java

 /**
     * 删除用户
     * @param uid
     */
    void deleteUser(Integer uid);

IUserDao.xml

    <delete id="deleteUser" parameterType="java.lang.Integer">
        DELETE FROM user WHERE id=#{id}
    </delete>

测试类:

    @Test
    public void testDeleteUser(){
        //执行删除操作
        userDao.deleteUser(60);
        //提交事务
        sqlSession.commit();
    }

四、通过id查数据

IUserDao.java

    /**
     * 通过id查询用户
     */
    User findById(Integer uid);

IUserDao.xml

  <select id="findById" resultType="com.ect.domain.User" parameterType="java.lang.Integer">
        SELECT * FROM user WHERE id=#{id}
    </select>

测试类：

    @Test
    public void testFindById(){
        User user = userDao.findById(59);
        System.out.println(user);
    }

五、模糊查询

IUserDao.java

    /**
     * 模糊查询
     */
    List<User>  findOne(String username);

IUserDao.xml

    <select id="findOne" resultType="com.ect.domain.User" parameterType="java.lang.String">
        SELECT * FROM user WHERE username like #{username}
    </select>

测试类：

    @Test
    public void testFindOne(){
        List<User> users = userDao.findOne("%王%");
        for (User user : users) {
            System.out.println(user);
        }
    }

六、保存数据细节（返回最后插入的id）

IUserDao.xml

    <insert id="saveUser" parameterType="com.ect.domain.User">
        
        <selectKey  keyProperty="id" order="AFTER" resultType="java.lang.Integer">
            SELECT last_insert_id();
        </selectKey>
        INSERT INTO user(username,birthday,sex,address) VALUES(#{username},#{birthday},#{sex},#{address});
    </insert>

七、使用Queryvo作为查询条件

主要用于多对象查询

QueryVo.java

public class QueryVo {
    private User user;

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }
}

IUserDao.java

    /**
     * 使用ognl  QueryVo查询
     */
    List<User> findByVo(QueryVo vo);

IUserDao.xml

   <select id="findByVo" parameterType="com.ect.domain.QueryVo" resultType="com.ect.domain.User">
        SELECT * FROM user WHERE username like #{user.username}
    </select>

测试类：

    @Test
    public void testFindByVo(){
        QueryVo vo = new QueryVo();
        User user = new User();
        user.setUsername("%王%");
        vo.setUser(user);
        List<User> byVo = userDao.findByVo(vo);
        for (User user1 : byVo) {
            System.out.println(user1);
        }
    }

八、如果数据库属性和实体类属性不一致

方法一，设置查询字段别名
方法二, 配置文件
IUserDao.xml

 <resultMap id="userMap" type="com.ect.domain.User">
        <id property="userId" column="id"></id>
        <result property="usrname" column="username"></result>
    </resultMap>
    <select id="findAll" resultMap="userMap">
        SELECT * FROM user
    </select>