springboot数据库配置

2022-10-09 13:59:40

1.对于数据访问层

无论是SQL还是NOSQL,SpringBoot默认采用整合SpringData的方式进行统一处理,添加大量自动配置,屏蔽了很多设置。引入各种xxxTemplate,xxxRepository来简化我们对数据访问层的操作。对我们来说只需要进行简单的设置即可。
Spring-boot-starter-data-XXX

2.jdbc访问mysql

1.在创建项目的时候勾选上mysql和jdbc
2.pom.xml中的配置如下:

<dependency><groupId>org.springframework.boot</groupId><artifactId>spring‐boot‐starter‐jdbc</artifactId></dependency><dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope></dependency>

3.application.yml中配置如下

spring:datasource:username: rootpassword:123456url: jdbc:mysql://192.168.15.22:3306/jdbcdriver‐class‐name: com.mysql.jdbc.Driver

效果:
默认是用org.apache.tomcat.jdbc.pool.DataSource作为数据源;
数据源的相关配置都在DataSourceProperties里面;
自动配置原理:org.springframework.boot.autoconfigure.jdbc:
1、参考DataSourceConfiguration,根据配置创建数据源,默认使用Tomcat连接池;可以使用spring.datasource.type指定自定义的数据源类型;
2、SpringBoot默认可以支持;
org.apache.tomcat.jdbc.pool.DataSource、HikariDataSource、BasicDataSource
3、自定义数据源类型

/**
* Generic DataSource configuration.
*/@ConditionalOnMissingBean(DataSource.class)@ConditionalOnProperty(name="spring.datasource.type")staticclassGeneric{@Beanpublic DataSourcedataSource(DataSourceProperties properties){//使用DataSourceBuilder创建数据源,利用反射创建响应type的数据源,并且绑定相关属性return properties.initializeDataSourceBuilder().build();}}

4、DataSourceInitializer:ApplicationListener;
作用:
1)、runSchemaScripts();运行建表语句;
2)、runDataScripts();运行插入数据的sql语句;
默认只需要将文件命名为:

schema‐*.sql、data‐*.sql

默认规则:schema.sql,schema‐all.sql;
可以在datasource下面使用

schema:- classpath:department.sql

指定位置
5、操作数据库:自动配置了JdbcTemplate操作数据库,可以通过标签@Autowired进行加载

3.配置druid连接

1.引入依赖

<!-- https://mvnrepository.com/artifact/com.alibaba/druid --><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.1.10</version></dependency>

2.修改配置文件

spring:datasource:username: rootpassword:123456url: jdbc:mysql://192.168.15.22:3306/jdbcdriver-class-name: com.mysql.jdbc.Drivertype: com.alibaba.druid.pool.DruidDataSourceinitialSize:5minIdle:5maxActive:20maxWait:60000timeBetweenEvictionRunsMillis:60000minEvictableIdleTimeMillis:300000validationQuery: SELECT 1 FROM DUALtestWhileIdle:truetestOnBorrow:falsetestOnReturn:falsepoolPreparedStatements:true#配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙filters: stat,wall,log4jmaxPoolPreparedStatementPerConnectionSize:20useGlobalDataSourceStat:trueconnectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500#   schema:#- classpath:department.sql

3.配置测试:

RunWith(SpringRunner.class)@SpringBootTestpublicclassSpringBoot06DataJdbcApplicationTests{@Autowired
	DataSource dataSource;@TestpublicvoidcontextLoads()throws SQLException{//org.apache.tomcat.jdbc.pool.DataSource
		System.out.println(dataSource.getClass());

		Connection connection= dataSource.getConnection();
		System.out.println(connection);
		connection.close();}}

4.自定义配置类

@ConfigurationpublicclassDruidConfig{@ConfigurationProperties(prefix="spring.datasource")@Beanpublic DataSourcedruid(){returnnewDruidDataSource();}}

5.配置监控统

@ConfigurationpublicclassDruidConfig{@ConfigurationProperties(prefix="spring.datasource")@Beanpublic DataSourcedruid(){returnnewDruidDataSource();}//配置Druid的监控//1、配置一个管理后台的Servlet@Beanpublic ServletRegistrationBeanstatViewServlet(){
        ServletRegistrationBean bean=newServletRegistrationBean(newStatViewServlet(),"/druid/*");
        Map<String,String> initParams=newHashMap<>();
        initParams.put("loginUsername","admin");
        initParams.put("loginPassword","123456");
        initParams.put("allow","");//默认就是允许所有访问
        initParams.put("deny","192.168.15.21");
        bean.setInitParameters(initParams);return bean;}//2、配置一个web监控的filter@Beanpublic FilterRegistrationBeanwebStatFilter(){
        FilterRegistrationBean bean=newFilterRegistrationBean();
        bean.setFilter(newWebStatFilter());
        Map<String,String> initParams=newHashMap<>();
        initParams.put("exclusions","*.js,*.css,/druid/*");
        bean.setInitParameters(initParams);
        bean.setUrlPatterns(Arrays.asList("/*"));return  bean;}}

