一胜臊、簡介

JDBC玻孟，Mybatis扼雏，Spring Data JPA
主要討論SpringBoot如何與關(guān)系型數(shù)據(jù)庫交互
對于數(shù)據(jù)訪問層，無論是SQL還是NOSQL杨名，Spring Boot默認(rèn)采用整合 Spring Data的方式進行統(tǒng)一處理，添加大量自動配置，屏蔽了很多設(shè)置。引入 各種xxxTemplate轩触，xxxRepository來簡化我們對數(shù)據(jù)訪問層的操作。對我們來 說只需要進行簡單的設(shè)置即可椿争。

二怕膛、JDBC

JDBC是Java提供的一個操作數(shù)據(jù)庫的API；
JDBC的相關(guān)資料：https://www.cnblogs.com/xiaotiaosi/p/6394554.html

（一）相關(guān)代碼

pom.xml

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

application.yaml

spring:
  datasource:
    username: admin
    password: admin
    url: jdbc:mysql://localhost:3306/jdbc
    driver-class-name: com.mysql.cj.jdbc.Driver

SpringBootDemoApplicationTests .java

@RunWith(SpringRunner.class)
@SpringBootTest
public class SpringBootDemoApplicationTests {
    @Autowired //自動注入
    DataSource dataSource;
    @Test
    public void contextLoads() throws SQLException {
        System.out.println(dataSource.getClass());//輸出看一下是什么數(shù)據(jù)源
        Connection connection = dataSource.getConnection();
        System.out.println(connection);//查看是否獲取到了連接
        connection.close();
    }

}

（二）遇到的錯誤：

mysql異常:

The server time zone value '?D1ú±ê×?ê±??' is unrecognized or represents more than one time zone

image.png

解決辦法：

由于時區(qū)問題導(dǎo)致的, 只需要更改MySQL的時區(qū) 注意在英文狀態(tài)下輸入

 show variables like '%time_zone%'; 
 set global time_zone = '+8:00';   //修改mysql全局時區(qū)為東8區(qū)秦踪，即北京時間
 set time_zone = '+8:00'; //修改當(dāng)前會話時區(qū)
flush privileges; //立即生效

image.png

效果：

不再報錯褐捻，并輸出了數(shù)據(jù)源和連接

image.png

?另，數(shù)據(jù)源的相關(guān)配置都在DataSourceProperties里面椅邓；

（三）自動配置原理：

org.springframework.boot.autoconfigure.jdbc：

1柠逞、參考DataSourceConfiguration，根據(jù)配置創(chuàng)建數(shù)據(jù)源

2景馁、SpringBoot默認(rèn)可以支持板壮；

org.apache.tomcat.jdbc.pool.DataSource、HikariDataSource合住、BasicDataSource绰精、

3、自定義數(shù)據(jù)源類型

static class Generic {
        Generic() {
        }
//使用DataSourceBuilder創(chuàng)建數(shù)據(jù)源透葛，利用反射創(chuàng)建響應(yīng)type的數(shù)據(jù)源笨使，并且綁定相關(guān)屬性
        @Bean
        public DataSource dataSource(DataSourceProperties properties) {
            return properties.initializeDataSourceBuilder().build();
        }
    }

 public DataSourceBuilder<?> initializeDataSourceBuilder() {
        return DataSourceBuilder.create(this.getClassLoader()).type(this.getType()).driverClassName(this.determineDriverClassName()).url(this.determineUrl()).username(this.determineUsername()).password(this.determinePassword());
    }

4、DataSourceInitializer僚害；

 public boolean createSchema() {
        List<Resource> scripts = this.getScripts("spring.datasource.schema", this.properties.getSchema(), "schema");
        if (!scripts.isEmpty()) {
            if (!this.isEnabled()) {
                logger.debug("Initialization disabled (not running DDL scripts)");
                return false;
            }

            String username = this.properties.getSchemaUsername();
            String password = this.properties.getSchemaPassword();
            this.runScripts(scripts, username, password);
        }

        return !scripts.isEmpty();
    }
