2.SpringBoot中的MySQL数据库(阿里巴巴连接池druid)配置以及Myatis框架配置和初步应用
1.配置数据库
JDBC配置
在resources
中创建配置文件 application.yaml
,写入配置信息
spring:
thymeleaf:
cache: false
datasource:
url: jdbc:mysql://127.0.0.1:3306/mybatis?useUnicode=true&useJDBCCompliantTimezoneShift=true&serverTimezone=UTC&utf8&useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
配置数据库链接池
pom中引入阿里巴巴的连接池
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.20</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.8</version>
</dependency>
在application.yaml
中进行配置
spring:
thymeleaf:
cache: false
datasource:
url: jdbc:mysql://127.0.0.1:3306/mybatis?useUnicode=true&useJDBCCompliantTimezoneShift=true&serverTimezone=UTC&utf8&useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
druid:
# initial-size: 5
# max-active: 20
# min-idle: 5
# filters: stat,wall,log4j
#2.连接池配置
#初始化连接池的连接数量 大小,最小,最大
initial-size: 5
min-idle: 5
max-active: 20
#配置获取连接等待超时的时间
max-wait: 60000
#配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
time-between-eviction-runs-millis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
min-evictable-idle-time-millis: 30000
validation-query: SELECT 1 FROM DUAL
test-while-idle: true
test-on-borrow: true
test-on-return: false
# 是否缓存preparedStatement,也就是PSCache 官方建议MySQL下建议关闭 个人建议如果想用SQL防火墙 建议打开
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filter:
stat:
merge-sql: true
slow-sql-millis: 5000
#3.基础监控配置
web-stat-filter:
enabled: true
url-pattern: /*
#设置不统计哪些URL
exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"
session-stat-enable: true
session-stat-max-count: 100
stat-view-servlet:
enabled: true
url-pattern: /druid/*
reset-enable: true
#设置监控页面的登录名和密码
login-username: admin
login-password: admin
allow: 127.0.0.1
#deny: 192.168.1.100
启动服务器后,在浏览器中输入http://localhost:8080/druid/login.html
,进入后台
账号密码为自己在yaml中配置的
#设置监控页面的登录名和密码
login-username: admin
login-password: admin
Mybatis
整合包 mybatis-spring-boot-starter
maven地址:https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter
<!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.2</version>
</dependency>
在pojo中新建一个user类
package com.example.demo4.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private int id;
private String name;
private String pwd;
}
新建一个mapper目录,用于创建我们的接口
package com.example.demo4.mapper;
import com.example.demo4.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
import java.util.List;
//这个注解表示这是一个mybatis的mapper类
@Mapper
//@Repository是标注在Dao层接口上,作用是将接口的一个实现类交给Spring管理
@Repository
public interface Usermapper {
List<User> querUserList();
User querUserById(int id);
int addUser(User user);
int updateUser(User user);
int deleteUser(int id);
}
创建配置文件
mybatis中文文档:https://mybatis.org/mybatis-3/zh/getting-started.html#
从文档中复制到我们的xml文件中
更改为自己的绑定接口
在application.properties中整合mybatis
#整合mybatis
mybatis.type-aliases-package=com.example.demo4.pojo
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
在xml中写入我们在mapper创建方法的内容
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo4.mapper.Usermapper">
<select id="querUserList" resultType="User">
select * from user
</select>
<select id="querUserById" resultType="User">
select * from user where id = #{id}
</select>
<insert id="addUser" parameterType="User">
insert into user (id,name,pwd) values (#{id},#{name},#{pwd})
</insert>
<update id="updateUser" parameterType="User">
update user set name=#{name},#{pwd} where id = #{id}
</update>
<delete id="deleteUser" parameterType="int">
delete from user where id = #{id}
</delete>
</mapper>
创建controller
package com.example.demo4.controller;
import com.example.demo4.mapper.Usermapper;
import com.example.demo4.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
public class UserController {
@Autowired
private Usermapper usermapper;
@GetMapping("/querUserList")
public List<User> querUserList(){
List<User> userList = usermapper.querUserList();
return userList;
}
@GetMapping("/addUser")
public String addUser(){
usermapper.addUser(new User(5,"阿毛","123412"));
return "OK";
}
}
运行springboot,测试我们的方法