Spring Boot之——整合数据库

数据源自动配置-HikariDataSource

目的:能够使用SpringBoot操作MySQL实现 CRUD 操作

步骤:

  1. 引入jdbc场景依赖
  2. 修改配置文件 application.yml
  3. 创建数据库表
  4. 创建对应的实体类
  5. 单元测试

实现:

1、新建工程,引入jdbc场景依赖

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
<!--springboot工程需要继承的父工程-->
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.10.RELEASE</version>
</parent>

<dependencies>
<!--web开发的起步依赖 场景启动器依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<!--JDBC-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<!--mysql驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
</dependencies>

2、创建引导类和配置文件 application.yml

1
2
3
4
5
6
7
8
9
10
server:
port: 80

# datasource
spring:
datasource:
url: jdbc:mysql://192.168.200.150:3306/springboot?serverTimezone=UTC
username: root
password: root
driver-class-name: com.mysql.jdbc.Driver

3、创建数据库表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
drop table if exists tb_user;
create table tb_user
(
id bigint primary key auto_increment,
user_name varchar(8) not null,
sex tinyint,
birthday datetime,
created datetime,
modified datetime
);
insert into tb_user
values (1, '赵一伤', 0, '2000-1-1', now(), now()),
(2, '钱二败', 0, '2000-1-1', now(), now()),
(3, '孙三毁', 0, '2000-1-1', now(), now()),
(4, '李四摧', 0, '2000-1-1', now(), now()),
(5, '周五输', 0, '2000-1-1', now(), now()),
(6, '吴六破', 0, '2000-1-1', now(), now()),
(7, '郑七灭', 0, '2000-1-1', now(), now()),
(8, '王八衰', 0, '2000-1-1', now(), now()),
(9, '张无忌', 0, '2010-3-1', now(), now()),
(10, '赵敏', 1, '2010-4-1', now(), now());

4、创建对应的实体类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
package com.itheima.sh.pojo;

import lombok.Data;

import java.time.LocalDate;
import java.util.Date;

@Data
public class User {

private Long id;
private String userName;
private Integer sex;
private LocalDate birthday;
private Date created;
private Date modified;

}

5、单元测试

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
package com.itheima.sh;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.util.List;
import java.util.Map;

@SpringBootTest
public class JdbcTest {

@Autowired
JdbcTemplate jdbcTemplate;

@Autowired
DataSource dataSource;


@Test
public void testFindAll() throws Exception{

// HikariDataSource (HikariPool-1)
System.out.println(dataSource);

List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from tb_user");
for (Map<String, Object> map : maps) {
System.out.println(map);
}
}

}

小结:

jdbc自动化配置帮助我们做了什么?

  • DataSourceAutoConfiguration : 数据源的自动配置

    • 修改数据源相关的配置:spring.datasource

    • 数据库连接池的配置,是自己容器中没有DataSource才自动配置的

    • 底层配置好的连接池是:HikariDataSource

      image-20210503125240967

    • DataSourceTransactionManagerAutoConfiguration: 事务管理器的自动配置
    • JdbcTemplateAutoConfiguration: JdbcTemplate的自动配置,可以来对数据库进行crud

      • 可以修改这个配置项@ConfigurationProperties(prefix = “spring.jdbc”) 来修改JdbcTemplate
      • @Bean@Primary JdbcTemplate;容器中有这个组件
    • JndiDataSourceAutoConfiguration: jndi的自动配置
    • XADataSourceAutoConfiguration: 分布式事务相关的

数据源切换为Druid

目的:能够切换数据库连接池为Druid,并完成相关高级配置

步骤:

  1. 依赖添加第三方Druid starter
  2. 修改配置文件 application.yml

实现:

1、依赖

1
2
3
4
5
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.24</version>
</dependency>

2、配置文件

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
server:
port: 80

# datasource
spring:
datasource:
url: jdbc:mysql://192.168.200.150:3306/springboot?serverTimezone=UTC
username: root
password: root
driver-class-name: com.mysql.jdbc.Driver

