SpringBoot操作数据库

我不是女神ヾ 2021-08-28 02:35 410阅读 0赞

## SpringData简介 ##

对于数据访问层，无论是SQL还是NoSQL，在SpringBoot 底层都是采用SpringData来进行封装的。  
官网地址：https://spring.io/projects/spring-data  
![官网][format_png]

使用IDEA构建项目时，我们可以勾选SpringData相关依赖。  
![勾选SpringData相关依赖][SpringData]  
如上图界面所示，Spring提供了很多工具供我们使用，它封装了大量的模板，在`Template Engines`选项中可以勾选需要的模板引擎依赖，IDEA会自动帮我们导入。  
JDBC我们选择了Spring Data JDBC，对应的依赖为`spring-boot-start-data-jdbc`，它本质上和`spring-boot-start-jdbc`依赖是一样的，点击依赖，我们发现它的底层使用的还是`spring-boot-start-jdbc`依赖。  
与此同时，可以发现SpringBoot2.x默认的数据源是`HikariCP`,其maven依赖如下：

<dependency>
      <groupId>com.zaxxer</groupId>
      <artifactId>HikariCP</artifactId>
      <version>3.4.2</version>
      <scope>compile</scope>
    </dependency>

我们还导入了MySQL驱动，需要注意的是MySQL驱动如果不指定版本号，默认使用8.0版本，其maven依赖如下：

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

如果不想使用默认版本，加上version即可。

## 集成JDBC ##

> 导入数据库

1、创建springboot数据库。

CREATE DATABASE /*!32312 IF NOT EXISTS*/`springboot` 
    /*!40100 DEFAULT CHARACTER SET utf8 */;

2、创建员工表，插入若干数据。

CREATE TABLE `employee` (
      `id` INT(5) NOT NULL AUTO_INCREMENT COMMENT '雇员id',
      `last_name` VARCHAR(100) NOT NULL COMMENT '名字',
      `email` VARCHAR(100) NOT NULL COMMENT '邮箱',
      `gender` INT(2) NOT NULL COMMENT '性别1 男, 0 女',
      `department` INT(3) NOT NULL COMMENT '部门id',
      `birth` DATETIME NOT NULL COMMENT '生日',
      PRIMARY KEY (`id`)
    ) ENGINE=INNODB AUTO_INCREMENT=1006 DEFAULT CHARSET=utf8;

3、创建部门表，插入若干数据。

CREATE TABLE `department` (
      `id` INT(3) NOT NULL AUTO_INCREMENT COMMENT '部门id',
      `department_name` VARCHAR(20) NOT NULL COMMENT '部门名字',
      PRIMARY KEY (`id`)
    ) ENGINE=INNODB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8;

> 创建项目

1、导入JDBC配置和MySQL驱动。

<dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jdbc</artifactId>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <scope>runtime</scope>
    </dependency>

2、编写配置文件，注意MySQL8.0以上需要配置时区。

spring:
      datasource:
        username: root
        password: 123456
        #注意MySQL8.0以上需要配置时区
        url: jdbc:mysql://localhost:3306/springboot?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
        driver-class-name: com.mysql.cj.jdbc.Driver

小技巧：如果记不清楚url，可以在IDEA的Database窗口中测试连接一下，测试成功后下方会显示完整的数据库连接地址。  
![测试连接地址][format_png 1]  
3、测试连接。

@SpringBootTest
    class SpringBootDataDemoApplicationTests {
    
        @Autowired
        DataSource dataSource;
    
        @Test
        void contextLoads() throws SQLException {
            //查看默认数据源  class com.zaxxer.hikari.HikariDataSource
            System.out.println(dataSource.getClass());
            Connection connection=dataSource.getConnection();
            //connection代表数据源  HikariProxyConnection@64068997 wrapping com.mysql.cj.jdbc.ConnectionImpl@2ae62bb6
            System.out.println(connection);
            //真实开发更多会去使用数据源
            connection.close();
        }
    }

> 自动配置核心类

*  `DataSourceConfiguration`：自动配置的数据源
 *  `DataSourceAutoConfiguration`：自动配置类
 *  `DataSourceProperties`：配置文件绑定

