SpringBoot学习笔记（七）SpringBoot数据访问

SpringJDBC

1. 导入相关依赖

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

2. 编写配置文件

spring:
  datasource:
    username: root
    password: a
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    driver-class-name: com.mysql.cj.jdbc.Driver

3. 编写测试类

@SpringBootTest
class Sb6DataJdbcApplicationTests {

	@Autowired
	DataSource dataSource;

	@Test
	void contextLoads() {
		//com.zaxxer.hikari.HikariDataSource
		System.out.println(dataSource.getClass());
	}
}

4. 默认使用 com.zaxxer.hikari.HikariDataSource 作为数据源。数据源相关的配置都在 org.springframework.boot.autoconfigure.jdbc.DataSourceProperties 中做了配置。

自动配置原理

org.springframework.boot.autoconfigure.jdbc

1. 参考 DataSourceConfiguration ，根据配置创建数据源，默认使用的是tomcat连接池，可以使用 spring.datasource.type" 指定自定义的数据源类型
2. SpringBoot默认支持的数据源类型：

• org.apache.commons.dbcp2.BasicDataSource
• com.zaxxer.hikari.HikariDataSource
• org.apache.tomcat.jdbc.pool.DataSource

示例如下：

package com.hx;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.util.List;

@SpringBootTest
class Sb6DataJdbcApplicationTests {

	@Autowired
	JdbcTemplate template;

	@Test
	public void test(){
		String sql = "select * from user";
		List list = template.queryForList(sql);
		System.out.println(list);
	}
}

整合Durid & 配置数据源监控

整合durid

1. 导入相关依赖

<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid</artifactId>
	<version>1.1.24</version>
</dependency>

2. 自定义配置
   编写配置文件

spring:
  datasource:
    username: root
    password: a
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000

编写配置类

@Configuration
public class DruidConfig {

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

配置durid服务监控

1. 导入相关依赖

<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid</artifactId>
	<version>1.1.24</version>
</dependency>

2. 编写配置类

package com.hx.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

/**
 * @ClassPath： com.hx.config.DruidConfig
 * @Author： Huathy
 * @Description：
 * @Date： 2020-09-19 18:51
 */
@Configuration
public class DruidConfig {

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

    //配置durid监控
    @Bean
    public ServletRegistrationBean statViewServlet() {
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        Map<String, String> map = new HashMap<>();
        map.put("loginUsername","admin");
        map.put("loginPassword","a");
        map.put("allow","127.0.0.1");   //如果不写的情况下，则允许所有访问
        map.put("deny","192.168.0.1");
        bean.setInitParameters(map);
        return bean;
    }

    //配置web监控filter
    @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;
    }
}

3. 访问：http://localhost:8080/druid/sql.html

整合MyBatis

1. 导入依赖

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

SpringData JPA

1. SpringData是为简化基于Spring框架的数据访问技术。为我们提供统一的API来对数据进行访问操作。
2. 为我们提供统一的Repository接口、数据访问模板类 xxxTemplate。
3. 底层是Hibernate
   

整合JPA

1. 导入依赖

<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-web</artifactId>
</dependency>

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

2. 创建实体与数据表映射，并配置好映射关系

//使用JPA注解配置映射关系
//告诉jpa这是一个实体类（和数据表映射的类)
@Entity
@Table(name = "user")
public class User {

    @Id //这是主键
    @GeneratedValue(strategy = GenerationType.IDENTITY) //主键自增列
    private Integer id;

    @Column(name = "name",length = 255)
    private String name;

    @Column //若省略则默认列名为属性名
    private String pwd;
	
	//省略getter、setter
}

3. 编写dao层接口来操作实体类对应的数据表（Repository）

/**
 * 继承JpaRepository来完成对数据库的操作
 */
public interface UserRepository extends JpaRepository<User,Integer> {

}

4. 基本配置

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/jpaTest?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    username: root
    password: a
    driver-class-name: com.mysql.cj.jdbc.Driver
  jpa:
    show-sql: true   #控制台显示sql
    hibernate:
      #更新或创建数据表结构
      ddl-auto: update

5. 编写Controller

package com.hx.jpa.controller;

import com.hx.jpa.entity.User;
import com.hx.jpa.repository.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.Optional;

/**
 * @ClassPath： com.hx.jpa.controller.UserController
 * @Author： Huathy
 * @Description：
 * @Date： 2020-09-20 15:34
 */
@RestController
public class UserController {

    @Autowired
    UserRepository userRepository;

    @RequestMapping("/getUser/{id}")
    //Optional类是Java8为了解决null值判断问题,可避免null导致的NPE（空指针异常）
    public Optional<User> getUser(@PathVariable("id") Integer id){
        Optional<User> user =  userRepository.findById(id);
        return user;
    }

    @RequestMapping("/add")
    public User add(User user){
       User u = userRepository.save(user);
        return  u;
    }
}

6. 测试
   访问：localhost:8080/getUser/1
   访问：localhost:8080/add?id=0&name=啾啾&pwd=jiujiu