一个简单Spring Boot+MyBatis整合详解(3)

Spring Boot+MyBatis使用XML进行SQL设置

在示例项目中，使用XML文件替换注释中设置的部分将如下所示

src/main/resources/data.sql

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '用户名ID' ,
  `username` VARCHAR(50) NOT NULL COMMENT '用户名' ,
  `password` VARCHAR(255) NOT NULL COMMENT '密码' ,
  `email` VARCHAR(100) NOT NULL COMMENT '用户电子邮箱',
PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `username`, `password`, `email`) VALUES
(1, '小红', '123456', 'xiaohong@qq.com'),
(2, '飞燕', 'admin', 'feiyan@sina.com');

--
-- Table structure for table `post`
--

CREATE TABLE `post` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '文章ID',
  `user_id` int(11) NOT NULL COMMENT '所属用户名ID',
  `title` varchar(200) NOT NULL COMMENT '标题',
PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `post`
--

INSERT INTO `post` (`id`, `user_id`, `title`) VALUES
(1, 1, '愿时光能缓，许故人不散'),
(2, 2, '曲声叮咚，光阴悠长'),
(3, 2, '我是，轻轻穿过你指尖的风');

创建数据库mydb，并制定默认的字符集是utf8

mysql> create database mydb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

创建和插入（用户表和文章表）

mysql> use mydb;
Database changed
mysql> CREATE TABLE `users` (
    ->   `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '用户名ID' ,
    ->   `username` VARCHAR(50) NOT NULL COMMENT '用户名' ,
    ->   `password` VARCHAR(255) NOT NULL COMMENT '密码' ,
    ->   `email` VARCHAR(100) NOT NULL COMMENT '用户电子邮箱',
    -> PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO `users` (`id`, `username`, `password`, `email`) VALUES
    -> (1, '小红', '123456', 'xiaohong@qq.com'),
    -> (2, '飞燕', 'admin', 'feiyan@sina.com');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE `post` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '文章ID',
    ->   `user_id` int(11) NOT NULL COMMENT '所属用户名ID',
    ->   `title` varchar(200) NOT NULL COMMENT '标题',
    -> PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO `post` (`id`, `user_id`, `title`) VALUES
    -> (1, 1, '愿时光能缓，许故人不散'),
    -> (2, 2, '曲声叮咚，光阴悠长'),
    -> (3, 2, '我是，轻轻穿过你指尖的风');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

查询

mysql> select * from users;
+----+----------+----------+-----------------+
| id | username | password | email           |
+----+----------+----------+-----------------+
|  1 | 小红     | 123456   | xiaohong@qq.com |
|  2 | 飞燕     | admin    | feiyan@sina.com |
+----+----------+----------+-----------------+
2 rows in set (0.00 sec)

mysql> select * from post;
+----+---------+--------------------------------------+
| id | user_id | title                                |
+----+---------+--------------------------------------+
|  1 |       1 | 愿时光能缓，许故人不散                 |
|  2 |       2 | 曲声叮咚，光阴悠长                    |
|  3 |       2 | 我是，轻轻穿过你指尖的风               |
+----+---------+--------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT *
    -> FROM
    ->  users AS u
    ->  LEFT JOIN post AS p ON u.id = p.user_id;
+----+----------+----------+-----------------+------+---------+--------------------------------------+
| id | username | password | email           | id   | user_id | title                                |
+----+----------+----------+-----------------+------+---------+--------------------------------------+
|  1 | 小红     | 123456   | xiaohong@qq.com |    1 |       1 | 愿时光能缓，许故人不散               |
|  2 | 飞燕     | admin    | feiyan@sina.com |    2 |       2 | 曲声叮咚，光阴悠长                   |
|  2 | 飞燕     | admin    | feiyan@sina.com |    3 |       2 | 我是，轻轻穿过你指尖的风             |
+----+----------+----------+-----------------+------+---------+--------------------------------------+
3 rows in set (0.01 sec)

查询截图

如果增加用户状态(字段名)

mysql> ALTER TABLE `users` ADD `status` TINYINT(2) NOT NULL DEFAULT '0' AFTER `email`;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看用户表

mysql> select * from users;
+----+----------+----------+-----------------+--------+
| id | username | password | email           | status |
+----+----------+----------+-----------------+--------+
|  1 | 小红     | 123456   | xiaohong@qq.com |      0 |
|  2 | 飞燕     | admin    | feiyan@sina.com |      0 |
+----+----------+----------+-----------------+--------+
2 rows in set (0.01 sec)

如果小红的活动状态要改为1

mysql> UPDATE `users` SET `status` = '1' WHERE `users`.`id` = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

查看小红状态是不是1

mysql> select * from users;
+----+----------+----------+-----------------+--------+
| id | username | password | email           | status |
+----+----------+----------+-----------------+--------+
|  1 | 小红     | 123456   | xiaohong@qq.com |      0 |
|  2 | 飞燕     | admin    | feiyan@sina.com |      1 |
+----+----------+----------+-----------------+--------+
2 rows in set (0.00 sec)