`HikariDataSource`号称当前Java Web环境中速度最快的数据源，和Druid相比，它的效率会更高一点。  
不同的数据源拥有不同的配置，详细配置可以查看对应数据源的DataSource源码。  
4、测试CRUD，直接编写Controller进行测试。我们可以使用JdbcTemplate来操作数据库，它会自动帮我们配置需要的环境，并且封装了很多操作方法，使用起来十分简单。

@RestController
    @RequestMapping("/jdbc")
    public class JdbcController {
        //Spring的核心思想之一：xxxTemplate
        //xxxTemplate会自动帮我们配置完需要的环境，拿来即用
        @Autowired
        JdbcTemplate jdbcTemplate;
    
        @GetMapping("/userList")
        public List<Map<String,Object>> userList(){
            String sql="select * from employee";
            List<Map<String,Object>> lists=jdbcTemplate.queryForList(sql);
            return lists;
        }
    
        //JdbcTemplate会自动帮我们处理事务
        @GetMapping("/add")
        public String addUser(){
            String sql="insert into employee(last_name, email, gender, department, birth)" +
                    "values ('wunian','1796608443@qq.com',1,303,'"+new Date().toLocaleString()+"')";
            jdbcTemplate.update(sql);
            return "ok";
        }
    
        @GetMapping("/update/{id}")
        public String updateUser(@PathVariable("id")int id){
            String sql="update employee set last_name=?,email=? where id="+id;
            //数据
            Object[] objects=new Object[2];
            objects[0]="wunian";
            objects[1]="1233@qq.com";
            jdbcTemplate.update(sql,objects);
            return "ok";
        }
    
        @GetMapping("/delete/{id}")
        public String deleteUser(@PathVariable("id")int id){
            String sql="delete from employee where id=?";
            jdbcTemplate.update(sql,id);
            return "ok";
        }
    }

## 集成Druid ##

> Druid简介

Java程序很多时候都是要操作数据库的，为了提高操作数据库的性能，一般会使用连接池。 Druid 是阿里巴巴的开源组件之一，结合了C3P0，DBCP连接池的优点，并且**自带日志监控功能**，Druid 可以天然的监控 SQL和 数据库连接池的状况。

> 配置参数

DruidDataSource配置属性如下列表所示：

