SpringBoot整合JDBC学习笔记

1.创建工程

2.Linux启动数据库

3.连接数据库

com.mysql.jdbc.Driver过时了，改成新的

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

数据源自动配置原理

数据源的配置都在这

自动配置原理都这

DataSourceConfiguration//数据源的配置

源码
//springboot默认支持的连池：
//org.apache.commons.dbcp2.BasicDataSource
//com.zaxxer.hikari.HikariDataSource
//org.apache.tomcat.jdbc.pool.DataSource
//给容器中加数据源的，根据配置创建数据源，默认使用Hikari连接池；可以使用spring.datasource.type指定自定义的数据源类型；
abstract class DataSourceConfiguration {
	/**
	 * Tomcat Pool DataSource configuration.
	 */
	@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;
		}
	}
	/**
	 * Hikari DataSource configuration.
	 */
	@Configuration(proxyBeanMethods = false)
	//1.如果导入了HikariDataSource
	@ConditionalOnClass(HikariDataSource.class)
	//2.并且spring.datasource.type是com.zaxxer.hikari.HikariDataSource
	@ConditionalOnMissingBean(DataSource.class)
	@ConditionalOnProperty(name = "spring.datasource.type", havingValue = "com.zaxxer.hikari.HikariDataSource",
			matchIfMissing = true)
	static class Hikari {

		@Bean
		@ConfigurationProperties(prefix = "spring.datasource.hikari")
		//3.那么就导入HikariDataSource 
		HikariDataSource dataSource(DataSourceProperties properties) {
			HikariDataSource dataSource = createDataSource(properties, HikariDataSource.class);
			if (StringUtils.hasText(properties.getName())) {
				dataSource.setPoolName(properties.getName());
			}
			return dataSource;
		}
	}
	
	/**
	 * Generic DataSource configuration.
	 * 自定义数据源，如果数据源类型不是以上几种，就创建这个
	 */
	@Configuration(proxyBeanMethods = false)
	@ConditionalOnMissingBean(DataSource.class)
	@ConditionalOnProperty(name = "spring.datasource.type")
	static class Generic {

		@Bean
		DataSource dataSource(DataSourceProperties properties) {
			//使用DataSourceBuilder创建数据源，利用反射创建响应type的数据源，并且绑定相关属性
			return properties.initializeDataSourceBuilder().build();
		}
	}
}

initializeDataSourceBuilder()//使用DataSourceBuilder创建数据源

		源码
		@ConfigurationProperties(prefix = "spring.datasource")
		public class DataSourceProperties implements BeanClassLoaderAware, InitializingBean {
			
			public DataSourceBuilder<?> initializeDataSourceBuilder() {
					//创建数据源
					return DataSourceBuilder.create(getClassLoader()).type(getType()).driverClassName(determineDriverClassName())
							.url(determineUrl()).username(determineUsername()).password(determinePassword());
				}
		}

create()//创建数据源

				源码：
				public final class DataSourceBuilder<T extends DataSource> {
					public static DataSourceBuilder<?> create(ClassLoader classLoader) {
							return new DataSourceBuilder<>(classLoader);
						}
					@SuppressWarnings("unchecked")
					public T build() {
						Class<? extends DataSource> type = getType();
						//BeanUtils工具进行反射创建数据源
						DataSource result = BeanUtils.instantiateClass(type);
						maybeGetDriverClassName();
						//绑定相关的属性
						bind(result);
						return (T) result;
					}
				}

DataSourceAutoConfiguration//数据源的自动配置

源码
@Configuration(proxyBeanMethods = false)
@ConditionalOnClass({ DataSource.class, EmbeddedDatabaseType.class })
@ConditionalOnMissingBean(type = "io.r2dbc.spi.ConnectionFactory")
@EnableConfigurationProperties(DataSourceProperties.class)
@Import({ DataSourcePoolMetadataProvidersConfiguration.class, DataSourceInitializationConfiguration.class })
public class DataSourceAutoConfiguration {
	static class Registrar implements ImportBeanDefinitionRegistrar {

		private static final String BEAN_NAME = "dataSourceInitializerPostProcessor";

		@Override
		public void registerBeanDefinitions(AnnotationMetadata importingClassMetadata,
				BeanDefinitionRegistry registry) {
			if (!registry.containsBeanDefinition(BEAN_NAME)) {
				GenericBeanDefinition beanDefinition = new GenericBeanDefinition();
				beanDefinition.setBeanClass(DataSourceInitializerPostProcessor.class);
				beanDefinition.setRole(BeanDefinition.ROLE_INFRASTRUCTURE);
				// We don't need this one to be post processed otherwise it can cause a
				// cascade of bean instantiation that we would rather avoid.
				beanDefinition.setSynthetic(true);
				registry.registerBeanDefinition(BEAN_NAME, beanDefinition);
			}
		}
	}
}

