MyBatis的CRUD操作

1、创建maven工程，导入坐标

File -> New -> Module

Maven -> Module SDK -> Next

Finish

导入坐标

2、创建数据表对应的pojo与dao接口

2.1 创建pojo

2.1.1 User类

public class User implements Serializable {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
}

2.1.2 QueryVo类

public class QueryVo implements Serializable {
    private User user;
}

2.2 创建dao接口

public interface UserDao {
    /**
     * 查询所有
     * @return
     */
    List<User> findAll();

    /**
     * 保存用户
     * @param user
     */
    void saveUser(User user);

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

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

    /**
     * 根据id查询用户
     * @param id
     */
    User findById(Integer id);

    /**
     * 根据名称模糊查询
     * @param username
     * @return
     */
    List<User> findByUsername(String username);

    /**
     * 查询用户的总记录数
     * @return
     */
    Integer findTotal();

    /**
     * 根据pojo查询用户
     */
    List<User> findByQueryVo(QueryVo queryVo);
}

3、创建Mybatis配置文件

3.1 创建Mybatis主配置文件

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

<!-- 根标签 -->
<configuration>
    <!-- 环境，可以配置多个，default：指定采用哪个环境 -->
    <environments default="development">
        <!-- id：environment的唯一标识 -->
        <environment id="development">
            <!-- 事务管理器，JDBC类型的事务管理器 -->
            <transactionManager type="JDBC"></transactionManager>
            <!-- 数据源，池类型的数据源 -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    
    <!-- 映射配置文件 -->
    <mappers>
        <mapper resource="com/mybatis/dao/UserDao.xml"></mapper>
    </mappers>
</configuration>

3.2 创建Mybatis映射配置文件

<?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.mybatis.dao.UserDao">
    <!-- 查询所有 -->
    <select id="findAll" resultType="com.mybatis.domain.User">
        SELECT * FROM user
    </select>

    <!-- 保存用户 -->
    <insert id="saveUser" parameterType="com.mybatis.domain.User">
        INSERT INTO user(username,birthday,sex,address) values(#{username},#{birthday},#{sex},#{address})
    </insert>

    <!-- 更新用户 -->
    <update id="updateUser" parameterType="com.mybatis.domain.User">
        UPDATE user
        SET username = #{username},
            birthday = #{birthday},
            sex = #{sex},
            address = #{address}
        WHERE id = #{id}
    </update>

    <!-- 删除用户 -->
    <delete id="deleteUser" parameterType="java.lang.Integer">
        DELETE FROM user WHERE id = #{id}   <!-- 当只有一个参数时，参数名称可以任意 -->
    </delete>

    <!-- 根据id查询用户 -->
    <select id="findById" parameterType="java.lang.Integer" resultType="com.mybatis.domain.User">
        SELECT * FROM user WHERE id = #{id}
    </select>

    <!-- 根据名称模糊查询 -->
    <select id="findByUsername" parameterType="java.lang.String" resultType="com.mybatis.domain.User">
        SELECT * FROM user WHERE username LIKE #{%username%}
    </select>

    <!-- 查询用户的总记录数 -->
    <select id="findTotal" resultType="java.lang.Integer">
        SELECT COUNT(id) FROM user
    </select>

    <!-- 根据pojo查询用户 -->
    <select id="findByQueryVo" parameterType="com.mybatis.domain.QueryVo" resultType="com.mybatis.domain.User">
        SELECT * FROM user WHERE username = #{user.username}
    </select>
</mapper>

4、MyBatis执行测试

4.1 测试主类

public class MybatisTest {

    private InputStream inputStream;
    private SqlSessionFactoryBuilder sqlSessionFactoryBuilder;
    private SqlSessionFactory sqlSessionFactory;
    private SqlSession sqlSession;
    private UserDao userDao;

    @Before
    public void init() throws Exception{
        //1. 读取配置文件
        this.inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2. 获取SqlSessionFactoryBuilder
        this.sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        //3. 获取SqlSessionFactory
        this.sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
        //4. 获取SqlSession
        this.sqlSession = sqlSessionFactory.openSession();
        //5. 获取接口的代理对象
        this.userDao = sqlSession.getMapper(UserDao.class);
    }

    @After
    public void destroy() throws Exception{
        //8. 提交事务
        sqlSession.commit();
        //9. 关闭资源
        sqlSession.close();
        inputStream.close();
    }

    /**
     * 查询所有
     */
    @Test
    public void testFindAll() throws Exception {
        List<User> userList = this.userDao.findAll();
        for (User user : userList) {
            System.out.println(user);
        }
    }

    /**
     * 保存用户
     */
    @Test
    public void testSaveUser() throws Exception {
        //6. 利用代理对象调用接口中的方法
        User user = new User();
        user.setUsername("找老刘");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setAddress("上海陆家嘴");
        this.userDao.saveUser(user);
    }

    /**
     * 更新用户
     */
    @Test
    public void testUpdateUser() throws Exception {
        User user = new User();
        user.setId(53);
        user.setUsername("王五");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setAddress("香格里拉");
        userDao.updateUser(user);
    }

    /**
     * 删除用户
     */
    @Test
    public void testDeleteUser() throws Exception {
        userDao.deleteUser(53);
    }

    /**
     * 根据id查询用户
     */
    @Test
    public void testFindById() throws Exception {
        User user = userDao.findById(51);
        System.out.println(user);
    }

    /**
     * 根据名称模糊查询
     */
    @Test
    public void testFindByUsername() throws Exception {
        List<User> userList = userDao.findByUsername("老王");
        for (User user : userList) {
            System.out.println(user);
        }
    }

    /**
     * 查询用户的总记录数
     */
    @Test
    public void testFindTotal() throws Exception {
        Integer total = userDao.findTotal();
        System.out.println(total);
    }

    /**
     * 根据pojo查询用户
     */
    @Test
    public void testFindByQueryVo() throws Exception {
        User user = new User();
        user.setUsername("tom");
        QueryVo queryVo = new QueryVo();
        queryVo.setUser(user);
        List<User> userList = userDao.findByQueryVo(queryVo);
        for (User u : userList) {
            System.out.println(u);
        }
    }
}

4.2 执行结果

-- 执行findAll()
User{id=41, username='张三', birthday=Wed Mar 27 18:47:08 CST 2019, sex='男', address='北京'}
User{id=42, username='李四', birthday=Wed Mar 27 18:47:08 CST 2019, sex='男', address='上海'}
User{id=43, username='王五', birthday=Wed Mar 27 18:47:08 CST 2019, sex='男', address='广州'}