1.Controller
package com.web.manage;
import com.entity.database.DataSourceEntity;
import com.service.system.BasicDataService;
import com.utils.Base64Data;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
/**
* @author chenfei
* @version 1.0.0
* @date 2021/11/29 10:29
*/
@Component
@RestController
@RequestMapping(value = "/manage/datasource", produces = "application/json;charset=utf-8")
public class DateSourceController {
public BasicDataService basicDataService;
@Autowired
public DateSourceController(BasicDataService basicDataService) {
this.basicDataService = basicDataService;
}
/**
* 添加数据源信息
* @return
*/
@RequestMapping(value = "/dataOriginEntitySave", method = RequestMethod.PUT)
public String dataOriginEntitySave(@RequestBody DataSourceEntity dataSourceEntity) throws Exception {
return basicDataService.dataOriginEntitySave(dataSourceEntity);
}
/*
查询数据源数据
*/
@RequestMapping(value = "/findAllDateSource", method = RequestMethod.POST)
public String findAllDateSource(@RequestBody DataSourceEntity data) {
return basicDataService.dataOriginEntityFindAll(data);
}
/**
* 查询数据字段值
* @return
*/
@RequestMapping(value = "/getColumns", method = RequestMethod.POST)
public String getColumns(@RequestBody DataSourceEntity data) {
return basicDataService.getColumns(data);
}
/**
* 文件上传
* @param files
* @param type
* @return
*/
@RequestMapping(value = "/upload", method = RequestMethod.POST)
public String upload(@RequestParam("files") MultipartFile[] files, Integer type) {
return basicDataService.upload(files, type);
}
/**
* base64图片上传
* @param data
* @return
*/
@RequestMapping(value = "/uploadBase64", method = RequestMethod.POST)
public String uploadBase64(@RequestBody Base64Data data) {
return basicDataService.uploadBase64(data);
}
/**
* 删除数据源信息
* @return
*/
@RequestMapping(value = "/dataOriginEntityDelete", method = RequestMethod.DELETE)
public String dataOriginEntityDelete(@RequestBody DataSourceEntity dataSourceEntity) throws Exception {
return basicDataService.dataOriginEntityDelete(dataSourceEntity);
}
}
2.service
package com.service.system;
import com.alibaba.fastjson.JSON;
import com.dao.jpa.ServiceDao;
import com.entity.database.DataSourceEntity;
import com.entity.response.DataOriginEntity;
import com.utils.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import javax.persistence.criteria.Predicate;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;
import static com.utils.JdbcUtils.insert;
/**
* @author chenfei
* @version 1.0.0
* @date 2021/12/3 9:37
*/
@Service
public class BasicDataService {
@Value("${spring.datasource.driver-class-name}")
private String driver;
@Value("${spring.datasource.url}")
private String jdbcUrl;
@Value("${spring.datasource.username}")
private String username;
@Value("${spring.datasource.password}")
private String password;
@Value("${savePath}")
private String savePath;
private final ServiceDao service;
@Autowired
public BasicDataService(ServiceDao service) {
this.service = service;
}
/**
* 设置数据源信息
*
* @param data
* @return
*/
@Transactional(rollbackFor = Exception.class)
// @Override
public String dataOriginEntitySave(DataSourceEntity data) throws Exception {
data.setTableName(data.getTableName() + "_" + System.currentTimeMillis());
String date = DateUtil3.getNowDate();
LoggerUtil.infoData("dataOriginEntitySave", data);
if (data.getId() == null) {
var tableName = service.dateSourceJpaDao.findByTableName(data.getTableName());
if (tableName != null && tableName.getId() != null) {
return new Result(400, "数据表已存在!", null).toString();
}
if (null == data.getTableName() || null == data.getTemplate()) {
return new Result(400, "提交数据不完整!", null).toString();
}
} else {
return new Result(400, "数据源已存在!", null).toString();
}
var connection = JdbcUtils.getConnection(driver, jdbcUrl, username, password);
ArrayList<HashMap<String, Object>> list = null;
switch (data.getType()) {
case 0:
//excel
data.setJdbcUrl(savePath + data.getJdbcUrl());
if (data.getFileUrl().endsWith(".csv")) {
list = CsvUtils.toListMap(data);
} else {
list = CsvUtils.readExcelFile(data);
}
break;
case 1:
//database
// var dataType = service.databaseTypeJpaDao.findById(dataOriginEntity.getDriver());
var con = JdbcUtils.getConnection(data.getJdbcDriver(), data.getJdbcUrl(), data.getJdbcUsername(), data.getJdbcPassword());
list = JdbcUtils2.select(con, data);
con.close();
break;
default:
return new Result(400, "数据源类型错误!", null).toString();
}
JdbcUtils2.createTable(connection, data);
for (var e : list) {
e.put("create_date_new", "TO_TIMESTAMP('" + date + "','YYYY-MM-DD HH24:MI:SS')");
insert(connection, e, data.getTableName());
}
connection.close();
DataSourceEntity dataSourceEntity = new DataSourceEntity();
if (data.getType().equals(1)){
dataSourceEntity.setCompanyId(data.getCompanyId());
dataSourceEntity.setTitle(data.getTitle());
dataSourceEntity.setSource("外部数据库");
dataSourceEntity.setFormId(data.getFormId());
dataSourceEntity.setTableName(data.getTableName());
dataSourceEntity.setJdbcSql(data.getJdbcSql());
dataSourceEntity.setJdbcUrl(data.getJdbcUrl());
dataSourceEntity.setJdbcUsername(data.getJdbcUsername());
dataSourceEntity.setJdbcPassword(data.getJdbcPassword());
dataSourceEntity.setJdbcPeriod(data.getJdbcPeriod());
dataSourceEntity.setJdbcDelta(data.getJdbcDelta());
dataSourceEntity.setJdbcDriver(data.getJdbcDriver());
dataSourceEntity.setTemplate1(JSON.toJSONString(data.getTemplate()));}
if (data.getType().equals(0)){
dataSourceEntity.setCompanyId(data.getCompanyId());
dataSourceEntity.setTitle(data.getTitle());
dataSourceEntity.setSource("表单");
dataSourceEntity.setFormId(data.getFormId());
dataSourceEntity.setTableName(data.getTableName());
dataSourceEntity.setJdbcUrl(data.getFileUrl());
dataSourceEntity.setTemplate1(JSON.toJSONString(data.getTemplate()));}
var doe = service.dateSourceJpaDao.save(dataSourceEntity);
if (null != doe.getJdbcPeriod()) {
var t = new Threads(service.dataBulkJpaDao, service.databaseTypeDao, service.columnsDao, service.dataMybatisDao, service.dataOriginDao, data.getId());
t.setDriver(driver);
t.setJdbcUrl(jdbcUrl);
t.setUsername(username);
t.setPassword(password);
service.scheduleManager.addTask(doe.getId(), t, data.getJdbcPeriod());
}
return new Result().toString();
}
/**
* 查询数据源信息
*
* @param data
* @return
*/
@Transactional(rollbackFor = Exception.class)
public String dataOriginEntityFindAll(DataSourceEntity data) {
LoggerUtil.infoData("DataSourceEntityFindAll", data);
data.setPages(Pages.getPages(data.getPages()));
var ens = service.dateSourceJpaDao.findAll((Specification<DataSourceEntity>) (root, query, cb) -> {
// TODO Auto-generated method stub
var predicates = new ArrayList<>(Result.gitPredicate(root, cb, data.toStringLikeMap(), data.toStringEqualMap(), data.toIntGequalMap()));
return cb.and(predicates.toArray(new Predicate[0]));
}, data.getPages().getPageable(data.getPages().getOrderBy())
);
// ens.getContent().forEach(e->e.setColumns(service.columnsDao.findAllByOriginId(e.getId())));
return new Result(201, null, ens).toString();
}
/**
* 删除数据源信息
*
* @return
*/
@Transactional(rollbackFor = Exception.class)
// @Override
public String dataOriginEntityDelete(DataSourceEntity dataSourceEntity) throws Exception {
LoggerUtil.infoData("dataOriginEntityDelete", dataSourceEntity);
//删除数据表
var conn = JdbcUtils.getConnection(driver, jdbcUrl, username, password);
// var data = service.dataOriginJpaDao.findById(dataOriginEntity.getId());
JdbcUtils.dropTable(conn, dataSourceEntity.getTableName());
service.dateSourceJpaDao.deleteById(dataSourceEntity.getId());
// service.dataOriginJpaDao.delete(dataOriginEntity);
// service.scheduleManager.cancelTask(dataOriginEntity.getId());
return new Result().toString();
}
/**
* 文件上传
*
* @param files
* @param type 0为图片 1为文件
* @return
*/
@Transactional(rollbackFor = Exception.class)
// @Override
public String upload(MultipartFile[] files, Integer type) {
LoggerUtil.infoData("files.length ", files.length + " type: " + type);
var list = new ArrayList<String>();
var r = new Result();
if (files.length > 0 && !"".equals(files[0].getOriginalFilename()) && files[0].getOriginalFilename() != " " && files[0].getOriginalFilename() != null) {
if (null == type) {
r.setCode(401);
r.setMessage("ERROR");
return r.toString();
}
var path = toTypeString(type);
if (null == path) {
return r.toString();
}
for (MultipartFile file : files) {
String filePaths = file.getOriginalFilename();
var currentTimeMillis = System.currentTimeMillis();
var x = (int) (Math.random() * 100);
var paths = savePath + path + x + currentTimeMillis + filePaths;
LoggerUtil.infoData("paths: ", paths);
try {
file.transferTo(new File(paths));
} catch (IOException e) {
e.printStackTrace();
}
list.add(path + x + currentTimeMillis + filePaths);
}
r.setData(list);
}
return r.toString();
}
/**
* base64图片上传
*
* @return
*/
@Transactional(rollbackFor = Exception.class)
// @Override
public String uploadBase64(Base64Data baseData) {
String base64Data = baseData.getData();
//base64格式前头
String dataPrix = "";
//实体部分数据
String data = "";
if (base64Data == null || "".equals(base64Data)) {
return new Result(400, "上传失败，上传图片数据为空", null).toString();
} else {
//将字符串分成数组
String[] d = base64Data.split("base64,");
if (d.length == 2) {
dataPrix = d[0];
data = d[1];
} else {
return new Result(401, "上传失败，数据不合法", null).toString();
}
}
//图片后缀，用以识别哪种格式数据
String suffix = "";
//data:image/jpeg;base64,base64编码的jpeg图片数据
if ("data:image/jpeg;".equalsIgnoreCase(dataPrix)) {
suffix = ".jpg";
} else if ("data:image/x-icon;".equalsIgnoreCase(dataPrix)) {
//data:image/x-icon;base64,base64编码的icon图片数据
suffix = ".ico";
} else if ("data:image/gif;".equalsIgnoreCase(dataPrix)) {
//data:image/gif;base64,base64编码的gif图片数据
suffix = ".gif";
} else if ("data:image/png;".equalsIgnoreCase(dataPrix)) {
//data:image/png;base64,base64编码的png图片数据
suffix = ".png";
} else {
return new Result(401, "上传图片格式不合法", null).toString();
}
var path = toTypeString(baseData.getType());
if (null == path) {
return new Result(400, "type error", null).toString();
}
String uuid = UUID.randomUUID().toString().replaceAll("-", "");
String tempFileName = uuid + suffix;
//新生成的图片
String imgFilePath = savePath + path + tempFileName;
try {
//Base64解码
byte[] b = Base64.getDecoder().decode(data);
for (int i = 0; i < b.length; ++i) {
if (b[i] < 0) {
//调整异常数据
b[i] += 256;
}
}
OutputStream out = new FileOutputStream(imgFilePath);
out.write(b);
out.flush();
out.close();
String imgurl = path + tempFileName;
//imageService.save(imgurl);
return new Result(201, null, imgurl).toString();
} catch (IOException e) {
e.printStackTrace();
return new Result(401, "上传图片失败", null).toString();
}
}
/**
* 查询数据库类型
*
* @return
*/
@Transactional(rollbackFor = Exception.class)
// @Override
public String databaseTypeEntityFindAll() {
return new Result(201, null, service.databaseTypeJpaDao.findAll()).toString();
}
/**
* 查询数据源表数据
*
* @param dataOriginEntity
* @return
*/
@Transactional(rollbackFor = Exception.class)
// @Override
public String queryDataSource(DataOriginEntity dataOriginEntity) {
LoggerUtil.infoData("queryDataSource", dataOriginEntity);
dataOriginEntity.setPages(Pages.getPages(dataOriginEntity.getPages()));
var data = service.dataOriginJpaDao.findById(dataOriginEntity.getId());
data.setColumns(service.columnsDao.findAllByOriginId(data.getId()));
data.setPages(dataOriginEntity.getPages());
if (data.getPages().getCurrentPage() > 1) {
data.getPages().setPageIndex((data.getPages().getCurrentPage() - 1) * data.getPages().getPageSize());
} else {
data.getPages().setPageIndex(0);
}
var count = service.dataMybatisDao.queryDataCount(data);
data.getPages().setTotalPage(count);
var r = new Result();
r.setData(service.dataMybatisDao.queryData(data));
r.setPages(data.getPages());
return r.toString();
}
private final static String MYSQL_SQL = "select column_name, column_comment, data_type from information_schema.columns where table_name = '$table'";
private final static String PGSQL_SQL = "SELECT col_description ( A.attrelid, A.attnum ) AS column_comment, format_type ( A.atttypid, A.atttypmod ) AS data_type,A.attname AS column_name FROM pg_class AS C,pg_attribute AS A WHERE C.relname = '$table' AND A.attrelid = C.oid AND A.attnum > 0";
/**
* 查询数据库字段值
*
* @return
*/
@Transactional(rollbackFor = Exception.class)
public String getColumns(DataSourceEntity data) {
LoggerUtil.infoData("/getColumns", data);
var sql = "";
if ("org.postgresql.Driver".equals(data.getJdbcDriver())) {
sql = PGSQL_SQL.replace("$table", data.getTableName());
} else if ("com.mysql.cj.jdbc.Driver".equals(data.getJdbcDriver())) {
sql = MYSQL_SQL.replace("$table", data.getTableName());
}
LoggerUtil.infoData("sql ", sql);
try {
var conn = JdbcUtils.getConnection(data.getJdbcDriver(), data.getJdbcUrl(), data.getJdbcUsername(), data.getJdbcPassword());
return new Result(201, null, JdbcUtils.getColumns(conn, sql)).toString();
} catch (Exception e) {
e.printStackTrace();
return new Result(500, "数据库信息错误!", null).toString();
}
}
/**
* 文件数据预览
*
* @param dataSourceEntity
* @return
*/
@Transactional(rollbackFor = Exception.class)
// @Override
public String dataPreview(DataSourceEntity dataSourceEntity) {
LoggerUtil.infoData("/dataPreview", dataSourceEntity);
dataSourceEntity.setFileUrl(savePath + dataSourceEntity.getFileUrl());
try {
if (dataSourceEntity.getFileUrl().endsWith(".csv")) {
return new Result(201, null, CsvUtils.toListMap(dataSourceEntity)).toString();
} else {
return new Result(201, null, CsvUtils.readExcelFile(dataSourceEntity)).toString();
}
} catch (Exception e) {
e.printStackTrace();
return new Result(500, "文件信息错误!", null).toString();
}
}
// /**
// * 查询自定义统计报表信息
// * @param dataOriginEntity
// * @return
// */
// @Transactional(rollbackFor = Exception.class)
// @Override
// public Result customizeData(DataOriginEntity dataOriginEntity) {
// // LoggerUtil.infoData("/customizeData",dataOriginEntity);
// var daoById = service.dataOriginDao.findById(dataOriginEntity.getId()).get();
// daoById.setBody(dataOriginEntity.getBody());
// //daoById.setOrderBy(dataOriginEntity.getOrderBy());
// daoById.setGroupBy(dataOriginEntity.getGroupBy());
// var list = JSON.parseArray(daoById.getBody(),CustomStatementUtil.class);
// var groups = new ArrayList<CustomStatementUtil>();
// StringBuilder value = new StringBuilder();
// StringBuilder order = new StringBuilder();
// StringBuilder where = new StringBuilder();
// for(CustomStatementUtil customStatementUtil:list){
// if(value.length()!=0){
// value.append(",");
// }
// if(null!=customStatementUtil.getGroup()){
// groups.add(customStatementUtil);
// value.append(customStatementUtil.getKey());
// if(null!=customStatementUtil.getOrder()){
// order.append(customStatementUtil.getKey()).append(" ").append(customStatementUtil.getOrder()).append(",");
// }
// }else {
// if (null != customStatementUtil.getFunc()) {
// customStatementUtil.setCustomValue(customStatementUtil.getFunc() + "(" + customStatementUtil.getKey() + ")");
// }
// if(null != customStatementUtil.getCustomValue()){
// value.append(customStatementUtil.getCustomValue());
// }else {
// value.append(customStatementUtil.getKey());
// }
// }
// value.append(" as ");
// value.append(customStatementUtil.getAs());
// if(null != customStatementUtil.getWhere() && customStatementUtil.getWhere().size() > 0){
// for(var w:customStatementUtil.getWhere()){
// where.append(" and ").append(w.getTable()).append(".").append(w.getO()).append(" ").append(w.getOn()).append(" (");
// for(var v:w.getV()){
// where.append("'").append(v).append("',");
// }
// where.deleteCharAt(where.length()-1);
// where.append(")");
// }
// }
// }
// if(order.length()>0){
// daoById.setOrderBy(order.substring(0,order.length()-1));
// }
// for(var i = 0 ;i<groups.size()-1;i++){
// for(var j=0;j<groups.size()-1-i;j++){
// if(groups.get(j).getGroup()<groups.get(j+1).getGroup()){
// var temp = groups.get(j);
// groups.set(j,groups.get(j+1));
// groups.set(j+1,temp);
// }
// }
// }
// StringBuilder s = new StringBuilder();
// for(CustomStatementUtil c:groups){
// if(s.length()!=0){
// s.append(",");
// }
// s.append(c.getKey());
// }
// if(s.length()>0){
// daoById.setGroupBy(s.toString());
// }
// daoById.setColumnString(value.toString());
// daoById.setWhere(where.toString());
// LoggerUtil.infoData("tableName", daoById.getTableName());
// return new Result(201,null,dataMybatisDao.queryStatementData(daoById));
// }
// @Override
public String queryDataViewCount() {
return new Result(201, null, service.dataMybatisDao.queryDataViewCount()).toString();
}
/**
* 判断文件类型
*
* @param type
* @return
*/
public String toTypeString(Integer type) {
switch (type) {
case 0:
return "/view_file/img/";
case 1:
return "/view_file/file/";
default:
break;
}
return null;
}
}
3.dao
package com.dao.jpa;
import com.dao.mybatis.*;
import com.service.system.ScheduleManager;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import org.springframework.stereotype.Service;
/**
* @author vip
*/
@Service
public class ServiceDao {
public final DateSourceMybatisDao dateSourceMybatisDao;
public final DateSourceJpaDao dateSourceJpaDao;
public final DatabaseTypeDao databaseTypeDao;
public final DataMybatisDao dataMybatisDao;
public final DataOriginDao dataOriginDao;
public final DatabaseTypeJpaDao databaseTypeJpaDao;
public final DataBulkJpaDao dataBulkJpaDao;
public final DataOriginJpaDao dataOriginJpaDao;
public final ScheduleManager scheduleManager;
public final DateSourceLogsJpaDao dateSourceLogsJpaDao;
public ServiceDao(CompanyJpaDao companyJpaDao, ContentJpaDao contentJpaDao, ForumsJpaDao forumsJpaDao, LeaveWordsJpaDao leaveWordsJpaDao, MaterialContentJpaDao materialContentJpaDao, MaterialJpaDao materialJpaDao, MenusJpaDao menusJpaDao, PermissionsJpaDao permissionsJpaDao, RolesJpaDao rolesJpaDao, ScheduleJpaDao scheduleJpaDao, SessionJpaDao sessionJpaDao, SessionLogsJpaDao sessionLogsJpaDao, SessionMemberJpaDao sessionMemberJpaDao, WorkMemberJpaDao workMemberJpaDao, WorkTaskJpaDao workTaskJpaDao, EmployeeJpaDao employeeJpaDao, BCryptPasswordEncoder bCryptPasswordEncoder, PermissionsMybatisDao permissionsMybatisDao, CompanyMybatisDao companyMybatisDao, WorkTaskMybatisDao workTaskMybatisDao, ScheduleMybatisDao scheduleMybatisDao, RolesMybatisDao rolesMybatisDao, EmployeeMybatisDao employeeMybatisDao, MaterialMybatisDao materialMybatisDao, ContentMybatisDao contentMybatisDao, ForumsMybatisDao forumsMybatisDao, DocumentsMybatisDao documentsMybatisDao, MessageMybatisDao messageMybatisDao, CustomInterfaceJpaDao customInterfaceJpaDao, CustomInterfaceMybatisDao customInterfaceMybatisDao, DocumentsJpaDao documentsJpaDao, DocumentDownLogJpaDao documentDownLogJpaDao, DocumentMybatisDao documentMybatisDao, DocumentDownLogMybatisDao documentDownLogMybatisDao, MenuMybatisDao menuMybatisDao, MaterialContentMybatisDao materialContentMybatisDao, MenusMybatisDao menusMybatisDao, LeaveWordsMybatisDao leaveWordsMybatisDao, LeaveWordMybatisDao leaveWordMybatisDao, PersonalDashboardMybatisDao personalDashboardMybatisDao, SessionMybatisDao sessionMybatisDao, SessionLogsMybatisDao sessionLogsMybatisDao, WorkLogsJpaDao workLogsJpaDao, WorkReportJpaDao workReportJpaDao, PushTemplateJpaDao pushTemplateJpaDao, ProjectTemplateJpaDao projectTemplateJpaDao, ProjectJpaDao projectJpaDao, ProjectLogsJpaDao projectLogsJpaDao, ProjectMemberJpaDao projectMemberJpaDao, ProjectStageJpaDao projectStageJpaDao, WorkReportMybatisDao workReportMybatisDao, MessageJpaDao messageJpaDao, MessageAimJapDao messageAimJapDao, ProjectMybatisDao projectMybatisDao, WorkLogsMybatisDao workLogsMybatisDao, ClientJpaDao clientJpaDao, ContractJpaDao contractJpaDao, ContractLogsJpaDao contractLogsJpaDao, DictionariesJpaDao dictionariesJpaDao, RelevanceJpaDao relevanceJpaDao, SupplierJpaDao supplierJpaDao, ContractMybatisDao contractMybatisDao, DictionariesMybatisDao dictionariesMybatisDao, FormTemplateJpaDao formTemplateJpaDao, FormDepartmentJpaDao formDepartmentJpaDao, FormTemplateMybatisDao formTemplateMybatisDao, FormDepartmentMybatisDao formDepartmentMybatisDao, FormLogsJpaDao formLogsJpaDao, DateSourceMybatisDao dateSourceMybatisDao, DateSourceJpaDao dateSourceJpaDao, StatementTemplateJpaDao statementTemplateJpaDao, StatementTemplateMybatisDao statementTemplateMybatisDao, ColumnsDao columnsDao, DatabaseTypeDao databaseTypeDao, DataMybatisDao dataMybatisDao, DataOriginDao dataOriginDao, ScheduleManager scheduleManager, DatabaseTypeJpaDao databaseTypeJpaDao, DataBulkJpaDao dataBulkJpaDao, DataOriginJpaDao dataOriginJpaDao, DateSourceLogsJpaDao dateSourceLogsJpaDao,CustomMybatisDao customMybatisDao) {
this.dateSourceMybatisDao = dateSourceMybatisDao;
this.dateSourceJpaDao = dateSourceJpaDao;
this.databaseTypeDao = databaseTypeDao;
this.dataMybatisDao = dataMybatisDao;
this.dataOriginDao = dataOriginDao;
this.databaseTypeJpaDao = databaseTypeJpaDao;
this.dataBulkJpaDao = dataBulkJpaDao;
this.dataOriginJpaDao = dataOriginJpaDao;
this.dateSourceLogsJpaDao = dateSourceLogsJpaDao;
}
}
4.接口
package com.dao.mybatis;
import com.entity.response.DatabaseTypeEntity;
import com.utils.RedisUtil;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;
import java.util.ArrayList;
import java.util.List;
/**
* @author chenfei
* @version 1.0.0
* @date 2021/12/3 10:59
*/
//@Repository
@Mapper
public interface DatabaseTypeDao
// extends JpaRepository<DatabaseTypeEntity, Integer>, JpaSpecificationExecutor<DatabaseTypeEntity>
{
ArrayList<DatabaseTypeEntity> findById(Integer id);
}
package com.dao.mybatis;
import com.entity.response.Columns;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;
import java.util.ArrayList;
/**
* @author chenfei
* @version 1.0.0
* @date 2021/12/3 11:03
*/
@Mapper
//@Repository
public interface ColumnsDao
// extends JpaRepository<Columns, Integer>, JpaSpecificationExecutor<Columns>
{
ArrayList<Columns> findAllByOriginId(Integer id);
Columns findByOriginIdAndIsKey(Integer id,Integer isKey);
void deleteAllByOriginId(Integer id);
void saveAll(ArrayList<Columns> columns);
}
package com.dao.mybatis;
import com.entity.database.DataSourceEntity;
import com.entity.response.DataOriginEntity;
import com.entity.response.DataViewCount;
import org.apache.ibatis.annotations.Mapper;
import java.util.ArrayList;
import java.util.HashMap;
/**
* @author chenfei
* @version 1.0.0
* @date 2021/12/3 9:52
*/
@Mapper
public interface DataMybatisDao {
int queryDataCount(DataOriginEntity dataOriginEntity);
ArrayList<HashMap<String,Object>> queryData(DataOriginEntity dataOriginEntity);
int deleteOriginData(DataOriginEntity dataOriginEntity);
int deleteData(DataOriginEntity dataOriginEntity);
HashMap<String,Object> queryDataLimitOne(DataSourceEntity dataSourceEntity);
ArrayList<HashMap<String,Object>> queryStatementData(DataOriginEntity dataOriginEntity);
DataViewCount queryDataViewCount();}
package com.dao.jpa;
import com.entity.response.DataBulkEntity;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;
/**
* @author chenfei
* @version 1.0.0
* @date 2021/12/3 17:41
*/
@Mapper
public interface DataBulkJpaDao
// extends JpaRepository<DataBulkEntity, Long>,
// JpaSpecificationExecutor<DataBulkEntity>
{
void save(DataBulkEntity dataBulkEntity);
}
package com.dao.mybatis;
import com.entity.response.DataOriginEntity;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;
/**
* @author chenfei
* @version 1.0.0
* @date 2021/12/3 10:28
*/
//@Repository
@Mapper
public interface DataOriginDao {
DataOriginEntity findByTableName(String name);
}
5.定时任务接口
package com.service.system;
import com.alibaba.druid.util.StringUtils;
import com.utils.LoggerUtil;
import org.springframework.scheduling.annotation.SchedulingConfigurer;
import org.springframework.scheduling.config.CronTask;
import org.springframework.scheduling.config.ScheduledTask;
import org.springframework.scheduling.config.ScheduledTaskRegistrar;
import org.springframework.stereotype.Service;
import java.util.HashMap;
import java.util.Map;
/**
* @author chenfei
* @version 1.0.0
* @date 2021/12/3 9:41
*/
@Service
public class ScheduleManager implements SchedulingConfigurer {
/**
* 受ScheduleManager管理的任务集合
*/
private final Map<Object, ScheduledTask> taskMap = new HashMap<>();
/**
* 定时任务注册器
*/
private ScheduledTaskRegistrar taskRegistrar;
@Override
public void configureTasks(ScheduledTaskRegistrar scheduledTaskRegistrar) {
this.taskRegistrar = scheduledTaskRegistrar;
LoggerUtil.infoData("configureTasks","scheduleManager has successfully acquired the taskRegistrar");
}
/**
* 添加新任务，如果存在key一致的任务，则取消原任务的执行，但添加新任务失败时原任务不会停止
*
* @param key 任务key
* @param runnable 新任务执行代码
* @param cron 新任务cron表达式
*/
public void addTask(Object key, Runnable runnable, String cron) {
if (runnable != null && !StringUtils.isEmpty(cron)) {
ScheduledTask oldTask = taskMap.get(key);
taskMap.put(key, taskRegistrar.scheduleCronTask(new CronTask(runnable, cron)));
if (oldTask != null) {
oldTask.cancel();
}
}
}
/**
* 重置任务的执行时机，修改失败时任务仍使用原有执行时机
*
* @param key 任务key
* @param cron 任务新cron表达式
*/
public void modifyTask(Object key, String cron) {
ScheduledTask oldTask = taskMap.get(key);
if (oldTask != null && !StringUtils.isEmpty(cron)) {
taskMap.put(key, taskRegistrar.scheduleCronTask(new CronTask(oldTask.getTask().getRunnable(), cron)));
oldTask.cancel();
}
}
/**
* 取消任务执行
*
* @param key 任务key
*/
public void cancelTask(Object key) {
ScheduledTask task = taskMap.remove(key);
if (task != null) {
task.cancel();
}
}
/**
* 查看是否存在任务
*
* @param key 任务key
* @return 如果任务存在返回true，否则返回false
*/
public boolean existTask(Object key) {
return taskMap.get(key) != null;
}
}
package com.dao.jpa;
import com.entity.response.DataOriginEntity;
import com.utils.RedisUtil;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;
/**
* @author chenfei
* @version 1.0.0
* @date 2021/12/3 17:54
*/
@Mapper
public interface DataOriginJpaDao
// extends JpaRepository<DataOriginEntity, Integer>, JpaSpecificationExecutor<DataOriginEntity>
{
DataOriginEntity findByTableName(String name);
DataOriginEntity save(DataOriginEntity dataOriginEntity);
DataOriginEntity findById(Integer oid);
DataOriginEntity findAll(Specification<DataOriginEntity> dataOriginEntitySpecification, Pageable pageable);
void delete(DataOriginEntity dataOriginEntity);
}
package com.dao.jpa;
import com.entity.database.DataSourceEntity;
import com.entity.database.DataSourceLogsEntity;
import com.entity.response.Columns;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;
/**
* @author chenfei
* @version 1.0.0
* @date 2021/11/29 10:38
*/
@Repository
public interface DateSourceJpaDao extends JpaRepository<DataSourceEntity, Integer>, JpaSpecificationExecutor<DataSourceEntity> {
// Columns findByOriginIdAndIsKey(Integer id, Integer isKey);
//
DataSourceEntity findByTableName(String name);
}
实体类
package com.entity.database;
import com.alibaba.fastjson.annotation.JSONField;
import com.entity.response.Columns;
import com.utils.Pages;
import com.vladmihalcea.hibernate.type.json.JsonStringType;
import org.hibernate.annotations.DynamicInsert;
import org.hibernate.annotations.DynamicUpdate;
import org.hibernate.annotations.TypeDef;
import javax.persistence.*;
import java.io.Serializable;
import java.util.*;
/**
* @author vip
*/
@Entity
@DynamicInsert
@DynamicUpdate
@TypeDef(name = "json", typeClass = JsonStringType.class)
@Table(name = "data_source", schema = "public", catalog = "eip")
public class DataSourceEntity implements Serializable {
// private static final long serialVersionUID = 1L;
private Integer id;
private String title;
private String source;
private String jdbcUrl;
private String jdbcDriver;
private String jdbcUsername;
private String jdbcPassword;
private String jdbcSql;
private String jdbcPeriod;
private String jdbcDelta;
private List<Map<String, Object>> template;
private Integer status;
private Integer companyId;
private Long formId;
private Pages pages;
private ArrayList<DataSourceLogsEntity> dataSourceLogsEntities;
private String tableName;
private Integer type;
private String fileUrl;
private boolean isTitle;
private Integer driver;
private ArrayList<Columns> columns;
private String isKey;
private String template1;
@Transient
public List<Map<String, Object>> getTemplate() {
return template;
}
public void setTemplate(List<Map<String, Object>> template) {
this.template = template;
}
@Basic
@Column(name = "template")
public String getTemplate1() {
return template1;
}
public void setTemplate1(String template) {
this.template1 = template;
}
@Transient
public Integer getDriver() {
return driver;
}
@Transient
public void setDriver(Integer driver) {
this.driver = driver;
}
@Transient
public String getIsKey() {
return isKey;
}
public void setIsKey(String isKey) {
this.isKey = isKey;
}
@Transient
public ArrayList<Columns> getColumns() {
return columns;
}
public void setColumns(ArrayList<Columns> columns) {
this.columns = columns;
}
@Transient
public Integer getType() {
return type;
}
public void setType(Integer type) {
this.type = type;
}
@Transient
public String getFileUrl() {
return fileUrl;
}
public void setFileUrl(String fileUrl) {
this.type = type;
}
@Transient
public Pages getPages() {
return pages;
}
public void setPages(Pages pages) {
this.pages = pages;
}
@Column(name = "company_id")
public Integer getCompanyId() {
return companyId;
}
public void setCompanyId(Integer companyId) {
this.companyId = companyId;
}
@Column(name = "table_name")
public String getTableName() {
if (tableName != null) {
return tableName.toLowerCase();
}
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
@Column(name = "form_id")
public Long getFormId() {
return formId;
}
public void setFormId(Long formId) {
this.formId = formId;
}
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
@Basic
@Column(name = "title")
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
@Transient
public boolean isTitle() {
return isTitle;
}
public void setIsTitle(boolean title) {
this.isTitle = isTitle;
}
@Basic
@Column(name = "source")
public String getSource() {
return source;
}
public void setSource(String source) {
this.source = source;
}
@Basic
@Column(name = "jdbc_url")
public String getJdbcUrl() {
return jdbcUrl;
}
public void setJdbcUrl(String jdbcUrl) {
this.jdbcUrl = jdbcUrl;
}
@Basic
@Column(name = "jdbc_driver")
public String getJdbcDriver() {
return jdbcDriver;
}
public void setJdbcDriver(String jdbcDriver) {
this.jdbcDriver = jdbcDriver;
}
@Basic
@Column(name = "jdbc_username")
public String getJdbcUsername() {
return jdbcUsername;
}
public void setJdbcUsername(String jdbcUsername) {
this.jdbcUsername = jdbcUsername;
}
@Basic
@Column(name = "jdbc_password")
public String getJdbcPassword() {
return jdbcPassword;
}
public void setJdbcPassword(String jdbcPassword) {
this.jdbcPassword = jdbcPassword;
}
@Basic
@Column(name = "jdbc_sql")
public String getJdbcSql() {
return jdbcSql;
}
public void setJdbcSql(String jdbcSql) {
this.jdbcSql = jdbcSql;
}
@Basic
@Column(name = "jdbc_period")
public String getJdbcPeriod() {
return jdbcPeriod;
}
public void setJdbcPeriod(String jdbcPeriod) {
this.jdbcPeriod = jdbcPeriod;
}
@Basic
@Column(name = "jdbc_delta")
public String getJdbcDelta() {
return jdbcDelta;
}
public void setJdbcDelta(String jdbcDelta) {
this.jdbcDelta = jdbcDelta;
}
@Basic
@Column(name = "status")
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
@Override
public boolean equals(Object o) {
if (this == o) {
return true;
}
if (o == null || getClass() != o.getClass()) {
return false;
}
DataSourceEntity that = (DataSourceEntity) o;
return id.equals(that.id) &&
status.equals(that.status) &&
Objects.equals(title, that.title) &&
Objects.equals(source, that.source) &&
Objects.equals(jdbcUrl, that.jdbcUrl) &&
Objects.equals(jdbcDriver, that.jdbcDriver) &&
Objects.equals(jdbcUsername, that.jdbcUsername) &&
Objects.equals(jdbcPassword, that.jdbcPassword) &&
Objects.equals(jdbcSql, that.jdbcSql) &&
Objects.equals(jdbcPeriod, that.jdbcPeriod) &&
Objects.equals(jdbcDelta, that.jdbcDelta) &&
Objects.equals(template, that.template);
}
@Override
public int hashCode() {
return Objects.hash(id, title, source, jdbcUrl, jdbcDriver, jdbcUsername, jdbcPassword, jdbcSql, jdbcPeriod, jdbcDelta, template, status);
}
@JSONField(serialize = false)
@Transient
public HashMap<String, String> toStringLikeMap() {
HashMap<String, String> stringLikeMap = new HashMap<>(2);
return stringLikeMap;
}
@JSONField(serialize = false)
@Transient
public HashMap<String, Integer> toIntGequalMap() {
HashMap<String, Integer> intGequalMap = new HashMap<>(2);
intGequalMap.put("id", id);
// intGequalMap.put("type", type);
intGequalMap.put("driver", driver);
return intGequalMap;
}
@JSONField(serialize = false)
@Transient
public HashMap<String, String> toStringEqualMap() {
HashMap<String, String> stringEqualMap = new HashMap<>(2);
stringEqualMap.put("name", title);
stringEqualMap.put("tableName", tableName);
return stringEqualMap;
}
}
package com.entity.response;
import com.alibaba.fastjson.annotation.JSONField;
import org.hibernate.annotations.DynamicInsert;
import org.hibernate.annotations.DynamicUpdate;
import javax.persistence.*;
import java.io.Serializable;
import java.sql.Timestamp;
/**
* @author chenfei
* @version 1.0.0
* @date 2021/12/3 11:01
*/
//@Entity
//@DynamicInsert
//@DynamicUpdate
//@Table(name = "data_bulk", schema = "public", catalog = "data_view")
public class DataBulkEntity implements Serializable {
private static final long serialVersionUID = 1L;
private Integer id;
private Integer amount;
private Timestamp createDate;
// @Id
// @Column(name = "id")
// @GeneratedValue(strategy = GenerationType.IDENTITY)
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
// @Column(name = "amount")
public Integer getAmount() {
return amount;
}
public void setAmount(Integer amount) {
this.amount = amount;
}
// @Column(name = "create_date")
// @JSONField(format="yyyy-MM-dd HH:mm:ss")
public Timestamp getCreateDate() {
return createDate;
}
public void setCreateDate(Timestamp createDate) {
this.createDate = createDate;
}
}
package com.entity.response;
import com.utils.Pages;
import javax.persistence.Column;
import java.io.Serializable;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
/**
* @author chenfei
* @version 1.0.0
* @date 2021/12/3 10:10
*/
//@Entity
//@DynamicInsert
//@DynamicUpdate
//@Table(name = "data_origin", schema = "public", catalog = "data_view")
public class DataOriginEntity implements Serializable {
private static final long serialVersionUID = 1L;
private Integer id;
private String name;
private Integer type;
private String tableName;
private String sql;
private String fileUrl;
private Integer pageSize;
private String cron;
private Long holdTime;
private String jdbcUrl;
private Timestamp createDate;
private ArrayList<Columns> columns;
private String password;
private String username;
private Integer driver;
private Pages pages;
private boolean title;
private Integer isIncremental;
private String isKey;
private String body;
private String groupBy;
private String orderBy;
private String columnString;
private String sid;
private String where;
// @Transient
public String getWhere() {
return where;
}
public void setWhere(String where) {
this.where = where;
}
// @Transient
public String getSid() {
return sid;
}
public void setSid(String sid) {
this.sid = sid;
}
// @Transient
public String getGroupBy() {
return groupBy;
}
public void setGroupBy(String groupBy) {
this.groupBy = groupBy;
}
// @Transient
public String getOrderBy() {
return orderBy;
}
public void setOrderBy(String orderBy) {
this.orderBy = orderBy;
}
// @Transient
public String getColumnString() {
return columnString;
}
public void setColumnString(String columnString) {
this.columnString = columnString;
}
// @Transient
public String getBody() {
return body;
}
public void setBody(String body) {
this.body = body;
}
// @Transient
public String getIsKey() {
return isKey;
}
public void setIsKey(String isKey) {
this.isKey = isKey;
}
// @Id
// @Column(name = "id")
// @GeneratedValue(strategy = GenerationType.IDENTITY)
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
// @Column(name = "name")
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
// @Column(name = "type")
public Integer getType() {
return type;
}
public void setType(Integer type) {
this.type = type;
}
// @Column(name = "table_name")
public String getTableName() {
if(tableName!=null){
return tableName.toLowerCase();
}
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
// @Column(name = "sql")
public String getSql() {
return sql;
}
public void setSql(String sql) {
this.sql = sql;
}
// @Column(name = "file_url")
public String getFileUrl() {
return fileUrl;
}
public void setFileUrl(String fileUrl) {
this.fileUrl = fileUrl;
}
// @Column(name = "page_size")
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
// @Column(name = "cron")
public String getCron() {
return cron;
}
public void setCron(String cron) {
this.cron = cron;
}
// @Column(name = "hold_time")
public Long getHoldTime() {
return holdTime;
}
public void setHoldTime(Long holdTime) {
this.holdTime = holdTime;
}
// @Column(name = "jdbc_url")
public String getJdbcUrl() {
return jdbcUrl;
}
public void setJdbcUrl(String jdbcUrl) {
this.jdbcUrl = jdbcUrl;
}
// @Column(name = "create_date")
public Timestamp getCreateDate() {
return createDate;
}
public void setCreateDate(Timestamp createDate) {
this.createDate = createDate;
}
// @Transient
public ArrayList<Columns> getColumns() {
return columns;
}
public void setColumns(ArrayList<Columns> columns) {
this.columns = columns;
}
// @Column
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
// @Column
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
@Column
public Long getDriver() {
return Long.valueOf(driver);
}
public void setDriver(Integer driver) {
this.driver = driver;
}
// @Transient
public Pages getPages() {
return pages;
}
public void setPages(Pages pages) {
this.pages = pages;
}
// @Transient
public boolean isTitle() {
return title;
}
public void setTitle(boolean title) {
this.title = title;
}
// @Column(name = "is_incremental")
public Integer getIsIncremental() {
return isIncremental;
}
public void setIsIncremental(Integer isIncremental) {
this.isIncremental = isIncremental;
}
// @JSONField(serialize = false)
// @Transient
public HashMap<String, Integer> toIntGequalMap() {
HashMap<String, Integer> intGequalMap = new HashMap<>(2);
intGequalMap.put("id", id);
intGequalMap.put("type", type);
intGequalMap.put("driver", driver);
return intGequalMap;
}
// @JSONField(serialize = false)
// @Transient
public HashMap<String, String> toStringEqualMap() {
HashMap<String, String> stringEqualMap = new HashMap<>(2);
stringEqualMap.put("name", name);
stringEqualMap.put("tableName", tableName);
return stringEqualMap;
}
// @JSONField(serialize = false)
// @Transient
public HashMap<String, String> toStringLikeMap() {
HashMap<String, String> stringLikeMap = new HashMap<>(2);
return stringLikeMap;
}
}
package com.entity.response;
import javax.persistence.*;
import java.io.Serializable;
/**
* @author chenfei
* @version 1.0.0
* @date 2021/12/3 10:08
*/
//@Entity
//@DynamicInsert
//@DynamicUpdate
//@Table(name = "columns", schema = "public", catalog = "data_view")
public class Columns {
private static final long serialVersionUID = 1L;
private Integer id;
private String name;
private String type;
private String key;
private Integer originId;
private Integer isKey;
// @Id
// @Column(name = "id")
// @GeneratedValue(strategy = GenerationType.IDENTITY)
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
// @Column(name = "name")
public String getName() {
if(name!=null){
return name.toLowerCase();
}
return name;
}
public void setName(String name) {
this.name = name;
}
// @Column(name="type")
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
// @Column(name = "key")
public String getKey() {
return key;
}
public void setKey(String key) {
this.key = key;
}
// @Column(name = "origin_id")
public Integer getOriginId() {
return originId;
}
public void setOriginId(Integer originId) {
this.originId = originId;
}
// @Column(name = "is_key")
public Integer getIsKey() {
return isKey;
}
public void setIsKey(Integer isKey) {
this.isKey = isKey;
}
}
八个工具类
1.
package com.utils;
/**
* @author chenfei
* @version 1.0.0
* @date 2021/12/3 11:50
*/
public class Base64Data {
private String data;
private Integer type = 0;
public Integer getType() {
return type;
}
public void setType(Integer type) {
this.type = type;
}
public String getData() {
return data;
}
public void setData(String data) {
this.data = data;
}
}
2.
package com.utils;
import com.alibaba.fastjson.JSON;
import com.entity.database.DataSourceEntity;
import com.entity.response.Columns;
import com.entity.response.DataOriginEntity;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
/**
* @author chenfei
* @version 1.0.0
* @date 2021/12/3 10:33
*/
public class CsvUtils {
private static Logger logger = LoggerFactory.getLogger(CsvUtils.class);
public static void main(String[] args) throws Exception {
var d = new DataSourceEntity();
d.setFileUrl("C:\\Users\\vip\\Desktop\\111.xlsx");
d.setIsTitle(false);
var cs1 =new ArrayList<Columns>();
var c1 = new Columns();
c1.setKey("address");
c1.setName("address");
c1.setType("varchar(128)");
cs1.add(c1);
d.setColumns(cs1);
var list = CsvUtils.readExcelFile(d);
//var list = JSON.parseArray(str,HashMap.class);
var conn = JdbcUtils.getConnection("org.postgresql.Driver","jdbc:postgresql://192.168.1.215:5432/gas_o2o_hangtian?characterEncoding=UTF-8","guanxin","D8XA71inJXIasQ0BwDePGMrYmdS0lYzm");
var cs =new ArrayList<Columns>();
var c = new Columns();
c.setKey("orde_id");
c.setName("id");
c.setType("int");
cs.add(c);
d.setColumns(cs);
for (var s : list){
var address = s.get("address").toString();
d.setJdbcSql("select o.orde_id from order_tab o\n" +
"left join deliver_tab dt on o.orde_id = dt.orde_id\n" +
"where dt.deli_id isnull and o.orde_addr = "+address);
var select = JdbcUtils2.select(conn,d);
if(select.size()>0){
System.out.println(address);
System.out.println(JSON.toJSONString(select));
}
}
conn.close();
}
/**
* @return List<List<String>>
* @Description 读取CSV文件的内容（不含表头）
* @Param filePath 文件存储路径，colNum 列数
**/
public static List<List<String>> readCsv(String filePath, int colNum, boolean isTitle) {
BufferedReader bufferedReader = null;
InputStreamReader inputStreamReader = null;
FileInputStream fileInputStream = null;
try {
fileInputStream = new FileInputStream(filePath);
inputStreamReader = new InputStreamReader(fileInputStream);
bufferedReader = new BufferedReader(inputStreamReader);
CSVParser parser = CSVFormat.DEFAULT.parse(bufferedReader);
// 表内容集合，外层List为行的集合，内层List为字段集合
List<List<String>> values = new ArrayList<>();
int rowIndex = 0;
for (CSVRecord record : parser.getRecords()) {
// 跳过表头
if ( isTitle && rowIndex == 0) {
rowIndex++;
continue;
}
// 每行的内容
List<String> value = new ArrayList<>(colNum + 1);
for (int i = 0; i < colNum; i++) {
value.add(record.get(i));
}
values.add(value);
rowIndex++;
}
return values;
} catch (IOException e) {
logger.error("解析CSV内容失败" + e.getMessage(), e);
}finally {
//关闭流
if (bufferedReader != null) {
try {
bufferedReader.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (inputStreamReader != null) {
try {
inputStreamReader.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (fileInputStream != null) {
try {
fileInputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return null;
}
/**
* execl 处理数据为对象
* @param dataSourceEntity
*/
public static ArrayList<HashMap<String,Object>> toListMap(DataSourceEntity dataSourceEntity){
//处理表格数据
var list = readCsv(dataSourceEntity.getFileUrl(),dataSourceEntity.getColumns().size(),dataSourceEntity.isTitle());
var listMap = new ArrayList<HashMap<String,Object>>();
for(var l:list){
var map = new HashMap<String,Object>(l.size()*2);
for(var i=0 ;i<l.size();i++){
String value = l.get(i);
if(null != value && value.isEmpty()){
continue;
}
Object vle = null;
switch (dataSourceEntity.getColumns().get(i).getType()){
case "timestamp":
vle = "TO_TIMESTAMP('"+ value +"','YYYY-MM-DD HH24:MI:SS')";
break;
case "date":
vle = "TO_DATE('"+ value +"','YYYY-MM-DD')";
break;
case "double":
case "float":
case "int":
vle = value;
break;
default:
vle = "'"+value+"'";
break;
}
if(null!=vle){
map.put(dataSourceEntity.getColumns().get(i).getKey(),vle);
}
}
listMap.add(map);
}
//LoggerUtil.infoData("importData",listMap);
return listMap;
}
public static ArrayList<HashMap<String,Object>> readExcelFile(DataSourceEntity dataSourceEntity) throws FileNotFoundException {
Workbook workbook = null;
var list = new ArrayList<HashMap<String, Object>>();
String filePath = dataSourceEntity.getFileUrl();
File file = new File(filePath);
FileInputStream fileInputStream=new FileInputStream(file);
try {
//判断什么类型文件
if (filePath.endsWith(".xls")) {
workbook = new HSSFWorkbook(fileInputStream);
} else if (filePath.endsWith(".xlsx")) {
workbook = new XSSFWorkbook(fileInputStream);
}
} catch (Exception e) {
e.printStackTrace();
}
if (workbook == null) {
return list;
} else {
//获取所有的工作表的的数量
int numOfSheet = workbook.getNumberOfSheets();
System.out.println(numOfSheet+"--->numOfSheet");
//遍历表
for (int i = 0; i < numOfSheet; i++) {
//获取一个sheet也就是一个工作本。
Sheet sheet = workbook.getSheetAt(i);
if(sheet == null) {
continue;
}
//获取一个sheet有多少Row
int lastRowNum = sheet.getLastRowNum();
if(lastRowNum == 0) {
continue;
}
Row row ;
var index = 0;
if(dataSourceEntity.isTitle()){
index = 1;
}
for ( int j = index;j <= lastRowNum; j++) {
row = sheet.getRow(j);
if(row == null) {
continue;
}
//获取一个Row有多少Cell
//short lastCellNum = row.getLastCellNum();
var lastCellNum = dataSourceEntity.getColumns().size();
var map = new HashMap<String, Object>();
for (int k = 0; k < lastCellNum; k++) {
if(row.getCell(k)==null) {
continue;
}
row.getCell(k).setCellType(Cell.CELL_TYPE_STRING);
String value = row.getCell(k).getStringCellValue().trim();
//打印出cell(单元格的内容)
Object vle = null;
switch (dataSourceEntity.getColumns().get(k).getType()){
case "timestamp":
vle = "TO_TIMESTAMP('"+ value +"','YYYY-MM-DD HH24:MI:SS')";
break;
case "date":
vle = "TO_DATE('"+ value +"','YYYY-MM-DD')";
break;
case "double":
case "float":
case "int":
vle = value;
break;
default:
vle = "'"+value+"'";
break;
}
map.put(dataSourceEntity.getColumns().get(k).getKey(),vle);
}
list.add(map);
}
}
}
//LoggerUtil.infoData("readExcelFile",list);
return list;
}
}
3.
package com.utils;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.ParsePosition;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.Calendar;
import java.util.Date;
/**
* @author chenfei
* @version 1.0.0
* @date 2021/12/3 10:19
*/
public class DateUtil3 {
public static String date2String(Date date){
DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String str = dateFormat.format(date);
return str;
}
/**
* 当前日期添加毫秒数
*/
public static String getNowDateAddMilliscond(String datetime) {
long currentTime = System.currentTimeMillis() + (Long.parseLong(datetime) * 1000);
Date Milliscond = new Date(currentTime);
DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String nowTime = "";
nowTime = df.format(Milliscond);
System.out.println(nowTime);
//SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//String dateString = formatter.format(Milliscond);
return nowTime;
}
public static String getNowDateAddDay(int day) {
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
//取今天日期,如果日期类型为String类型,可以使用df.parse()方法,转换为Date类型
Date date = new Date();
Calendar calendar = Calendar.getInstance();//new一个Calendar类,把Date放进去
calendar.setTime(date);
calendar.add(Calendar.DATE, day);//实现日期加一操作,也就是明天
return df.format(calendar.getTime());
}
/**
* 字符串转Date
*/
public static Date StrToDate(String date) throws ParseException {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
return sdf.parse(date);
}
/**
* 判断日期是否为当天
*/
public static boolean isToNowDate(String date) throws ParseException {
if (null == date) {
return false;
}
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
Date dt = simpleDateFormat.parse(date);
if (getCurrentOrdeID().equals(simpleDateFormat.format(dt))) {
return true;
}
return false;
}
/**
* 获取现在时间
*
* @return 返回时间类型 yyyy-MM-dd HH:mm:ss
*/
public static String getNowDate() {
Date currentTime = new Date();
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String dateString = formatter.format(currentTime);
return dateString;
}
public static String getCurrentYear() {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
Date date = new Date();
return sdf.format(date);
}
public static String getCurrentYearMM() {
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
Date date = new Date();
return sdf.format(date);
}
public static String getCurrentOrdeID() {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date = new Date();
return sdf.format(date);
}
/**
* 将长时间格式字符串转换为时间 yyyy-MM-dd HH:mm:ss
*
* @param strDate
* @return
*/
public static Date strToDateLong(String strDate) {
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
ParsePosition pos = new ParsePosition(0);
Date strtodate = formatter.parse(strDate, pos);
return strtodate;
}
public static Calendar dataToCalendar(Date date) {
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
return calendar;
}
public static Date asDate(LocalDateTime localDateTime) {
return Date.from(localDateTime.atZone(ZoneId.systemDefault()).toInstant());
}
public static LocalDateTime asLocalDateTime(String date){
DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS");
return LocalDateTime.parse(date,df);
}
public static LocalDateTime asLocalDateTimes(String date){
DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
return LocalDateTime.parse(date,df);
}
public static Timestamp getTimestamp(){
Date date = new Date();
return new Timestamp(date.getTime());
}
}
4.
package com.utils;
import com.alibaba.fastjson.JSON;
import com.entity.database.DataSourceEntity;
import com.entity.response.Columns;
import com.entity.response.DataOriginEntity;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
/**
* @author chenfei
* @version 1.0.0
* @date 2021/12/3 10:52
*/
public class JdbcUtils2 {
public static void main(String[] args) throws Exception {
var conn = getConnection("org.postgresql.Driver","jdbc:postgresql://47.103.207.195:5432/data_view?characterEncoding=UTF-8","dev","cs@123456");
}
/**
* 创建表
* @param connection
* @throws SQLException
*/
public static void createTable(Connection connection, DataSourceEntity dataSourceEntity) throws SQLException {
StringBuilder sql = new StringBuilder("create table ").append(dataSourceEntity.getTableName()).append("(");
StringBuilder comment = new StringBuilder("comment on table ").append(dataSourceEntity.getTableName()).append(" is '").append(dataSourceEntity.getTitle()).append("';");
for(var columns:dataSourceEntity.getTemplate()){
sql.append(columns.get("key")).append(" ").append(columns.get("type")).append(",");
comment.append("comment on column ").append(dataSourceEntity.getTableName()).append(".").append(columns.get("key")).append(" is '").append(columns.get("name")).append("';");
}
sql.append("create_date_new timestamp default now()");
sql.append(");");
LoggerUtil.infoData("createTable",sql.toString());
Statement smt = connection.createStatement();
smt.executeUpdate(sql.toString());
smt.executeUpdate(comment.toString());
smt.close();
}
public static ArrayList<HashMap<String,Object>> select(Connection connection, DataSourceEntity dataSourceEntity) throws SQLException {
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
preparedStatement = connection.prepareStatement(dataSourceEntity.getJdbcSql());
resultSet = preparedStatement.executeQuery();
ResultSetMetaData rsMeta = preparedStatement.getMetaData();
var list = new ArrayList<HashMap<String,Object>>();
while(resultSet.next()){
var map = result2Map(resultSet, rsMeta,dataSourceEntity);
list.add(map);
}
preparedStatement.close();
resultSet.close();
return list;
}
public static ArrayList<HashMap<String,Object>> getColumns(Connection connection,String sql) throws SQLException {
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
ResultSetMetaData rsMeta = preparedStatement.getMetaData();
var list = new ArrayList<HashMap<String,Object>>();
while(resultSet.next()){
var map = result2Map(resultSet, rsMeta,null);
list.add(map);
}
preparedStatement.close();
resultSet.close();
return list;
}
private static HashMap<String, Object> result2Map(ResultSet rs, ResultSetMetaData meta,DataSourceEntity dataSourceEntity) throws SQLException {
HashMap<String, Object> map = new HashMap<String, Object>();
for (int i = 1; i <= meta.getColumnCount(); i++) {
if(dataSourceEntity==null){
map.put(meta.getColumnName(i), rs.getObject(meta.getColumnName(i)));
continue;
}
var columns = JSON.parseArray(JSON.toJSONString(dataSourceEntity.getTemplate()),Columns.class);
//getColumns() null
for(int k=0;k<columns.size();k++){
if(meta.getColumnName(i).equals(columns.get(k).getKey())){
if(null == rs.getObject(meta.getColumnName(i))){
break;
}
String value = rs.getObject(meta.getColumnName(i)).toString();
Object vle = null;
switch (columns.get(k).getType()){
case "timestamp":
vle = "TO_TIMESTAMP('"+ value +"','YYYY-MM-DD HH24:MI:SS')";
break;
case "date":
vle = "TO_DATE('"+ value +"','YYYY-MM-DD')";
break;
case "double":
case "float":
case "int":
vle = value;
break;
default:
value = value.replace("'","''");
vle = "'"+value+"'";
break;
}
map.put(meta.getColumnName(i), vle);
break;
}
}
}
return map;
}
public static int insert(Connection connection,HashMap<String,Object> map,String tableName) throws SQLException {
StringBuilder sql = new StringBuilder("INSERT INTO ").append(tableName).append("(");
StringBuilder valueSql = new StringBuilder(" VALUES(");
for(Map.Entry<String, Object> entry: map.entrySet())
{
sql.append(entry.getKey()).append(",");
valueSql.append(entry.getValue()).append(",");
}
sql.deleteCharAt(sql.length()-1);
sql.append(")");
valueSql.deleteCharAt(valueSql.length()-1);
valueSql.append(")");
sql.append(valueSql);
Statement smt = connection.createStatement();
int i= smt.executeUpdate(sql.toString());
smt.close();
return i;
}
public static int showTable(Connection connection,String tableName) throws SQLException {
StringBuilder sql = new StringBuilder("select count(1) from pg_class where relname = '").append(tableName).append("'");
Statement state = connection.createStatement();
ResultSet rs = state.executeQuery(sql.toString());
var r = 0;
while (rs.next()){
r = rs.getInt("count");
break;
}
rs.close();
state.close();
return r;
}
public static void dropTable(Connection connection,String tableName) throws SQLException {
Statement smt = connection.createStatement();
smt.executeUpdate("drop table " + tableName);
smt.close();
}
public static Connection getConnection(String DRIVERNAME,String URL,String USER,String PASSWORD) throws Exception {
Connection conn = null;
Class.forName(DRIVERNAME);
conn = DriverManager.getConnection(URL, USER, PASSWORD);
return conn;
}
public static void closeResource(Connection conn, PreparedStatement st) throws SQLException {
st.close();
conn.close();
}
public static void closeResource(Connection conn, ResultSet rs, PreparedStatement st) throws SQLException {
st.close();
rs.close();
conn.close();
}
}
5.
package com.utils;
import com.alibaba.fastjson.JSON;
import org.apache.log4j.Logger;
/**
* @author chenfei
* @version 1.0.0
* @date 2021/12/3 9:42
*/
public class LoggerUtil {
private static Logger logger = Logger.getLogger(LoggerUtil.class);
public static void infoData(String message, Object data) {
logger.info(message +" "+ JSON.toJSONString(data));
}
}
6.
package com.utils;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import java.io.Serializable;
import java.util.ArrayList;
/**
* @author vip
*/
public class Pages implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 页数
*/
private Integer currentPage = 1;
/**
* 每页多少条
*/
private Integer pageSize = 25;
/**
* 总页数
*/
private Integer totalPage;
/**
* 总条数
*/
private Integer intRowCount = 1;
/**
* 从多少条开始查
*/
private Integer pageIndex;
private String startDate;
private String endDate;
private String orderBy = "id";
private String dateName = "create_time";
private String sort = "DESC";
public String getSort() {
return sort;
}
public void setSort(String sort) {
this.sort = sort;
}
public String getStartDate() {
return startDate;
}
public void setStartDate(String startDate) {
this.startDate = startDate;
}
public String getEndDate() {
return endDate;
}
public void setEndDate(String endDate) {
this.endDate = endDate;
}
public String getOrderBy() {
return orderBy;
}
public void setOrderBy(String orderBy) {
this.orderBy = orderBy;
}
public String getDateName() {
return dateName;
}
public void setDateName(String dateName) {
this.dateName = dateName;
}
public Integer getCurrentPage() {
return currentPage;
}
public void setCurrentPage(Integer currentPage) {
this.currentPage = currentPage;
}
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
public Integer getTotalPage() {
totalPage = intRowCount / pageSize;
if (totalPage == 0) {
totalPage = totalPage + 1;
}
if (intRowCount % pageSize > 0 && intRowCount > pageSize) {
totalPage = totalPage + 1;
}
return totalPage;
}
public void setTotalPage(Integer totalPage) {
this.totalPage = totalPage;
}
public Integer getIntRowCount() {
return intRowCount;
}
public void setIntRowCount(Integer intRowCount) {
this.intRowCount = intRowCount;
}
public Integer getPageIndex() {
if (currentPage == 1) {
return 0;
}
return (pageSize * (currentPage - 1));
}
public void setPageIndex(Integer pageIndex) {
this.pageIndex = pageIndex;
}
public static Pages getPages(Pages pages) {
if (pages == null) {
return new Pages();
}
return pages;
}
public Pageable getPageable(String id) {
Sort sort = Sort.by(Sort.Direction.DESC, id);
Pageable pageable = PageRequest.of(currentPage - 1, pageSize, sort);
return pageable;
}
public Pageable getPageable(String[] id) {
Sort sort = Sort.by(Sort.Direction.DESC, id);
Pageable pageable = PageRequest.of(currentPage - 1, pageSize, sort);
return pageable;
}
public static void startPage(Pages pages) {
PageHelper.startPage(pages.getCurrentPage(), pages. getPageSize());
}
public static PageInfo<?> pageInfo(ArrayList<?> list, Pages pages) {
return new PageInfo<>(list, pages.getPageSize());
}
}
package com.utils;
import com.github.pagehelper.PageInfo;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.io.Serializable;
import java.util.*;
/**
* @author vip
*/
public class Result implements Serializable {
private static final long serialVersionUID = 1L;
private int code = 201;
private String message;
private Object data;
private Long totalElements;
private Integer totalPages;
private HashMap<String,Object> sumMap;
private String token;
private Pages pages;
public Pages getPages() {
return pages;
}
public void setPages(Pages pages) {
this.pages = pages;
}
// public static Result ok() {
// return new Result(true, StatusCode.OK, "成功");
// }
public String getToken() {
return token;
}
public void setToken(String token) {
this.token = token;
}
public Long getTotalElements() {
return totalElements;
}
public void setTotalElements(Long totalElements) {
this.totalElements = totalElements;
}
public Integer getTotalPages() {
return totalPages;
}
public void setTotalPages(Integer totalPages) {
this.totalPages = totalPages;
}
public Result() {
}
public Result(PageInfo<?> pageInfo) {
this.data = pageInfo.getList();
this.totalElements = pageInfo.getTotal();
this.totalPages = pageInfo.getPages();
}
public Result(PageInfo<?> pageInfo, HashMap<String,Object> sumMap) {
this.data = pageInfo.getList();
this.totalElements = pageInfo.getTotal();
this.totalPages = pageInfo.getPages();
this.sumMap = sumMap;
}
public HashMap<String, Object> getSumMap() {
return sumMap;
}
public void setSumMap(HashMap<String, Object> sumMap) {
this.sumMap = sumMap;
}
public Result(Object data) {
this.data = data;
}
public Result(int code, String message, Object data) {
this.code = code;
this.message = message;
this.data = data;
}
public int getCode() {
return code;
}
public void setCode(int code) {
this.code = code;
}
public String getMessage() {
return message;
}
public void setMessage(String message) {
this.message = message;
}
public Object getData() {
return data;
}
public void setData(Object data) {
this.data = data;
}
@Override
public String toString() {
return LoggerUtils.toJsonString(this);
}
/**
* 动态查询
*
* @param root
* @param cb
* @return
*/
public static List<Predicate> gitPredicate(Root<?> root, CriteriaBuilder cb, HashMap StringLikeMap, HashMap StringequalMap, HashMap IntegerequalMap) {
List<Predicate> predicates = new ArrayList<Predicate>();
List<Predicate> predicatesOr = new ArrayList<Predicate>();
Iterator StringLikeliter = StringLikeMap.entrySet().iterator();
Iterator Stringequaliter = StringequalMap.entrySet().iterator();
Iterator Integerequaliter = IntegerequalMap.entrySet().iterator();
while (StringLikeliter.hasNext()) {
Map.Entry entry = (Map.Entry) StringLikeliter.next();
Object key = entry.getKey();
Object val = entry.getValue();
if (null != val) {
if(key.toString().indexOf("keyWord")!=-1){
predicatesOr.add(cb.like(root.get(key.toString().split("-")[1]).as(String.class), '%' + val.toString() + '%'));
}else {
predicates.add(cb.like(root.get(key.toString()).as(String.class), '%' + val.toString() + '%'));
}
}
}
if(predicatesOr.size()>0){
Predicate[] pr = new Predicate[predicatesOr.size()];
predicates.add(cb.or(predicatesOr.toArray(pr)));
}
while (Stringequaliter.hasNext()) {
Map.Entry entry = (Map.Entry) Stringequaliter.next();
Object key = entry.getKey();
Object val = entry.getValue();
if (null != val) {
predicates.add(cb.equal(root.get(key.toString()).as(String.class), val));
}
}
while (Integerequaliter.hasNext()) {
Map.Entry entry = (Map.Entry) Integerequaliter.next();
Object key = entry.getKey();
Object val = entry.getValue();
if (null != val) {
predicates.add(cb.equal(root.get(key.toString()).as(Integer.class), val));
}
}
return predicates;
}
}
8.线程
package com.utils;
import com.dao.jpa.DataBulkJpaDao;
import com.dao.jpa.DataOriginJpaDao;
import com.dao.jpa.DatabaseTypeJpaDao;
import com.dao.jpa.DateSourceJpaDao;
import com.dao.mybatis.*;
import com.entity.database.DataSourceEntity;
import com.entity.response.DataBulkEntity;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Optional;
/**
* @author chenfei
* @version 1.0.0
* @date 2021/12/3 11:04
*/
public class Threads extends Thread{
private DataMybatisDao dataMybatisDao;
private DataOriginDao dataOriginDao;
private ColumnsDao columnsDao;
private DatabaseTypeDao databaseTypeDao;
private DatabaseTypeJpaDao databaseTypeJpaDao;
// private DataOriginJpaDao dataOriginJpaDao;
private DateSourceJpaDao dateSourceJpaDao;
private DateSourceMybatisDao dateSourceMybatisDao;
// private DataBulkDao dataBulkDao;
private Integer oid;
private String driver;
private String jdbcUrl;
private String username;
private String password;
private DataBulkJpaDao dataBulkJpaDao;
@Override
public void run() {
var dataSourceEntity = dateSourceJpaDao.findById(oid).get();
dataSourceEntity.setColumns(columnsDao.findAllByOriginId(dataSourceEntity.getId()));
// var databaseTypeEntity = databaseTypeJpaDao.findById(dataSourceEntity.getDriver());
var list = new ArrayList<HashMap<String,Object>>();
String date = DateUtil3.getNowDate();
Connection connection = null;
Connection conn = null;
try {
connection = JdbcUtils.getConnection(driver,jdbcUrl,username,password);
conn = JdbcUtils.getConnection(dataSourceEntity.getJdbcDriver(),dataSourceEntity.getJdbcUrl(),dataSourceEntity.getJdbcUsername(),dataSourceEntity.getJdbcPassword());
//查询是否增量
// if(dataSourceEntity.getIsIncremental().equals(0)){
// //删除原有数据
// LoggerUtil.infoData("deleteData",dataMybatisDao.deleteData(dataOriginEntity));
// list = JdbcUtils2.select(conn,dataOriginEntity);
// for (var e:list){
// e.put("create_date_new","TO_TIMESTAMP('"+ date +"','YYYY-MM-DD HH24:MI:SS')");
// JdbcUtils.insert(connection,e,dataOriginEntity.getTableName());
// }
// }else {
// //查询增量主键
var columns = dateSourceMybatisDao.findByOriginIdAndIsKey(dataSourceEntity.getId(),1);
var sql = dataSourceEntity.getJdbcSql();
dataSourceEntity.setIsKey(columns.getKey());
var map = dataMybatisDao.queryDataLimitOne(dataSourceEntity);
String where = null;
if(null!=null&&map.containsKey(dataSourceEntity.getIsKey())){
where = " "+dataSourceEntity.getIsKey()+">"+map.get(dataSourceEntity.getIsKey())+" ";
}
ArrayList<HashMap<String,Object>> selects=null;
if(where!=null){
StringBuffer sb=new StringBuffer(sql);
var index = sql.indexOf("where");
if(index!=-1){
sb.insert(index,where+" and ");
}
dataSourceEntity.setJdbcSql(sb.toString());
}
selects = JdbcUtils2.select(conn,dataSourceEntity);
for(var s:selects){
s.put("create_date_new","TO_TIMESTAMP('"+ date +"','YYYY-MM-DD HH24:MI:SS')");
JdbcUtils.insert(connection,s,dataSourceEntity.getTableName());
}
var dataBulkEntity = new DataBulkEntity();
dataBulkEntity.setAmount(list.size());
dataBulkJpaDao.save(dataBulkEntity);
// }
connection.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public Threads(){}
public Threads(DataBulkJpaDao dataBulkJpaDao, DatabaseTypeDao databaseTypeDao, ColumnsDao columnsDao, DataMybatisDao dataMybatisDao, DataOriginDao dataOriginDao, Integer oid) {
this.dataMybatisDao = dataMybatisDao;
this.dataOriginDao = dataOriginDao;
this.databaseTypeDao = databaseTypeDao;
this.columnsDao = columnsDao;
this.oid = oid;
}
public String getDriver() {
return driver;
}
public void setDriver(String driver) {
this.driver = driver;
}
public String getJdbcUrl() {
return jdbcUrl;
}
public void setJdbcUrl(String jdbcUrl) {
this.jdbcUrl = jdbcUrl;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Integer getOid() {
return oid;
}
public void setOid(Integer oid) {
this.oid = oid;
}
public DataMybatisDao getDataMybatisDao() {
return dataMybatisDao;
}
public void setDataMybatisDao(DataMybatisDao dataMybatisDao) {
this.dataMybatisDao = dataMybatisDao;
}
public DataOriginDao getDataOriginDao() {
return dataOriginDao;
}
public void setDataOriginDao(DataOriginDao dataOriginDao) {
this.dataOriginDao = dataOriginDao;
}
public DatabaseTypeDao getDatabaseTypeDao() {
return databaseTypeDao;
}
public void setDatabaseTypeDao(DatabaseTypeDao databaseTypeDao) {
this.databaseTypeDao = databaseTypeDao;
}
public ColumnsDao getColumnsDao() {
return columnsDao;
}
public void setColumnsDao(ColumnsDao columnsDao) {
this.columnsDao = columnsDao;
}
}
以及一些凑数的xml
最后是依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.5.6</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com</groupId>
<artifactId>eip</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>eip</name>
<description>eip</description>
<properties>
<java.version>11</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<!--发布时移除tomcat-->
<!-- <exclusions>-->
<!-- <exclusion>-->
<!-- <groupId>org.springframework.boot</groupId>-->
<!-- <artifactId>spring-boot-starter-tomcat</artifactId>-->
<!-- </exclusion>-->
<!-- </exclusions>-->
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.76</version>
</dependency>
<!-- log4j -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.6</version>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.10</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-security</artifactId>
</dependency>
<dependency>
<groupId>org.apache.tomcat.embed</groupId>
<artifactId>tomcat-embed-core</artifactId>
</dependency>
<dependency>
<groupId>io.jsonwebtoken</groupId>
<artifactId>jjwt</artifactId>
<version>0.9.0</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-websocket</artifactId>
</dependency>
<!-- csv -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-csv</artifactId>
<version>1.7</version>
</dependency>
<!--execl-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<!-- 阿里连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.18</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.eweb4j</groupId>
<artifactId>fel</artifactId>
<version>0.8</version>
</dependency>
<dependency>
<groupId>com.vladmihalcea</groupId>
<artifactId>hibernate-types-52</artifactId>
<version>2.4.3</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
实现效果
扫一扫
860
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
