SQL

1. Automatic configuration of data source

HikariDataSource data source

1. Importing JDBC scenarios

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>
        

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-g2EOaJTt-1627625553738)(D: \ notes \ typroa_images06366100317-5e0199fa-6709-4d32-bce3-bb262e2e5e6a.png)]

Database driven?

Why is the JDBC scenario imported and the official driver not imported? – > Officials don't know what database we're going to operate next.

The database version corresponds to the driver version

Default version:<mysql.version>8.0.22</mysql.version>
Want to modify the version
1,Import specific version directly( maven (principle of proximity)
	        <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
				<version>5.1.49</version>
       		 </dependency>
2,Redeclare version( maven (principle of proximity priority of attributes)
    <properties>
        <java.version>1.8</java.version>
        <mysql.version>5.1.49</mysql.version>
    </properties>

2. Analysis auto configuration

Automatically configured classes:

1. DataSourceAutoConfiguration: automatic configuration of data sources

• 1. It is bound to modify the configuration related to the data source: spring datasource
  2. The configuration of database connection pool is automatically configured only when there is no DataSource in its own container
  3. The bottom automatically configured connection pool is HikariDataSource. (because the HikariDataSource dependency was introduced when the JDBC initiator was introduced)

•     @Configuration(proxyBeanMethods = false)
      @Conditional(PooledDataSourceCondition.class)
      @ConditionalOnMissingBean({ DataSource.class, XADataSource.class })
      @Import({ DataSourceConfiguration.Hikari.class, DataSourceConfiguration.Tomcat.class,
              DataSourceConfiguration.Dbcp2.class, DataSourceConfiguration.OracleUcp.class,
              DataSourceConfiguration.Generic.class, DataSourceJmxConfiguration.class })
      protected static class PooledDataSourceConfiguration
  

1. DataSourceTransactionManagerAutoConfiguration: automatic configuration of the transaction manager
2. JdbcTemplateAutoConfiguration: the automatic configuration of JdbcTemplate, which can be used to crud the database

• • You can modify the configuration item: @ ConfigurationProperties(prefix = "spring.jdbc") to modify the JdbcTemplate

  • There is this component in the container

    @Bean
    @Primary   
    JdbcTemplate
    

1. JndiDataSourceAutoConfiguration: automatic configuration of jndi
2. XADataSourceAutoConfiguration: distributed transaction related

3. Modify configuration item

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/db_account
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver
    type: com.zaxxer.hikari.HikariDataSource #Data source type  
  jdbc:
    template: 
      query-timeout: 3

4. Testing

@Slf4j
@SpringBootTest
class Boot05WebAdminApplicationTests {

    @Autowired
    JdbcTemplate jdbcTemplate;


    @Test
    void contextLoads() {

//        jdbcTemplate.queryForObject("select * from account_tbl")
//        jdbcTemplate.queryForList("select * from account_tbl",)
        Long aLong = jdbcTemplate.queryForObject("select count(*) from account_tbl", Long.class);
        log.info("Total records:{}",aLong);
    }

}

2. Use Druid data source

druid official github address

https://github.com/alibaba/druid

We use Druid mainly because it has monitoring function.

Two ways to integrate third-party technology

• custom
• Find starter

1. User defined method

1. Create data source

Import dependency:

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

Previously spring configured data sources:

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
        destroy-method="close">
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
        <property name="maxActive" value="20" />
        <property name="initialSize" value="1" />
        <property name="maxWait" value="60000" />
        <property name="minIdle" value="1" />
        <property name="timeBetweenEvictionRunsMillis" value="60000" />
        <property name="minEvictableIdleTimeMillis" value="300000" />
        <property name="testWhileIdle" value="true" />
        <property name="testOnBorrow" value="false" />
        <property name="testOnReturn" value="false" />
        <property name="poolPreparedStatements" value="true" />
        <property name="maxOpenPreparedStatements" value="20" />

Now configure the data source for springboot:

@ConfigurationProperties("spring.datasource") //Property is bound to the configuration file
@Configuration
public class MyDataSourceConfig {
    //The default is auto configuration, which means that @ ConditionalOnMissingBean(DataSource.class) will be configured only if it is judged that there is no in the container
    @Bean
    public DataSource dataSource() {
        DruidDataSource druidDataSource = new DruidDataSource();
        //druidDataSource.setUrl("..");
        //...
        return druidDataSource;
    }
}

2,StatViewServlet

The purpose of StatViewServlet of Druid data source includes: (monitoring function)

• Provide html page for monitoring information display

  [the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-ZFoBk8qf-1627625553739)(D: \ notes \ typroa_images\image-20210611152529319.png)]

• JSON API that provides monitoring information

Opening mode:

    <servlet>
        <servlet-name>DruidStatView</servlet-name>
        <servlet-class>com.alibaba.druid.support.http.StatViewServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>DruidStatView</servlet-name>
        <url-pattern>/druid/*</url-pattern>
    </servlet-mapping>

springboot configuration:

/**
* Configure the monitoring page function of druid
*/
@Bean
public ServletRegistrationBean statViewServlet() {
    StatViewServlet statViewServlet = new StatViewServlet();
    ServletRegistrationBean<StatViewServlet> registrationBean = new ServletRegistrationBean<StatViewServlet>(statViewServlet, "/druid/*");
    return registrationBean
}

3,StatFilter

Used for statistical monitoring information; Such as SQL monitoring and URI monitoring

The following attributes need to be configured in the data source:; Multiple can be allowed filter，Multiple use, segmentation; For example:

<property name="filters" value="stat,slf4j" />

All filter s in the system:

alias	Filter class name
default	com.alibaba.druid.filter.stat.StatFilter
stat	com.alibaba.druid.filter.stat.StatFilter
mergeStat	com.alibaba.druid.filter.stat.MergeStatFilter
encoding	com.alibaba.druid.filter.encoding.EncodingConvertFilter
log4j	com.alibaba.druid.filter.logging.Log4jFilter
log4j2	com.alibaba.druid.filter.logging.Log4j2Filter
slf4j	com.alibaba.druid.filter.logging.Slf4jLogFilter
commonlogging	com.alibaba.druid.filter.logging.CommonsLogFilter

Slow SQL record configuration

<bean id="stat-filter" class="com.alibaba.druid.filter.stat.StatFilter">
    <property name="slowSqlMillis" value="10000" />
    <property name="logSlowSql" value="true" />
</bean>

use slowSqlMillis Define slow SQL Duration of

springboot configuration example:

/**
* WebStatFilter It is used to collect web JDBC Association monitoring data.
*/
@Bean
public FilterRegistrationBean webStatFilter() {
    WebStatFilter webStatFilter = new WebStatFilter();
    FilterRegistrationBean<WebStatFilter> filterRegistrationBean = new FilterRegistrationBean<> (webStatFilter);
    filterRegistrationBean.setUrlPatterns(Arrays.asList("/*"));
    filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
    return filterRegistrationBean;
}

2. Use the official starter mode

1. Introducing Druid starter

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.17</version>
        </dependency>

2. Analysis auto configuration

• Extension configuration item spring datasource. druid
• DruidSpringAopConfiguration.class to monitor the of SpringBean; Configuration item: spring datasource. druid. aop-patterns
• DruidStatViewServletConfiguration.class, monitoring page configuration: spring datasource. druid. stat-view-servlet； Default on
• DruidWebStatFilterConfiguration.class, web monitoring configuration; spring.datasource.druid.web-stat-filter； Default on
• DruidFilterConfiguration.class}) configuration of all Druid filter s

    private static final String FILTER_STAT_PREFIX = "spring.datasource.druid.filter.stat";
    private static final String FILTER_CONFIG_PREFIX = "spring.datasource.druid.filter.config";
    private static final String FILTER_ENCODING_PREFIX = "spring.datasource.druid.filter.encoding";
    private static final String FILTER_SLF4J_PREFIX = "spring.datasource.druid.filter.slf4j";
    private static final String FILTER_LOG4J_PREFIX = "spring.datasource.druid.filter.log4j";
    private static final String FILTER_LOG4J2_PREFIX = "spring.datasource.druid.filter.log4j2";
    private static final String FILTER_COMMONS_LOG_PREFIX = "spring.datasource.druid.filter.commons-log";
    private static final String FILTER_WALL_PREFIX = "spring.datasource.druid.filter.wall";

