java 新建表与新增数据与读取工作表
table-create-db:
username: root
password: xxx
driver-class-name: com.mysql.cj.jdbc.Driver
url:
CREATE TABLE `work_table` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`folder_id` int(11) NOT NULL DEFAULT '0' COMMENT '文件夹id',
`project_id` int(11) NOT NULL COMMENT '项目ID',
`name` varchar(255) NOT NULL DEFAULT '0' COMMENT '表名称',
`custom_type` char(1) NOT NULL DEFAULT '1' COMMENT '类型（1.录入 2.excel 3.数据库4.问卷）',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
`create_user` int(11) NOT NULL COMMENT '创建者',
`dept_id` int(11) NOT NULL DEFAULT '1' COMMENT '所属部门',
`del_flag` char(1) NOT NULL DEFAULT '0' COMMENT '删除标记（0正常，1删除）',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='工作表上传记录表';
CREATE TABLE `work_table_record` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`table_id` int(11) NOT NULL DEFAULT '0' COMMENT '文件夹id',
`table_name` varchar(255) NOT NULL DEFAULT '0' COMMENT '表名称',
`sheet_name` varchar(255) NOT NULL DEFAULT '1' COMMENT '工作表名称',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
`del_flag` char(1) NOT NULL DEFAULT '0' COMMENT '删除标记（0正常，1删除）',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='工作表上传记录表';
CREATE TABLE `work_table_title` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`table_name` varchar(255) NOT NULL DEFAULT '0' COMMENT '表名称',
`custom_name` varchar(150) NOT NULL DEFAULT '' COMMENT '自定义名称',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
`create_user` int(11) NOT NULL COMMENT '创建者',
`dept_id` int(11) NOT NULL DEFAULT '1' COMMENT '所属部门',
`del_flag` char(1) NOT NULL DEFAULT '0' COMMENT '删除标记（0正常，1删除）',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=86 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='工作表标头';
CREATE TABLE `work_report` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`del_flag` tinyint(1) DEFAULT '0' COMMENT '删除标记（0正常，1删除）',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
`dept_id` int(11) NOT NULL DEFAULT '1' COMMENT '所属部门',
`create_by` bigint(20) unsigned DEFAULT '0' COMMENT '创建人',
`issue_dept` text COLLATE utf8mb4_bin COMMENT '下发部门（多选）',
`report_name` varchar(100) COLLATE utf8mb4_bin DEFAULT '' COMMENT '报告名称',
`report_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '报告上传时间',
`report_files` text COLLATE utf8mb4_bin COMMENT '报告文件',
`report_type` varchar(2) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '报告类型(1,总报告,2,分报告)',
`project_id` bigint(20) DEFAULT NULL COMMENT '项目id',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='报告管理表';
@Value("${table-create-db.url}")
private String url;
@Value("${table-create-db.username}")
private String username;
@Value("${table-create-db.password}")
private String password;
@Resource
private HttpSessionService httpSessionService;
@Resource
private WorkTableTitleService tableTitleService;
@Resource
private WorkTableService tableService;
/**
* 上传工作表
* @param file excel
* @param projectId 项目ID
* @return
*/
@Override
public R uploadExcel(MultipartFile file, Integer projectId) {
Integer userId = Integer.parseInt(httpSessionService.getCurrentUserId());
Integer deptId = Integer.parseInt(httpSessionService.getCurrentDeptId());
//获得Workbook工作薄对象
Workbook workbook = getWorkBook(file);
if (workbook != null) {
int sheetsNum = workbook.getNumberOfSheets();
for(int i = 0; i < sheetsNum; i++){
String sheetName = workbook.getSheetName(i);
WorkTable table = new WorkTable();
String fileName = file.getOriginalFilename();
assert fileName != null;
fileName = fileName.substring(0, fileName.lastIndexOf("."));
if(sheetsNum == 1){
table.setName(fileName);
}
else{
table.setName(fileName + "-" + sheetName);
}
table.setCreateUser(userId);
table.setCustomType("1");
table.setDeptId(deptId);
if(projectId != null){
table.setProjectId(projectId);
}
else{
table.setProjectId(0);
}
tableService.save(table);
Sheet sheet = workbook.getSheetAt(i);
//获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
//获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
List<String[]> list = new ArrayList<>();
//循环除了第一行的所有行 为了过滤到第一行因为我的第一行是数据库的列
for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
//获得当前行
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
//获得当前行的开始列
int firstCellNum = row.getFirstCellNum();
//获得当前行的列数 为空列获取
int lastCellNum = row.getLastCellNum();
String[] cells = new String[row.getLastCellNum()];
//循环当前行
for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
Cell cell = row.getCell(cellNum);
cells[cellNum] = getCellValue(cell);
}
list.add(cells);
}
if(CollUtil.isNotEmpty(list)){
String[] titles = list.get(0);
String tableName = SqlDataUtil.createTableByField(url, username, password, titles.length);
WorkTableRecord wtr = new WorkTableRecord();
wtr.setTableName(tableName);
wtr.setSheetName(sheetName);
wtr.setTableId(table.getId());
this.save(wtr);
SqlDataUtil.insertDataByTableName(url, username, password, tableName, list.subList(1, list.size()));
for(String title : titles){
WorkTableTitle t = new WorkTableTitle();
t.setTableName(tableName);
t.setCustomName(title);
t.setCreateUser(userId);
t.setDeptId(deptId);
tableTitleService.save(t);
}
}
}
return R.ok(Boolean.TRUE);
}
return R.failed("文件读取异常");
}
/**
* 获取工作表
* @param tableName 数据库表名称
* @param current 当前页
* @param size 每页数量
* @return
*/
@Override
public Map<String, Object> getDetailByTableName(String tableName, Integer current, Integer size) {
Map<String, Object> result = new HashMap<>();
WorkTableRecord record = this.getOne(Wrappers.<WorkTableRecord>lambdaQuery().eq(WorkTableRecord::getTableName, tableName).last("limit 1"));
List<WorkTableTitle> titles = tableTitleService.list(Wrappers.<WorkTableTitle>lambdaQuery().eq(WorkTableTitle::getTableName, tableName));
List<JSONObject> titleObjects = new ArrayList<>();
for(int i = 0; i < titles.size(); i++){
JSONObject jsonObject = new JSONObject();
jsonObject.set("c" + i, titles.get(i).getCustomName());
titleObjects.add(jsonObject);
}
result.put("title", titleObjects);
String limitSql = "";
if(current != null && size != null){
int startIndex = (current - 1) * size;
int endIndex = current * size;
limitSql = " limit " + startIndex + ", " + endIndex;
}
Map<String, Object> resultData = SqlDataUtil.getDataByTableName(url, username, password, tableName, limitSql);
if(resultData != null){
JSONArray data = JSONUtil.parseArray(resultData.get("data"));
result.put("records", data);
result.put("total", resultData.get("total"));
if(size != null){
BigDecimal pages = new BigDecimal(resultData.get("total").toString()).divide(new BigDecimal(size), BigDecimal.ROUND_HALF_UP);
result.put("pages", pages);
}
}
result.put("updateTime", record.getUpdateTime());
result.put("tableName", tableName);
result.put("current", current);
result.put("size", size);
return result;
}
/**
* 更新工作表
* @param dto 参数
* @return
*/
@Override
public Boolean updateDetail(TableDTO dto) {
WorkTableRecord record = this.getOne(Wrappers.<WorkTableRecord>lambdaQuery().eq(WorkTableRecord::getTableName, dto.getTableName()).last("limit 1"));
record.setUpdateTime(LocalDateTime.now());
this.updateById(record);
SqlDataUtil.executeSql(url, username, password, "UPDATE " + dto.getTableName() + " SET " + dto.getRow() + " = '" + dto.getValue() + "' WHERE id = " + dto.getLineId().toString());
return Boolean.TRUE;
}
/**
* 重新上传
* @param file excel
* @param tableName 记录表
* @return
*/
@Override
public R replaceUploadExcel(MultipartFile file, String tableName) {
//获得Workbook工作薄对象
Workbook workbook = getWorkBook(file);
if (workbook != null) {
Sheet sheet = workbook.getSheetAt(0);
//获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
//获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
List<String[]> list = new ArrayList<>();
//循环除了第一行的所有行 为了过滤到第一行因为我的第一行是数据库的列
for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
//获得当前行
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
//获得当前行的开始列
int firstCellNum = row.getFirstCellNum();
//获得当前行的列数 为空列获取
int lastCellNum = row.getLastCellNum();
String[] cells = new String[row.getLastCellNum()];
//循环当前行
for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
Cell cell = row.getCell(cellNum);
cells[cellNum] = getCellValue(cell);
}
list.add(cells);
}
String[] titles = list.get(0);
List<WorkTableTitle> oldTitles = tableTitleService.list(Wrappers.<WorkTableTitle>lambdaQuery().eq(WorkTableTitle::getTableName, tableName));
if(titles.length != oldTitles.size()){
return R.failed("表头不一致，无法导入");
}
SqlDataUtil.executeSql(url, username, password, "DELETE FROM " + tableName);
SqlDataUtil.insertDataByTableName(url, username, password, tableName, list.subList(1, list.size()));
return R.ok(Boolean.TRUE);
}
return R.failed("文件读取异常");
}
/**
* 获取单元格内容
*
* @param cell cell
* @return String
*/
private static String getCellValue(Cell cell) {
String cellValue = "";
if (cell == null) {
return cellValue;
}
cell.setCellType(CellType.STRING);
//把数字当成String来读，避免出现1读成1.0的情况
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
cell.setCellType(Cell.CELL_TYPE_STRING);
}
//判断数据的类型
switch (cell.getCellType()) {
//数字
case Cell.CELL_TYPE_NUMERIC:
cellValue = String.valueOf(cell.getNumericCellValue());
break;
//字符串
case Cell.CELL_TYPE_STRING:
cellValue = String.valueOf(cell.getStringCellValue());
break;
//Boolean
case Cell.CELL_TYPE_BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
//公式
case Cell.CELL_TYPE_FORMULA:
cellValue = String.valueOf(cell.getStringCellValue());
break;
//空值
case Cell.CELL_TYPE_BLANK:
cellValue = "";
break;
//故障
case Cell.CELL_TYPE_ERROR:
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
import cn.hutool.core.lang.Console;
import cn.hutool.core.util.IdUtil;
import cn.hutool.json.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
import com.govmade.gds.entity.khpg.ConnectionDbInfo;
import com.govmade.gds.entity.khpg.ConnectionInfo;
import com.govmade.gds.entity.khpg.CustomFields;
import com.govmade.gds.entity.khpg.MysqlDbInfo;
import com.govmade.gds.entity.khpg.dto.ConnectionToCustomFieldDTO;
import com.govmade.gds.entity.khpg.vo.ConnectionCleanInfoVO;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class SqlDataUtil {
private SqlDataUtil() {
// 做成通用需要考虑驱动（需要加对应依赖），数据库url格式及具体sql语句写法等因素
}
private static final Log logger = LogFactory.getLog(SqlDataUtil.class);
/**
* 连接三方数据库获取字段信息
*
* @param govConnectionInfo
* 连接信息
* @return list
*/
public static List<ConnectionCleanInfoVO> getGovConnectionCleanInfoList(ConnectionInfo govConnectionInfo, String deptName, String deptNameEn) {
List<ConnectionCleanInfoVO> list = Lists.newArrayList();
ConnectionCleanInfoVO govConnectionCleanInfo;
ResultSet rs = null;
Connection con = null;
PreparedStatement st = null;
if (StringUtils.isBlank(govConnectionInfo.getDriver())) {
// 后续考虑根据类型来决定驱动
govConnectionInfo.setDriver("com.mysql.cj.jdbc.Driver");
}
try {
// 1、加载驱动
Class.forName(govConnectionInfo.getDriver());
// 2、链接数据库
con = DriverManager.getConnection(govConnectionInfo.getConnectionUrl(), govConnectionInfo.getConnectionName(),
govConnectionInfo.getConnectionPassword());
// 判断数据库是否链接成功
if (con != null && !con.isClosed()) {
logger.info("已成功链接数据库！");
// sql语句
String sql = "SELECT t1.column_name columnName, t1.data_type dataType,t1.column_type columnType,t1.column_comment columnComment,t1.column_key columnKey,t1.extra,t2.TABLE_COMMENT FROM information_schema. COLUMNS t1 LEFT JOIN information_schema.`TABLES` t2 ON t1.TABLE_NAME = t2.TABLE_NAME WHERE t1.table_name = '"
+ govConnectionInfo.getDataTableNameEn()
+ "' AND t1.table_schema = (SELECT DATABASE()) GROUP BY t1.column_name ORDER BY t1.ordinal_position";
// 3、创建Statement对象
st = con.prepareStatement(sql);
// 4、执行sql语句
rs = st.executeQuery();
// 5、处理返回结果
while (rs.next()) {
govConnectionCleanInfo = getGovConnectionCleanInfo(rs, govConnectionInfo, deptName, deptNameEn);
list.add(govConnectionCleanInfo);
}
return list;
} else {
throw new RuntimeException("数据库连接失败");
}
} catch (Exception e) {
logger.error(e.getMessage());
throw new IllegalArgumentException("数据库连接信息有误");
} finally {
closeMethod(rs, st, con);
}
}
/**
* 根据连接信息获取具体数据
*
* @param connectionInfo
* 连接信息
* @return list
*/
public static List<JSONObject> getDataListByConnectionCleanInfo(ConnectionInfo connectionInfo) {
List<JSONObject> list = Lists.newArrayList();
JSONObject jsonObject;
ResultSet rs = null;
Connection con = null;
PreparedStatement st = null;
if (StringUtils.isBlank(connectionInfo.getDriver())) {
// 后续考虑根据类型来决定驱动
connectionInfo.setDriver("com.mysql.cj.jdbc.Driver");
}
try {
// 1、加载驱动
Class.forName(connectionInfo.getDriver());
// 2、链接数据库
con = DriverManager.getConnection(connectionInfo.getConnectionUrl(), connectionInfo.getConnectionName(),
connectionInfo.getConnectionPassword());
// 判断数据库是否链接成功
if (con != null && !con.isClosed()) {
logger.info("已成功链接数据库！");
// sql语句
String sql = "SELECT * FROM `" + connectionInfo.getDataTableNameEn() + "` LIMIT 0 , 10";
// 3、创建Statement对象
st = con.prepareStatement(sql);
// 4、执行sql语句
rs = st.executeQuery();
// 5、处理返回结果
while (rs.next()) {
jsonObject = getJsonObjectByResultSet(rs);
list.add(jsonObject);
}
return list;
} else {
throw new RuntimeException("数据库连接失败");
}
} catch (Exception e) {
logger.error(e.getMessage());
throw new IllegalArgumentException("数据库连接信息有误");
} finally {
closeMethod(rs, st, con);
}
}
/**
* 抽取数据组成内容
*
* @param connectionInfo
* 连接信息
* @param customFields
* 字段信息
* @param map
* 区间信息
* @return List
*/
public static List<JSONObject> getContentByConnectionAndFields(ConnectionInfo connectionInfo, List<CustomFields> customFields,
Map<String, ConnectionToCustomFieldDTO> map, Integer start, Integer end) {
List<JSONObject> list = Lists.newArrayList();
JSONObject jsonObject;
ResultSet rs = null;
Connection con = null;
PreparedStatement st = null;
if (StringUtils.isBlank(connectionInfo.getDriver())) {
// 后续考虑根据类型来决定驱动
connectionInfo.setDriver("com.mysql.cj.jdbc.Driver");
}
try {
// 1、加载驱动
Class.forName(connectionInfo.getDriver());
// 2、链接数据库
con = DriverManager.getConnection(connectionInfo.getConnectionUrl(), connectionInfo.getConnectionName(),
connectionInfo.getConnectionPassword());
// 判断数据库是否链接成功
if (con != null && !con.isClosed()) {
logger.info("已成功链接数据库！");
// sql语句
StringBuilder stringBuilder = new StringBuilder();
for (CustomFields field : customFields) {
stringBuilder.append(field.getDbfieldName()).append(" as `").append(field.getFieldUuid()).append("`,");
}
String sql = "SELECT " + stringBuilder.substring(0, stringBuilder.length() - 1) + " FROM `" + connectionInfo.getDataTableNameEn()
+ "` LIMIT " + start + " , " + end;
Console.log(sql);
// 3、创建Statement对象
st = con.prepareStatement(sql);
// 4、执行sql语句
rs = st.executeQuery();
// 5、处理返回结果
while (rs.next()) {
jsonObject = getJsonObjectForContent(rs, map);
list.add(jsonObject);
}
return list;
} else {
throw new RuntimeException("数据库连接失败");
}
} catch (Exception e) {
logger.error(e.getMessage());
throw new IllegalArgumentException("数据抽取失败");
} finally {
closeMethod(rs, st, con);
}
}
private static JSONObject getJsonObjectForContent(ResultSet rs, Map<String, ConnectionToCustomFieldDTO> map) throws SQLException {
// 获取字段个数
int columns = rs.getMetaData().getColumnCount() + 1;
JSONObject jsonObject = new JSONObject();
String key;
String value;
for (int i = 1; i < columns; i++) {
key = rs.getMetaData().getColumnLabel(i);
value = rs.getString(i);
if (map.containsKey(key)) {
Map<String, String> sectionMap = map.get(key).getMap();
if (sectionMap != null) {
value = sectionMap.getOrDefault(value, value);
}
}
jsonObject.put(key, value);
}
return jsonObject;
}
private static JSONObject getJsonObjectByResultSet(ResultSet rs) throws SQLException {
// 获取字段个数
int columns = rs.getMetaData().getColumnCount() + 1;
JSONObject jsonObject = new JSONObject();
for (int i = 1; i < columns; i++) {
jsonObject.put(rs.getMetaData().getColumnName(i), rs.getString(i));
}
return jsonObject;
}
private static ConnectionCleanInfoVO getGovConnectionCleanInfo(ResultSet rs, ConnectionInfo govConnectionInfo, String deptName, String deptNameEn)
throws SQLException {
ConnectionCleanInfoVO govConnectionCleanInfo = new ConnectionCleanInfoVO();
govConnectionCleanInfo.setDataDeptName(deptName);
govConnectionCleanInfo.setDataDeptNameEn(deptNameEn);
govConnectionCleanInfo.setDataTableNameEn(govConnectionInfo.getDataTableNameEn());
govConnectionCleanInfo.setDataFieldNameEn(rs.getString("columnName"));
govConnectionCleanInfo.setDataFieldName(rs.getString("columnComment"));
govConnectionCleanInfo.setDataType(rs.getString("dataType"));
govConnectionCleanInfo.setColumnType(rs.getString("columnType"));
govConnectionCleanInfo.setDataTableName(rs.getString("TABLE_COMMENT"));
if (isExistColumn(rs, "columnKey") && "PRI".equals(rs.getString("columnKey"))) {
govConnectionInfo.setPriKey(rs.getString("columnName"));
}
return govConnectionCleanInfo;
}
private static void closeMethod(ResultSet rs, PreparedStatement st, Connection con) {
try {
if (rs != null) {
// 关闭资源
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (st != null) {
st.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (con != null) {
// 关闭数据库
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private static boolean isExistColumn(ResultSet rs, String columnName) {
try {
if (rs.findColumn(columnName) > 0) {
return true;
}
} catch (SQLException e) {
return false;
}
return false;
}
public static List<ConnectionInfo> getConnectionCleanInfoList(ConnectionDbInfo connectionDbInfo, Integer userId, Integer deptId) {
List<ConnectionInfo> list = Lists.newArrayList();
ConnectionInfo connectionInfo;
ResultSet rs = null;
Connection con = null;
PreparedStatement st = null;
String driver;
switch (connectionDbInfo.getRequestType()) {
case 0:
driver = "com.mysql.cj.jdbc.Driver";
break;
default:
driver = "";
break;
}
try {
con = getDriverManager(driver, connectionDbInfo.getConnectionUrl(), connectionDbInfo.getConnectionName(),
connectionDbInfo.getConnectionPassword());
// 判断数据库是否链接成功
if (con != null && !con.isClosed()) {
logger.info("已成功链接数据库！");
// sql语句
String sql = "SELECT table_name,table_comment FROM information_schema.tables WHERE table_schema = '"
+ connectionDbInfo.getDataName() + "' AND table_type = 'base table' ";
// 3、创建Statement对象
st = con.prepareStatement(sql);
// 4、执行sql语句
rs = st.executeQuery();
// 5、处理返回结果
while (rs.next()) {
connectionInfo = getConnectionInfo(rs, connectionDbInfo, userId,deptId);
list.add(connectionInfo);
}
return list;
} else {
throw new RuntimeException("数据库连接失败");
}
} catch (Exception e) {
logger.error(e.getMessage());
throw new IllegalArgumentException("数据库连接信息有误");
} finally {
closeMethod(rs, st, con);
}
}
private static ConnectionInfo getConnectionInfo(ResultSet rs, ConnectionDbInfo connectionDbInfo, Integer userId,Integer deptId) throws SQLException {
ConnectionInfo connectionInfo = new ConnectionInfo();
connectionInfo.setConnectionType(0);
connectionInfo.setConnectionUrl(connectionDbInfo.getConnectionUrl());
connectionInfo.setConnectionName(connectionDbInfo.getConnectionName());
connectionInfo.setConnectionPassword(connectionDbInfo.getConnectionPassword());
connectionInfo.setRequestType(0);
connectionInfo.setDataTableNameEn(rs.getString("table_name"));
if (StringUtils.isBlank(connectionDbInfo.getInfoName())) {
connectionDbInfo.setInfoName(("默认库" + LocalDateTime.now()));
}
// 暂时不查询主键信息，主键大于等于表数量，如果必要，用第二个sql查询表和主键，然后map方式对应存储,这里直接返回list，需要就另外list作为参数的形式进行第二个sql
connectionInfo.setCreateUser(userId);
connectionInfo.setCreateTime(LocalDateTime.now());
connectionInfo.setDeptId(deptId);
return connectionInfo;
}
private static Connection getDriverManager(String driver, String connectionUrl, String connectionName, String connectionPassword)
throws ClassNotFoundException, SQLException {
// 1、加载驱动
Class.forName(driver);
// 2、链接数据库
try {
return DriverManager.getConnection(connectionUrl, connectionName, connectionPassword);
}
catch (Exception e){
return null;
}
}
/**
* 根据字段数量创建表
*
* @param fieldNum 字段数量
* @return 表明
*/
public static String createTableByField(String url, String username, String password, Integer fieldNum){
String uuid = IdUtil.fastSimpleUUID();
try {
Connection conn = getDriverManager("com.mysql.cj.jdbc.Driver", url
, username, password);
// 判断数据库是否链接成功
if (conn != null && !conn.isClosed()) {
Statement stmt = conn.createStatement();
StringBuilder sql = new StringBuilder("CREATE TABLE `" + uuid + "` (`");
sql.append("id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',");
for(int i = 0; i< fieldNum; i++){
sql.append("`c").append(i).append("` text,");
}
sql.append("PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
stmt.executeUpdate(sql.toString());
stmt.close();
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
return uuid;
}
/**
* 根据字段和表名称插入数据
*
* @param tableName 表名称
* @param data 数据
*/
public static void insertDataByTableName(String url, String username, String password, String tableName, List<String[]> data){
try {
Connection conn = getDriverManager("com.mysql.cj.jdbc.Driver", url
, username, password);
// 判断数据库是否链接成功
if (conn != null && !conn.isClosed()) {
Statement stmt = conn.createStatement();
int len = data.get(0).length;
for(String[] d : data){
StringBuilder sql = new StringBuilder("INSERT INTO ");
sql.append("`");
sql.append(tableName);
sql.append("`");
sql.append(" (");
for(int i = 0; i < len; i++){
sql.append("`c").append(i).append("`,");
}
sql.deleteCharAt(sql.length()-1);
sql.append(") VALUES (");
for(String value : d){
sql.append("'").append(value).append("'").append(",");
}
sql.deleteCharAt(sql.length()-1);
sql.append(");");
stmt.executeUpdate(sql.toString());
}
stmt.close();
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 根据表名称查询所有数据
*
* @param tableName 表名称
* @return 表明
*/
public static Map<String, Object> getDataByTableName(String url, String username, String password, String tableName, String limitSql){
try {
Connection conn = getDriverManager("com.mysql.cj.jdbc.Driver", url, username, password);
// 判断数据库是否链接成功
if (conn != null && !conn.isClosed()) {
Map<String, Object> result = new HashMap<>(2);
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM `" + tableName + "`" + limitSql);
ResultSet rs = stmt.executeQuery();
ResultSetMetaData md = rs.getMetaData();
int num = md.getColumnCount();
List<String> titles = new ArrayList<>();
for (int i = 1; i <= num; i++) {
titles.add(md.getColumnName(i));
}
JSONArray data = new JSONArray();
while (rs.next()) {
cn.hutool.json.JSONObject mapOfColValues = new cn.hutool.json.JSONObject();
for (int i = 1; i <= num; i++) {
mapOfColValues.put(md.getColumnName(i), rs.getObject(i));
}
data.add(mapOfColValues);
}
result.put("data", data);
result.put("titles", titles);
int totalNum = 0;
stmt = conn.prepareStatement("SELECT COUNT(1) FROM `" + tableName + "`");
rs = stmt.executeQuery();
while (rs.next()) {
totalNum = rs.getInt(1);
}
result.put("total", totalNum);
stmt.close();
conn.close();
return result;
}
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 根据数据库链接信息获取该链接下所有数据库
*
* @param info 数据库链接信息
* @return 表明
*/
public static List<String> getDatabaseByLinkInfo(MysqlDbInfo info){
try {
Connection conn = getDriverManager("com.mysql.cj.jdbc.Driver"
, "jdbc:mysql://" + info.getConnectionUrl() + ":" + info.getConnectionPort()
, info.getConnectionName(), info.getConnectionPassword());
// 判断数据库是否链接成功
if (conn != null && !conn.isClosed()) {
DatabaseMetaData dmd = conn.getMetaData();
ResultSet rs=dmd.getCatalogs();
ResultSetMetaData md = rs.getMetaData();
int num = md.getColumnCount();
List<String> result = new ArrayList<>();
while(rs.next()){
for (int i = 1; i <= num; i++) {
result.add(rs.getObject(i).toString());
}
}
conn.close();
return result;
}
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 根据数据库链接信息获取该链接下所有数据库
*
* @param info 数据库链接信息
* @return 表明
*/
public static JSONArray getDataSheetByLinkInfo(MysqlDbInfo info){
try {
Connection conn = getDriverManager("com.mysql.cj.jdbc.Driver"
, "jdbc:mysql://" + info.getConnectionUrl() + ":" + info.getConnectionPort() + "/" + info.getDbName()
, info.getConnectionName(), info.getConnectionPassword());
// 判断数据库是否链接成功
if (conn != null && !conn.isClosed()) {
PreparedStatement stmt = conn.prepareStatement("SELECT table_name,table_comment FROM information_schema.tables WHERE table_schema = '" + info.getDbName() + "' AND table_type = 'base table'");
ResultSet rs = stmt.executeQuery();
ResultSetMetaData md = rs.getMetaData();
int num = md.getColumnCount();
JSONArray result = new JSONArray();
while (rs.next()) {
cn.hutool.json.JSONObject mapOfColValues = new cn.hutool.json.JSONObject();
for (int i = 1; i <= num; i++) {
mapOfColValues.put(md.getColumnName(i), rs.getObject(i));
}
result.add(mapOfColValues);
}
stmt.close();
conn.close();
return result;
}
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 根据数据库信息执行sql
*
* @param url 数据库地址
* @param username 用户名
* @param password 密码
* @param sql sql
*/
public static void executeSql(String url, String username, String password, String sql) {
try {
Connection conn = getDriverManager("com.mysql.cj.jdbc.Driver", url, username, password);
// 判断数据库是否链接成功
if (conn != null && !conn.isClosed()) {
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.execute();
stmt.close();
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
