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

prollerweg

　　在《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 Namecon = 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] ");//Sqlrs = 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.csdn.net/downmoon/" mce_href="http://blog.csdn.net/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 useString driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";// the database name//String dbName = "AdventureWorksLT2008";// define the Derby connection URL to useString 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