SpringBoot 从入门到光头 —— 第六章 数据访问

---

1. SQL

1.1. 数据源的自动配置 — HikariDataSource

1. 导入 JDBC 场景

pom.xml

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

其中：

• HikariCP：数据源
• spring-jdbc：JDBC
• spring-tx：事务管理

数据库驱动并没有在其中的原因：Spring 不知道用户要使用的是哪款数据库，因此需要自行导入驱动依赖

pom.xml

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

Spring 已经对驱动版本做好了仲裁（当前默认 8.0.22），且数据库版本需要和驱动版本对应

修改版本号的方法：

1. 直接依赖引入具体版本（Maven 的就近依赖原则）

   <dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
         <version>8.0.22</version>
   </dependency>
   

2. 重新声明版本（Maven 的属性的就近优先原则）

   <properties>
           <java.version>1.8</java.version>
           <mysql.version>8.0.22</mysql.version>
   </properties>
   

2. 分析自动配置

spring-boot-starter-jdbc-2.5.6.pom

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter</artifactId>
  <version>2.5.6</version>
  <scope>compile</scope>
</dependency>
<dependency>
  <groupId>com.zaxxer</groupId>
  <artifactId>HikariCP</artifactId>
  <version>4.0.3</version>
  <scope>compile</scope>
</dependency>

自动配置的类

• DataSourceAutoConfiguration：数据源的自动配置

  • 修改数据源相关的配置：spring.datasource

  • 数据库连接池的配置，是自己容器中没有 DataSource 才自动配置的

  • 底层配置好的连接池是： HikariDataSource

    org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration.PooledDataSourceAutoConfiguration

    @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 {
    
    }
    

• DataSourceTransactionManagerAutoConfiguration： 事务管理器的自动配置

• JdbcTemplateAutoConfiguration： JdbcTemplate 的自动配置，可以来对数据库进行简单的 CRUD 操作

  • 可以通过修改这个配置项 @ConfigurationProperties(prefix = "spring.jdbc") 来修改 JdbcTemplate 配置

  • 容器中已经预先放好了 JdbcTemplate 组件

    ​ org.springframework.boot.autoconfigure.jdbc.JdbcTemplateConfiguration

    @Bean
    @Primary
    JdbcTemplate jdbcTemplate(DataSource dataSource, JdbcProperties properties) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        JdbcProperties.Template template = properties.getTemplate();
        jdbcTemplate.setFetchSize(template.getFetchSize());
        jdbcTemplate.setMaxRows(template.getMaxRows());
        if (template.getQueryTimeout() != null) {
            jdbcTemplate.setQueryTimeout((int) template.getQueryTimeout().getSeconds());
        }
        return jdbcTemplate;
    }
    

• JndiDataSourceAutoConfiguration：JNDI 的自动配置

• XADataSourceAutoConfiguration：分布式事务相关的

3. 修改配置项

application.yaml

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/db_account?useSSL=false&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    username: root
    password: "root"
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver

4. 测试

com.yourname.adminserver.test.AdminServerApplicationTest

@Slf4j
@SpringBootTest
class AdminServerApplicationTests {
    @Autowired
    JdbcTemplate jdbcTemplate;

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

1.2. 使用 Druid 数据源

1.2.1. Druid 官方 GitHub 地址

https://github.com/alibaba/druid

1.2.2. 整合第三方技术的两种方式

• 自定义
• 找相应的 starter

1.2.3. 自定义方式

1. 创建数据源

pom.xml

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

2. 配置数据源

com.yourname.adminserver.config.MyDataSourceConfig

/**
 * @author gregPerlinLi
 * @since 2021-11-06
 */
@Configuration
public class MyDataSourceConfig {
    /**
     * The default autoconfiguration is determined that there is nothing in the container
     * before {@code @ConditionalOnMissingBean(DataSource.class)} is configured
     *
     * @return DataSource
     */
    @Bean
    @ConfigurationProperties(value = "spring.datasource")
    public DataSource dataSource() throws SQLException {

        DruidDataSource druidDataSource = new DruidDataSource();
        // Start Druid data source monitoring and firewall
        druidDataSource.setFilters("stat, wall");
        druidDataSource.setMaxActive(12);
        return druidDataSource;
    }

