springboot连接mysql数据库

1.创建maven工程，添加依赖包，pom文件内容如下：

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.szcatic</groupId>
  <artifactId>springboot</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>war</packaging>

  <name>springboot</name>
  <url>http://maven.apache.org</url>
    
  	<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.0.RELEASE</version>
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
        <!-- junit5运行所需jar包 -->
		<dependency>
		    <groupId>org.junit.jupiter</groupId>
		    <artifactId>junit-jupiter-engine</artifactId>
		    <scope>test</scope>
		</dependency>
		<dependency>
		    <groupId>org.junit.platform</groupId>
		    <artifactId>junit-platform-runner</artifactId>
		    <scope>test</scope>
		</dependency>
		<!-- spring-boot-starter-jdbc -->
		<dependency>
		    <groupId>org.springframework.boot</groupId>
		    <artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<!-- mysql-connector-java -->
		<dependency>
		    <groupId>mysql</groupId>
		    <artifactId>mysql-connector-java</artifactId>
		</dependency>
		<!-- spring-boot-starter-data-redis -->
		<dependency>
		    <groupId>org.springframework.boot</groupId>
		    <artifactId>spring-boot-starter-data-redis</artifactId>
		</dependency>
    </dependencies>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
        <!-- 指定springboot入口类 -->
    	<start-class>com.szcatic.Application</start-class>
    </properties>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
	                <source>1.8</source>
	                <target>1.8</target>
	            </configuration>
            </plugin>
        </plugins>
    </build>
</project>

2.在文件application.properties中配置数据源

spring.db-product-service.driverClassName = com.mysql.cj.jdbc.Driver
spring.db-product-service.jdbcUrl = jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
spring.db-product-service.username = root
spring.db-product-service.password = 1234
spring.db-product-service.testOnBorrow = true
spring.db-product-service.testWhileIdle = true
spring.db-product-service.timeBetweenEvictionRunsMillis = 60000
spring.db-product-service.minEvictableIdleTimeMillis = 30000
spring.db-product-service.validationQuery = SELECT 1
spring.db-product-service.max-active = 15
spring.db-product-service.max-idle = 10
spring.db-product-service.max-wait = 8000

spring.spring.db-user-ervice.driverClassName = com.mysql.cj.jdbc.Driver
spring.spring.db-user-ervice.jdbcUrl = jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
spring.spring.db-user-ervice.username = root
spring.spring.db-user-ervice.password = 1234
spring.spring.db-user-ervice.testOnBorrow = true
spring.spring.db-user-ervice.testWhileIdle = true
spring.spring.db-user-ervice.timeBetweenEvictionRunsMillis = 60000
spring.spring.db-user-ervice.minEvictableIdleTimeMillis = 30000
spring.spring.db-user-ervice.validationQuery = SELECT 1
spring.spring.db-user-ervice.max-active = 15
spring.spring.db-user-ervice.max-idle = 10
spring.spring.db-user-ervice.max-wait = 8000

3.创建一个Configuration类DatabaseConfig.java，为多个数据源创建DataSource和JdbcTemplate

package com.szcatic.configuration;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;

@Configuration
public class DatabaseConfig {

	@Bean(name = "dbProductService")
	@ConfigurationProperties(prefix = "spring.db-product-service")
	@Primary
	public DataSource createProductServiceDataSource() {
		return DataSourceBuilder.create().build();
	}

	@Bean(name = "dbUserService")
	@ConfigurationProperties(prefix = "spring.db-user-ervice")
	public DataSource createUserServiceDataSource() {
		return DataSourceBuilder.create().build();
	}

	@Bean(name = "jdbcProductService")
	@Autowired
	public JdbcTemplate createJdbcTemplate_ProductService(@Qualifier("dbProductService") DataSource productServiceDS) {
		return new JdbcTemplate(productServiceDS);
	}

	@Bean(name = "jdbcUserService")
	@Autowired
	public JdbcTemplate createJdbcTemplate_UserService(@Qualifier("dbUserService") DataSource userServiceDS) {
		return new JdbcTemplate(userServiceDS);
	}
}

4.创建实体类User.java

package com.szcatic.entity;

import java.io.Serializable;

public class User implements Serializable {
	
	private static final long serialVersionUID = 1L;
	
	private String userName;
	private String password;
	
	public User() {
	}

	public User(String userName, String password) {
		this.userName = userName;
		this.password = password;
	}

	public String getUserName() {
		return userName;
	}

	public void setUserName(String userName) {
		this.userName = userName;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	@Override
	public String toString() {
		return "User [userName=" + userName + ", password=" + password + "]";
	}

}

5.创建控制器类UserController.java

package com.szcatic.controller;

import java.util.List;

import org.springframework.beans.BeanWrapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.szcatic.entity.User;

@RestController
@SpringBootApplication
public class UserController {
	
