Mybatis教程

郎家岭伯爵 2022年01月07日 370次浏览

准备数据

创建数据库

CREATE DATABASE ssmdemo;

新建数据表

DROP TABLE IF EXISTS tb_user;
CREATE TABLE tb_user (
id int NOT NULL PRIMARY KEY  AUTO_INCREMENT,
user_name varchar(32) DEFAULT NULL,
password varchar(32) DEFAULT NULL,
name varchar(32) DEFAULT NULL,
age int(10) DEFAULT NULL,
sex int(2) DEFAULT NULL,
birthday date DEFAULT NULL,
created datetime DEFAULT NULL,
updated datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

INSERT INTO ssmdemo.tb_user ( user_name, password, name, age, sex, birthday, created, updated) VALUES ( 'zpc', '123456', '鹏程', '22', '1', '1990-09-02', sysdate(), sysdate());
INSERT INTO ssmdemo.tb_user ( user_name, password, name, age, sex, birthday, created, updated) VALUES ( 'hj', '123456', '静静', '22', '1', '1993-09-05', sysdate(), sysdate());

Mybatis快速入门

引入依赖(pom.xml)

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.27</version>
</dependency>

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.2.8</version>
</dependency>

全局配置文件(mybatis-config.xml)

注:

  • 此文件应创建在resources/mybatis-config.xml目录。

  • mybatis-config.xml文件应指明mapper文件位置

<?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>
        <property name="driver" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/ssmdemo?useUnicode=true&amp;characterEncoding=utf-8&amp;allowMultiQueries=true"/>
        <property name="username" value="root"/>
        <property name="password" value=""/>
    </properties>

    <!-- 环境,可以配置多个,default:指定采用哪个环境 -->
    <environments default="test">
        <!-- id:唯一标识 -->
        <environment id="test">
            <!-- 事务管理器,JDBC类型的事务管理器 -->
            <transactionManager type="JDBC" />
            <!-- 数据源,池类型的数据源 -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/ssmdemo" />
                <property name="username" value="root" />
                <property name="password" value="" />
            </dataSource>
        </environment>
        <environment id="development">
            <!-- 事务管理器,JDBC类型的事务管理器 -->
            <transactionManager type="JDBC" />
            <!-- 数据源,池类型的数据源 -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" /> <!-- 配置了properties,所以可以直接引用 -->
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/ssmdemo" />
                <property name="username" value="root" />
                <property name="password" value="" />
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <!--此处注意要对应自己mapper文件的路径,否则会报错~-->
        <mapper resource="mappers/MyMapper.xml" />
    </mappers>
</configuration>

配置Map.xml(MyMapper.xml)

注:

  • 此文件应建在resources/mappers/MyMapper.xml目录。

  • MyMapper.xml文件应注意resultType应指明User.java的路径。

<?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:命名空间,随便写,一般保证命名空间唯一 -->
<mapper namespace="MyMapper">
    <!-- statement,内容:sql语句。id:唯一标识,随便写,在同一个命名空间下保持唯一
       resultType:sql语句查询结果集的封装类型,tb_user即为数据库中的表
     -->

    <!--resultType的路径要与项目文件的路径保持一致,否则同样会报错!!-->
    <select id="selectUser" resultType="com.langjialing.mybatis.User">
        select * from tb_user where id = #{id}
    </select>
</mapper>

完整代码

MybatisTest.java

package com.langjialing.mybatis;

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 java.io.InputStream;

public class MybatisTest {

    public static void main(String[] args) throws Exception {
        // 指定全局配置文件
        String resource = "mybatis-config.xml";
        // 读取配置文件
        InputStream inputStream = Resources.getResourceAsStream(resource);
        // 构建sqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // 获取sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            // 操作CRUD,第一个参数:指定statement,规则:命名空间+“.”+statementId
            // 第二个参数:指定传入sql的参数:这里是用户id
            User user = sqlSession.selectOne("MyMapper.selectUser", 2);
            System.out.println(user);
        } finally {
            sqlSession.close();
        }
    }

}

User.java

package com.langjialing.mybatis;

import java.text.SimpleDateFormat;
import java.util.Date;

public class User {
    private String id;
    private String userName;
    private String password;
    private String name;
    private Integer age;
    private Integer sex;
    private Date birthday;
    private String created;
    private String updated;

    public String getId() {
        return id;
    }

    public void setId(String 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 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;
    }

    public Integer getSex() {
        return sex;
    }

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

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getCreated() {
        return created;
    }

    public void setCreated(String created) {
        this.created = created;
    }

    public String getUpdated() {
        return updated;
    }

    public void setUpdated(String updated) {
        this.updated = updated;
    }

