文章目录
springboot数据库开发的流程:
- 导入数据库开发场景spring-boot-starter-data-XXX
- 场景会自动导入配置类XXXAutoConfiguration
- 自动配置类会导入组件,最重要的组件是DataSource数据源
- 数据源的信息是和XXXProperties里面的信息绑定
我们要关注的是首尾两步,中间的流程springboot帮我们做好了。
1.SQL
1.1数据源的自动配置-HikariDataSource
HikariDataSource是springboot默认使用的数据源
1.1.1导入JDBC场景
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId> spring-boot-starter-data-jdbc</artifactId>
</dependency>
我们可以看到官方引入了数据源、事务,但是官方没有导入数据库驱动,因为官方不知道用户用的什么数据库,对应要导入什么数据库的驱动。但是springboot在dependence里面仲裁了数据库驱动的版本,而我们的数据库版本要跟驱动版本一致。
官方为我们设置好的数据库版本:
安装的数据库版本是5:
修改数据库版本的两种方式
1.直接引入依赖具体版本(maven就近依赖原则)
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.49</version>
</dependency>
2.因为版本是一个properties,可以重新声明版本(利用maven属性的就近优先原则)
<properties>
<java.version>1.8</java.version>
<mysql.version>5.1.49</mysql.version>
</properties>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
1.1.2分析自动配置
DataSourceAutoConfiguration数据源的自动配置
- 绑定的前缀prefix = “spring.datasource”
- 数据库的连接池是容器中没有DataSource才自动配置的
- Hikari是默认配置好的连接池
@Configuration(proxyBeanMethods = false)
@Conditional(PooledDataSourceCondition.class)
@ConditionalOnMissingBean({ DataSource.class, XADataSource.class })
@Import({ DataSourceConfiguration.Hikari.class, DataSourceConfiguration.Tomcat.class,
DataSourceConfiguration.Dbcp2.class, DataSourceConfiguration.OracleUcp.class,
DataSourceConfiguration.Generic.class, DataSourceJmxConfiguration.class })
protected static class PooledDataSourceConfiguration {
}
- DataSourceTransactionManagerAutoConfiguration: 事务管理器的自动配置
- JdbcTemplateAutoConfiguration: JdbcTemplate的自动配置,可以来对数据库进行crud
可以修改这个配置项@ConfigurationProperties(prefix = “spring.jdbc”) 来修改JdbcTemplate
@Bean@Primary JdbcTemplate;容器中有这个组件 - JndiDataSourceAutoConfiguration: jndi的自动配置
- XADataSourceAutoConfiguration: 分布式事务相关的
1.1.3修改配置项
spring:
datasource:
url: jdbc:mysql://localhost:3306/db_account
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
1.1.4测试
@Slf4j
@SpringBootTest
class Boot05WebAdminApplicationTests {
@Autowired
JdbcTemplate jdbcTemplate;
@Test
void contextLoads() {
Long aLong = jdbcTemplate.queryForObject("select count(*) from user", Long.class);
log.info("记录总数:{}",aLong);
}
}
1.2使用Druid数据源
1.2.1druid官方github地址
druid官方github地址
1druid是第三方技术,整合第三方技术的两种方式
• 自定义
• 找starter
1.2.2自定义方式
1、创建数据源
引入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.17</version>
</dependency>
没有用springboot的时候,可在pom.xml里配置
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.17</version>
</dependency>
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
destroy-method="close">
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<property name="maxActive" value="20" />
<property name="initialSize" value="1" />
<property name="maxWait" value="60000" />
<property name="minIdle" value="1" />
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<property name="minEvictableIdleTimeMillis" value="300000" />
<property name="testWhileIdle" value="true" />
<property name="testOnBorrow" value="false" />
<property name="testOnReturn" value="false" />
<property name="poolPreparedStatements" value="true" />
<property name="maxOpenPreparedStatements" value="20" />
引入springboot后可以写一个自己的配置类MyDataSourceConfig
@Configuration
public class MyDataSourceConfig {
// 创建一个数据源
// DataSourceConfiguration.class里,没有@ConditionalOnMissingBean(DataSource.class),才会配置HikariDataSource
@Bean
@ConfigurationProperties("spring.datasource") // 跟yml文件绑定
public DataSource dataSource(){
DruidDataSource druidDataSource = new DruidDataSource();
// druidDataSource.setUrl();
// druidDataSource.setUsername();
return druidDataSource;
}
}
dataSource.getClass() dataSource的类型为class com.alibaba.druid.pool.DruidDataSource
2、StatViewServlet 使用Druid的内置监控页面
// 使用Druid的内置监控页面
// 给容器中注入一个statViewServlet
@Bean
public ServletRegistrationBean statViewServlet(){
StatViewServlet statViewServlet = new StatViewServlet();
ServletRegistrationBean<StatViewServlet> registrationBean = new ServletRegistrationBean<>(statViewServlet,"/druid/*");
return registrationBean;
}
能进入监控页但是没监控上是因为要开启监控统计功能
3、StatFilter开启监控统计功能
@Bean
@ConfigurationProperties("spring.datasource") // 跟yml文件绑定
public DataSource dataSource() throws SQLException {
DruidDataSource druidDataSource = new DruidDataSource();
// druidDataSource.setUrl();
// druidDataSource.setUsername();
druidDataSource.setFilters("stat"); // 开启监控统计功能
return druidDataSource;
}
1.2.3使用官方starter方式
引入starter
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.17</version>
</dependency>
分析自动配置
扩展配置项 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的配置
private static final String FILTER_STAT_PREFIX = "spring.datasource.druid.filter.stat";
private static final String FILTER_CONFIG_PREFIX = "spring.datasource.druid.filter.config";
private static final String FILTER_ENCODING_PREFIX = "spring.datasource.druid.filter.encoding";
private static final String FILTER_SLF4J_PREFIX = "spring.datasource.druid.filter.slf4j";
private static final String FILTER_LOG4J_PREFIX = "spring.datasource.druid.filter.log4j";
private static final String FILTER_LOG4J2_PREFIX = "spring.datasource.druid.filter.log4j2";
private static final String FILTER_COMMONS_LOG_PREFIX = "spring.datasource.druid.filter.commons-log";
private static final String FILTER_WALL_PREFIX = "spring.datasource.druid.filter.wall";
private static final String FILTER_WALL_CONFIG_PREFIX = "spring.datasource.druid.filter.wall.config";
配置示例
spring:
datasource:
url: jdbc:mysql://localhost:3306/db_account
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
druid:
aop-patterns: com.atguigu.admin.* #监控SpringBean
filters: stat,wall # 底层开启功能,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
1.3整合MyBatis操作
MyBatisgithub地址
starter
SpringBoot官方的Starter:spring-boot-starter-*
第三方的: *-spring-boot-starter
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
1.3.1配置模式
全局配置文件
SqlSessionFactory: 自动配置好了
SqlSession:自动配置了 SqlSessionTemplate 组合了SqlSession
@Import(AutoConfiguredMapperScannerRegistrar.class);会找到所有标注了@Mapper注解的接口
Mapper: 只要我们写的操作MyBatis的接口标准了 @Mapper 就会被自动扫描进来
// MybatisAutoConfiguration.class
@EnableConfigurationProperties({MybatisProperties.class})
@AutoConfigureAfter({DataSourceAutoConfiguration.class, MybatisLanguageDriverAutoConfiguration.class})
public class MybatisAutoConfiguration implements InitializingBean {
}
// MybatisProperties.class
public class MybatisProperties {
public static final String MYBATIS_PREFIX = "mybatis";
}
mybatis需要有一个全局的配置文件
项目结构
<!-- pom.xml -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId> spring-boot-starter-data-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.49</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.17</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
# application.yml
spring:
mvc:
hiddenmethod:
filter:
enabled: false
datasource:
url: jdbc:mysql://XXXX/testData?autoReconnect=true&failOverReadOnly=false
username: root
password: 12345678
driver-class-name: com.mysql.jdbc.Driver
mybatis:
config-location: classpath:mybatis/mybatis-config.xml #全局配置文件位置
mapper-locations: classpath:mybatis/mapper/*.xml #sql映射文件位置
// UserController.java
@RestController
public class UserController {
@Autowired
UserService userService;
@RequestMapping(value = "/user",method = RequestMethod.GET)
public User getUser(){
return userService.getUserById((long) 1);
}
}
// UserService .java
@Service
public class UserService {
@Autowired
UserMapper userMapper;
public User getUserById(Long uid){
return userMapper.getUser(uid);
};
}
// UserMapper.class
@Mapper
public interface UserMapper {
public User getUser(Long uid);
}
<!-- userMapper.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.ll.mapper.UserMapper">
<select id="getUser" resultType="com.ll.bean.User">
select * from user where uid=#{uid}
</select>
</mapper>
<!-- mybatis-config.xml -->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- <!–开启驼峰命名–>
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>-->
</configuration>
配置 private Configuration configuration; mybatis.configuration下面的所有,就是相当于改mybatis全局配置文件中的值
// MybatisProperties.class
public class MybatisProperties {
public static final String MYBATIS_PREFIX = "mybatis";
@NestedConfigurationProperty
private Configuration configuration;
}
// Configuration.class
public class Configuration {
protected Environment environment;
protected boolean safeRowBoundsEnabled;
protected boolean safeResultHandlerEnabled;
protected boolean mapUnderscoreToCamelCase;
protected boolean aggressiveLazyLoading;
protected boolean multipleResultSetsEnabled;
protected boolean useGeneratedKeys;
protected boolean useColumnLabel;
protected boolean cacheEnabled;
protected boolean callSettersOnNulls;
protected boolean useActualParamName;
protected boolean returnInstanceForEmptyRow;
protected boolean shrinkWhitespacesInSql;
......
}
# 配置mybatis规则
mybatis:
# config-location: classpath:mybatis/mybatis-config.xml
mapper-locations: classpath:mybatis/mapper/*.xml
configuration:
map-underscore-to-camel-case: true
# 可以不写全局配置文件,所有全局配置文件的配置都放在configuration配置项中即可
# 全局配置文件和yml只能存在一个
// User.java
@Data
public class User {
private Long uid;
private String uname;
}
总结:
导入mybatis官方starter
编写mapper接口。标注@Mapper注解
编写sql映射文件并绑定mapper接口
在application.yaml中指定Mapper配置文件的位置,以及指定全局配置文件的信息 (建议;配置在mybatis.configuration)