druid:
aop-patterns: com.itheima.sh.* #监控SpringBean
filters: stat,wall,slf4j # 底层开启功能,stat(sql监控),wall(防火墙)
stat-view-servlet: # 配置监控页功能
enabled: true
login-username: admin
login-password: admin
resetEnable: false
web-stat-filter: # 监控web
enabled: true
urlPattern: /*
exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'
filter:
stat: # 对上面filters里面的stat的详细配置
slow-sql-millis: 1000
logSlowSql: true
enabled: true
wall:
enabled: true
config:
drop-table-allow: false

参考官网

3、测试上面的测试类

小结:

druid starter为什么需要添加依赖版本?

帮助我们做了哪些自动化配置功能?

  • 扩展配置项 spring.datasource.druid

  • DruidSpringAopConfiguration.class, 监控SpringBean的;配置项:spring.datasource.druid.aop-patterns

  • DruidStatViewServletConfiguration.class, 监控页的配置:spring.datasource.druid.stat-view-servlet;默认开启

  • DruidWebStatFilterConfiguration.class, web监控配置;spring.datasource.druid.web-stat-filter;默认开启

  • DruidFilterConfiguration.class}) 所有Druid自己filter的配置

其他配置:

参考官网介绍:https://github.com/alibaba/druid/wiki/%E5%B8%B8%E8%A7%81%E9%97%AE%E9%A2%98

Druid starter介绍:https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter

整合MyBatis

目的:能独立完成SpringBoot整合Mybatis【重要】

步骤:

  1. 添加依赖

  2. 编写Mapper接口

  3. 在resources下创建 com/itheima/sh/mapper 文件夹

  4. 编写Controller测试/编写单元测试类

实现:

1、添加依赖,官网参考

1
2
3
4
5
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>

2、编写Mapper接口

1
2
3
4
5
6
7
8
9
10
11
package com.itheima.sh.mapper;

import com.itheima.sh.pojo.User;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;
@Mapper
public interface UserMapper {

public List<User> findAll();
}

3、在resources下创建 com/itheima/sh/mapper 文件夹

1
2
3
4
5
6
7
8
9
10
11
<?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.itheima.sh.mapper.UserMapper">

<select id="findAll" resultType="com.itheima.sh.pojo.User">
select * from tb_user
</select>

</mapper>

4、编写Controller测试/编写单元测试类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
package com.itheima.sh.controller;

import com.itheima.sh.mapper.UserMapper;
import com.itheima.sh.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("user")
public class UserController {

@Autowired
UserMapper userMapper;

@GetMapping
public List<User> findAll() {
return userMapper.findAll();
}

}

浏览器测试:http://localhost/user

我们发现 Mybatis的所有的配置是按照我们学习的默认规则,Mybatis starter 帮助我们做成了自动化配置,开发是不是越来越简单了。

  • 全局配置文件

  • SqlSessionFactory: 自动配置好了

  • SqlSession:自动配置了 SqlSessionTemplate 组合了SqlSession

  • @Import(AutoConfiguredMapperScannerRegistrar.class);

  • Mapper: 只要写的操作MyBatis的接口标准**@Mapper 就会自动扫描进来**

完整截图:

image-20210503133354025

那么如果我改变了默认的配置会怎么样呢?

1、破坏Mapper配置文件存放的位置:

resources 路径下创建 mapper 文件夹,并且将UserMapper.xml放在mapper文件夹下:

image-20210503133539262

运行测试 访问浏览器,发现会出异常:

image-20210503133702931

解决方案:在 application.yml 配置文件添加:

1
2
3
mybatis:
mapper-locations: classpath:mapper/**Mapper.xml
# mapper 映射文件位置

2、驼峰命名规范:

数据库表结构:

image-20210503134155676

实体类: private String userName;

发现现象:

image-20210503134307517

解决方案:在 application.yml 配置文件添加:

1
2
3
mybatis:
configuration:
map-underscore-to-camel-case: true

3、省略@Mapper注解:

image-20210503134449972

启动项目,出现异常:

image-20210503134549596

解决方案:在引导类上添加 @MapperScan 注解:

image-20210503134710486

小结:

  • 以上是项目中常用的内容,其他配置可以参考官网查看

整合Mybatis实现分页

目的:能够实现SpringBoot整合Mybatis完成分页功能

步骤:

1、依赖

1
2
3
4
5
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.3.0</version>
</dependency>

2、配置

1
2
3
4
5
pagehelper:
helperDialect: mysql
reasonable: true
supportMethodsArguments: true
params: count=countSql

实现:作业实现分页查询+CRUD

整合Redis

目的:能够独立使用SpringBoot整合Redis

步骤:

  1. 场景(redis)依赖

  2. 配置文件

  3. 配置类实现key和value序列化和反序列化(可选)

  4. 准备Redis

  5. 编写测试类

实现:

1、依赖

1
2
3
4
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>

2、配置文件

1
2
3
4
5
spring:
redis:
host: 192.168.200.150 # 默认localhost
port: 6379 # 默认6379
database: 1 # 默认0

3、配置类

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
package com.itheima.sh.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.redis.connection.RedisConnectionFactory;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.data.redis.serializer.Jackson2JsonRedisSerializer;
import org.springframework.data.redis.serializer.RedisSerializer;

/**
* @Description:
* @Version: V1.0
*/
@Configuration
public class RedisConfig {


@Bean
public RedisSerializer<Object> redisKeySerializer() {
return new Jackson2JsonRedisSerializer<Object>(Object.class);
}

@Bean
public RedisSerializer<Object> redisValueSerializer() {
return new Jackson2JsonRedisSerializer<Object>(Object.class);
}

/**
* RedisTemplate配置
* @param factory
*/
@Bean
public RedisTemplate<String, Object> redisTemplate(RedisConnectionFactory factory
, RedisSerializer<Object> redisKeySerializer, RedisSerializer<Object> redisValueSerializer) {
RedisTemplate<String, Object> redisTemplate = new RedisTemplate<>();
redisTemplate.setConnectionFactory(factory);

redisTemplate.setDefaultSerializer(redisValueSerializer);
redisTemplate.setKeySerializer(redisKeySerializer);
redisTemplate.setHashKeySerializer(redisKeySerializer);
redisTemplate.afterPropertiesSet();
return redisTemplate;
}
}

