/**
* 数据库文件处理Service
*
* @author linc
* @date 2023-02-02 10:05
*/
@Service
public class DbFileDisposeService {
@Value("${spring.datasource.url}")
private String url;
@Value("${spring.datasource.username}")
private String username;
@Value("${spring.datasource.password}")
private String password;
@Autowired
private SysDataImpListXmDao sysDataImpListXmDao;
@Autowired
private SysDataImpListAjjDao sysDataImpListAjjDao;
@Autowired
private SysDataImpListDao sysDataImpListDao;
/**
* 解析数据库文件
*
* @param file
* @return
*/
public ResponseJson dbFileAnalysis(MultipartFile file, String tableName) {
//文件名后缀
String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1);
/**
* 1、MultipartFile转file
*/
File file1 = multipartFileToFile(file);
if (file1 != null) {
try {
boolean result = false;
int frequency = 0;
switch (suffix) {
// 处理.sql文件数据
case Constant.DATABASE_FILE_TYPE.SQL:
/**
* 1、file转String
*/
String strSql = fileToString(file1);
/**
* 2、执行数据库
*/
if (strSql.indexOf(TRUNCATE) < 0 && strSql.indexOf(DELETE) < 0 && strSql.indexOf(DROP) < 0) {
if (strSql.indexOf(tableName) > 0) {
String replaceSql = strSql.replace(INSERT, REPLACE);
result = executeSql(url, username, password, replaceSql);
frequency = frequency(replaceSql, REPLACE);
// 删除临时文件
if (file1.exists())
file1.delete();
if (!result) {
return new ResponseJson(true, "上传成功,共上传数量:" + frequency + "条");
} else {
return new ResponseJson(false, "上传失败");
}
} else {
return new ResponseJson(true, "上传失败,请选择正确的" + tableName + "数据库文件");
}
} else {
return new ResponseJson(true, "上传失败,包含非法字符:" + TRUNCATE + "、" + DELETE + "、" + DROP);
}
// 处理.dbf文件数据
case Constant.DATABASE_FILE_TYPE.DBF:
/**
* 解析dbf,获取dbf数据
*/
DbfInfo dbfInfo = DbfUtil.readDbf(file1, "UTF-8");
if (null == dbfInfo) {
return new ResponseJson(false, "上传失败,文件为空");
}
List<Map<String, String>> rowList = dbfInfo.getRowList();
if (null == rowList && rowList.size() == 0) {
return new ResponseJson(false, "上传失败,文件为空");
}
Map<String, String> map = new HashMap<>();
switch (tableName) {
// 导入库项目级
case SYS_DATA_IMP_LIST_XM:
List<SysDataImpListXm> sysDataImpListXmList = new ArrayList<>();
rowList.forEach(e -> {
SysDataImpListXm sysDataImpListXm = Convert.convert(SysDataImpListXm.class, e);
sysDataImpListXm = combineSydwCore(sysDataImpListXm, sysDataImpListXm, SysDataImpListXm.class);
sysDataImpListXmList.add(sysDataImpListXm);
});
if (sysDataImpListXmList.size() > 0) {
sysDataImpListXmDao.batchSaveSysDataImpListXm(sysDataImpListXmList);
}
break;
// 导入库案卷级
case SYS_DATA_IMP_LIST_AJJ:
List<SysDataImpListAjj> sysDataImpListAjjList = new ArrayList<>();
rowList.forEach(e -> {
SysDataImpListAjj sysDataImpListAjj = Convert.convert(SysDataImpListAjj.class, e);
sysDataImpListAjj = combineSydwCore(sysDataImpListAjj, sysDataImpListAjj, SysDataImpListAjj.class);
sysDataImpListAjjList.add(sysDataImpListAjj);
});
if (sysDataImpListAjjList.size() > 0) {
sysDataImpListAjjDao.batchSaveSysDataImpListAjj(sysDataImpListAjjList);
}
break;
// 导入库按件级
case SYS_DATA_IMP_LIST:
List<SysDataImpList> sysDataImpListList = new ArrayList<>();
rowList.forEach(e -> {
SysDataImpList sysDataImpList = Convert.convert(SysDataImpList.class, e);
sysDataImpList = combineSydwCore(sysDataImpList, sysDataImpList, SysDataImpList.class);
sysDataImpListList.add(sysDataImpList);
});
if (sysDataImpListList.size() > 0) {
sysDataImpListDao.batchSaveSysDataImpList(sysDataImpListList);
}
break;
}
break;
}
} catch (Exception e) {
e.printStackTrace();
return new ResponseJson(false, "上传失败");
}
}
return new ResponseJson(true, "上传成功");
}
public static void main(String[] args) throws IOException {
File file = new File("C:/Users/Administrator.DESKTOP-HJTM0N5/Desktop/sys_data_imp_list_xm.dbf");
DbfInfo dbfInfo = DbfUtil.readDbf(file, "UTF-8");
String[] fieldName = dbfInfo.getFieldName();
List<Map<String, String>> rowList = dbfInfo.getRowList();
System.out.println("测试");
String strSql = "/*\n" +
" Navicat Premium Data Transfer\n" +
"\n" +
" Source Server : localhost-xiaopi\n" +
" Source Server Type : MySQL\n" +
" Source Server Version : 80012\n" +
" Source Host : localhost:3308\n" +
" Source Schema : archivessive\n" +
"\n" +
" Target Server Type : MySQL\n" +
" Target Server Version : 80012\n" +
" File Encoding : 65001\n" +
"\n" +
" Date: 06/02/2023 08:45:38\n" +
"*/\n" +
"\n" +
"SET NAMES utf8mb4;\n" +
"SET FOREIGN_KEY_CHECKS = 0;\n" +
"\n" +
"-- ----------------------------\n" +
"-- Table structure for xhk_ajj_copy1\n" +
"-- ----------------------------\n" +
"DROP TABLE IF EXISTS `xhk_ajj_copy1`;\n" +
"CREATE TABLE `xhk_ajj_copy1` (\n" +
" `id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '编号',\n" +
" `sbj` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '省部级',\n" +
" `sj` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '市级',\n" +
" `dagmc` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '档案馆名称',\n" +
" `dagdm` varchar(6) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '档案馆代码',\n" +
" `qzmc` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '全宗名称',\n" +
" `wjlx` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '文件类型',\n" +
" `lddw` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '立档单位名称',\n" +
" `qzh` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '全宗号',\n" +
" `mlh` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '目录号',\n" +
" `nd` int(11) NULL DEFAULT NULL COMMENT '年度',\n" +
" `bgqx` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '保管期限',\n" +
" `jg` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '机构或问题',\n" +
" `daml` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '档案门类',\n" +
" `zlfs` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '整理方式',\n" +
" `lbh` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '类别号',\n" +
" `flhs` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '分类号',\n" +
" `xmh` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '项目号',\n" +
" `sajh` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '室编案卷号',\n" +
" `gajh` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '馆编案卷号',\n" +
" `sjh` int(11) NULL DEFAULT NULL COMMENT '室编件号',\n" +
" `jh` int(11) NULL DEFAULT NULL COMMENT '馆编件号',\n" +
" `wjxv` varchar(3) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '文档序号',\n" +
" `dh` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '档号',\n" +
" `yh` varchar(3) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '页号',\n" +
" `ztm` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '主题名',\n" +
" `bltm` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '并列题名',\n" +
" `ftm` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '副题名',\n" +
" `smtmwz` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '说明题名文字',\n" +
" `ztc` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '主题词',\n" +
" `gjc` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '关键词',\n" +
" `rm` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '人名',\n" +
" `zy` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '摘要',\n" +
" `flh` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '分类号',\n" +
" `wh` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '文件编号(文号)',\n" +
" `zrz` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '责任者',\n" +
" `cwrq` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '日期(成文日期)',\n" +
" `wz` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '文种',\n" +
" `jjcd` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '紧急程度',\n" +
" `zs` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '主送',\n" +
" `cs` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '抄送',\n" +
" `mj` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密级',\n" +
" `bgqxs` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '保密期限',\n" +
" `gkfss` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '公开方式',\n" +
" `gwlsh` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '公文流水号',\n" +
" `nr` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '内容',\n" +
" `zhlx` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '文件组合类型',\n" +
" `js` int(11) NULL DEFAULT NULL COMMENT '件数',\n" +
" `ys` int(11) NULL DEFAULT NULL COMMENT '页数',\n" +
" `yz` int(11) NULL DEFAULT NULL COMMENT '语种',\n" +
" `gb` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '稿本',\n" +
" `gsxx` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '格式信息',\n" +
" `csrq` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '文件产生日期',\n" +
" `wjdx` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '计算机文件大小',\n" +
" `cjcx` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '文档创建程序',\n" +
" `xxxtms` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '信息系统描述',\n" +
" `dxxt` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '数字化对象形态',\n" +
" `smfbl` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '扫描分辨率',\n" +
" `smscms` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '扫描色彩模式',\n" +
" `txysfa` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '图像压缩方案',\n" +
" `qmgz` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '签名规则',\n" +
" `qmsj` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '签名时间',\n" +
" `qmr` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '签名人',\n" +
" `qmjg` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '签名结果',\n" +
" `zhs` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '证书',\n" +
" `zsyz` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '证书引证',\n" +
" `qmsfbs` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '签名算法标识',\n" +
" `fmd5` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '父MD5',\n" +
" `md5` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'MD5',\n" +
" `dqwz` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '当前位置(实体库房)',\n" +
" `wjccwz` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '电子文件存储位置',\n" +
" `cfip` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '存放IP',\n" +
" `lxccwz` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '离线存储位置',\n" +
" `jzlx` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '介质类型',\n" +
" `tjztbh` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '脱机载体编号',\n" +
" `tjztcz` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '脱机载体存址',\n" +
" `swh` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '缩微号',\n" +
" `dhlj` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '档号路径',\n" +
" `mllj` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '档案门类路径',\n" +
" `xzqhlj` varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '行政区划路径',\n" +
" `dalj` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '档案路径',\n" +
" `wjm` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '文件名',\n" +
" `wjgs` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '文件格式(后缀)',\n" +
" `qlj` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '路径',\n" +
" `ljid` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '文件路径ID',\n" +
" `zscqsm` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '知识产权说明',\n" +
" `sqdx` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '授权对象',\n" +
" `sqxw` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '授权行为',\n" +
" `kzbs` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '控制标识',\n" +
" `jgrylx` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '机构人员类型',\n" +
" `jgrymc` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '机构人员名称',\n" +
" `zzjgdm` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '组织机构代码',\n" +
" `grzw` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '个人职位',\n" +
" `ywzt` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '业务状态',\n" +
" `ywxw` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '业务行为',\n" +
" `xwsj` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '行为时间',\n" +
" `xwyj` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '行为依据',\n" +
" `xwms` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '行为描述',\n" +
" `stbsf` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '实体标识符',\n" +
" `gxlx` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '关系类型',\n" +
" `gx` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '关系',\n" +
" `gxms` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '关系描述',\n" +
" `kczt` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '库存状态',\n" +
" `dajd` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '档案鉴定',\n" +
" `gkfs` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '公开方式',\n" +
" `skzt` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '受控状态',\n" +
" `daqj` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '档案抢救',\n" +
" `fwqx` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '访问权限',\n" +
" `gxqx` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新权限',\n" +
" `xgqx` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '修改权限',\n" +
" `scqx` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '删除权限',\n" +
" `xzqx` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '下载权限',\n" +
" `dyqx` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '打印权限',\n" +
" `djzt` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '冻结状态',\n" +
" `nfxm` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '男方姓名',\n" +
" `nfsfzh` varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '男方身份证号',\n" +
" `nxm` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '女方姓名',\n" +
" `nsfzh` varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '女方身份证号',\n" +
" `sybs` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '索引标识',\n" +
" `myd` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '满意度',\n" +
" `lymd` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '利用目的',\n" +
" `cyslr` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '查阅受理人',\n" +
" `cyslrjsj` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '查阅受理人计算机',\n" +
" `cyryfl` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '查阅人员分类',\n" +
" `cynrfl` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '查阅内容分类 ',\n" +
" `jsxwd` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '介绍信文档',\n" +
" `cjbs` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '重件标识,0:非重件,1:重件',\n" +
" `zlbs` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '整理标识',\n" +
" `xz` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '是否新增',\n" +
" `sc` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '是否删除',\n" +
" `ajh` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '案卷号',\n" +
" `xh` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '序号',\n" +
" `hh` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '盒号',\n" +
" `bz` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',\n" +
" `xmjdh` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '项目级档号',\n" +
" `ajjdh` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '案卷级档号',\n" +
" `wjjdh` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '文件级档号',\n" +
" `dflh` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '大分类号',\n" +
" `status` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '状态',\n" +
" `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',\n" +
" `create_time` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建时间',\n" +
" `admin_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户编号',\n" +
" `admin_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名称',\n" +
" PRIMARY KEY (`id`) USING BTREE\n" +
") ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '销毁库案卷级' ROW_FORMAT = DYNAMIC;\n" +
"\n" +
"-- ----------------------------\n" +
"-- Records of xhk_ajj_copy1\n" +
"-- ----------------------------\n" +
"INSERT INTO `xhk_ajj_copy1` VALUES ('106-001-0844', NULL, NULL, NULL, '', NULL, NULL, NULL, '106', '001', 2000, 'C', '', 'ws', 'ct', '', NULL, '', NULL, NULL, NULL, NULL, NULL, '106-001-0844', NULL, '大竹县竹北乡政府:大竹县政府、人事、农业、企业局、城关区公所关于注销土地证书、表彰、缴三项统筹、调动、转正、任免、解聘、毕业分配、下达任务的通知', NULL, NULL, NULL, '', NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', NULL, '0844', NULL, '', '', '', NULL, NULL, '', '1', 'null(操作人:admin,操作时间:2022-11-17 16:35:35,操作:销毁审批通过)', '2022-11-17 16:35:27', '1714770640800407552', 'admin');\n" +
"INSERT INTO `xhk_ajj_copy1` VALUES ('106-001-0845', NULL, NULL, NULL, '', NULL, NULL, NULL, '106', '001', 2000, 'C', '', 'ws', 'ct', '', NULL, '', NULL, NULL, NULL, NULL, NULL, '106-001-0845', NULL, '大竹县竹北乡科协:县、区、乡文件、批复、通知、各类协会章程、合同、花名册', NULL, NULL, NULL, '', NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', NULL, '0845', NULL, '', '', '', NULL, NULL, '', '1', 'null(操作人:admin,操作时间:2022-11-17 16:35:35,操作:销毁审批通过)', '2022-11-17 16:35:27', '1714770640800407552', 'admin');\n" +
"INSERT INTO `xhk_ajj_copy1` VALUES ('106-001-0846', NULL, NULL, NULL, '', NULL, NULL, NULL, '106', '001', 2000, 'C', '', 'ws', 'ct', '', NULL, '', NULL, NULL, NULL, NULL, NULL, '106-001-0846', NULL, '大竹县竹北乡人民政府:竹北乡政府2000年刘用君、唐长容等人结婚登记申请及婚姻状况证明', NULL, NULL, NULL, '', NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', NULL, '0846', NULL, '', '', '', NULL, NULL, '', '1', 'null(操作人:admin,操作时间:2022-11-17 16:35:35,操作:销毁审批通过)', '2022-11-17 16:35:27', '1714770640800407552', 'admin');\n" +
"INSERT INTO `xhk_ajj_copy1` VALUES ('106-001-0850', NULL, NULL, NULL, '', NULL, NULL, NULL, '106', '001', 2004, 'C', '', 'ws', 'ct', '', NULL, '', NULL, NULL, NULL, NULL, NULL, '106-001-0850', NULL, '中共大竹县竹北乡委员会:大竹县委、组织部、团委、妇联、竹北乡党委关于表彰、任职、考评结果的通知、决定、党员名册', NULL, NULL, NULL, '', NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', NULL, '0850', NULL, '', '', '', NULL, NULL, '', '1', 'null(操作人:admin,操作时间:2022-11-17 16:35:35,操作:销毁审批通过)', '2022-11-17 16:35:27', '1714770640800407552', 'admin');\n" +
"INSERT INTO `xhk_ajj_copy1` VALUES ('106-001-0851', NULL, NULL, NULL, '', NULL, NULL, NULL, '106', '001', 2004, 'C', '', 'ws', 'ct', '', NULL, '', NULL, NULL, NULL, NULL, NULL, '106-001-0851', NULL, '大竹县竹北乡人民政府:大竹县政府、人事、广播、财政、民政、畜牧局关于表彰、命名、晋升技术等级、工资待遇、调动、录用、拨款、选举的通知', NULL, NULL, NULL, '', NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', NULL, '0851', NULL, '', '', '', NULL, NULL, '', '1', 'null(操作人:admin,操作时间:2022-11-17 16:35:35,操作:销毁审批通过)', '2022-11-17 16:35:27', '1714770640800407552', 'admin');\n" +
"INSERT INTO `xhk_ajj_copy1` VALUES ('106-001-0859', NULL, NULL, NULL, '', NULL, NULL, NULL, '106', '001', 2007, 'Y', '', 'ws', 'ct', '', NULL, '', NULL, NULL, NULL, NULL, NULL, '106-001-0859', NULL, '大竹县竹北乡:竹北乡政府关于敬老院房屋买卖、维修、施工造价预算书、合同、批复', NULL, NULL, NULL, '', NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', NULL, '0859', NULL, '', '', '', NULL, NULL, '', '1', 'null(操作人:admin,操作时间:2022-11-17 16:35:35,操作:销毁审批通过)', '2022-11-17 16:35:27', '1714770640800407552', 'admin');\n" +
"INSERT INTO `xhk_ajj_copy1` VALUES ('106-001-0860', NULL, NULL, NULL, '', NULL, NULL, NULL, '106', '001', 2007, 'C', '', 'ws', 'ct', '', NULL, '', NULL, NULL, NULL, NULL, NULL, '106-001-0860', NULL, '大竹县竹北乡人民政府:竹北乡党委、政府关于新农村建设、整治及村治安、信访、蔬菜产业化经营、征兵、渡汛、地质灾害应急、土地出让金的请示、通知', NULL, NULL, NULL, '', NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '', NULL, '0860', NULL, '', '', '', NULL, NULL, '', '1', 'null(操作人:admin,操作时间:2022-11-17 16:35:35,操作:销毁审批通过)', '2022-11-17 16:35:27', '1714770640800407552', 'admin');\n" +
"\n" +
"SET FOREIGN_KEY_CHECKS = 1;\n";
// long count = frequency(strSql, INSERT);
// System.out.println(count);
}
/**
* 获取INSERT出现次数
*
* @param strSql
* @param findStr
* @return
*/
private int frequency(String strSql, String findStr) {
int i = 0;
Pattern p = Pattern.compile(findStr);
Matcher m = p.matcher(strSql);
while (m.find()) {
i++;
}
return i;
}
}
public class DbfUtil {
/**
* 读取dbf
* @param filePath
* @param charsetName
* @return
* @throws IOException
*/
public DbfInfo readDbf(String filePath,String charsetName) throws IOException {
DbfInfo dbfInfo = new DbfInfo();
FileInputStream inputStream = null;
DBFReader dbfReader = null;
Object[] rowVales = null;
List<Map<String, String>> rowList = new ArrayList<>();
File file = new File(filePath);
if (!file.exists()) {
System.out.println("文件路径:{},该文件不存在!"+ filePath);
return dbfInfo;
}
try {
System.out.println("开始读取DBF文件,文件路径为: {}"+ filePath);
inputStream = new FileInputStream(filePath);
dbfReader = new DBFReader(inputStream, Charset.forName(charsetName), false);
// 字段数量
int fieldCount = dbfReader.getFieldCount();
System.out.println("读取DBF文件,字段数量为:{}个!"+fieldCount);
// 记录数量
int recordCount = dbfReader.getRecordCount();
System.out.println("读取DBF文件,数据量为:{}个!"+ recordCount);
String[] fieldName = new String[fieldCount];
for (int i = 0; i < fieldCount; i++) {
fieldName[i] = dbfReader.getField(i).getName();
}
dbfInfo.setFieldCount(fieldCount);
dbfInfo.setFieldName(fieldName);
dbfInfo.setRecordCount(recordCount);
while ((rowVales = dbfReader.nextRecord()) != null) {
Map<String, String> rowMap = new HashMap<String, String>();
for (int i = 0; i < rowVales.length; i++) {
rowMap.put(dbfReader.getField(i).getName(), String.valueOf(rowVales[i]).trim());
}
rowList.add(rowMap);
}
if (CollectionUtils.isNotEmpty(rowList)) {
dbfInfo.setRowList(rowList);
}
return dbfInfo;
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (null != inputStream) {
inputStream.close();
}
if (null != dbfReader) {
dbfReader.close();
}
}
return dbfInfo;
}
/**
* 读取dbf文件
* @param file
* @param charsetName
* @return
* @throws IOException
*/
public static DbfInfo readDbf(File file, String charsetName) throws IOException {
DbfInfo dbfInfo = new DbfInfo();
FileInputStream inputStream = null;
DBFReader dbfReader = null;
Object[] rowVales = null;
List<Map<String, String>> rowList = new ArrayList<>();
try {
inputStream = new FileInputStream(file);
dbfReader = new DBFReader(inputStream, Charset.forName(charsetName), false);
while ((rowVales = dbfReader.nextRecord()) != null) {
Map<String, String> rowMap = new HashMap<String, String>();
for (int i = 0; i < rowVales.length; i++) {
rowMap.put(dbfReader.getField(i).getName(), String.valueOf(rowVales[i]).trim());
}
rowList.add(rowMap);
}
if (CollectionUtils.isNotEmpty(rowList)) {
dbfInfo.setRowList(rowList);
}
return dbfInfo;
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (null != inputStream) {
inputStream.close();
}
if (null != dbfReader) {
dbfReader.close();
}
}
return dbfInfo;
}
}