4. 整合数据库
4.1 整合 JDBC
(1) 导入依赖
<!--pom.xml-->
<dependencies>
<!--web-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--JDBC-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!--MYSQL 驱动-->
<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>
</dependency>
</dependencies>
(2) 配置数据库
# application.yml
spring:
datasource:
username: root
password: 981030
url: jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
driver-class-name: com.mysql.cj.jdbc.Driver
(3) 测试数据链接
// DataApplicationTests
@Test
void contextLoads() throws SQLException {
// 查看默认数据源: com.zaxxer.hikari.HikariDataSource
System.out.println("dataSource ==> " + dataSource.getClass());
// 获得数据库链接
Connection connection = dataSource.getConnection();
System.out.println("connection ==> " + connection);
connection.close();
}
(4) RCUD
@RestController
public class JdbcController {
@Autowired
JdbcTemplate jdbcTemplate;
@GetMapping("/userList")
public List<Map<String, Object>> userList() {
String sql = "select * from user";
List<Map<String, Object>> list_maps = jdbcTemplate.queryForList(sql);
return list_maps;
}
@GetMapping("/addUser")
public String addUser() {
String sql = "insert into mybatis.user (id, name, pw) values (5, '周五', '555555')";
int add = jdbcTemplate.update(sql);
return "add ==> " + add;
}
@GetMapping("/updateUser/{id}")
public String updateUser(@PathVariable("id") int id) {
String sql = "update mybatis.user set name = ?, pw = ? where id = " + id;
Object[] objects = new Object[2];
objects[0] = "周武";
objects[1] = "50005";
int update = jdbcTemplate.update(sql, objects);
return "update ==> " + update;
}
@GetMapping("/deleteUser/{id}")
public String deleteUser(@PathVariable("id") int id) {
String sql = "delete from mybatis.user where id =? ";
int delete = jdbcTemplate.update(sql, id);
return "delete ==> " + delete;
}
}
4.2 整合 Druid
(1) 导入依赖
<!--pom.xml-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.21</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
(2) 配置数据源
spring:
datasource:
username: root
password: 981030
url: jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
# SpringBoot 默认不注入以下属性值
# 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
(3) 配置后台监控
@Configuration
public class DruidConfig {
@Bean
@ConfigurationProperties("spring.datasource")
public DataSource druidDataSource() {
return new DruidDataSource();
}
/**
* 后台监控
* @return ServletRegistration
*/
@Bean
public ServletRegistrationBean statViewServlet() {
ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
HashMap<String, String> initParameters = new HashMap<>();
// 后台管理员配置
// key 固定
initParameters.put("loginUsername", "admin");
initParameters.put("loginPassword", "123456");
// 允许访问
// 允许所有人访问
initParameters.put("allow", "");
// 禁止访问
// initParameters.put("人名", "ip地址");
// 设置初始化参数
bean.setInitParameters(initParameters);
return bean;
}
/**
* 过滤器
* @return FilterRegistrationBean
*/
@Bean
public FilterRegistrationBean webStatFilter() {
FilterRegistrationBean bean = new FilterRegistrationBean();
bean.setFilter(new WebStatFilter());
HashMap<String, String> initParameters = new HashMap<>();
// 过滤请求,不进行统计
initParameters.put("exclusions", "*.js, *.css, /druid/*");
bean.setInitParameters(initParameters);
return bean;
}
}
4.3 整合 Mybatis
(1) 配置 mapper 路径
# 整合 mybatis
mybatis.type-aliases-package=com.why.pojo
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
(2) 编写 mapper 接口
// 标识 mybatis 的 mapper
@Mapper
@Repository
public interface UserMapper {
List<User> queryUserList();
User queryUserById(int id);
int addUser(User user);
int updateUser(User user);
int deleteUser(int id);
}
(3) 编写 xml 文件
<?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.why.mapper.UserMapper">
<select id="queryUserList" resultType="User">
select * from user
</select>
<select id="queryUserById" resultType="User">
select * from user where id = #{id}
</select>
<insert id="addUser" parameterType="User">
insert into user (id, name, pw) values (#{id}, #{name}, #{pw})
</insert>
<insert id="updateUser" parameterType="User">
update user set name = #{name}, pw = #{pw} where id = #{id}
</insert>
<delete id="deleteUser" parameterType="int">
delete from user where id = #{id}
</delete>
</mapper>
(4) 编写控制器
@RestController
public class UserController {
@Autowired
private UserMapper userMapper;
@GetMapping("/queryUserList")
public List<User> queryUserList() {
List<User> userList = userMapper.queryUserList();
for (User user : userList) {
System.out.println("user ==> " + user);
}
return userList;
}
@GetMapping("/queryUserById/{id}")
public User queryUserById(@PathVariable("id") int id) {
User user = userMapper.queryUserById(id);
return user;
}
@GetMapping("/addUser")
public String queryUserById() {
int i = userMapper.addUser(new User(6, "吴六", "6000"));
return "添加 ==> " + i;
}
@GetMapping("/updateUser")
public String updateUser() {
int i = userMapper.updateUser(new User(6, "吴柳", "6000"));
return "更新 ==> " + i;
}
@GetMapping("/deleteUser/{id}")
public String deleteUser(@PathVariable("id") int id) {
int i = userMapper.deleteUser(id);
return "删除 ==> " + i;
}
}
文章来源地址https://www.uudwc.com/A/Pmv54/
文章来源:https://www.uudwc.com/A/Pmv54/