mybatis（Spring boot集成原生mybatis）-

创建项目：

image.png image.png image.png

创建测试表

在数据库中：创建测试表user

create table user
(
    id bigint not null AUTO_INCREMENT comment '主键' primary key,
    age int null comment '年龄',
    password varchar(32) null comment '密码',
    sex int null comment '性别',
    username varchar(32) null comment '用户名'
);

mybats 集成

配置数据库 db.properties

在资源文件夹下创建db子目录，然后创建db.properties文件

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/helloworld?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
username=root
password=root

配置mybatis: mybatis-config.xml

在资源文件夹下创建mybatis/config子目录，然后创建mybatis-config.xml 文件。注意如下几个地方:
1.引用了db.properties文件，注意路径是否配置正确
2.在 <mappers> 中配置了映射文件（后续将创建该文件）

<?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/db.properties"/>
    <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="mybatis/mapper/UserMapper.xml"/>
    </mappers>
</configuration>

创建数据访问接口UserMapper.java

package com.example.mybatis.mapper;
import com.example.mybatis.entity.User;
public interface UserMapper {
    // 从User表中查询所有记录
    List<Map> selectAll_map();

     // 从User表中查询指定ID的记录
    Map selectByID_map(int id);
}

创建映射xml文件 UserMapper.xml

创建UserMapper.xml文件，该文件路径，应与 mybatis-conifg.xml中配置的路径匹配。
注意:

1. namespase 需要与 UserMapper接口一致（接口绑定）
2. id的值（selectAll_map）需要与 UserMapper接口中的方法名一致（SQL绑定：将接口中的方法，绑定到XML中定义的SQL）
3. resultType指定查询结果映射类型，这里使用hashmap ，即将每条记录映射为hashmap.

<?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.example.mybatis.mapper.UserMapper">
    <select id="selectByID_map" resultType="hashmap">
        select * from user where id = #{id}
    </select>
    <select id="selectAll_map" resultType="hashmap">
        select * from user
    </select>
</mapper>

访问数据库

    @Test
    public void testMybatis() throws IOException {
        //创建SqlSessionFactory
        String resource = "mybatis/config/mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        try (SqlSession session = sqlSessionFactory.openSession()) {
            UserMapper mapper = session.getMapper(UserMapper.class);
            List<Map> list = mapper.selectAll_map();
            System.out.println(list);

            Map map = mapper.selectByID_map(1);
            System.out.println(map);
        }
    }

映射到实体对象 ------------

创建实体类 User.java

package com.example.mybatis.entity;

public class User {
    private int id;
    private String username;
    private String password;
    private int age;
    private int sex;

    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 int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public int getSex() {
        return sex;
    }

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

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

接口定义

User selectAll();

SQL及对象映射

    <select id="selectAll" resultType="com.example.mybatis.entity.User">
        select * from user
    </select>

查询（返回对象）

  List<User> list = mapper.selectAll();
  System.out.println(list);

DELETE

    <delete id="deleteUserByID">
        delete from user where id = #{id}
    </delete>

//接口定义
    int deleteUserByID(int id);
    int deleteUserByID(User user);

//测试DELETE
            int n = mapper.deleteUserByID(2);
            session.commit();
            System.out.println(n);

//测试DELETE
            User user = new User();
            user.setId(2);
            int n = mapper.deleteUserByID(user);
            session.commit();
            System.out.println(n);

INSERT

    <insert id="insertUser">
        insert into user (id,username,password,age,sex)
        values (#{id},#{username},#{password},#{age},#{sex})
    </insert>

    int insertUser(User user);
    int insertUser(int id,String username, String password, int sex, int age);

            User user = new User();
            user.setUsername("Jack");
            user.setPassword("PW123456");
            user.setAge(18);
            int n = mapper.insertUser(user);
            session.commit();
            System.out.println(n);

            mapper.insertUser(0, "Jack02", "PW12345678", 1, 28);
            session.commit();

UPDATE

略

执行SQL

public interface SqlMapper {
    //select
    List<Map> selectBySql(String sql);

    //insert update delete
    int executeBySql(String sql);
}

<mapper namespace="com.example.mybatis.mapper.SqlMapper">
    <select id="selectBySql" parameterType="String" resultType="hashmap">
        ${value}
    </select>

    <insert id="executeBySql" parameterType="String">
        ${value}
    </insert>
</mapper>

            SqlMapper mapper = session.getMapper(SqlMapper.class);
            int i = mapper.executeBySql("insert into user(username,age,sex) values('bbbb',9,8)");
            System.out.println(i);
            session.commit();

            List<Map> list = mapper.selectBySql("select * from user");
            System.out.println(list);

执行SQL(批处理模式-ExecutorType.BATCH)

非批处理模式下，每执行一个SQL会访问一次数据库。
在批处理模式下，多个SQL语句只会访问一次数据库。

        //使用批处理模式
        try (SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
            SqlMapper mapper = session.getMapper(SqlMapper.class);
            mapper.executeBySql("insert into user(username,age,sex) values('bbbb',9,8)");
            mapper.executeBySql("insert into user(username,age,sex) values('bccc',9,8)");
            session.commit();  //提交事务时批量操作才会写入数据库
        }

-end-