SQLServer多版本兼容Java方案和数据采集

Maven引入

<dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>4.0</version>
        </dependency>
        <dependency>
            <groupId>net.sourceforge.jtds</groupId>
            <artifactId>jtds</artifactId>
            <version>1.3.1</version>
        </dependency>

连接创建

// 数据库URL，用户名和密码
        String connectionUrl = "jdbc:jtds:sqlserver://"+dataSource.getIp()+":"+dataSource.getPort()+";databaseName="
                +dataSource.getDatabaseName()+";user="+dataSource.getUserName()+";password="+dataSource.getPassword()+";";
        try {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("当前不支持的SQLServer驱动版本");
        }

查询样例（SQLServer通用数据采集）

表内容查询

public List<List<JSONObject>> importData(DataSourceImportDataVO dataSourceImportDataVO) {

        List<List<JSONObject>> rtn = new ArrayList<>();
        // 创建SQLServer连接
        TbDataSource dataSource = dataSourceImportDataVO.getDataSource();
        Long dataCollectJobId = dataSourceImportDataVO.getDataCollect().getId();
        if (!DataSourceLinkTypeEnum.SQLSERVER.getTypeCode().equals(dataSource.getLinkType())){
            // 配置的连接不是SQLServer类型
            collectStatusMQService.send(dataCollectJobId, CollectJobRunStatusTypeEnum.CONNECT_FAILED);
            collectRecordMQService.send(dataSourceImportDataVO, CollectJobRunStatusTypeEnum.CONNECT_FAILED, "", "");
            throw new CustomException("数据源不是 SQLServer 连接");
        }
        SQLServerDataDealBaseService dataDealBaseService =(SQLServerDataDealBaseService) dataSourceImportDataVO.getDataDealBaseService();
        // 开始时间
        Date date = new Date();
        // 文件类型和文件名称枚举值
        // 数据库连接URL，格式为：jdbc:sqlserver://服务器地址:端口号;databaseName=数据库名
        String connectionUrl = "jdbc:jtds:sqlserver://"+dataSource.getIp()+":"+dataSource.getPort()+";databaseName="
                +dataSource.getDatabaseName();
        try {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // 数据源连接失败：可能发身在数据源连接创建过程中，或后续数据查询过程中，都算做连接失败
            collectStatusMQService.send(dataCollectJobId, CollectJobRunStatusTypeEnum.CONNECT_FAILED);
            collectRecordMQService.send(dataSourceImportDataVO, CollectJobRunStatusTypeEnum.CONNECT_FAILED, "", "");
            return new ArrayList<>();
        }
        // 数据库用户和密码
        String user = dataSource.getUserName();
        String password = dataSource.getPassword();
        List<JSONObject> datas = new ArrayList<>();
        try (Connection con = DriverManager.getConnection(connectionUrl, user, password);
             Statement stmt = con.createStatement()) {
            SQLServerDealVO sqlServerDealVO = new SQLServerDealVO();
            sqlServerDealVO.setConnection(con);
            sqlServerDealVO.setStatement(stmt);
            sqlServerDealVO.setTableName(dataSourceImportDataVO.getDataCollect().getFileType());
            sqlServerDealVO.setDataSourceImportData(dataSourceImportDataVO);
            sqlServerDealVO.setDataCollectJobId(dataCollectJobId);
            // 预置连接成功，后边查询数据过程中可能还会出现连接失败的情况，通过SQLException捕捉
            collectStatusMQService.send(dataCollectJobId, CollectJobRunStatusTypeEnum.CONNECT_SUCCESS);
            datas = dataDealBaseService.fromSQLServer(sqlServerDealVO);
            rtn.add(datas);
        } catch (SQLException e) {
            // 发送作业MQ状态
            log.error("【SQLServer 连接创建失败】 ", e);
            // 数据源连接失败：可能发身在数据源连接创建过程中，或后续数据查询过程中，都算做连接失败
            collectStatusMQService.send(dataCollectJobId, CollectJobRunStatusTypeEnum.CONNECT_FAILED);
            collectRecordMQService.send(dataSourceImportDataVO, CollectJobRunStatusTypeEnum.CONNECT_FAILED, "", "");
            return new ArrayList<>();
        }catch (DataParsingException e){
            // 发送作业MQ状态
            log.error("【SQLServer 数据解析失败】 ", e);
            collectRecordMQService.send(dataSourceImportDataVO, CollectJobRunStatusTypeEnum.PARSE_FAILED, JSON.toJSONString(datas), "采集数据源中的表 "+dataSourceImportDataVO.getDataCollect().getFileType()+" 或该表同步字段 "+dataSourceImportDataVO.getDataCollect().getSynchronousField()+" 不存在");
            return new ArrayList<>();
        }
        log.info("【数据接入】【SQLServer】处理完成 耗时 {} 秒 ", (new DateBetween(date, new Date())).between(DateUnit.SECOND));
        return rtn;
    }

表名称获取

