springboot连接mysql数据库

客官°小女子只卖身不卖艺 2022-04-15 04:21 378阅读 0赞

### 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>
            
    		<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>
    		
    		<dependency>
    		    <groupId>org.springframework.boot</groupId>
    		    <artifactId>spring-boot-starter-jdbc</artifactId>
    		</dependency>
    		
    		<dependency>
    		    <groupId>mysql</groupId>
    		    <artifactId>mysql-connector-java</artifactId>
    		</dependency>
    		
    		<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>
            
        	<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.

![20181121151422604.png][]

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

### 8.项目目录结构 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz_size_16_color_FFFFFF_t_70][]

[20181121151422604.png]: /images/20220415/686c2a479a334c7bac9854b7c57b9038.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz_size_16_color_FFFFFF_t_70]: /images/20220415/d65a51491b694f40912c9b3c73fb7361.png