(二)关于SpringBoot的数据访问

一、JDBC方式

  1. 引入starter。

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
  1. 配置application.properties

spring.datasource.driver-class-name=com.mysql.jdbc.cj.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test
spring.datasource.username=root
spring.datasource.password=root
  1. 配置后默认使用org.apache.tomcat.jdbc.pool.DataSource作为数据源;数据源的相关配置都在org.springframework.boot.autoconfigure.jdbc.DataSourceProperties里面。

@ConfigurationProperties(
prefix = "spring.datasource"
)
public class DataSourceProperties implements BeanClassLoaderAware, InitializingBean {
private ClassLoader classLoader;
private String name;
private boolean generateUniqueName;
private Class<? extends DataSource> type;
private String driverClassName;
private String url;
private String username;
private String password;
private String jndiName;
private DataSourceInitializationMode initializationMode;
private String platform;
private List<String> schema;
private String schemaUsername;
private String schemaPassword;
private List<String> data;
private String dataUsername;
private String dataPassword;
private boolean continueOnError;
private String separator;
private Charset sqlScriptEncoding;
private EmbeddedDatabaseConnection embeddedDatabaseConnection;
private DataSourceProperties.Xa xa;
private String uniqueName;
.....
}
  1. 自动配置原理 根据org.springframework.boot.autoconfigure.jdbc.DataSourceConfiguration,根据配置去创建数据源,默认使用tomcat连接池。

  2. SpringBoot默认支持的数据源类型:

    • "com.zaxxer.hikari.HikariDataSource",

    • "org.apache.tomcat.jdbc.pool.DataSource",

    • "org.apache.commons.dbcp2.BasicDataSource"

  3. 可以使用spring.datasource.type指定数据源类型。因为springboot在创建数据源的时候就是根据这个来选择要创建的数据源的类型的。

abstract class DataSourceConfiguration {
DataSourceConfiguration() {
}
protected static <T> T createDataSource(DataSourceProperties properties, Class<? extends DataSource> type) {
return properties.initializeDataSourceBuilder().type(type).build();
}
@Configuration
@ConditionalOnMissingBean({DataSource.class})
@ConditionalOnProperty(
name = {"spring.datasource.type"}
)
static class Generic {
Generic() {
}
@Bean
public DataSource dataSource(DataSourceProperties properties) {
return properties.initializeDataSourceBuilder().build();
}
}
@Configuration
@ConditionalOnClass({BasicDataSource.class})
@ConditionalOnMissingBean({DataSource.class})
@ConditionalOnProperty(
name = {"spring.datasource.type"},
havingValue = "org.apache.commons.dbcp2.BasicDataSource",
matchIfMissing = true
)
static class Dbcp2 {
Dbcp2() {
}
@Bean
@ConfigurationProperties(
prefix = "spring.datasource.dbcp2"
)
public BasicDataSource dataSource(DataSourceProperties properties) {
return (BasicDataSource)DataSourceConfiguration.createDataSource(properties, BasicDataSource.class);
}
}
@Configuration
@ConditionalOnClass({HikariDataSource.class})
@ConditionalOnMissingBean({DataSource.class})
@ConditionalOnProperty(
name = {"spring.datasource.type"},
havingValue = "com.zaxxer.hikari.HikariDataSource",
matchIfMissing = true
)
static class Hikari {
Hikari() {
}
@Bean
@ConfigurationProperties(
prefix = "spring.datasource.hikari"
)
public HikariDataSource dataSource(DataSourceProperties properties) {
HikariDataSource dataSource = (HikariDataSource)DataSourceConfiguration.createDataSource(properties, HikariDataSource.class);
if (StringUtils.hasText(properties.getName())) {
dataSource.setPoolName(properties.getName());
}
return dataSource;
}
}
@Configuration
@ConditionalOnClass({org.apache.tomcat.jdbc.pool.DataSource.class})
@ConditionalOnMissingBean({DataSource.class})
@ConditionalOnProperty(
name = {"spring.datasource.type"},
havingValue = "org.apache.tomcat.jdbc.pool.DataSource",
matchIfMissing = true
)
static class Tomcat {
Tomcat() {
}
@Bean
@ConfigurationProperties(
prefix = "spring.datasource.tomcat"
)
public org.apache.tomcat.jdbc.pool.DataSource dataSource(DataSourceProperties properties) {
org.apache.tomcat.jdbc.pool.DataSource dataSource = (org.apache.tomcat.jdbc.pool.DataSource)DataSourceConfiguration.createDataSource(properties, org.apache.tomcat.jdbc.pool.DataSource.class);
DatabaseDriver databaseDriver = DatabaseDriver.fromJdbcUrl(properties.determineUrl());
String validationQuery = databaseDriver.getValidationQuery();
if (validationQuery != null) {
dataSource.setTestOnBorrow(true);
dataSource.setValidationQuery(validationQuery);
}
return dataSource;
}
}
}
  1. 自定义数据源类型。

