Spring Boot 从零到一：连接数据库

最新推荐文章于 2024-08-19 14:40:16 发布

JCXQ

最新推荐文章于 2024-08-19 14:40:16 发布

阅读量321

点赞数

分类专栏： spring boot idea 文章标签： spring mysql java mybatis

本文链接：https://blog.csdn.net/zmq52007/article/details/105839044

版权

spring boot 同时被 2 个专栏收录

6 篇文章 0 订阅

订阅专栏

idea

3 篇文章 0 订阅

订阅专栏

前言

上一篇已经创建好了一个最简单的Spring Boot项目，那接下来按照我自己的思路是把ta和数据库连接起来。

接下来是用到阿里的数据连接池和mybatisplus，为什么用这他们原因很多，比较重要的点 1国产 2到目前还在持续更新 3习惯问题

正文

先在pom.xml 添加以下依赖

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--MySql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!-- druid数据库 -->
        <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>${druid.version}</version>
        </dependency>

        <!-- 省去手写getter或equals方法 -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!-- mybatisplus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatisplus.version}</version>
        </dependency>

然后去application.yml下添加配置，这一步将配置根据运行环境修改成多个application.yml、application-dev.yml、application-xxx.yml

application.yml

server:
  port: 8089

spring:
  # 环境 dev|test|prod
  profiles:
    active: dev

#mybatis
mybatis-plus:
  #MyBatis Mapper 所对应的 XML 文件位置
  mapper-locations: classpath*:mapper/**/*.xml
  #  MyBaits 别名包扫描路径，通过该属性可以给包中的类注册别名，
  #注册后在 Mapper 对应的 XML 文件中可以直接使用类名，而不用使用全限定的类名（即 XML 中调用的时候不用包含包名）
  typeAliasesPackage: com.zz.ll.*.entity 

  configuration:
    #使用驼峰法映射属性,配置这个resultType可以映射
    map-underscore-to-camel-case: true
  global-config:
    db-config:
      #主键类型  AUTO:"数据库ID自增", INPUT:"用户输入ID", ID_WORKER:"全局唯一ID (数字类型唯一ID)", UUID:"全局唯一ID UUID";
      #id-type: AUTO
      # 配置表明前缀，例如表设计时表名为tb_manager，对应entity为Manager
      #table-prefix: admin_
      #逻辑已删除值
      logic-delete-value: 1
      #逻辑未删除值
      logic-not-delete-value: 0
      # 是否开启like查询，即对 stirng 字段是否使用 like，默认不开启
      # column-like: false
    #原生配置
    configuration:
      map-underscore-to-camel-case: true
      cache-enabled: false
      call-setters-on-nulls: true
      jdbc-type-for-null: 'null'

application-dev.yml

server:
  port: 8088

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://ip:port/databaseName?characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&useSSL=false
      username: ***
      password: ***
      initial-size: 10
      max-active: 100
      min-idle: 10
      max-wait: 60000
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
      #Oracle需要打开注释
      #validation-query: SELECT 1 FROM DUAL
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      stat-view-servlet:
        enabled: true
        url-pattern: /druid/*
        #login-username: admin
        #login-password: admin
      filter:
        stat:
          log-slow-sql: true
          slow-sql-millis: 1000
          merge-sql: false
        wall:
          config:
            multi-statement-allow: true

在启动类加上两个注解

@MapperScan("com.zz.ll.admin.dao")

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})

package com.zz.ll;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;

@MapperScan("com.zz.ll.admin.dao")
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
public class LlApplication {

    public static void main(String[] args) {
        SpringApplication.run(LlApplication.class, args);
    }

}

根据MyBatis-Plus官方文档（https://mp.baomidou.com/guide/quick-start.html#%E5%88%9D%E5%A7%8B%E5%8C%96%E5%B7%A5%E7%A8%8B）写道这里就好了，但是我实际上遇到了问题：Caused by: java.lang.IllegalArgumentException: Property 'sqlSessionFactory' or 'sqlSessionTemplate' are required

这里是默认的他不知道用，所以我们自己手动把bean注入所以创建MybatisPlusConfig

package com.zz.ll.datasource.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;


/**
 * Mybatis-Plus 配置相关
 */
@Configuration
public class MybatisPlusConfig {

