mybatis小例子

什么是Mybatis

MyBatis 是一款优秀的持久层框架，它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生信息，将接口和 Java 的 POJOs(Plain Old Java Objects,普通的 Java对象)映射成数据库中的记录。

建立Mybatis工程

在maven工程pom中添加

<dependency>
  <groupId>org.mybatis</groupId>
  <artifactId>mybatis</artifactId>
  <version>3.4.5</version>
</dependency>
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.11</version>
</dependency>

mybatis的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>
  <typeAliases>#对pojo的路径起别名，供mapper的xml文件使用
        <typeAlias alias="Student" type="com.tsrain.domain.Student"/>
    </typeAliases>
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
        <property name="driver" value="${driver}"/>#com.mysql.jdbc.Driver
        <property name="url" value="${url}"/>#jdbc:mysql://localhost:3306/person?serverTimezone=GMT%2B8
        <property name="username" value="${username}"/>
        <property name="password" value="${password}"/>
      </dataSource>
    </environment>
  </environments>
  <mappers>
    <mapper resource="mapper/StudentMapper.xml"/>
  </mappers>

environment主要体现了对事务和连接池的配置，mappers 元素则是包含一组 mapper 映射器
resource是mapper文件的相对地址，由于放在src层级下出现了找不到资源的情况，所以我放到了resource文件夹下。

映射器（mapper）类实现方式

通过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="org.mybatis.example.BlogMapper">#这个路径是将mapper的xml文件映射成一个文件，文件的路径在这设置。
  <select id="selectBlog" resultType="Blog">
    select * from Blog where id = #{id}
  </select>
</mapper>

命名空间“org.mybatis.example.BlogMapper”中定义了一个名为“selectBlog”的映射语句，这样它就允许你使用指定的完全限定名“org.mybatis.example.BlogMapper.selectBlog”来调用映射语句

Mapper XML 文件中sql语句实现

<select id="selectPerson" parameterType="int" resultType="hashmap">
  SELECT * FROM PERSON WHERE ID = #{id}
</select>

这个语句被称作 selectPerson，接受一个 int（或 Integer）类型的参数，并返回一个 HashMap 类型的对象，其中的键是列名，值便是结果行中的对应值，其他类似的语句如下。

<insert id="insertAuthor">
  insert into Author (id,username,password,email,bio)
  values (#{id},#{username},#{password},#{email},#{bio})
</insert>

<update id="updateAuthor">
  update Author set
    username = #{username},
    password = #{password},
    email = #{email},
    bio = #{bio}
  where id = #{id}
</update>

<delete id="deleteAuthor">
  delete from Author where id = #{id}
</delete>

通过注解方式实现

package org.mybatis.example;
public interface BlogMapper {
  @Select("SELECT * FROM blog WHERE id = #{id}")
  Blog selectBlog(int id);
}

构建 SqlSessionFactory

从xml文件中构建，SqlSessionFactory 的实例可以通过 SqlSessionFactoryBuilder 获得。而 SqlSessionFactoryBuilder 则可以从 XML 配置文件或一个预先定制的 Configuration 的实例构建出 SqlSessionFactory 的实例。

String resource = "**/mybatis-config.xml";//mybatis配置文件路径
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

获取SqlSession

SqlSessionFactory是核心，有了 SqlSessionFactory ，就可以从中获得 SqlSession 的实例。SqlSession 完全包含了面向数据库执行 SQL 命令所需的所有方法。你可以通过 SqlSession 实例来直接执行已映射的 SQL 语句。

SqlSession session = sqlSessionFactory.openSession();
try {
  BlogMapper mapper = session.getMapper(BlogMapper.class);
  Blog blog = mapper.selectBlog(101);
} finally {
  session.close();
}

完整的代码：
数据库中的简单表：

image.png

Pojo:

package com.tsrain.domain;

public class Student {
    private int id;
    private String name;
    private int age;

    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 void setAge(int age) {
        this.age = age;
    }
    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }

    public int getAge() {
        return age;
    }
}

mapper文件（放在resource的mapper文件夹下）