@ConditionalOnMissingBean(DataSource.class)
@ConditionalOnProperty(name="spring.datasource.type")
static class Generic{
@Bean
public DataSource dataSource(DataSourceProperties properties){
//使用DataSourceBuilder创建数据源,利用反射创建响应type的数据源,并绑定相关属性
return properties.initializeDataSourceBuilder().build();
}
}
  1. 自动运行建表语句原理

    自动运行建表语句依赖于org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer这个类,在应用启动的时候,会去寻找字段建表语句并运行。

class DataSourceInitializer {
private static final Log logger = LogFactory.getLog(DataSourceInitializer.class);
private final DataSource dataSource;
private final DataSourceProperties properties;
private final ResourceLoader resourceLoader;
DataSourceInitializer(DataSource dataSource, DataSourceProperties properties, ResourceLoader resourceLoader) {
this.dataSource = dataSource;
this.properties = properties;
this.resourceLoader = (ResourceLoader)(resourceLoader != null ? resourceLoader : new DefaultResourceLoader());
}
DataSourceInitializer(DataSource dataSource, DataSourceProperties properties) {
this(dataSource, properties, (ResourceLoader)null);
}
public DataSource getDataSource() {
return this.dataSource;
}
public boolean createSchema() {
List<Resource> scripts = this.getScripts("spring.datasource.schema", this.properties.getSchema(), "schema");
if (!scripts.isEmpty()) {
if (!this.isEnabled()) {
logger.debug("Initialization disabled (not running DDL scripts)");
return false;
}
String username = this.properties.getSchemaUsername();
String password = this.properties.getSchemaPassword();
this.runScripts(scripts, username, password);
}
return !scripts.isEmpty();
}
public void initSchema() {
List<Resource> scripts = this.getScripts("spring.datasource.data", this.properties.getData(), "data");
if (!scripts.isEmpty()) {
if (!this.isEnabled()) {
logger.debug("Initialization disabled (not running data scripts)");
return;
}
String username = this.properties.getDataUsername();
String password = this.properties.getDataPassword();
this.runScripts(scripts, username, password);
}
}
private boolean isEnabled() {
DataSourceInitializationMode mode = this.properties.getInitializationMode();
if (mode == DataSourceInitializationMode.NEVER) {
return false;
} else {
return mode != DataSourceInitializationMode.EMBEDDED || this.isEmbedded();
}
}
private boolean isEmbedded() {
try {
return EmbeddedDatabaseConnection.isEmbedded(this.dataSource);
} catch (Exception var2) {
logger.debug("Could not determine if datasource is embedded", var2);
return false;
}
}
//查找要自动运行的语句
private List<Resource> getScripts(String propertyName, List<String> resources, String fallback) {
if (resources != null) {
return this.getResources(propertyName, resources, true);
} else {
String platform = this.properties.getPlatform();
List<String> fallbackResources = new ArrayList();
fallbackResources.add("classpath*:" + fallback + "-" + platform + ".sql");
fallbackResources.add("classpath*:" + fallback + ".sql");
return this.getResources(propertyName, fallbackResources, false);
}
}
//运行语句
private void runScripts(List<Resource> resources, String username, String password) {
if (!resources.isEmpty()) {
ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
populator.setContinueOnError(this.properties.isContinueOnError());
populator.setSeparator(this.properties.getSeparator());
if (this.properties.getSqlScriptEncoding() != null) {
populator.setSqlScriptEncoding(this.properties.getSqlScriptEncoding().name());
}
Iterator var5 = resources.iterator();
while(var5.hasNext()) {
Resource resource = (Resource)var5.next();
populator.addScript(resource);
}
DataSource dataSource = this.dataSource;
if (StringUtils.hasText(username) && StringUtils.hasText(password)) {
dataSource = DataSourceBuilder.create(this.properties.getClassLoader()).driverClassName(this.properties.determineDriverClassName()).url(this.properties.determineUrl()).username(username).password(password).build();
}
DatabasePopulatorUtils.execute(populator, dataSource);
}
}
}