DataSourceInitializerPostProcessor

	源码：
	class DataSourceInitializerPostProcessor implements BeanPostProcessor, Ordered {
		@Override
		public Object postProcessAfterInitialization(Object bean, String beanName) throws BeansException {
			if (bean instanceof DataSource) {
				// force initialization of this bean as soon as we see a DataSource
				//看到数据源就执行这个方法
				this.beanFactory.getBean(DataSourceInitializerInvoker.class);
			}
			return bean;
		}
	}

DataSourceInitializationConfiguration

	源码：
	@Configuration(proxyBeanMethods = false)
	@Import({ DataSourceInitializerInvoker.class, DataSourceInitializationConfiguration.Registrar.class })
	class DataSourceInitializationConfiguration {
	
	}

DataSourceInitializerInvoker

		源码：
		//ApplicationListener监听器，
		//作用：初始化时可以帮我们运行schema的schema-*.sql，data-*.sql
		class DataSourceInitializerInvoker implements ApplicationListener<DataSourceSchemaCreatedEvent>, InitializingBean {
			@Override
			public void afterPropertiesSet() {
				DataSourceInitializer initializer = getDataSourceInitializer();
				if (initializer != null) {
					//运行建表语句
					boolean schemaCreated = this.dataSourceInitializer.createSchema();
					if (schemaCreated) {
						initialize(initializer);
					}
				}
			}
		
			private void initialize(DataSourceInitializer initializer) {
				try {
					this.applicationContext.publishEvent(new DataSourceSchemaCreatedEvent(initializer.getDataSource()));
					// The listener might not be registered yet, so don't rely on it.
					if (!this.initialized) {
						this.dataSourceInitializer.initSchema();
						this.initialized = true;
					}
				}
				catch (IllegalStateException ex) {
					logger.warn(LogMessage.format("Could not send event to complete DataSource initialization (%s)",
							ex.getMessage()));
				}
			}
			private DataSourceInitializer getDataSourceInitializer() {
				if (this.dataSourceInitializer == null) {
					DataSource ds = this.dataSource.getIfUnique();
					if (ds != null) {
						this.dataSourceInitializer = new DataSourceInitializer(ds, this.properties, this.applicationContext);
					}
				}
				return this.dataSourceInitializer;
			}
			//监听器监听到事件后调用这个方法
			@Override
			public void onApplicationEvent(DataSourceSchemaCreatedEvent event) {
				// NOTE the event can happen more than once and
				// the event datasource is not used here
				DataSourceInitializer initializer = getDataSourceInitializer();
				if (!this.initialized && initializer != null) {
					//运行插入数据的语句
					initializer.initSchema();
					this.initialized = true;
				}
			}
		}

DataSourceInitializer

		源码：
		//运行建表语句
		boolean createSchema() {
			List<Resource> scripts = getScripts("spring.datasource.schema", this.properties.getSchema(), "schema");
			if (!scripts.isEmpty()) {
				if (!isEnabled()) {
					logger.debug("Initialization disabled (not running DDL scripts)");
					return false;
				}
				String username = this.properties.getSchemaUsername();
				String password = this.properties.getSchemaPassword();
				runScripts(scripts, username, password);
			}
			return !scripts.isEmpty();
		}
		/**
		 * Initialize the schema if necessary.
		 * @see DataSourceProperties#getData()
		 * //运行插入数据的语句
		 */
		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);
		}

默认只需要将文件命名为：

schema-*.sql、data-*.sql

建表语句时候

	/**
	 * Create the schema if necessary.
	 * @return {@code true} if the schema was created
	 * @see DataSourceProperties#getSchema()
	 */
	boolean createSchema() {
		//得到这个文件
		List<Resource> scripts = getScripts("spring.datasource.schema", this.properties.getSchema(), "schema");
		if (!scripts.isEmpty()) {
			if (!isEnabled()) {
				logger.debug("Initialization disabled (not running DDL scripts)");
				return false;
			}
			String username = this.properties.getSchemaUsername();
			String password = this.properties.getSchemaPassword();
			runScripts(scripts, username, password);
		}
		return !scripts.isEmpty();
	}
	//可以指定文件路径
	private List<Resource> getScripts(String propertyName, List<String> resources, String fallback) {
		if (resources != null) {
			return getResources(propertyName, resources, true);
		}
		//如果获取不到，就找classpath*:schema-all.sql或classpath*:schema.sql
		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);
	}

platform

		源码：
		/**
		 * Platform to use in the DDL or DML scripts (such as schema-${platform}.sql or
		 * data-${platform}.sql).
		 */
		private String platform = "all";

没建起来

添加initialization-mode: always就好了





可以指定文件

