搜索到
3
篇与
的结果
-
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: net.xiangcaowuyu.simplemybatis.entity配置druid根据yaml文件提供的DataSource，分别配置对应的两个数据源。DataSourceConfig.java/** * datasource配置文件 * @author laughing * @date 2020/9/30 * @site https://www.xiangcaowuyu.net */ @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://www.xiangcaowuyu.net */ @Configuration @MapperScan(basePackages = {"net.xiangcaowuyu.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://www.xiangcaowuyu.net */ @Configuration @MapperScan(basePackages = {"net.xiangcaowuyu.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="net.xiangcaowuyu.simplemybatis.mapper.one.UseroneMapper"> <resultMap id="BaseResultMap" type="net.xiangcaowuyu.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="net.xiangcaowuyu.simplemybatis.entity.Userone" useGeneratedKeys="true"> insert into userone (`name`) values (#{name,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.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="net.xiangcaowuyu.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="net.xiangcaowuyu.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="net.xiangcaowuyu.simplemybatis.mapper.two.UsertwoMapper"> <resultMap id="BaseResultMap" type="net.xiangcaowuyu.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="net.xiangcaowuyu.simplemybatis.entity.Usertwo" useGeneratedKeys="true"> insert into usertwo (`name`) values (#{name,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.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="net.xiangcaowuyu.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="net.xiangcaowuyu.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://www.xiangcaowuyu.net */ @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)
