springboot整合spring-jdbc

前言：springboot 整合spring-jdbc

一、spring-jdbc整合

1、准备测试数据

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(10) NULL DEFAULT NULL,
  `pwd` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `perms` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '张三', 45, '123456', 'user:add');
INSERT INTO `user` VALUES (2, '张三丰', 88, '123456', 'user:add');
INSERT INTO `user` VALUES (3, '郡主', 28, '888888', 'user:add');
INSERT INTO `user` VALUES (6, '李四', 33, '666666', 'user:add,user:update');

SET FOREIGN_KEY_CHECKS = 1;

2、导入依赖

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.4.5</version>
    <relativePath/> <!-- lookup parent from repository -->
</parent>

<dependencies>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.1.22</version>
    </dependency>
    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.17</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <!--jdbc-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <!--mysql-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.18</version>
    </dependency>
</dependencies>

<build>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>
    </plugins>
</build>

3、创建application.yaml配置文件

spring:
  datasource:
    username: root
    driver-class-name: com.mysql.cj.jdbc.Driver
    password: 123456
    url: jdbc:mysql://localhost:3306/springboot_test?useUnicode=true&characterEncoding=utf8&useSSL=false&useSSL=false&serverTimezone=UTC
    type: com.alibaba.druid.pool.DruidDataSource

    druid:
      # 连接池配置
      initial-size: 1
      max-active: 20
      min-idle: 1
      max-wait: 10000
      pool-prepared-statements: true
      max-open-prepared-statements: 20
      validation-query: SELECT 1 FROM DUAL
      validation-query-timeout: 5000
      test-on-borrow: false
      test-on-return: false
      test-while-idle: true
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 30000
      max-evictable-idle-time-millis: 60000
      removeAbandoned: true
      removeAbandonedTimeout: 1800

      filters: stat,wall,log4j
      max-pool-prepared-statement-per-connection-size: 20
      use-global-data-source-stat: true
      connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

4、创建pojo类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {

    private int id;
    private String name;
    private int age;
    private String pwd;
}

5、创建controller类

@RestController
public class JdbcController {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @RequestMapping("/user/getList")
    public List<Map<String,Object>> getList(){
        String sql = "select * from user";
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
        return mapList;
    }

    @RequestMapping("/add")
    public String add(){
        String sql = "insert into user(name,age,pwd) values ('林志颖',45,123456)";
        int add = jdbcTemplate.update(sql);
        return "add::"+add;
    }

    @RequestMapping("/update/{id}")
    public String update(@PathVariable("id") int id){
        String sql = "update user set name = ?,age = ? where id ="+id;
        Object[] objects = new Object[2];
        objects[0] = "林志颖1";
        objects[1] = 46;
        int update = jdbcTemplate.update(sql,objects);
        return "update::"+update;
    }

    @RequestMapping("/delete/{id}")
    public String delete(@PathVariable("id") int id){
        String sql = "delete from user where id = "+id;
        int delete = jdbcTemplate.update(sql);
        return "delete::"+delete;
    }

}

6、启动springboot应用，测试

二、查看druid监控页面

1、创建配置类

@Configuration
public class DruidConfig {

    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druidDataSource(){
        return new DruidDataSource();
    }

    //druid后台监控: web.xml  ServletRegistrationBean
    //springboot内置了servlet容器，可以用ServletRegistrationBean替换
    @Bean
    public ServletRegistrationBean statViewServlet(){
        ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(),"/druid/*");

        //后台需要有人登录，账号密码设置
        Map<String, String> initParameters = new HashMap<>();
        //增加配置
        initParameters.put("loginUsername","admin");  //登录key是固定的  loginUsername loginPassword
        initParameters.put("loginPassword","123456");
        //允许谁可以访问
        initParameters.put("allow","");
        bean.setInitParameters(initParameters);  //设置初始化参数
        return bean;
    }

    //过滤器
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());
        //可以过滤哪些请求呢
        Map<String, String> initParameters = new HashMap<>();
        //这些东西不进行统计
        initParameters.put("exclusions","*.js,*.css,/druid/*");
        bean.setInitParameters(initParameters);
        return bean;
    }
}

2、访问页面

在浏览器输入http://127.0.0.1:8080/druid/login.html 访问

登录密码为配置类中配置的账号密码。

完毕！！！