......
private void runScripts(List<Resource> resources, String username, String password) {
        if (!resources.isEmpty()) {
            ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
            populator.setContinueOnError(this.properties.isContinueOnError());
            populator.setSeparator(this.properties.getSeparator());
            if (this.properties.getSqlScriptEncoding() != null) {
                populator.setSqlScriptEncoding(this.properties.getSqlScriptEncoding().name());
            }

            Iterator var5 = resources.iterator();

            while(var5.hasNext()) {
                Resource resource = (Resource)var5.next();
                populator.addScript(resource);
            }

            DataSource dataSource = this.dataSource;
            if (StringUtils.hasText(username) && StringUtils.hasText(password)) {
                dataSource = DataSourceBuilder.create(this.properties.getClassLoader()).driverClassName(this.properties.determineDriverClassName()).url(this.properties.determineUrl()).username(username).password(password).build();
            }

            DatabasePopulatorUtils.execute(populator, dataSource);
        }
    }

默認(rèn)只需要將文件命名為：

schema-*.sql硫椰、data-*.sql
默認(rèn)規(guī)則：schema.sql，schema-all.sql萨蚕；
可以使用   
    schema:
      - classpath:department.sql
      指定位置

小例子

department.sql

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `departmentName` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

application.yml

spring:
  datasource:
    username: admin
    password: admin
    url: jdbc:mysql://localhost:3306/jdbc
    driver-class-name: com.mysql.cj.jdbc.Driver
    initialization-mode: always
    schema:
      - classpath:department.sql

遇到的問題:

Property spring.datasource.schema with value 'class path resource.[sql/department.sql]' is invalid: The specified resource does not exist

image.png

原因：

因為我剛開始寫的是:

schema:
      - classpath:sql/department.sql

路徑不匹配靶草，所以報錯說沒有找到sql文件

效果：

通過SpringBoot創(chuàng)建的表

5、操作數(shù)據(jù)庫：自動配置了JdbcTemplate操作數(shù)據(jù)庫
如何使用岳遥？

小例子

controller

/**
 * 需要注意的是奕翔，建表操作是每啟動一次項目，就執(zhí)行一次
 */
@Controller
public class TestController {
    @Autowired
    JdbcTemplate jdbcTemplate;

    @ResponseBody 
    @GetMapping("/query")
    public Map<String,Object> map(){
        List<Map<String, Object>> list = jdbcTemplate.queryForList("SELECT * FROM department");
        System.out.println(list.get(0));
        return list.get(0);
    }
}

表里的數(shù)據(jù)

效果

三浩蓉、整合Druid數(shù)據(jù)源

在開發(fā)過程中糠悯，很少使用org.apache.tomcat.jdbc.pool.DataSource數(shù)據(jù)源，可以用：c3p0 妻往，Druid互艾，HikariDataSource
Druid是一個阿里數(shù)據(jù)源產(chǎn)品，用的比較多讯泣，因為他有安全纫普，監(jiān)控等解決方案
數(shù)據(jù)庫連接池相關(guān)資料：https://www.cnblogs.com/JavaSubin/p/5294721.html
maven倉庫：https://mvnrepository.com/
到maven倉庫中選擇要引入的版本

image.png

spring:
  datasource:
    username: admin
    password: admin
    url: jdbc:mysql://localhost:3306/jdbc
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource //用來自定義數(shù)據(jù)源

運行test，(就是測試JDBC時的test，不用做任何更改昨稼，代碼見上面)

image.png

spring:
  datasource:
    #   數(shù)據(jù)源基本配置
    username: admin
    password: admin
    url: jdbc:mysql://localhost:3306/jdbc
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
#    initialization-mode: always
#    schema:
#      - classpath:department.sql

    #   數(shù)據(jù)源其他配置
    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
    #   配置監(jiān)控統(tǒng)計攔截的filters假栓，去掉后監(jiān)控界面sql無法統(tǒng)計寻行，'wall'用于防火墻  
    filters: stat,wall,log
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

兩個框里的背景色不同

設(shè)置完后可以看到下面的配置背景填充，這是應(yīng)為下邊這些配置無法綁定到DataSourceProperties上匾荆，即是這些配置沒有生效拌蜘。

debug

