SpringBoot(二)
一、整合JDBC
- 建立springboot项目,添加依赖:web,jdbcAPI ,Mysql driver
- 写配置
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
password: 123456
username: root
url: jdbc:mysql://localhost:3306/employee?useUnicode=true&characterEncoding=utf-8
- 测试是否连接上
package com.example;
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 Springboot05DataApplicationTests {
//引入数据源
@Autowired
DataSource dataSource;
@Test
void contextLoads() throws SQLException {
//输出看看数据源是什么
System.out.println(dataSource.getClass());//class com.zaxxer.hikari.HikariDataSource
//获取连接
Connection connection = dataSource.getConnection();
System.out.println(connection);
// xxxxTemplate:SpringBoot 已经配置好模板bean,拿来即用 CRUD
//关闭连接
connection.close();
}
}
- 不写实体类,用jdbc操作数据库,操作CRUD
package com.example.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
import java.util.Map;
@RestController
public class JdbcController {
@Autowired
JdbcTemplate jdbcTemplate;
//查询数据库表的所有信息
//没有实体类,数据库中的东西,怎么获取 Map
@GetMapping("/userList")
public List<Map<String,Object>>userList(){
String sql="select * from employee";
List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
return mapList;
}
@GetMapping("/departmentList")
public List<Map<String,Object>>departmentList(){
String sql="select * from department";
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
return list;
}
//增加
@GetMapping("/addDepartment")
public String addDepartment(){
String sql = "insert into employee.department(id,departmentName)values(6,'勤学部')";
jdbcTemplate.update(sql);
return "update-ok";
}
//修改
@GetMapping("/updateDepartment")
public String updateDepartment(){
String sql = "update employee.department set 'departmentName'='党委部' where id=4";
jdbcTemplate.update(sql);
return "update-ok";
}
//删除
@GetMapping("/delDepartment")
public String delDepartment(){
String sql = "delete from employee.department where id=3";
jdbcTemplate.update(sql);
return "update-ok";
}
}
二、整合Druid数据源
- DRUID简介
- Druid是阿里巴巴开源台上一个数据库连接池实现,结合了C3P0、DBCP、PROXOOL等DB池的优点,同时加入了日志监控。
- Druid可以很好的监控DB池连接和SQL的执行情况,天生就是针对监控而生的DB连接池
- Springboot2.0以上默认使用HiKari 数据源,可以说Hikari 与 Driud都是当前Java web上最优秀的数据源
- 使用Druid数据源
- 导入依赖去maven官网
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.11</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
- 配置druid
#SpringBoot默认是不注入这些属性值的,需要自己绑定
#druid 数据源专有配置
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
#配置监控统计拦截的filters, stat:监控统计 , log4j:日志记录 , wall:防御sql注入
#如果运行时报错,导入log4j即可(maven)
filters: stat,wall,log4j
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
- 自定义druid配置
@Configuration
public class DruidConfig {
//将自定义配置和配置文件application.yml绑定
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource druidDataSource(){
return new DruidDataSource();
}
//后台监控 web.xml
//因为springboot内置了servlet容器,所以没有web.xml,替换方法ServletRegistrationBean
//后台监控功能自定义
@Bean
public ServletRegistrationBean a(){
ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
//后台需要有人登录账号密码配置
HashMap<String, String> initParameters = new HashMap<>();
//增加配置
initParameters.put("loginUsername","admin");
initParameters.put("loginPassword","123456"); //登录的key是固定的,不能改
//允许谁可以访问
initParameters.put("allow",""); //都能访问
//禁止谁能访问 initParameters.put("kaungshne","192.168.11.123");
bean.setInitParameters(initParameters);//设置初始化参数
return bean;
}
//通过访问localhost:8080/druid 可以进入监控后台
//自定义filter过滤器
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;
}
}
配置容器都是通过这样的方法自定义,多看源码,提示需要什么就new什么
三、整合Mybatis框架
- 导入依赖
<!-- 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>
- 连接数据库,写配置文件(整合mybatis)
spring:
datasource:
username: root
password: 123456
url: jdbc:mysql://localhost:3306/employee?useUnicode=true&characterEncoding=utf-8
driver-class-name: com.mysql.cj.jdbc.Driver
#整合mybatis
mybatis:
type-aliases-package: com.qian.pojo
mapper-locations: classpath:mybatis/mapper/*.xml
- 写实体类pojo
package com.qian.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Department {
private int id;
private String departmentName;
}
- DepartmentMapper(增删改查)
package com.qian.mapper;
import com.qian.pojo.Department;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
import java.util.List;
//这个注解表示了这是一个mybatis的mapper类:Dao
@Mapper
@Repository
public interface DepartmentMapper {
List<Department>queryDepartmentList();
Department queryDepartmentById(int id);
int addDepartment(Department department);
int updateDepartment(Department department);
int deleteDepartment(int id);
}
- DepartmentMapper.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">
<!--namespace=绑定一个对应的Dao/Mapper接口-->
<mapper namespace="com.qian.mapper.DepartmentMapper">
<select id="queryDepartmentList" resultType="Department">
select * from department
</select>
<select id="queryDepartmentById" parameterType="Department">
select * from department where id=#{id}
</select>
<insert id="addDepartment" parameterType="Department">
insert into department(id,departmentName)values (#{id},#{departmentName})
</insert>
<update id="updateDepartment" parameterType="Department">
update department set departmentName=#{departmentName} where id = #{id}
</update>
<delete id="deleteDepartment" parameterType="int">
delete from department where id = #{id}
</delete>
</mapper>
- 省去了service层,实际开发还是要写service层
DepartmentController
package com.qian.controller;
import com.qian.mapper.DepartmentMapper;
import com.qian.pojo.Department;
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 DepartmentController {
@Autowired
private DepartmentMapper departmentMapper;
//查询全部
@GetMapping("/queryDepartmentList")
public List<Department>queryDepartmentList(){
List<Department> departmentList = departmentMapper.queryDepartmentList();
for (Department department:departmentList){
System.out.println(department);
}
return departmentList;
}
//增加一个部门
@GetMapping("/addDepartment")
public String addDepartment(){
departmentMapper.addDepartment(new Department(7,"营销部"));
return "ok";
}
//修改一个部门
@GetMapping("/updateDepartment")
public String updateDepartment(){
departmentMapper.updateDepartment(new Department(1,"人才部"));
return "ok";
}
//删除一个部门
@GetMapping("/deleteDepartment")
public String deleteDepartment(){
departmentMapper.deleteDepartment(6);
return "ok";
}
}
- 运行测试