代码拉取完成，页面将自动刷新
package util;
import constant.CodeConstant;
import constant.Constant;
import entity.Parameters;
import entity.TableNameAndType;
import javax.swing.*;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
public class DBUtils {
public static Connection getConnection(Parameters parameters) {
Connection conn = null;
String url;
String driverClass;
if ("mysql".equals(parameters.getDataBaseTypeVal())) {
try {
url = "jdbc:mysql://" + parameters.getDataBaseIpVal() + ":" + parameters.getDataBasePortVal() + "/"
+ parameters.getDataBaseNameVal()
+ "?allowPublicKeyRetrieval=true&useSSL=false&connectTimeout=10000&socketTimeout=10000&serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull";
driverClass = "com.mysql.cj.jdbc.Driver";
parameters.setDataBaseUrl(url);
parameters.setDataBaseDriverClass(driverClass);
Class.forName(driverClass);
DriverManager.setLoginTimeout(10);
conn = DriverManager.getConnection(url, parameters.getDataBaseUserNameVal(),
parameters.getDataBasePwdVal());
} catch (Exception e) {
JOptionPane.showMessageDialog(Constant.frmv, "数据库连接失败！请检查数据库类型是否选择正确，相关配置是否填写正确！mysql请使用5.7及以上版本" + CodeConstant.NEW_LINE
+ "错误信息：" + e.getMessage(), "错误",
JOptionPane.ERROR_MESSAGE);
return null;
}
} else if ("oracle".equals(parameters.getDataBaseTypeVal())) {
try {
url = "jdbc:oracle:thin:@//" + parameters.getDataBaseIpVal() + ":" + parameters.getDataBasePortVal() + "/"
+ parameters.getDataBaseNameVal();
driverClass = "oracle.jdbc.OracleDriver";
parameters.setDataBaseUrl(url);
parameters.setDataBaseDriverClass(driverClass);
Class.forName(driverClass);
Properties info = new Properties();
info.put("oracle.net.CONNECT_TIMEOUT", 10000);
info.put("oracle.jdbc.ReadTimeout", 10000);
info.put("user", parameters.getDataBaseUserNameVal());
info.put("password", parameters.getDataBasePwdVal());
DriverManager.setLoginTimeout(10);
conn = DriverManager.getConnection(url, info);
} catch (Exception e) {
JOptionPane.showMessageDialog(Constant.frmv, "数据库连接失败！请检查数据库类型是否选择正确，相关配置是否填写正确！" + CodeConstant.NEW_LINE
+ "错误信息：" + e.getMessage(), "错误",
JOptionPane.ERROR_MESSAGE);
return null;
}
} else if ("postgresql".equals(parameters.getDataBaseTypeVal())) {
try {
url = "jdbc:postgresql://" + parameters.getDataBaseIpVal() + ":" + parameters.getDataBasePortVal()
+ "/" + parameters.getDataBaseNameVal() + "?socketTimeout=1&connectTimeout=1&useUnicode=true&characterEncoding=UTF-8";
driverClass = "org.postgresql.Driver";
parameters.setDataBaseUrl(url);
parameters.setDataBaseDriverClass(driverClass);
Class.forName(driverClass);
DriverManager.setLoginTimeout(10);
conn = DriverManager.getConnection(url, parameters.getDataBaseUserNameVal(),
parameters.getDataBasePwdVal());
} catch (Exception e) {
JOptionPane.showMessageDialog(Constant.frmv, "数据库连接失败！请检查数据库类型是否选择正确，相关配置是否填写正确！" + CodeConstant.NEW_LINE
+ "错误信息：" + e.getMessage(), "错误",
JOptionPane.ERROR_MESSAGE);
return null;
}
} else if ("sqlserver".equals(parameters.getDataBaseTypeVal())) {
try {
url = "jdbc:sqlserver://" + parameters.getDataBaseIpVal() + ":" + parameters.getDataBasePortVal() + ";DatabaseName=" + parameters.getDataBaseNameVal();
driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
parameters.setDataBaseUrl(url);
parameters.setDataBaseDriverClass(driverClass);
Class.forName(driverClass);
DriverManager.setLoginTimeout(10);
conn = DriverManager.getConnection(url, parameters.getDataBaseUserNameVal(),
parameters.getDataBasePwdVal());
} catch (Exception e) {
JOptionPane.showMessageDialog(Constant.frmv, "数据库连接失败！请检查数据库类型是否选择正确，相关配置是否填写正确！" + CodeConstant.NEW_LINE
+ "错误信息：" + e.getMessage(), "错误",
JOptionPane.ERROR_MESSAGE);
return null;
}
}
return conn;
}
/**
* 获取表的主键id
*
* @param parameters
* @param tableName
* @return
* @throws Exception
*/
public static List<String> getPrimarykey(Parameters parameters, String tableName) throws Exception {
List<String> primaryKeyList = new ArrayList<>(3);
Connection connection = getConnection(parameters);
if (connection == null) {
return null;
}
DatabaseMetaData metaData = connection.getMetaData();
String userName = connection.getMetaData().getUserName();
if ("oracle".equals(parameters.getDataBaseTypeVal())) {
tableName = tableName.toUpperCase();
} else if ("postgresql".equals(parameters.getDataBaseTypeVal())) {
if (tableName.contains(".")) {
String[] split = tableName.split("\\.");
tableName = split[split.length - 1];
}
}
// 获取主键字段
ResultSet primaryKeys = metaData.getPrimaryKeys(connection.getCatalog(), "oracle".equals(parameters.getDataBaseTypeVal()) ? userName : connection.getSchema(), tableName);
//根据结果集元数据打印内容
ResultSetMetaData pkmd = primaryKeys.getMetaData();
while (primaryKeys.next()) {
for (int i = 1; i <= pkmd.getColumnCount(); i++) {
//获取主键的名称
if ("COLUMN_NAME".equalsIgnoreCase(pkmd.getColumnName(i))) {
primaryKeyList.add(primaryKeys.getString(i));
}
}
}
connection.close();
return primaryKeyList;
}
/**
* @param tableNameVal
* @param connection
* @return
*/
private static Map<String, String> getColumnComment(String databaseType, String tableNameVal, Connection connection) {
PreparedStatement columnListPst = null;
ResultSet columnListRs = null;
String sql = "";
if ("mysql".equals(databaseType)) {
sql = "show full columns from `" + tableNameVal + "`";
} else if ("postgresql".equals(databaseType)) {
String[] tableNameArr = tableNameVal.split("\\.");
if (tableNameArr.length == 1) {
tableNameVal = tableNameArr[0];
} else {
tableNameVal = tableNameArr[1];
}
sql = "SELECT" +
" A.attname AS \"Field\"," +
" col_description ( A.attrelid, A.attnum ) AS \"Comment\"" +
" FROM" +
" pg_class AS C," +
" pg_attribute AS A " +
" WHERE" +
" C.relname = '" + tableNameVal + "' " +
" AND A.attrelid = C.oid " +
" AND A.attnum > 0";
} else if ("oracle".equals(databaseType)) {
sql = "select column_name AS \"Field\",comments AS \"Comment\" from user_col_comments where lower(table_Name)='" + tableNameVal.toLowerCase() + "'";
} else if ("sqlserver".equals(databaseType)) {
sql = "SELECT\n" +
"\tcast(col.name as varchar(500)) AS \"Field\",\n" +
"\tcast(ep.[value] as varchar(500)) AS \"Comment\" \n" +
"FROM\n" +
"\tdbo.syscolumns col\n" +
"\tLEFT JOIN dbo.systypes t ON col.xtype= t.xusertype\n" +
"\tINNER JOIN dbo.sysobjects obj ON col.id= obj.id \n" +
"\tAND obj.xtype= 'U' \n" +
"\tAND obj.status >= 0\n" +
"\tLEFT JOIN dbo.syscomments comm ON col.cdefault= comm.id\n" +
"\tLEFT JOIN sys.extended_properties ep ON col.id= ep.major_id \n" +
"\tAND col.colid= ep.minor_id \n" +
"\tAND ep.name= 'MS_Description'\n" +
"\tLEFT JOIN sys.extended_properties epTwo ON obj.id= epTwo.major_id \n" +
"\tAND epTwo.minor_id= 0 \n" +
"\tAND epTwo.name= 'MS_Description' \n" +
"WHERE\n" +
"\tobj.name= '" + tableNameVal + "'";
}
// 列名集合
Map<String, String> commentMqp = new HashMap<>();
try {
columnListPst = connection.prepareStatement(sql);
columnListRs = columnListPst.executeQuery();
while (columnListRs.next()) {
commentMqp.put(columnListRs.getString("Field").toUpperCase(), columnListRs.getString("Comment"));
}
} catch (SQLException e) {
JOptionPane.showMessageDialog(Constant.frmv, "获取数据库字段注释的时候出错（不影响后续使用）" + CodeConstant.NEW_LINE
+ "错误信息：" + e.getMessage(), "提示",
JOptionPane.WARNING_MESSAGE);
return commentMqp;
} finally {
try {
if (connection != null) {
connection.close();
}
} catch (SQLException ignored) {
}
try {
if (columnListPst != null) {
columnListPst.close();
}
} catch (SQLException ignored) {
}
try {
if (columnListRs != null) {
columnListRs.close();
}
} catch (SQLException ignored) {
}
}
return commentMqp;
}
public static List<String> getColumnNameList(String databaseType, String tableNameVal, Connection connection) {
PreparedStatement columnListPst = null;
ResultSet columnListRs = null;
String sql = "";
if ("mysql".equals(databaseType)) {
sql = "select * from `" + tableNameVal + "` where 1=0";
} else if ("oracle".equals(databaseType)) {
sql = "select * from " + tableNameVal + " where 1=0";
} else if ("postgresql".equals(databaseType)) {
sql = "select * from " + tableNameVal + " where 1=0";
} else if ("sqlserver".equals(databaseType)) {
sql = "select * from " + tableNameVal + " where 1=0";
}
// 列名集合
List<String> columnList = new ArrayList<>();
try {
columnListPst = connection.prepareStatement(sql);
columnListRs = columnListPst.executeQuery();
ResultSetMetaData metaData = columnListRs.getMetaData();
int columnCount = metaData.getColumnCount();
for (int i = 0; i < columnCount; i++) {
columnList.add(metaData.getColumnName(i + 1));
}
} catch (SQLException e) {
return null;
} finally {
try {
if (connection != null) {
connection.close();
}
} catch (SQLException ignored) {
}
try {
if (columnListPst != null) {
columnListPst.close();
}
} catch (SQLException ignored) {
}
try {
if (columnListRs != null) {
columnListRs.close();
}
} catch (SQLException ignored) {
}
}
return columnList;
}
/**
* 获取表中所有字段名称和类型
*
* @return
*/
public static List<TableNameAndType> getColumnNameAndTypes(String databaseType, String tableNameVal,
Connection connection) {
PreparedStatement columnListPst = null;
ResultSet columnListRs = null;
String sql = "";
if ("mysql".equals(databaseType)) {
sql = "select * from `" + tableNameVal + "` where 1=0";
} else if ("oracle".equals(databaseType)) {
sql = "select * from " + tableNameVal + " where 1=0";
} else if ("postgresql".equals(databaseType)) {
sql = "select * from " + tableNameVal + " where 1=0";
} else if ("sqlserver".equals(databaseType)) {
sql = "select * from " + tableNameVal + " where 1=0";
}
// 列名集合
List<TableNameAndType> nameAndTypes = new ArrayList<>();
try {
columnListPst = connection.prepareStatement(sql);
columnListRs = columnListPst.executeQuery();
ResultSetMetaData metaData = columnListRs.getMetaData();
int columnCount = metaData.getColumnCount();
for (int i = 0; i < columnCount; i++) {
TableNameAndType nameAndType = new TableNameAndType();
nameAndType.setName(metaData.getColumnName(i + 1));
//设置sqlParamName
nameAndType.setSqlParamName(DataUtils.getSqlParam(metaData.getColumnName(i + 1)));
nameAndType.setComment((metaData.getColumnName(i + 1)));
String javaTypeName = "";
String javaClassName = "";
String columnTypeName = metaData.getColumnTypeName(i + 1).toUpperCase();
switch (databaseType) {
case "postgresql":
switch (columnTypeName) {
case "INT2":
case "SERIAL4":
case "SERIAL2":
case "INT4":
javaTypeName = "Integer";
break;
case "INT8":
case "SERIAL8":
javaTypeName = "Long";
break;
case "DATE":
case "TIMESTAMPTZ":
case "TIMESTAMP":
case "TIMETZ":
case "TIME":
javaTypeName = "Date";
javaClassName = "java.util.Date";
break;
default:
javaTypeName = "String";
break;
}
break;
case "mysql":
switch (columnTypeName) {
case "INT":
case "MEDIUMINT":
case "BIT":
case "TINYINT":
case "SMALLINT":
case "BOOLEAN":
javaTypeName = "Integer";
break;
case "BLOB":
javaTypeName = "byte[]";
break;
case "INTEGER":
case "BIGINT":
// 主键
case "ID":
javaTypeName = "Long";
break;
case "FLOAT":
javaTypeName = "Float";
break;
case "DOUBLE":
javaTypeName = "Double";
break;
case "DECIMAL":
javaTypeName = "BigDecimal";
javaClassName = "java.math.BigDecimal";
break;
case "DATE":
case "DATETIME":
case "TIME":
case "TIMESTAMP":
case "YEAR":
javaTypeName = "Date";
javaClassName = "java.util.Date";
break;
default:
javaTypeName = "String";
break;
}
break;
case "oracle":
switch (columnTypeName) {
case "NUMBER":
javaTypeName = "Integer";
break;
case "DATE":
case "TIMESTAMP":
javaTypeName = "Date";
javaClassName = "java.util.Date";
break;
default:
javaTypeName = "String";
break;
}
break;
case "sqlserver":
switch (columnTypeName) {
case "BIGINT":
javaTypeName = "Long";
break;
case "DOUBLE":
javaTypeName = "Double";
break;
case "INTEGER":
javaTypeName = "Integer";
break;
case "DATE":
case "TIME":
case "TIMESTAMP":
case "DATETIME":
case "DATETIME2":
javaTypeName = "Date";
javaClassName = "java.util.Date";
break;
default:
javaTypeName = "String";
break;
}
default:
break;
}
nameAndType.setTypeName(javaTypeName);
nameAndType.setClassName(javaClassName);
nameAndTypes.add(nameAndType);
}
//System.out.println(nameAndTypes);
// 注释map
Map<String, String> columnComment = getColumnComment(databaseType, tableNameVal, connection);
for (TableNameAndType tableNameAndType : nameAndTypes) {
String name = tableNameAndType.getName().toUpperCase();
// 设置注释内容
assert columnComment != null;
tableNameAndType.setComment(
"".equals(columnComment.get(name)) || columnComment.get(name) == null ? tableNameAndType.getName() : columnComment.get(name));
}
} catch (SQLException e) {
return null;
} finally {
try {
if (connection != null) {
connection.close();
}
} catch (SQLException ignored) {
}
try {
if (columnListPst != null) {
columnListPst.close();
}
} catch (SQLException ignored) {
}
try {
if (columnListRs != null) {
columnListRs.close();
}
} catch (SQLException ignored) {
}
}
return nameAndTypes;
}
}
此处可能存在不合适展示的内容，页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容，可点击提交进行申诉，我们将尽快为您处理。
马建仓 AI 助手