标签:postgresq shm utf8mb4 如何 template 表达式 otc tables code
介绍:
Sharding-JDBC,定位为轻量级Java框架,在Java的JDBC层提供的额外服务。 它使用客户端直连数据库,以jar包形式提供服务,无需额外部署和依赖,可理解为增强版的JDBC驱动,完全兼容JDBC和各种ORM框架。
Sharding-JDBC的核心功能为 数据分片 和 读写分离 ,通过 Sharding-JDBC,应用可以透明的使用 jdbc 访问已经分库分表、读写分离的多个数据源,而不用关心数据源的数量以及数据如何分布。
适用于任何基于 Java 的ORM框架,如: Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
基于任何第三方的数据库连接池,如: DBCP, C3P0, BoneCP, Druid, HikariCP等。
支持任意实现 JDBC 规范的数据库。目前支持MySQL,Oracle,SQLServer和PostgreSQL。
使用Sharding-Jdbc前需要人工对数据库进行分库分表,在应用程序中加入Sharding-Jdbc的Jar包,应用程序通过Sharding-Jdbc操作分库分表后的数据库和数据表,由于Sharding-Jdbc是对Jdbc驱动的增强,使用Sharding-Jdbc就像使用Jdbc驱动一样,在应用程序中是无需指定具体要操作的分库和分表的。
快速入门:
1.需求说明
人工创建两张表,t_order_1和t_order_2,这两张表是订单表拆分后的表,通过Sharding-Jdbc向订单表插入数据,按照一定的分片规则,主键为奇数的进入t_order_1,另一部分数据进入t_order_2,通过Sharding-Jdbc 查询数据,根据 SQL语句的内容从t_order_1或t_order_2查询数据。
2.创建数据库,创建表
CREATE DATABASE `order_db` CHARACTER SET ‘utf8mb4‘;
USE order_db;
DROP TABLE IF EXISTS t_order_1
;
CREATE TABLE t_order_1
(
order_id
BIGINT (20) NOT NULL COMMENT ‘订单id‘,
price
DECIMAL (10, 2) NOT NULL COMMENT ‘订单价格‘,
</span><span style="color: #ff00ff;">user_id</span>
BIGINT (20) NOT NULL COMMENT ‘下单用户id‘,
status
VARCHAR (50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘订单状态‘,
PRIMARY KEY (order_id
) USING BTREE
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC ;
DROP TABLE IF EXISTS t_order_2
;
CREATE TABLE t_order_2
(
order_id
BIGINT (20) NOT NULL COMMENT ‘订单id‘,
price
DECIMAL (10, 2) NOT NULL COMMENT ‘订单价格‘,
</span><span style="color: #ff00ff;">user_id</span>
BIGINT (20) NOT NULL COMMENT ‘下单用户id‘,
status
VARCHAR (50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘订单状态‘,
PRIMARY KEY (order_id
) USING BTREE
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC ;
3.创建springboot工程,引入maven依赖
<!-- sharding-jdbc和SpringBoot整合的Jar包 --> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>4.0.0-RC1</version> </dependency>
具体spring boot相关依赖及配置省略.......
4.分片规则配置
#sharding-jdbc分片规则配置 #数据源 spring.shardingsphere.datasource.names = m1
spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url = jdbc:mysql://localhost:3306/order_db?useUnicode=true
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = root
指定t_order表的数据分布情况,配置数据节点 m1.t_order_1,m1.t_order_2
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes = m1.t_order_$->{1…2}
指定t_order表的主键生成策略为SNOWFLAKE(雪花算法)
spring.shardingsphere.sharding.tables.t_order.key-generator.column = order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type = SNOWFLAKE
指定t_order表的分片策略,分片策略包括分片键和分片算法 {order_id % 2 + 1}:计算出的值要么为1,要么为2,根据结果选择使用哪张表
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column = order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression = t_order_$->{order_id % 2 + 1}
打开sql输出日志
spring.shardingsphere.props.sql.show = true
1. 首先定义数据源m1,并对m1进行实际的参数配置。
2.指定t_order表的数据分布情况,他分布在 m1.t_order_1,m1.t_order_2
3.指定t_order表的主键生成策略为SNOWFLAKE,SNOWFLAKE是一种分布式自增算法,保证id全局唯一
4.定义t_order分片策略,order_id为偶数的数据落在t_order_1,为奇数的落在t_order_2,分表策略的表达式为 t_order_$->{order_id % 2 + 1}
5.持久层
@Mapper public interface OrderDao {</span><span style="color: #008000;">/**</span><span style="color: #008000;"> * 插入订单 * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> price * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> userId * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> status * </span><span style="color: #808080;">@return</span> <span style="color: #008000;">*/</span><span style="color: #000000;"> @Insert(</span>"insert into t_order(price, user_id, status) values(#{price}, #{userId}, #{status})"<span style="color: #000000;">) </span><span style="color: #0000ff;">int</span><span style="color: #000000;"> insertOrder(BigDecimal price, Long userId, String status); </span><span style="color: #008000;">/**</span><span style="color: #008000;"> * 根据id列表查询订单 * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> orderIds * </span><span style="color: #808080;">@return</span> <span style="color: #008000;">*/</span><span style="color: #000000;"> @Select(</span>"<script>" + "select" + " * " + " from t_order o " + " where o.order_id in " + " <foreach collection=‘orderIds‘ open=‘(‘ separator=‘,‘ close=‘)‘ item=‘id‘>" + " #{id} " + " </foreach>" + "</script>"<span style="color: #000000;">) List</span><Map> selectOrderbyIds(@Param("orderIds") List<Long><span style="color: #000000;"> orderIds);
}
6.测试
@RunWith(SpringRunner.class) @SpringBootTest(classes = {ShardingJdbcSimpleBootstrap.class}) public class OrderDaoTest {@Autowired OrderDao orderDao; @Test </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> testInsertOrder() { </span><span style="color: #008000;">//</span><span style="color: #008000;"> for (int i = 1; i <= 20; i++) {</span> orderDao.insertOrder(<span style="color: #0000ff;">new</span> BigDecimal(21), 1L, "SUCCESS"<span style="color: #000000;">); </span><span style="color: #008000;">//</span><span style="color: #008000;"> }</span>
}
@Test
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> testSelectOrderbyIds() {
List</span><Long> ids = <span style="color: #0000ff;">new</span> ArrayList<><span style="color: #000000;">();
ids.add(</span>463369285373263872L<span style="color: #000000;">);
ids.add(</span>463369285301960704L<span style="color: #000000;">);
List</span><Map> maps =<span style="color: #000000;"> orderDao.selectOrderbyIds(ids);
System.out.println(maps);
}
}
执行流程:
查看日志,Sharding-JDBC在拿到用户要执行的sql之后干了哪些事儿:
(1)解析sql,获取片键值,在本例中是order_id
(2)Sharding-JDBC通过规则配置 t_order_$->{order_id % 2 + 1},知道了当order_id为偶数时,应该往t_order_1表插数据,为奇数时,往t_order_2插数据。
(3)于是Sharding-JDBC根据order_id的值改写sql语句,改写后的SQL语句是真实所要执行的SQL语句。
(4)执行改写后的真实sql语句
(5)将所有真正执行sql的结果进行汇总合并,返回。
Java配置类的方式配置分片规则:
@Configuration public class ShardingJdbcConfig {</span><span style="color: #008000;">//</span><span style="color: #008000;"> 配置分片规则 </span><span style="color: #008000;">//</span><span style="color: #008000;"> 定义数据源</span> Map<String, DataSource><span style="color: #000000;"> createDataSourceMap() { DruidDataSource dataSource1 </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> DruidDataSource(); dataSource1.setDriverClassName(</span>"com.mysql.jdbc.Driver"<span style="color: #000000;">); dataSource1.setUrl(</span>"jdbc:mysql://localhost:3306/order_db?useUnicode=true"<span style="color: #000000;">); dataSource1.setUsername(</span>"root"<span style="color: #000000;">); dataSource1.setPassword(</span>"root"<span style="color: #000000;">); Map</span><String, DataSource> result = <span style="color: #0000ff;">new</span> HashMap<><span style="color: #000000;">(); result.put(</span>"m1"<span style="color: #000000;">, dataSource1); </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> result; } </span><span style="color: #008000;">//</span><span style="color: #008000;"> 定义主键生成策略</span> <span style="color: #0000ff;">private</span> <span style="color: #0000ff;">static</span><span style="color: #000000;"> KeyGeneratorConfiguration getKeyGeneratorConfiguration() { KeyGeneratorConfiguration result </span>= <span style="color: #0000ff;">new</span> KeyGeneratorConfiguration("SNOWFLAKE", "order_id"<span style="color: #000000;">); </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> result; } </span><span style="color: #008000;">//</span><span style="color: #008000;"> 定义t_order表的分片策略</span>
TableRuleConfiguration getOrderTableRuleConfiguration() {
TableRuleConfiguration result = new TableRuleConfiguration(“t_order”, “m1.t_order_KaTeX parse error: Expected 'EOF', got '&' at position 2: -&̲gt;{1..2}"<span…->{order_id % 2 + 1}”));
result.setKeyGeneratorConfig(getKeyGeneratorConfiguration());
</span><span style="color: #0000ff;">return</span><span style="color: #000000;"> result;
}
</span><span style="color: #008000;">//</span><span style="color: #008000;"> 定义sharding-Jdbc数据源</span>
@Bean
DataSource getShardingDataSource() throws SQLException {
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());
//spring.shardingsphere.props.sql.show = true
Properties properties = new Properties();
properties.put(“sql.show”, “true”);
return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, properties);
}
}
由于采用了配置类所以需要屏蔽原来 application.properties 文件中spring.shardingsphere开头的配置信息。
需要在SpringBoot启动类中屏蔽使用spring.shardingsphere配置项的类:@SpringBootApplication(exclude = SpringBootConfiguration.class)
标签:postgresq shm utf8mb4 如何 template 表达式 otc tables code
原文地址:https://www.cnblogs.com/roadlandscape/p/12818732.html
<div id="commentlistend" name="commentlistend" class="divtextaligncenter margintop20">
<span id="lblpage"></span>
</div>
</div>
<div class="margintop20">
<form method="post" action="/ajaxjs/info_detail_commentadd.aspx">
<div class="divtextalignleft paddingtop20">
<div id="commenthf" class="divbackgroundcolor1">
</div>
<div>
<textarea name="tbcommentcontent" id="tbcommentcontent" class="tb" disabled="disabled" style="width: 680px;
height: 120px;"></textarea>
</div>
</div>
<div class="divtextalignright paddingtop10 ">
<span id="addCommentTishi" class="colorboldHong">登录后才能评论!</span>
<span id="loginno"><input type="button" class="mbtn1" value="登录" onclick="location.href='http://member.mamicode.com/login.aspx?returnUrl='+document.URL.replace(new RegExp('&', 'g'), '(_)')"></span>
</div>
</form>
</div>
</div>
</div>
<script type="text/javascript"> mamicode_adload('content_bottom');</script><div style="margin:20px 0 20px 0;"> <div class="_2s94w8raros"><iframe id="iframeu5994850_0" name="iframeu5994850_0" src="https://pos.baidu.com/vczm?conwid=680&conhei=200&rdid=5994850&dc=3&exps=110011&psi=6676795520671d987fef9befb9313ec4&di=u5994850&dri=0&dis=0&dai=2&ps=8284x284&enu=encoding&ant=0&dcb=___adblockplus_&dtm=HTML_POST&dvi=0.0&dci=-1&dpt=none&tsr=0&tpr=1595485914705&ti=Sharding-JDBC%20%E5%BF%AB%E9%80%9F%E5%85%A5%E9%97%A8%EF%BC%88%E6%B0%B4%E5%B9%B3%E5%88%86%E8%A1%A8%EF%BC%89&ari=2&ver=0716&dbv=2&drs=3&pcs=1583x767&pss=1583x8570&cfv=0&cpl=3&chi=1&cce=true&cec=UTF-8&tlm=1595485914&prot=2&rw=767&ltu=http%3A%2F%2Fwww.mamicode.com%2Finfo-detail-2996675.html&ltr=https%3A%2F%2Fwww.baidu.com%2Flink%3Furl%3DzkpIdc9xQWVOyKKmuz_bWiwGjglZrGJSMiStQBmjM-kTqgEyodIlXUsuiWta25L2y55SNsVIN0igddgbdYiMNK%26wd%3D%26eqid%3Dcf771d4f000ffa10000000035f192e8a&lcr=https%3A%2F%2Fwww.baidu.com%2Flink%3Furl%3DzkpIdc9xQWVOyKKmuz_bWiwGjglZrGJSMiStQBmjM-kTqgEyodIlXUsuiWta25L2y55SNsVIN0igddgbdYiMNK%26wd%3D%26eqid%3Dcf771d4f000ffa10000000035f192e8a&ecd=1&uc=1600x870&pis=-1x-1&sr=1600x900&tcn=1595485915&qn=93e8f8c02680300c&tt=1595485914590.233.233.233" width="680" height="200" align="center,center" vspace="0" hspace="0" marginwidth="0" marginheight="0" scrolling="no" frameborder="0" style="border:0;vertical-align:bottom;margin:0;width:680px;height:200px" allowtransparency="true"></iframe></div> <script type="text/javascript"> (window.slotbydup = window.slotbydup || []).push({ id: "u5994850", container: "_2s94w8raros", async: true }); </script></div>
</div>
</div>