MyBatis入门

一 简介

mybatis让程序将主要精力放在sql上，通过mybatis提供的映射方式，自由灵活生成（半自动化，大部分需要程序员编写sql）满足需要sql语句。
mybatis可以将向preparedStatement中输入参数自动进行输入映射，将查询结果灵活映射成java对象。（输出映射）

二 原理

mabatis框架原理

三 入门程序

1 环境搭建

（1）maven jar包

    <!-- https://mvnrepository.com/artifact/junit/junit -->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>

    <!-- https://mvnrepository.com/artifact/asm/asm -->
    <dependency>
      <groupId>asm</groupId>
      <artifactId>asm</artifactId>
      <version>3.3.1</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/cglib/cglib -->
    <dependency>
      <groupId>cglib</groupId>
      <artifactId>cglib</artifactId>
      <version>2.2.2</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/commons-logging/commons-logging -->
    <dependency>
      <groupId>commons-logging</groupId>
      <artifactId>commons-logging</artifactId>
      <version>1.1.1</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.javassist/javassist -->
    <dependency>
      <groupId>org.javassist</groupId>
      <artifactId>javassist</artifactId>
      <version>3.17.1-GA</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/log4j/log4j -->
    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-api -->
    <dependency>
      <groupId>org.apache.logging.log4j</groupId>
      <artifactId>log4j-api</artifactId>
      <version>2.0</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-core -->
    <dependency>
      <groupId>org.apache.logging.log4j</groupId>
      <artifactId>log4j-core</artifactId>
      <version>2.0</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-api -->
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-api</artifactId>
      <version>1.7.5</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-log4j12 -->
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-log4j12</artifactId>
      <version>1.7.5</version>
      <scope>test</scope>
    </dependency>

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

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

（2）log4j.properties

#Global logging configuration
#在开发环境下日志级别要设置成DEBUG，生产环境设置成info或error
log4j.rootLogger=DEBUG,stdout
#Console output
log4j.appender.stdout = org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout = org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern = %5p [%t] - %m%n

（3）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>
    <!--和spring整合后environments配置将废除-->
    <environments default="development">
        <environment id="development">
            <!--使用jdbc事务管理，事务控制由mybatis-->
            <transactionManager type="JDBC"></transactionManager>
            <!--数据库连接池，由mybatis管理-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"></property>
                <property name="url" value="jdbc:mysql://localhost:3306/test?serverTimezone=UTC"></property>
                <property name="username" value="root"></property>
                <property name="password" value="root"></property>
            </dataSource>
        </environment>
    </environments>

环境搭建

2 根据id查询用户

(1) User Bean

package entity;

import java.util.Date;

public class User {
    private int id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    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 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;
    }

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

(2) userMap.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">
        <!--namespace命名空间，作用就是对sql进行分类化管理，区分不同的sql空间
        注意：使用mapper代理方法开发，namespace有特殊重要作用-->
<mapper namespace="test">
    <!--在映射文件中配置很多sql语句-->
   <!-- 需求：通过id查询用户表的记录-->
 <!--   通过select执行数据库查询
    id：标识映射文件中sql，将sql语句封装到mappedStatement对象中，所以将id称为statement的id
    parameterType：指定输入参数的类型，这里指定int型
    resultType:指定sql输出结果所映射的java对象类型
    #{ }表示一个占位符
    #{id}：其中的id表示接收输入的参数，参数名称就是id-->
    <select id="findUserById" parameterType="int" resultType="entity.User">
        SELECT * FROM user WHERE id = #{id}
    </select>
</mapper>

(3) 在核心配置文件中加载映射文件

图片.png

(4) 测试

import entity.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.jupiter.api.Test;

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

public class mybatisTest {
    @Test
    public void findUserById() throws IOException {
        //配置文件
        String resource = "SqlMapConfig.xml";
        //得到配置文件流
        InputStream inputStream = Resources.getResourceAsStream(resource);
        //创建会话工厂
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //通过工厂得到SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //通过SqlSession操作数据库
            //第一个参数：等于 = namespace+“.”+statement的id
            //第二个参数：指定映射文件中所匹配的parameterType类型的参数
        User user = sqlSession.selectOne("test.findUserById",1);
        System.out.println(user);
        sqlSession.close();
    }
}

执行结果

3 根据用户名称模糊查询信息

(1) userMap.xml

...
    <!--根据用户名称模糊查询信息-->
    <!--可能返回多条信息
    ${}：表示拼接sql串，将接收到的参数的内容不加任何修饰拼接在sql中
    使用${}拼接sql，引起sql注入
    ${value}：接收输入参数的内容，如果出入类型是简单类型，${}只能使用value-->
    <select id="findUserByName" parameterType="String" resultType="entity.User">
        SELECT * FROM  user WHERE username LIKE "${value}%"
    </select>
...

(2) 测试

    @Test
    public void findUserByName() throws IOException {
        InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<User> list = sqlSession.selectList("test.findUserByName","马");
        System.out.println(list);
    }

执行结果

4 添加用户

（1）userMap.xml

....
    <!--添加用户-->
    <insert id="insertUser" parameterType="entity.User">
        <!--将插入数据的主键返回
        SELECT Last_insert_ID 得到刚插入的主键值 只适用与自增主键

        keyProperty :将查询到主键值设置到parameterType指定的对象的那个属性
        order:SELECT Last_insert_ID执行顺序，相对于insert语句来说它的执行顺序
        resultType:指定SELECT LAST_INSERT_ID()的结果类型
        -->
        <selectKey keyProperty="id" order="AFTER" resultType="int">
            SELECT LAST_INSERT_ID()
        </selectKey>
        INSERT INTO user (username,birthday,sex,address) VALUES(#{username},#{birthday},#{sex},#{address})
    </insert>
....

(2) 测试

    @Test
    public void insertUser() throws IOException {
        InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        User user = new User();
        user.setUsername("张三");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setAddress("美国纽约");
        sqlSession.insert("test.insertUser",user);
        sqlSession.commit();
        System.out.println(user.getId());
        System.out.println(new Date());
        sqlSession.close();
    }

5 删除用户

（1）userMap.xml

....
    <!--删除用户 通过id-->
    <delete id="deleteUser" parameterType="int">
        DELETE from user where id = #{id}
    </delete>
....

(2) 测试

    @Test
    public void deleteUser() throws IOException {
       InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
       SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
       SqlSession sqlSession = sqlSessionFactory.openSession();
       sqlSession.delete("test.deleteUser",12);
        sqlSession.commit();
        sqlSession.close();
    }

6 更新用户

（1）userMap.xml

.....
<!--修改用户信息 根据id-->
    <update id="updateUser" parameterType="entity.User">
        UPDATE user SET username = #{username},birthday = #{birthday},sex = #{sex},address = #{address} WHERE  id = #{id}
    </update>
.....

(2) 测试

    @Test
    public void updateUser() throws IOException {
        InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession= sqlSessionFactory.openSession();
        User user = new User();
        user.setId(12);
        user.setUsername("李四");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setAddress("英国伦敦");
        sqlSession.update("test.updateUser",user);
        sqlSession.commit();
        sqlSession.close();
    }