-- 創(chuàng)建furns_ssm
DROP DATABASE IF EXISTS spring_boot;
CREATE DATABASE spring_boot;
USE spring_boot;
-- 創(chuàng)建家居表
CREATE TABLE furn(
`id` INT(11) PRIMARY KEY AUTO_INCREMENT, ## id
`name` VARCHAR(64) NOT NULL, ## 家居名
`maker` VARCHAR(64) NOT NULL, ## 廠商
`price` DECIMAL(11,2) NOT NULL, ## 價(jià)格
`sales` INT(11) NOT NULL, ## 銷量
`stock` INT(11) NOT NULL, ## 庫(kù)存
`img_path` VARCHAR(256) NOT NULL ## 照片路徑
);
-- 初始化家居數(shù)據(jù)
INSERT INTO furn(`id` , `name` , `maker` , `price` , `sales` , `stock` , `img_path`)
VALUES(NULL , ' 北歐風(fēng)格小桌子' , ' 熊貓家居' , 180 , 666 , 7 ,
'assets/images/product-image/1.jpg');
INSERT INTO furn(`id` , `name` , `maker` , `price` , `sales` , `stock` , `img_path`)
VALUES(NULL , ' 簡(jiǎn)約風(fēng)格小椅子' , ' 熊貓家居' , 180 , 666 , 7 ,
'assets/images/product-image/2.jpg');
INSERT INTO furn(`id` , `name` , `maker` , `price` , `sales` , `stock` , `img_path`)
VALUES(NULL , ' 典雅風(fēng)格小臺(tái)燈' , ' 螞蟻家居' , 180 , 666 , 7 ,
'assets/images/product-image/3.jpg');
INSERT INTO furn(`id` , `name` , `maker` , `price` , `sales` , `stock` , `img_path`)
VALUES(NULL , ' 溫馨風(fēng)格盆景架' , ' 螞蟻家居' , 180 , 666 , 7 ,
'assets/images/product-image/4.jpg');
SELECT * FROM furn;

        <!--進(jìn)行數(shù)據(jù)庫(kù)開(kāi)發(fā)，引入data-jdbc starter-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>

        <!--引入mysql的驅(qū)動(dòng)
        1. 說(shuō)明這里沒(méi)有使用版本仲裁  <mysql.version>8.0.26</mysql.version>
        2. 指定的版本是5.1.49
        -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.49</version>
        </dependency>

spring:
  servlet:
    multipart:
      max-file-size: 10MB
      max-request-size: 50MB
  datasource: #配置數(shù)據(jù)源
    # 說(shuō)明: 如果你沒(méi)有指定useSSL=true ,啟動(dòng)項(xiàng)目會(huì)報(bào)紅警告, 環(huán)境的問(wèn)題，要靈活處理
    url: jdbc:mysql://localhost:3306/spring_boot?useSSL=true&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: 自己的密碼
    driver-class-name: com.mysql.jdbc.Driver

public class Furn {
    private Integer id;
    private String name;
    private String maker;
    private BigDecimal price;
    private Integer sales;
    private Integer stock;
    private String imgPath = "assets/images/product-image/1.jpg";
    public Furn(Integer id, String name, String maker, BigDecimal price, Integer sales, Integer stock, String imgPath) {
        this.id = id;
        this.name = name;
        this.maker = maker;
        this.price = price;
        this.sales = sales;
        this.stock = stock;
        this.imgPath = imgPath;
    }
    public Furn() {
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getMaker() {
        return maker;
    }
    public void setMaker(String maker) {
        this.maker = maker;
    }
    public BigDecimal getPrice() {
        return price;
    }
    public void setPrice(BigDecimal price) {
        this.price = price;
    }
    public Integer getSales() {
        return sales;
    }
    public void setSales(Integer sales) {
        this.sales = sales;
    }
    public Integer getStock() {
        return stock;
    }
    public void setStock(Integer stock) {
        this.stock = stock;
    }
    public String getImgPath() {
        return imgPath;
    }
    public void setImgPath(String imgPath) {
        this.imgPath = imgPath;
    }
    @Override
    public String toString() {
        return "Furn{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", maker='" + maker + '\'' +
                ", price=" + price +
                ", sales=" + sales +
                ", stock=" + stock +
                ", imgPath='" + imgPath + '\'' +
                '}';
    }
}

@SpringBootTest
public class ApplicationTests {
    //如果不知道JdbcTemplate請(qǐng)看一下spring的博客文章
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Test
    public void contextLoads() {
        BeanPropertyRowMapper<Furn> rowMapper =
                new BeanPropertyRowMapper<>(Furn.class);
        List<Furn> furns = jdbcTemplate.query("SELECT * FROM `furn`", rowMapper);
        for (Furn furn : furns) {
            System.out.println(furn);
        }
        System.out.println(jdbcTemplate.getDataSource().getClass());
    }
}

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

@Configuration
public class DruidDataSourceConfig {
    //編寫(xiě)方法,注入DruidDataSource
    //還有說(shuō)明一下為什么我們注入自己的DataSource , 默認(rèn)的HiKariDatasource失效?
    //1. 默認(rèn)的數(shù)據(jù)源是如配置? @ConditionalOnMissingBean({ DataSource.class, XADataSource.class })
    //  解讀通過(guò)@ConditionalOnMissingBean({ DataSource.class}) 判斷如果容器有DataSource Bean 就不注入默認(rèn)的HiKariDatasource
    @ConfigurationProperties("spring.datasource")
    @Bean
    public DataSource dataSource() throws SQLException {
        //1. 配置了 @ConfigurationProperties("spring.datasource")
        //   就可以讀取到application.yml的配置
        //2. 我們就不需要調(diào)用DruidDataSource 對(duì)象的setXxx, 會(huì)自動(dòng)關(guān)聯(lián)
        DruidDataSource druidDataSource = new DruidDataSource();
        return druidDataSource;
    }
}

