Spring Boot与数据访问

六、Spring Boot与数据访问

1. 简介

Spring Boot对于数据访问层,无论是SQL(关系型)还是NOSQL(非关系型),都默认采用整合Spring Data的方式进行统一处理。添加大量自动配置,屏蔽了很多设置。引入各种xxxTemplate,xxxRepository来简化我们对数据访问层的操作。对我们来说只需要进行简单的设置即可。

2. Spring Boot整合JDBC

添加如下依赖:

<!--        JDBC 依赖-->
        <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>

配置文件中配置:

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

测试连接

//获取数据源
@Autowired
DataSource dataSource;

/**
 * 测试获取连接是否成功
 * @throws SQLException
 */
@Test
void contextLoads() throws SQLException {
    System.out.println(dataSource.getClass());
    Connection connection = dataSource.getConnection();
    System.out.println();
    System.out.println(connection);
    connection.close();
}

自动配置原理:

数据源的相关配置都在DataSourceProperties中探究

依赖包的目录:

jetbrains://idea/navigate/reference?project=spring-boot-06-jdbc&fqn=org.springframework.boot.autoconfigure.jdbc

参考DataSourceAutoConfiguration,根据配置创建数据源,默认使用Hikari连接池。

可以使用:

spring.datasource.type

指定自定义的数据源类型

还可以自定义数据源类型

DataSourceInitializer:ApplicationListener

​ 作用:

​ 1)、runSchemaScripts();运行建表语句;

​ 2)、runDataScripts();运行插入数据的sql语句;

默认只需要将文件命名为:

schema-*.sql(建表)、data-*.sql(数据)
默认规则:schema.sql,schema-all.sql;
可以使用   
	schema:
      - classpath:department.sql
      指定位置

操作数据库:自动配置了JdbcTemplate操作数据库

使用IDEA执行sql脚本创建数据库表

方式一:

  1. 在resources下创建sql脚本

  2. 使用命名规范:schema-.sql(建表)、data-.sql(数据)

  3. 配置文件中配置:

    spring:
      datasource:
        username: root
        password: root
        url: jdbc:mysql://192.168.1.106:3306/jdbc
        driver-class-name: com.mysql.cj.jdbc.Driver
        initialization-mode: always
    
  4. 运行程序会自动建表或者添加数据

方式二:

  1. 在resources文件夹下新建sql文件夹,放入sql脚本文件

  2. 配置文件配置

    spring:
      datasource:
        username: root
        password: root
        url: jdbc:mysql://192.168.1.106:3306/jdbc
        driver-class-name: com.mysql.cj.jdbc.Driver
        initialization-mode: always
        schema:
          - classpath:sql/department.sql
    
  3. 运行程序建表成功

操纵数据库数据举例

package com.why.springboot.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import java.util.List;
import java.util.Map;

/**
 * @Description TODO 操作数据库
 * @Author why
 * @Date 2020/12/25 14:48
 * Version 1.0
 **/
@Controller
public class HelloController {

    @Autowired
    JdbcTemplate jdbcTemplate;

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

3. Spring Boot整合Druid数据源

引入Druid依赖

<!-- https://mvnrepository.com/artifact/com.alibaba/druid  Druid依赖-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.4</version>
</dependency>

**Druid版本依赖地址:**https://mvnrepository.com/artifact/com.alibaba/druid

配置文件配置:

spring:
  datasource:
#   数据源基本配置
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/ssm_crud
    type: com.alibaba.druid.pool.DruidDataSource
#   数据源其他配置
    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
#   配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙  
    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true  
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
    

此时虽然配置了属性但并不生效需要自行配置

配置使得属性生效

package com.why.springboot.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
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 javax.sql.DataSource;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

/**
 * @Description TODO Druid配置使其他druid属性生效
 * @Author why
 * @Date 2020/12/25 15:19
 * Version 1.0
 **/
@Configuration
public class DruidConfig {

    @ConfigurationProperties(prefix = "spring.datasource")//将以spring.datasource为前缀的属性绑定至容器
    @Bean
    public DataSource druid(){
        return new DruidDataSource();
    }

    /**
     * 配置druid监控
     */
    //1.配置管理后台的Servlet
    @Bean
    public ServletRegistrationBean statViewServlet(){
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        //配置初始化参数
        //配置的参数可在StatViewServlet()StatViewServlet()的ResourceServlet父类中查看
        Map<String,String> initParams = new HashMap<>();
        //登录后台系统用户名
        initParams.put("loginUsername","admin");
        //登录后台系统密码
        initParams.put("loginPassword","123456");
        //允许谁访问
        initParams.put("allow","");//第二个参数不写或者为null时默认允许所有访问
        //配置拒绝谁访问
        initParams.put("deny","192.168.1.106");
        bean.setInitParameters(initParams);
        return bean;
    }

