[Java知识库]搭建mybatis环境实现crud

1、idea创建maven工程

2、添加对应的依赖pom.xml

 <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.29</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.1</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.9</version>
        </dependency>

3、创建user实体类

package com.geekmice.mvnmybatis.bo;


import java.util.Date;

public class UserDO {
    private String userName;
    private Date birthday;
    private String sex;
    private String address;
    private String id;

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public Date getBirthday() {
        return birthday;
    }

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

    public String getSex() {
        return sex;
    }

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

    public String getAddress() {
        return address;
    }

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

    public String getId() {
        return id;
    }

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

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

4、创建数据层DAO

package com.geekmice.mvnmybatis.dao;

import com.geekmice.mvnmybatis.bo.UserDO;

import java.util.List;

public interface UserDAO {
    List<UserDO> listUsers();

    UserDO getUserById(String id);
}

5、创建映射文件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.geekmice.mvnmybatis.dao.UserDAO">
    <select id="listUsers" resultType="com.geekmice.mvnmybatis.bo.UserDO">
        select
         user_name as userName,
         birthday,
         sex,
         address,
         id
         from user;
    </select>

    <!--根据id查询用户信息-->
    <select id="getUserById" parameterType="java.lang.String" resultType="com.geekmice.mvnmybatis.bo.UserDO">
        select
        user_name as userName,
        birthday,
        sex,
        address,
        id
        from user
        <where>
            <if test="id != null || id != '' ">
                id = #{id}
            </if>
        </where>
    </select>
</mapper>

6、创建mybatis配置文件mybatis_config.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">
<configuration>

    <!--添加数据库连接映射属性文件-->
    <properties resource="dbconfig.properties"/>

    <!--打印日志-->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    <!-- 7.environments数据库环境配置 -->
    <!-- 和Spring整合后environments配置将被废除 -->
    <environments default="development">
        <environment id="development">
            <!-- 使用JDBC事务管理 -->
            <transactionManager type="JDBC"/>
            <!-- 数据库连接池 -->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url"
                          value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <!-- 8.加载映射文件 -->
    <mappers>
        <mapper resource="mapper/UserDao.xml"/>
    </mappers>
</configuration>

7、创建数据库连接属性文件

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.*:3306/school?useSSL=false&useUnicode=true&characterEncoding=utf-8
jdbc.username=root
jdbc.password=root

8、单元测试

package com.geekmice.mvnmybatis.test;

import com.geekmice.mvnmybatis.bo.UserDO;
import com.geekmice.mvnmybatis.dao.UserDAO;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.*;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.List;

public class MybatisTest {

    private SqlSession sqlSession;

    /**
     * @throws SQLException
     * @Description 读取配置文件，创建SQL工厂，打开回话
     */
    @Before
    public void init() throws SQLException, IOException {
        InputStream is = Resources.getResourceAsStream("mybatis_config.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(is);
        sqlSession = factory.openSession();
    }

    /**
     * @Description 销毁回话
     */
    @After
    public void destroy() {
        if (sqlSession != null) {
            sqlSession.close();
        }
    }

    @Test
    public void test() throws IOException {
        UserDAO userDAO = sqlSession.getMapper(UserDAO.class);
        // 测试查询
        List<UserDO> userDOList = userDAO.listUsers();
        for (UserDO userDO : userDOList) {
            System.out.println(userDO);
        }
    }
}

9、响应结果

Logging initialized using ‘class org.apache.ibatis.logging.stdout.StdOutImpl’ adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1702940637.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6580cfdd]
> Preparing: select user_name as userName, birthday, sex, address, id from user;
> Parameters:
< Columns: userName, birthday, sex, address, id
< Row: 胡一刀, 1995-07-07 00:00:00, 男, 197283@163.com, 1
<== Total: 1
UserBO{userName=‘胡一刀’, birthday=Fri Jul 07 00:00:00 CST 1995, sex=‘男’, address=‘197283@163.com’, id=‘1’}
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6580cfdd]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6580cfdd]
Returned connection 1702940637 to pool.