Mybatis的增删改查

一、准备

创建一个空的maven项目

导入依赖包：

copy
<dependencies>
 
    <!--数据库驱动-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.9</version>
    </dependency>
 
    <!--mybatis-->
    <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.5</version>
    </dependency>
 
    <!--单元测试-->
    <!-- https://mvnrepository.com/artifact/junit/junit -->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.13</version>
        <scope>test</scope>
    </dependency>
</dependencies>

maven项目过滤静态文件：

copy
<build>
    <resources>
        <resource >
            <directory>src/main/resources</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>true</filtering>
        </resource>
 
        <resource >
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>true</filtering>
        </resource>
    </resources>
</build>
 
<!--设置字符集-->
<properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>

配置完pom.xml文件之后，要记得点击右下角的Enable Auto-import，让idea自动导入jar包，过程可能有点慢。

二、创建数据库表

创建名为userinfo的数据表：

copy
CREATE TABLE userinfo(
id INT(10) NOT NULL AUTO_INCREMENT COMMENT '用户id',
name VARCHAR(100) NOT NULL COMMENT '用户名',
password VARCHAR(20) NOT NULL COMMENT '密码',
KEY id (id)
)ENGINE=INNODB DEFAULT CHARSET=utf8

向数据表添加数据：

copy
INSERT INTO userinfo (id,name,password) VALUES
(1,'小明','123456'),
(2,'小蓝','123456'),
(3,'小黄','123456'),
(4,'小绿','123456');

三、创建实体类

copy
public class UserInfo {
    private int id;
    private String name;
    private String password;

    public UserInfo(int id, String name, String password) {
        this.id = id;
        this.name = name;
        this.password = password;
    }

    public UserInfo() {
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

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

    @Override
    public String toString() {
        return "UserInfo{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

四、接口实现类和UserMapper.xml

接口类要与配置文件放在同一个包下

UserMapper.java接口文件：

copy
public interface UserMapper {

    List<UserInfo> getList(); //查询所有的用户数据

    int addUser(UserInfo userInfo); //添加用户

    int deleteUser(int id); //删除用户

    int updateUser(UserInfo userInfo); //修改用户

    UserInfo getUserById(int id); //根据id查询用户

}

UserMapper.xml配置文件：

copy
<?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.dao.UserMapper">

    <!--
    在mybatis-config.xml中设置的别名在这里使用，resultType，userInfo
    -->
    <!--查询所有用户信息-->
    <select id="getList" resultType="userInfo">
    select * from userinfo;
  </select>

    <!--根据id查询用户信息-->
    <select id="getUserById" parameterType="int" resultType="userInfo">
    select * from userinfo where id = #{id};
  </select>

    <!--添加用户-->
    <insert id="addUser" parameterType="userInfo">
        insert into userinfo(id,name,password)value(#{id},#{name},#{password});
    </insert>

    <!--删除用户-->
    <delete id="deleteUser" parameterType="int">
        delete from userinfo where id = #{id};
    </delete>

    <!--修改用户-->
    <update id="updateUser" parameterType="userInfo">
        update userinfo set name=#{name},password=#{password} where id = #{id};
    </update>
</mapper>

五、创建mybatis的配置文件

首先创建一个mybatis-config.xml的配置文件，其次创建一个db.properties的数据库连接基本信息文件，db.properties的作用是放置数据库参数，可以动态改变你需要用的数据库，当你想换数据库时无需改动mybatis-config.xml，只需要改动db.properties中的参数即可

mybatis-config.xml配置文件：

copy
<?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"></properties>

    <!--别名-->
    <typeAliases>
        <!--根据包名下的类来起别名，一般用类的小写来作为名字，大写也可以用，也可以用注解的方式加上别名@Alias("")-->
        <package name="com.pojo"/>
    </typeAliases>


    <!--连接数据库的环境-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>


    <mappers>
        <mapper resource="com/dao/UserMapper.xml"/>
    </mappers>

</configuration>

db.properties数据库参数文件：

copy
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/text?userSSL=true&userUnicode=true&characterEncoding=UTF-8
username=root
password=123456

mybatis工具类：

copy
public class MyBatisUtils {

    private static SqlSessionFactory sqlSessionFactory;

    static{
        try {

            String resource = "mybatis-config.xml";
            InputStream inputStream  = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        } catch (IOException e) {
            e.printStackTrace();
        }

    }
    public static SqlSession getSqlSession(){
        //这个true是自动提交事务，当要对数据表进行操作时，就要提交事务，否则进行添加修改等操作数据表无变化
        return sqlSessionFactory.openSession(true);
    }
}

六、测试类

copy
public class MyTest {

    //显示所有用户数据
    @Test
    public void getAllUser(){

        SqlSession sqlSession = MyBatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        List<UserInfo> list = mapper.getList();

        for (UserInfo user : list) {
            System.out.println(user);
        }
        sqlSession.close();
    }

    //根据用户id查询用户信息
    @Test
    public void getUserByid(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        UserInfo userInfo = mapper.getUserById(1);//传入用户id

        System.out.println(userInfo);
    }

    //添加用户
    @Test
    public void addUser(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        int i = mapper.addUser(new UserInfo(5,"小红","77777777"));

        if (i>0){
            System.out.println("添加成功");
        }
        sqlSession.close();
    }

    //删除用户
    @Test
    public void deleteUser(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        int i = mapper.deleteUser(5);

        if (i>0){
            System.out.println("删除成功");
        }
        sqlSession.close();
    }

    //修改用户
    @Test
    public void updateUser(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        int i = mapper.updateUser(new UserInfo(1,"小花","444444"));

        if (i>0){
            System.out.println("修改成功");
        }

        sqlSession.close();
    }

}

七、结构