那該如何讓他們生效呢？
自己配一下就可以了
自己創(chuàng)一個數(shù)據(jù)源牙丽，并加在容器中（@Bean）简卧，再用一個屬性注解（@ConfigurationProperties）綁定配置

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

    //配置Druid的監(jiān)控
    //1、配置一個管理后臺的Servlet
    @Bean
    public ServletRegistrationBean statViewServlet(){
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        Map<String,String> initParams = new HashMap<>();

        initParams.put("loginUsername","admin");
        initParams.put("loginPassword","123456");
        initParams.put("allow","");//默認(rèn)就是允許所有訪問
        initParams.put("deny","192.168.15.21");

        bean.setInitParameters(initParams);
        return bean;
    }


    //2烤芦、配置一個web監(jiān)控的filter
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());

        Map<String,String> initParams = new HashMap<>();
        initParams.put("exclusions","*.js,*.css,/druid/*");

        bean.setInitParameters(initParams);

        bean.setUrlPatterns(Arrays.asList("/*"));

        return  bean;
    }
}

可以了举娩！

監(jiān)控頁面

三、MyBatis

MyBatis是一個支持普通SQL查詢构罗，存儲過程和高級映射的優(yōu)秀持久層框架铜涉。
pom.xml

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

根據(jù)命名習(xí)慣，可以看出遂唧，這不是SpringBoot官方出的芙代，而是mybatis自己來適配SpringBoot的starter

starter引入的包

（一）步驟

1）、配置數(shù)據(jù)源相關(guān)屬性（見上一節(jié)Druid）

?2）蠢箩、給數(shù)據(jù)庫建表
employee.sql

ET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lastName` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `gender` int(2) DEFAULT NULL,
  `d_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

department.sql

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `departmentName` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

pom.xml在原來基礎(chǔ)上加上這幾句話

schema:
      - classpath:department.sql
      - classpath:employee.sql

sql文件的位置

創(chuàng)建了兩個表

建好表后，注釋掉事甜，以防下次啟動項目重新建表

?3）谬泌、創(chuàng)建JavaBean
根據(jù)表格的設(shè)計，寫相關(guān)的代碼逻谦，再自動生成get/set方法

4)掌实、注解版

寫一個mapper操作數(shù)據(jù)庫

@Mapper //指定這是一個操作數(shù)據(jù)庫的mapper
public interface DepartmentMapper {
    @Select("select * from department where id = #{id}")
    public Department getDeptById(Integer id);
    @Delete("delete from department where id = #{id}")
    public int deleteDeptById(Integer id);
    @Insert("insert into department(departmentName) values (#{departmentName})")
    public int insertDept(Department department);
    @Update("update department set departmentName = #{departmentName} where id = #{id}")
    public int updateDept(Department department);
}

寫一個Controller測一測

@RestController //返回json數(shù)據(jù)，不返回頁面
public class DeptController {
    @Autowired
    DepartmentMapper departmentMapper;
    @GetMapping("/dept/{id}") //要處理的映射邦马，以占位符的方式取出id
    public Department getDepartment(@PathVariable("id") int id){
        return departmentMapper.getDeptById(id);
    }
    @GetMapping("/dept")
    public Department insertDepartment(Department department){
        departmentMapper.insertDept(department);
        return department;
    }
}

插入成功

表里的內(nèi)容

成功獲取

配置好了SqlSessionFactory以及properties

存在的問題：插入的時候，返回的數(shù)據(jù)里面id為null

解決辦法：

加上這句就好了

出來了滋将，嘻嘻邻悬！

如何進行相關(guān)的mybatis設(shè)置

修改departmentName為department_Name

修改相應(yīng)的sql語句

竟查不到department_name了

@Configuration
public class MyBatisConfig {
    @Bean
    public ConfigurationCustomizer configurationCustomizer (){
        return new ConfigurationCustomizer(){
            @Override
            public void customize(org.apache.ibatis.session.Configuration configuration) {
                configuration.setMapUnderscoreToCamelCase(true);//開啟駝峰命名方式
            }
        };
    }
}

就可以了

5)、配置版

自己配置好煩啦掘宪！以后再學(xué)
教程在這：https://www.bilibili.com/video/av38657363/?p=65

參考資料：

http://www.cnblogs.com/wangdianqian/p/9927406.html