DruidDataSource监控、Sharding
Druid监控
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.22</version>
</dependency>
@Configuration
public class WebInitializer {
@Bean
public ServletRegistrationBean statViewServlet() {
ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
HashMap<String,String> map = new HashMap<>();
map.put("loginUsername","admin");
map.put("loginPassword","123");
servletRegistrationBean.setInitParameters(map);
// 可以添加初始化参数来配置白名单、黑名单、登录用户名和密码等
return servletRegistrationBean;
}
@Bean
public FilterRegistrationBean webStatFilter() {
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
// 可以配置principalSessionName、principalCookieName、principalCookieDomain等
return filterRegistrationBean;
}
}
spring:
application:
name: xmh-service
datasource:
driver-class-name: ${DS_DRIVER:com.mysql.cj.jdbc.Driver}
url:
username:
password:
# type: ${DS_TYPE:com.zaxxer.hikari.HikariDataSource}
type: ${DS_TYPE:com.alibaba.druid.pool.DruidDataSource}
druid:
filters: stat,wall
#慢sql记录
connection-properties: druid.stat.mergeSql=true;
filter:
stat:
slow-sql-millis: 1
#合并多个druiDataSource的监控数据
use-global-data-source-stat: true
https://www.jb51.net/program/3223232p4.htm
官网 https://shardingsphere.apache.org/document/current/en/user-manual/shardingsphere-jdbc/spring-namespace/rules/mix/
springboot启动类
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class, SpringBootConfiguration.class})
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>23.0</version>
</dependency>
@Configuration
@MapperScan(basePackages = "com.pingan.insurance.policy.core.mapper.sharding", sqlSessionFactoryRef = "sqlSessionFactorySharding")//
public class DbShardingConfig {
@Autowired
private PasswordProvider dbPasswordProvider;
@Value("${mysql.policy.write.aliasName}")
private String policyWriteAliasName;
@Bean(name="policyWriteDataSource2",initMethod = "init",destroyMethod = "close")
@ConfigurationProperties(prefix = "mysql.policy.write")
public DruidDataSource policyWriteDataSource2() {
DruidDataSource druidDataSource = new DruidDataSource();
String password=dbPasswordProvider.getPassword(policyWriteAliasName);
druidDataSource.setPassword(password);
druidDataSource.setKeepAlive(true);
druidDataSource.setQueryTimeout(300);
druidDataSource.setTransactionQueryTimeout(30);
druidDataSource.setTestWhileIdle(true);
druidDataSource.setTestOnBorrow(true);
druidDataSource.setTestOnReturn(true);
druidDataSource.setTimeBetweenEvictionRunsMillis(600000);
druidDataSource.setMinEvictableIdleTimeMillis(300000);
return druidDataSource;
}
@Bean(name="xmhshardingDataSource")
public javax.sql.DataSource xmhshardingDataSource(){
System.out.println("xmhshardingDataSource.ds.before***************");
/*
* 1. 数据源集合：dataSourceMap
* 2. 分片规则：shardingRuleConfig
*/
// 配置真实数据源
Map<String, javax.sql.DataSource> dataSourceMap = new HashMap<String, javax.sql.DataSource>();
// 添加数据源.两个数据源ds_0和ds_1
dataSourceMap.put("ds_0",policyWriteDataSource2());
dataSourceMap.put("ds_1",policyWriteDataSource2());
System.out.println("xmhshardingDataSource.ds.before***************1:"+policyWriteDataSource2());
/**
* 需要构建表规则 1. 指定逻辑表. 2. 配置实际节点》 3. 指定主键字段. 4. 分库和分表的规则》
* 数据库：ds_0和ds_1 表：t_order_0 和 t_order_1 t_order表的字段 (order_id,user_id,status)
* 表的分片策略 order_id 库的分片策略 user_id
*/
// 配置t_order表规则
TableRuleConfiguration orderTableRuleConfiguration = new TableRuleConfiguration();
// 指定逻辑表
orderTableRuleConfiguration.setLogicTable("t_order");
// 配置实际节点
// ds_0.t_order_0 , ds_0.t_order_1, ds_1.t_order_0 , ds_1.t_order_1 dataSource_$->{0..1}.t_order_$->{0..1}
// orderTableRuleConfiguration.setActualDataNodes("ds_${0..1}.t_order_${0..1}");
orderTableRuleConfiguration.setActualDataNodes("ds_$->{0..1}.t_order_$->{0..1}");
// 指定主键字段
// orderTableRuleConfiguration.setKeyGeneratorColumnName("order_id");
// 表的分片策略根据 order_id,分成t_order_0,t_order_1两个表
orderTableRuleConfiguration.setTableShardingStrategyConfig(
new InlineShardingStrategyConfiguration("user_id", "t_order_${user_id%2}"));
// 数据库的分片策略 根据user_id，分成ds_0,ds_1两个数据库
orderTableRuleConfiguration.setDatabaseShardingStrategyConfig(
new InlineShardingStrategyConfiguration("user_id", "ds_${user_id%2}"));
// 分片规则：shardingRuleConfig
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfiguration);
shardingRuleConfig.setDefaultDataSourceName("ds_0");
Properties otherProperties = new Properties();
otherProperties.setProperty("sql-show", "true");
DataSource ds = null;
try {
ds = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig,
new HashMap<String, Object>(), otherProperties);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
System.out.println("xmhshardingDataSource.ds***************"+ds);
return ds;
}
@Bean(name = "sqlSessionFactorySharding")
// @ConfigurationProperties(prefix = "mybatis")//不需要
public SqlSessionFactoryBean sqlSessionFactoryBeanTemplateInsurance() {
SqlSessionFactoryBean sqlSessionFactoryBeanOrder = new SqlSessionFactoryBean();
sqlSessionFactoryBeanOrder.setDataSource(xmhshardingDataSource());
return sqlSessionFactoryBeanOrder;
}
}
***************************************** tk.mybatis实现 *****************************************
@Data
@ToString
@EqualsAndHashCode
@Table(name = "t_order")
public class Order extends BaseModel {
@Column(name = "user_id")
private Integer userId;// 用户ID 投保人ID
private Integer insId;// 保险产品id
@Column(name = "insurance_name")
private String insuranceName;// 险种名称
}
@Mapper
public interface OrderXmhMapper extends BaseMapperXmh<Order> {
static final String FIELDS=" id,user_id,ins_id,insurance_name ";
@Select("select id,user_id,ins_id,insurance_name from t_order where user_id=#{userId} ")
List<Object> getOrderByUserId(int userId);
}
public interface BaseMapper<T> extends Mapper<T> {//,InsertIgnoreMapper<T>
}
@Autowired
OrderXmhMapper orderXmhMapper;
@PostMapping("/insert")
public String insert(int userId){
System.out.println("TestController.test*******************hello word:"+userId);
SpringBootConfiguration s;
Order order = new Order();
order.setUserId(userId);
order.setCreateTime(new Date());
order.setInsuranceName("name"+userId);
orderXmhMapper.insert(order);
return order.toString();
}
***************************************** java 代码实现 *********************************
@Autowired
@Qualifier("xmhshardingDataSource")
DataSource xmhshardingDataSource;
private void insert(Order order) throws SQLException {
// * 2. 通过DataSource获取Connection
Connection connection = xmhshardingDataSource.getConnection();
// * 3. 定义一条SQL语句.
// 注意：******* sql语句中 使用的表是 上面代码中定义的逻辑表 *******
String sql = "insert into t_order(user_id,insurance_name) values(5,'name5')";
// * 4. 通过Connection获取到PreparedStament.
PreparedStatement preparedStatement = connection.prepareStatement(sql);
// * 5. 执行SQL语句.
preparedStatement.execute();
sql = "insert into t_order(user_id,insurance_name) values(6,'name6')";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.execute();
// * 6. 关闭连接.
preparedStatement.close();
connection.close();
}
sharding我的mvc实现？
<!-- 配置ShardingSphereDataSource -->
<shardingsphere:data-source id="shardingDataSource" data-source-names="dataSource_0,dataSource_1" rule-refs="shardingRule">
</shardingsphere:data-source>
<!-- 配置sharding策略 -->
<sharding:rule id="shardingRule">
<sharding:table-rules>
<sharding:table-rule logic-table="t_order" actual-data-nodes="dataSource_$->{0..1}.t_order_$->{0..1}" database-strategy-ref="dbStrategy" table-strategy-ref="tableStrategy"/>
<!--key-generate-strategy-ref="orderKeyGenerator"-->
</sharding:table-rules>
</sharding:rule>
<!-- 配置分库策略 -->
<sharding:sharding-algorithm id="dbShardingAlgorithm" type="INLINE">
<props>
<prop key="algorithm-expression">dataSource_$->{user_id % 2}</prop>
</props>
</sharding:sharding-algorithm>
<sharding:standard-strategy id="dbStrategy" sharding-column="user_id" algorithm-ref="dbShardingAlgorithm" />
<!-- 配置分表策略 -->
<sharding:sharding-algorithm id="tableShardingAlgorithm" type="INLINE">
<props>
<prop key="algorithm-expression">t_order_$->{user_id % 2}</prop>
</props>
</sharding:sharding-algorithm>
<sharding:standard-strategy id="tableStrategy" sharding-column="user_id" algorithm-ref="tableShardingAlgorithm" />
<!-- 让spring管理sqlsessionfactory 使用mybatis和spring整合包中的 -->
<bean id="sqlSessionFactorySharding" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- 数据库连接池 -->
<property name="dataSource" ref="shardingDataSource" />
<!-- 自动扫描mapping.xml文件 -->
<!-- <property name="mapperLocations" value="classpath:mapper/*.xml"></property>-->
<!-- 加载mybatis的全局配置文件 -->
<property name="configLocation" value="classpath:ibatis/global-config.xml" />
</bean> xmlns:sharding="http://shardingsphere.apache.org/schema/shardingsphere/sharding" xmlns:shardingsphere="http://shardingsphere.apache.org/schema/shardingsphere/datasource"
http://shardingsphere.apache.org/schema/shardingsphere/sharding
http://shardingsphere.apache.org/schema/shardingsphere/sharding/sharding.xsd
http://shardingsphere.apache.org/schema/shardingsphere/datasource
http://shardingsphere.apache.org/schema/shardingsphere/datasource/datasource.xsd