4.整合mybatis–注解方式

1.新建议一个工程
file->project->spring Initializr
web中勾选spring web
sql中勾选jdbc、mysql、mybatis
2.pom.xml中的关键配置依赖

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.3</version>
</dependency>

依赖包之间的关系图
在这里插入图片描述

3.引入druid连接池配置

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.12</version>
</dependency>

4.配置application.yml文件

spring:datasource:username: rootpassword:123456url: jdbc:mysql://192.168.15.22:3306/mybatis?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=true&allowMultiQueries=TRUEdriver-class-name: com.mysql.jdbc.Drivertype: com.alibaba.druid.pool.DruidDataSourceinitialSize:5minIdle:5maxActive:20maxWait:60000timeBetweenEvictionRunsMillis:60000minEvictableIdleTimeMillis:300000validationQuery: SELECT 1 FROM DUALtestWhileIdle:truetestOnBorrow:falsetestOnReturn:falsepoolPreparedStatements:true#   配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙filters: stat,wall,log4jmaxPoolPreparedStatementPerConnectionSize:20useGlobalDataSourceStat:trueconnectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500initialization-mode: alwaysschema:- classpath:sql/department.sql- classpath:sql/employee.sql

5.配置类

@ConfigurationpublicclassDruidConfig{@ConfigurationProperties(prefix="spring.datasource")@Beanpublic DataSourcedruid(){returnnewDruidDataSource();}//配置Druid的监控//1、配置一个管理后台的Servlet@Beanpublic ServletRegistrationBeanstatViewServlet(){
        ServletRegistrationBean bean=newServletRegistrationBean(newStatViewServlet(),"/druid/*");
        Map<String,String> initParams=newHashMap<>();
        initParams.put("loginUsername","admin");
        initParams.put("loginPassword","123456");
        initParams.put("allow","");//默认就是允许所有访问
        initParams.put("deny","192.168.15.21");
        bean.setInitParameters(initParams);return bean;}//2、配置一个web监控的filter@Beanpublic FilterRegistrationBeanwebStatFilter(){
        FilterRegistrationBean bean=newFilterRegistrationBean();
        bean.setFilter(newWebStatFilter());
        Map<String,String> initParams=newHashMap<>();
        initParams.put("exclusions","*.js,*.css,/druid/*");
        bean.setInitParameters(initParams);
        bean.setUrlPatterns(Arrays.asList("/*"));return  bean;}}

5.数据库中配置一些表
在application.yml中进行配置

schema:

  • classpath:sql/department.sql
  • classpath:sq/employee.sql

6.运行查看数据库表是否被创建、查看 druid页面是否可以登录
7.创建对应的bean
8.注解配置
创建mapper

@MapperpublicinterfaceDepartmentMapper{@Select("select * from department where id=#{id}")public DepartmentgetDeptById(Integer id);@Delete("delect from department where id=#{id}")publicintdeleteDeptById(Integer id);@Options(useGeneratedKeys=true,keyProperty="id")@Insert("insert into department(departmentName) values(#{departmentName})")publicintinsertDept(Department department);@Update("update department set departmentName=#{departmentName} where id=#{id}")publicintupdateDept(Department department);}

9.编写controller测试

@RestControllerpublicclassDeptController{@Autowired
    DepartmentMapper departmentMapper;@GetMapping("/dept/{id}")public DepartmentgetDepartment(@PathVariable("id") Integer id){return departmentMapper.getDeptById(id);}@GetMapping("/dept")public DepartmentinsertDept(Department department){
        departmentMapper.insertDept(department);return department;}}

10.配置pom.xml文件

<dependency><groupId>log4j</groupId><artifactId>log4j</artifactId><version>1.2.17</version></dependency>

11.运行测试
12.开启数据库查询语句到bean的驼峰命名映射配置类

@ConfigurationpublicclassMyBatisConfig{public ConfigurationCustomizerconfigurationCustomizer(){returnnewConfigurationCustomizer(){@Overridepublicvoidcustomize(org.apache.ibatis.session.Configuration configuration){
                configuration.setMapUnderscoreToCamelCase(true);}};}}