3、编写测试类(先启动Redis服务)

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
package com.itheima.sh.controller;

import com.itheima.sh.mapper.UserMapper;
import com.itheima.sh.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.Set;

/**
* @Description:
* @Version: V1.0
*/
@RestController
@RequestMapping("user")
public class UserController {

@Autowired
JdbcTemplate jdbcTemplate;

@Autowired
UserMapper userMapper;

@Autowired
RedisTemplate redisTemplate;


@GetMapping("findAll")
public List findAll() {

// String
redisTemplate.opsForValue().set("hello", "world");

// Hash Map
redisTemplate.opsForHash().put("key", "k1", "v1");
redisTemplate.opsForHash().put("key", "k2", "v2");

// set 无序不重复
redisTemplate.opsForSet().add("setkey", 1, 1, 2, 3, 4, 5);

// list 有序可重复
redisTemplate.opsForList().leftPush("lkey", 1);
redisTemplate.opsForList().leftPush("lkey", 1);
redisTemplate.opsForList().leftPush("lkey", 2);
redisTemplate.opsForList().leftPush("lkey", 3);
redisTemplate.opsForList().leftPush("lkey", 4);



// 取值
Object value = redisTemplate.opsForValue().get("hello");
System.out.println(value);

Object k1 = redisTemplate.opsForHash().get("key", "k1");
System.out.println(k1);

Set setkey = redisTemplate.opsForSet().members("setkey");
System.out.println(setkey);

List lkey = redisTemplate.opsForList().range("lkey", 0, -1);
System.out.println(lkey);


//******************
redisTemplate.boundValueOps("bk").set("v1111");

List<User> userList = userMapper.findAll();
return userList;
}
}