java中如何以windows集成方式连接SQL Server

在《JDBC Driver For SQL2000/2005/2008 》一文中，邀月介绍了如何下载并使用jdbc连接SQL Server,
今天有人问起，如何以windows集成方式连接SQL Server,这个以前真没试过。
于是，打开netBeans测试了一下，代码如下：

/* * To change this template, choose Tools | Templates * and open the template in the editor. */ package testsqlconn; import java.sql.*; import com.microsoft.sqlserver.jdbc.*; /** *//** * * @author: Administrator:downmoon(3w@live.cn) * @date:2009-9-23 18:42:32 * @Encoding:UTF-8 * @File:TestSqlbyDS/TestSqlbyDS.java * @Package:testsqlconn */ public class TestSqlbyDS { public TestSqlbyDS(){} public void GetResutls() { // Declare the JDBC objects. Connection con = null; CallableStatement cstmt = null; ResultSet rs = null; try { // Establish the connection. SQLServerDataSource ds = new SQLServerDataSource(); ds.setIntegratedSecurity(true); ds.setServerName("ap4//agronet08");//数据库实例名 ds.setPortNumber(1433); ds.setDatabaseName("AdventureWorksLT2008");//Database Name con = ds.getConnection(); // Execute a SQL that returns some data. //cstmt = con.prepareCall("{call dbo.uspGetEmployeeManagers(?)}"); //cstmt.setInt(1,50); cstmt = con.prepareCall(" select top 10 * from [SalesLT].[Product] ");//Sql rs = cstmt.executeQuery(); // Iterate through the data in the result set and display it. while (rs.next()) { System.out.println("Product: " + rs.getString("Name") + ", " + rs.getString("ProductNumber")); System.out.println("ListPrice: " + rs.getString("ListPrice")); System.out.println(); } } // Handle any errors that may have occurred. catch (Exception e) { e.printStackTrace(); } finally { if (rs != null) { try { rs.close(); } catch (Exception e) { } } if (cstmt != null) { try { cstmt.close(); } catch (Exception e) { } } if (con != null) { try { con.close(); } catch (Exception e) { } } } } }

结果提示：找不到sqljdbc_auth.dll, 到下载的压缩包里看了下： auth/x86,auth/x64/,auth/IA64下都有该文件，直接复制auth/x86/sqljdbc_auth.dll到
E:/Java/jdkUpdate/jre/lib/ext/下，这是本机的jre路径。
然后运行。成功！
后来再试了下，发现直接用URL方式也可以实现：
代码如下：

/* * To change this template, choose Tools | Templates * and open the template in the editor. */ package testsqlconn; import java.sql.*; /** *//** * * @author: <a title="邀月工作室" href="http://blog.youkuaiyun.com/downmoon/" mce_href="http://blog.youkuaiyun.com/downmoon/" >欢迎与邀月交流,net技术与软件架构</a> * @date:2009-9-23 18:42:32 * @Encoding:UTF-8 * @File:TestSqlByURL/TestSqlByURL.java * @Package:testsqlconn */ public class TestSqlByURL { public TestSqlByURL() { } public void GetResults() { // Create a variable for the connection string. String connectionUrl = "jdbc:sqlserver://ap4//agronet08:1433;databaseName=AdventureWorksLT2008;integratedSecurity=true;"; // Declare the JDBC objects. Connection con = null; Statement stmt = null; ResultSet rs = null; try { // Establish the connection. Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); con = DriverManager.getConnection(connectionUrl); // Create and execute an SQL statement that returns some data. String SQL = "SELECT TOP 10 * FROM [SalesLT].[Product]"; stmt = con.createStatement(); rs = stmt.executeQuery(SQL); // Iterate through the data in the result set and display it. while (rs.next()) { System.out.println(rs.getString(2) + " " + rs.getString(3)); } } // Handle any errors that may have occurred. catch (Exception e) { e.printStackTrace(); } finally { if (rs != null) { try { rs.close(); } catch (Exception e) { } } if (stmt != null) { try { stmt.close(); } catch (Exception e) { } } if (con != null) { try { con.close(); } catch (Exception e) { } } } } }

如果是用户名加密码的URL方式，则不需要sqljdbc_auth.dll ,简单多了：

/* * To change this template, choose Tools | Templates * and open the template in the editor. */ package testsqlconn; import java.sql.*; import com.microsoft.sqlserver.jdbc.*; /** *//** * * @author: Administrator:downmoon(3w@live.cn) * @date:2009-9-23 18:42:32 * @Encoding:UTF-8 * @File:TestSqlUserPwdURL/TestSqlUserPwdURL.java * @Package:testsqlconn */ public class TestSqlUserPwdURL { public TestSqlUserPwdURL(){} public static void ShowProduct(String ip,String dbName,String user,String pwd,int port,String sql) { try { // ## DEFINE VARIABLES SECTION ## // define the driver to use String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; // the database name //String dbName = "AdventureWorksLT2008"; // define the Derby connection URL to use String connectionURL = "jdbc:sqlserver://"+ip+":"+port+";databaseName=" + dbName; // System.out.println(connectionURL); Connection conn = null; // Beginning of JDBC code sections // ## LOAD DRIVER SECTION ## Class.forName(driver); System.out.println(driver + " loaded. "); conn = DriverManager.getConnection(connectionURL, user, pwd); Statement s = conn.createStatement(); ResultSet rs = s.executeQuery(sql); while (rs.next()) { System.out.println("ID : " + rs.getInt(1)); System.out.println("Name : " + rs.getString(2)); System.out.println("Number: " + rs.getString(3)); System.out.println("Time: " + rs.getString(4)); System.out.println(); } rs.close(); s.close(); conn.close(); } catch (Exception e) { System.out.println("Exception: " + e); e.printStackTrace(); } } }

测试代码：

TestSqlUserPwdURL test3=new TestSqlUserPwdURL(); String sql="SELECT top 10 ProductID,[Name],ProductNumber,Modifieddate FROM [SalesLT].[Product] order by Modifieddate desc "; test3.ShowProduct("192.168.30.99//agronet08", "AdventureWorksLT2008", "sa", "sa", 1433, sql);

小结：java的jdbc集成windows方式连接共有两种方式：data source object和URL方式，分别见第一种和第二种。

如果有任何问题，请联系邀月。

助人等于自助! 3w@live.cn