SpringBoot学习笔记07
SpringBoot整合
1. 整合Druid数据源
1. 创建新SpringBoot项目
勾选相关框架< 未勾选后面也可以自己导入依赖>
创建成功:
2. 连接数据库
-
新建数据库
运行sql文件, 下载连接如下:
链接：https://pan.baidu.com/s/1JHEtuVryzPOp3ogTItTDeA
提取码：zyr2运行后如下图:
-
在resources下新建一个application.yaml<功能比application.properties更强更广>并编写
spring: datasource: username: root password: 123456 url: jdbc:mysql://localhost:3306/springboot?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&characterEncoding=utf8 driver-class-name: com.mysql.cj.jdbc.Driver -
导入maven依赖
<!-- https://mvnrepository.com/artifact/com.alibaba/druid --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.2.6</version> </dependency> -
使用Druid数据源
Druid可以很好的监控DB池连接和SQL的执行情况，天生就是针对监控而生的DB连接池
springboot 2.0以上开始默认使用
Hikari数据源，Hikari与Druid都是当前Java Web上最优秀的数据源在application.yaml中指定Druid数据源
spring: datasource: username: root password: 123456 url: jdbc:mysql://localhost:3306/springboot?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&characterEncoding=utf8 driver-class-name: com.mysql.cj.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource
-
编写测试类Springboot04DataApplicationTests.java
package com.zyr; import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import javax.sql.DataSource; import java.sql.Connection; import java.sql.SQLException; @SpringBootTest class Springboot04DataApplicationTests { @Autowired DataSource dataSource; @Test void contextLoads() throws SQLException { //查看一下默认的数据源为：com.zaxxer.hikari.HikariDataSource System.out.println("默认的数据源为"+dataSource.getClass()); //获取数据库连接：默认为：HikariProxyConnection@1794489296 wrapping com.mysql.cj.jdbc.ConnectionImpl@76464795 Connection connection = dataSource.getConnection(); System.out.println(connection); //关闭连接 connection.close(); } }不指定数据源：
指定Druid：
-
使用Druid，application.yaml的具体配置
spring: datasource: username: root password: 123456 url: jdbc:mysql://localhost:3306/springboot?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&characterEncoding=utf8 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参数解释：
com.alibaba.druid.pool.DruidDataSource 基本配置参数如下：
-
日志功能使用
-
导入log4j的依赖
<!-- https://mvnrepository.com/artifact/log4j/log4j --> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency> -
在application.yaml添加相应内容
spring: datasource: username: root password: 123456 url: jdbc:mysql://localhost:3306/springboot?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&characterEncoding=utf8 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 -
新建config包下并新建一个DruidConfig.java
@Configuration public class DruidConfig { @Bean @ConfigurationProperties(prefix = "spring.datasource") public DataSource druidDataSource(){ return new DruidDataSource(); } //后台监控：web.xml @Bean public ServletRegistrationBean statViewServlet(){ ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*"); //后台需要有人登录 HashMap<String, String> initParameters = new HashMap<>(); //增加配置 //账号密码 initParameters.put("loginUsername", "zyr"); initParameters.put("loginPassword", "123456"); //允许谁访问 initParameters.put("allow", ""); //禁止谁访问 /*initParameters.put("zyr", "192.168.91.130");*/ bean.setInitParameters(initParameters); return bean; } //配置 Druid 监控 之 web 监控的 filter //WebStatFilter：用于配置Web和Druid数据源之间的管理关联监控统计 @Bean public FilterRegistrationBean webStatFilter() { FilterRegistrationBean bean = new FilterRegistrationBean(); bean.setFilter(new WebStatFilter()); //exclusions：设置哪些请求进行过滤排除掉，从而不进行统计 Map<String, String> initParams = new HashMap<>(); initParams.put("exclusions", "*.js,*.css,/druid/*,/jdbc/*"); bean.setInitParameters(initParams); //"/*" 表示过滤所有请求 bean.setUrlPatterns(Arrays.asList("/*")); return bean; } } -
运行，访问：http://localhost:8080/druid，结果如下
用设置的账号密码登录
-
测试
新建cotroller文件夹并在下面编写JDBCController
@RestController public class JDBCController { @Autowired JdbcTemplate jdbcTemplate; @GetMapping("/userList") public List<Map<String, Object>> userList(){ String sql = "select * from user"; List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql); return maps; } }运行：输入http://localhost:8080/userList
查到相应数据：
观察Druid的SQL监控页面：
-
2. 整合Mybatis框架
1. 创建新SpringBoot项目
2. 整合Mybatis
-
导入maven依赖
<!-- 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.1.4</version> </dependency> -
配置数据库信息,这里我使用application.yaml
spring: datasource: username: root password: 123456 url: jdbc:mysql://localhost:3306/springboot?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&characterEncoding=utf8 driver-class-name: com.mysql.cj.jdbc.Driver -
新建mapper、pojo、controller骨架包
pojo下编写User类<需导入lombook>
@Data @AllArgsConstructor @NoArgsConstructor public class User { private int id; private String name; private String pwd; }mapper下编写UserMapper接口
@Mapper public interface UserMapper { List<User> queryUserList(); User queryUserById(int id); int addUser(User user); int updateUser(User user); int deleteUser(int id); }application.yaml整合mybatis
#整合mybatis mybatis: type-aliases-package: com.zyr.pojo mapper-locations: classpath:mybatis/mapper/*.xml在resource下新建mapper文件夹并新建mapper.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.zyr.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, pwd) values (#{id}, #{name}, #{pwd}) </insert> <update id="updateUser" parameterType="User"> update user set name = #{name}, pwd = #{pwd} where id = #{id} </update> <delete id="deleteUser"> delete from user where id = #{id} </delete> </mapper> -
编写UserController
@RestController public class UserController { @Autowired private UserMapper userMapper; @RequestMapping("/queryalluser") public List<User> queryAllUser(){ List<User> users = userMapper.queryUserList(); return users; } @RequestMapping("/queryuserbyid") public String queryUserById(){ User user = userMapper.queryUserById(1); System.out.println(user); return "ok"; } @RequestMapping("/adduser") public String addUser(){ int user = userMapper.addUser(new User(4, "刘云杰", "123456")); return "添加成功"; } @RequestMapping("/updateuser") public String updateUser(){ int user = userMapper.updateUser(new User(4, "刘云杰", "654321")); return "修改成功"; } @RequestMapping("/deleteuser") public String deleteUser(){ int i = userMapper.deleteUser(4); return "删除成功"; } }
er(new User(4, “刘云杰”, “123456”));
return “添加成功”;
}
@RequestMapping("/updateuser")
public String updateUser(){
int user = userMapper.updateUser(new User(4, "刘云杰", "654321"));
return "修改成功";
}
@RequestMapping("/deleteuser")
public String deleteUser(){
int i = userMapper.deleteUser(4);
return "删除成功";
}
}
5. 运行测试
664
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