3. Configuration example

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/db_account
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver

    druid:
      aop-patterns: com.atguigu.admin.*  #Monitoring springbeans
      filters: stat,wall     # Bottom open function, stat (sql monitoring), wall (firewall)

      stat-view-servlet:   # Configure monitoring page function
        enabled: true
        login-username: admin
        login-password: admin
        resetEnable: false

      web-stat-filter:  # Monitoring web
        enabled: true
        urlPattern: /*
        exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'


      filter:
        stat:    # Detailed configuration of stat in the above filters
          slow-sql-millis: 1000
          logSlowSql: true
          enabled: true
        wall:
          enabled: true
          config:
            drop-table-allow: false

SpringBoot configuration example

https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter

List of configuration items:

https://github.com/alibaba/druid/wiki/DruidDataSource%E9%85%8D%E7%BD%AE%E5%B1%9E%E6%80%A7%E5%88%97%E8%A1%A8

3. Integrate MyBatis operations

https://github.com/mybatis

starter

• SpringBoot's official Starter: spring boot Starter-*

• Third party: * - spring boot starter

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

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-zK2PiKK2-1627625553741)(D: \ notes \ typroa_images06704096118-53001250-a04a-4210-80ee-6de6a370be2e.png)]

1. Configuration mode

1. Global profile
2. SqlSessionFactory: Auto configured
3. SqlSession: SqlSessionTemplate is automatically configured and SqlSession is combined
4. @Import(AutoConfiguredMapperScannerRegistrar.class)；
5. Mapper: as long as the interface of MyBatis is marked with @ mapper, it will be automatically scanned

@EnableConfigurationProperties(MybatisProperties.class) //MyBatis configuration item binding class.
@AutoConfigureAfter({ DataSourceAutoConfiguration.class, MybatisLanguageDriverAutoConfiguration.class })
public class MybatisAutoConfiguration{}
===============================================
@ConfigurationProperties(prefix = "mybatis")
public class MybatisProperties

You can modify all the settings in the configuration file that start with mybatis;

# Configure mybatis rules
mybatis:
  config-location: classpath:mybatis/mybatis-config.xml  #Global profile location
  mapper-locations: classpath:mybatis/mapper/*.xml  #sql mapping file location
 

Mapper interface - > bind 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.admin.mapper.AccountMapper">
<!--    public Account getAcct(Long id); -->
    <select id="getAcct" resultType="com.atguigu.admin.bean.Account">
        select * from  account_tbl where  id=#{id}
    </select>
</mapper>

Configure private configuration; mybatis. All under configuration is equivalent to changing the values in the mybatis global configuration file

# Configure mybatis rules
mybatis:
#  config-location: classpath:mybatis/mybatis-config.xml
  mapper-locations: classpath:mybatis/mapper/*.xml
  configuration:
    map-underscore-to-camel-case: true
    
 You can not write the global configuration file. The configuration of all global configuration files are placed in the configuration Configuration item, but the two cannot exist at the same time.

Step summary:

1. Import mybatis official starter
2. Write Mapper interface. Standard @ Mapper annotation
3. Write the sql mapping file and bind the mapper interface
4. In application Specify the location of Mapper configuration file and the information of global configuration file in yaml (recommended; configuration in mybatis.configuration)

2. Annotation mode

@Mapper
public interface CityMapper {

    @Select("select * from city where id=#{id}")
    public City getById(Long id);

    public void insert(City city);

}

3. Mixed mode (best practice)

@Mapper
public interface CityMapper {

    @Select("select * from city where id=#{id}")
    public City getById(Long id);

    public void insert(City city);

}

Best practice:

• Introducing mybatis starter

• Configure application In yaml, specify the mapper location

• Write Mapper interface and annotate @ Mapper annotation

• Simple method direct annotation method

• Write mapper with complex method XML binding mapping

• @MapperScan("com.atguigu.admin.mapper") is simplified, and other interfaces do not need to be annotated with @ mapper annotation

  @MapperScan("....")
  @SpringBootApplication
  public class BootWebAdminApplication {
      public static void main(String[] args) {
          SpringApplication.run(BootWebAdminApplication.class, args);
      }
  }
  

4. Integrate mybatis plus to complete CRUD

1. What is mybatis plus

MyBatis-Plus MP is an abbreviation MyBatis On the basis of MyBatis, the enhancement tool is only enhanced without change. It is born to simplify development and improve efficiency.

mybatis plus official website

It is recommended to install the MybatisX plug-in

2. Integrate mybatis plus

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.1</version>
        </dependency>

Auto configuration:

1. The MybatisPlusAutoConfiguration configuration class is bound to the MybatisPlusProperties configuration item. Mybatis plus: xxx is the customization of * * * * mybatis plus
2. SqlSessionFactory is automatically configured. The bottom layer is the default data source in the container
3. Maperlocations is automatically configured. There are default values. classpath*:/mapper/**/*.xml； All XML in any path under all mapper folders under the classpath of any package are sql mapping files. It is recommended to put the sql mapping file under mapper in the future**
4. SqlSessionTemplate is also automatically configured in the container
5. @The interface marked by mapper will also be automatically scanned; It is recommended to directly scan @ MapperScan("com.atguigu.admin.mapper") in batches

