Review JDBC concepts

  • Understand data sources, connections, sessions (DataSource, Connection, SqlSession)
  • What is a database connection pool (Apache Commons – DBCP, C3P0, Druid, HikariCP)
  • Why use database connection pools

summary

The name of the meaning
(DataSource) data source Is the address of the data and some additional configuration properties that can be used to establish a link to the database
Connection The physical link that connects the data sourceDatabase object, Java Everything is an object)
SqlSession A logical link (session in Mybatis) that connects to a data source can correspond to multiple Connections. Select one Connection according to the scheduling policy

Database Connection pool: a collection of connections. Different objects use different connections. Since Connection creation is a waste of resources and time, it is necessary to create more at once.

SpringBoot using JDBC

  • Data source, configured in application.yaml
spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/mybatis? useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai
    driver-class-name: com.mysql.cj.jdbc.Driver
Copy the code

Username and password need to be correct, idea prompt is unreliable

  • Using the JdbcTemplate directly, you are done with the JDBC connection until the result set is returned and the resources are released,
  • The sample
@Controller
public class JDBCController {
    @Autowired
    JdbcTemplate template;
    @GetMapping("/u")
    @ResponseBody
    public List<Map<String, Object>> getlist(){
        String sql="select * from mybatis.blog";
        returntemplate.queryForList(sql); }}Copy the code

exp

  • SpringBoot source code readingIn order to xxxxTemplateThe name of the class’s format indicates that the class has been hosted by Spring to be registered as a bean (@Bean)

The use of the Druid

  • Importing Maven dependencies
<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.5</version>
</dependency>
Copy the code
  • Write application. Yaml
spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/mybatis? useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai
    driver-class-name: com.mysql.cj.jdbc.Driver
    #SpringBoot does not inject these by default, you need to bind them yourself
    #druid Data source proprietary configuration
    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

    # Configure filters to monitor statistics interception, stat: monitor statistics, log4j: logging, wall: defend against SQL injection
    # if an error is allowed, Java. Lang. ClassNotFoundException: org.. Apache Log4j. Properity
    Import log4j dependencies
    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true; druid.stat.slowSqlMillis=500
Copy the code
  • Writing configuration classes (mostly fixed)
@Configuration
public class DruidConfig {
    @Bean
    @ConfigurationProperties("spring.datasource")
    public DataSource druidDataSource(a){
        return new DruidDataSource();
    }
    // Background monitoring
   @Bean
    public ServletRegistrationBean servletRegistrationBean(a){
        ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
        // Background login password
        Map<String, String> initParameter = new HashMap<>();// Set parameters
        initParameter.put("loginUsername"."admin");
        initParameter.put("loginPassword"."123456");
        initParameter.put("allow"."");
        // Both are fixed configurations
        bean.setInitParameters(initParameter);
        return bean;
    }
    @Bean
    public FilterRegistrationBean filter(a){
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());
        Map<String, String> initParameters = new HashMap<>();
       // These things are not counted
        initParameters.put("exclusions" ,"*.js,*.css,/druid/*");
        bean.setInitParameters(initParameters);
        returnbean; }}Copy the code