Springboot项目中解决Druid DataSource 连接Mysql 存取 emoji表情 - 寻知者的个人页面 - OSCHINA
一、原理：
应用获取数据库连接前，设置Session 字符集 "SET NAMES utf8mb4"
1)SpringMVC项目中，直接在数据源 xml 文件中添加 ：
<property name="connectionInitSqls" value="set names utf8mb4;" />
如图：
2）在Springboot项目中，默认数据源用的是tomcat数据源，如果使用druid数据源的话，则需要自己编写数据源配置类。
二、自己编写数据源配置
1）配置文件
spring:
datasource:
#驱动配置信息
url: jdbc:mysql://ip:port/dbname?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
username: xxxx
password: xxxx
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.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
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
filters: stat,wall,log4j
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=50002)数据源配置类
import com.alibaba.druid.pool.DruidDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Collections;
import java.util.StringTokenizer;
/\*\*
\* @author Charlie
\* @create 2018-03-13 14:37
**/
@Configuration
public class DruidDBConfig {
private Logger logger = LoggerFactory.getLogger(DruidDBConfig.class);
@Value("${spring.datasource.url}")
private String dbUrl;
@Value("${spring.datasource.username}")
private String username;
@Value("${spring.datasource.password}")
private String password;
@Value("${spring.datasource.driver-class-name}")
private String driverClassName;
@Value("${spring.datasource.initialSize}")
private int initialSize;
@Value("${spring.datasource.minIdle}")
private int minIdle;
@Value("${spring.datasource.maxActive}")
private int maxActive;
@Value("${spring.datasource.maxWait}")
private int maxWait;
@Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
private int timeBetweenEvictionRunsMillis;
@Value("${spring.datasource.minEvictableIdleTimeMillis}")
private int minEvictableIdleTimeMillis;
@Value("${spring.datasource.validationQuery}")
private String validationQuery;
@Value("${spring.datasource.testWhileIdle}")
private boolean testWhileIdle;
@Value("${spring.datasource.testOnBorrow}")
private boolean testOnBorrow;
@Value("${spring.datasource.testOnReturn}")
private boolean testOnReturn;
@Value("${spring.datasource.poolPreparedStatements}")
private boolean poolPreparedStatements;
@Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
private int maxPoolPreparedStatementPerConnectionSize;
@Value("${spring.datasource.filters}")
private String filters;
@Value("{spring.datasource.connectionProperties}")
private String connectionProperties;
@Bean //声明其为Bean实例
@Primary //在同样的DataSource中，首先使用被标注的DataSource
public DataSource dataSource() {
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(this.dbUrl);
datasource.setUsername(username);
datasource.setPassword(password);
datasource.setDriverClassName(driverClassName);
//configuration
String connectionInitSqls = "SET NAMES utf8mb4";
StringTokenizer tokenizer = new StringTokenizer(connectionInitSqls, ";");
datasource.setConnectionInitSqls(Collections.list(tokenizer));//重点设置该参数
datasource.setInitialSize(initialSize);
datasource.setMinIdle(minIdle);
datasource.setMaxActive(maxActive);
datasource.setMaxWait(maxWait);
datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
datasource.setValidationQuery(validationQuery);
datasource.setTestWhileIdle(testWhileIdle);
datasource.setTestOnBorrow(testOnBorrow);
datasource.setTestOnReturn(testOnReturn);
datasource.setPoolPreparedStatements(poolPreparedStatements);
datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
try {
datasource.setFilters(filters);
} catch (SQLException e) {
logger.error("druid configuration initialization filter", e);
}
datasource.setConnectionProperties(connectionProperties);
return datasource;
}
}修正
/mnt1t/JAVA_HOME/superdesk-libs/superdesk-libs-mybatis/src/main/java/cn/superdesk/libs/mybatis/datasource/builder/DruidDataSourceBuilder.java
package cn.superdesk.libs.mybatis.datasource.builder;
import java.util.Collections;
import java.util.Properties;
import java.util.StringTokenizer;
import org.springframework.beans.factory.support.BeanDefinitionBuilder;
import com.alibaba.druid.pool.DruidDataSource;
public class DruidDataSourceBuilder {
public static BeanDefinitionBuilder builder(Properties props) {
BeanDefinitionBuilder beanDefinitionBuilder = BeanDefinitionBuilder.genericBeanDefinition(DruidDataSource.class);
if (props.containsKey("name")) beanDefinitionBuilder.addPropertyValue("name", props.getProperty("name"));
beanDefinitionBuilder.addPropertyValue("driverClassName", props.getProperty("driverClassName"));
beanDefinitionBuilder.addPropertyValue("url", props.getProperty("url"));
beanDefinitionBuilder.addPropertyValue("username", props.getProperty("username"));
beanDefinitionBuilder.addPropertyValue("password", props.getProperty("password"));
beanDefinitionBuilder.addPropertyValue("testWhileIdle", Boolean.parseBoolean(props.getProperty("testWhileIdle", "true")));
beanDefinitionBuilder.addPropertyValue("validationQuery", props.getProperty("validationQuery", "SELECT 'x'"));
beanDefinitionBuilder.addPropertyValue("maxActive", Integer.parseInt(props.getProperty("maxActive", "10")));
beanDefinitionBuilder.addPropertyValue("initialSize", Integer.parseInt(props.getProperty("initialSize", "1")));
beanDefinitionBuilder.addPropertyValue("maxIdle", Integer.parseInt(props.getProperty("maxIdle", "4")));
beanDefinitionBuilder.addPropertyValue("minIdle", Integer.parseInt(props.getProperty("minIdle", "1")));
beanDefinitionBuilder.addPropertyValue("maxWait", Long.parseLong(props.getProperty("maxWait", "10000")));
beanDefinitionBuilder.addPropertyValue("minEvictableIdleTimeMillis", Long.parseLong(props.getProperty("minEvictableIdleTimeMillis", "60000")));
beanDefinitionBuilder.addPropertyValue("timeBetweenEvictionRunsMillis", Long.parseLong(props.getProperty("timeBetweenEvictionRunsMillis", "60000")));
beanDefinitionBuilder.addPropertyValue("testOnBorrow", Boolean.parseBoolean(props.getProperty("testOnBorrow", "true")));
beanDefinitionBuilder.addPropertyValue("testOnReturn", Boolean.parseBoolean(props.getProperty("testOnReturn", "false")));
if (props.containsKey("connectionInitSqls")) {
String connectionInitSqls = props.getProperty("connectionInitSqls");
StringTokenizer tokenizer = new StringTokenizer(connectionInitSqls, ";");
// datasource.setConnectionInitSqls();
// 点设置该参数
beanDefinitionBuilder.addPropertyValue("connectionInitSqls", Collections.list(tokenizer));
}
return beanDefinitionBuilder;
}
}#datasource
db.group.size=1
db.shard.size=1000
db.driverClass=com.mysql.jdbc.Driver
db.initialSize=2
db.minIdle=1
db.maxActive=50
db.maxWait=60000
db.timeBetweenEvictionRunsMillis=60000
db.minEvictableIdleTimeMillis=300000
db.testOnBorrow=true
db.testOnReturn=false
db.connectionInitSqls=set names utf8mb4;Original url: Access
Created at: 2019-05-28 16:19:26
Category: default
Tags: none
未标明原创文章均为采集，版权归作者所有，转载无需和我联系，请注明原出处，南摩阿彌陀佛，知识，不只知道，要得到
最新评论