    /**
     * Mybatis-Plus 分页插件
     * @return
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        // 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求  默认false
        // paginationInterceptor.setOverflow(false);
        // 设置最大单页限制数量，默认 500 条，-1 不受限制
        // paginationInterceptor.setLimit(500);
        // 开启 count 的 join 优化,只针对部分 left join
        paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
        return paginationInterceptor;
    }

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

当然导致这个还有几种可能

1.@MapperScan("com.zz.ll.admin.dao") 这里的路径不对

2.mapper-locations: classpath*:mapper/**/*.xml
typeAliasesPackage: com.zz.ll.*.entity 这里的路径不对

项目里使用的是自己封装的分页，sql 基本上也是自己写的。这里为了体现Mybatis-Plus的核心功能，我们使用CRUD 接口和自带的分页

package com.zz.ll.admin.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;

@Data
@TableName("admin_user_info")
public class User implements Serializable {
    
    private static final long serialVersionUID = 1L;
    
    @TableId(value = "user_id", type = IdType.INPUT)
    private String id;

    @TableField(value = "s_name")
    private String name;

    @TableField(value = "status")
    private Integer status;

    @TableField(value = "ct_time")
    private Date ctTime;


}

package com.zz.ll.admin.service;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.IService;
import com.zz.ll.admin.entity.User;

import java.util.List;

public interface UserService extends IService<User> {

    public List<User> getList();

    IPage<User> selectPageVo(Page<?> page, Integer status);
}

package com.zz.ll.admin.service.impl;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.zz.ll.admin.dao.UserDao;
import com.zz.ll.admin.entity.User;
import com.zz.ll.admin.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserServiceImpl extends ServiceImpl<UserDao, User> implements UserService {

    @Autowired
    private UserService userService;

    @Autowired
    private UserDao userDao;

    @Override
    public List<User> getList() {
        return userService.list();
    }

    @Override
    public IPage<User> selectPageVo(Page<?> page, Integer status) {
        return userDao.selectPageVo(page,status);
    }
}

package com.zz.ll.admin.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.zz.ll.admin.entity.User;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface UserDao extends BaseMapper<User> {

    /**
     * <p>
     * 查询 : 根据state状态查询用户列表，分页显示
     * </p>
     *
     * @param page 分页对象,xml中可以从里面进行取值,传递参数 Page 即自动分页,必须放在第一位(你可以继承Page实现自己的分页对象)
     * @param status 状态
     * @return 分页对象
     */
    IPage<User> selectPageVo(Page<?> page, Integer status);
}

<?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.zz.ll.admin.dao.UserDao">


    <select id="selectPageVo" resultType="com.zz.ll.admin.entity.User">
      SELECT * from admin_user_info WHERE status=#{status}
    </select>

</mapper>

最后我们打开Druid的sql监控这一块，任然是在MybatisPlusConfig。

package com.zz.ll.datasource.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize;
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;


/**
 * Mybatis-Plus 配置相关
 */
@Configuration
public class MybatisPlusConfig {

    /**
     * Mybatis-Plus 分页插件
     * @return
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        // 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求  默认false
        // paginationInterceptor.setOverflow(false);
        // 设置最大单页限制数量，默认 500 条，-1 不受限制
        // paginationInterceptor.setLimit(500);
        // 开启 count 的 join 优化,只针对部分 left join
        paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
        return paginationInterceptor;
    }

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

    /**
     * 注册一个StatViewServlet
     */
    @Bean
    public ServletRegistrationBean druidStatViewServlet(){
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean();
        servletRegistrationBean.setServlet(new StatViewServlet());
        servletRegistrationBean.addUrlMappings("/druid/*");
        //添加初始化参数：initParams
        //白名单：
        //servletRegistrationBean.addInitParameter("allow","127.0.0.1");
        //IP黑名单 (存在共同时，deny优先于allow) : 如果满足deny的话提示:Sorry, you are not permitted to view this page.
        //servletRegistrationBean.addInitParameter("deny","192.168.1.100");
        //登录查看信息的账号密码.
        //servletRegistrationBean.addInitParameter("loginUsername","admin");
        //servletRegistrationBean.addInitParameter("loginPassword","111111");
        //是否能够重置数据.
        servletRegistrationBean.addInitParameter("resetEnable","false");
        return servletRegistrationBean;
    }

    /**
     * 注册一个：filterRegistrationBean
     */
    @Bean
    public FilterRegistrationBean druidStatFilter(){
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        //添加过滤规则.
        filterRegistrationBean.addUrlPatterns("/*");
        //添加不需要忽略的格式信息.
        filterRegistrationBean.addInitParameter("exclusions","*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }
}

在启动项目后，访问http://localhost:8088/druid/ 即可看到，如果需要登陆输入自己设置的账号密码//servletRegistrationBean.addInitParameter("loginUsername","admin");
//servletRegistrationBean.addInitParameter("loginPassword","111111");