JDBC工具类，兼容IPv4和IPv6

最近要做IPv4和IPv6的兼容处理，且包含了MySQL、Oracle、DB2、SqlServer，所以进行了统一化处理，记录一下，方便后期使用，后期还会增加其他数据库，如有不足请大家及时指出。

import cn.hutool.core.lang.Validator;
import cn.hutool.core.util.ArrayUtil;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;import java.sql.*;
import java.util.*;/*** @author ZGY* @date 2023/4/28 11:08*/
public class JDBCUtil {private JDBCUtil() {throw new IllegalStateException("JDBCUtil Utility class");}private static final Logger logger = LoggerFactory.getLogger(JDBCUtil.class);public static final String MYSQL_DRIVER_NAME="com.mysql.cj.jdbc.Driver";public static final String ORACAL_DRIVER_NAME="oracle.jdbc.driver.OracleDriver";public static final String SQLSERVER_DRIVER_NAME="com.microsoft.sqlserver.jdbc.SQLServerDriver";public static final String DB2_DRIVER_NAME="com.ibm.db2.jcc.DB2Driver";/*** 获取Properties* @param type 类型标记* @param userName 用户名* @param password 密码* @return*/public static Properties getProperties(Integer type,String userName,String password){Properties properties = new Properties();properties.setProperty("user", userName);properties.setProperty("password", password);return properties;}/*** 获取连接URL* @param type 类型标识* @param ip Ip地址* @param port 端口* @param dbname 数据库名* @param ipv6 是否为IPv6* @return*/public static String getUrl(Integer type , String ip, String port, String dbname,boolean ipv6){String jdbcIpv4Url = "";String jdbcIpv6Url = "";if(Objects.equals(type, ConstUtil.MYSQL)){jdbcIpv4Url = "jdbc:mysql://"+ip+":"+port+"/"+dbname+"?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false";jdbcIpv6Url = "jdbc:mysql://address=(protocol=tcp)(host="+ip+")(port="+port+")/"+dbname+"?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false";}else if(Objects.equals(type, ConstUtil.DB2)){jdbcIpv4Url = "jdbc:db2://"+ip+":"+port+"/"+dbname;jdbcIpv6Url = "jdbc:db2://"+ip+":"+port+"/"+dbname;}else if(Objects.equals(type, ConstUtil.ORACLE)){jdbcIpv4Url = "jdbc:oracle:thin:@//"+ip+":"+port+"/"+dbname;jdbcIpv6Url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST="+ip+")(PORT="+port+"))(CONNECT_DATA=(SERVICE_NAME="+dbname+")))";}else if(Objects.equals(type, ConstUtil.SQLSERVER )){jdbcIpv4Url = "jdbc:sqlserver://"+ip+":"+port+";DatabaseName="+dbname;jdbcIpv6Url = "jdbc:sqlserver://;";}String jdbcUrl = ipv6 ? jdbcIpv6Url : jdbcIpv4Url;return jdbcUrl;}/*** 获取List 集合数据* @param type 设备类型* @param ip IP地址* @param port 端口* @param dbname 数据库名* @param userName 用户名* @param password 密码* @param sql SQL* @throws SQLException*/public static List> queryDBMapCustom(Integer type , String ip, String port, String dbname, String userName, String password, String sql)  {// 返回的集合List> list = new ArrayList<>();Properties properties = getProperties(type,userName,password);boolean ipv6 = false;//先判断是否为IPv4if (Validator.isIpv6(ip)) {ip = Tools.disposeIPv6Str(ip, type);ipv6 = true;}//获取连接URLString jdbcUrl = getUrl(type,ip,port,dbname,ipv6);try {if(Objects.equals(type, ConstUtil.MYSQL)){Class.forName(MYSQL_DRIVER_NAME);}else if(Objects.equals(type, ConstUtil.DB2)){Class.forName(DB2_DRIVER_NAME);}else if(Objects.equals(type, ConstUtil.ORACLE)){Class.forName(ORACAL_DRIVER_NAME);}else if(Objects.equals(type, ConstUtil.SQLSERVER )){if (ipv6) {properties.setProperty("portNumber", port);properties.setProperty("instanceName ", dbname);properties.setProperty("serverName", ip);}Class.forName(SQLSERVER_DRIVER_NAME);}} catch (ClassNotFoundException e) {logger.error("初始化连接异常：{}",e.getMessage());}if(StringUtils.isNoneBlank(jdbcUrl)){try (Connection conn = DriverManager.getConnection(jdbcUrl,properties); PreparedStatement pstmt=conn.prepareStatement(sql);ResultSet rs=pstmt.executeQuery();){pstmt.setQueryTimeout(30);// 获取结果集元数据ResultSetMetaData rsmd = rs.getMetaData();//  获取列的个数int columnCount = rsmd.getColumnCount();while(rs.next()){Map map = new HashMap<>();// 遍历每一行的每一列, 封装数据for (int i = 1; i <= columnCount; i++) {// 获取每一列的列名称// 获取每一列的列名称, 对应的值String value = rs.getString(i);map.put(i, value);}// 把封装完毕的对象，添加到list集合中list.add(map);}} catch (SQLException e) {logger.error("获取sql异常：{}",e.getMessage());}}return list;}
}

标记类ConstUtil

public class ConstUtil {public static final Integer MYSQL = 1;
public static final Integer SQLSERVER = 2;
public static final Integer ORACLE = 3;
public static final Integer DB2 = 4;
}

其中用到了工具类的方法Tools.disposeIPv6Str 是为了判断IPv6后增加"[]"用的，如下所示

/*** IPv6字符串处理方法* @param ip  ipv6地址* @param equipType 设备类型* @return*/
public static String disposeIPv6Str(String ip, Integer equipType) {if(Objects.equals(equipType,ConstUtil.DB2) || Objects.equals(equipType,ConstUtil.MYSQL)) {ip = "["+ip+"]";}return ip;}

虽然sonar建议去掉Class.forName，但是在实际过程中测试发现无此设置会连接失败。

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！