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));//设置sqlParamNamenameAndType.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);// 注释mapMap<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;}}
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。
1. Open source ecosystem
2. Collaboration, People, Software
3. Evaluation model