Mybatis实现CRUD（基于dao实现类）

之前的文章都是使用代理dao的方式进行开发，这篇文章将介绍自己实现dao实现类的方式。

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.qublog</groupId>
    <artifactId>mybatis02_dao</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>

    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.16</version>
        </dependency>

        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.12</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.10</version>
        </dependency>
    </dependencies>
</project>

log4j.properties

# Set root category priority to INFO and its only appender to CONSOLE.
#log4j.rootCategory=INFO, CONSOLE            debug   info   warn error fatal
log4j.rootCategory=debug, CONSOLE, LOGFILE

# Set the enterprise logger category to FATAL and its only appender to CONSOLE.
log4j.logger.org.apache.axis.enterprise=FATAL, CONSOLE

# CONSOLE is set to be a ConsoleAppender using a PatternLayout.
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n

# LOGFILE is set to be a File appender using a PatternLayout.
log4j.appender.LOGFILE=org.apache.log4j.FileAppender
log4j.appender.LOGFILE.File=d:\axis.log
log4j.appender.LOGFILE.Append=true
log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
log4j.appender.LOGFILE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n

SqlMapConfig.xml

<?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">
<!-- mybatis的主配置文件 -->
<configuration>
    <!-- 配置环境 -->
    <environments default="mysql">
        <!-- 配置mysql的环境 -->
        <environment id="mysql">
            <!-- 配置事务类型 -->
            <transactionManager type="JDBC"></transactionManager>
            <!-- 配置连接池 -->
            <dataSource type="POOLED">
                <!-- 配置连接数据库的基本信息 -->
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/test_mybatis?serverTimezone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="1234"/>
            </dataSource>
        </environment>
    </environments>

    <!-- 用注解时使用class属性指定被注解的dao全限定类名 -->
    <mappers>
        <mapper resource="com/qublog/dao/UserDao.xml" />
    </mappers>
</configuration>

POJO

package com.qublog.domain;

import java.io.Serializable;
import java.util.Date;

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

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", address='" + address + '\'' +
                ", sex='" + sex + '\'' +
                ", birthday=" + birthday +
                '}';
    }
}

UserDao

package com.qublog.dao;


import com.qublog.domain.User;

import java.util.List;

//用户的持久层接口
public interface UserDao {

    //查询所有用户
    List<User> findAll();

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

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

    //删除用户
    void deleteUser(Integer userId);

    //根据id查询用户
    User findById(Integer userId);

    //根据名称模糊查询用户信息
    List<User> findByName(String username);

    //查询总用户数
    Integer findTotal();

}

UserDaoImpl

package com.qublog.dao.Impl;

import com.qublog.dao.UserDao;
import com.qublog.domain.User;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;

import java.util.List;

public class UserDaoImpl implements UserDao {

    private SqlSessionFactory factory;

    public UserDaoImpl(SqlSessionFactory factory) {
        this.factory = factory;
    }

    public List<User> findAll() {
        //根据factory获取SqlSession对象
        SqlSession session = factory.openSession();
        //调用SqlSession中的方法，实现查询列表
        List<User> users = session.selectList("com.qublog.dao.UserDao.findAll");//参数就是能获取配置信息的key
        //释放资源
        session.close();
        return users;
    }

    public void saveUser(User user) {
        //根据factory获取SqlSession对象
        SqlSession session = factory.openSession();
        //调用SqlSession中的方法，实现保存
        session.insert("com.qublog.dao.UserDao.saveUser", user);
        //提交事务
        session.commit();
        //释放资源
        session.close();
    }

    public void updateUser(User user) {
        //根据factory获取SqlSession对象
        SqlSession session = factory.openSession();
        //调用SqlSession中的方法，实现更新
        session.update("com.qublog.dao.UserDao.updateUser", user);
        //提交事务
        session.commit();
        //释放资源
        session.close();
    }

    public void deleteUser(Integer userId) {
        //根据factory获取SqlSession对象
        SqlSession session = factory.openSession();
        //调用SqlSession中的方法，实现删除
        session.delete("com.qublog.dao.UserDao.deleteUser", userId);
        //提交事务
        session.commit();
        //释放资源
        session.close();
    }

    public User findById(Integer userId) {
        //根据factory获取SqlSession对象
        SqlSession session = factory.openSession();
        //调用SqlSession中的方法，实现查询一个
        User user = session.selectOne("com.qublog.dao.UserDao.findById",userId);
        //释放资源
        session.close();
        return user;
    }

