5 数据访问

最新推荐文章于 2023-06-27 04:46:58 发布

破剑茶寮

最新推荐文章于 2023-06-27 04:46:58 发布

阅读量89

点赞数

分类专栏： SpringBoot

本文链接：https://blog.csdn.net/qq_44633875/article/details/118462742

版权

SpringBoot 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

数据访问

SQL

数据源的自动配置-HikariDataSource

导入JDBC场景

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

数据库版本和驱动版本对应

默认版本：<mysql.version>8.0.22</mysql.version>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<!--            <version>5.1.49</version>-->
</dependency>
想要修改版本
1、直接依赖引入具体版本（maven的就近依赖原则）
2、重新声明版本（maven的属性的就近优先原则）
<properties>
<java.version>1.8</java.version>
<mysql.version>5.1.49</mysql.version>
</properties>

分析自动配置

自动配置的类

DataSourceAutoConfiguration ：数据源的自动配置
修改数据源相关的配置：spring.datasource
数据库连接池的配置，是自己容器中没有DataSource才自动配置的
底层配置好的连接池是：HikariDataSource

@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

DataSourceTransactionManagerAutoConfiguration：事务管理器的自动配置
JdbcTemplateAutoConfiguration： JdbcTemplate的自动配置，可以来对数据库进行crud
可以修改这个配置项@ConfigurationProperties(prefix = “spring.jdbc”) 来修改JdbcTemplate
@Bean@Primary JdbcTemplate；容器中有这个组件
JndiDataSourceAutoConfiguration： jndi的自动配置
XADataSourceAutoConfiguration：分布式事务相关的

修改配置项

spring:
	datasource:
		url: jdbc:mysql://localhost:3306/db_account
		username: root
		password: 123456
		driver-class-name: com.mysql.jdbc.Driver

测试

package com.qin.boot;

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 java.util.List;
import java.util.Map;

@SpringBootTest
class Springboot04ApplicationTests {
    @Autowired
    JdbcTemplate jdbcTemplate;
    @Test
    void contextLoads() {
        String s = jdbcTemplate.queryForObject("select count(1) from smbms_role", String.class);
        List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from smbms_role");
        for (Map<String, Object> map : maps) {
            System.out.println(map);
        }
        System.out.println(s);
    }
}

使用Druid数据源

druid官方github地址

https://github.com/alibaba/druid

整合第三方技术的两种方式

自定义
找starter

自定义

引入依赖

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

注入组件

package com.qin.boot.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.sql.SQLException;
import java.util.Arrays;

@Configuration
public class DataSourceConfig {
    //配置了这个数据源之后，默认的数据源将不再配置
    @Bean
    @ConfigurationProperties("spring.datasource")//使用配置类绑定数据源属性
    public DataSource dataSource() throws SQLException {
        DruidDataSource dataSource = new DruidDataSource();
        //stat:打开监控功能   wall：打开sql防火墙
        //也可以在application.yml中配置  spring.datasource.filters: stat,wall
//        dataSource.setFilters("stat,wall");
        return dataSource;
    }
    /**
     * 配置druid servlet的功能
     * @return
     */
    @Bean
    public ServletRegistrationBean statViewServlet(){
        StatViewServlet viewServlet = new StatViewServlet();
        ServletRegistrationBean<StatViewServlet> registrationBean = new ServletRegistrationBean<>(viewServlet,"/druid/*");
        //设置监控页面的用户登录
        registrationBean.addInitParameter("loginUsername","root");
        registrationBean.addInitParameter("loginPassword","123456");
        return registrationBean;
    }
    /**
     * 配置Web应用关联监控功能
     * @return
     */
    @Bean
    public FilterRegistrationBean webStatFilter(){
        WebStatFilter webStatFilter = new WebStatFilter();
        FilterRegistrationBean<WebStatFilter> registrationBean = new FilterRegistrationBean<>(webStatFilter);
        registrationBean.setUrlPatterns(Arrays.asList("/*"));
        registrationBean.addInitParameter("exclusions","*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return registrationBean;
    }
}

starter

引入依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.17</version>
</dependency>

分析自动配置

扩展配置项 spring.datasource.druid
DruidSpringAopConfiguration.class, 监控SpringBean的；配置项：spring.datasource.druid.aop-patterns
DruidStatViewServletConfiguration.class, 监控页的配置：spring.datasource.druid.stat-view-servlet；默认开启
DruidWebStatFilterConfiguration.class, web监控配置；spring.datasource.druid.web-stat-filter；默认开启
DruidFilterConfiguration.class}) 所有Druid自己filter的配置

