SpringBoot-23-整合持久层-整合jdbc

8.整合持久层

• 对于数据访问层，无论是SQL(关系型数据库)还是NOSQL(非关系型数据库)，Spring Boot底层都是采用Spring Data的方式进行统一处理，通过大量自动配置，来简化我们对数据访问层的操作，我们只需要进行简单的设置即可实现对数据的访问。。
• Spring Boot底层都是采用Spring Data的方式进行统一处理各种数据库，Spring Data也是Spring中与Spring Boot、Spring Cloud等齐名的知名项目。
• Sping Data官网：https://spring.io/projects/spring-data。数据库相关的启动器：可以参考官方文档：https://docs.spring.io/spring-boot/docs/2.1.7.RELEASE/reference/htmlsingle/#using-boot-starter

spring-boot-starter-data-cassandra	Starter for using Cassandra distributed database and Spring Data Cassandra	Pom
spring-boot-starter-data-cassandra-reactive	Starter for using Cassandra distributed database and Spring Data Cassandra Reactive	Pom
spring-boot-starter-data-couchbase	Starter for using Couchbase document-oriented database and Spring Data Couchbase	Pom
spring-boot-starter-data-couchbase-reactive	Starter for using Couchbase document-oriented database and Spring Data Couchbase Reactive	Pom
spring-boot-starter-data-elasticsearch	Starter for using Elasticsearch search and analytics engine and Spring Data Elasticsearch	Pom
spring-boot-starter-data-jdbc	Starter for using Spring Data JDBC	Pom
spring-boot-starter-data-jpa	Starter for using Spring Data JPA with Hibernate	Pom
spring-boot-starter-data-ldap	Starter for using Spring Data LDAP	Pom
spring-boot-starter-data-mongodb	Starter for using MongoDB document-oriented database and Spring Data MongoDB	Pom
spring-boot-starter-data-mongodb-reactive	Starter for using MongoDB document-oriented database and Spring Data MongoDB Reactive	Pom
spring-boot-starter-data-neo4j	Starter for using Neo4j graph database and Spring Data Neo4j	Pom
spring-boot-starter-data-redis	Starter for using Redis key-value data store with Spring Data Redis and the Lettuce client	Pom
spring-boot-starter-data-redis-reactive	Starter for using Redis key-value data store with Spring Data Redis reactive and the Lettuce client	Pom
spring-boot-starter-data-rest	Starter for exposing Spring Data repositories over REST using Spring Data REST	Pom
spring-boot-starter-data-solr	Starter for using the Apache Solr search platform with Spring Data Solr	Pom

8.1 整合jdbc使用

• Spring Boot 将日常企业应用研发中的各种场景都抽取出来，做成一个个的场景启动器（Starter），场景启动器中整合了该场景下各种可能用到的依赖，让用户摆脱了处理各种依赖和配置的困扰。
• jpa最好不要用，有数据安全问题
• 新建一个springboot项目，勾选spring Boot DevTools热部署、Lombok、Spring Web、Thymeleaf模板引擎、JDBC API、Spring Data JDBC、MySql Driver等依赖

JDBC 的场景启动器中并没有导入数据库驱动，我们需要根据自身的需求引入所需的数据库驱动。例如，访问 MySQL 数据库时，需要导入 MySQL 的数据库驱动：mysql-connector-java

Spring Boot 默认为数据库驱动程序做了版本仲裁，所以我们在导入数据库驱动时，可以不再声明版本。需要注意的是，数据库驱动的版本必须与数据库的版本相对应。

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

• 在导入了 JDBC 场景启动器和数据库驱动后，在application.yml配置数据源相关的内容

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/jdbcstudy?useSSL=true&useUnicode=true&characterEncoding=utf-8&serverTimeZone=UTC

• 测试一下数据库的默认数据源和数据库链接链接对象版本

@SpringBootTest
class SpringbootData03ApplicationTests {

    @Autowired
    DataSource dataSource;
    @Test
    void contextLoads() throws SQLException {
        //查看默认的数据源 com.zaxxer.hikari.HikariDataSource   dbcp/c3p0/druid
        System.out.println(dataSource.getClass());
        //获得数据库链接
        Connection connection = dataSource.getConnection();
        //com.mysql.cj.jdbc.ConnectionImpl@4451f60c
        System.out.println(connection);

        //关闭链接
        connection.close();
    }

}

• 也可以查看 spring-boot-starter-jdbc 的依赖树，可以看到，该场景启动器默认引入了一个数据源：HikariCP，如下图所示。

• HikariDataSource号称Java WEB当前速度最快的数据源，相l比于传统的C3P0、DBCP、Tomcatjdbc等连接池更加优秀；

//静态倒入包，直接用里面的静态方法和属性，而不用调类名，但违反spring原理
import static org.springframework.jdbc.core.JdbcTemplate;

Spring JDBC 提供了多个实用的数据库访问工具，以简化 JDBC 的开发，其中使用最多就是一个名为 JdbcTemplate 的轻量级数据访问工具,它是对 JDBC 的封装.Spring Boot 对 JdbcTemplate 提供了默认自动配置，我们可以直接使用 @Autowired 或构造函数将它注入到 bean 中使用。

