java springboot 初体验（十一）对接mysql

上一篇
1. 　　java springboot 初体验（十）对接ut
2. https://www.cnblogs.com/zwjvzwj/p/16616151.html

pom文件添加依赖

　　数据库依赖

        <!--alibaba druid datasource Druid的依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>${druid.version}</version>
        </dependency>

mybatis-plus。依赖

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!-- mybatisPlus 核心库 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.1.0</version>
        </dependency>

日志依赖

        <!-- https://mvnrepository.com/artifact/log4j/log4j -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

　　添加配置文件

application.yml或者application.properties添加配置，如果之前对结果apollo。也可以配置在apollo中

application.yml

spring:
    datasource:
        # DataSource settings
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://ats-staging.mysql.rds.aliyuncs.com/moka_workwechat_staging-22?useUnicode=true&characterEncoding=utf8
        username: moka_root
        password: Moka2015
        driverClassName: com.mysql.cj.jdbc.Driver
        #连接池的配置信息
        initialSize: 5
        minIdle: 5
        maxActive: 20
        maxWait: 60000
        # 配置获取连接等待超时的时间
        timeBetweenEvictionRunsMillis: 60000
        # 配置一个连接在池中最小生存的时间，单位是毫秒
        minEvictableIdleTimeMillis: 300000
        validationQuery: SELECT 1 FROM DUAL
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        # 打开PSCache，并且指定每个连接上PSCache的大小
        poolPreparedStatements: true
        maxPoolPreparedStatementPerConnectionSize: 20
        # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
        filters: stat,wall,log4j
        # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
        connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
        #是否启用StatFilter默认值true
        druid:
            web-stat-filter:
                enabled: true

druid:
    monitor:
        allow: 127.0.0.1
        deny: 0.0.0.0
        loginUsername: root
        loginPassword: root
        resetEnable: false

application.properties

#DataSource settings

spring.datasource.type = com.alibaba.druid.pool.DruidDataSource
spring.datasource.url = jdbc:mysql://ats-staging.mysql.rds.aliyuncs.com/moka_workwechat_staging-22?useUnicode=true&characterEncoding=utf8
spring.datasource.username = moka_root
spring.datasource.password = Moka2015
spring.datasource.driverClassName = com.mysql.cj.jdbc.Driver

#连接池的配置信息
spring.datasource.initialSize = 5
spring.datasource.minIdle=5
spring.datasource.maxActive=20

# 配置获取连接等待超时的时间
spring.datasource.maxWait=60000

# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.timeBetweenEvictionRunsMillis=60000

# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 1 FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false

# 打开PSCache，并且指定每个连接上PSCache的大小
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20

# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.filters=stat,wall,log4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

#是否启用StatFilter默认值true
spring.datasource.druid.web-stat-filter.enabled=true
#多个白名单IP以逗号分隔
druid.monitor.allow=127.0.0.1
#多个黑名单IP以逗号分隔
druid.monitor.deny=0.0.0.0
#druid监控管理界面登录帐号
druid.monitor.loginUsername=root
#druid监控管理界面登录密码
druid.monitor.loginPassword=root
#是否开启重置功能
druid.monitor.resetEnable=false


log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

　　添加数据库配置文件

package com.zwj.zwjproject.configuration;


import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

/**
 * @ClassName: DruidConfiguration
 * @Author zhangwujie
 * @Date 2022/8/25 6:00 下午
 * @Description: 如果配置文件druid.properties
 */
@Configuration
public class DruidConfiguration {
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource druidDataSource() {
        return new DruidDataSource();
    }

}

package com.zwj.zwjproject.configuration;

import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.beans.factory.annotation.Value;
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;

/**
 * @ClassName: DruidMonitorCOnfiguration
 * @Author zhangwujie
 * @Date 2022/8/26 10:59 上午
 * @Description:
 */
@Configuration
public class DruidMonitorConfiguration {

    @Value("${druid.monitor.allow:127.0.0.1}")
    private String allow;
    @Value("${druid.monitor.deny}")
    private String deny;
    @Value("${druid.monitor.loginUsername:admin}")
    private String loginUsername;
    @Value("${druid.monitor.loginPassword:password}")
    private String loginPassword;
    @Value("${druid.monitor.resetEnable:false}")
    private String resetEnable;

    /**
     * 配置Druid的监控
     * @return
     */
    @Bean
    public ServletRegistrationBean druidStatViewServlet() {
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        servletRegistrationBean.addInitParameter("allow", this.allow);
        servletRegistrationBean.addInitParameter("deny", this.deny);
        servletRegistrationBean.addInitParameter("loginUsername", this.loginUsername);
        servletRegistrationBean.addInitParameter("loginPassword", this.loginPassword);
        servletRegistrationBean.addInitParameter("resetEnable", this.resetEnable);
        return servletRegistrationBean;
    }

    /**
     * 配置一个web监控的filter
     * @return
     */
    @Bean
    public FilterRegistrationBean druidStatFilter() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }
}

　　mybatis配置

　　配置数据表对象

package com.zwj.zwjproject.entity;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import lombok.experimental.Accessors;

import java.math.BigDecimal;
import java.util.Date;

/**
 * @ClassName: WorkwechatLicenseOrder
 * @Author zhangwujie
 * @Date 2022/7/11 11:05 上午
 * @Description:
 */
@Data
@Accessors(chain = true)
@TableName("order")
public class orderPO {

    @TableField("id")
    private Long id;

    @TableField("order_id")
    private String orderId;

}

　　创建一个数据表的mapper

package com.zwj.zwjproject.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zwj.zwjproject.entity.orderPO;
import org.apache.ibatis.annotations.*;

@Mapper
public interface orderMapper extends BaseMapper<orderPO> {

}

　　写一个service层。我单独写了一个dbservice层

package com.zwj.zwjproject.dbService;

import com.baomidou.mybatisplus.extension.service.IService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.zwj.zwjproject.entity.orderPO;
import com.zwj.zwjproject.mapper.orderMapper;
import org.springframework.stereotype.Service;

/**
 * @ClassName: WorkwechatLicenseOrderDBService
 * @Author zhangwujie
 * @Date 2022/8/26 3:01 下午
 * @Description:
 */
@Service
public class OrderDBService extends ServiceImpl<orderMapper, orderPO> implements IService<orderPO>  {
}

　　控制器里直接写一个新接口调用查询数据

    @ApiOperation(value = "测试mysql接口")
    @PostMapping(value = "/testMysql")
    public RespEntity testMysql() {
        try {
            int count = orderDBService.count();
            orderPO byId = orderDBService.getById(3);

            List<orderPO> list = orderDBService.list();
            return RespEntity.success(list);
        } catch (Exception e) {
            log.info("TestOuterController testMysql catch error msg={}", e.getMessage(), e);
            return RespEntity.fail(e.getMessage());
        }
    }

　　启动服务
调用接口查询数据
下一篇
1. 　　java springboot 初体验（十二）对接eureka
2. https://www.cnblogs.com/zwjvzwj/p/16640193.html

posted @ 2022-08-26 16:59 zwjvzwj 阅读(114) 评论(0) 编辑收藏举报

刷新页面返回顶部

zwjvzwj

java springboot 初体验 （十一）对接mysql

公告

java springboot 初体验（十一）对接mysql