advantage:

• As long as our Mapper inherits BaseMapper, it can have crud capability

3. CRUD function

    @GetMapping("/user/delete/{id}")
    public String deleteUser(@PathVariable("id") Long id,
                             @RequestParam(value = "pn",defaultValue = "1")Integer pn,
                             RedirectAttributes ra){

        userService.removeById(id);

        ra.addAttribute("pn",pn);
        return "redirect:/dynamic_table";
    }


    @GetMapping("/dynamic_table")
    public String dynamic_table(@RequestParam(value="pn",defaultValue = "1") Integer pn,Model model){
        //Traversal of table contents
//        response.sendError
//     List<User> users = Arrays.asList(new User("zhangsan", "123456"),
//                new User("lisi", "123444"),
//                new User("haha", "aaaaa"),
//                new User("hehe ", "aaddd"));
//        model.addAttribute("users",users);
//
//        if(users.size()>3){
//            throw new UserTooManyException();
//        }
        //Find out the users in the user table from the database for display

        //Construct paging parameters
        Page<User> page = new Page<>(pn, 2);
        //Call page to page
        Page<User> userPage = userService.page(page, null);


//        userPage.getRecords()
//        userPage.getCurrent()
//        userPage.getPages()


        model.addAttribute("users",userPage);

        return "table/dynamic_table";
    }

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper,User> implements UserService {


}