跟JdbcTemplate一样XXXXTemplate:SpringBoot已经封装配置好大量的数据库访问模板bean,拿来即用即.如
jdbcTemplate crud
redisTemplate

• springboot中JdbcTemplate的使用CRUD,同时提供了事务支持

package com.zk.controller;

import org.springframework.beans.factory.annotation.Autowired;
//静态倒入包，直接用里面的静态方法和属性，而不用调类名，但违反spring原理
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author CNCLUKZK
 * @create 2022/8/3-11:14
 */
@RestController
public class JdbcController {
    @Autowired
    JdbcTemplate jdbcTemplate;

    @GetMapping("/userList")
    public List<Map<String, Object>> getUserList(){
        //查询数据库的所有信息
        //没有实体类，数据库中的东西，怎么获取？Map
        String sql = "select * from jdbcstudy.users";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        return maps;
    }

    @GetMapping("/addUser")
    public String addUser(){
        String sql = "insert into jdbcstudy.users (id,NAME,PASSWORD,email,birthday) values (?,?,?,?,?); ";
        Object[] args = new Object[5];
        args[0]=20;
        args[1]="李云";
        args[2]="123456";
        args[3]="123456@qq.com";
        args[4]= new Date().clone();
        int update = jdbcTemplate.update(sql,args);
        return "addUser-ok";
    }
    @GetMapping("/updUser/{id}")
    public String updUser(@PathVariable("id")Integer id){
        String sql = "update jdbcstudy.users set NAME =?,PASSWORD=?  where id ="+id;
        Object[] args = new Object[2];
        args[0]="Tom";
        args[1]="111111";
        int update = jdbcTemplate.update(sql,args);
        return "updUser-ok";
    }
    @GetMapping("/deleteUser/{id}")
    public String deleteUser(@PathVariable("id")Integer id){
        String sql = "delete from jdbcstudy.users where id =?";
        int update = jdbcTemplate.update(sql,id);
        return "deleteUser-ok";
    }
}

• 测试结果访问http://127.0.0.1:8080/userList

[{"id":1,"NAME":"zhansan","PASSWORD":"123456","email":"zs@sina.com","birthday":"2022-05-03"},{"id":2,"NAME":"lisi","PASSWORD":"123456","email":"lisi@sina.com","birthday":"2022-05-03"},{"id":3,"NAME":"wangwu","PASSWORD":"123456","email":"wangwu@sina.com","birthday":"2022-05-03"},{"id":4,"NAME":"张三","PASSWORD":"123456","email":"123456789@163.com","birthday":"2022-05-03"},{"id":5,"NAME":"李四","PASSWORD":"123456","email":"123456789@163.com","birthday":"2022-05-03"},{"id":8,"NAME":"王五","PASSWORD":"123456","email":"123456789@163.com","birthday":"2022-05-03"},{"id":9,"NAME":"王五","PASSWORD":"111111","email":"123456789@163.com","birthday":"2022-05-03"},{"id":10,"NAME":"王五","PASSWORD":"111111","email":"123456789@163.com","birthday":"2022-05-03"},{"id":11,"NAME":"王五","PASSWORD":"111111","email":"123456789@163.com","birthday":"2022-05-03"},{"id":13,"NAME":"ll","PASSWORD":"477777","email":"tryrtyry@qq.com","birthday":"2022-05-03"},{"id":14,"NAME":"王路","PASSWORD":"123456","email":"123456789@163.com","birthday":"2022-05-04"},{"id":16,"NAME":"王路","PASSWORD":"123456","email":"123456789@163.com","birthday":"2022-05-04"},{"id":18,"NAME":"华安","PASSWORD":"9527","email":"123456789@163.com","birthday":"2022-05-04"}]

insert into formname values后面可以插入多条数据value不行

• 测试可以访问http://127.0.0.1:8080/addUser、http://127.0.0.1:8080/updUser/3、http://127.0.0.1:8080/deleteUser/2等链接使用jdbcTemplate对数据表进行CRUD
• 注意sql预编译中参数的传递

 String sql = "insert into jdbcstudy.users (id,NAME,PASSWORD,email,birthday) values (?,?,?,?,?); ";
 Object[] args = new Object[5];
 args[0]=19;
 args[1]="mary";
 args[2]="123456";
 args[3]="123456@qq.com";
 args[4]= new Date().clone();
 int update = jdbcTemplate.update(sql,args);

• 数据源自动配置类DataSourceAutoConfiguration

@Configuration(proxyBeanMethods = false)
	@Conditional(PooledDataSourceCondition.class)
	@ConditionalOnMissingBean({ DataSource.class, XADataSource.class })
	@Import({ DataSourceConfiguration.Hikari.class, DataSourceConfiguration.Tomcat.class,
			DataSourceConfiguration.Dbcp2.class, DataSourceConfiguration.OracleUcp.class,
			DataSourceConfiguration.Generic.class, DataSourceJmxConfiguration.class })
	protected static class PooledDataSourceConfiguration {

	}

• 只有一个DataSourceConfiguration.Hikari.class生效了

下一篇：SpringBoot-24-整合持久层-整合DruidDataSource数据源