<table> 
 <thead> 
  <tr> 
   <th>配置</th> 
   <th>缺省值</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>name</td> 
   <td></td> 
   <td>配置这个属性的意义在于，如果存在多个数据源，监控的时候可以通过名字来区分开来。如果没有配置，将会生成一个名字，格式是：“DataSource-” + System.identityHashCode(this). 另外配置此属性至少在1.0.5版本中是不起作用的，强行设置name会出错。<a href="http://blog.csdn.net/lanmo555/article/details/41248763" rel="nofollow">详情-点此处</a>。</td> 
  </tr> 
  <tr> 
   <td>url</td> 
   <td></td> 
   <td>连接数据库的url，不同数据库不一样。例如： mysql : jdbc:mysql://10.20.153.104:3306/druid2 oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnauto</td> 
  </tr> 
  <tr> 
   <td>username</td> 
   <td></td> 
   <td>连接数据库的用户名</td> 
  </tr> 
  <tr> 
   <td>password</td> 
   <td></td> 
   <td>连接数据库的密码。如果你不希望密码直接写在配置文件中，可以使用ConfigFilter。<a href="https://github.com/alibaba/druid/wiki/%E4%BD%BF%E7%94%A8ConfigFilter" rel="nofollow">详细看这里</a></td> 
  </tr> 
  <tr> 
   <td>driverClassName</td> 
   <td>根据url自动识别</td> 
   <td>这一项可配可不配，如果不配置druid会根据url自动识别dbType，然后选择相应的driverClassName</td> 
  </tr> 
  <tr> 
   <td>initialSize</td> 
   <td>0</td> 
   <td>初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时</td> 
  </tr> 
  <tr> 
   <td>maxActive</td> 
   <td>8</td> 
   <td>最大连接池数量</td> 
  </tr> 
  <tr> 
   <td>maxIdle</td> 
   <td>8</td> 
   <td>已经不再使用，配置了也没效果</td> 
  </tr> 
  <tr> 
   <td>minIdle</td> 
   <td></td> 
   <td>最小连接池数量</td> 
  </tr> 
  <tr> 
   <td>maxWait</td> 
   <td></td> 
   <td>获取连接时最大等待时间，单位毫秒。配置了maxWait之后，缺省启用公平锁，并发效率会有所下降，如果需要可以通过配置useUnfairLock属性为true使用非公平锁。</td> 
  </tr> 
  <tr> 
   <td>poolPreparedStatements</td> 
   <td>false</td> 
   <td>是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。</td> 
  </tr> 
  <tr> 
   <td>maxPoolPreparedStatementPerConnectionSize</td> 
   <td>-1</td> 
   <td>要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100</td> 
  </tr> 
  <tr> 
   <td>validationQuery</td> 
   <td></td> 
   <td>用来检测连接是否有效的sql，要求是一个查询语句，常用select ‘x’。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会起作用。</td> 
  </tr> 
  <tr> 
   <td>validationQueryTimeout</td> 
   <td></td> 
   <td>单位：秒，检测连接是否有效的超时时间。底层调用jdbc Statement对象的void setQueryTimeout(int seconds)方法</td> 
  </tr> 
  <tr> 
   <td>testOnBorrow</td> 
   <td>true</td> 
   <td>申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。</td> 
  </tr> 
  <tr> 
   <td>testOnReturn</td> 
   <td>false</td> 
   <td>归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。</td> 
  </tr> 
  <tr> 
   <td>testWhileIdle</td> 
   <td>false</td> 
   <td>建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。</td> 
  </tr> 
  <tr> 
   <td>keepAlive</td> 
   <td>false （1.0.28）</td> 
   <td>连接池中的minIdle数量以内的连接，空闲时间超过minEvictableIdleTimeMillis，则会执行keepAlive操作。</td> 
  </tr> 
  <tr> 
   <td>timeBetweenEvictionRunsMillis</td> 
   <td>1分钟（1.0.14）</td> 
   <td>有两个含义： 1) Destroy线程会检测连接的间隔时间，如果连接空闲时间大于等于minEvictableIdleTimeMillis则关闭物理连接。 2) testWhileIdle的判断依据，详细看testWhileIdle属性的说明</td> 
  </tr> 
  <tr> 
   <td>numTestsPerEvictionRun</td> 
   <td>30分钟（1.0.14）</td> 
   <td>不再使用，一个DruidDataSource只支持一个EvictionRun</td> 
  </tr> 
  <tr> 
   <td>minEvictableIdleTimeMillis</td> 
   <td></td> 
   <td>连接保持空闲而不被驱逐的最小时间</td> 
  </tr> 
  <tr> 
   <td>connectionInitSqls</td> 
   <td></td> 
   <td>物理连接初始化的时候执行的sql</td> 
  </tr> 
  <tr> 
   <td>exceptionSorter</td> 
   <td>根据dbType自动识别</td> 
   <td>当数据库抛出一些不可恢复的异常时，抛弃连接</td> 
  </tr> 
  <tr> 
   <td>filters</td> 
   <td></td> 
   <td>属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有： 监控统计用的filter:stat 日志用的filter:log4j 防御sql注入的filter:wall</td> 
  </tr> 
  <tr> 
   <td>proxyFilters</td> 
   <td></td> 
   <td>类型是List&lt;com.alibaba.druid.filter.Filter&gt;，如果同时配置了filters和proxyFilters，是组合关系，并非替换关系</td> 
  </tr> 
 </tbody> 
</table>

在项目中配置Druid。

spring:
      datasource:
        username: root
        password: 123456
        #?serverTimezone=UTC解决时区的报错
        url: jdbc:mysql://localhost:3306/springboot?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        #Spring Boot 默认是不注入这些属性值的，需要自己绑定
        #druid 数据源专有配置
        initialSize: 5
        minIdle: 5
        maxActive: 20
        maxWait: 60000
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        validationQuery: SELECT 1 FROM DUAL
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
        #配置监控统计拦截的filters，stat:监控统计、log4j：日志记录、wall：防御sql注入
        #如果允许时报错	java.lang.ClassNotFoundException: org.apache.log4j.Priority 
        #则导入 log4j 依赖即可，Maven 地址：
        #https://mvnrepository.com/artifact/log4j/log4j
        filters: stat,wall,log4j
        maxPoolPreparedStatementPerConnectionSize: 20
        useGlobalDataSourceStat: true
        connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

