搜索到
4
篇与
的结果
-
druid discard long time none received connection解决办法 Spring Boot集成阿里巴巴druid时，如果你使用的是MySQL数据库，可能会在控制台不定时的输出以下警告信息虽然这个警告信息不会影响程序的正常运行，但是这种日志不仅会让人感到疑惑，心理上也会比较难受。网上出现这种问题的很多，也有很多人提供了类似修改环境变量的方式进行屏蔽，但是很多说的都比较含糊。问题分析通过日志，我们可以看到，警告信息是c.a.druid.pool.DruidAbstractDataSource这个类打印的，我们可以打开对应jar包的位置具体查看一下打印日志的方法经过分析，可以看到这个问题的根源在于Druid连接池对MySQL连接的处理逻辑。在DruidAbstractDataSource类的testConnectionInternal方法中，如果连接空闲时间超过60秒，Druid会认为该连接已经不再有效，并将其丢弃。{message type="warning" content="我这里出现问题的druid版本是1.2.4"/}解决办法这里主要介绍两种处理方式，方法一是升级版本，升级到一个不打印警告的版本即可，第二方法可以通过druid.mysql.usePingMethod系统属性禁用掉ping检测。方法一、升级版本具体哪个版本去掉的提示我没细查，我这里是升级到了1.2.23版本，可以确认这个版本不会打印这条警告信息，低于或者高于此版本都可能会再次打印这条警告信息。方法二、设置系统属性通过设置druid.mysql.usePingMethod=false来避免使用MySQL的Ping方法来检查连接有效性。我是通过在启动类main方法设置的public static void main(String[] args) { System.setProperty("druid.mysql.usePingMethod", "false");//去掉discard long time none received connection提示 SpringApplication.run(LeeFrameApplication.class, args); System.out.println("启动成功"); }{message type="info" content="⚠️设置属性值的方法一定要在run方法前面"/} -
druid discard long time none received connection 阿里巴巴的Druid是一个Java数据库连接池（JDBC connection pool）组件，由阿里巴巴开发并开源。它不仅是一个数据库连接管理器，还提供数据源代理，SQL解析，监控等功能。Druid的主要特性包括：高效性：Druid使用了高效的连接池实现，减少了创建和销毁连接的开销。监控功能：Druid可以监控应用程序中的SQL执行情况，帮助开发者优化数据库操作。SQL解析：Druid能够解析SQL语句，对于一些复杂的SQL语句，可以进行优化或改写。防SQL注入：Druid通过SQL解析，能有效防止SQL注入攻击。高可用性：Druid支持主从读写分离，负载均衡等高级功能，提高系统的稳定性和性能。兼容性：Druid对主流的JDBC驱动和数据库都有很好的兼容性。当我们执行Sql时，如果当前执行时间与上一次执行Sql的时间间隔60s以上，在日志中就会有一条日志discard long time none received connection. , jdbcUrl : jdbc:mysql://xxx:3306/xxx?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC, version : 1.2.5, lastPacketReceivedIdleMillis : 62681这个信息，不影响程序正常运行，如果我们想屏蔽掉这个消息，可以在启动类中，加一个静态代码块 static { System.getProperties().put("druid.mysql.usePingMethod", "false"); }再次启动测试，间隔60s以上请求
-
Spring Boot alibaba druid配置输出sql 用的slf4j打印的sql# 数据源配置 spring: datasource: type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.cj.jdbc.Driver druid: # 主库数据源 master: url: jdbc:mysql://localhost:3306/leeframe?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8 username: root password: root # 从库数据源 slave: # 从数据源开关/默认关闭 enabled: false url: username: password: # 初始连接数 initialSize: 5 # 最小连接池数量 minIdle: 10 # 最大连接池数量 maxActive: 20 # 配置获取连接等待超时的时间 maxWait: 60000 # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 timeBetweenEvictionRunsMillis: 60000 # 配置一个连接在池中最小生存的时间，单位是毫秒 minEvictableIdleTimeMillis: 300000 # 配置一个连接在池中最大生存的时间，单位是毫秒 maxEvictableIdleTimeMillis: 900000 # 配置检测连接是否有效 validationQuery: SELECT 1 FROM DUAL testWhileIdle: true testOnBorrow: false testOnReturn: false webStatFilter: enabled: true statViewServlet: enabled: true # 设置白名单，不填则允许所有访问 allow: url-pattern: /druid/* # 控制台管理用户名和密码 login-username: login-password: filter: stat: enabled: true # 慢SQL记录 log-slow-sql: true slow-sql-millis: 1000 merge-sql: true wall: config: multi-statement-allow: true slf4j: enabled: true statement-create-after-log-enabled: false statement-log-enabled: true statement-executable-sql-log-enable: true statement-log-error-enabled: true result-set-log-enabled: false logging: level: druid: sql: Statement: DEBUG
-
Spring boot整合mybatis多数据源简单使用 日常开发中，我们很少会在一个应用程序中同时使用多个数据源。但是，如果涉及一些数据迁移等应用，可能会涉及将数据从一个库迁移到另外一个库，甚至是不同类型的数据库，比如MySQL到Oracle。这篇博文，我们不介绍mybatis的基本使用，只介绍基于mybatis配置多数据源的方法。数据源准备我这里用了本地MySQL两个库，分别是mybatisone和mybatistwo,mybatisone库中有一张userone表，mybatistwo库中有一张usertwo表，建表sql如下：userone/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 80021 Source Host : localhost:3306 Source Schema : mybatisone Target Server Type : MySQL Target Server Version : 80021 File Encoding : 65001 Date: 01/10/2020 00:34:29 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for userone -- ---------------------------- DROP TABLE IF EXISTS `userone`; CREATE TABLE `userone` ( `id` bigint(0) NOT NULL, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of userone -- ---------------------------- INSERT INTO `userone` VALUES (1, '姓名1'); SET FOREIGN_KEY_CHECKS = 1; usertwo/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 80021 Source Host : localhost:3306 Source Schema : mybatistwo Target Server Type : MySQL Target Server Version : 80021 File Encoding : 65001 Date: 01/10/2020 00:34:35 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for usertwo -- ---------------------------- DROP TABLE IF EXISTS `usertwo`; CREATE TABLE `usertwo` ( `id` bigint(0) NOT NULL, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of usertwo -- ---------------------------- INSERT INTO `usertwo` VALUES (1, '姓名2'); SET FOREIGN_KEY_CHECKS = 1; 添加依赖主要依赖如下 <!--mybatis依赖--> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.3</version> </dependency> <!--MySQL驱动--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <!--lombok--> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <!--druid--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.14</version> </dependency>application.yaml配置文件yaml文件配置数据源信息以及mybatis的配置信息spring: datasource: mybatisone: url: jdbc:mysql://localhost:3306/mybatisone?chartset=utf8mb4&serverTimezone=UTC&usessl=false username: root password: root type: com.alibaba.druid.pool.DruidDataSource mybatistwo: url: jdbc:mysql://localhost:3306/mybatistwo?chartset=utf8mb4&serverTimezone=UTC&usessl=false username: root password: root type: com.alibaba.druid.pool.DruidDataSource mybatis: mapper-locations: classpath*:mapper/*.xml type-aliases-package: cc.lisen.simplemybatis.entity配置druid根据yaml文件提供的DataSource，分别配置对应的两个数据源。DataSourceConfig.java/** * datasource配置文件 * @author laughing * @date 2020/9/30 * @site https://lisen.cc */ @Configuration public class DataSourceConfig { /** * * @return 第一个数据源 */ @Bean @ConfigurationProperties(prefix = "spring.datasource.mybatisone") DataSource dataSourceOne(){ return DruidDataSourceBuilder.create().build(); } /** * * @return 第二个数据源 */ @Bean @ConfigurationProperties(prefix = "spring.datasource.mybatistwo") DataSource dataSourceTwo(){ return DruidDataSourceBuilder.create().build(); } }配置mybatis数据源MyBatisConfigOne.java/** * mybatis第一个配置文件 * @author laughing * @date 2020/9/30 * @site https://lisen.cc */ @Configuration @MapperScan(basePackages = {"cc.lisen.simplemybatis.mapper.one"},sqlSessionFactoryRef = "sqlSessionFactoryOne",sqlSessionTemplateRef = "sqlSessionTemplateOne") public class MyBatisConfigOne { private final Logger logger = LoggerFactory.getLogger(MyBatisConfigOne.class); @Resource(name = "dataSourceOne") DataSource dataSourceOne; @Bean SqlSessionFactory sqlSessionFactoryOne() { SqlSessionFactory sqlSessionFactory = null; try { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/one/*.xml")); sqlSessionFactoryBean.setDataSource(dataSourceOne); sqlSessionFactory = sqlSessionFactoryBean.getObject(); }catch (Exception ex){ logger.error(ex.getMessage()); } return sqlSessionFactory; } @Bean SqlSessionTemplate sqlSessionTemplateOne(){ return new SqlSessionTemplate(sqlSessionFactoryOne()); } }MyBatisConfigTwo.java/** * mybatis第二个配置文件 * @author laughing * @date 2020/9/30 * @site https://lisen.cc */ @Configuration @MapperScan(basePackages = {"cc.lisen.simplemybatis.mapper.two"},sqlSessionFactoryRef = "sqlSessionFactoryTwo",sqlSessionTemplateRef = "sqlSessionTemplateTwo") public class MyBatisConfigTwo { private final Logger logger = LoggerFactory.getLogger(MyBatisConfigTwo.class); @Resource(name = "dataSourceTwo") DataSource dataSourceTwo; @Bean SqlSessionFactory sqlSessionFactoryTwo() { SqlSessionFactory sqlSessionFactory = null; try { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/two/*.xml")); sqlSessionFactoryBean.setDataSource(dataSourceTwo); sqlSessionFactory = sqlSessionFactoryBean.getObject(); }catch (Exception ex){ logger.error(ex.getMessage()); } return sqlSessionFactory; } @Bean SqlSessionTemplate sqlSessionTemplateTwo(){ return new SqlSessionTemplate(sqlSessionFactoryTwo()); } }生成mybatis信息mybatis不是我们要讲解的重点，我这里的信息都是通过Free MyBatis插件自动生成的。不做过多介绍。增加实体Userone.java/** * userone * @author */ @Data public class Userone implements Serializable { private Long id; private String name; private static final long serialVersionUID = 1L; }Usertwo.java/** * usertwo * @author */ @Data public class Usertwo implements Serializable { private Long id; private String name; private static final long serialVersionUID = 1L; }增加mapperUseroneMapper.java@Mapper public interface UseroneMapper { int deleteByPrimaryKey(Long id); int insert(Userone record); int insertSelective(Userone record); Userone selectByPrimaryKey(Long id); int updateByPrimaryKeySelective(Userone record); int updateByPrimaryKey(Userone record); }Usertwo.java/** * usertwo * @author */ @Data public class Usertwo implements Serializable { private Long id; private String name; private static final long serialVersionUID = 1L; }增加xmlUseroneMapper.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="cc.lisen.simplemybatis.mapper.one.UseroneMapper"> <resultMap id="BaseResultMap" type="cc.lisen.simplemybatis.entity.Userone"> <id column="id" jdbcType="BIGINT" property="id"/> <result column="name" jdbcType="VARCHAR" property="name"/> </resultMap> <sql id="Base_Column_List"> id, `name` </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap"> select <include refid="Base_Column_List"/> from userone where id = #{id,jdbcType=BIGINT} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Long"> delete from userone where id = #{id,jdbcType=BIGINT} </delete> <insert id="insert" keyColumn="id" keyProperty="id" parameterType="cc.lisen.simplemybatis.entity.Userone" useGeneratedKeys="true"> insert into userone (`name`) values (#{name,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="cc.lisen.simplemybatis.entity.Userone" useGeneratedKeys="true"> insert into userone <trim prefix="(" suffix=")" suffixOverrides=","> <if test="name != null"> `name`, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="name != null"> #{name,jdbcType=VARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="cc.lisen.simplemybatis.entity.Userone"> update userone <set> <if test="name != null"> `name` = #{name,jdbcType=VARCHAR}, </if> </set> where id = #{id,jdbcType=BIGINT} </update> <update id="updateByPrimaryKey" parameterType="cc.lisen.simplemybatis.entity.Userone"> update userone set `name` = #{name,jdbcType=VARCHAR} where id = #{id,jdbcType=BIGINT} </update> </mapper>UsertwoMapper.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="cc.lisen.simplemybatis.mapper.two.UsertwoMapper"> <resultMap id="BaseResultMap" type="cc.lisen.simplemybatis.entity.Usertwo"> <id column="id" jdbcType="BIGINT" property="id" /> <result column="name" jdbcType="VARCHAR" property="name" /> </resultMap> <sql id="Base_Column_List"> id, `name` </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap"> select <include refid="Base_Column_List" /> from usertwo where id = #{id,jdbcType=BIGINT} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Long"> delete from usertwo where id = #{id,jdbcType=BIGINT} </delete> <insert id="insert" keyColumn="id" keyProperty="id" parameterType="cc.lisen.simplemybatis.entity.Usertwo" useGeneratedKeys="true"> insert into usertwo (`name`) values (#{name,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="cc.lisen.simplemybatis.entity.Usertwo" useGeneratedKeys="true"> insert into usertwo <trim prefix="(" suffix=")" suffixOverrides=","> <if test="name != null"> `name`, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="name != null"> #{name,jdbcType=VARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="cc.lisen.simplemybatis.entity.Usertwo"> update usertwo <set> <if test="name != null"> `name` = #{name,jdbcType=VARCHAR}, </if> </set> where id = #{id,jdbcType=BIGINT} </update> <update id="updateByPrimaryKey" parameterType="cc.lisen.simplemybatis.entity.Usertwo"> update usertwo set `name` = #{name,jdbcType=VARCHAR} where id = #{id,jdbcType=BIGINT} </update> </mapper>配置测试Api/** * @author laughing * @date 2020/9/30 * @site https://lisen.cc */ @RestController public class MyBatisController { @Resource UseroneMapper useroneMapper; @Resource UsertwoMapper usertwoMapper; @RequestMapping("/one") public Userone userone(){ return useroneMapper.selectByPrimaryKey(1L); } @RequestMapping("/two") public Usertwo usertwo(){ return usertwoMapper.selectByPrimaryKey(1L); } }增加两个Api，分别用于获取数据源1和数据源2的数据。整体代码结构里面封装了全局异常，如果不了解的，可以参考 SpringBoot 之 @ControllerAdvice使用场景代码测试我们在分别打开两个请求，查看获取的数据注意事项在配置数据源时，注意设置mapper的位置，即如下代码：sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/one/*.xml"));如果没有设置，可能会提示Invalid bound statement (not found)
