Mybatis 入门

第一个程序

工具和环境

  • mysql
  • maven

创建数据库

create database MybatisDemo;
use MybatisDemo;

create table User (
    id int primary key auto_increment,
    name varchar(10)
)charset=utf8;


环境配置

创建 maven 工程,修改 pom.xml:

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" 
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>website.yuchen</groupId>
  <artifactId>MybatisDemo</artifactId>
  <version>1.0-SNAPSHOT</version>

  <name>MybatisDemo</name>
  <!-- FIXME change it to the project's website -->
  <url>https://www.yuchen.website</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>

    <mybatis.version>3.5.0</mybatis.version>
    <mysql.version>8.0.15</mysql.version>

  </properties>

  <dependencies>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>${mybatis.version}</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>${mysql.version}</version>
    </dependency>
  </dependencies>

</project>


配置文件

添加文件夹,使工程的目录结构大致如下:

MybatisDemo/
├── pom.xml
└── src
    ├── main
    │   ├── java
    │   └── resources
    └── test
        └── java

如果是 idea,可以通过 Project Structre 设置标记main下的 java 为源码文件夹;resources 为资源文件夹;test 为测试文件夹


在源码文件夹下创建包 website.yuchen.model,以及对应数据库 User 表的 Bean:

package website.yuchen.model;

public class User
{
    private Integer id;  private String name;

    public Integer getId() {return id;}
    public void setId(Integer id) {this.id = id;}

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

编写操作的接口,位置在包 website.yuchen.mapper 下:

package website.yuchen.mapper;
import website.yuchen.model.User;

public interface UserMapper {
    public User getUser(Integer id) throws Exception;
    public void insertUser(User User) throws Exception;
}

接下来就需要创建这个接口对应的配置文件了:
在resources下编写: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="website.yuchen.mapper.UserMapper">

    <select id="getUser" parameterType="int" resultType="User">
        select * from User where id=#{id}
    </select>

