SpringBoot的Web开发入门案例9—数据访问

骑猪看日落 2023-05-22 14:25 95阅读 0赞

**SpringBoot的Web开发入门案例9—数据访问**

1.  创建一个springboot项目（打包方式为jar包）：  
    ![java.jpg][]
2.  勾选**Spring Web**选项，勾选**JDBC API**和**MySQL Driver**  
    ![java.jpg][java.jpg 1]
3.  pom文件：

<?xml version="1.0" encoding="UTF-8"?>
    <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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    	<modelVersion>4.0.0</modelVersion>
    	<parent>
    		<groupId>org.springframework.boot</groupId>
    		<artifactId>spring-boot-starter-parent</artifactId>
    		<version>2.2.6.RELEASE</version>
    		<relativePath/> 
    	</parent>
    	<groupId>com.example</groupId>
    	<artifactId>springboot_db</artifactId>
    	<version>0.0.1-SNAPSHOT</version>
    	<name>springboot_db</name>
    	<description>Demo project for Spring Boot</description>
    
    	<properties>
    		<java.version>1.8</java.version>
    	</properties>
    
    	<dependencies>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-jdbc</artifactId>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-web</artifactId>
    		</dependency>
    
    		<dependency>
    			<groupId>mysql</groupId>
    			<artifactId>mysql-connector-java</artifactId>
    			<scope>runtime</scope>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-test</artifactId>
    			<scope>test</scope>
    			<exclusions>
    				<exclusion>
    					<groupId>org.junit.vintage</groupId>
    					<artifactId>junit-vintage-engine</artifactId>
    				</exclusion>
    			</exclusions>
    		</dependency>
    	</dependencies>
    
    	<build>
    		<plugins>
    			<plugin>
    				<groupId>org.springframework.boot</groupId>
    				<artifactId>spring-boot-maven-plugin</artifactId>
    			</plugin>
    		</plugins>
    	</build>
    
    </project>

1.  application.properties

server.port=8088
    spring.datasource.url=jdbc:mysql://localhost:3306/test
    spring.datasource.username=root
    spring.datasource.password=123456
    spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

1.  写测试方法 SpringbootDbApplicationTests：

package com.example.demo;
    
    import java.sql.SQLException;
    
    import javax.sql.DataSource;
    
    import org.junit.jupiter.api.Test;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.test.context.SpringBootTest;
    
    @SpringBootTest
    class SpringbootDbApplicationTests { 
    
    	
    	@Autowired
    	private DataSource ds;
    	
    	@Test
    	public void jdbcTest() throws SQLException { 
    		System.out.println(ds.getConnection());
    		System.out.println(ds.getClass().getName());
    	}
    }

1.  运行 **jdbcTest()** ，报错：

java.sql.SQLException: The server time zone value '�й���׼ʱ��' is unrecognized or represents more than one time zone. 
    You must configure either the server or JDBC driver (via the 'serverTimezone' configuration property) to use a more specifc time zone value if you want to utilize time zone support.
    
    Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value '�й���׼ʱ��' is unrecognized or represents more than one time zone.

1.  修改application.properties文件：

server.port=8088
    spring.datasource.url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC
    spring.datasource.username=root
    spring.datasource.password=123456
    spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

1.  再次运行**jdbcTest()**， 成功，控制台打印：

HikariProxyConnection@797224183 wrapping com.mysql.cj.jdbc.ConnectionImpl@291373d3
    com.zaxxer.hikari.HikariDataSource

所以SpringBoot的默认数据源是：**com.zaxxer.hikari.HikariDataSource**

1.  修改测试方法 SpringbootDbApplicationTests：

package com.example.demo;
    
    import java.sql.SQLException;
    import java.util.List;
    import java.util.Map;
    
    import org.junit.jupiter.api.Test;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.test.context.SpringBootTest;
    import org.springframework.jdbc.core.JdbcTemplate;
    
    @SpringBootTest
    class SpringbootDbApplicationTests { 
    
    	@Autowired
    	private JdbcTemplate jdbcTemplate;
    	
    	
    	@Test
    	public void jdbcTest() throws SQLException { 
    		String sql = "select * from user";
    		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
    		System.out.println(list);
    	}
    }

1.  test数据库中的user表：  
    ![在这里插入图片描述][20200418003533875.png]
2.  运行**jdbcTest**测试方法，控制台打印：

[{id=1, first_name=BLU, last_name=BLU}, {id=2, first_name=BLU1, last_name=spring}, {id=3, first_name=BLU2, last_name=mybatis}]

--------------------

**切换数据源druid**

1.  加入druid依赖：

<dependency>
    	<groupId>com.alibaba</groupId>
    	<artifactId>druid-spring-boot-starter</artifactId>
    	<version>1.1.20</version>
    </dependency>

1.  修改application.properties文件（切换数据源为：**com.alibaba.druid.pool.DruidDataSource**）：

[java.jpg]: /images/20230521/fb3561c117834a5088fedb437b921598.png
[java.jpg 1]: /images/20230521/c6d1b0dd91794389a4bea7bcef27a223.png
[20200418003533875.png]: /images/20230521/0df01027edb941e89130428ecfedc459.png