Spring 全家桶之 Spring Boot 2.6.4（四）- Data Access（Part A JDBC）

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: root
    url: jdbc:mysql://localhost.com:3306/test
复制代码

@SpringBootTest
public class DataApplicationTests {
    @Autowired
    private DataSource dataSource;
    @Test
    public void testGetConnection() throws SQLException {
        System.out.println("数据源类型为：" + dataSource.getClass());
        Connection connection = dataSource.getConnection();
        System.out.println("获取到到数据库连接为：" + connection);
        connection.close();
    }
}
复制代码

@Configuration
public class LilithDataSourceInitializerConfig {
    @Value("classpath:porsche.sql")
    private Resource script;
    @Bean
    public DataSourceInitializer dataSourceInitializer(final DataSource dataSource){
        final DataSourceInitializer initializer = new DataSourceInitializer();
        initializer.setDataSource(dataSource);
        initializer.setDatabasePopulator(databasePopulator());
        return initializer;
    }
    private DatabasePopulator databasePopulator(){
        final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
        populator.addScripts(script);
        //populator.setSeparator("$$");
        return populator;
    }
}
复制代码

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for porsche
-- ----------------------------
DROP TABLE IF EXISTS `porsche`;
CREATE TABLE `porsche` (
  `por_id` int(11) NOT NULL AUTO_INCREMENT,
  `por_name` char(100) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci DEFAULT NULL,
  `por_price` double DEFAULT NULL,
  `por_stock` int(11) DEFAULT NULL,
  PRIMARY KEY (`por_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=gb2312;
-- ----------------------------
-- Records of porsche
-- ----------------------------
BEGIN;
INSERT INTO `porsche` VALUES (2, 'Cayenne', 910000, 30);
INSERT INTO `porsche` VALUES (3, 'Macan', 550000, 40);
INSERT INTO `porsche` VALUES (4, 'Taycay 2022', 880000, 50);
INSERT INTO `porsche` VALUES (5, 'Porsche 911', 1270000, 40);
INSERT INTO `porsche` VALUES (6, 'Porsche 718', 540000, 70);
INSERT INTO `porsche` VALUES (7, '918 Spyder', 13380000, 10);
INSERT INTO `porsche` VALUES (8, 'Cayman', 720000, 30);
INSERT INTO `porsche` VALUES (9, 'Boxster', 670000, 110);
INSERT INTO `porsche` VALUES (10, 'Carrera GT', 6450000, 20);
INSERT INTO `porsche` VALUES (11, 'Taycan Tubo S', 880000, 140);
INSERT INTO `porsche` VALUES (12, 'Taycan 2023', 880000, 120);
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
复制代码

@Data
public class Porsche {
    private Integer porId;
    private String porName;
    private Double porPrice;
    private Integer porStock;
}
复制代码

@Component
public class PorscheMapper {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    public List<Map<String, Object>> selectAll(){
        List<Map<String, Object>> maps = jdbcTemplate.queryForList("SELECT * FROM porsche");
        return maps;
    }
}
复制代码

@SpringBootTest
public class PorscheMapperTest {
    @Autowired
    private PorscheMapper porscheMapper;
    @Test
    public void selectAll() {
        List<Map<String, Object>> maps = porscheMapper.selectAll();
        for (Map<String, Object> map : maps) {
            System.out.println(map);
        }
    }
}
复制代码

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.8</version>
</dependency>
复制代码

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/test
    druid:
      # 初始化大小，最小，最大
      initial-size: 5
      max-active: 100
      min-idle: 1
      # 配置获取连接等待超时的时间
      max-wait: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位毫秒
      time-between-eviction-runs-millis: 60000
      # 配置一个连接在池中最小生存时间
      min-evictable-idle-time-millis: 300000
      # 用来检测连接是否有效的sql 必须是一个查询语句 注意没有此语句以下三个属性不会生效
      validation-query: SELECT 1 FROM DUAL
      # 归还连接时会执行validationQuery检测连接是否有效,开启会降低性能,默认为true
      test-on-return: false
      # 申请连接时会执行validationQuery检测连接是否有效,开启会降低性能,默认为true
      test-on-borrow: true
      # 申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
      test-while-idle: true
      # 配置监控统计拦截的 Filter，去掉后监控界面 SQL 无法统计，wall 用于防火墙
      filters: stat,wall
      # 通过 connection-properties 属性打开 mergeSql 功能；慢 SQL 记录
      connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      # 配置 DruidStatFilter
      web-stat-filter:
        enabled: true
        url-pattern: /*
        exclusions: .js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*
      # 配置 DruidStatViewServlet
      stat-view-servlet:
        url-pattern: /druid/*
        # IP 白名单，没有配置或者为空，则允许所有访问
        #allow: 127.0.0.1
        # IP 黑名单，若白名单也存在，则优先使用
        #deny: 192.168.31.253
        # 禁用 HTML 中 Reset All 按钮
        reset-enable: true
        # 登录用户名/密码
        login-username: root
        login-password: 123
        # 注意 此处必须开启，否则无法访问druid监控面板
        enabled: true
      use-global-data-source-stat: true
复制代码

@RestController
public class PorscheController {
    @Autowired
    private PorscheMapper porscheMapper;
    @GetMapping("/porsche")
    public Map<String,Object> query(){
        List<Map<String, Object>> maps = porscheMapper.selectAll();
        return maps.get(0);
    }
}
复制代码