springboot数据库配置
知識點和問題匯總
1.对于数据访问层
⽆论是SQL还是NOSQL，SpringBoot默认采⽤整合SpringData的⽅式进⾏统⼀处理，添加⼤量⾃动配置，屏蔽了很多设置。引⼊各种xxxTemplate，xxxRepository来简化我们对数据访问层的操作。对我们来说只需要进⾏简单的设置即可。
Spring-boot-starter-data-XXX
2.jdbc访问mysql
1.在创建项⽬的时候勾选上mysql和jdbc
l中的配置如下：
<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>
l中配置如下
spring:
datasource:
username: rootpassword:123456
url: jdbc:mysql://192.168.15.22:3306/jdbc
driver‐class‐name: sql.jdbc.Driver
效果：
默认是⽤at.jdbc.pool.DataSource作为数据源；
druid连接池配置详解
数据源的相关配置都在DataSourceProperties⾥⾯；
⾃动配置原理：org.springframework.boot.autoconfigure.jdbc：
1、参考DataSourceConfiguration，根据配置创建数据源，默认使⽤Tomcat连接池；可以使⽤pe指定⾃定义的数据源类型；
2、SpringBoot默认可以⽀持；
at.jdbc.pool.DataSource、HikariDataSource、BasicDataSource
3、⾃定义数据源类型
/**
* Generic DataSource configuration.
*/
@ConditionalOnMissingBean(DataSource.class)
@ConditionalOnProperty(name ="pe")
static class Generic {
@Bean
public DataSource dataSource(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.引⼊依赖
<!-- mvnrepository/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.10</version>
</dependency>
2.修改配置⽂件
spring:
datasource:
username: root
password:123456
url: jdbc:mysql://192.168.15.22:3306/jdbc
driver-class-name: sql.jdbc.Driver
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: Sql=true;druid.stat.slowSqlMillis=500
#  schema:
#- classpath:department.sql
3.配置测试：
RunWith(SpringRunner.class)
@SpringBootTest
public class SpringBoot06DataJdbcApplicationTests {
@Autowired
DataSource dataSource;
@Test
public void contextLoads()throws SQLException {
//at.jdbc.pool.DataSource
System.out.Class());
Connection connection = Connection();
System.out.println(connection);
connection.close();
}
}
4.⾃定义配置类
@Configuration
public class DruidConfig {
@ConfigurationProperties(prefix ="spring.datasource")
@Bean
public DataSource druid(){
return new DruidDataSource();
}
}
5.配置监控统
@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.15.21");
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;
}
}
4.整合mybatis–注解⽅式
1.新建议⼀个⼯程
file->project->spring Initializr
web中勾选spring web
sql中勾选jdbc、mysql、mybatis
l中的关键配置依赖
<dependency>
<groupId&batis.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.配置l⽂件
spring:
datasource:
username: root
password:123456
url: jdbc:mysql://192.168.15.22:3306/mybatis?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=true&allowMultiQueries=TRU E
driver-class-name: sql.jdbc.Driver
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: Sql=true;druid.stat.slowSqlMillis=500
initialization-mode: always
schema:
- classpath:sql/department.sql
- classpath:sql/employee.sql
5.配置类
@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.15.21");
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;
}
}
5.数据库中配置⼀些表
在l中进⾏配置
schema:
classpath:sql/department.sql
classpath:sq/employee.sql
6.运⾏查看数据库表是否被创建、查看 druid页⾯是否可以登录
7.创建对应的bean
8.注解配置
创建mapper
@Mapper
public interface DepartmentMapper {
@Select("select * from department where id=#{id}")
public Department getDeptById(Integer id);
@Delete("delect from department where id=#{id}")
public int deleteDeptById(Integer id);
@Options(useGeneratedKeys =true,keyProperty ="id")
@Insert("insert into department(departmentName) values(#{departmentName})")
public int insertDept(Department department);
@Update("update department set departmentName=#{departmentName} where id=#{id}") public int updateDept(Department department);
}
9.编写controller测试

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。