	@Qualifier("jdbcProductService")
	@Autowired
    private JdbcTemplate jdbcTemplate;
	
	@RequestMapping("/getUser")
	public User getUser() {
		return new User("zhangsan", "1234");
	}
	
	@RequestMapping("/getUsers")
	public List<User> getUsers() {
		String sql = "SELECT f_username AS 'userName', f_password AS 'password' FROM t_user";
		List<User> users = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class) {
			@Override
			protected void initBeanWrapper(BeanWrapper bw) {
				super.initBeanWrapper(bw);
			}
		});
		return users;
	}
	
}

6.创建测试类UserControllerTest.java

package com.szcatic.test;

import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.context.annotation.Configuration;
import org.springframework.test.context.junit.jupiter.SpringExtension;

import com.szcatic.Application;
import com.szcatic.controller.UserController;

@Configuration
@ExtendWith(SpringExtension.class)
@SpringBootTest(classes = Application.class)
public class UserControllerTest {
	
	@Autowired
	private UserController userController;
	
	@Test
	void testGetUsers() {
		System.out.println("userController=====" + userController);
		System.out.println(userController.getUsers());
	}

}

7.运行测试方法testGetUsers()，控制台输出内容如下

15:12:27.590 [main] DEBUG org.springframework.test.context.BootstrapUtils - Instantiating CacheAwareContextLoaderDelegate from class [org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate]
15:12:27.607 [main] DEBUG org.springframework.test.context.BootstrapUtils - Instantiating BootstrapContext using constructor [public org.springframework.test.context.support.DefaultBootstrapContext(java.lang.Class,org.springframework.test.context.CacheAwareContextLoaderDelegate)]
15:12:27.627 [main] DEBUG org.springframework.test.context.BootstrapUtils - Instantiating TestContextBootstrapper for test class [com.szcatic.test.UserControllerTest] from class [org.springframework.boot.test.context.SpringBootTestContextBootstrapper]
15:12:27.639 [main] INFO org.springframework.boot.test.context.SpringBootTestContextBootstrapper - Neither @ContextConfiguration nor @ContextHierarchy found for test class [com.szcatic.test.UserControllerTest], using SpringBootContextLoader
15:12:27.643 [main] DEBUG org.springframework.test.context.support.AbstractContextLoader - Did not detect default resource location for test class [com.szcatic.test.UserControllerTest]: class path resource [com/szcatic/test/UserControllerTest-context.xml] does not exist
15:12:27.643 [main] DEBUG org.springframework.test.context.support.AbstractContextLoader - Did not detect default resource location for test class [com.szcatic.test.UserControllerTest]: class path resource [com/szcatic/test/UserControllerTestContext.groovy] does not exist
15:12:27.644 [main] INFO org.springframework.test.context.support.AbstractContextLoader - Could not detect default resource locations for test class [com.szcatic.test.UserControllerTest]: no resource found for suffixes {-context.xml, Context.groovy}.
15:12:27.703 [main] DEBUG org.springframework.test.context.support.ActiveProfilesUtils - Could not find an 'annotation declaring class' for annotation type [org.springframework.test.context.ActiveProfiles] and class [com.szcatic.test.UserControllerTest]
15:12:27.835 [main] DEBUG org.springframework.boot.test.context.SpringBootTestContextBootstrapper - @TestExecutionListeners is not present for class [com.szcatic.test.UserControllerTest]: using defaults.
15:12:27.836 [main] INFO org.springframework.boot.test.context.SpringBootTestContextBootstrapper - Loaded default TestExecutionListener class names from location [META-INF/spring.factories]: [org.springframework.boot.test.mock.mockito.MockitoTestExecutionListener, org.springframework.boot.test.mock.mockito.ResetMocksTestExecutionListener, org.springframework.boot.test.autoconfigure.restdocs.RestDocsTestExecutionListener, org.springframework.boot.test.autoconfigure.web.client.MockRestServiceServerResetTestExecutionListener, org.springframework.boot.test.autoconfigure.web.servlet.MockMvcPrintOnlyOnFailureTestExecutionListener, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverTestExecutionListener, org.springframework.test.context.web.ServletTestExecutionListener, org.springframework.test.context.support.DirtiesContextBeforeModesTestExecutionListener, org.springframework.test.context.support.DependencyInjectionTestExecutionListener, org.springframework.test.context.support.DirtiesContextTestExecutionListener, org.springframework.test.context.transaction.TransactionalTestExecutionListener, org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener]
15:12:27.856 [main] INFO org.springframework.boot.test.context.SpringBootTestContextBootstrapper - Using TestExecutionListeners: [org.springframework.test.context.web.ServletTestExecutionListener@6236eb5f, org.springframework.test.context.support.DirtiesContextBeforeModesTestExecutionListener@7c1e2a9e, org.springframework.boot.test.mock.mockito.MockitoTestExecutionListener@fa36558, org.springframework.boot.test.autoconfigure.SpringBootDependencyInjectionTestExecutionListener@672872e1, org.springframework.test.context.support.DirtiesContextTestExecutionListener@32910148, org.springframework.test.context.transaction.TransactionalTestExecutionListener@3f56875e, org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener@2b4bac49, org.springframework.boot.test.mock.mockito.ResetMocksTestExecutionListener@fd07cbb, org.springframework.boot.test.autoconfigure.restdocs.RestDocsTestExecutionListener@3571b748, org.springframework.boot.test.autoconfigure.web.client.MockRestServiceServerResetTestExecutionListener@3e96bacf, org.springframework.boot.test.autoconfigure.web.servlet.MockMvcPrintOnlyOnFailureTestExecutionListener@484970b0, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverTestExecutionListener@4470f8a6]
15:12:27.859 [main] DEBUG org.springframework.test.context.support.AbstractDirtiesContextTestExecutionListener - Before test class: context [DefaultTestContext@11c9af63 testClass = UserControllerTest, testInstance = [null], testMethod = [null], testException = [null], mergedContextConfiguration = [WebMergedContextConfiguration@757acd7b testClass = UserControllerTest, locations = '{}', classes = '{class com.szcatic.Application, class com.szcatic.Application}', contextInitializerClasses = '[]', activeProfiles = '{}', propertySourceLocations = '{}', propertySourceProperties = '{org.springframework.boot.test.context.SpringBootTestContextBootstrapper=true}', contextCustomizers = set[org.springframework.boot.test.context.filter.ExcludeFilterContextCustomizer@79ad8b2f, org.springframework.boot.test.json.DuplicateJsonObjectContextCustomizerFactory$DuplicateJsonObjectContextCustomizer@13c10b87, org.springframework.boot.test.mock.mockito.MockitoContextCustomizer@0, org.springframework.boot.test.web.client.TestRestTemplateContextCustomizer@78e94dcf, org.springframework.boot.test.autoconfigure.properties.PropertyMappingContextCustomizer@0, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverContextCustomizerFactory$Customizer@77167fb7], resourceBasePath = 'src/main/webapp', contextLoader = 'org.springframework.boot.test.context.SpringBootContextLoader', parent = [null]], attributes = map['org.springframework.test.context.web.ServletTestExecutionListener.activateListener' -> true]], class annotated with @DirtiesContext [false] with mode [null].
15:12:27.882 [main] DEBUG org.springframework.test.context.support.TestPropertySourceUtils - Adding inlined properties to environment: {spring.jmx.enabled=false, org.springframework.boot.test.context.SpringBootTestContextBootstrapper=true, server.port=-1}

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.1.0.RELEASE)