    //2.配置一个监控的filter
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());
        //设置初始化参数
        //配置的参数可在WebStatFilter()中查看
        Map<String,String> initParams = new HashMap<>();
        //排除拦截的请求
        initParams.put("exclusions","*.js,*.css,/druid/*");
        bean.setInitParameters(initParams);
        //设置拦截的请求
        bean.setUrlPatterns(Arrays.asList("/*"));
        return bean;
    }
}

4. Spring Boot整合MyBatis

4.1 创建MyBatis基础环境

创建工程

选中需要的模块

image-20201225160203149

引入的依赖

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

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

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot</artifactId>
            <version>2.4.1</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.alibaba/druid  Druid依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.4</version>
        </dependency>
    </dependencies>

引入Druid数据源,配置属性文件,配置DruidConfig配置类,具体参照Spring Boot整合Druid数据源

创建数据库表

在resources/sql文件夹下创建sql脚本

image-20201225164632725

配置属性文件

initialization-mode: always
schema:
  - classpath:sql/department.sql
  - classpath:sql/employee.sql

创建Java-Bean

Employee.java

package com.why.springboot.entity;

/**
 * @Description TODO 员工表
 * @Author why
 * @Date 2020/12/25 17:10
 * Version 1.0
 **/
public class Employee {
    private Integer id;
    private String lastName;
    private Integer gender;
    private String email;
    private Integer d_id;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public Integer getGender() {
        return gender;
    }

    public void setGender(Integer gender) {
        this.gender = gender;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getD_id() {
        return d_id;
    }

    public void setD_id(Integer d_id) {
        this.d_id = d_id;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", lastName='" + lastName + '\'' +
                ", gender=" + gender +
                ", email='" + email + '\'' +
                ", d_id=" + d_id +
                '}';
    }
}

Department.java

package com.why.springboot.entity;

/**
 * @Description TODO 部门表
 * @Author why
 * @Date 2020/12/25 17:13
 * Version 1.0
 **/
public class Department {
    private Integer id;
    private String departmentName;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getDepartmentName() {
        return departmentName;
    }

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }

    @Override
    public String toString() {
        return "Department{" +
                "id=" + id +
                ", departmentName='" + departmentName + '\'' +
                '}';
    }
}
4.2 使用MyBatis操纵数据库
4.2.1 Mybatis注解版

创建mapper实现操作数据库

package com.why.springboot.mapper;

import com.why.springboot.entity.Department;
import org.apache.ibatis.annotations.*;

/**
 * @Description TODO 操作Department表
 * @Author why
 * @Date 2020/12/25 17:18
 * Version 1.0
 **/
@Mapper//指定操作数据库的mapper
public interface DepartmentMapper {

    /**
     * 插入数据
     *
     * @Options(useGeneratedKeys = true,keyProperty = "id") 获取到自增id
     *
     * @param department
     * @return
     */
    @Options(useGeneratedKeys = true,keyProperty = "id")
    @Insert("INSERT INTO department(departmentName) VALUES(#{departmentName})")
    public int insertDept(Department department);

    /**
     * 删除一条数据
     * @param id
     * @return
     */
    @Delete("DELETE FROM department WHERE id=#{id}")
    public int deleteDeptById(Integer id);

    /**
     * 更新数据
     * @param department
     * @return
     */
    @Update("UPDATE department SET departmentName=#{departName} WHERE id = #{id}")
    public int updateDept(Department department);

    /**
     * 查询数据
     * @param id
     * @return
     */
    @Select("SELECT * FROM department WHERE id=#{id}")
    public Department getDeptById(Integer id);
}

测试

@RestController
public class DeptController {

    @Autowired
    DepartmentMapper departmentMapper;

    /**
     * 通过id查找
     * @param id
     * @return
     */
    @GetMapping("/dept/{id}")
    public Department getDepartment(@PathVariable("id") Integer id){
        return departmentMapper.getDeptById(id);
    }

    /**
     * 根据传入的部门名称插入部门
     * @param department
     * @return
     */
    @GetMapping("/dept")
    public Department insertDept(Department department){
        departmentMapper.insertDept(department);
        return department;
    }
}

注意:

  1. 获取自增的主键用于返回数据

    @Options(useGeneratedKeys = true,keyProperty = "id")
    
  2. 当Java-Bean和数据库表的名称不一致时

    如Java-Bean:departmentName

    数据库表:department_name

    使用如下规则自定义Mybatis规则

    @Configuration
    public class MyBatisConfig {
    
        /**
         * 配置当Java-bean和数据库表命名不同时,依然能够注入,自定义Mybatis配置规则
         * @return
         */
        @Bean
        public ConfigurationCustomizer configurationCustomizer(){
            return new ConfigurationCustomizer(){
                @Override
                public void customize(org.apache.ibatis.session.Configuration configuration) {
                    configuration.setMapUnderscoreToCamelCase(true);
                }
            };
        }
    }
    
  3. mapper包特别多的时候可以使用配置添加如下注解,批量扫描

    @MapperScan(value = "com.why.springboot.mapper")//自动扫描此包下的所有mapper
    
