Java连接数据库小案例
前期准备：
JDK IDEA MYSQL MAVEN REDIS
一、建立数据库表
二、创建Spring boot文件
添加连接池Druid
第一步、在项目pom.xml文件中添加依赖
<!—Lombok注释 -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
</dependency>
<!-- MySQL驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.19</version>
</dependency>
<!-- Druid依赖 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.21</version>
</dependency>
第二步、在resource文件夹下的application.yml中添加配置：
server:
port: 80
servlet:
context-path: /demo
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&serverTimezone=UTC
username: root
password: 123456
# 使用druid数据源
type: com.alibaba.druid.pool.DruidDataSource
druid:
# 配置测试查询语句
validationQuery: SELECT 1 FROM DUAL
# 初始化大小，最小，最大
initialSize: 10
minIdle: 10
maxActive: 200
# 配置一个连接在池中最小生存的时间，单位是毫秒
minEvictableIdleTimeMillis: 180000
testOnBorrow: false
testWhileIdle: true
removeAbandoned: true
removeAbandonedTimeout: 1800
logAbandoned: true
# 打开PSCache，并且指定每个连接上PSCache的大小
poolPreparedStatements: true
maxOpenPreparedStatements: 100
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
filters: stat,wall,slf4j,config
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
第三步、创建Druid的Servlet和Filter监控配置类：
@Configuration
public class WebConfiguration implements WebMvcConfigurer {
/**
* druidServlet注册
*/
@Bean
public ServletRegistrationBean druidServletRegistration() {
ServletRegistrationBean registration = new ServletRegistrationBean(new StatViewServlet());
registration.addUrlMappings("/druid/*");
return registration;
}
/**
* druid监控 配置URI拦截策略
*/
@Bean
public FilterRegistrationBean druidStatFilter() {
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
//添加过滤规则.
filterRegistrationBean.addUrlPatterns("/*");
//添加不需要忽略的格式信息.
filterRegistrationBean.addInitParameter(
"exclusions", "/static/*,*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid,/druid/*");
//用于session监控页面的用户名显示 需要登录后主动将username注入到session里
filterRegistrationBean.addInitParameter("principalSessionName", "username");
return filterRegistrationBean;
}
/**
* druid数据库连接池监控
*/
@Bean
public DruidStatInterceptor druidStatInterceptor() {
return new DruidStatInterceptor();
}
@Bean
public JdkRegexpMethodPointcut druidStatPointcut() {
JdkRegexpMethodPointcut druidStatPointcut = new JdkRegexpMethodPointcut();
String patterns = "com.example.demo.service.*";
//可以set多个
druidStatPointcut.setPatterns(patterns);
return druidStatPointcut;
}
/**
* druid数据库连接池监控
*/
@Bean
public BeanTypeAutoProxyCreator beanTypeAutoProxyCreator() {
BeanTypeAutoProxyCreator beanTypeAutoProxyCreator = new BeanTypeAutoProxyCreator();
beanTypeAutoProxyCreator.setTargetBeanType(DruidDataSource.class);
beanTypeAutoProxyCreator.setInterceptorNames("druidStatInterceptor");
return beanTypeAutoProxyCreator;
}
/**
* druid 为druidStatPointcut添加拦截
*/
@Bean
public Advisor druidStatAdvisor() {
return new DefaultPointcutAdvisor(druidStatPointcut(), druidStatInterceptor());
}
}
添加持久层Mybatis Plus
第一步、在项目pom.xml文件中添加依赖
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.0</version>
</dependency>
第二步、在resource文件夹下的application.yml中添加配置：
mybatis-plus:
# 本地mapper路径
mapper-locations: classpath:mapper/*.xml
# 指定实体类路径
typeAliasesPackage: com.*.*.entity
global-config:
db-config:
# 指定自增ID类型
id-type: ASSIGN_ID
创建业务相关类
第一步、创建系统用户实体类：
@Data
public class SysUser {
private Long id;
private String userName;
private String passWord;
private Date createTime;
private Integer userAge;
private String remark;
}
第二步、创建系统用户持久层；
@Mapper
public interface SysUserDao extends BaseMapper<SysUser> {
}
第三步、创建系统用户业务层；
public interface SysUserService {
}
@Service("SysUserService")
public class SysUserServiceImp extends ServiceImpl<SysUserDao, SysUser> implements SysUserService {
}
第四步、创建系统用户控制层；
@RestController
public class SysUserController {
@Autowired
SysUserServiceImp sysUserServiceImp;
@GetMapping("/getUser")
public SysUser getUser(Long id){
return sysUserServiceImp.getById(id);
}
@PostMapping("/insertUser")
public String insertUser(SysUser sysUser){
sysUser.setCreateTime(new Date());
return sysUserServiceImp.save(sysUser)?"保存成功":"保存失败";
}
@PutMapping("/updateUser")
public String updateUser(SysUser sysUser){
return sysUserServiceImp.updateById(sysUser)?"修改成功":"修改失败";
}
@GetMapping("/getList")
public List<SysUser> getList(){
return sysUserServiceImp.list();
}
}
添加权限框架Spring Security
第一步、在项目pom.xml文件中添加依赖：
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-security</artifactId>
</dependency>
第二步、简单模式测试，直接访问http://localhost:8080/：
（1）创建配置类：
@Configuration
@EnableWebSecurity
public class SpringSecurityConfig extends WebSecurityConfigurerAdapter {
@Override
protected void configure(HttpSecurity http) throws Exception {
http.authorizeRequests()
.antMatchers("/").permitAll()
.anyRequest().authenticated()
.and()
.logout().permitAll()
.and()
.formLogin();
http.csrf().disable();
}
@Override
public void configure(WebSecurity web) throws Exception {
web.ignoring().antMatchers("/js/**", "/css/**", "/images/**");
}
}
（2）添加接口方法：
@GetMapping("/")
public String index(Long id){
return "欢迎使用！";
}
第四步、添加内存账号测试
（1）增加内存账号配置到配置类：
@Override
protected void configure(AuthenticationManagerBuilder auth) throws Exception {
auth.inMemoryAuthentication().passwordEncoder(new BCryptPasswordEncoder()).withUser("admin").password(new BCryptPasswordEncoder().encode("123456")).roles("ADMIN");
}
（2）访问http://localhost:8080/getList ,输入账号admin，密码123456：