    //配置druid的監(jiān)控頁(yè)功能
    @Bean
    public ServletRegistrationBean statViewServlet() {
        //創(chuàng)建StatViewServlet
        StatViewServlet statViewServlet = new StatViewServlet();
        ServletRegistrationBean<StatViewServlet> registrationBean =
                new ServletRegistrationBean<>(statViewServlet, "/druid/*");
        //設(shè)置init-parameter, 設(shè)置用戶名和密碼
        registrationBean.addInitParameter("loginUsername", "wyx");
        registrationBean.addInitParameter("loginPassword", "666666");
        return registrationBean;
    }

@Controller
public class DruidSqlController {
    @Resource
    private JdbcTemplate jdbcTemplate;
    @ResponseBody
    @GetMapping("/sql")
    public List<Furn> crudDB() {
        BeanPropertyRowMapper<Furn> rowMapper = new BeanPropertyRowMapper<>(Furn.class);
        List<Furn> furns = jdbcTemplate.query("select * from `furn`", rowMapper);
        for (Furn furn : furns) {
            System.out.println(furn);
        }
        return furns;
    }
}

    //配置WebStatFilter, 用于采集web-jdbc關(guān)聯(lián)的監(jiān)控?cái)?shù)據(jù)
    @Bean
    public FilterRegistrationBean webStatFilter() {
        //創(chuàng)建 WebStatFilter
        WebStatFilter webStatFilter = new WebStatFilter();
        FilterRegistrationBean<WebStatFilter> filterRegistrationBean =
                new FilterRegistrationBean<>(webStatFilter);
        //默認(rèn)對(duì)所有的url請(qǐng)求進(jìn)行監(jiān)控
        filterRegistrationBean.setUrlPatterns(Arrays.asList("/*"));
        //排除指定的url
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }

    @ConfigurationProperties("spring.datasource")
    @Bean
    public DataSource dataSource() throws SQLException {
        //1. 配置了 @ConfigurationProperties("spring.datasource")
        //   就可以讀取到application.yml的配置
        //2. 我們就不需要調(diào)用DruidDataSource 對(duì)象的setXxx, 會(huì)自動(dòng)關(guān)聯(lián)
        DruidDataSource druidDataSource = new DruidDataSource();
        //加入監(jiān)控功能, 加入了sql防火墻監(jiān)控
        druidDataSource.setFilters("stat,wall");
        return druidDataSource;
    }

spring:
  servlet:
    multipart:
      max-file-size: 10MB
      max-request-size: 50MB
  datasource: #配置數(shù)據(jù)源
    # 說(shuō)明: 如果你沒(méi)有指定useSSL=true ,啟動(dòng)項(xiàng)目會(huì)報(bào)紅警告, 環(huán)境的問(wèn)題，靈活處理
    url: jdbc:mysql://localhost:3306/spring_boot?useSSL=true&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: 自己的密碼
    driver-class-name: com.mysql.jdbc.Driver
    #配置druid和監(jiān)控功能
    druid:
      stat-view-servlet:
        enabled: true
        login-username: jack
        login-password: 666
        reset-enable: false
      web-stat-filter: #配置web監(jiān)控
        enabled: true
        url-pattern: /*
        exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'
      filter:
        stat: #sql監(jiān)控
          slow-sql-millis: 1000
          log-slow-sql: true
          enabled: true
        wall: #配置sql防火墻
          enabled: true
          config:
            drop-table-allow: false
            select-all-column-allow: false