    /**
     * Configure Druid monitoring page
     *
     * @return A StatView ServletRegistrationBean
     */
    @Bean
    public ServletRegistrationBean<StatViewServlet> servletRegistrationBean() &#123;
        StatViewServlet statViewServlet = new StatViewServlet();
        ServletRegistrationBean<StatViewServlet> servletRegistrationBean = new ServletRegistrationBean<>(statViewServlet, "/druid/*");
        servletRegistrationBean.addInitParameter("loginUsername", "admin");
        servletRegistrationBean.addInitParameter("loginPassword", "123456");
        return servletRegistrationBean;
    &#125;

    /**
     * Used to collect web-jdbc Association monitoring data
     * 
     * @return A WebStatFilter FilterRegistrationBean
     */
    @Bean
    public FilterRegistrationBean<WebStatFilter> webStatFilter() &#123;
        WebStatFilter webStatFilter = new WebStatFilter();
        FilterRegistrationBean<WebStatFilter> filterRegistrationBean = new FilterRegistrationBean<>(webStatFilter);
        filterRegistrationBean.setUrlPatterns(List.of("/*"));
        filterRegistrationBean.addInitParameter("exclusions", "*.js, *.gif, *.jpg, *.png, *.css, *.ico, /druid/*");
        return filterRegistrationBean;
    &#125;
&#125;

3. StatViewServlet

StatViewServlet 的用途包括：

• 提供监控信息展示的html页面
• 提供监控信息的JSON API

@Bean
public ServletRegistrationBean<StatViewServlet> servletRegistrationBean() &#123;
    StatViewServlet statViewServlet = new StatViewServlet();
    ServletRegistrationBean<StatViewServlet> servletRegistrationBean = new ServletRegistrationBean<>(statViewServlet, "/druid/*");
    servletRegistrationBean.addInitParameter("loginUsername", "admin");
    servletRegistrationBean.addInitParameter("loginPassword", "123456");
    return servletRegistrationBean;
&#125;

4. StatFilter

用于统计监控信息，如 SQL 监控、URI 监控等

@Bean
public FilterRegistrationBean<WebStatFilter> webStatFilter() &#123;
    WebStatFilter webStatFilter = new WebStatFilter();
    FilterRegistrationBean<WebStatFilter> filterRegistrationBean = new FilterRegistrationBean<>(webStatFilter);
    filterRegistrationBean.setUrlPatterns(List.of("/*"));
    filterRegistrationBean.addInitParameter("exclusions", "*.js, *.gif, *.jpg, *.png, *.css, *.ico, /druid/*");
    return filterRegistrationBean;
&#125;

使用时需要给数据源中配置如下属性，可以允许多个 Filter，通过 , 分割

druidDataSource.setFilters("stat, wall");

Druid 中的所有 Filter：

别名	Filter类名
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

慢 SQL 记录配置：

@Bean
@ConfigurationProperties(value = "spring.datasource")
public DataSource dataSource() throws SQLException {
    DruidDataSource druidDataSource = new DruidDataSource();
    // Start Druid data source monitoring and firewall
    druidDataSource.setFilters("stat, wall");
    druidDataSource.setMaxActive(12);
    druidDataSource.setProxyFilters(List.of(statFilter()));
    return druidDataSource;
}

public Filter statFilter() {
    StatFilter statFilter = new StatFilter();
    // The SQL executed beyond this time is slow, Ms
    statFilter.setSlowSqlMillis(1000);
    // Print slow log
    statFilter.setLogSlowSql(true);
    // Merge logs
    statFilter.setMergeSql(true);
    return statFilter;
}

1.2.4. 使用官方 starter 方式

1. 引入 druid-starter

pom.xml

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

2. 分析自动配置

com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure

@Configuration
@ConditionalOnClass(DruidDataSource.class)
@AutoConfigureBefore(DataSourceAutoConfiguration.class)
@EnableConfigurationProperties({DruidStatProperties.class, DataSourceProperties.class})
@Import({DruidSpringAopConfiguration.class,
    DruidStatViewServletConfiguration.class,
    DruidWebStatFilterConfiguration.class,
    DruidFilterConfiguration.class})
public class DruidDataSourceAutoConfigure {

    private static final Logger LOGGER = LoggerFactory.getLogger(DruidDataSourceAutoConfigure.class);

