mysql plus crud_Mybatis Plus学习笔记（入门-CRUD）

快速入门

一.创建Spring Initializr工程

1.引入依赖包

org.springframework.boot

spring-boot-starter-jdbc

org.springframework.boot

spring-boot-starter-web

com.baomidou

mybatis-plus-boot-starter

3.3.1.tmp

mysql

mysql-connector-java

runtime

org.projectlombok

lombok

true

org.springframework.boot

spring-boot-starter-test

test

org.junit.vintage

junit-vintage-engine

2.设置配置文件application.yml

server:

#项目端口号

port: 8080

spring:

datasource:

driver-class-name: com.mysql.cj.jdbc.Driver

url: jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=GMT%2B8&useSSL=false

username: root

password: root

dbcp2:

min-idle: 5

initial-size: 5

max-total: 5

max-wait-millis: 60000

mybatis-plus:

configuration:

log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

3.创建user表

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------ Table structure for user-- ----------------------------DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (

`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键',

`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',

`age` int(3) NULL DEFAULT NULL COMMENT '年龄',

`email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '电子邮箱',

`phone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号',

`parent_id` int(10) NULL DEFAULT NULL COMMENT '父id',

`create_time` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ------------------------------ Records of user-- ----------------------------INSERT INTO `user` VALUES (1, '张三', 28, 'zhangsan@163.com', '15001187313', 0, '2020-03-21 17:54:25');

INSERT INTO `user` VALUES (2, '李四', 26, 'lisi@163.com', '15001187354', 1, '2020-03-21 17:55:09');

INSERT INTO `user` VALUES (3, '王五', 34, 'wangwu@sina.com', '15109872381', 1, '2020-03-21 17:55:48');

INSERT INTO `user` VALUES (4, '赵六', 21, 'zhaoliu@hotmail.com', '19902128729', 1, '2020-03-21 17:55:54');

INSERT INTO `user` VALUES (5, '牛七', 15, 'niuqi@gmail.com', '18911219823', 2, '2020-03-21 17:55:58');

INSERT INTO `user` VALUES (6, '马八', 24, 'maba@outlook.com', '16798120912', 2, '2020-03-21 17:56:09');

SET FOREIGN_KEY_CHECKS = 1;

二.入门程序启动类添加mapper扫描：@MapperScan("com.yoni.mq.mapper")

mapper接口：UserMapper extendsBaseMapper

测试程序：

@Autowired

private UserMapper userMapper;

@Test

void firstSelectDemo01() {

List users = userMapper.selectList(null);

users.forEach(System.out::println);

}

---------------------------------控制台日志---------------------------------

Preparing: SELECT id,name,age,email,phone,parent_id,create_time FROM user

Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4584304]

User(id=1, name=张三, age=28, email=zhangsan@163.com, phone=15001187313, parentId=0, createTime=2020-03-21T17:54:25)

User(id=2, name=李四, age=26, email=lisi@163.com, phone=15001187354, parentId=1, createTime=2020-03-21T17:55:09)

User(id=3, name=王五, age=34, email=wangwu@sina.com, phone=15109872381, parentId=1, createTime=2020-03-21T17:55:48)

User(id=4, name=赵六, age=21, email=zhaoliu@hotmail.com, phone=19902128729, parentId=1, createTime=2020-03-21T17:55:54)

User(id=5, name=牛七, age=15, email=niuqi@gmail.com, phone=18911219823, parentId=2, createTime=2020-03-21T17:55:58)

User(id=6, name=马八, age=24, email=maba@outlook.com, phone=16798120912, parentId=2, createTime=2020-03-21T17:56:09)

三.简单CRUD

1.insert

如果主键自增需要添加注解：@TableId(value = "id",type = IdType.AUTO)

@Test

void insert() {

User user = new User();

user.setName("李九");

user.setAge(31);

user.setEmail("lijiu@qq.com");

user.setPhone("13189721234");

user.setCreateTime(LocalDateTime.now());

user.setParentId(2L);

int insert = userMapper.insert(user);

log.info("插入数据量：" + insert);

}

---------------------------------控制台日志---------------------------------

==> Preparing: INSERT INTO user ( name, age, email, phone, parent_id, create_time ) VALUES ( ?, ?, ?, ?, ?, ? )

==> Parameters: 李九(String), 31(Integer), lijiu@qq.com(String), 13189721234(String), 2(Long), 2020-03-21T19:35:07.894(LocalDateTime)

<== Updates: 1

Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@482f7af0]

2020-03-21 19:35:08.968 INFO 99588 --- [ main] com.yoni.mq.MqApplicationTests : 插入数据量：1

2.update

@Test

void update() {

User user = new User();

user.setName("李九九");

QueryWrapper queryWrapper = new QueryWrapper();

queryWrapper.eq("id", 7L);

int update = userMapper.update(user, queryWrapper);

log.info("影响数据量：" + update);

}

---------------------------------控制台日志---------------------------------

==> Preparing: UPDATE user SET name=? WHERE (id = ?)

==> Parameters: 李九九(String), 7(Long)

<== Updates: 1

Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7b8ea1db]

2020-03-21 19:43:38.634 INFO 98320 --- [ main] com.yoni.mq.MqApplicationTests : 影响数据量：1

3.select

@Test

void select() {

User user = new User();

user.setId(7L);

QueryWrapper queryWrapper = new QueryWrapper();

queryWrapper.setEntity(user);

User user1 = userMapper.selectOne(queryWrapper);

log.info("查询参数：" + user1.toString());

}

---------------------------------控制台日志---------------------------------

==> Preparing: SELECT id,name,age,email,phone,parent_id,create_time FROM user WHERE id=?

==> Parameters: 7(Long)

<== Columns: id, name, age, email, phone, parent_id, create_time

<== Row: 7, 李九九, 31, lijiu@qq.com, 13189721234, 2, 2020-03-21 19:35:08

<== Total: 1

查询参数：User(id=7, name=李九九, age=31, email=lijiu@qq.com, phone=13189721234, parentId=2, createTime=2020-03-21T19:35:08)

4.delete

@Test

void delete() {

QueryWrapper queryWrapper = new QueryWrapper();

queryWrapper.eq("id", 7L);

int delete = userMapper.delete(queryWrapper);

log.info("删除记录数：" + delete);

}

---------------------------------控制台日志---------------------------------

==> Preparing: DELETE FROM user WHERE (id = ?)

==> Parameters: 7(Long)

<== Updates: 1

2020-03-21 19:49:12.752 INFO 48444 --- [ main] com.yoni.mq.MqApplicationTests : 删除记录数：1