    <insert id="insertUser"  parameterType="User">
        insert into User values(#{id}, #{name})
    </insert>

</mapper>

tips:

  • mapper namespace 名字空间需要对应接口所在的位置


接着在resources下创建 MybatisConfig.xml,作为对 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="User" type="website.yuchen.model.User" />
    </typeAliases>

    <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://localhost:3306/MybatisDemo" />
                <property name="username" value="root" />
                <property name="password" value="123456" />
            </dataSource>
        </environment>
    </environments>

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

</configuration>


最后在test文件夹下创建测试主函数:插入一条数据并从数据库中读取出来:

package website.yuchen;

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 website.yuchen.mapper.UserMapper;
import website.yuchen.model.User;
import java.io.Reader;


public class Main
{
    private static SqlSessionFactory ssfactory;
    private static Reader reader;

    static {    // 预加载配置文件
        try {
            reader = Resources.getResourceAsReader("MybatisConfig.xml");
            ssfactory = new SqlSessionFactoryBuilder().build(reader);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args)
    {
        SqlSession ss = ssfactory.openSession();
        UserMapper mapper = ss.getMapper(UserMapper.class);

        User o = new User();
        o.setId(1);  o.setName("yuchen");

        try {
            mapper.insertUser(o);
            ss.commit();

            User User = mapper.getUser(1);
            System.out.println(User.getName());
            ss.commit();
        }
        catch(Exception e) {
            e.printStackTrace();
            ss.rollback();
        }
        finally {ss.close();}
    }
}

tips:

  • 因为之前Mybatis中的配置,所以在这里commit、rollback 等方法对应 JDBC 中的事务



接口注释

将文档注释添加到接口对应的方法上,来减少配置文件的内容。修改上例中的接口:

package website.yuchen.mapper;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import website.yuchen.model.User;

public interface UserMapper
{
    @Select("select * from User where id = #{id}")
    public User getUser(Integer id) throws Exception;

    @Insert("insert into User values(#{id}, #{name})")
    public void insertUser(User User) throws Exception;
}

于是可以去掉 MybatisConfig.xml 中的对应配置:

<mappers>
    <!--<mapper resource="UserMapper.xml" />-->
</mappers>

因为在配置文件中不能加载这个接口,所以在主函数中加入这个接口:

static {    // 预加载配置文件
    try {
        reader = Resources.getResourceAsReader("MybatisConfig.xml");
        ssfactory = new SqlSessionFactoryBuilder().build(reader);

        ssfactory.getConfiguration().addMapper(UserMapper.class);    // 加入接口映射
    }
    catch (Exception e) {
        e.printStackTrace();
    }
}

在清除上个例子中插入数据库的数据后,重新运行,可以得到一样的结果


增删改查

还原上例中的注释方式到第一个程序的配置,并清空数据库的User表,为接口添加新的方法:

package website.yuchen.mapper;
import website.yuchen.model.User;
import java.util.List;

public interface UserMapper {
    public User getUser(Integer id) throws Exception;
    public void insertUser(User User) throws Exception;
    public void updateUser(User User) throws Exception;
    public void deleteUser(Integer id) throws Exception;
    public List<User> getAllUser() throws Exception;
}

同样对应的配置文件需要进行修改:

<?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="website.yuchen.mapper.UserMapper">

    <select id="getUser" parameterType="int" resultType="User">
        select * from User where id=#{id}
    </select>

    <insert id="insertUser"  parameterType="User">
        insert into User values(#{id}, #{name})
    </insert>

    <update id="updateUser" parameterType="User">
        update User set name=#{name} where id=#{id}
    </update>

    <delete id="deleteUser" parameterType="int">
        delete from User where id=#{id}
    </delete>

    <select id="getAllUser" resultType="User">
        select * from User;
    </select>

</mapper>

以上完成了最基本的增删改查原子操作,最后在主函数中测试,添加测试函数:

public static void insertTest()
{
    SqlSession ss = ssfactory.openSession();
    UserMapper mapper = ss.getMapper(UserMapper.class);
    User o = new User();

    try {
        o.setId(1);  o.setName("AAA");
        mapper.insertUser(o);

        o.setId(2);  o.setName("BBB");
        mapper.insertUser(o);

        o.setId(3);  o.setName("CCC");
        mapper.insertUser(o);

        ss.commit();
    }
    catch(Exception e) {
        e.printStackTrace();
        ss.rollback();
    }
    finally {ss.close();}
}
public static void deleteTest()
{
    SqlSession ss = ssfactory.openSession();
    UserMapper mapper = ss.getMapper(UserMapper.class);

    try {
        mapper.deleteUser(2);
        ss.commit();
    }
    catch(Exception e) {
        e.printStackTrace();
        ss.rollback();
    }
    finally {
        ss.close();
    }
}
public static void updateTest()
{
    SqlSession ss = ssfactory.openSession();
    UserMapper mapper = ss.getMapper(UserMapper.class);

    try {
        User o = new User();
        o.setId(3);  o.setName("DDD");

        mapper.updateUser(o);
        ss.commit();
    }
    catch(Exception e) {
        e.printStackTrace();
        ss.rollback();
    }
    finally {ss.close();}
}
public static void showAll()
{
    SqlSession ss = ssfactory.openSession();
    UserMapper mapper = ss.getMapper(UserMapper.class);

    try {
        List<User> ls = mapper.getAllUser();
        ls.forEach(e-> {System.out.println(
            "id: " + e.getId() + " name: " + e.getName()
        );});

        ss.commit();
    }
    catch(Exception e) {
        e.printStackTrace();
        ss.rollback();
    }
    finally {ss.close();}
}

由main函数调用这些方法:

public static void main(String[] args)
{
    System.out.println("\ninsert test: ");
    insertTest();
    showAll();

    System.out.println("\nupdate test: ");
    updateTest();
    showAll();

    System.out.println("\ndelete test...");
    deleteTest();
    showAll();
}

/*输出:

insert test: 
id: 1 name: AAA
id: 2 name: BBB
id: 3 name: CCC

update test: 
id: 1 name: AAA
id: 2 name: BBB
id: 3 name: DDD

delete test...
id: 1 name: AAA
id: 3 name: DDD*/



多表关联

另外创建一个group表,假定一个group中可以有多个User,于是这两个表的关系就是一对多:

create table `Group` (
    id int primary key auto_increment,
    name varchar(10)
)charset=utf8;

alter table User add column gid int default 1;
alter table User add foreign key(gid) references `Group`(id);

清空两个表的数据,并插入一些新的测试数据:

delete from User;
delete from `Group`;

insert into `Group` values(1, "甲");
insert into `Group` values(2, "乙");

insert into User values(1, "AAA", 1);
insert into User values(2, "BBB", 1);
insert into User values(3, "CCC", 2);
insert into User values(4, "DDD", 2);


创建 Group 对应的Bean类:

package website.yuchen.model;

import java.util.List;

public class Group
{
    private Integer id;  private String name;
    private List<User> users;

    public Integer getId() {return id;}
    public void setId(Integer id) {this.id = id;}

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

    public List<User> getUsers() {return users;}
    public void setUsers(List<User> users) {this.users = users;}
}

创建 GroupMapper 接口方法:

package website.yuchen.mapper;
import website.yuchen.model.Group;

public interface GroupMapper {
    public Group getGroup(Integer id) throws Exception;
}

以及 GroupMapper 对应的映射配置:

<?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="website.yuchen.mapper.GroupMapper">

    <resultMap id="groupResultMap" type="Group">
        <id property="id" column="gid" />
        <result property="name" column="gname" />
        <collection property="users" ofType="User">
            <id property="id" column="uid" />
            <result property="name" column="uname" />
        </collection>
    </resultMap>

    <select id="getGroup" parameterType="int" resultMap="groupResultMap">
      select Group.id as gid, Group.name as gname,
            User.id as uid, User.name as uname
        from `Group`, User
        where Group.id=#{id} and User.gid=Group.id
    </select>

</mapper>

tips:

  • resultMap 的设置是将数据库查询的结果集手动映射到Bean对应的属性上
  • collection 子标签是 Java集合(Set、List) 类型的映射,常用于一对多的配置
  • collection 子属性 ofType 用来指定集合内的元素的类型
  • 要避免查询结果集重名的情况


记得将 GroupMapper.xml 加入Mybatis配置文件:

<typeAliases>
    <typeAlias alias="User" type="website.yuchen.model.User" />
    <typeAlias alias="Group" type="website.yuchen.model.Group" />
</typeAliases>
<mappers>
    <mapper resource="UserMapper.xml" />
    <mapper resource="GroupMapper.xml" />
</mappers>

最后是主函数测试:

package website.yuchen;

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 website.yuchen.mapper.GroupMapper;
import website.yuchen.model.Group;
import java.io.Reader;


public class Main
{
    private static SqlSessionFactory ssfactory;
    private static Reader reader;

    static {    // 预加载配置文件
        try {
            reader = Resources.getResourceAsReader("MybatisConfig.xml");
            ssfactory = new SqlSessionFactoryBuilder().build(reader);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args)
    {
        SqlSession ss = ssfactory.openSession();
        GroupMapper mapper = ss.getMapper(GroupMapper.class);

        try {
            int groupId = 1;
            Group group = mapper.getGroup(1);
            System.out.println("group name: " + group.getName());

            group.getUsers().forEach(e -> {System.out.println(
                "id: " + e.getId() + " name: " + e.getName()
            );});

            ss.commit();
        }
        catch(Exception e) {
            e.printStackTrace();
            ss.rollback();
        }
        finally {ss.close();}
    }
}

/*输出:
group name: 甲
id: 1 name: AAA
id: 2 name: BBB
*/


  • 一对多的关系常常在“一”所对应的Bean中设立集合属性(比如此例中的Group中 users属性是 List 类型),若需要设置这个类型(List)的resultMap,则collection子标签中的ofType设置为集合内元素的类型(这里是User类型)
  • 一对一的关系(比如说这个例子中一组只能有一个人,且Group中 users属性就是 User 类型),这时当需要设置resultMap时,对于这个类型(User),对应的子标签为association没有 ofType 属性,但可以设置 javaType 属性
  • 多对多的关系(比如此例中用户可以参与多个组,在用户类中也要加个groups属性的List,去掉User的gid属性的关联使两个表相对独立),这时需要在数据库中加上一个中转过度的表 Linker以及对应的Bean类,对这个LinkerMapper进行操作,方法同 一对多 的处理



动态SQL

简单修改User表:插入一列为年龄,并加入新数据:

alter table User add column age int not null;

delete from User;
insert into User(id, name, gid, age) values(1, "AAA", 1, 20);
insert into User(id, name, gid, age) values(2, "BBB", 1, 18);
insert into User(id, name, gid, age) values(3, "CCC", 2, 21);
insert into User(id, name, gid, age) values(4, "ABC", 1, 20);

以及对应的 User Bean类:

package website.yuchen.model;

public class User
{
    private Integer id;  private String name;
    private Integer age;

    public Integer getId() {return id;}
    public void setId(Integer id) {this.id = id;}

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

    public Integer getAge() {return age;}
    public void setAge(Integer age) {this.age = age;}
}


重置UserMapper接口和xml:

package website.yuchen.mapper;

import org.apache.ibatis.annotations.Param;
import website.yuchen.model.User;
import java.util.List;

public interface UserMapper {
    public User whereAndIfDemo(
        @Param("namePattern") String namePattern,
        @Param("age") Integer age,
        @Param("gid") Integer gid
    ) throws Exception;

    public User chooseDemo(
        @Param("namePattern") String namePattern,
        @Param("age") Integer age
    ) throws Exception;

    public User foreachDemo(Integer[] arr) throws Exception;
}

tips:

  • @Param 将方法的参数绑定另一个名字,用于接下来的配置文件中 #{param} 的定位,一般用于多参数
  • 当只有一个方法只有一个Bean参数时,配置文件里 #{param} 名字可以随意起
  • 也可以将参数放在一个 Java Bean 中,#{param} 会配对参数对象属性的名字


<?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="website.yuchen.mapper.UserMapper">

    <select id="whereAndIfDemo" resultType="User">
        select * from User
        <where>
            <if test="namePattern != ''">name like #{namePattern}</if>
            <if test="age != null">and age=#{age}</if>
            <if test="gid != null">and gid=#{gid}</if>
        </where>
    </select>

    <select id="chooseDemo" resultType="User">
        select * from User where gid=1
        <choose>
            <when test="namePattern != ''">and name like #{namePattern}</when>
            <when test="age != null">and age=#{age}</when>
            <otherwise> and id=4</otherwise>
        </choose>
    </select>

    <select id="foreachDemo" resultType="User">
        select * from User where id in 
          <foreach collection="array" index="index" item="item"
            open="(" separator="," close=")">
              #{item}
          </foreach>
    </select>

</mapper>
  • where 标签主要是用来简化条件判断,能智能的处理 and / or , 不必担心多余导致语法错误
  • if 标签用于简单的条件判断
  • choose-when-otherwise 标签作用类似 switch-case-default
  • foreach 标签主要用于 in 语句查询
    • collection 子标签分为 list / array
    • index / item 子标签为下标和对应的值
    • open / close / separator 子标签分别为起止标识及分隔标识


主函数测试:

package website.yuchen;

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 website.yuchen.mapper.UserMapper;
import website.yuchen.model.User;
import java.io.Reader;


public class Main
{
    private static SqlSessionFactory ssfactory;
    private static Reader reader;

    static {    // 预加载配置文件
        try {
            reader = Resources.getResourceAsReader("MybatisConfig.xml");
            ssfactory = new SqlSessionFactoryBuilder().build(reader);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args)
    {
        SqlSession ss = ssfactory.openSession();
        UserMapper mapper = ss.getMapper(UserMapper.class);
        User user = null;

        try {
            System.out.println("\nwhere and if test: ");
            user = mapper.whereAndIfDemo("", null, 2);
            System.out.println("name: " + user.getName());

            System.out.println("\nchoose test: ");
            user = mapper.chooseDemo("", null);
            System.out.println("name: " + user.getName());

            System.out.println("\nforeach test: ");
            user = mapper.foreachDemo(new Integer[]{3, 6, 9});
            System.out.println("name: " + user.getName());

            ss.commit();
        }
        catch(Exception e) {
            ss.rollback();
            e.printStackTrace();
        }
        finally {ss.close();}
    }
}

/*输出:

where and if test: 
name: CCC

choose test: 
name: ABC

foreach test: 
name: CCC*/



参考

MyBatis教程
MyBatis 框架基础入门

-------------本文结束-------------