使用 Microsoft JDBC Driver 连接 Azure SQL Database

目前 Azure 平台 SQL Database 支持使用多种 JDBC Driver 进行连接, 例如:Microsoft JDBC Driver 和 JTDS 等其他第三方驱动。但是在实际使用中,类似 JTDS 等 Driver 在连接过程中存在不同于 Microsoft JDBC Driver 的行为,而该行为会导致异常的发生,所以我们建议客户在情况允许下尽量尝试使用 Microsoft JDBC Driver 连接 SQL DB。下面将演示在同一个方案中,JTDS 驱动连接 SQL DB 出现异常而 Microsoft 驱动连接正常。

问题现象

在 Eclipse 中使用 JTDS 和 Microsoft JDBC Driver 连接 Azure SQL DB,对某一张表进行插入 5 条和 30 条数据操作。使用 Microsoft 驱动时,两种情况都能正常插入;然后使用 JTDS 插入 5 条数据时运行正常但是插入 30 条数据时返回错误。

问题描述

  • Microsoft JDBC Driver

    以下为 Java 代码

    package test;
    
    //Use the JDBC driver  
    import java.sql.*;  
    import com.microsoft.*;  
    
    public class microsoft_driver_sample {  
        // Connect to your database.  
        // Replace server name, username, and password with your credentials  
        public static void main(String[] args) {  
            String connectionString =  
                "jdbc:sqlserver://servername.database.chinacloudapi.cn:1433;"  
                + "database=dbname;"  
                + "user=username;"  
                + "password=YourPassword;"  
                + "encrypt=true;"  
                + "trustServerCertificate=true;"  
                + "hostNameInCertificate=*.database.chinacloudapi.cn;"  
                + "loginTimeout=30;";    
    
            // Declare the JDBC objects.  
            Connection connection = null;  
            Statement statement = null;   
            ResultSet resultSet = null;  
            PreparedStatement prepsInsertProduct = null;  
    
            try {  
                connection = DriverManager.getConnection(connectionString);
                // Create and execute an INSERT SQL prepared statement.
                String selectSql = " insert into top5m "
                        + "         select '20170101' , '10' , '13714' , '81948' , '82565' , '303166' , '75648' , '40.7625' , '-73.9861' , '40.7625' , '-73.9861' , '40.8029' , '-73.9527' , '40.8029' , '-73.9527' , '1' , '616' , '3.4' , 'CSH' , '12.5' , '0.5' , '0.5' , '0' , '0' , '13.5' "
                        + " union all select '20170102' , '10' , '13714' , '79428' , '79518' , '70098' , '37119' , '40.751' , '-73.9941' , '40.751' , '-73.9941' , '40.7551' , '-73.9916' , '40.7551' , '-73.9916' , '1' , '90' , '0.3' , 'CSH' , '3.5' , '0.5' , '0.5' , '0' , '0' , '4.5' "
                        + " union all select '20170103' , '10' , '13714' , '79989' , '80395' , '42235' , '90430' , '40.7512' , '-73.994' , '40.7512' , '-73.994' , '40.7705' , '-73.9855' , '40.7705' , '-73.9855' , '2' , '405' , '1.6' , 'CSH' , '7.5' , '0.5' , '0.5' , '0' , '0' , '8.5' "
                        + " union all select '20170104' , '100' , '34413' , '76620' , '77280' , '90187' , '140693' , '40.7528' , '-73.9889' , '40.7528' , '-73.9889' , '40.725' , '-73.994' , '40.725' , '-73.994' , '5' , '660' , '2.51' , 'CSH' , '10' , '0.5' , '0.5' , '0' , '0' , '11' "
                        + " union all select '20170105' , '10000' , '11682' , '21420' , '22260' , '41922' , '206829' , '40.7622' , '-73.9683' , '40.7622' , '-73.9683' , '40.7633' , '-73.9402' , '40.7633' , '-73.9402' , '3' , '840' , '3.67' , 'CSH' , '14' , '0.5' , '0.5' , '0' , '0' , '15' "
                        + " union all select '20170106' , '10001' , '33292' , '74340' , '75060' , '30908' , '282467' , '40.7905' , '-73.9475' , '40.7905' , '-73.9475' , '40.7383' , '-73.984' , '40.7383' , '-73.984' , '1' , '720' , '4.2' , 'CSH' , '14' , '0.5' , '0.5' , '0' , '0' , '15' "
                        + " union all select '20170107' , '10001' , '33292' , '9600' , '10860' , '278119' , '253826' , '40.7102' , '-74.0011' , '40.7102' , '-74.0011' , '40.7241' , '-74.0092' , '40.7241' , '-74.0092' , '1' , '1260' , '6.37' , 'CSH' , '21' , '0.5' , '0.5' , '0' , '0' , '22' "
                        + " union all select '20170108' , '10001' , '33292' , '8160' , '8580' , '278119' , '37908' , '40.7102' , '-74.0011' , '40.7102' , '-74.0011' , '40.7029' , '-73.9865' , '40.7029' , '-73.9865' , '1' , '420' , '2' , 'CSH' , '8.5' , '0.5' , '0.5' , '0' , '0' , '9.5' "
                        + " union all select '20170109' , '10002' , '31417' , '38683' , '39341' , '78781' , '196448' , '40.7558' , '-73.962' , '40.7558' , '-73.962' , '40.7569' , '-73.9874' , '40.7569' , '-73.9874' , '1' , '658' , '2.1' , 'CSH' , '10' , '0' , '0.5' , '0' , '0' , '10.5' "
                        + " union all select '20170110' , '10006' , '32255' , '862' , '936' , '194301' , '28080' , '40.7345' , '-74.0024' , '40.7345' , '-74.0024' , '40.7266' , '-74.0056' , '40.7266' , '-74.0056' , '1' , '73' , '0.5' , 'CSH' , '3.5' , '0.5' , '0.5' , '0' , '0' , '4.5' "
                        + " union all select '20170111' , '10006' , '32255' , '13832' , '14754' , '17387' , '197463' , '40.7678' , '-73.9939' , '40.7678' , '-73.9939' , '40.7094' , '-74.01' , '40.7094' , '-74.01' , '3' , '922' , '4.6' , 'CSH' , '17' , '0.5' , '0.5' , '0' , '0' , '18' "
                        + " union all select '20170112' , '10009' , '8842' , '2580' , '3600' , '276408' , '163385' , '40.7566' , '-73.9939' , '40.7566' , '-73.9939' , '40.7342' , '-73.9781' , '40.7342' , '-73.9781' , '5' , '1020' , '2.47' , 'CSH' , '12.5' , '0.5' , '0.5' , '0' , '0' , '13.5' "
                        + " union all select '20170113' , '1001' , '2136' , '76019' , '76209' , '238464' , '96401' , '40.7642' , '-73.9771' , '40.7642' , '-73.9771' , '40.7772' , '-73.9762' , '40.7772' , '-73.9762' , '1' , '189' , '1.1' , 'CSH' , '5.5' , '0.5' , '0.5' , '0' , '0' , '6.5' "
                        + " union all select '20170114' , '1001' , '2136' , '5015' , '5452' , '116085' , '300418' , '40.7589' , '-73.9956' , '40.7589' , '-73.9956' , '40.756' , '-73.9947' , '40.756' , '-73.9947' , '1' , '436' , '0.8' , 'CSH' , '6.5' , '0.5' , '0.5' , '0' , '0' , '7.5' "
                        + " union all select '20170115' , '10010' , '4375' , '23100' , '23400' , '71544' , '71544' , '40.7427' , '-73.9885' , '40.7427' , '-73.9885' , '40.7427' , '-73.9885' , '40.7427' , '-73.9885' , '1' , '300' , '1.02' , 'CSH' , '6' , '0' , '0.5' , '0' , '0' , '6.5' "
                        + " union all select '20170116' , '10010' , '4375' , '43200' , '43500' , '299755' , '299755' , '40.7631' , '-73.9747' , '40.7631' , '-73.9747' , '40.7631' , '-73.9747' , '40.7631' , '-73.9747' , '1' , '300' , '0.96' , 'CSH' , '6' , '0' , '0.5' , '0' , '0' , '6.5' "
                        + " union all select '20170117' , '10011' , '11049' , '19020' , '19140' , '71766' , '138646' , '40.7644' , '-73.9771' , '40.7644' , '-73.9771' , '40.7694' , '-73.9809' , '40.7694' , '-73.9809' , '1' , '120' , '0.65' , 'CSH' , '4' , '0.5' , '0.5' , '0' , '0' , '5' "
                        + " union all select '20170118' , '10016' , '4044' , '85080' , '85320' , '77207' , '19159' , '40.7722' , '-73.9498' , '40.7722' , '-73.9498' , '40.7751' , '-73.9568' , '40.7751' , '-73.9568' , '1' , '240' , '0.7' , 'CSH' , '5' , '0.5' , '0.5' , '0' , '0' , '6' "
                        + " union all select '20170119' , '10016' , '4044' , '7320' , '8160' , '148402' , '229683' , '40.7112' , '-74.0159' , '40.7112' , '-74.0159' , '40.7215' , '-74.0048' , '40.7215' , '-74.0048' , '1' , '840' , '2.86' , 'CSH' , '12' , '0.5' , '0.5' , '0' , '0' , '13' "
                        + " union all select '20170120' , '10017' , '14611' , '49418' , '49578' , '124587' , '80377' , '40.7764' , '-73.9558' , '40.7764' , '-73.9558' , '40.7902' , '-73.9472' , '40.7902' , '-73.9472' , '2' , '159' , '1.1' , 'CSH' , '5' , '0' , '0.5' , '0' , '0' , '5.5' "
                        + " union all select '20170121' , '10017' , '29868' , '15179' , '15822' , '203624' , '216131' , '40.7454' , '-73.9868' , '40.7454' , '-73.9868' , '40.7232' , '-73.9895' , '40.7232' , '-73.9895' , '2' , '643' , '1.9' , 'CSH' , '9.5' , '0.5' , '0.5' , '0' , '0' , '10.5' "
                        + " union all select '20170122' , '10018' , '28347' , '21640' , '22602' , '27791' , '284844' , '40.8042' , '-73.9376' , '40.8042' , '-73.9376' , '40.7619' , '-73.9497' , '40.7619' , '-73.9497' , '2' , '961' , '6.7' , 'CSH' , '20' , '0' , '0.5' , '0' , '0' , '20.5' "
                        + " union all select '20170123' , '1002' , '16951' , '56200' , '57087' , '277409' , '99995' , '40.7494' , '-73.9923' , '40.7494' , '-73.9923' , '40.7093' , '-74.0178' , '40.7093' , '-74.0178' , '1' , '886' , '3.9' , 'CSH' , '14.5' , '0' , '0.5' , '0' , '0' , '15' "
                        + " union all select '20170124' , '1002' , '35304' , '14473' , '15148' , '240939' , '43609' , '40.6961' , '-73.9883' , '40.6961' , '-73.9883' , '40.6735' , '-73.9894' , '40.6735' , '-73.9894' , '1' , '674' , '2' , 'CSH' , '10' , '0.5' , '0.5' , '0' , '0' , '11' "
                        + " union all select '20170125' , '1002' , '35304' , '62041' , '62404' , '50834' , '254464' , '40.7563' , '-73.9725' , '40.7563' , '-73.9725' , '40.7769' , '-73.9554' , '40.7769' , '-73.9554' , '2' , '363' , '1.8' , 'CSH' , '7.5' , '0' , '0.5' , '0' , '0' , '8' "
                        + " union all select '20170126' , '1002' , '35304' , '1241' , '2748' , '13649' , '275206' , '40.7571' , '-73.9823' , '40.7571' , '-73.9823' , '40.7491' , '-73.992' , '40.7491' , '-73.992' , '2' , '1507' , '1.4' , 'CSH' , '15.5' , '0.5' , '0.5' , '0' , '0' , '16.5' "
                        + " union all select '20170127' , '1002' , '35304' , '83453' , '83919' , '288808' , '240926' , '40.7343' , '-73.9899' , '40.7343' , '-73.9899' , '40.7467' , '-74.0019' , '40.7467' , '-74.0019' , '2' , '466' , '1.4' , 'CSH' , '7.5' , '0.5' , '0.5' , '0' , '0' , '8.5' "
                        + " union all select '20170128' , '10020' , '16472' , '35203' , '35286' , '220089' , '271047' , '40.7556' , '-73.968' , '40.7556' , '-73.968' , '40.7521' , '-73.9754' , '40.7521' , '-73.9754' , '2' , '82' , '0.6' , 'CSH' , '4' , '0' , '0.5' , '0' , '0' , '4.5' "
                        + " union all select '20170129' , '10022' , '12947' , '5089' , '6013' , '287636' , '52881' , '40.7721' , '-73.9561' , '40.7721' , '-73.9561' , '40.7136' , '-73.9871' , '40.7136' , '-73.9871' , '1' , '923' , '5.3' , 'CSH' , '17.5' , '0.5' , '0.5' , '0' , '0' , '18.5' "
                        + " union all select '20170130' , '10022' , '13552' , '49834' , '50508' , '162211' , '94463' , '40.7372' , '-73.9963' , '40.7372' , '-73.9963' , '40.7679' , '-73.962' , '40.7679' , '-73.962' , '3' , '673' , '3.3' , 'CSH' , '11.5' , '0' , '0.5' , '0' , '0' , '12' ";                       
                prepsInsertProduct = connection.prepareStatement(selectSql, Statement.RETURN_GENERATED_KEYS);  
                prepsInsertProduct.execute();  
                System.out.println("Microsoft driver executed successfully");
                }  
            catch (Exception e) {  
                e.printStackTrace();  
            }  
            finally {  
                // Close the connections after the data has been handled.  
                if (prepsInsertProduct != null) try { prepsInsertProduct.close(); } catch(Exception e) {}  
                if (resultSet != null) try { resultSet.close(); } catch(Exception e) {}
                if (statement != null) try { statement.close(); } catch(Exception e) {}
                if (connection != null) try { connection.close(); } catch(Exception e) {}
            }  
        }  
    }  
    

    保存代码后,运行:

    console

  • JTDS

    代码和以上不同之处是 connection string 及调用方法:

    connection = DriverManager.getConnection("jdbc:jtds:sqlserver://servername.database.chinacloudapi.cn:1433/dbname ","username","Password");
    String selectSql = “ insert into top5m select ……”
    statement = connection.createStatement();               
        statement.execute(selectSql);                 
    System.out.println("Jtds driver Executed successfully");
    

    保存代码后,运行:

    console-2

问题分析

在抓取了相关网络包进行分析我们发现最新版 JTDS 使用的 TDS 版本为 7.1。

packet

Driver 将整个语句拆成了 5 个网络包(Frame Number 为 1929,1930,1931,1932 和 1992),然而 Frame Number 为 1992 的包距离 1932 时间差较大(1932 的 Flags 是 A),然后服务器端立即对之前的包进行了回复,但是发现 1929,1930,1931,1932 的包并不完整,随即对整个链接发出了中断的请求(1993), 最后出现 Reset 操作(2010)。这整个过程体现了 JTDS driver 对一个大的请求在拆包过程中与服务器交互出现了问题。

反观 Microsoft JDBC Driver 与服务器交互过程一切正常,且最后是由客户端发起了中断请求:

packet-2

总结

如今 Azure SQL DB 产品更新速度较快,第三方驱动在和 Azure SQL DB 交互之中的确可能存在某些异常情况, 特别是对那些许久未更新版本较老的驱动,所以建议客户在条件允许情况下尽可能使用 Microsoft JDBC Driver 连接 SQL DB。