13.如果某个包下面不标注mapper注解,可以在启动类上添加图下配置:

@MapperScan(value="com.atguigu.springboot.mapper")

5.整合mybatis–配置文件方式

1.配置接口

@MapperpublicinterfaceEmployeeMapper{public EmployeegetEmpById(Integer id);publicvoidinsertEmp(Employee employee);}

2.配置目录resources/mybatis/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"><mappernamespace="com.atguigu.springbootmybatis.mapper.EmployeeMapper"><!--    public Employee getEmpById(Integer id);
     public void insertEmp(Employee employee);--><selectid="getEmpById"resultType="com.atguigu.springbootmybatis.bean.Employee">
        SELECT * FROM employee WHERE id=#{id}</select><insertid="insertEmp">
        INSERT INTO employee(lastName,email,gender,d_id) VALUES (#{lastName},#{email},#{gender},#{dId})</insert></mapper>

3.配置yml文件

mybatis:config-location: classpath:mybatis/mybatis-config.xmlmapper-locations: classpath:mybatis/mapper/*.xml

4.测试

@RestControllerpublicclassDeptController{@Autowired
    EmployeeMapper employeeMapper;@GetMapping("/emp/{id}")public EmployeegetEmp(@PathVariable("id") Integer id){return employeeMapper.getEmpById(id);}}

5.可在配置文件mybatis/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><settings><settingname="mapUnderscoreToCamelCase"value="true"/></settings></configuration>

另一种配置方式,切换环境方式
配置文件application.yml的配置如下:

spring:profiles:active: test

application-test.yml的配置如下:

spring:datasource:#141master:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://192.168.50.141:3306/**********?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=true&allowMultiQueries=TRUEusername: XXXpassword: XXXslaver:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://192.168.50.141:3306/**********?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=true&allowMultiQueries=TRUEusername: XXXpassword: XXXbackups:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://192.168.50.141:3306/**********?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=true&allowMultiQueries=TRUEusername: XXXpassword: XXX

配置类信息如下:

@Configuration@MapperScan(basePackages="com.changdao.skpathcenterapi.mapper.backups", sqlSessionTemplateRef="backupsSqlSessionTemplate")publicclassBackupsDataSourceConfiguration{@Value("${spring.datasource.backups.driver-class-name}")private String driverClassName;@Value("${spring.datasource.backups.url}")private String url;@Value("${spring.datasource.backups.username}")private String username;@Value("${spring.datasource.backups.password}")private String password;@Bean(name="backupsDataSource")public DataSourcedataSource(){
    	DruidDataSource dataSource=newDruidDataSource();
        dataSource.setDriverClassName(this.driverClassName);
        dataSource.setUrl(this.url);
        dataSource.setUsername(this.username);
        dataSource.setPassword(this.password);return dataSource;}@Bean(name="backupsSqlSessionFactory")public SqlSessionFactorysqlSessionFactory(@Qualifier("backupsDataSource") DataSource dataSource)throws Exception{
        SqlSessionFactoryBean bean=newSqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(newPathMatchingResourcePatternResolver().getResources("classpath*:mybatis/backups/*.xml"));return bean.getObject();}@Bean(name="backupsTransactionManager")public DataSourceTransactionManagertransactionManager(@Qualifier("backupsDataSource") DataSource dataSource){returnnewDataSourceTransactionManager(dataSource);}@Bean(name="backupsSqlSessionTemplate")public SqlSessionTemplatesqlSessionTemplate(@Qualifier("backupsSqlSessionFactory") SqlSessionFactory sqlSessionFactory)throws Exception{returnnewSqlSessionTemplate(sqlSessionFactory);}}

6.Spring Data

SpringData为我们提供使用统一的API来对数据访问层进行操作;这主要是Spring Data Commons项目来实现的。Spring Data Commons让我们在使用关系型或者非关系型数据访问技术时都基于Spring提供的统一标准,标准包含了CRUD(创建、获取、更新、删除)、查询、排序和分页的相关操作。
2、统一的Repository接口
Repository<T, ID extends Serializable>:统一接口
RevisionRepository<T, ID extends Serializable, N extends Number & Comparable>:基于乐观锁机制
CrudRepository<T, ID extends Serializable>:基本CRUD操作
PagingAndSortingRepository<T, ID extends Serializable>:基本CRUD及分页
3

  • 作者:小疯子青
  • 原文链接:https://blog.csdn.net/a1773570500/article/details/108045975
    更新时间:2022-10-09 13:59:40