配置示例

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/jdbc?useSSL=false
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver

    druid:
      aop-patterns: com.qin.boot.*  #监控SpringBean
      filters: stat,wall     # 底层开启功能，stat（sql监控），wall（防火墙）

      stat-view-servlet:   # 配置监控页功能
        enabled: true
        login-username: root
        login-password: 123
        resetEnable: false   # 重置功能

      web-stat-filter:  # 监控web
        enabled: true
        urlPattern: /*
        exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'


      filter:
        stat:    # 对上面filters里面的stat的详细配置
          slow-sql-millis: 1000  # 慢启动时间设置
          logSlowSql: true    
          enabled: true
        wall:
          enabled: true
          config:
            drop-table-allow: false    # 是否可执行删除表操作

SpringBoot配置示例

https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter

配置项列表https://github.com/alibaba/druid/wiki/DruidDataSource%E9%85%8D%E7%BD%AE%E5%B1%9E%E6%80%A7%E5%88%97%E8%A1%A8

整合MyBatis操作

官网地址:https://github.com/mybatis

引入依赖

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

配置模式

全局配置文件
SqlSessionFactory: 自动配置好了
SqlSession：自动配置了 SqlSessionTemplate 组合了SqlSession
@Import(AutoConfiguredMapperScannerRegistrar.class）；
Mapper：只要我们写的操作MyBatis的接口标准了 @Mapper 就会被自动扫描进来

@EnableConfigurationProperties(MybatisProperties.class) ： MyBatis配置项绑定类。
@AutoConfigureAfter({ DataSourceAutoConfiguration.class, MybatisLanguageDriverAutoConfiguration.class })
public class MybatisAutoConfiguration{}
@ConfigurationProperties(prefix = "mybatis")
public class MybatisProperties

可以修改配置文件中 mybatis 开始的所有；

# 配置mybatis规则
mybatis:
  config-location: classpath:mybatis/mybatis-config.xml  #全局配置文件位置
  mapper-locations: classpath:mybatis/mapper/*.xml  #sql映射文件位置

编写业务

package com.qin.boot.bean;

import lombok.Data;
//实体类
@Data
public class Role {
    private int id;
    private String roleCode;
    private String roleName;
}

package com.qin.boot.mappers;

import com.qin.boot.bean.Role;
import org.apache.ibatis.annotations.Mapper;
//Mapper接口
@Mapper
public interface RoleMapper {
    public Role getRole(int id);
}

<!--Mapper接口--绑定Xml-->
<?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">
<mapper namespace="com.qin.boot.mappers.RoleMapper">
    <!--    public Role getRole(int id); -->
    <select id="getRole" resultType="com.qin.boot.bean.Role">
        select * from smbms_role where id=#{id}
    </select>
</mapper>

package com.qin.boot.service;

import com.qin.boot.bean.Role;
import org.springframework.stereotype.Service;
//Service接口
@Service
public interface RoleService {
    public Role getRoleById(int id);

}

package com.qin.boot.service;

import com.qin.boot.bean.Role;
import com.qin.boot.mappers.RoleMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
//Service 实现类
public class RoleService {
    @Autowired
    private RoleMapper roleMapper;
    public Role getRoleById(int id) {
        Role role = roleMapper.getRole(id);
        return role;
    }
}

@Autowired
private RoleService roleService;
//Contorller
@ResponseBody
@RequestMapping("/mybatis")
public Role getRole(@RequestParam("id") int id){
    return roleService.getRoleById(id);
}

配置规则

# 配置mybatis规则
# 配置 private Configuration configuration; mybatis.configuration下面的所有，就是相当于改mybatis全局配置文件中的值
mybatis:
#  config-location: classpath:mybatis/mybatis-config.xml
  mapper-locations: classpath:mybatis/mapper/*.xml
  configuration:
    map-underscore-to-camel-case: true
    
# 可以不写全局；配置文件，所有全局配置文件的配置都放在configuration配置项中即可
# 一项配置只能出现一次

注解模式

package com.qin.boot.bean;

import lombok.Data;
//实体类
@Data
public class City {
    private int id;
    private String name;
    private String country;
}

package com.qin.boot.mappers;

import com.qin.boot.bean.City;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
//Mapper接口
@Mapper
public interface CityMapper {
    @Select("select * from city where id = #{id}")
    public City getCityById(int id);
}

package com.qin.boot.service;

import com.qin.boot.bean.City;
import com.qin.boot.mappers.CityMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
//Service
@Service
public class CityService {
    @Autowired
    private CityMapper cityMapper;

    public City getCityById(int id){
        return cityMapper.getCityById(id);
    }
}

@Autowired
private CityService cityService;
//Controller
@ResponseBody
@RequestMapping("/city")
public City getCityById(@RequestParam("id")int id){
    return cityService.getCityById(id);
}

总结

引入mybatis-starter
配置application.yaml中，指定mapper-location位置即可
编写Mapper接口并标注@Mapper注解
简单方法直接注解方式
复杂方法编写mapper.xml进行绑定映射
@MapperScan(“com.atguigu.admin.mapper”) 简化，其他的接口就可以不用标注@Mapper注解

整合 MyBatis-Plus 完成CRUD

什么是MyBatis-Plus

MyBatis-Plus（简称 MP）是一个 MyBatis 的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生。

mybatis plus 官网

建议安装 MybatisX 插件

整合MyBatis-Plus

引入依赖

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.1</version>
</dependency>

自动配置

MybatisPlusAutoConfiguration 配置类，MybatisPlusProperties 配置项绑定。mybatis-plus：xxx 就是对mybatis-plus的定制
SqlSessionFactory 自动配置好。底层是容器中默认的数据源
mapperLocations 自动配置好的。有默认值。classpath*:/mapper/*/*.xml；任意包的类路径下的所有mapper文件夹下任意路径下的所有xml都是sql映射文件。建议以后sql映射文件，放在 mapper下
容器中也自动配置好了 SqlSessionTemplate
@Mapper 标注的接口也会被自动扫描；建议直接 @MapperScan(“com.atguigu.admin.mapper”) 批量扫描就行