<?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.tsrain.domain.StudentMapper">

    <!--     替代，语句中的内容,简写 -->
    <sql id="cols">
        id,name,age
    </sql>
    <!--     查询所有记录 -->
    <select id="listAll" resultType="Student">
        select <include refid="cols"/> from   student
    </select>

    <!--     查询一个用户 -->                            <!--  别名替代，简写-->
    <select id="getOne" parameterType="int" resultType="Student">
        select <include refid="cols"/> from   student  where id= #{id}
    </select>

    <!--     插入一个用户 -->
    <insert id="insertOne" parameterType="Student" >
        insert into student( <include refid="cols"/> ) values (#{id},#{name},#{age})
    </insert>

    <!--     更新一个用户 -->
    <insert id="updateOne" parameterType="Student" >
        update student set name=#{name},age=#{age} where id=#{id}
    </insert>

    <!--     删除一个用户 -->
    <update id="deleteOne" parameterType="Student" >
        delete from student where id= #{id}
    </update>
</mapper>

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>
    <typeAliases>
        <typeAlias alias="Student" type="com.tsrain.domain.Student"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/person?serverTimezone=GMT%2B8"/>
                <property name="username" value="root"/>
                <property name="password" value="****"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mapper/StudentMapper.xml"/>
    </mappers>
</configuration>

测试代码：

package com.tsrain.demo;

import com.tsrain.domain.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.*;
import org.junit.*;

import java.io.*;
import java.util.List;
import java.util.UUID;

public class TestMybatis {

    SqlSessionFactory sqlSessionFactory;
    static String prefix = "com.tsrain.domain.StudentMapper.";

    @Before
    public void initFactory() throws IOException {
        String resource = "configuration.xml";

        InputStream inputStream = Resources.getResourceAsStream(resource);

        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }

    @Test
    public void testListAll() {
        SqlSession session = sqlSessionFactory.openSession();
        List<Student> students = session.selectList(prefix + "listAll");
        System.out.println(students.toString());
    }

    @Test
    public void testQueryOne() {
        SqlSession session = sqlSessionFactory.openSession();
        Student student = session.selectOne(prefix + "getOne", 1);
        System.out.println(student.toString());
    }
    //事务需要程序员处理

    @Test
    public void testInsertOne() {
        Student u = new Student();
        u.setId(3);
        u.setName("HaHa");
        u.setAge(18);
        SqlSession session = sqlSessionFactory.openSession();
        int count = session.insert(prefix + "insertOne", u);
        session.commit();
        System.out.println(count);
    }

    @Test
    public void testUpdateOne() {
        SqlSession session = sqlSessionFactory.openSession();
        Student u = new Student();
        u.setId(3);
        u.setName("newup");
        u.setAge(100);
        int count = session.update(prefix + "updateOne", u);
        session.commit();
        System.out.println(count);
    }

    @Test
    public void testDeleteOne() {
        SqlSession session = sqlSessionFactory.openSession();
        Student u = new Student();
        u.setId(3);
        int count = session.delete(prefix + "deleteOne", u);
        session.commit();
        System.out.println(count);
    }
}

开始时由于mysql数据库无法连接成功，搜集了一份测试连接的代码,此代码与mybatis无关，是最基本的连接。

package com.tsrain.demo;

import java.sql.*;

public class ConMysqlTest {

    public static void main(String[] args) {
        //声明Connection对象
        Connection con;
        //驱动程序名
        String driver = "com.mysql.jdbc.Driver";
        //URL指向要访问的数据库名mydata
        String url = "jdbc:mysql://localhost:3306/person?serverTimezone=GMT%2B8";
        //MySQL配置时的用户名
        String user = "root";
        //MySQL配置时的密码
        String password = "***";
        //遍历查询结果集
        try {
            //加载驱动程序
            Class.forName(driver);
            //1.getConnection()方法，连接MySQL数据库！！
            con = DriverManager.getConnection(url, user, password);
            if (!con.isClosed())
                System.out.println("Succeeded connecting to the Database!");
            //2.创建statement类对象，用来执行SQL语句！！
            Statement statement = con.createStatement();
            //要执行的SQL语句
            String sql = "select * from student";
            //3.ResultSet类，用来存放获取的结果集！！
            ResultSet rs = statement.executeQuery(sql);
            System.out.println("-----------------");
            System.out.println("执行结果如下所示:");
            System.out.println("-----------------");
            System.out.println(" 学号" + "\t" + " 姓名");
            System.out.println("-----------------");

            String name = null;
            int id;
            while (rs.next()) {
                //获取stuname这列数据
                name = rs.getString("name");
                //获取stuid这列数据
                id = rs.getInt("id");
                //首先使用ISO-8859-1字符集将name解码为字节序列并将结果存储新的字节数组中。
                //然后使用GB2312字符集解码指定的字节数组。
                name = new String(name.getBytes("utf-8"), "utf-8");
                //输出结果
                System.out.println(id + "\t" + name);
            }
            rs.close();
            con.close();
        } catch (ClassNotFoundException e) {
            //数据库驱动类异常处理
            System.out.println("Sorry,can`t find the Driver!");
            e.printStackTrace();
        } catch (SQLException e) {
            //数据库连接失败异常处理
            System.out.println("Sorry,SQL exception!");
            e.printStackTrace();
        } catch (Exception e) {
            // TODO: handle exception
            System.out.println("Sorry,Other exception!");
            e.printStackTrace();
        } finally {
            System.out.println("数据库操作结束。");
        }
    }

}