From 53015845597b3e834f30a5c930aea7376db78bd7 Mon Sep 17 00:00:00 2001
From: yangys <y_ys79@sina.com>
Date: 星期四, 31 七月 2025 12:10:50 +0800
Subject: [PATCH] biz字典排序
---
blade-service/blade-qinzhesync/src/main/java/org/springblade/qinzhesync/service/QinzheZzFgb.java | 49 ++++++++++++
blade-service/blade-qinzhesync/src/main/java/org/springblade/qinzhesync/config/JdbcTemplateConfig.java | 2
doc/sql/mdm/mdm.mysql.all.create.sql | 54 +++++++++++++
blade-service/blade-qinzhesync/src/main/java/org/springblade/qinzhesync/service/QinzheSyncService.java | 118 ++++++++++++++++++++++++++++
4 files changed, 218 insertions(+), 5 deletions(-)
diff --git a/blade-service/blade-qinzhesync/src/main/java/org/springblade/qinzhesync/config/JdbcTemplateConfig.java b/blade-service/blade-qinzhesync/src/main/java/org/springblade/qinzhesync/config/JdbcTemplateConfig.java
index 73c0fb3..6b90674 100644
--- a/blade-service/blade-qinzhesync/src/main/java/org/springblade/qinzhesync/config/JdbcTemplateConfig.java
+++ b/blade-service/blade-qinzhesync/src/main/java/org/springblade/qinzhesync/config/JdbcTemplateConfig.java
@@ -21,7 +21,7 @@
}
@Bean
- public JdbcTemplate qingzheJdbcTemplate(@Qualifier("qinzheDataSource") DataSource dataSource) {
+ public JdbcTemplate qinzheJdbcTemplate(@Qualifier("qinzheDataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
diff --git a/blade-service/blade-qinzhesync/src/main/java/org/springblade/qinzhesync/service/QinzheSyncService.java b/blade-service/blade-qinzhesync/src/main/java/org/springblade/qinzhesync/service/QinzheSyncService.java
index 09489c4..8fdd08e 100644
--- a/blade-service/blade-qinzhesync/src/main/java/org/springblade/qinzhesync/service/QinzheSyncService.java
+++ b/blade-service/blade-qinzhesync/src/main/java/org/springblade/qinzhesync/service/QinzheSyncService.java
@@ -1,11 +1,22 @@
package org.springblade.qinzhesync.service;
import lombok.extern.slf4j.Slf4j;
+import org.springblade.core.tool.api.R;
+import org.springblade.system.feign.ISysClient;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
+import org.springframework.dao.DataAccessException;
+import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.core.ResultSetExtractor;
+import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.List;
import java.util.Map;
/**
@@ -18,14 +29,115 @@
@Autowired
private JdbcTemplate mdmJdbcTemplate;
- @Qualifier("qingzheJdbcTemplate")
+ @Qualifier("qinzheJdbcTemplate")
@Autowired
- private JdbcTemplate qingzheJdbcTemplate;
+ private JdbcTemplate qinzheJdbcTemplate;
+ @Autowired
+ private ISysClient sysClient;
+ private static final String DEFAULT_QUERY_SQL = "select 浜у搧鍙� cph,闆朵欢鍙� ljh,闆朵欢鍚嶇О ljmc,涓诲埗 zz,涓诲埗宸ユ zzgd,涓荤宸ヨ壓 zggy,鍓 fg,杞﹂棿璁″垝鍛� cjjhy,璁″垝鍛� jhy from v_寮犵珛铏巁2鍘備富鍒跺垎宸ヨ〃";
+ private static final String QINZHE_QUERY_KEY = "QINZHE_QUERY";
+
+ /**
+ * 鎵归噺鎻掑叆鏁版嵁鏁伴噺
+ */
+ static final int BATCH_SIZE = 4;//5000;
public void syncData(){
+ /*
Map mmdData = mdmJdbcTemplate.queryForMap("select count(*) n from mdm_machine");
log.info("mdm:{}",mmdData);
- Map qinzheData = qingzheJdbcTemplate.queryForMap("select count(*) n from mdm_machine");
+ Map qinzheData = qinzheJdbcTemplate.queryForMap("select count(*) n from mdm_machine");
log.info("qingzhe:{}",qinzheData);
+ */
+ cleanLocalTable();
+ saveQinzheFgbToLocalTable();
+
+ updateProduceDivision();
+ }
+
+ /**
+ * //qingzhe - mdm
+ * 涓荤宸ヨ壓 - 涓撲笟缁勯暱
+ * 缂栧埗锛屾牎瀵癸紝楂樺笀锛堢洰鍓嶉珮甯堟�诲叡6涓汉锛変篃閮芥病鏈�.鐩墠鍙尮閰嶄笓涓氱粍闀垮嵆鍙紝缂栧埗鏍″鍦╩dm涓淮鎶�
+ * 鏇存柊mdm涓诲埗鍒嗗伐琛ㄦ暟鎹�,
+ */
+ void updateProduceDivision(){
+
+
+ mdmJdbcTemplate.update("delete from mdm_produce_division where create_user=-1 and update_user=-1");
+ }
+
+ /**
+ * 娓呴櫎mdm鍒嗗伐琛ㄥ唴鎵�鏈変粠鍕ゅ摬瀵煎叆鐨勬暟鎹�
+ */
+ void cleanQinzheDataInDivision(){
+
+ }
+ /**
+ * 灏嗗嫟鍝茬郴缁熺殑鏁版嵁鍏堜竴娆℃�у鍏dm绯荤粺鏁版嵁搴撲复鏃惰〃
+ */
+ public void saveQinzheFgbToLocalTable(){
+
+ List<QinzheZzFgb> fgbList = this.qinzheJdbcTemplate.query(this.getQuerySql(),new RowMapper<QinzheZzFgb>(){
+ @Override
+ public QinzheZzFgb mapRow(ResultSet rs, int rowNum) throws SQLException {
+ QinzheZzFgb fgb = new QinzheZzFgb();
+ fgb.setCph(rs.getString("cph"));
+ fgb.setLjh(rs.getString("ljh"));;
+ fgb.setLjmc(rs.getString("ljmc"));
+ fgb.setZz(rs.getString("zz"));
+ fgb.setZzgd(rs.getString("zzgd"));
+ fgb.setZggy(rs.getString("zggy"));
+ fgb.setFg(rs.getString("fg"));
+ fgb.setCjjhy(rs.getString("cjjhy"));
+ fgb.setJhy(rs.getString("jhy"));
+
+ return fgb;
+ }
+ });
+
+ insertToLocalTable(fgbList);
+ }
+
+ /**
+ * 娓呴櫎鏈湴涓存椂琛ㄦ暟鎹�
+ */
+ private void cleanLocalTable(){
+ this.mdmJdbcTemplate.update("delete from mdm_qinzhe_fgb");
+
+ }
+
+ /**
+ * 灏嗕粠鍕ゅ摬鏌ヨ鍒扮殑鏁版嵁鎻掑叆鏈湴涓存椂琛�
+ * @param fgbList
+ */
+ private void insertToLocalTable(List<QinzheZzFgb> fgbList) {
+ String insSql = "insert into mdm_qinzhe_fgb (cph,ljh,ljmc,zz,zzgd,zggy,fg,cjjhy,jhy,update_time) values (?,?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP)";
+
+ QinzheZzFgb fgb;
+ List<Object[]> args = new ArrayList<>();
+ int insertedAmount = 0;//宸叉彃鍏ユ暟鎹殑鏁伴噺
+ while(insertedAmount < fgbList.size()) {
+ int added = 0;
+ for(int i=insertedAmount;added<BATCH_SIZE && i<fgbList.size();i++){
+ fgb = fgbList.get(i);
+ args.add(new String[]{fgb.getCph(),fgb.getLjh(),fgb.getLjmc(),fgb.getZz(),fgb.getZzgd(),fgb.getZggy(),fgb.getFg(),fgb.getCjjhy(),fgb.getJhy()});
+
+ added ++;
+ }
+
+ mdmJdbcTemplate.batchUpdate(insSql, args);
+ args.clear();
+ insertedAmount += added;
+ }
+ }
+
+ String getQuerySql(){
+ String querySql = DEFAULT_QUERY_SQL;
+ R<String> sqlResult = sysClient.getParamValue(QINZHE_QUERY_KEY);
+ if(sqlResult.isSuccess()){
+ querySql = sqlResult.getData();
+ }
+ return querySql;
}
}
diff --git a/blade-service/blade-qinzhesync/src/main/java/org/springblade/qinzhesync/service/QinzheZzFgb.java b/blade-service/blade-qinzhesync/src/main/java/org/springblade/qinzhesync/service/QinzheZzFgb.java
new file mode 100644
index 0000000..b888755
--- /dev/null
+++ b/blade-service/blade-qinzhesync/src/main/java/org/springblade/qinzhesync/service/QinzheZzFgb.java
@@ -0,0 +1,49 @@
+package org.springblade.qinzhesync.service;
+
+import lombok.Getter;
+import lombok.Setter;
+
+/**
+ * 鍕ゅ摬涓诲埗鍒嗗伐琛ㄦ暟鎹璞�
+ */
+@Setter
+@Getter
+public class QinzheZzFgb {
+ /**
+ * 浜у搧鍙�
+ */
+ private String cph;
+
+ /**
+ * 闆朵欢鍙�
+ */
+ private String ljh;
+ /**
+ * 闆朵欢鍚嶇О
+ */
+ private String ljmc;
+ /**
+ * 涓诲埗
+ */
+ private String zz;
+ /**
+ * 涓诲埗宸ユ zzgd
+ */
+ private String zzgd;
+ /**
+ * 涓荤宸ヨ壓
+ */
+ private String zggy;
+ /**
+ * 鍓
+ */
+ private String fg;
+ /**
+ * 杞﹂棿璁″垝鍛�
+ */
+ private String cjjhy;
+ /**
+ * 璁″垝鍛�
+ */
+ private String jhy;
+}
diff --git a/doc/sql/mdm/mdm.mysql.all.create.sql b/doc/sql/mdm/mdm.mysql.all.create.sql
index 308f19a..3bf9113 100644
--- a/doc/sql/mdm/mdm.mysql.all.create.sql
+++ b/doc/sql/mdm/mdm.mysql.all.create.sql
@@ -42,6 +42,57 @@
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='鏈哄簥';
+DROP TABLE IF EXISTS `mdm_dbbak_config`;
+CREATE TABLE `mdm_dbbak_config` (
+ `id` bigint NOT NULL,
+ `name` varchar(100) NULL COMMENT '鍥惧彿锛岄浂缁勪欢鍙�',
+ `ip_addr` varchar(20) NOT NULL COMMENT '涓撲笟缁勯暱璐﹀彿',
+ `account` varchar(80) NOT NULL COMMENT '鏁版嵁搴撹处鍙�',
+ `passwd` varchar(80) NOT NULL COMMENT '鏁版嵁搴撳瘑鐮�',
+ `db_name` varchar(80) NOT NULL COMMENT '鏁版嵁搴撳悕绉�',
+ `db_port` int NOT NULL COMMENT '鏁版嵁搴撶鍙�',
+ `status` int DEFAULT NULL COMMENT '涓氬姟鐘舵��',
+ `is_deleted` int DEFAULT 0,
+ PRIMARY KEY (`id`) USING BTREE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='鏁版嵁搴撳浠介厤缃�';
+
+
+DROP TABLE IF EXISTS `mdm_qinzhe_fgb`;
+CREATE TABLE `mdm_qinzhe_fgb` (
+ `cph` varchar(40) NULL COMMENT '浜у搧鍙�',
+ `ljh` varchar(80) NULL COMMENT '闆朵欢鍙�',
+ `ljmc` varchar(100) NULL COMMENT '闆朵欢鍚嶇О',
+ `zz` varchar(40) NULL COMMENT '涓诲埗',
+ `zzgd` varchar(40) NULL COMMENT '涓诲埗宸ユ',
+ `zggy` varchar(40) NULL COMMENT '涓荤宸ヨ壓',
+ `fg` varchar(40) NULL COMMENT '鍓',
+ `cjjhy` varchar(40) NULL COMMENT '杞﹂棿璁″垝鍛�',
+ `jhy` varchar(40) NULL COMMENT '璁″垝鍛�',
+ update_time datetime null COMMENT '鏇存柊鏃堕棿'
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='鍕ゅ摬鍒嗗伐琛ㄦ湰鍦颁复鏃惰〃';
+
+DROP TABLE IF EXISTS `v_寮犵珛铏巁2鍘備富鍒跺垎宸ヨ〃`;
+CREATE TABLE `v_寮犵珛铏巁2鍘備富鍒跺垎宸ヨ〃` (
+ `浜у搧鍙穈 varchar(40) NULL COMMENT '浜у搧鍙�',
+ `闆朵欢鍙穈 varchar(80) NULL COMMENT '闆朵欢鍙�',
+ `闆朵欢鍚嶇О` varchar(100) NULL COMMENT '闆朵欢鍚嶇О',
+ `涓诲埗` varchar(40) NULL COMMENT '涓诲埗',
+ `闆朵欢绛夌骇` varchar(40) NULL COMMENT '闆朵欢绛夌骇',
+ `鏈哄瀷` varchar(40) NULL COMMENT '鏈哄瀷',
+ `鍨嬪埆` varchar(40) NULL COMMENT '鍨嬪埆',
+ `渚濇嵁` varchar(40) NULL COMMENT '渚濇嵁',
+ `涓诲埗宸ユ` varchar(40) NULL COMMENT '涓诲埗宸ユ',
+ `涓荤宸ヨ壓` varchar(40) NULL COMMENT '涓荤宸ヨ壓',
+ `鍓` varchar(40) NULL COMMENT '鍓',
+ `鏄惁鍏抽敭浠禶 varchar(40) NULL COMMENT '鏄惁鍏抽敭浠�',
+ `澶囨敞` varchar(40) NULL COMMENT '澶囨敞',
+ `闆朵欢缂栧彿` varchar(40) NULL COMMENT '闆朵欢缂栧彿',
+ `杞﹂棿璁″垝鍛榒 varchar(40) NULL COMMENT '杞﹂棿璁″垝鍛�',
+ `璁″垝鍛榒 varchar(40) NULL COMMENT '璁″垝鍛�'
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='鍕ゅ摬鍒嗗伐琛ㄨ鍥炬ā鎷�';
+
+
+
DROP TABLE IF EXISTS `mdm_produce_division`;
CREATE TABLE `mdm_produce_division` (
`id` bigint NOT NULL,
@@ -51,7 +102,7 @@
`checker_id` bigint NOT NULL COMMENT '鏍″鍛樿处鍙凤紙宸ヨ壓鍛樿鑹诧級',
`senior_id` bigint NOT NULL COMMENT '瀹℃壒楂樺笀璐﹀彿',
- `tenant_id` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '000000' COMMENT '绉熸埛ID',
+ `tenant_id` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '000000' COMMENT '绉熸埛ID',
`status` int DEFAULT NULL COMMENT '涓氬姟鐘舵��',
`create_dept` bigint DEFAULT NULL COMMENT '鍒涘缓鍗曚綅',
`is_deleted` int DEFAULT 0,
@@ -63,6 +114,7 @@
UNIQUE KEY `uniqueCheckIndex` (`team_leader_id`,`programmer_id`,`checker_id`,`senior_id`,`is_deleted`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='涓诲埗鍒嗗伐琛�';
+
DROP TABLE IF EXISTS `mdm_nc_node`;
CREATE TABLE `mdm_nc_node` (
`id` bigint NOT NULL,
--
Gitblit v1.9.3