Mybatis实战1--简单增删改查使用

一、Mybatis介绍

MyBatis是一个支持普通SQL查询，存储过程和高级映射的优秀持久层框架。MyBatis消除了几乎所有的JDBC代码和参数的手工设置以及对结果集的检索封装。MyBatis可以使用简单的XML或注解用于配置和原始映射，将接口和Java的POJO（Plain Old Java Objects，普通的Java对象）映射成数据库中的记录。

二、准备环境

1 创建用户表

-- 创建用户表
CREATE TABLE `user_info` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户名',
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '密码',
  `phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '手机',
  `icon` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '头像链接地址',
  `sex` int(5) NOT NULL DEFAULT '3' COMMENT '性别： 1 男，2 女， 3 保密',
  `age` int(5) NOT NULL DEFAULT '0' COMMENT '年龄',
  `birthday` bigint(15) DEFAULT '0' COMMENT '出生年月',
  `create_time` bigint(15) NOT NULL DEFAULT '0' COMMENT '创建时间',
  `update_time` bigint(15) NOT NULL DEFAULT '0' COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `index_name` (`user_name`) USING BTREE COMMENT '用户名唯一索引',
  KEY `index_phone` (`phone`) USING BTREE COMMENT '手机号索引'
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='用户表';

-- 插入数据
INSERT INTO `demo`.`user_info`(`user_name`, `password`, `phone`, `icon`, `sex`, `age`, `birthday`, `create_time`, `update_time`) VALUES ('test01', 'test01', '15810101010', '', 3, 0, 1577460560869, 1577460560869, 1577460560869);

2 添加jar包

        <!--组件引用-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
        </dependency>
        <!--添加spring boot mybatis依赖-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>
        <!--添加数据库连接池依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.21</version>
        </dependency>
        <!--添加数据库依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.18</version>
        </dependency>

三、使用Mybatis查询数据

1.在yml配置文件中添加mybatis配置

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/demo?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
    username: demo
    password: demo
    driver-class-name: com.mysql.cj.jdbc.Driver
    druid:
      initial-size: 5  # 初始化大小
      min-idle: 5  # 最小
      max-active: 100  # 最大
      max-wait: 60000  # 连接超时时间
      time-between-eviction-runs-millis: 60000  # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      min-evictable-idle-time-millis: 300000  # 指定一个空闲连接最少空闲多久后可被清除，单位是毫秒
      validationQuery: select 'x'
      test-while-idle: true  # 当连接空闲时，是否执行连接测试
      test-on-borrow: false  # 当从连接池借用连接时，是否测试该连接
      test-on-return: false  # 在连接归还到连接池时是否测试该连接
      filters: config,wall,stat  # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
      maxOpenPreparedStatements: 20
      connectionProperties: druid.stat.slowSqlMillis=200;druid.stat.logSlowSql=true
      web-stat-filter:
        enabled: true
        url-pattern: /*
        exclusions: /druid/*,*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico
        session-stat-enable: true
        session-stat-max-count: 10
      stat-view-servlet:
        enabled: true
        url-pattern: /druid/*
        reset-enable: true
        login-username: admin
        login-password: admin
      filter:
        slf4j:
          statement-log-enabled: true

#配置mapper xml 文件路径，即在resources目录下创建mapper文件夹
mybatis:
  mapper-locations: /mapper/*.xml

#开启打印sql语句
logging:
  level:
    com.gqlofe.userinfo.dao: debug

2.通过mybatis generator 生成实体类和mapper，以及xml

实体类

@Data
public class UserInfo implements Serializable {
    // 用户id
    private Integer id;

    // 用户名
    private String userName;

    // 密码
    private String password;

    // 手机
    private String phone;

    // 头像链接地址
    private String icon;

    // 性别： 1 男，2 女， 3 保密
    private Integer sex;

    // 年龄
    private Integer age;

    // 出生年月
    private Long birthday;

    // 创建时间
    private Long createTime;

    // 更新时间
    private Long updateTime;

    private static final long serialVersionUID = 1L;

}

生成mapper类，注意 @Mapper 注解是mybatis的框架的注解，需要手动加上

@Mapper
public interface UserInfoMapper {
    int deleteByPrimaryKey(Integer id);

    int insert(UserInfo record);

    int insertSelective(UserInfo record);

    UserInfo selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(UserInfo record);

    int updateByPrimaryKey(UserInfo record);

    UserInfo selectByNamePwd(@Param("userName") String userName, @Param("password") String password);

    UserInfo getUser(UserInfo query);
}

生成的UserInfoMapper.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="com.gqlofe.userinfo.dao.mapper.UserInfoMapper">
  <resultMap id="BaseResultMap" type="com.gqlofe.userinfo.bean.entity.UserInfo">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="user_name" jdbcType="VARCHAR" property="userName" />
    <result column="password" jdbcType="VARCHAR" property="password" />
    <result column="phone" jdbcType="VARCHAR" property="phone" />
    <result column="icon" jdbcType="VARCHAR" property="icon" />
    <result column="sex" jdbcType="INTEGER" property="sex" />
    <result column="age" jdbcType="INTEGER" property="age" />
    <result column="birthday" jdbcType="BIGINT" property="birthday" />
    <result column="create_time" jdbcType="BIGINT" property="createTime" />
    <result column="update_time" jdbcType="BIGINT" property="updateTime" />
  </resultMap>
  <sql id="Base_Column_List">
    id, user_name, password, phone, icon, sex, age, birthday, create_time, update_time
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from user_info
    where id = #{id,jdbcType=INTEGER}
  </select>

  <!--通过用户名和密码查询-->
  <select id="selectByNamePwd" resultType="com.gqlofe.userinfo.bean.entity.UserInfo">
    select
    <include refid="Base_Column_List" />
    from user_info
    where user_name = #{userName} and password = #{password}

  </select>


  <!--通过用户名或手机号查询-->
  <select id="getUser" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from user_info
    where 1 = 1
    <if test="userName">
      and user_name = #{userName}
    </if>
    <if test="phone">
      and phone = #{phone}
    </if>
    and password = #{password}
  </select>


  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from user_info
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.gqlofe.userinfo.bean.entity.UserInfo" keyProperty="id" useGeneratedKeys="true">
    insert into user_info (id, user_name, password, 
      phone, icon, sex, age, 
      birthday, create_time, update_time
      )
    values (#{id,jdbcType=INTEGER}, #{userName,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}, 
      #{phone,jdbcType=VARCHAR}, #{icon,jdbcType=VARCHAR}, #{sex,jdbcType=INTEGER}, #{age,jdbcType=INTEGER}, 
      #{birthday,jdbcType=BIGINT}, #{createTime,jdbcType=BIGINT}, #{updateTime,jdbcType=BIGINT}
      )
  </insert>
  <insert id="insertSelective" parameterType="com.gqlofe.userinfo.bean.entity.UserInfo" useGeneratedKeys="true" keyProperty="id">
    insert into user_info
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        id,
      </if>
      <if test="userName != null">
        user_name,
      </if>
      <if test="password != null">
        password,
      </if>
      <if test="phone != null">
        phone,
      </if>
      <if test="icon != null">
        icon,
      </if>
      <if test="sex != null">
        sex,
      </if>
      <if test="age != null">
        age,
      </if>
      <if test="birthday != null">
        birthday,
      </if>
      <if test="createTime != null">
        create_time,
      </if>
      <if test="updateTime != null">
        update_time,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="id != null">
        #{id,jdbcType=INTEGER},
      </if>
      <if test="userName != null">
        #{userName,jdbcType=VARCHAR},
      </if>
      <if test="password != null">
        #{password,jdbcType=VARCHAR},
      </if>
      <if test="phone != null">
        #{phone,jdbcType=VARCHAR},
      </if>
      <if test="icon != null">
        #{icon,jdbcType=VARCHAR},
      </if>
      <if test="sex != null">
        #{sex,jdbcType=INTEGER},
      </if>
      <if test="age != null">
        #{age,jdbcType=INTEGER},
      </if>
      <if test="birthday != null">
        #{birthday,jdbcType=BIGINT},
      </if>
      <if test="createTime != null">
        #{createTime,jdbcType=BIGINT},
      </if>
      <if test="updateTime != null">
        #{updateTime,jdbcType=BIGINT},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.gqlofe.userinfo.bean.entity.UserInfo">
    update user_info
    <set>
      <if test="userName != null">
        user_name = #{userName,jdbcType=VARCHAR},
      </if>
      <if test="password != null">
        password = #{password,jdbcType=VARCHAR},
      </if>
      <if test="phone != null">
        phone = #{phone,jdbcType=VARCHAR},
      </if>
      <if test="icon != null">
        icon = #{icon,jdbcType=VARCHAR},
      </if>
      <if test="sex != null">
        sex = #{sex,jdbcType=INTEGER},
      </if>
      <if test="age != null">
        age = #{age,jdbcType=INTEGER},
      </if>
      <if test="birthday != null">
        birthday = #{birthday,jdbcType=BIGINT},
      </if>
      <if test="createTime != null">
        create_time = #{createTime,jdbcType=BIGINT},
      </if>
      <if test="updateTime != null">
        update_time = #{updateTime,jdbcType=BIGINT},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.gqlofe.userinfo.bean.entity.UserInfo">
    update user_info
    set user_name = #{userName,jdbcType=VARCHAR},
      password = #{password,jdbcType=VARCHAR},
      phone = #{phone,jdbcType=VARCHAR},
      icon = #{icon,jdbcType=VARCHAR},
      sex = #{sex,jdbcType=INTEGER},
      age = #{age,jdbcType=INTEGER},
      birthday = #{birthday,jdbcType=BIGINT},
      create_time = #{createTime,jdbcType=BIGINT},
      update_time = #{updateTime,jdbcType=BIGINT}
    where id = #{id,jdbcType=INTEGER}
  </update>
</mapper>

3.基本增删改查

1.查询

    @Autowired
    private UserInfoMapper userInfoMapper;

    @Test
    public void selectUserInfo() {

        UserInfo userInfo = userInfoMapper.selectByPrimaryKey(6);
        System.out.println(userInfo);
    }

查询结果：

image.png

可以看到执行的sql语句也打印了，数据也查出来了。

2.插入

    @Test
    public void addUserInfo() {
        long now = System.currentTimeMillis();
        UserInfo userInfo = new UserInfo();
        userInfo.setUserName("test02").setPassword("test02").setPhone("15811111111")
                .setIcon("").setSex(1).setAge(10).setBirthday(now)
                .setCreateTime(now).setUpdateTime(now);
        int insert = userInfoMapper.insert(userInfo);
        System.out.println("insert = " + insert);

查询结果：

image.png

可以看到数据库多了一条记录.

3.更新

    @Test
    public void updateUserInfo() {
        long now = System.currentTimeMillis();
        UserInfo userInfo = new UserInfo();
        userInfo.setId(12).setPhone("15800000000").setUpdateTime(now);
        int i = userInfoMapper.updateByPrimaryKeySelective(userInfo);
        System.out.println("i = " + i);
    }

更新结果：

image.png

4.删除

    @Test
    public void deleteUserInfo() {
        int i = userInfoMapper.deleteByPrimaryKey(12);
        System.out.println("i = " + i);
    }

删除结果：

image.png

可以看到已经被删除了。

4.基于注解的增删改查

使用注解方式，就需要将对应的xml去掉

@Mapper
public interface UserInfoMapper {
    int deleteByPrimaryKey(Integer id);

    @Insert("insert into user_info (id, user_name, password, phone, icon, sex, age, birthday, create_time, update_time)values (#{id}, #{userName}, #{password}, #{phone}, #{icon}, #{sex}, #{age}, #{birthday}, #{createTime}, #{updateTime})")
    int insert(UserInfo record);

    int insertSelective(UserInfo record);

    @Select("select * from user_info where id = #{id}")
    UserInfo selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(UserInfo record);

    int updateByPrimaryKey(UserInfo record);

    UserInfo selectByNamePwd(@Param("userName") String userName, @Param("password") String password);

    UserInfo getUser(UserInfo query);
}

经过测试结果同上。