public interface UserService extends IService<User> {

}

NoSQL

Redis is an open source (BSD licensed) in memory data structure storage system, which can be used as database, cache and message middleware. It supports many types of data structures, such as strings， hashes， Lists (lists)， sets， sorted sets And range query, bitmaps， hyperloglogs and geospatial Index radius query. Redis has built-in replication，Lua scripting， LRU event，transactions And different levels of Disk persistence , and passed Redis Sentinel And automatic Partition (Cluster) Provide high availability.

1. Redis auto configuration

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
        </dependency>

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-6QVtRQmE-1627625553743)(D: \ notes \ typroa_images06745732785-17d1227a-75b9-4f00-a3f1-7fc4137b5113.png)]

Auto configuration:

1. RedisAutoConfiguration autoconfiguration class. RedisProperties property class -- > spring redis. XXX is the configuration of redis
2. The connection factory is ready. LettuceConnectionConfiguration,JedisConnectionConfiguration
3. Redistemplate < object, Object >: xxtemplate is automatically injected;
4. String redistemplate is automatically injected; k: v are all strings
5. key: value
6. At the bottom layer, we can operate redis as long as we use * * StringRedisTemplate and * * RedisTemplate

Setting up redis environment

1. Alicloud pays per volume redis. Classic network

2. Public network connection address of redis application

3. Modify the white list to allow 0.0.0.0/0 access

2. RedisTemplate and lattice

    @Test
    void testRedis(){
        ValueOperations<String, String> operations = redisTemplate.opsForValue();

        operations.set("hello","world");

        String hello = operations.get("hello");
        System.out.println(hello);
    }

3. Switch to jedis

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
        </dependency>

		<!--  Import jedis-->
        <dependency>
            <groupId>redis.clients</groupId>
            <artifactId>jedis</artifactId>
        </dependency>

spring:
  redis:
      host: r-bp1nc7reqesxisgxpipd.redis.rds.aliyuncs.com
      port: 6379
      password: lfy:Lfy123456
      client-type: jedis
      jedis:
        pool:
          max-active: 10
      operations.set("hello","world");

        String hello = operations.get("hello");
        System.out.println(hello);
    }

3. Switch to jedis

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
        </dependency>

		<!--  Import jedis-->
        <dependency>
            <groupId>redis.clients</groupId>
            <artifactId>jedis</artifactId>
        </dependency>

spring:
  redis:
      host: r-bp1nc7reqesxisgxpipd.redis.rds.aliyuncs.com
      port: 6379
      password: lfy:Lfy123456
      client-type: jedis
      jedis:
        pool:
          max-active: 10