Mybatis开发CRUD

目录结构

添加MyBatis的坐标及其他依赖

<dependencies>
        <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
        <!-- mybatis坐标 -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.5</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <!-- mysql驱动坐标 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.21</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/junit/junit -->
        <!-- 单元测试坐标 -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.1</version>
            <scope>test</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <!-- lombok坐标 -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.14</version>
            <scope>provided</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/log4j/log4j -->
        <!-- 日志坐标 -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
    </dependencies>

准备数据库环境

create database mybatis;
use mybatis;

CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(32) NOT NULL COMMENT '用户名称',
`sex` char(1) DEFAULT NULL COMMENT '性别',
`address` varchar(256) DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `user` (`id`, `username`, `sex`, `address`)
values(NULL,'我爱罗','男','砂隐村');
insert into `user` (`id`, `username`, `sex`, `address`)
values(NULL,'由木人','女','云隐村');
insert into `user` (`id`, `username`, `sex`, `address`)
values(NULL,'矢仓','男','雾隐村');
insert into `user` (`id`, `username`, `sex`, `address`)
values(NULL,'老紫','男','岩隐村');

编写User实体类（提前安装lombok插件并导入依赖）

import lombok.Data;

@Data
public class User {
    private Integer id; //定义类型为Integer，在初始化的时候会设置为null；而设置类型为int的话，数据初始化为0，可能会具有实际意义，产生二义性
    private String username;
    private String sex;
    private String address;
}

编写核心文件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">
<configuration>
    <!--配置数据库运⾏环境-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url"
                          value="jdbc:mysql://127.0.0.1:3306/mybatis?characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=UTC&amp;rewriteBatchedStatements=true"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <!--配置关联的SQL语句的映射⽂件-->
    <mappers>
        <mapper resource="UserMapper.xml"/>
    </mappers>
</configuration>

编写映射文件UserMapper.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="userMapper">
    <!--查询所有⽤户-->
    <select id="findAll" resultType="com.byc.pojo.User">
             select * from user
    </select>
</mapper>

7. 编写测试类

import com.byc.pojo.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.Test;

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


public class UserTest {
    @Test
    public void findAll() throws IOException {
        //加载核⼼配置⽂件 SqlMapConfig.xml
        InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        //构造SQLSessionFactory对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //构造SQLSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //执⾏查询语句
        List<User> userList = sqlSession.selectList("userMapper.findAll");
        for (User user : userList) {
            System.out.println("user = " + user);
        }
        //释放资源
        sqlSession.close();
    }
}

运行截图

CRUD操作

1. 插入

    <insert id="insert" parameterType="com.byc.pojo.User">
        insert into user values (#{id},#{username},#{sex},#{address})
    </insert>

import com.byc.pojo.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.Test;

import java.io.IOException;
import java.io.InputStream;

public class UserTest {
    @Test
    public void insert() throws IOException {
        //加载核⼼配置⽂件 SqlMapConfig.xml
        InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        //构造SQLSessionFactory对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //构造SQLSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //执行插入语句
        User user = new User();
        user.setUsername("鸣人");
        user.setSex("男");
        user.setAddress("木叶村");
        int line = sqlSession.insert("userMapper.insert", user);
        System.out.println("line = "+line);
        sqlSession.commit();
        //释放资源
        sqlSession.close();
    }
}

和上面不一样的是mybatis提交数据的时候默认设置的是手动提交，所以在资源释放之前需要增添资源提交的代码sqlSession.commit();

由于在数据库表结构中，设置用户id是主键自增型，所以需要获取并在程序中使用
2. 删除

    <delete id="delete" parameterType="java.lang.Integer">
        delete from user where id=#{id}
    </delete>

import com.byc.pojo.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.Test;

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

public class UserTest {
    @Test
    public void delete() throws IOException {
        //加载核心配置⽂件 SqlMapConfig.xml
        InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        //构造SQLSessionFactory对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //构造SQLSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        sqlSession.delete("userMapper.delete",2);
        sqlSession.commit();
        //释放资源
        sqlSession.close();
    }
}

3. 更新

    <update id="update" parameterType="com.byc.pojo.User">
        update user set username=#{username},sex=#{sex},address=#{address} where id=#{id}
    </update>

import com.byc.pojo.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.Test;

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

public class UserTest {
    @Test
    public void update() throws IOException {
        //加载核⼼配置⽂件 SqlMapConfig.xml
        InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        //构造SQLSessionFactory对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //构造SQLSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //执⾏更新语句
        User user = new User();
        user.setId(7);
        user.setUsername("jack");
        user.setSex("男");
        user.setAddress("⽇本");
        sqlSession.update("userMapper.update",user);
        sqlSession.commit();
        //释放资源
        sqlSession.close();
    }
}

4. 查询
   1）通过用户ID

    <select id="findById" parameterType="integer" resultType="com.byc.pojo.User">
        select * from user where id=#{id}
    </select>

import com.byc.pojo.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.Test;

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

public class UserTest {
    @Test
    public void findById() throws IOException {
        //加载核⼼配置⽂件 SqlMapConfig.xml
        InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        //构造SQLSessionFactory对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //构造SQLSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
/*        //执⾏查询语句
        List<User> userList = sqlSession.selectList("userMapper.findAll");
        for (User user : userList) {
            System.out.println("user = " + user);
        }*/
//        //执行插入语句
//        User user = new User();
//        user.setUsername("鸣人");
//        user.setSex("男");
//        user.setAddress("木叶村");
//        int line = sqlSession.insert("userMapper.insert", user);
//        System.out.println("line = "+line);
//        sqlSession.commit();
//        System.out.println("id = "+user.getId());
//        sqlSession.delete("userMapper.delete",2);
//        sqlSession.commit();
//        //执⾏更新语句
        User user = new User();
        user.setId(7);
        user.setUsername("jack");
        user.setSex("男");
        user.setAddress("⽇本");
        sqlSession.update("userMapper.update",user);
        sqlSession.commit();
        //执⾏查询语句
        User user = sqlSession.selectOne("userMapper.findById", 8);
        System.out.println("user = " + user);
        //释放资源
        sqlSession.close();
    }
}