所以。如果ROM要初始化一些数据库脚本,可以按照规则,将要初始化的数据库脚本命名为schema-*.sql 、data-*.sql这种格式,比如schema.sql,sachema-all.sql等,也可以在配置文件中指定位置。

schema:
- classpath:department.sql

二、整合Druid数据源

  1. 引入druid依赖

<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
</dependency>
  1. 编写druid的配置类

//导入druid数据源
@Configuration
public class DruidConfig {
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource druid(){
return new DruidDataSource();
}
//配置druid管理监控
//1.配置一个管理后台的Servlet
@Bean
public ServletRegistrationBean statViewServlet(){
ServletRegistrationBean bean = new ServletRegistrationBean(
new StatViewServlet(),"/druid/*"
);
Map<String,String> initParams = new HashMap<>();
initParams.put("loginUsername","admin");
initParams.put("loginPassword","123456");
//允许所有访问
initParams.put("allow","");
initParams.put("deny","192.168.12.34");
bean.setInitParameters(initParams);
return bean;
}
//2.配置一个web监控的filter
@Bean
public FilterRegistrationBean webStateFilter(){
FilterRegistrationBean bean = new FilterRegistrationBean();
bean.setFilter(new WebStatFilter());
Map<String,String> initParams = new HashMap<>();
initParams.put("exclusions","*.js,*.css,/druid/*");
bean.setInitParameters(initParams);
bean.setUrlPatterns(Arrays.asList("/*"));
return bean;
}
}

三、整合Mybatis

  1. 引入mybatis依赖

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-mybaties</artifactId>
</dependency>
  1. 配置数据源属性(同上)

  2. 自定义MyBatis的配置规则 要想自定义mybatis的匹配规则只需要容器中添加一个ConfigurationCustomizer即可。

@Configuration
public class MyBatisConfig {
@Bean
public ConfigurationCustomizer configurationCustomeizer(){
return new ConfigurationCustomizer(){
@Override
public void customize(Configuration configuration){
configuration.setMapUnderscoreToCamelCase(true);
}
};
}
}
  1. 使用MapperScan批量扫描所有Mapper接口

@MapperScan(value="com.desperado.mapper")
@SpringBootApplication
public class ProjectDemoApplication {
public static void main(String[] args) {
SpringApplication.run(ProjectDemoApplication.class, args);
}
}
  1. 使用配置文件扫描

mybatis.config-locations=classpath:mybatis/mybatis-config.xml
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml

四、整合SpringData JPA

image.png
  1. 编写一个实体类和数据表进行映射,并且配置好映射关系。

// 使用JPA注解配置映射关系
@Entity // 标识这是一个JPA的实体类(和数据表映射的类)
@Table(name = "tbl_user") // 指定和数据库对应的表,如果省略默认表名就是类名小写
public class User {
@Id // 标识这是一个主键
@GeneratedValue(strategy = GenerationType.IDENTITY)//指定主键的生成方式
private Integer id;
@Column(name = "name",length = 50) //指定和数据表对应的列
private String name;
@Column // 如果忽略名称,那么需要字段名称和数据表字段名称一致
private String email;
}
  1. 编写一个Dao接口来操作实体类对应的数据表。

// 继承JpaRepository来完成对数据库的操作
public interface UserRepository extends JpaRepository<User,Integer> {
}
  1. 基本配置

# 更新或者创建数据表结构
spring.jpa.hibernate.ddl-auto=update
# 控制台显示sql
spring.jpa.hibernate.show-sql=true