    @Bean(initMethod = "init")
    @ConditionalOnMissingBean
    public DataSource dataSource() {
        LOGGER.info("Init DruidDataSource");
        return new DruidDataSourceWrapper();
    }
}

• 拓展配置项 spring.datasource.druid
• DruidSpringAopConfiguration：监控 SpringBean 组件（配置项：spring.datasource.druid.aop-patterns）
• DruidStatViewServletConfiguration：监控页的配置（配置项：spring.datasource.druid.stat-view-servlet，默认开启）
• DruidWebStatFilterConfiguration：Web 监控配置（配置项：spring.datasource.druid.web-stat-filter，默认开启）
• DruidFilterConfiguration：所有 Druid 自己的 Filter 过滤器的配置（配置项：spring.datasource.druid.filter.*）

3. 配置示例：

application.yaml

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/db_account?useSSL=false&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    username: root
    password: "root"
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver

    druid:
      filters: stat, wall, slf4j    # Bottom open function, stat => sql monitoring, wall => firewall
      aop-patterns: com.gregperlinli.adminserver.*    # Monitoring SpringBean
      stat-view-servlet:    # Configure monitoring page function
        enabled: true
        login-username: admin
        login-password: "123456"
        reset-enable: false

      web-stat-filter:    # Monitoring web
        enabled: true
        url-pattern: /*
        exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"
        
      filter:    # Detailed configuration of the filters
        stat:
          enabled: true
          slow-sql-millis: 1000
          log-slow-sql: true
        wall:
          enabled: true
          config:
            drop-table-allow: false

SpringBoot 配置示例：

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

配置项列表：

https://github.com/alibaba/druid/wiki/DruidDataSource配置属性列表

1.3. 整合 MyBatis 操作

https://github.com/mybatis

MyBatis 的官方 starter

pom.xml

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

mybatis-spring-boot-starter 引入的依赖：

1.3.1. 配置模式

• 全局配置文件
• SqlSessionFactory：已经自动配置好
• SqlSession：自动配置了 SqlSessionTemplate 在其中组合了 SqlSession
• @Import(AutoConfiguredMapperScannerRegistrar.class)
• Mapper：只要编写的操作 MyBatis 的接口标注了了 @Mapper 就会被自动扫描进去

org.mybatis.spring.boot.autoconfigure.MybatisAutoConfiguration

@org.springframework.context.annotation.Configuration
@ConditionalOnClass({ SqlSessionFactory.class, SqlSessionFactoryBean.class })
@ConditionalOnSingleCandidate(DataSource.class)
@EnableConfigurationProperties(MybatisProperties.class)    // MyBatis 配置项绑定类
@AutoConfigureAfter({ DataSourceAutoConfiguration.class, MybatisLanguageDriverAutoConfiguration.class })
public class MybatisAutoConfiguration implements InitializingBean {
  
}

可以修改配置文件中 MyBatis 开始的所有配置

配置步骤：

• 导入 MyBatis 官方 starter
• 编写 Mapper 接口，和标准 @Mapper 注解
• 编写 SQL 映射文件并绑定 Mapper 接口
• 在 application.yaml 中指定 Mapper 配置文件的位置，以及指定全局配置文件的信息 （建议配置在 application.yaml 的 mybatis.configuration 中）

示例代码：

application.yaml

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/db_account?useSSL=false&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    username: root
    password: "root"
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    
    druid:
      filters: stat, wall, slf4j  # Bottom open function, stat => sql monitoring, wall => firewall
      aop-patterns: com.gregperlinli.adminserver.*  # Monitoring SpringBean
      stat-view-servlet:  # Configure monitoring page function
        enabled: true
        login-username: admin
        login-password: "123456"
        reset-enable: false

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

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

mybatis:  # Configure Mybatis rules
  mapper-locations: classpath:mapper/*.xml
  configuration:
    map-underscore-to-camel-case: true

mapper/AccountMapper.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.gregperlinli.adminserver.mapper.AccountMapper">
    <select id="getAccount" resultType="com.gregperlinli.adminserver.bean.Account">
        select * from account_tbl where id = #&#123;id&#125;
    </select>
</mapper>

com.yourname.adminserver.bean.Account

/**
 * @author gregPerlinLi
 * @since 2021-11-06
 */
@Data
public class Account {