    @Override
    public String toString() {
        return "User{" +
                "id='" + id + '\'' +
                ", userName='" + userName + '\'' +
                ", password='" + password + '\'' +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", sex=" + sex +
                ", birthday='" + new SimpleDateFormat("yyyy-MM-dd").format(birthday) + '\'' +
                ", created='" + created + '\'' +
                ", updated='" + updated + '\'' +
                '}';
    }
}

完整的CRUD

目录结构

创建UserDao接口

package com.langjialing.mybatis.dao;

import com.langjialing.mybatis.pojo.User;
import java.util.List;

public interface UserDao {

    /**
     * 根据id查询用户信息
     *
     * @param id
     * @return
     */
    public User queryUserById(String id);

    /**
     * 查询所有用户信息
     *
     * @return
     */
    public List<User> queryUserAll();

    /**
     * 新增用户
     *
     * @param user
     */
    public void insertUser(User user);

    /**
     * 更新用户信息
     *
     * @param user
     */
    public void updateUser(User user);

    /**
     * 根据id删除用户信息
     *
     * @param id
     */
    public void deleteUser(String id);
}

创建UserDaoImpl

package com.langjialing.mybatis.dao.impl;

import com.langjialing.mybatis.dao.UserDao;
import com.langjialing.mybatis.pojo.User;
import org.apache.ibatis.session.SqlSession;
import java.util.List;

public class UserDaoImpl implements UserDao {
    public SqlSession sqlSession;

    public UserDaoImpl(SqlSession sqlSession) {
        this.sqlSession = sqlSession;
    }

    @Override
    public User queryUserById(String id) {
        return this.sqlSession.selectOne("UserDao.queryUserById", id);
    }

    @Override
    public List<User> queryUserAll() {
        return this.sqlSession.selectList("UserDao.queryUserAll");
    }

    @Override
    public void insertUser(User user) {
        this.sqlSession.insert("UserDao.insertUser", user);
    }

    @Override
    public void updateUser(User user) {
        this.sqlSession.update("UserDao.updateUser", user);
    }

    @Override
    public void deleteUser(String id) {
        this.sqlSession.delete("UserDao.deleteUser", id);
    }

}

