集成mysql
所需依赖
mysql驱动
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
通过jdbc连接数据库
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
在mysql新建表(spring数据库是我已有的)
use spring;
drop table if exists zuser;
create table zuser
(
id varchar(32) not null comment '主键',
name varchar(10) default null comment '用户名',
password varchar(32) default null comment '密码'
);
insert into zuser values ('1','小T','123456');
insert into zuser values ('2','小H','123456');
在application.properties里添加(用的是mysql8.0,url比较麻烦)
#mysql连接信息
spring.datasource.url=jdbc:mysql://localhost:3306/spring?serverTimezone=GMT&useUnicode=true&characterEncoding=UTF-8&useSSL=false&zeroDateTime
#用户名
spring.datasource.username=root
#密码
spring.datasource.password=123456
#驱动
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
建立实体类
package com.zzf.demo.model;
import lombok.Data;
/*
*
*@author:zzf
*@time:2020-11-16
*
*/
@Data
public class ZUser {
//主键
private String id;
//用户名
private String name;
//密码
private String password;
}
这里用了lombok插件(需下载),可以生成get/set/toString方法
lombok插件依赖
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
测试
package com.zzf.demo;
import com.zzf.demo.model.ZUser;
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;
import org.springframework.jdbc.core.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
@SpringBootTest
class DemoApplicationTests {
@Autowired
private JdbcTemplate jdbcTemplate;
@Test
void contextLoads() {
String sql="select id,name,password from zuser";
List<ZUser> userList=jdbcTemplate.query(sql, new RowMapper<ZUser>() {
@Override
public ZUser mapRow(ResultSet resultSet, int i) throws SQLException {
ZUser user=new ZUser();
user.setId(resultSet.getString("id"));
user.setName(resultSet.getString("name"));
user.setPassword(resultSet.getString("password"));
return user;
}
});
System.out.println("查询成功:");
for (ZUser user:userList
) {
System.out.println(user.toString());
}
}
}
JdbcTemplate 是一个可以对数据库增删查改的工具类
query()是查询
RowMapper对象可以将查询出的每一行数据封装成用户定义的类
效果
集成Druid
Druid是阿里巴巴开源项目的一个数据库连接池,是个JDBC组件,主要用于监控,观察连接处和SQL查询工作情况,包括三部分:
DruidDriver:代理Driver,提供基于Filtter-Chain模式的插件体系
DruidDataSource:高效可管理的数据库连接池
SQLParser:支持所有JDBC兼容的数据库
druid依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.21</version>
</dependency>
druid配置
#mysql连接信息
spring.datasource.url=jdbc:mysql://localhost:3306/spring?serverTimezone=GMT&useUnicode=true&characterEncoding=UTF-8&useSSL=false&zeroDateTime
#用户名
spring.datasource.username=root
#密码
spring.datasource.password=123456
#驱动
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#数据源类别
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
#初始化大小,最小、最大
spring.datasource.druid.initial-size=5
spring.datasource.druid.min-idle=5
spring.datasource.druid.max-active=20
#获取连接等待超时的时间,单位为毫秒
spring.datasource.druid.max-wait=60000
#间隔多久检测一次,关闭空闲连接
spring.datasource.druid.time-between-eviction-runs-millis=60000
#配置一个连接在池中最小生存时间
spring.datasource.druid.min-evictable-idle-time-millis=300000
spring.datasource.druid.validation-query=select 1 from dual
spring.datasource.druid.test-while-idle=true
spring.datasource.druid.test-on-borrow=false
spring.datasource.druid.test-on-return=false
#打开PSCache,并指定每个连接上PSCache的大小
spring.datasource.druid.pool-prepared-statements=true
spring.datasource.druid.max-pool-prepared-statement-per-connection-size=20
#配置监控统计拦截的filters,去掉后监控界面SQL无法统计,'wall'用于防火墙
spring.datasource.druid.filters=stat,wall,log4j
#通过connecProperties属性打开mergeSql功能;慢SQL记录
spring.datasource.druid.connection-properties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
#合并多个DruidDataSource的监控数据
spring.datasource.druid.use-global-data-source-stat=true
还要引入log4j依赖,否则会报错,可以打印日志
log4j
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
新建log4j.properties文件
log4j.rootLogger=DEBUG, stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
开启监控功能
配置类
package com.zzf.demo.filter;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
/*
*
*@author:zzf
*@time:2020-11-16
*
*/
@Configuration
public class DruidConfiguration {
@Bean
public ServletRegistrationBean druidStatViewServlet(){
//ServletRegistrationBean提供类的进行注册
ServletRegistrationBean servletRegistrationBean
=new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
//添加初始化参数:initParams
//白名单:
servletRegistrationBean.addInitParameter("allow","127.0.0.1");
//IP黑名单(如果同时存在,deny优先于allow)
//如果满足deny提示没有权限访问
servletRegistrationBean.addInitParameter("deny","192.168.1.73");
//登录查看账号密码的信息
servletRegistrationBean.addInitParameter("LoginUsername","admin");
servletRegistrationBean.addInitParameter("loginPassword","123456");
//是否能够重置数据
servletRegistrationBean.addInitParameter("resetEnable","false");
return servletRegistrationBean;
}
@Bean
public FilterRegistrationBean druidStatFilter(){
FilterRegistrationBean filterRegistrationBean
=new FilterRegistrationBean(new WebStatFilter());
//添加过滤规则
filterRegistrationBean.addUrlPatterns("/*");
//添加需要忽略的格式信息
filterRegistrationBean.addInitParameter("exclusions",
"*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
return filterRegistrationBean;
}
}
相当于ssm中的spring配置文件里的<bean>
运行项目在浏览器输入http://localhost:8080/druid/index.html
登录后可以进入监控界面