application.yml文件的配置文件:
spring:
datasource:
username: root
password: 990526
url: jdbc:mysql://localhost:3306/springboot?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
【注意】type: com.alibaba.druid.pool.DruidDataSource
这个代表导入了德鲁伊的数据源,德鲁伊数据源很强大,网上说他是最快的数据源
完整的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.3.0.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.kuang</groupId>
<artifactId>springboot-04-data</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>springboot-04-data</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.21</version>
</dependency>
<!--log4j-->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<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>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>
这里面的是JDK、数据库、测试模块、日志log的依赖。
serverTimezone=UTC代表设置了时区
没有实体类数据库中的数据怎么获取: 用map即可实现
默认数据源和指定数据源输出对比:
//因为springboot的自动装配原理,所有不需要写一大堆的代码
@Autowired//注入数据源
DataSource dataSource;
@Test
void contextLoads() throws SQLException {
//查看默认数据源
System.out.println(dataSource.getClass());//class com.zaxxer.hikari.HikariDataSource 默认数据源输出
//修改数据源后的输出 type: com.alibaba.druid.pool.DruidDataSource
//class com.alibaba.druid.pool.DruidDataSource
//获取连接
Connection connection = dataSource.getConnection();
System.out.println(connection);//HikariProxyConnection@1889457907 wrapping com.mysql.cj.jdbc.ConnectionImpl@698fee9a
//修改数据源后的输出 com.mysql.cj.jdbc.ConnectionImpl@71978f46
//关闭
connection.close();
}
【注意】以下是增删改查的实现
@RestController
public class JDBCController {
@Autowired
JdbcTemplate jdbcTemplate;
//查询数据库的所有信息
@GetMapping("/userList")
public List<Map<String, Object>> userList(){
String sql = "select * from tb_user";
List<Map<String, Object>> list_maps = jdbcTemplate.queryForList(sql);
return list_maps;
}
@GetMapping("/addUser")
public String addUser(){
String sql ="insert into user values (3,'小明','123456')";
jdbcTemplate.update(sql);
return "update-ok";
}
@GetMapping("/updateUser/{id}")
public String updateUser(@PathVariable("id") Integer id){
String sql ="update user set name = ?,password=? where id="+id;
//封装
Object[] object = new Object[2];
object[0]="小明2";
object[1] = "zzzz";
jdbcTemplate.update(sql,object);
return "updateUser-ok";
}
@GetMapping("/deleteUser/{id}")
public String deleteUser(@PathVariable("id") int id){
String sql ="delete from user where id=?";
jdbcTemplate.update(sql, id);
return "deleteUser-ok";
}
德鲁伊数据源专有配置:
【注意】以下这些直接加到application.yml中取即可:
# Springboot默认不注入这些属性,需要自己绑定
# druid数据源转有配置
initialSize: 10
minIdle: 10
maxActive: 1000
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
【注意】写这个的时候切记跟username对其不然会报错:
spring:
datasource:
username: root
password: 123456
#?serverTimezone=UTC解决时区的报错
url: jdbc:mysql://localhost:3306/springboot?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
#Spring Boot 默认是不注入这些属性值的,需要自己绑定
#druid 数据源专有配置
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
#配置监控统计拦截的filters,stat:监控统计、log4j:日志记录、wall:防御sql注入
#如果允许时报错 java.lang.ClassNotFoundException: org.apache.log4j.Priority
#则导入 log4j 依赖即可,Maven 地址:https://mvnrepository.com/artifact/log4j/log4j
filters: stat,wall,log4j
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500