> 使用第三方数据源

如果项目中要使用第三方数据源，一般操作步骤如下：  
1、导入依赖  
2、看源码配置  
3、看官方文档解释  
4、测试使用

> 测试

1、导入log4j、Druid依赖。

<dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.1.16</version>
    </dependency>
    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
    </dependency>

2、配置数据源，和上面配置Druid一样。  
3、配置数据日志监控。

//自定义DruidConfig
    @Configuration
    public class DruidConfig {
    
        //绑定配置的bean
        @ConfigurationProperties(prefix = "spring.datasource")
        @Bean
        public DataSource druidDataSource(){
            return new DruidDataSource();
        }
    
        //注册后台监控页面，SpringBoot如何注册Servlet
        //没有web.xml的情况配置Servlet的方法 ServletRegistrationBean
        //测试访问 /druid
        @Bean
        public ServletRegistrationBean statViewServlet(){
            //配置后台监控页面
            ServletRegistrationBean bean=new ServletRegistrationBean(new StatViewServlet(),"/druid/*");
            Map<String,String> map=new HashMap<>();
            //后台登陆的用户名和密码
            //用户名密码可以放到数据库
            map.put("loginUserName","admin");
            map.put("loginPassword","123456");
            // 访问权限
            //map.put("allow","localhost");//只允许本机访问
            map.put("allow","");//所有人都可以访问
    
            //拒绝访问
            //map.put("deny","192.168.1.1")//ip会被拒绝访问
    
            bean.setInitParameters(map);
            return bean;
        }
    
        //过滤器配置，看看哪些请求需要被过滤
        //没有web.xml的情况配置filter的方法 FilterRegistrationBean
        @Bean
        public FilterRegistrationBean  webStatFilter(){
            FilterRegistrationBean bean=new FilterRegistrationBean();
            bean.setFilter(new WebStatFilter());
            //配置内容
            //配置哪些请求可以被过滤
            Map<String,String> map=new HashMap<>();
            map.put("exclusions",".js,*.css,/druid/*");
            bean.setInitParameters(map);
            bean.setUrlPatterns(Arrays.asList("/*"));
            return bean;
        }
    }

4、测试，通过http://localhost:8080/druid来访问Druid后台监控页面。  
![Druid后台监控页面][Druid]  
当我们去访问一个查询员工列表的请求时，再回到后台监控页面的`SQL监控`页下，可以看到多了一条记录，详细记录了查询员工列表SQL的各项信息。  
![SQL监控][SQL]

## 集成Mybatis ##

> MyBatis如何使用？

1、导入依赖。  
2、编写MyBatis配置文件。  
3、编写接口。  
4、编写接口Mapper配置文件。  
5、注册Mapper配置文件。  
6、测试。

> MyBatis-Spring

在Spring中需要集成MyBatis-Spring依赖，上面原生的对象被Spring托管了，直接拿来使用即可。

> MyBatis Spring Boot Starter

在maven官网中搜索"MyBatis"，可以查到很多MyBatis的依赖，这些依赖都可以使用，我们这里使用的是`MyBatis Spring Boot Starter`  
![搜索MyBatis依赖][MyBatis]  
官方文档地址：

*  MyBatis：https://mybatis.org/mybatis-3/getting-started.html
 *  MyBatis-Spring：http://mybatis.org/spring/zh/factorybean.html
 *  MyBatis Spring Boot Starter：http://mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/

> 测试

1.导入依赖，依赖要求：`mybatis-spring-boot-starter`2.1版本。  
![依赖要求][format_png 2]

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

2、编写配置文件。

3、启动项目，测试是否连接成功。  
4、编写实体类。