2018-11-21 15:12:28.252  INFO 11192 --- [           main] com.szcatic.test.UserControllerTest      : Starting UserControllerTest on zsx with PID 11192 (started by admin in F:\workspace4.7\springboot)
2018-11-21 15:12:28.255  INFO 11192 --- [           main] com.szcatic.test.UserControllerTest      : No active profile set, falling back to default profiles: default
2018-11-21 15:12:29.676  INFO 11192 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Multiple Spring Data modules found, entering strict repository configuration mode!
2018-11-21 15:12:29.679  INFO 11192 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data repositories in DEFAULT mode.
2018-11-21 15:12:29.709  INFO 11192 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 17ms. Found 0 repository interfaces.
2018-11-21 15:12:30.200  INFO 11192 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration' of type [org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration$$EnhancerBySpringCGLIB$$4a09f8ae] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2018-11-21 15:12:31.818  INFO 11192 --- [           main] o.s.s.concurrent.ThreadPoolTaskExecutor  : Initializing ExecutorService 'applicationTaskExecutor'
2018-11-21 15:12:33.722  INFO 11192 --- [           main] com.szcatic.test.UserControllerTest      : Started UserControllerTest in 5.832 seconds (JVM running for 6.858)
userController=====com.szcatic.controller.UserController$$EnhancerBySpringCGLIB$$767d12fa@682af059
2018-11-21 15:12:34.211  INFO 11192 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2018-11-21 15:12:34.564  INFO 11192 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
[User [userName=zhangsan, password=1234], User [userName=lisi, password=1234]]
2018-11-21 15:12:34.709  INFO 11192 --- [       Thread-3] o.s.s.concurrent.ThreadPoolTaskExecutor  : Shutting down ExecutorService 'applicationTaskExecutor'
2018-11-21 15:12:34.714  INFO 11192 --- [       Thread-3] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2018-11-21 15:12:34.725  INFO 11192 --- [       Thread-3] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

从输出结果可以看出操作数据库成功

8.项目目录结构