使用 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) {} } } }保存代码后，运行：
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");保存代码后，运行：
问题分析
在抓取了相关网络包进行分析我们发现最新版 JTDS 使用的 TDS 版本为 7.1。
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 与服务器交互过程一切正常，且最后是由客户端发起了中断请求：
总结
如今 Azure SQL DB 产品更新速度较快，第三方驱动在和 Azure SQL DB 交互之中的确可能存在某些异常情况， 特别是对那些许久未更新版本较老的驱动，所以建议客户在条件允许情况下尽可能使用 Microsoft JDBC Driver 连接 SQL DB。