SpringBoot之数据访问常用的几种方式详解

一、SpringBoot整合原生JDBC与数据源

1、启动mysql，查看进程

2、SpringBoot连接数据库

（1）application.yml

spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://192.168.93.101:3306/jdbc_test?useUnicode=true&characterEncoding=utf-8&relaxAutoCommit=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
    driver-class-name: com.mysql.cj.jdbc.Driver

(2)测试：数据源连接

SpringBoot2.0默认是使用com.zaxxer.hikari.HikariDataSource;

数据源的相关配置都在DataSourceProperties里面；

自动配置原理：
org.springframework.boot.autoconfigure.jdbc：
1、参考DataSourceConfiguration，根据配置创建数据源；可以使用spring.datasource.type指定自定义的数据源类型；

源码：

（3）SpringBoot自动配置了JdbcTemplate操作数据库 

@Controller
public class HelloController {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @ResponseBody   
    @GetMapping("/find")
    public Map<String,Object> findAll() {
        List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from employee");
        return list.get(0);
    }
}

 二、SpringBoot整合Druid数据源

1、(1)引入druid

    <!--引入druid数据源-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.20</version>
        </dependency>

(2)配置druid

spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://192.168.93.101:3306/jdbc_test?useUnicode=true&characterEncoding=utf-8&relaxAutoCommit=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
    driver-class-name: com.mysql.cj.jdbc.Driver
    initialization-mode: always
#   指定数据源的类型
    type: com.alibaba.druid.pool.DruidDataSource

(3)测试

2、配置Druid有关的其他信息

（1） application.yml

(2) DruidConfig.java

@Configuration
public class DruidConfig {

    @ConfigurationProperties(prefix="spring.datasource") //来获取application.yml配置的属性，绑定DruidDataSource的属性
    @Bean
    public DruidDataSource druidDataSource(){
        return new DruidDataSource();
    }

 DruidDataSource.java 源码:

三、SpringBoot整合MyBatis

1、(1)DruidConfig.java

@Configuration
public class DruidConfig {

    @ConfigurationProperties(prefix="spring.datasource") //来获取application.yml配置的属性
    @Bean
    public DruidDataSource druidDataSource(){
        return new DruidDataSource();
    }

    //配置Druid的监控
    //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", "1234");
        initParams.put("allow", ""); //默认运行所有访问
        initParams.put("deny", "192.168.0.105");
        bean.setInitParameters(initParams);
        return bean;
    }

    //配置一个web监控的filter
    public FilterRegistrationBean webStatFilter() {
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());
        Map<String,String> initParams = new HashMap<>();

        initParams.put("exclusions", "*.js, *.css, *.images, /druid/*");
        bean.setInitParameters(initParams);
        bean.setUrlPatterns(Arrays.asList("/*"));
        return bean;
    }
}

(2)druid监控后台

（3）运行建表语句

 

2、使用Mybatis注解版

(1) 使用@Mapper注解 ： 必须要加，指定这是操作数据库的mapper，省去映射文件

@Mapper //指定这是操作数据库的mapper，不用写mapper映射文件
public interface DepartmentMapper {
    @Select("select * from department where id = #{id}")
    Department queryDepById(Integer id);

    @Delete("delete from department where id = #{id}")
    int deleteDepById(Integer id);

    @Insert("insert into department(department_name) values(#{departmentName})")
    //获取自增的id
    @Options(useGeneratedKeys = true,keyProperty = "id") //使用自动生成的主键，哪个属性是主键
    int insert(Department dept);

    @Update("update department set department_name = #{departmentName} where id = #{id}")
    int update(Department dept);
}

但是，假如我们以后开发实体类非常多，每个类都加@Mapper也麻烦。我们可以在SpringBoot的主配置类上加

@MapperScan(basePackages = "com.dhu.mapper")，使用MapperScan批量扫描所有的Mapper接口，相当于mapper包下所有的接口都加了@Mapper注解。

 

注意：这里使用了开启驼峰命名法，因为：

Department.java:

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Department implements Serializable {
    private Integer id;
    private String departmentName;
}

数据库字段：

（2） 自定义MyBatis的配置规则；给容器中添加一个ConfigurationCustomizer

MabatisCongif.java : 设置驼峰命名

import org.apache.ibatis.session.Configuration;
import org.mybatis.spring.boot.autoconfigure.ConfigurationCustomizer;
import org.springframework.context.annotation.Bean;

/**
 * @author zhou
 * @create 2020/5/18
 */
@org.springframework.context.annotation.Configuration
public class MybatisConfig {
    @Bean
    public ConfigurationCustomizer configurationCustomizer(){
        return new ConfigurationCustomizer() {
            @Override
            public void customize(Configuration configuration) {
                configuration.setMapUnderscoreToCamelCase(true); //开启驼峰命名
            }
        };
    }
}

 (3) 

@Controller
public class DeptController {
    @Autowired
    private DepartmentMapper mapper;
    @Autowired
    private EmployeeMapper employeeMapper;

    @GetMapping("/dept/{id}")
    @ResponseBody
    public Department findDepById(@PathVariable("id") Integer id){
        Department department = mapper.queryDepById(id);
        return department;
    }

    @GetMapping("/dept")
    @ResponseBody
    public Department add(Department dept) {
        int out = mapper.insert(dept);
        return dept;
    }

    @ResponseBody
    @GetMapping("/emp/{id}")
    public Employee findEmpById(@PathVariable("id") Integer id){ //@PathVariable接收请求路径中占位符的值
        Employee employee = employeeMapper.queryById(id);
        return employee;
    }
}

 

3、使用Mybatis配置文件版：

需要Mybatis全局配置文件，以及每一个mapper接口对应的mapper.xml映射文件

（1）mybatis-config.xml

<configuration>
    <!--驼峰命名-->
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <typeAliases>
        <package name="com.dhu.pojo"/>
    </typeAliases>

    <!--需要将mapper接口类名和mapper.xml映射文件名称保持一致，且在一个目录，不过在这个项目中不适用 -->
    <mappers>
        <package name="com.dhu.mapper"/>
    </mappers>
</configuration>

（2）EmployeeMapper.xml

<mapper namespace="com.dhu.mapper.EmployeeMapper">
     <select id="queryById" parameterType="Integer" resultType="employee">
         select * from employee where id = #{id}
     </select>

    <insert id="insert" parameterType="employee">
        insert into employee(lastName, email, gender, d_id) values(#{lastName}, #{email}, #{gender}, #{dId})
    </insert>
</mapper>

 （3）application.yml 

作用：指定Mybatis配置文件的位置

（4）启动类

需要添加：

@MapperScan(basePackages = "com.dhu.mapper")

@SpringBootApplication
@MapperScan(basePackages = "com.dhu.mapper")
public class SpringbootDataMybatisApplication {
    public static void main(String[] args) {
        SpringApplication.run(SpringbootDataMybatisApplication.class, args);
    }

}