@Repository //dao层使用
    @Mapper //表示这是一个Mybatis的Mapper
    public interface EmployeeMapper{
       // @Select("sql") 注解版配置
       //获取全部员工信息
        List<Employee> getEmployees();
        //新增一个员工信息
        int save(Employee employee);
        //通过id获取员工信息
        Employee get(Integer id);
        //通过id删除员工信息
        int delete(Integer id);
    }

5、编写Mapper.xml文件。SQL语句可以直接在IDEA的DataSource窗口中用鼠标点击数据库，点击上方铅笔图标打开console窗口进行编写，在这里编写会有智能提示，可以提升写SQL的效率。

<?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.wunian.mapper.EmployeeMapper">
    
        
        
        <resultMap id="EmployeeMap" type="Employee">
            
            <id property="id" column="eid"/>
            <result property="lastName" column="last_name"/>
            <result property="email" column="email"/>
            <result property="gender" column="gender"/>
            <result property="birth" column="birth"/>
            
            <association property="eDepartment" javaType="Department">
                <id property="id" column="did"/>
                <result property="departmentName" column="dname"/>
            </association>
        </resultMap>
        
        <select id="getEmployees"  resultMap="EmployeeMap">
            select e.id as eid,last_name,email,gender,birth,d.id as did,d.department_name as dname
             from employee e,department d where e.department=d.id
        </select>
    
        <insert id="save" parameterType="Employee" >
            insert into employee(last_name, email, gender, department, birth)
            values (#{lastName}, #{email}, #{gender}, #{department}, #{birth})
        </insert>
    
        <select id="get"  resultType="Employee">
            select * from employee where id= #{id}
        </select>
    
        <delete id="delete" parameterType="int">
             delete from employee where id= #{id}
        </delete>
    </mapper>

6、编写Controller进行测试。

public class EmployeeController {
    
        @Autowired
        EmployeeMapper mapper;
    
        //获取全部员工信息
        @GetMapping("/getEmployees")
        public List<Employee> getEmployees(){
            return mapper.getEmployees();
        }
    
        //新增一个员工信息
        @GetMapping("/save")
        public int save(){
            Employee employee=new Employee();
            employee.setLastName("wunian");
            employee.setEmail("193939@qq.com");
            employee.setGender(1);
            employee.setBirth(new Date());
            employee.setDepartment(101);
            return mapper.save(employee);
        }
        //通过id获取员工信息
        @GetMapping("/get/{id}")
        public Employee get(@PathVariable("id") Integer id){
            return mapper.get(id);
        }
        //通过id删除员工信息
        @GetMapping("/delete/{id}")
        public int delete(@PathVariable("id") Integer id){
            return mapper.delete(id);
        }
    }

7、配置MyBatis的配置项。

#配置mybatis的配置
    #下划线转驼峰
    mybatis.configuration.map-underscore-to-camel-case=true
    #使用表名的包
    mybatis.type-aliases-package=com.wunian.pojo
    #mapper配置文件地址，自动扫描
    mybatis.mapper-locations=classpath:com/wunian/mapper/xml/*.xml
    
    #mybatis核心配置文件地址，集成Spring之后就不需要了
    #mybatis.config-location=

8、解决Maven资源过滤问题。由于Maven默认不支持从src/java目录下获取除了代码之外的配置文件，我们需要在pom文件中过滤一下配置。

<resources>
      <resource>
        <filtering>true</filtering>
        <directory>src/main/java</directory>
          <includes>
            <include>**/*.xml</include>
          </includes>
      </resource>
    </resources>

9、测试运行。

[format_png]: /images/20210607/08a9053edcd445bf83a18cc018024cb5.png
[SpringData]: /images/20210607/34685ff642be4ab9a78524502bc02411.png
[format_png 1]: /images/20210607/ef4d089f833b4ee5ad5ae0f2a9135b6a.png
[Druid]: /images/20210607/50529da1ee08414fbe360921b6494c66.png
[SQL]: /images/20210607/ef78c15ff4de48e0b3fa871ce4cb81ce.png
[MyBatis]: /images/20210607/8f70372f35424f5f8f0182d95cbb286a.png
[format_png 2]: /images/20210607/ea585fea81984686ae7d1962930248d7.png