编写UserDao对应的UserDaoMapper.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:命名空间,随便写,一般保证命名空间唯一 -->
<mapper namespace="UserDao">
    <!-- statement,内容:sql语句。id:唯一标识,随便写,在同一个命名空间下保持唯一
       resultType:sql语句查询结果集的封装类型,tb_user即为数据库中的表
     -->
    <!--<select id="queryUserById" resultType="com.zpc.mybatis.pojo.User">-->
    <!--select * from tb_user where id = #{id}-->
    <!--</select>-->

    <!--使用别名-->
    <select id="queryUserById" resultType="com.langjialing.mybatis.pojo.User">
        select
            tuser.id as id,
            tuser.user_name as userName,
            tuser.password as password,
            tuser.name as name,
            tuser.age as age,
            tuser.birthday as birthday,
            tuser.sex as sex,
            tuser.created as created,
            tuser.updated as updated
        from
            tb_user tuser
        where tuser.id = #{id};
    </select>

    <select id="queryUserAll" resultType="com.langjialing.mybatis.pojo.User">
        select * from tb_user;
    </select>

    <!--插入数据-->
    <insert id="insertUser" parameterType="com.langjialing.mybatis.pojo.User">
        INSERT INTO tb_user (
            user_name,
            password,
            name,
            age,
            sex,
            birthday,
            created,
            updated
        )
        VALUES
            (
                #{userName},
                #{password},
                #{name},
                #{age},
                #{sex},
                #{birthday},
                now(),
                now()
            );
    </insert>

    <update id="updateUser" parameterType="com.langjialing.mybatis.pojo.User">
        UPDATE tb_user
        <trim prefix="set" suffixOverrides=",">
            <if test="userName!=null">user_name = #{userName},</if>
            <if test="password!=null">password = #{password},</if>
            <if test="name!=null">name = #{name},</if>
            <if test="age!=null">age = #{age},</if>
            <if test="sex!=null">sex = #{sex},</if>
            <if test="birthday!=null">birthday = #{birthday},</if>
            updated = now(),
        </trim>
        WHERE
        (id = #{id});
    </update>

    <delete id="deleteUser">
        delete from tb_user where id=#{id}
    </delete>
</mapper>

注:

  • 需要在mybatis-config.xml中添加配置:
<mappers>
    <mapper resource="mappers/MyMapper.xml"/>
    <mapper resource="mappers/UserDaoMapper.xml"/>
</mappers>

添加UserDao的测试用例

Pom文件中添加junit依赖:

<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
</dependency>

按住Alt+Enter,选择create test:

编写UserDao的测试用例代码

package com.langjialing.mybatis.dao;

import com.langjialing.mybatis.dao.UserDao;
import com.langjialing.mybatis.dao.impl.UserDaoImpl;
import com.langjialing.mybatis.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.Before;
import org.junit.Test;
import java.io.InputStream;
import java.util.Date;
import java.util.List;

public class UserDaoTest {

    public UserDao userDao;
    public SqlSession sqlSession;

    @Before
    public void setUp() throws Exception {
        // mybatis-config.xml
        String resource = "mybatis-config.xml";
        // 读取配置文件
        InputStream is = Resources.getResourceAsStream(resource);
        // 构建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        // 获取sqlSession
        sqlSession = sqlSessionFactory.openSession();
        this.userDao = new UserDaoImpl(sqlSession);
    }

    @Test
    public void queryUserById() throws Exception {
        System.out.println(this.userDao.queryUserById("1"));
    }

    @Test
    public void queryUserAll() throws Exception {
        List<User> userList = this.userDao.queryUserAll();
        for (User user : userList) {
            System.out.println(user);
        }
    }

    @Test
    public void insertUser() throws Exception {
        User user = new User();
        user.setAge(16);
        user.setBirthday(new Date("1990/09/02"));
        user.setName("大鹏");
        user.setPassword("123456");
        user.setSex(1);
        user.setUserName("evan");
        this.userDao.insertUser(user);
        this.sqlSession.commit();
    }

    @Test
    public void updateUser() throws Exception {
        User user = new User();
        user.setBirthday(new Date());
        user.setName("静鹏");
        user.setPassword("654321");
        user.setSex(1);
        user.setUserName("evanjin");
        user.setId("1");
        this.userDao.updateUser(user);
        this.sqlSession.commit();
    }

    @Test
    public void deleteUser() throws Exception {
        this.userDao.deleteUser("3");
        this.sqlSession.commit();
    }

}

动态代理Mapper实现类(官方推荐的方式)

思考:能否只写接口,不写实现类。只编写接口和Mapper.xml即可?

因为在dao(mapper)的实现类中对sqlsession的使用方式很类似。因此mybatis提供了接口的动态代理。

目录结构

创建UserMapper接口(对应原UserDao)

package com.langjialing.mybatis.dao;

import com.langjialing.mybatis.pojo.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface UserMapper {

    /**
     * 登录(直接使用注解指定传入参数名称)
     * @param userName
     * @param password
     * @return
     */
    public User login(@Param("userName") String userName, @Param("password") String password);

    /**
     * 根据表名查询用户信息(直接使用注解指定传入参数名称)
     * @param tableName
     * @return
     */
    public List<User> queryUserByTableName(@Param("tableName") String tableName);

    /**
     * 根据Id查询用户信息
     * @param id
     * @return
     */
    public User queryUserById(int id);

    /**
     * 查询所有用户信息
     * @return
     */
    public List<User> queryUserAll();

    /**
     * 新增用户信息
     * @param user
     */
    public void insertUser(User user);

    /**
     * 根据id更新用户信息
     * @param user
     */
    public void updateUser(User user);

    /**
     * 根据id删除用户信息
     * @param id
     */
    public void deleteUserById(int id);
}

创建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:命名空间,随便写,一般保证命名空间唯一 ,为了使用接口动态代理,这里必须是接口的全路径名-->
<mapper namespace="com.langjialing.mybatis.dao.UserMapper">
    <!--
       1.#{},预编译的方式preparedstatement,使用占位符替换,防止sql注入,一个参数的时候,任意参数名可以接收
       2.${},普通的Statement,字符串直接拼接,不可以防止sql注入,一个参数的时候,必须使用${value}接收参数
     -->
    <select id="queryUserByTableName" resultType="com.langjialing.mybatis.pojo.User">
        select * from ${tableName}
    </select>

    <select id="login" resultType="com.langjialing.mybatis.pojo.User">
        select * from tb_user where user_name = #{userName} and password = #{password}
    </select>

    <!-- statement,内容:sql语句。
       id:唯一标识,随便写,在同一个命名空间下保持唯一,使用动态代理之后要求和方法名保持一致
       resultType:sql语句查询结果集的封装类型,使用动态代理之后和方法的返回类型一致;resultMap:二选一
       parameterType:参数的类型,使用动态代理之后和方法的参数类型一致
     -->
    <select id="queryUserById" resultType="com.langjialing.mybatis.pojo.User">
        select * from tb_user where id = #{id}
    </select>
    <select id="queryUserAll" resultType="com.langjialing.mybatis.pojo.User">
        select * from tb_user
    </select>
    <!-- 新增的Statement
       id:唯一标识,随便写,在同一个命名空间下保持唯一,使用动态代理之后要求和方法名保持一致
       parameterType:参数的类型,使用动态代理之后和方法的参数类型一致
       useGeneratedKeys:开启主键回写
       keyColumn:指定数据库的主键
       keyProperty:主键对应的pojo属性名
     -->
    <insert id="insertUser" useGeneratedKeys="true" keyColumn="id" keyProperty="id"
            parameterType="com.langjialing.mybatis.pojo.User">
        INSERT INTO tb_user (
            id,
            user_name,
            password,
            name,
            age,
            sex,
            birthday,
            created,
            updated
        )
        VALUES
            (
                null,
                #{userName},
                #{password},
                #{name},
                #{age},
                #{sex},
                #{birthday},
                NOW(),
                NOW()
            );
    </insert>
    <!--
       更新的statement
       id:唯一标识,随便写,在同一个命名空间下保持唯一,使用动态代理之后要求和方法名保持一致
       parameterType:参数的类型,使用动态代理之后和方法的参数类型一致
     -->
    <update id="updateUser" parameterType="com.langjialing.mybatis.pojo.User">
        UPDATE tb_user
        <trim prefix="set" suffixOverrides=",">
            <if test="userName!=null">user_name = #{userName},</if>
            <if test="password!=null">password = #{password},</if>
            <if test="name!=null">name = #{name},</if>
            <if test="age!=null">age = #{age},</if>
            <if test="sex!=null">sex = #{sex},</if>
            <if test="birthday!=null">birthday = #{birthday},</if>
            updated = now(),
        </trim>
        WHERE
        (id = #{id});
    </update>
    <!--
       删除的statement
       id:唯一标识,随便写,在同一个命名空间下保持唯一,使用动态代理之后要求和方法名保持一致
       parameterType:参数的类型,使用动态代理之后和方法的参数类型一致
     -->
    <delete id="deleteUserById" parameterType="java.lang.String">
        delete from tb_user where id=#{id}
    </delete>
</mapper>

全局配置文件mybatis-config.xml引入UserMapper.xml

<mappers>
   <!--此处注意要对应自己mapper文件的路径,否则会报错~-->
   <mapper resource="mappers/MyMapper.xml" />
   <mapper resource="mappers/UserDaoMapper.xml"/>
   <mapper resource="mappers/UserMapper.xml"/>
</mappers>

创建UserMapper测试用例

package com.langjialing.mybatis.dao;

import com.langjialing.mybatis.dao.UserMapper;
import com.langjialing.mybatis.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.Before;
import org.junit.Test;

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

public class UserMapperTest {

    public UserMapper userMapper;

    @Before
    public void setUp() throws Exception {
        // 指定配置文件
        String resource = "mybatis-config.xml";
        // 读取配置文件
        InputStream inputStream = Resources.getResourceAsStream(resource);
        // 构建sqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // 获取sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        // 1. 映射文件的命名空间(namespace)必须是mapper接口的全路径
        // 2. 映射文件的statement的id必须和mapper接口的方法名保持一致
        // 3. Statement的resultType必须和mapper接口方法的返回类型一致
        // 4. statement的parameterType必须和mapper接口方法的参数类型一致(不一定)
        this.userMapper = sqlSession.getMapper(UserMapper.class);
    }

    @Test
    public void testQueryUserByTableName() {
        List<User> userList = this.userMapper.queryUserByTableName("tb_user");
        for (User user : userList) {
            System.out.println(user);
        }
    }

    @Test
    public void testLogin() {
        System.out.println(this.userMapper.login("hj", "123456"));
    }

    @Test
    public void testQueryUserById() {
        System.out.println(this.userMapper.queryUserById(1));
    }

    @Test
    public void testQueryUserAll() {
        List<User> userList = this.userMapper.queryUserAll();
        for (User user : userList) {
            System.out.println(user);
        }
    }

    @Test
    public void testInsertUser() {
        User user = new User();
        user.setAge(20);
        user.setBirthday(new Date());
        user.setName("大神");
        user.setPassword("123456");
        user.setSex(2);
        user.setUserName("bigGod222");
        this.userMapper.insertUser(user);
        System.out.println(user.getId());
    }

    @Test
    public void testUpdateUser() {
        User user = new User();
        user.setBirthday(new Date());
        user.setName("静静");
        user.setPassword("123456");
        user.setSex(0);
        user.setUserName("Jinjin");
        user.setId("1");
        this.userMapper.updateUser(user);
    }

    @Test
    public void testDeleteUserById() {
        this.userMapper.deleteUserById(1);
    }
}

动态代理总结

使用mapper接口不用写接口实现类即可完成数据库操作,使用非常简单,也是官方所推荐的使用方法。

使用mapper接口的必须具备以几个条件:

  1. Mapper的namespace必须和mapper接口的全路径一致。
  2. Mapper接口的方法名必须和sql定义的id一致。
  3. Mapper接口中方法的输入参数类型必须和sql定义的parameterType一致(不一定)。
  4. Mapper接口中方法的输出参数类型必须和sql定义的resultType一致。
捐赠页面示例