/**
 * {@link EnableAutoConfiguration Auto-configuration} for {@link JdbcTemplate} and
 * {@link NamedParameterJdbcTemplate}.
 *
 * @author Dave Syer
 * @author Phillip Webb
 * @author Stephane Nicoll
 * @author Kazuki Shimizu
 * @since 1.4.0
 */
@Configuration(proxyBeanMethods = false)
@ConditionalOnClass({ DataSource.class, JdbcTemplate.class })
@ConditionalOnSingleCandidate(DataSource.class)
@AutoConfigureAfter(DataSourceAutoConfiguration.class)
@EnableConfigurationProperties(JdbcProperties.class)
//有数据源的情况下，在容器中添加jdbcTemplate和namedParameterJdbcTemplate（具名参数）
@Import({ JdbcTemplateConfiguration.class, NamedParameterJdbcTemplateConfiguration.class })
public class JdbcTemplateAutoConfiguration {

}

JdbcTemplateConfiguration

	源码：
	/**
	 * Configuration for {@link JdbcTemplateConfiguration}.
	 *
	 * @author Stephane Nicoll
	 */
	@Configuration(proxyBeanMethods = false)
	@ConditionalOnMissingBean(JdbcOperations.class)
	class JdbcTemplateConfiguration {
		
		@Bean
		@Primary
		JdbcTemplate jdbcTemplate(DataSource dataSource, JdbcProperties properties) {
			JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
			JdbcProperties.Template template = properties.getTemplate();
			jdbcTemplate.setFetchSize(template.getFetchSize());
			jdbcTemplate.setMaxRows(template.getMaxRows());
			if (template.getQueryTimeout() != null) {
				jdbcTemplate.setQueryTimeout((int) template.getQueryTimeout().getSeconds());
			}
			return jdbcTemplate;
		}
	}

NamedParameterJdbcTemplateConfiguration

	源码：
	/**
	 * Configuration for {@link NamedParameterJdbcTemplate}.
	 *
	 * @author Stephane Nicoll
	 */
	@Configuration(proxyBeanMethods = false)
	@ConditionalOnSingleCandidate(JdbcTemplate.class)
	@ConditionalOnMissingBean(NamedParameterJdbcOperations.class)
	class NamedParameterJdbcTemplateConfiguration {
	
		@Bean
		@Primary
		NamedParameterJdbcTemplate namedParameterJdbcTemplate(JdbcTemplate jdbcTemplate) {
			return new NamedParameterJdbcTemplate(jdbcTemplate);
		}
	}

jdbcTemplate操作数据库

没显示出来页面

java.lang.IndexOutOfBoundsException: Index: 0, Size: 0
	at java.util.ArrayList.rangeCheck(ArrayList.java:653) ~[na:1.8.0_131]
	at java.util.ArrayList.get(ArrayList.java:429) ~[na:1.8.0_131]
	at com.aitiguigu.springbootjdbc.controller.HelloController.query(HelloController.java:26) ~[classes/:na]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_131]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_131]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_131]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_131]
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190) ~[spring-web-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) ~[spring-web-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105) ~[spring-webmvc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:879) ~[spring-webmvc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:793) ~[spring-webmvc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040) ~[spring-webmvc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943) ~[spring-webmvc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898) ~[spring-webmvc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:634) ~[tomcat-embed-core-9.0.35.jar:9.0.35]
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) ~[tomcat-embed-core-9.0.35.jar:9.0.35]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-9.0.35.jar:9.0.35]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.35.jar:9.0.35]
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.35.jar:9.0.35]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.35.jar:9.0.35]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.35.jar:9.0.35]
	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.35.jar:9.0.35]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.35.jar:9.0.35]
	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.35.jar:9.0.35]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.35.jar:9.0.35]
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.35.jar:9.0.35]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.35.jar:9.0.35]
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) ~[tomcat-embed-core-9.0.35.jar:9.0.35]
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [tomcat-embed-core-9.0.35.jar:9.0.35]
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) [tomcat-embed-core-9.0.35.jar:9.0.35]
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) [tomcat-embed-core-9.0.35.jar:9.0.35]
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [tomcat-embed-core-9.0.35.jar:9.0.35]
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) [tomcat-embed-core-9.0.35.jar:9.0.35]
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) [tomcat-embed-core-9.0.35.jar:9.0.35]
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373) [tomcat-embed-core-9.0.35.jar:9.0.35]
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) [tomcat-embed-core-9.0.35.jar:9.0.35]
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868) [tomcat-embed-core-9.0.35.jar:9.0.35]
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1590) [tomcat-embed-core-9.0.35.jar:9.0.35]
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-9.0.35.jar:9.0.35]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_131]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_131]
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.35.jar:9.0.35]
	at java.lang.Thread.run(Thread.java:748) [na:1.8.0_131]

原来项目启动完后，又执行了建表语句，数据库中表的数据没了，需要从新写。或是把建表的删除