优点：

只需要我们的Mapper继承 BaseMapper 就可以拥有crud能力

实体类

package com.qin.boot.bean;

import com.baomidou.mybatisplus.annotation.TableField;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    //使用mybatis-plus的内置查询，需要实体类中的属性必须在数据库中存在
    //除非标注了 @TableField(exist = false)
    @TableField(exist = false)
    private String userName;
    @TableField(exist = false)
    private String password;

    private long id;
    private String name;
    private int age;
    private String email;
}

Mapper接口

package com.qin.boot.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.qin.boot.bean.User;
import org.apache.ibatis.annotations.Mapper;

import java.io.Serializable;

@Mapper
//继承 BaseMapper<User> 类 内置了一些简单查询
public interface UserMapper extends BaseMapper<User> {
    @Override
    User selectById(Serializable id);
}

测试

@Autowired
UserMapper userMapper;

@Test
void userMapperTest(){
    User user = userMapper.selectById(1L);
    log.info(String.valueOf(user));
}

实现分页功能

Service

package com.qin.boot.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.qin.boot.bean.User;

/**
 * 实现 IService<User> 接口
 *      参数User:操作User类
 *      内置了一些方法
 */
public interface UserService extends IService<User> {

}

package com.qin.boot.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.qin.boot.bean.User;
import com.qin.boot.mapper.UserMapper;
import com.qin.boot.service.UserService;
import org.springframework.stereotype.Service;

/**
 * 继承ServiceImpl<UserMapper,User> 接口
 *  参数UserMapper:传入xxxMapper
 *  参数User:操作User类
 */
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper,User> implements UserService {
}

Controller

package com.qin.boot.controller;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.qin.boot.bean.User;
import com.qin.boot.exception.UserException;
import com.qin.boot.service.UserService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;

import java.util.List;


@Controller
@Slf4j
public class TableController {
    @Autowired
    private UserService userService;
    /**
     *
     * @param pn   当前页码,默认为 1
     * @param model
     * @return
     */
    @GetMapping("/dynamic_table")
    public String dynamic_table(@RequestParam(value = "pn",defaultValue = "1")long pn, Model model){
        //构造分页参数 pn:当前页  size:页容量
        Page<User> page=new Page(pn,2L);
        //调用page进行分页
        Page<User> userPage = userService.page(page);
        long current = userPage.getCurrent();//获取当前页
        Long size = userPage.getSize();//获取页容量
        List<User> records = userPage.getRecords();//获取查询结果
        log.info("当前页:{},页容量:{},结果:{}",current,size,records);
        model.addAttribute("users",userPage);
        return "table/dynamic_table";
    }
}

html

<div class="panel-body">
    <div class="adv-table">
        <table  class="display table table-bordered table-striped" id="user-table">
            <thead>
            <tr>
                <th>#</th>
                <th>Name</th>
                <th>Age</th>
                <th>Email</th>
            </tr>
            </thead>
            <tbody>
            <tr class="gradeX" th:each="user,stats:${users.records}">
                <td th:text="${user.getId()}"></td>
                <td th:text="${user.getName()}">Internet Explorer 4.0</td>
                <td th:text="${user.getAge()}"></td>
                <td th:text="${user.getEmail()}"></td>
            </tr>
            </tbody>
        </table>
        <div class="row-fluid">
            <div class="span6">
                <div class="dataTables_info" id="dynamic-table_info">
                    当前第[[${users.current}]]页  总计 [[${users.pages}]]页  共[[${users.total}]]条记录
                </div>
            </div>
            <div class="span6">
                <div class="dataTables_paginate paging_bootstrap pagination">
                    <ul>
                        <li class="prev disabled"><a href="#">← 前一页</a></li>
                        <li th:class="${num == users.current?'active':''}" th:each="num:${#numbers.sequence(1,users.pages)}" >
                            <a th:href="@{/dynamic_table(pn=${num})}">[[${num}]]</a>
                        </li>
                        <li class="next disabled"><a href="#">下一页 → </a></li>
                    </ul>
                </div>
            </div>
        </div>
    </div>
</div>

                </div>
            </div>
            <div class="span6">
                <div class="dataTables_paginate paging_bootstrap pagination">
                    <ul>
                        <li class="prev disabled"><a href="#">← 前一页</a></li>
                        <li th:class="${num == users.current?'active':''}" th:each="num:${#numbers.sequence(1,users.pages)}" >
                            <a th:href="@{/dynamic_table(pn=${num})}">[[${num}]]</a>
                        </li>
                        <li class="next disabled"><a href="#">下一页 → </a></li>
                    </ul>
                </div>
            </div>
        </div>
    </div>
</div>