问答平台（1），MyBatis入门

安装数据库

1
2
- 安装 MySQL Server
- 安装 MySQL Workbench

配置文件

  • my.ini
    1
    2
    3
    4
    5
    6
    7
    8
    9
    [mysql]
    default-character-set=utf8 
    [mysqld]
    port=3307
    basedir=D:\mysql-8.0.15-winx64
    datadir=D:\mysql-8.0.15-winx64\data
    max_connections=20
    character-set-server=utf8
    default-storage-engine=INNODB

配置环境变量

1
D:\mysql-8.0.15-winx64\bin

命令行初始化

1
2
3
4
5
6
7
8
9
10
11
12
cd D:\mysql-8.0.15-winx64\bin
// 初始化
mysqld --initialize --console(记录默认的密码)
// 安装
mysqld install
// 启动
net start mysql
mysql -uroot -p(输入默认的密码)
// 修改密码
alter user root@localhost identified by 'xxx';
exit
 mysql -uroot -p

导入数据库文件

1
2
3
4
create database community;
use community;
source d:/文件目录/sql文件
show tables;

MyBatis

核心组件如下

1
2
3
4
5
- SqlSessionFactory：用于创建SqlSession的工厂类。
- SqlSession：MyBatis的核心组件，用于向数据库执行SQL。
- 主配置文件：XML配置文件，可以对MyBatis的底层行为做出详细的配置。
- Mapper接口：DAO接口，在MyBatis中称之为Mapper。
- Mapper映射器：用于编写SQL，并将SQL和实体类映射的组件，采用XML、注解均可实现。

示例

使用 MyBatis 对用户表进行 CRUD 操作。

  • 引入依赖
    1
    2
    3
    4
    5
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.0.1</version>
    </dependency>
  • 配置文件
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    # DataSourceProperties
    spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
    spring.datasource.url=jdbc:mysql://localhost:3307/community?characterEncoding=utf-8&useSSL=false&serverTimezone=Hongkong
    spring.datasource.username=root
    spring.datasource.password=xxx
    spring.datasource.type=com.zaxxer.hikari.HikariDataSource
    spring.datasource.hikari.maximum-pool-size=15
    spring.datasource.hikari.minimum-idle=5
    spring.datasource.hikari.idle-timeout=30000

    # MybatisProperties
    mybatis.mapper-locations=classpath:mapper/*.xml
    mybatis.type-aliases-package=com.nowcoder.community.entity
    mybatis.configuration.useGeneratedKeys=true
    mybatis.configuration.mapUnderscoreToCamelCase=true

    # logger
    logging.level.com.nowcoder.community=debug
    #logging.file=d:/work/data/nowcoder/community.log
  • entity
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    // User.java
    public class User {

        private int id;
        private String username;
        private String password;
        private String salt;
        private String email;
        private int type;
        private int status;
        private String activationCode;
        private String headerUrl;
        private Date createTime;

        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 String getPassword() {
            return password;
        }

        public void setPassword(String password) {
            this.password = password;
        }

        public String getSalt() {
            return salt;
        }

        public void setSalt(String salt) {
            this.salt = salt;
        }

        public String getEmail() {
            return email;
        }

        public void setEmail(String email) {
            this.email = email;
        }

        public int getType() {
            return type;
        }

        public void setType(int type) {
            this.type = type;
        }

        public int getStatus() {
            return status;
        }

        public void setStatus(int status) {
            this.status = status;
        }

        public String getActivationCode() {
            return activationCode;
        }

        public void setActivationCode(String activationCode) {
            this.activationCode = activationCode;
        }

        public String getHeaderUrl() {
            return headerUrl;
        }

        public void setHeaderUrl(String headerUrl) {
            this.headerUrl = headerUrl;
        }

        public Date getCreateTime() {
            return createTime;
        }

        public void setCreateTime(Date createTime) {
            this.createTime = createTime;
        }

        @Override
        public String toString() {
            return "User{" +
                    "id=" + id +
                    ", username='" + username + '\'' +
                    ", password='" + password + '\'' +
                    ", salt='" + salt + '\'' +
                    ", email='" + email + '\'' +
                    ", type=" + type +
                    ", status=" + status +
                    ", activationCode='" + activationCode + '\'' +
                    ", headerUrl='" + headerUrl + '\'' +
                    ", createTime=" + createTime +
                    '}';
        }
    }
  • dao
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    // UserMapper.java
    @Repository
    public interface UserMapper {

        User selectById(int id);

        User selectByName(String username);

        User selectByEmail(String email);

        int insertUser(User user);

        int updateStatus(int id, int status);

        int updateHeader(int id, String headerUrl);

        int updatePassword(int id, String password);
    }
  • mapper
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    <!-- user-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="com.nowcoder.community.dao.UserMapper">

        <sql id="selectFields">
            id, username, password, salt, email, type, status, activation_code, header_url, create_time
        </sql>

        <sql id="insertFields">
            username, password, salt, email, type, status, activation_code, header_url, create_time
        </sql>

        <select id="selectById" resultType="User">
            select
            <include refid="selectFields"></include>
            from user
            where id = #{id}
        </select>

        <select id="selectByName" resultType="User">
            select
            <include refid="selectFields"></include>
            from user
            where username = #{username}
        </select>

        <select id="selectByEmail" resultType="User">
            select
            <include refid="selectFields"></include>
            from user
            where email = #{email}
        </select>

        <insert id="insertUser" parameterType="User" keyProperty="id">
            insert into user (<include refid="insertFields"></include>)
            values (#{username}, #{password}, #{salt}, #{email}, #{type}, #{status}, #{activationCode}, #{headerUrl},
            #{createTime})
        </insert>

        <update id="updateStatus">
            update user set status = #{status} where id = #{id}
        </update>

        <update id="updateHeader">
            update user set header_url = #{headerUrl} where id = #{id}
        </update>

        <update id="updatePassword">
            update user set password = #{password} where id = #{id}
        </update>

    </mapper>
  • MapperTests
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    @RunWith(SpringRunner.class)
    @SpringBootTest
    @ContextConfiguration(classes = CommunityApplication.class)
    public class MapperTests {

        @Autowired
        private UserMapper userMapper;

        @Test
        public void testSelectUser() {
            User user = userMapper.selectById(101);
            System.out.println(user);

            user = userMapper.selectByName("liubei");
            System.out.println(user);

            user = userMapper.selectByEmail("nowcoder101@sina.com");
            System.out.println(user);
        }

        @Test
        public void testInsertUser() {
            User user = new User();
            user.setUsername("test");
            user.setPassword("123456");
            user.setSalt("abc");
            user.setEmail("test@qq.com");
            user.setHeaderUrl("http://www.nowcoder.com/101.png");
            user.setCreateTime(new Date());

            int rows = userMapper.insertUser(user);
            System.out.println(rows);
            System.out.println(user.getId());
        }

        @Test
        public void updateUser() {
            int rows = userMapper.updateStatus(150, 1);
            System.out.println(rows);

            rows = userMapper.updateHeader(150, "ttp://www.nowcoder.com/102.png");
            System.out.println(rows);

            rows = userMapper.updatePassword(150, "hello");
            System.out.println(rows);
        }
    }

参考资料

#问答平台
问答平台（1），MyBatis入门
https://lcf163.github.io/2020/04/13/问答平台（1），MyBatis入门/
作者
乘风的小站
发布于
2020年4月13日
许可协议