    public int id;
    public int userId;
    public Double money;
}

com.yourname.adminserver.mapper.AccountMapper

/**
 * @author gregPerlinLi
 * @since 2021-11-06
 */
@Mapper
public interface AccountMapper {

    /**
     * Get account
     *
     * @param id id
     * @return account
     */
    Account getAccount(Integer id);
}

com.yourname.adminserver.service.AccountService

/**
 * @author gregPerlinLi
 * @since 2021-11-06
 */
public interface AccountService {
    /**
     * Get account by id
     *
     * @param id id
     * @return account
     */
    Account getAccountById(Integer id);
}

com.yourname.adminserver.service.impl.AccountServiceImpl

/**
 * @author gregPerlinLi
 * @since 2021-11-06
 */
@Service
public class AccountServiceImpl implements AccountService {
    @Autowired
    AccountMapper accountMapper;

    @Override
    public Account getAccountById(Integer id) {
        return accountMapper.getAccount(id);
    }
}

com.yourname.adminserver.controller.IndexController

/**
 * @author gregPerlinLi
 * @since 2021-11-03
 */
@Slf4j
@Controller
public class IndexController {
    @Autowired
    private AccountService accountService;

    @ResponseBody
    @GetMapping(value = "account")
    public Account getById(@RequestParam("id") Integer id) {
        return accountService.getAccountById(id);
    }
}

注意⚠️：MyBatis 核心配置文件 mybatis-config.xml （或者是 application.yaml 中的 mybatis.config-location 配置）和 application.yaml 中的 mybatis.configuration 配置不能共存！

1.3.2. 注解模式 & 混合模式

示例代码：

com.yourname.adminserver.bean.City

/**
 * @author gregPerlinLi
 * @since 2021-11-06
 */
@Data
public class City {
    private Integer id;
    private String name;
    private String state;
    private String country;
}

com.yourname.adminserver.mapper.CityMapper

/**
 * @author gregPerlinLi
 * @since 2021-11-06
 */
@Mapper
public interface CityMapper {
    /**
     * Get city by id
     *
     * @param id id
     * @return city
     */
    @Select("select * from city where id = #{id}")
    City getById(Integer id);

    /**
     * Inset city
     *
     * @param city city to insert
     */
    void insert(City city);
}

mapper/CityMapper.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.gregperlinli.adminserver.mapper.CityMapper">

