重温MyBatis（二、增删改查操作）

1、jar包准备：

• mybatis-3.4.6.jar
• log4j-1.2.17.jar
• mysql-connector-java-5.1.6-bin.jar

2、创建实体类UserInfo：

package com.springmvc.pojo;

/**
 * @author YuusukeUchiha
 * @date 2020/7/22 22:20
 * @ide IntelliJ IDEA
 */
public class UserInfo {

    private int id;
    private String userName;
    private String password;
    private String realName;
    private String sex;
    private String address;
    private String email;
    private String regDate;
    private int status;

    @Override
    public String toString() {
        return "UserInfo{" +
                "id=" + id +
                ", userName='" + userName + '\'' +
                ", password='" + password + '\'' +
                ", realName='" + realName + '\'' +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                ", email='" + email + '\'' +
                ", regDate='" + regDate + '\'' +
                ", status=" + status +
                '}';
    }

    public int getId() {
        return id;
    }

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

    public String getUserName() {
        return userName;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getRealName() {
        return realName;
    }

    public void setRealName(String realName) {
        this.realName = realName;
    }

    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 getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getRegDate() {
        return regDate;
    }

    public void setRegDate(String regDate) {
        this.regDate = regDate;
    }

    public int getStatus() {
        return status;
    }

    public void setStatus(int status) {
        this.status = status;
    }
}

3、创建属性文件db.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/springdemo
jdbc.username=root
jdbc.password=123456

4、创建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>

    <!--    加载属性文件-->
    <properties resource="db.properties" />

    <!--    给包中的类注册别名-->
    <typeAliases>
        <package name="com.springmvc.pojo"/>
    </typeAliases>

    <!--    配置环境-->
    <environments default="development">
        <!--    配置一个id为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>

    <!--    引用映射文件-->
    <mappers>
        <mapper resource="com/springmvc/mapper/UserInfoMapper.xml" />
    </mappers>

</configuration>

5、创建SQL映射的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.springmvc.mapper.UserInfoMapper">

    <!--    根据用户编号查询用户信息-完整查询-->
    <select id="findUserInfoById" parameterType="int" resultType="UserInfo">
        select * from user where id = #{id}
    </select>

    <!--    根据用户编号查询用户信息-模糊查询-->
    <select id="findUserInfoByUserName" parameterType="String" resultType="UserInfo">
        select * from user where userName like concat(concat('%', #{userName}), '%')
    </select>

    <!--    添加用户-->
    <insert id="addUserInfo" parameterType="UserInfo">
        insert into user(userName, password) values(#{userName}, #{password});
    </insert>

    <!--   修改用户-->
    <update id="updateUserInfo" parameterType="UserInfo">
        update user set userName = #{userName}, password = #{password} where id = #{id}
    </update>

    <delete id="deleteUserInfo" parameterType="int">
        delete from user where id = #{id}
    </delete>

</mapper>

6、创建测试类MybatisTest：

package com.springmvc.test;

import com.springmvc.pojo.UserInfo;
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.IOException;
import java.io.InputStream;
import java.util.List;

/**
 * @author YuusukeUchiha
 * @date 2020/7/22 22:45
 * @ide IntelliJ IDEA
 */
public class MybatisTest {

    private SqlSessionFactory sqlSessionFactory;
    private SqlSession sqlSession;

    @Before
    public void init() {
        // 读取mybatis配置文件
        String resource = "mybatis-config.xml";
        InputStream inputStream;

        try {
            // 得到配置文件
            inputStream = Resources.getResourceAsStream(resource);

            // 根据配置文件信息，创建会话工厂
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

            // 通过工厂得到SqlSession
            sqlSession = sqlSessionFactory.openSession();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //    @Test
    public void testFindUserInfoById() {

        // 通过sqlSession执行映射文件中定义的SQL，并返回映射结果
        UserInfo userInfo = sqlSession.selectOne("findUserInfoById", 1);

        // 打印输出结果
        System.out.println(userInfo.toString());
    }

    //    @Test
    public void testFindUserInfoByUserName() {
        // 执行映射文件中定义的SQL，并返回映射结果
        List<UserInfo> all = sqlSession.selectList("findUserInfoByUserName", "张");
        for (UserInfo userInfo : all) {
            // 打印输出结果
            System.out.println(userInfo.toString());
        }
    }

//    @Test
    public void testAddUserInfo() {
        // 创建UserInfo对象
        UserInfo userInfo = new UserInfo();

        // 向对象中添加数据
        userInfo.setUserName("李四2号");
        userInfo.setPassword("123456");

        // 执行sqlSession的insert方法，返回结果是SQL语句受影响的行数
        int result = sqlSession.insert("addUserInfo", userInfo);
        if (result > 0) {
            System.out.println("插入成功");
        } else {
            System.out.println("插入失败");
        }
    }

//    @Test
    public void testUpdateUserInfo() {
        // 加载id号为8的用户
        UserInfo userInfo = sqlSession.selectOne("findUserInfoById", 7);
        // 重新设置用户密码
        userInfo.setPassword("123123");
        // 执行sqlSession的update方法，返回结果是SQL语句受影响的行数
        int result = sqlSession.update("updateUserInfo", userInfo);

        if (result > 0) {
            System.out.println("更新成功");
            System.out.println(userInfo);
        } else {
            System.out.println("更新失败");
        }
    }

    @Test
    public void testDeleteUserInfo(){
        // 执行sqlSession的delete方法，灰灰的结果是SQL语句受影响的行数
        int result = sqlSession.delete("deleteUserInfo", 8);

        if(result > 0){
            System.out.println("成功删除了" + result + "条记录");
        }else{
            System.out.println("删除失败");
        }
    }

    @After
    public void destroy() {
        // 提交事务
        sqlSession.commit();

        // 关闭sqlSession
        sqlSession.close();
    }
}