问题遇到了才会去解决。
今天有个数据表数据量4000万。不得已分表。选用 ShardingSphere
目前本人仅用于查询。单库多表。mysql5.7数据库
https://www.jfinal.com/share/1395
maven依赖:
<!-- https://mvnrepository.com/artifact/io.shardingsphere/sharding-jdbc -->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc</artifactId>
<version>3.0.0.M1</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.14</version>
</dependency>
sql如下:
/*
SQLyog Ultimate v12.08 (64 bit)
MySQL - 5.7.24-log : Database - db0
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`db0` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
USE `db0`;
/*Table structure for table `t_order_0` */
DROP TABLE IF EXISTS `t_order_0`;
CREATE TABLE `t_order_0` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) NOT NULL,
`order_id` bigint(20) NOT NULL,
`order_no` varchar(30) NOT NULL,
`isactive` tinyint(4) NOT NULL DEFAULT '1',
`inserttime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updatetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
/*Data for the table `t_order_0` */
insert into `t_order_0`(`id`,`user_id`,`order_id`,`order_no`,`isactive`,`inserttime`,`updatetime`) values (1,1,1,'1',1,'2019-01-21 17:14:46','2019-01-21 17:14:46'),(2,2,1,'1',1,'2019-01-21 17:14:46','2019-01-21 17:14:46'),(3,3,1,'1',1,'2019-01-21 17:14:46','2019-01-21 17:14:46'),(4,4,1,'1',1,'2019-01-21 17:14:46','2019-01-21 17:14:46'),(5,5,1,'1',1,'2019-01-21 17:14:46','2019-01-21 17:14:46'),(6,6,1,'1',1,'2019-01-21 17:14:46','2019-01-21 17:14:46');
/*Table structure for table `t_order_1` */
DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE `t_order_1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) NOT NULL,
`order_id` bigint(20) NOT NULL,
`order_no` varchar(30) NOT NULL,
`isactive` tinyint(4) NOT NULL DEFAULT '1',
`inserttime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updatetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
/*Data for the table `t_order_1` */
insert into `t_order_1`(`id`,`user_id`,`order_id`,`order_no`,`isactive`,`inserttime`,`updatetime`) values (1,1,1,'1',1,'2019-01-21 17:14:51','2019-01-21 17:14:51'),(2,2,1,'1',1,'2019-01-21 17:14:51','2019-01-21 17:14:51'),(3,3,1,'1',1,'2019-01-21 17:14:51','2019-01-21 17:14:51'),(4,4,1,'1',1,'2019-01-21 17:14:51','2019-01-21 17:14:51'),(5,5,1,'1',1,'2019-01-21 17:14:51','2019-01-21 17:14:51'),(6,6,1,'1',1,'2019-01-21 17:14:51','2019-01-21 17:14:51');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
示例:
Controller中代码
public void list() {
Page<Record> page = Db.paginate(
getParaToInt("page_num"),
getParaToInt("page_size"),
" SELECT o.* ",
" FROM t_order o ");
renderJson(page);
}
public void add() {
String sql = " insert into t_order (user_id,order_id,order_no) values (1,2,1) ";
int i = Db.update(sql);
System.out.println(i);
String del = " delete from t_order where id = 1 ";
int delete = Db.delete(del);
System.out.println(delete);
renderJson("SUCCESS");
}
public void add2(){
Record record = new Record()
.set("user_id",3)
.set("order_id",3)
.set("order_no",3);
boolean t_order = Db.save("t_order", record);
System.out.println(t_order);
renderJson();
}
实现的 JFinalConfig 中的代码,配置在插件中。
插件中定义了防止重复启动的变量,所以代码如下
public void configPlugin(Plugins me) {
// 配置真实数据源
Map<String, DataSource> dataSourceMap = new HashMap<>();
DruidPlugin db0 = new DruidPlugin("jdbc:mysql://localhost:3306/db0?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&verifyservercertificate=false&useSSL=false", "root", "123456");
db0.start();
dataSourceMap.put("db0", db0.getDataSource());
// 配置Order表规则
TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();
orderTableRuleConfig.setLogicTable("t_order");
orderTableRuleConfig.setActualDataNodes("db0.t_order_${0..1}");
// 配置分库 + 分表策略
orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order_${order_id % 2}"));
// 配置分片规则
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);
// 省略配置order_item表规则...
// ...
// 获取数据源对象
DataSource dataSource = null;
try {
dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new ConcurrentHashMap(), new Properties());
} catch (SQLException e) {
e.printStackTrace();
}
ActiveRecordPlugin shareArp = new ActiveRecordPlugin("shareArp", dataSource);
shareArp.setDialect(new MysqlDialect());
shareArp.setDevMode(true);
shareArp.setShowSql(true);
shareArp.start();
me.add(db0);
me.add(shareArp);
}