    <insert id="insert" useGeneratedKeys="true" keyProperty="id">
        insert into city(`name`, `state`, `country`) values(#&#123;name&#125;, #&#123;state&#125;, #&#123;country&#125;)
    </insert>

</mapper>

com.yourname.adminserver.service.CityService

/**
 * @author gregPerlinLi
 * @since 2021-11-06
 */
public interface CityService {

    /**
     * Get city by id
     *
     * @param id id
     * @return city
     */
    City getCityById(Integer id);
    /**
     * Save city
     *
     * @param city city to insert
     */
    void saveCity(City city);
}

com.yourname.adminserver.service.impl.CityServiceImpl

/**
 * @author gregPerlinLi
 * @since 2021-11-06
 */
@Service
public class CityServiceImpl implements CityService {
    @Autowired
    CityMapper cityMapper;

    @Override
    public City getCityById(Integer id) {
        return cityMapper.getById(id);
    }
    @Override
    public void saveCity(City city) {
        cityMapper.insert(city);
    }
}

com.yourname.adminserver.controller.IndexController

/**
 * @author gregPerlinLi
 * @since 2021-11-03
 */
@Slf4j
@Controller
public class IndexController {
    @Autowired
    private CityService cityService;

    @ResponseBody
    @GetMapping(value = "/city")
    public City getCityById(@RequestParam("id") Integer id) {
        return cityService.getCityById(id);
    }
    @ResponseBody
    @PostMapping(value = "/city")
    public City saveCity(City city) {
        cityService.saveCity(city);
        return city;
    }
}

最佳实战：

• 引入 mybatis-starter
• 在配置 application.yaml 中，指定 mapper-location 位置
• 编写 Mapper 接口并标注 @Mapper 注解
• 简单方法直接注解方式
• 复杂方法编写 mapper.xml 进行绑定映射
• @MapperScan("com.yourname.adminserver.mapper") 简化，其他的接口就可以不用标注 @Mapper 注解

1.4. 整合 MyBatis-Plus 完成 CRUD 操作

1.4.1. 什么是 MyBatis-Plus

MyBatis-Plus（简称 MP）是一个 MyBatis 的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生。

MyBatis-Plus 官网：

https://baomidou.com/

建议安装 MybatisX 插件

1.4.2. 整合 MyBatis-Plus

1. 导入 mybatis-plus-starter

pom.xml

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

2. 自动配置分析

com.baomidou.mybatisplus.autoconfigure.MybatisPlusAutoConfiguration

@Configuration(proxyBeanMethods = false)
@ConditionalOnClass({SqlSessionFactory.class, SqlSessionFactoryBean.class})
@ConditionalOnSingleCandidate(DataSource.class)
@EnableConfigurationProperties(MybatisPlusProperties.class)
@AutoConfigureAfter({DataSourceAutoConfiguration.class, MybatisPlusLanguageDriverAutoConfiguration.class})
public class MybatisPlusAutoConfiguration implements InitializingBean {

}

• MybatisPlusAutoConfiguration：配置类
• MybatisPlusProperties：配置项绑定（配置项：mybatis-plus.*）
• SqlSessionFactory：已自动配置（底层是容器中默认的数据）
• mapperLocations：已自动配置，并且有默认值（classpath:/mapper/**/*.xml，任意一个包的类路径下的所有 mapper 文件夹下任意路径下的所有 XML 文件，建议以后 SQL 映射文件都是放在这个文件中）
• 容器中也自动配置好了 SqlSessionYemplate
• @Mapper 标注的接口也会被自动扫描（建议直接用 @MapperScan(basePackage = "com.yourname.adminserver.mapper") 进行组件扫描）

优点：只需要自己编写的 Mapper 继承 BaseMapper 就可以直接实现较简单的 CRUD 操作

示例代码：

application.yaml

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/db_account?useSSL=false&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    username: root
    password: "root"
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver

    druid:
      filters: stat, wall, slf4j  # Bottom open function, stat => sql monitoring, wall => firewall
      aop-patterns: com.gregperlinli.adminserver.*  # Monitoring SpringBean
      stat-view-servlet:  # Configure monitoring page function
        enabled: true
        login-username: admin
        login-password: "123456"
        reset-enable: false

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

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


mybatis-plus: # Configure Mybatis rules
  mapper-locations: classpath:mapper/*.xml
  configuration:
    map-underscore-to-camel-case: true

com.yourname.adminserver.bean.User

/**
 * @author gregPerlinLi
 * @since 2021-11-03
 */
@NoArgsConstructor
@AllArgsConstructor
@Data
public class User {
    /**
     * All properties should be in the database
     */
    @TableField(exist = false)
    private String userName;
    @TableField(exist = false)
    private String password;

    /**
     * The following are database operations
     */
    private Long id;
    private String name;
    private Integer age;
    private String email;
}

com.yourname.adminserver.mapper.UserMapper

/**
 * @author gregPerlinLi
 * @since 2021-11-06
 */
public interface UserMapper extends BaseMapper<User> &#123;

&#125;

com.gregperlinli.adminserver.test.AdminServerApplicationTests

@Slf4j
@SpringBootTest
class AdminServerApplicationTests {
    @Autowired
    UserMapper userMapper;

    @Test
    void testUserMapper() {
        User user = userMapper.selectById(1L);
        log.info("User info: {}", user);
    }

}

1.4.3. 实现 CRUD 功能

示例代码：

com.yourname.adminserver.bean.User

/**
 * @author gregPerlinLi
 * @since 2021-11-03
 */
@NoArgsConstructor
@AllArgsConstructor
@Data
@TableName("user")
public class User {
    /**
     * All properties should be in the database
     */
    @TableField(exist = false)
    private String userName;
    @TableField(exist = false)
    private String password;

    /**
     * The following are database operations
     */
    private Long id;
    private String nam
    private Integer age;
    private String email;
}

com.yourname.adminserver.mapper.UserMapper

/**
 * @author gregPerlinLi
 * @since 2021-11-06
 */
public interface UserMapper extends BaseMapper<User> &#123;

&#125;

com.yourname.adminserver.service.UserService

/**
 * @author gregPerlinLi
 * @since 2021-11-06
 */
public interface UserService extends IService<User> &#123;

&#125;

com.yourname.adminserver.service.impl.UserServiceImpl

/**
 * @author gregPerlinLi
 * @since 2021-11-06
 */
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService &#123;


&#125;

com.yourname.adminserver.controller.TableController

/**
 * @author gregPerlinLi
 * @since 2021-11-03
 */
@Slf4j
@Controller
public class TableController {
    @Autowired
    UserService userService;

    @GetMapping(value = "/dynamic_table")
    public String dynamicTable(@RequestParam(value = "pn", defaultValue = "1") Integer pn, Model model) {
        // Find out the users in the user table from the database and display
        List<User> list = userService.list();
        // Paging query data
        Page<User> userPage = new Page<>(pn, 2);
        // Paging query results
        Page<User> page = userService.page(userPage, null);
        model.addAttribute("page", page);
        return "table/dynamic_table";
    &#125;
&#125;

com.yourname.adminserver.config.MyBatisConfig

/**
 * @author gregPerlinLi
 * @since 2021-11-06
 */
@Configuration
public class MyBatisConfig {
    /**
     * Paging interceptor
     *
     * @return MyBatisPlusInterceptor
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(DbType.H2);
        paginationInnerInterceptor.setOverflow(true);
        paginationInnerInterceptor.setMaxLimit(500L);
        interceptor.addInnerInterceptor(paginationInnerInterceptor);
        return interceptor;
    }
}

2. NoSQL

Redis （ REmote DIctionary Server ）是一个开源（BSD 许可）的，内存中的数据结构存储系统，它可以用作数据库、缓存和消息中间件。 它支持多种类型的数据结构，如 字符串（strings）， 散列（hashes）， 列表（lists）， 集合（sets）， 有序集合（sorted sets）与范围查询，bitmaps，hyperloglogs 和地理空间（geospatial）索引半径查询。Redis 内置了复制（replication），LUA 脚本（Lua scripting），LRU 驱动事件（LRU eviction），事务（Transactions）和不同级别的磁盘持久化（Persistence），并通过 Redis 哨兵（Sentinel）和自动分区（Cluster）提供高可用性（High availability）。

2.1. Redis 自动配置

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

自动配置：

org.springframework.boot.autoconfigure.data.redis.RedisAutoConfiguration

@Configuration(proxyBeanMethods = false)
@ConditionalOnClass(RedisOperations.class)
@EnableConfigurationProperties(RedisProperties.class)
@Import({ LettuceConnectionConfiguration.class, JedisConnectionConfiguration.class })
public class RedisAutoConfiguration {

}

• RedisAutoConfiguration 自动配置类，RedisProperties 属性类 → spring.redis.xxx 是对 Redis 的配置
• 连接工厂是准备好的。LettuceConnectionConfiguration、JedisConnectionConfiguration
• 自动注入了 RedisTemplate<Object, Object>：xxxTemplate
• 自动注入了 StringRedisTemplate，key，value 都是 String
• key: value
• 底层只要我们使用 StringRedisTemplate、RedisTemplate 就可以操作 Redis

2.2. RedisTemplate 与 Lettuce 客户端

application.yaml

spring:
  redis:
    database: 0
    host: 127.0.0.1
    port: 16379
    timeout: 3000
    client-type: lettuce
    lettuce:
      pool:
        max-active: 10
        min-idle: 5

com.yourname.adminserver.test.AdminServerApplicationTest

@Slf4j
@SpringBootTest
class AdminServerApplicationTests {
    @Autowired
    StringRedisTemplate redisTemplate;

    @Test
    void testRedis() {
        ValueOperations<String, String> operations = redisTemplate.opsForValue();
        operations.set("Hello", "Redis");
        String hello = operations.get("Hello");
        System.out.println(hello);
    &#125;
&#125;

2.3. 切换至 Jedis 客户端

pom.xml

<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>

application.yaml

spring:
  redis:
    database: 0
    host: 127.0.0.1
    port: 16379
    timeout: 3000
    client-type: jedis
    jedis:
      pool:
        max-active: 10
        min-idle: 5

---