    public List<User> findByName(String username) {
        //根据factory获取SqlSession对象
        SqlSession session = factory.openSession();
        //调用SqlSession中的方法，实现查询列表
        List<User> users = session.selectList("com.qublog.dao.UserDao.findByName",username);
        //释放资源
        session.close();
        return users;
    }

    public Integer findTotal() {
        //根据factory获取SqlSession对象
        SqlSession session = factory.openSession();
        //调用SqlSession中的方法，实现查询一个
        Integer count = session.selectOne("com.qublog.dao.UserDao.findTotal");
        //释放资源
        session.close();
        return count;
    }
}

UserDao.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.qublog.dao.UserDao">

    <!-- 配置查询所有 -->
    <select id="findAll" resultType="com.qublog.domain.User">
        <!-- select id as userId, username as userName, address as userAddress, sex as userSex, birthday as userBirthday from user ; -->
        select * from user;
    </select>

    <!-- 保存用户 -->
    <insert id="saveUser" parameterType="com.qublog.domain.User">
        <!-- 配置插入操作后，获取插入数据的id -->
        <selectKey keyProperty="id" keyColumn="id" resultType="Integer" order="AFTER">
            select last_insert_id();
        </selectKey>
        insert into user(username,address,sex,birthday) value(#{username},#{address},#{sex},#{birthday});
    </insert>

    <!-- 更新用户 -->
    <update id="updateUser" parameterType="com.qublog.domain.User">
        update user set username=#{username},address=#{address},sex=#{sex},birthday=#{birthday} where id=#{id};
    </update>

    <delete id="deleteUser" parameterType="Integer">
        delete from user where id=#{id};
    </delete>

    <!-- 根据id查询用户 -->
    <select id="findById" parameterType="Integer" resultType="com.qublog.domain.User">
        select * from USER where id=#{id};
    </select>

    <!-- 根据名称模糊查询 -->
    <select id="findByName" parameterType="String" resultType="com.qublog.domain.User">
        select * from USER where username like #{username};
    </select>
    
    <!-- 查询总用户数 -->
    <select id="findTotal" resultType="Integer">
        SELECT count(id) from user;
    </select>

</mapper>

MybatisTest

package com.qublog.test;

import com.qublog.dao.Impl.UserDaoImpl;
import com.qublog.dao.UserDao;
import com.qublog.domain.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.InputStream;
import java.util.Date;
import java.util.List;

//测试mybatis的crud操作
public class MybatisTest {
    private InputStream in = null;
    UserDao userDao = null;

    @Before //用于在测试方法执行之前执行
    public void init() throws Exception {
        //读取配置文件，生成字节输入流
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //获取SqlSessionFactory对象
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        //使用工厂对象创建dao对象
        userDao = new UserDaoImpl(factory);
    }

    @After //用于在测试方法执行之后执行
    public void destroy() throws Exception {
        //释放资源
        in.close();
    }

    @Test
    public void testFindAll() throws Exception {
        //执行查询所有方法
        List<User> users = userDao.findAll();
        for (User user:users) {
            System.out.println(user);
        }
    }

    //测试保存操作
    @Test
    public void testSave() throws Exception {
        User user = new User();
        user.setUsername("lamer");
        user.setAddress("wuhan");
        user.setSex("男");
        user.setBirthday(new Date());

        System.out.println("保存前："+user);
        //执行保存方法
        userDao.saveUser(user);
        System.out.println("保存后："+user);
    }

    //测试更新操作
    @Test
    public void testUpdate() {
        User user = new User();
        user.setId(52);
        user.setUsername("tz");
        user.setAddress("wuhan");
        user.setSex("男");
        user.setBirthday(new Date());
        //执行更新方法
        userDao.updateUser(user);
    }

    //测试删除操作
    @Test
    public void testDelete() {
        //执行删除方法
        userDao.deleteUser(52);
    }

    //测试查询一个用户
    @Test
    public void testFindOne() {
        User user = userDao.findById(48);
        System.out.println(user);
    }

    //测试模糊查询
    @Test
    public void testFindByName() {
        List<User> users = userDao.findByName("%王%");
        for (User user:users) {
            System.out.println(user);
        }
    }

    //测试查询总记录条数
    @Test
    public void testFindTotal() {
        Integer count = userDao.findTotal();
        System.out.println(count);
    }


}