一、整合JDBC数据源
1、JDBC:
<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>
如果需要添加版本 也可以对驱动器添加版本。
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
使用yml文件进行配置:
application.yml:
spring:
datasource:
username: root
password: 123456
url: jdbc:mysql://47.97.192.241:3307/jdbcdata?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=false
driver-class-name: com.mysql.jdbc.Driver
测试连接:
@Test
void contextLoads() throws SQLException {
System.out.println("----------------");
System.out.println(dataSource.getClass());
Connection connection = dataSource.getConnection();
System.out.println(connection.toString());
connection.close();
}
连接成功.
效果:
默认使用的是class com.zaxxer.hikari.HikariDataSource作为数据源
数据源相关配置都在com\zaxxer\hikari\HikariConfig.class包下
自动配置原理:
自动配置都放在org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration这个包下。
数据源配置文件为:org.springframework.boot.autoconfigure.jdbc.DataSourceConfiguration
获取配置过程:
在org.springframework.boot.autoconfigure.jdbc.DataSourceConfiguration配置文件下:
拿一个来举例:
@Configuration(proxyBeanMethods = false)
@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 {
@Bean
@ConfigurationProperties(prefix = "spring.datasource.tomcat")
org.apache.tomcat.jdbc.pool.DataSource dataSource(DataSourceProperties properties) {
org.apache.tomcat.jdbc.pool.DataSource dataSource = 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;
}
}
org.apache.tomcat.jdbc.pool.DataSource dataSource = createDataSource(properties,
org.apache.tomcat.jdbc.pool.DataSource.class);
代码意思为,调用createDataSource这个函数,并传入properties对象和tomcat的class类,properties从容器中取出来;
我们来看看createDataSource这个函数:
@SuppressWarnings("unchecked")
protected static <T> T createDataSource(DataSourceProperties properties, Class<? extends DataSource> type) {
return (T) properties.initializeDataSourceBuilder().type(type).build();
}
这个函数使用properties对象调用**initializeDataSourceBuilder.type(type).build()**初始化函数:
@SuppressWarnings("unchecked")
public T build() {
Class<? extends DataSource> type = getType();
DataSource result = BeanUtils.instantiateClass(type);
maybeGetDriverClassName();
bind(result);
return (T) result;
}
build对其properties文件和DataSource对象进行绑定。
并且在DataSourceBuilder这个类中:
@SuppressWarnings("unchecked")
public T build() {
Class<? extends DataSource> type = getType();
DataSource result = BeanUtils.instantiateClass(type);
maybeGetDriverClassName();
bind(result);
return (T) result;
}
private void maybeGetDriverClassName() {
if (!this.properties.containsKey("driverClassName") && this.properties.containsKey("url")) {
String url = this.properties.get("url");
String driverClass = DatabaseDriver.fromJdbcUrl(url).getDriverClassName();
this.properties.put("driverClassName", driverClass);
}
}
private void bind(DataSource result) {
ConfigurationPropertySource source = new MapConfigurationPropertySource(this.properties);
ConfigurationPropertyNameAliases aliases = new ConfigurationPropertyNameAliases();
aliases.addAliases("url", "jdbc-url");
aliases.addAliases("username", "user");
Binder binder = new Binder(source.withAliases(aliases));
binder.bind(ConfigurationPropertyName.EMPTY, Bindable.ofInstance(result));
}
@SuppressWarnings("unchecked")
public <D extends DataSource> DataSourceBuilder<D> type(Class<D> type) {
this.type = type;
return (DataSourceBuilder<D>) this;
}
public DataSourceBuilder<T> url(String url) {
this.properties.put("url", url);
return this;
}
public DataSourceBuilder<T> driverClassName(String driverClassName) {
this.properties.put("driverClassName", driverClassName);
return this;
}
public DataSourceBuilder<T> username(String username) {
this.properties.put("username", username);
return this;
}
public DataSourceBuilder<T> password(String password) {
this.properties.put("password", password);
return this;
}
@SuppressWarnings("unchecked")
public static Class<? extends DataSource> findType(ClassLoader classLoader) {
for (String name : DATA_SOURCE_TYPE_NAMES) {
try {
return (Class<? extends DataSource>) ClassUtils.forName(name, classLoader);
}
catch (Exception ex) {
// Swallow and continue
}
}
return null;
}
private Class<? extends DataSource> getType() {
Class<? extends DataSource> type = (this.type != null) ? this.type : findType(this.classLoader);
if (type != null) {
return type;
}
throw new IllegalStateException("No supported DataSource type found");
}
这个类中,我们可以看到,在这个类中进行了username、password、typedriverNameClass的绑定配置。
经过以上配置,properties配置文件和DataSource就绑定好了,自动配置完毕。
然后我们可以在org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer中看到,sql文件的设置格式:
我们在顶部可以看见:
/**
* Initialize a {@link DataSource} based on a matching {@link DataSourceProperties}
* config.
*
* @author Dave Syer
* @author Phillip Webb
* @author Eddú Meléndez
* @author Stephane Nicoll
* @author Kazuki Shimizu
*/
然后点入DataSourceProperties文件,如果你是反编译文件,那么这里就是文件格式,如果你是源码文件,这里就是我这种形式。
我们来看看这个文件:
这里面设置有以下属性:
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 = DataSourceInitializationMode.EMBEDDED;
我们在这里可以看见有一段注释:
/**
* Platform to use in the DDL or DML scripts (such as schema-${platform}.sql or
* data-${platform}.sql).
*/
这一段注释的意思是我们自己设置的sql文件格式为:schema-xx.sql和data-xx.sql,只有这两种文件格式才能被得到自动加载和使用,其他类型的sql文件无法被加载和使用。
而且我们还可以发现:
/**
* Determine the username to use based on this configuration and the environment.
* @return the username to use
* @since 1.4.0
*/
public String determineUsername() {
if (StringUtils.hasText(this.username)) {
return this.username;
}
if (EmbeddedDatabaseConnection.isEmbedded(determineDriverClassName())) {
return "sa";
}
return null;
}
如果我们不指定username,那么自动配置原理会返回一个sa的用户名进行访问。
/**
* Determine the password to use based on this configuration and the environment.
* @return the password to use
* @since 1.4.0
*/
public String determinePassword() {
if (StringUtils.hasText(this.password)) {
return this.password;
}
if (EmbeddedDatabaseConnection.isEmbedded(determineDriverClassName())) {
return "";
}
return null;
}
如果我们password不指定的话,会自动返回一个空的字符串作为密码。
private List<Resource> getScripts(String propertyName, List<String> resources, String fallback) {
if (resources != null) {
return getResources(propertyName, resources, true);
}
String platform = this.properties.getPlatform();
List<String> fallbackResources = new ArrayList<>();
fallbackResources.add("classpath*:" + fallback + "-" + platform + ".sql");
fallbackResources.add("classpath*:" + fallback + ".sql");
return getResources(propertyName, fallbackResources, false);
}
初始化函数:首先查看有没有创表语句文件,如果有就添加到Resource的list列表对象中,然后从properties文件中得到username、password,然后执行runScripts函数。
void initSchema() {
List<Resource> scripts = getScripts("spring.datasource.data", this.properties.getData(), "data");
if (!scripts.isEmpty()) {
if (!isEnabled()) {
logger.debug("Initialization disabled (not running data scripts)");
return;
}
String username = this.properties.getDataUsername();
String password = this.properties.getDataPassword();
runScripts(scripts, username, password);
}
}
private void runScripts(List<Resource> resources, String username, String password) {
if (resources.isEmpty()) {
return;
}
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());
}
for (Resource resource : resources) {
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);
}
创表函数:根据你给的sql文件,进行创表;fallback就是前面说的schema或者data,schema是创表语句,data是增删改查语句。默认规则是schema-all.sql或者schema-xx.sql或者schema.sql;
private List<Resource> getScripts(String propertyName, List<String> resources, String fallback) {
if (resources != null) {
return getResources(propertyName, resources, true);
}
String platform = this.properties.getPlatform();
List<String> fallbackResources = new ArrayList<>();
fallbackResources.add("classpath*:" + fallback + "-" + platform + ".sql");
fallbackResources.add("classpath*:" + fallback + ".sql");
return getResources(propertyName, fallbackResources, false);
}
但是如果我们要自定义sql文件格式,我们可以在配置文件中设置,使用yml设置为:
spring:
datasource:
username: root
password: 123456
url: jdbc:mysql://47.97.192.241:3307/jdbcdata?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=false
driver-class-name: com.mysql.jdbc.Driver
type: com.mysql.cj.jdbc.MysqlDataSource
initialization-mode: always
schema:
- classpath:sys_config.sql
然后执行。记住对于springboot2.x以上的都需要添加initialization-mode: always设置,并且执行主函数,不是test函数。
注意:自定义的schema下方的classpath:后面不要有空格,不能有空格!!!!
使用JDBC进行实际查询操作:
1、创建Controller:
package com.ogj.demojdbc.Controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.ResponseBody;
import java.util.List;
import java.util.Map;
@Controller
public class HelloController {
@Autowired
JdbcTemplate jdbcTemplate;
@GetMapping("/hello")
@ResponseBody
public Map<String, Object> hello(){
List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from user");
return list.get(0);
}
}
2、前台使用浏览器发出hello请求: