SpringBoot与数据访问
说明
对于数据访问层,无论是SQL还是NOSQL,Spring Boot默认采用整合
Spring Data的方式进行统一处理,添加大量自动配置,屏蔽了很多设置。引入
各种xxxTemplate,xxxRepository来简化我们对数据访问层的操作。对我们来
说只需要进行简单的设置即可。
DataSourceAutoConfiguration
:数据源的自动配置DataSourceProperties
:数据源的相关配置
整合Druid数据源
创建工程
引入druid
在Maven仓库查找Druid的依赖:点击查看
pom.xml中引入:
<!-- 引入Druid依赖 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.8</version>
</dependency>
yaml基本配置
spring:
datasource:
#数据源基本配置
username: root
password: 123456
##springboot 2.x中,com.mysql.jdbc.Driver已过期,请使用com.mysql.cj.jdbc.Driver
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://192.168.0.113:13306/jdbc
##默认数据源为:org.apache.tomcat.jdbc.pool.DataSource
##修改数据源为:com.alibaba.druid.pool.DruidDataSource
type: com.alibaba.druid.pool.DruidDataSource
新建测试用例
@SpringBootTest
class SpringBoot06DataJdbcApplicationTests {
@Autowired
DataSource dataSource;
@Test
void contextLoads() throws SQLException {
System.out.println("数据源:" + dataSource.getClass());
Connection connection = dataSource.getConnection();
System.out.println("数据源链接:" + connection);
connection.close();
}
}
测试用例输出,说明数据源已经切换为了Druid的数据源:
数据源:class com.alibaba.druid.pool.DruidDataSource
添加yaml其他配置
spring:
datasource:
#数据源基本配置
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://192.168.0.113:13306/jdbc
##默认数据源为:org.apache.tomcat.jdbc.pool.DataSource
##修改数据源为:com.alibaba.druid.pool.DruidDataSource
type: com.alibaba.druid.pool.DruidDataSource
#数据源其他配置
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,去掉后监控界面sql无法统计,'wall'用于防火墙
filters: stat,wall,log4j
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
启用druid
新建配置类DruidConfig:
@Configuration
public class DruidConfig {
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource druid(){
return new DruidDataSource();
}
/*配置Druid的监控*/
//1.配置一个管理后台的Servlet
@Bean
public ServletRegistrationBean statViewServlet(){
ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
Map<String,String> initParams = new HashMap<>();
initParams.put("loginUsername","admin");
initParams.put("loginPassword","123456");
initParams.put("allow","");//默认就是允许所有访问
initParams.put("deny","192.168.0.113");
bean.setInitParameters(initParams);
return bean;
}
//2.配置一个web监控的filter
@Bean
public FilterRegistrationBean webStatFilter(){
FilterRegistrationBean bean = new FilterRegistrationBean();
bean.setFilter(new WebStatFilter());
Map<String,String> initParams = new HashMap<>();
initParams.put("exclusions","*.js,*.css,/druid/*");
bean.setInitParameters(initParams);
bean.setUrlPatterns(Arrays.asList("/*"));
return bean;
}
}
监控后台
在浏览器输入:http://localhost:8080/druid,即可进入监控后台:
整合Mybatis
配置数据源相关属性(见上一节《整合Druid》)
创建数据表
#创建department表
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`departmentName` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
#创建employee表
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`lastName` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`gender` int(2) DEFAULT NULL,
`d_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
创建JavaBean
//Employee类
public class Employee {
private Integer id;
private String lastName;
private Integer gender;
private String email;
private Integer dId;
//getter、setter...
}
//Department类
public class Department {
private Integer id;
private String departmentName;
//getter、setter...
}
操作数据库(注解版)
创建Mapper
@Mapper
注解可以不写在Mapper类中,可在SpringBoot的启动类中添加@MapperScan({ "com.xx.xx.mapper" })
,也能起到同样效果:
@Mapper
public interface DepartmentMapper {
@Select("select * from department where id=#{id}")
public Department getDeptById(Integer id);
}
创建Controller
@RestController
public class DepartmentController {
@Autowired
DepartmentMapper dMapper;
@GetMapping("/dept/{id}")
public Department getDepartment(@PathVariable("id") Integer id){
return dMapper.getDeptById(id);
}
}
创建驼峰命名映射(可不加)
//配置mybatis驼峰命名映射
@org.springframework.context.annotation.Configuration
public class MyBatisConfig {
@Bean//加入到容器中
public ConfigurationCustomizer configurationCustomizer(){
return new ConfigurationCustomizer(){
@Override
public void customize(Configuration configuration) {
configuration.setMapUnderscoreToCamelCase(true);
}
};
}
}
在浏览器中请求:http://localhost:8080/dept/1
(提前插入一条ID为1的数据):
查询成功!!
操作数据库(配置文件版)
在classpath目录下的创建/mybatis/mapper
,如图所示:
创建全局配置文件mybatis-config.xml
:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--配置驼峰命名映射规则,跟上一节《操作数据库(注解版)》中的MyBatisConfig作用相同-->
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
</configuration>
创建Mapper
@Mapper
注解可以不写在Mapper类中,可在SpringBoot的启动类中添加@MapperScan({ "com.xx.xx.mapper" })
,也能起到同样效果:
@Mapper
public interface DepartmentMapper {
@Select("select * from department where id=#{id}")
public Department getDeptById(Integer id);
}
创建Controller
@RestController
public class EmployeeController {
@Autowired
EmployeeMapper eMapper;
@GetMapping("/emp/{id}")
public Employee getEmp(@PathVariable("id") Integer id){
return eMapper.getEmpById(id);
}
}
创建sql映射文件xxxMapper.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.atguigu.springboot.mapper.EmployeeMapper">
<!-- public Employee getEmpById(Integer id);
public void insertEmp(Employee employee);-->
<select id="getEmpById" resultType="com.atguigu.springboot.bean.Employee">
SELECT * FROM employee WHERE id=#{id}
</select>
</mapper>
在application.yml中添加mybatis配置:
mybatis:
# 声明全局配置文件的路径
config-location: classpath:mybatis/mybatis-config.xml
# 声明sql映射文件的路径
mapper-locations: classpath:mybatis/mapper/*
在浏览器中请求:http://localhost:8080/emp/1
(提前插入一条ID为1的数据):
查询成功!!
整合SpringData JPA
未完持续