java SpringBoot（十二）数据库访问Ddemo 使用Druid跟MyBatis简单实例

小咪咪 2022-10-15 14:54 89阅读 0赞

**今天我们简单使用Druid作为数据库操作的数据源 Druid可以提供对数据库访问、慢sql、防火墙、sql注入等很多监控功能**

**使用MyBatis数据库操作工具对数据库进行简单操作。**

**前提：**

我们都知道用SpringBoot操作数据库需要导入JDBC包

JDBC包帮我们底层配置好的连接池是：**HikariDataSource**

**我们想将数据源切换成Druid，导入Druid包查看源码**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzcxNTY3_size_16_color_FFFFFF_t_70][]

在 DataSourceAutoConfiguration之前加载，所以数据源就切换成我们的 **Druid**

## **Demo：** ##

**1、首先pom修改，导入jdbc、mysql驱动、druid以及MyBatis**

<dependency>
    			<groupId>com.alibaba</groupId>
    			<artifactId>druid-spring-boot-starter</artifactId>
    			<version>1.1.17</version>
    		</dependency>
    		<dependency>
    			<groupId>org.mybatis.spring.boot</groupId>
    			<artifactId>mybatis-spring-boot-starter</artifactId>
    			<version>2.1.4</version>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-data-jdbc</artifactId>
    		</dependency>
    		<dependency>
    			<groupId>mysql</groupId>
    			<artifactId>mysql-connector-java</artifactId>

**2、修改配置文件**

server:
      port: 8081 #spring 启动端口
    spring:
      datasource:
        url: jdbc:mysql://localhost:3306/test #mysql 地址
        username: root
        password: 123456
        driver-class-name: com.mysql.jdbc.Driver #指定驱动
    
        druid:
          filters: stat,wall,slf4j #指定开启的功能
    
          stat-view-servlet: #监控页面配置
            enabled: true #开启
            login-username: admin #账号 密码
            login-password: admin
            reset-enable: false
          web-stat-filter: #web 过滤器
            enabled: true #开启
            url-pattern: /* #监控的页面
            exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*' #解除监控的页面
          filter:
            stat:
              slow-sql-millis: 1000 #慢sql阈值 ms
              log-slow-sql: true #日志记录慢sql
              enabled: true #开启
            wall:
              enabled: true #开启防火墙
              config:
                drop-table-allow: false #禁止删除表的操作
    
    mybatis:
      mapper-locations: classpath:mapper/*xml #mapper扫描路径
      type-aliases-package: com.example.demo.bean #类型所在目录
      configuration:
        map-underscore-to-camel-case: true #开启驼峰字段转换

**3、开启数据库 查看数据表**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzcxNTY3_size_16_color_FFFFFF_t_70 1][]

**4、根据数据库数据内容创建bean包装类**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzcxNTY3_size_16_color_FFFFFF_t_70 2][]

**这里利用了 lombok 的@Data 实现自动实现了构造函数、get set以及toString**

package com.example.demo.bean;
    
    import lombok.Data;
    
    @Data
    public class Student {
        private Long id;
        private Integer score;
        private String name;
    }

**5、编写mapper**

package com.example.demo.mapper;
    
    import com.example.demo.bean.Student;
    import org.apache.ibatis.annotations.Mapper;
    
    @Mapper
    public interface StudentMapper {
    
        public Student getStudent(Long id);
    }

**6、创建该mapper的xml配置文件（这里要放在我们配置的目录下边，这样才能扫描到）**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzcxNTY3_size_16_color_FFFFFF_t_70 3][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzcxNTY3_size_16_color_FFFFFF_t_70 4][]

<?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.example.demo.mapper.StudentMapper">
    
        
        <select id="getStudent" resultType="com.example.demo.bean.Student">
            select * from  student where  id=#{id}
        </select>
    
        
    
    </mapper>

1、是命名空间 直接复制引用即可

2、指定mapper里边的方法

3、输出的类型

4、sql语句

**7、编写service层**

**取出我们注入的mapper 利用mapper实现sql查询**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzcxNTY3_size_16_color_FFFFFF_t_70 5][]

package com.example.demo.service;
    
    import com.example.demo.bean.Student;
    import com.example.demo.mapper.StudentMapper;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    @Service
    public class StudentService {
    
        @Autowired
        StudentMapper studentMapper;
    
        public Student getStudentById(Long id){return studentMapper.getStudent(id);}
    }

**8、最后编写controller**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzcxNTY3_size_16_color_FFFFFF_t_70 6][]

package com.example.demo.controller;
    
    import com.example.demo.service.StudentService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RequestParam;
    import org.springframework.web.bind.annotation.ResponseBody;
    
    @Controller
    public class SqlConTestController {
    
        @Autowired
        JdbcTemplate jdbcTemplate;
    
        @Autowired
        StudentService studentService;
    
        @ResponseBody
        @GetMapping("/query")
        public String query(){
            Long count = jdbcTemplate.queryForObject("select count(*) from student", Long.class);
            return count.toString();
        }
    
        @ResponseBody
        @GetMapping("/query2")
        public String query2(@RequestParam("id") Long id){
            return studentService.getStudentById(id).toString();
        }
    }

**9、启动程序 访问本地8081 实现查询**

![20210527192028483.png][]

**10、查看Druid的监控数据**

**访问 **[http://localhost:8081/druid/index.html][http_localhost_8081_druid_index.html]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzcxNTY3_size_16_color_FFFFFF_t_70 7][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzcxNTY3_size_16_color_FFFFFF_t_70]: /images/20221014/204f3fd2b2ae4df5894775c9b20bb59b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzcxNTY3_size_16_color_FFFFFF_t_70 1]: /images/20221014/e6a101289684424c801bdd182ce4e357.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzcxNTY3_size_16_color_FFFFFF_t_70 2]: /images/20221014/4c707a9213e54ac7ae7da197febdb852.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzcxNTY3_size_16_color_FFFFFF_t_70 3]: /images/20221014/115b38f524a449d09e89e91c190d53b7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzcxNTY3_size_16_color_FFFFFF_t_70 4]: /images/20221014/ef84a5977acd4e10a14c422874f08fce.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzcxNTY3_size_16_color_FFFFFF_t_70 5]: /images/20221014/40a268ced4d44e1a9f2f2309b501060a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzcxNTY3_size_16_color_FFFFFF_t_70 6]: /images/20221014/e89086f9663247a38c353264276042cc.png
[20210527192028483.png]: /images/20221014/356d6d4516b544e6b9d544c273ad2ff8.png
[http_localhost_8081_druid_index.html]: http://localhost:8081/druid/index.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzcxNTY3_size_16_color_FFFFFF_t_70 7]: /images/20221014/0bd3f659ee734d19bfc112d69af72a78.png