SpringBoot-02-操作数据库

4 SpringBoot操作数据库

4.1 SpringData

什么是SpringData

  • SpringData是Spring全家桶中专门用于处理数据访问层的组件了，它使用Spring的方式对所有SQL和NoSQL数据库进行统一处理
  • 在所有Spring项目的底层，都统一使用SpringData处理各种数据库

4.2 集成JDBC

导入对应的依赖

<dependencies>
    <!--JDBC-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
	<!--MySQL驱动-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
</dependencies>

配置SpringBoot

  • 配置文件

    spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/springboot?serverTimezone=GMT%2B8&useSSL=true&useUnicode=true&characterEncoding=utf8

  • 测试

    @SpringBootTest
class Springboot02DbApplicationTests {

    @Autowired
    DataSource dataSource;

    @Test
    void contextLoads() {
        System.out.println(dataSource.getClass());
    }

}

    image-20201217211131863

CRUD

  • Spring对原生的JDBC进行轻量级封装，大大简化了JDBC的操作。即使在没有第三方ORM框架的情况下，也能完成一些简单的CRUD操作

  • Spring将原生的JDBC封装成了 JDBCTemplate 并完成了自动配置，我们直接拿来用就好了

  • Controller

    @RestController
public class JDBCController {

    @Autowired
    private JdbcTemplate template;

    @RequestMapping("/list")
    public List<Map<String, Object>> getAllDepartment() {
        String sql = "select * from springboot.department";
        return template.queryForList(sql);
    }

}
    • 这边是一个小技巧，在没有实体类的情况下，可以使用map来装载查询出来的数据

    image-20201217212959214

4.3 集成Druid

  • Drudi号称是Java中最好的数据库连接池，他继承了C3P0、DBCP 等 DB 池的优点，同时具有强大的日志监控功能

  • 这里主要介绍的是它的监控功能

  • 配置SpringBoot

    • 要想使得SpringBoot切换连接池，我们可以通过 spring.datasource.type 属性进行手动切换。
    # 配置基本的数据库连接信息
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/springboot?serverTimezone=GMT%2B8&useSSL=true&useUnicode=true&characterEncoding=utf8
    type: com.alibaba.druid.pool.DruidDataSource

  • Druid配置类，这里顺便复习一下Java配置类

    package com.pbx.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.servlet.Servlet;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
 * @author BruceXu
 * @date 2020/12/17
 */
@Configuration
public class DruidConfig {

    @Bean
    @ConfigurationProperties("druid")
    public DataSource druidDataSource() {
        return new DruidDataSource();
    }

    @Bean
    public ServletRegistrationBean<Servlet> druidServlet() {    // 配置监控
        ServletRegistrationBean<Servlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
        Map<String, String> map = new HashMap<>();
        //后台管理界面的登录账号密码
        map.put("loginUsername", "admin");
        map.put("loginPassword", "123456");

        // 访问白名单
        map.put("allow", "");

        // 黑名单
        map.put("deny", "192.168.1.1");

        bean.setInitParameters(map);
        return bean;
    }

}

    • 这里给出Druid可供配置的所有后台参数

      image-20201217220512819

  • 测试

    image-20201217222848386

  • 排坑

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

    • 使用JDBC时，使用第二个依赖，不要用第一个，不然会引起异常，排查时具体表现为 jdbc的url not set

      image-20201217223223708

4.4 集成MyBatis

导入依赖

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

实体类

@Repository
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Department {
    private int id;
    private String name;
}

mapper接口

@Mapper
@Repository
public interface DepartmentMapper {

    List<Department> getAllDepartment();

    Department getDepartmentById(@Param("id") int id);

}

mapper.xml

  • 记得在SpringBoot中配置这些配置文件的位置，要不就启用全局的mybatis-config文件进行配置，不然就会注册不到mapper
<mapper namespace="com.pbx.mapper.DepartmentMapper">

    <select id="getDepartmentList" resultType="com.pbx.pojo.Department">
        select * from springboot.department
    </select>
    <select id="getDepartmentById" resultType="com.pbx.pojo.Department">
        select * from springboot.department where id = #{id}
    </select>

</mapper>

application.yaml

mybatis:
  mapper-locations: classpath:mybatis/mapper/*.xml

测试

@SpringBootTest
class Springboot03ApplicationTests {

     @Autowired
     private DataSource dataSource;

     @Autowired
     private DepartmentMapper mapper;

    @Test
    void contextLoads() {
        System.out.println(dataSource.getClass());

        List<Department> list = mapper.getDepartmentList();
        System.out.println(list);

    }

}

image-20201217234222180

posted @ 2020-12-21 16:28  PrimaBruceXu  阅读(108)  评论(0)  编辑  收藏  举报