SQL Server 2012为JDBC驱动配置XA

想要在项目中使用sqlserver的xa datasource就必须先配置sql server使其可以支持jdbc xa连接。微软doc中给出了详细的配置步骤和注意事项，请参考https://msdn.microsoft.com/en-us/library/aa342335.aspx
大致分为以下几步：

1. 确保sqlserver所在机器的MSDTC服务可用
2. 下载并安装SQL Server JDBC Driver http://go.microsoft.com/fwlink/?LinkId=245496
3. copy sqljdbc_xa.dll至sqlserver的Binn目录（需重启sqlserver）
4. 以sa的身份登陆SSMS执行xa_install.sql
5. 执行下面的sql给login授权使用XA

USE master  GO  EXEC sp_grantdbaccess 'login_name', 'login_name'  GO  EXEC sp_addrolemember [SqlJDBCXAUser], 'login_name'  

还可以用下面这个java class测试配置是否成功

import java.net.Inet4Address;  import java.sql.*;  import java.util.Random;  import javax.transaction.xa.*;  import javax.sql.*;  import com.microsoft.sqlserver.jdbc.*;  public class testXA {     public static void main(String[] args) throws Exception {        // Create variables for the connection string.        String prefix = "jdbc:sqlserver://";        String serverName = "localhost";        int portNumber = 1433;        String databaseName = "AdventureWorks";         String user = "UserName";         String password = "*****";        String connectionUrl = prefix + serverName + ":" + portNumber           + ";databaseName=" + databaseName + ";user=" + user + ";password=" + password;        try {           // Establish the connection.           Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");           Connection con = DriverManager.getConnection(connectionUrl);           // Create a test table.           Statement stmt = con.createStatement();           try {              stmt.executeUpdate("DROP TABLE XAMin");            }           catch (Exception e) {           }           stmt.executeUpdate("CREATE TABLE XAMin (f1 int, f2 varchar(max))");           stmt.close();           con.close();           // Create the XA data source and XA ready connection.           SQLServerXADataSource ds = new SQLServerXADataSource();           ds.setUser(user);           ds.setPassword(password);           ds.setServerName(serverName);           ds.setPortNumber(portNumber);           ds.setDatabaseName(databaseName);           XAConnection xaCon = ds.getXAConnection();           con = xaCon.getConnection();           // Get a unique Xid object for testing.           XAResource xaRes = null;           Xid xid = null;           xid = XidImpl.getUniqueXid(1);           // Get the XAResource object and set the timeout value.           xaRes = xaCon.getXAResource();           xaRes.setTransactionTimeout(0);           // Perform the XA transaction.           System.out.println("Write -> xid = " + xid.toString());           xaRes.start(xid,XAResource.TMNOFLAGS);           PreparedStatement pstmt =            con.prepareStatement("INSERT INTO XAMin (f1,f2) VALUES (?, ?)");           pstmt.setInt(1,1);           pstmt.setString(2,xid.toString());           pstmt.executeUpdate();           // Commit the transaction.           xaRes.end(xid,XAResource.TMSUCCESS);           xaRes.commit(xid,true);           // Cleanup.           con.close();           xaCon.close();           // Open a new connection and read back the record to verify that it worked.           con = DriverManager.getConnection(connectionUrl);           ResultSet rs = con.createStatement().executeQuery("SELECT * FROM XAMin");           rs.next();           System.out.println("Read -> xid = " + rs.getString(2));           rs.close();           con.close();        }         // Handle any errors that may have occurred.        catch (Exception e) {           e.printStackTrace();        }     }  }  class XidImpl implements Xid {     public int formatId;     public byte[] gtrid;     public byte[] bqual;     public byte[] getGlobalTransactionId() {return gtrid;}     public byte[] getBranchQualifier() {return bqual;}     public int getFormatId() {return formatId;}     XidImpl(int formatId, byte[] gtrid, byte[] bqual) {        this.formatId = formatId;        this.gtrid = gtrid;        this.bqual = bqual;     }     public String toString() {        int hexVal;        StringBuffer sb = new StringBuffer(512);        sb.append("formatId=" + formatId);        sb.append(" gtrid(" + gtrid.length + ")={0x");        for (int i=0; i<gtrid.length; i++) {           hexVal = gtrid[i]&0xFF;           if ( hexVal < 0x10 )              sb.append("0" + Integer.toHexString(gtrid[i]&0xFF));           else              sb.append(Integer.toHexString(gtrid[i]&0xFF));           }           sb.append("} bqual(" + bqual.length + ")={0x");           for (int i=0; i<bqual.length; i++) {              hexVal = bqual[i]&0xFF;              if ( hexVal < 0x10 )                 sb.append("0" + Integer.toHexString(bqual[i]&0xFF));              else                 sb.append(Integer.toHexString(bqual[i]&0xFF));           }           sb.append("}");           return sb.toString();        }        // Returns a globally unique transaction id.        static byte [] localIP = null;        static int txnUniqueID = 0;        static Xid getUniqueXid(int tid) {        Random rnd = new Random(System.currentTimeMillis());        txnUniqueID++;        int txnUID = txnUniqueID;        int tidID = tid;        int randID = rnd.nextInt();        byte[] gtrid = new byte[64];        byte[] bqual = new byte[64];        if ( null == localIP) {           try {              localIP = Inet4Address.getLocalHost().getAddress();           }           catch ( Exception ex ) {              localIP =  new byte[] { 0x01,0x02,0x03,0x04 };           }        }        System.arraycopy(localIP,0,gtrid,0,4);        System.arraycopy(localIP,0,bqual,0,4);        // Bytes 4 -> 7 - unique transaction id.        // Bytes 8 ->11 - thread id.        // Bytes 12->15 - random number generated by using seed from current time in milliseconds.        for (int i=0; i<=3; i++) {           gtrid[i+4] = (byte)(txnUID%0x100);           bqual[i+4] = (byte)(txnUID%0x100);           txnUID >>= 8;           gtrid[i+8] = (byte)(tidID%0x100);           bqual[i+8] = (byte)(tidID%0x100);           tidID >>= 8;           gtrid[i+12] = (byte)(randID%0x100);           bqual[i+12] = (byte)(randID%0x100);           randID >>= 8;        }        return new XidImpl(0x1234, gtrid, bqual);     }  }