4.2.2 Mybatiss使用配置文件版

创建mapper文件

package com.why.springboot.mapper;

import com.why.springboot.entity.Employee;
import org.apache.ibatis.annotations.Mapper;

/**
 * @Description TODO Employee表的数据库操作
 * @Author why
 * @Date 2020/12/29 12:05
 * Version 1.0
 **/
@Mapper//将接口扫描装配到容器中
public interface EmployeeMapper {

    /**
     * 查询
     * @param id
     * @return
     */
    public Employee getEmpById(Integer id);

    /**
     * 添加
     * @param employee
     */
    public void insertEmp(Employee employee);
}

创建如下目录

image-20201229135058248

mybatis-config.xml,Mybatis全局配置

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--    设置数据表和Java-bean命名方式的不同-->
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
</configuration>

EmployeeMapper.xml,mapper映射文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- namespace绑定mapper文件的全类名-->
<mapper namespace="com.why.springboot.mapper.EmployeeMapper">
<!--    public Employee getEmpById(Integer id);-->
    <select id="getEmpById" resultType="com.why.springboot.entity.Employee">
        select * from employee where id = #{id}
    </select>
<!--    public void insertEmp(Employee employee);-->
    <insert id="insertEmp">
        insert into employee(lastName,email,gender,d_id) values (#{lastNmae},#{email},#{gender},#{d_id})
    </insert>
</mapper>

在yml文件中设置Mybatis的相关配置

#配置Mybatis
mybatis:
  #Mybatis全局配置文件的位置
  config-location: classpath:mybatis/mybatis-config.xml
  mapper-locations: classpath:mybatis/mapper/*.xml

Spring Boot整合JPA

1. JPA简介

JPA:基于ORM(Object Relation Mapping)思想

2. 使用基本步骤
  1. 编写实体类(Java-Bean)和数据表进行映射,并配置好映射关系

    package com.why.springboot.entity;
    
    import javax.persistence.*;
    
    /**
     * @Description TODO 用户实体类
     *
     * 配置映射关系使用JPA注解:
     * @Author why
     * @Date 2020/12/29 14:27
     * Version 1.0
     **/
    @Entity//告诉JPA这是一个实体类
    @Table(name = "user")//指定和哪个数据表对应,如果省略默认表名是类名小写user
    public class User {
    
        @Id//标注这是一个主键
        @GeneratedValue(strategy = GenerationType.IDENTITY)//自增逐渐
        private Integer id;
        @Column(name = "last_name",length = 50)//表示对应的列
        private String lastName;
        @Column(name = "email")//如果省略列名就是属性名
        private String email;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getLastName() {
            return lastName;
        }
    
        public void setLastName(String lastName) {
            this.lastName = lastName;
        }
    
        public String getEmail() {
            return email;
        }
    
        public void setEmail(String email) {
            this.email = email;
        }
    }
    
  2. 编写Dao接口操作实体类对应的数据表

    package com.why.springboot.repository;
    
    import com.why.springboot.entity.User;
    import org.springframework.data.jpa.repository.JpaRepository;
    
    /**
     * @Description TODO 操作JAP数据库
     *
     * 继承JpaRepository<User,Integer>,User数据表对应的实体类,Integer主键对应的类型
     *
     * @Author why
     * @Date 2020/12/29 14:42
     * Version 1.0
     **/
    public interface UserRepository extends JpaRepository<User,Integer> {
    
    }
    
  3. yaml配置文件基本配置

    spring:
      datasource:
        url: jdbc:mysql://localhost:3306/data?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
        username: root
        password: root
        driver-class-name: com.mysql.cj.jdbc.Driver
        initialization-mode: always
      jpa:
        hibernate:
          #更新或者创建数据表
          ddl-auto: update
        #在控制台显示SQL
        show-sql: true
    

    注意:

    配置nitialization-mode: always否则建表不成功

  4. 使用JPA操作数据库数据

    /**
     * @Description TODO 使用增删改查
     * @Author why
     * @Date 2020/12/29 14:56
     * Version 1.0
     **/
    @RestController
    public class UserController {
    
        @Autowired
        UserRepository userRepository;
    
        @GetMapping("/user/{id}")
        public User getUser(@PathVariable("id") Integer id){
            User user = userRepository.findById(id).get();
            return user;
        }
    
        @GetMapping("/user")
        public User insertUser(User user){
            User save = userRepository.save(user);
            return save;
        }
    }
    

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值