public List<String> getDataSourceTables(TbDataSource dataSource) {
        // 数据库URL，用户名和密码
        String connectionUrl = "jdbc:jtds:sqlserver://"+dataSource.getIp()+":"+dataSource.getPort()+";databaseName="
                +dataSource.getDatabaseName()+";user="+dataSource.getUserName()+";password="+dataSource.getPassword()+";";
        List<String> tables = new ArrayList<>();
        try {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("当前不支持的SQLServer驱动版本");
        }
        try (Connection connection = DriverManager.getConnection(connectionUrl)) {
            // 创建Statement对象来执行查询
            Statement statement = connection.createStatement();

            // 执行查询获取所有表名
            ResultSet resultSet = statement.executeQuery("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'");
            // 遍历结果集
            while (resultSet.next()) {
                tables.add(resultSet.getString("TABLE_NAME"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return tables;
    }

字段名称获取

/**
* SQLServer 2005后版本判断
*/
private final String pattern = "对象名\\s+'.*?'\\s+无效";
    
public List<GetSourceColumnOutputDTO> getSourceColumns(TbDataSource dataSource, String tableName) {
        // 数据库URL，用户名和密码
        String connectionUrl = "jdbc:jtds:sqlserver://" + dataSource.getIp() + ":" + dataSource.getPort() + ";databaseName="
                + dataSource.getDatabaseName() + ";user=" + dataSource.getUserName() + ";password=" + dataSource.getPassword() + ";";
        List<GetSourceColumnOutputDTO> colums = new ArrayList<>();
        try {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("当前不支持的SQLServer驱动版本");
        }
        try (Connection connection = DriverManager.getConnection(connectionUrl)) {
//            // 获取DatabaseMetaData对象
//            DatabaseMetaData metaData = connection.getMetaData();
//
//            // 查询表的列名
//            ResultSet resultSet = metaData.getColumns(null, null, tableName, "%");

            // 创建Statement对象来执行查询
            Statement statement = connection.createStatement();

            String sql = "SELECT \n" +
                    "     CONVERT(NVARCHAR(100), c.name) AS ColumnName,\n" +
                    "    CONVERT(NVARCHAR(100),ep.value) AS ColumnComment\n" +
                    "FROM \n" +
                    "    sys.columns c\n" +
                    "INNER JOIN \n" +
                    "    sys.objects o ON c.object_id = o.object_id\n" +
                    "LEFT OUTER JOIN \n" +
                    "    sys.extended_properties ep ON ep.major_id = c.object_id \n" +
                    "                                AND ep.minor_id = c.column_id \n" +
                    "                                AND ep.name = 'MS_Description'\n" +
                    "WHERE \n" +
                    String.format("    o.name = '%s' \n", tableName) +
                    "ORDER BY \n" +
                    "    c.column_id;";
            // 执行查询获取所有表名
            ResultSet resultSet = null;
            try {
                resultSet = statement.executeQuery(sql);
            } catch (SQLException e) {
                // sys.extended_properties等 是 SQL Server 2005 引入的系统视图，用于存储扩展属性信息。SQL Server 2000 及更早版本中没有这个视图。
                if (e.getMessage().matches(".*" + pattern + ".*")){
                    sql = "SELECT \n" +
                            "        CONVERT(NVARCHAR(100), sc.name) AS ColumnName,\n" +
                            "        CONVERT(NVARCHAR(100), sp.value) AS ColumnComment\n" +
                            "    FROM \n" +
                            "        syscolumns sc\n" +
                            "    INNER JOIN \n" +
                            "        sysobjects so ON sc.id = so.id\n" +
                            "    LEFT OUTER JOIN \n" +
                            "        sysproperties sp ON sp.id = sc.id \n" +
                            "                          AND sp.smallid = sc.colid \n" +
                            "                          AND sp.name = 'MS_Description'\n" +
                            "    WHERE \n" +
                            String.format("    so.name = '%s' \n", tableName) +
                            "    ORDER BY \n" +
                            "        sc.colid";
                    resultSet = statement.executeQuery(sql);
                }
            }

            if (Objects.isNull(resultSet)) {
                return colums;
            }
            // 遍历结果集
            int sort = 1;
            while (resultSet.next()) {
                GetSourceColumnOutputDTO tmp = new GetSourceColumnOutputDTO();
                String columnName = resultSet.getString("ColumnName");
                String columnComment = resultSet.getString("ColumnComment");
                tmp.setName(StringUtils.isEmpty(columnComment) ? columnName : columnComment);
                tmp.setCode(columnName);
                tmp.setOriginCode(columnName);
                tmp.setSort(sort++);
                colums.add(tmp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return colums;
    }

public class GetSourceColumnOutputDTO implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "字段中文名")
    private String name;

    @ApiModelProperty(value = "源字段编码")
    private String code;

    @ApiModelProperty(value = "源字段原始编码")
    private String originCode;

    @ApiModelProperty(value = "源字段协议中的排序")
    private Integer sort;
    
}