springboot学习笔记13-数据访问-JDBC整合

一、整合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请求:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rSTbEQun-1584205341845)(C:\Users\ouguangji\AppData\Roaming\Typora\typora-user-images\image-20200314152538094.png)]

其他添加、修改、删除等操作都是同理可得。

©️2020 CSDN 皮肤主题: 精致技术 设计师:CSDN官方博客 返回首页