原创|其它|编辑:郝浩|2009-09-27 11:07:49.000|阅读 642 次
概述:本文介绍了java的jdbc集成windows方式连接方式,共有两种:data source object和URL方式,分别见第一种和第二种。
# 慧都年终大促·界面/图表报表/文档/IDE等千款热门软控件火热促销中 >>
如何以windows集成方式连接SQL Server,这个以前真没试过。
于是,打开netBeans测试了一下,代码如下:
+ expand sourceview plaincopy to clipboardprint?
/*
* 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) {
}
}
}
}
}
/*
* 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方式也可以实现:
代码如下:
+ expand sourceview plaincopy to clipboardprint?
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package testsqlconn;
import java.sql.*;
/** *//**
*
* @author: 欢迎与邀月交流,net技术与软件架构
* @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) {
}
}
}
}
}
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package testsqlconn;
import java.sql.*;
/** *//**
*
* @author: 欢迎与邀月交流,net技术与软件架构
* @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 ,简单多了:
+ expand sourceview plaincopy to clipboardprint?
/*
* 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();
}
}
}
/*
* 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();
}
}
}
测试代码:
view plaincopy to clipboardprint?
TestSqlUserPwdURL test3=new TestSqlUserPwdURL();
String sql="SELECT top 10 ProductID,[Name],ProductNumber,Modifieddate FROM [SalesLT].[Product] ";
test3.ShowProduct("192.168.30.99\\agronet08", "AdventureWorksLT2008", "sa", "sa", 1433, sql);
TestSqlUserPwdURL test3=new TestSqlUserPwdURL();
String sql="SELECT top 10 ProductID,[Name],ProductNumber,Modifieddate FROM [SalesLT].[Product] ";
test3.ShowProduct("192.168.30.99\\agronet08", "AdventureWorksLT2008", "sa", "sa", 1433, sql);
小结:java的jdbc集成windows方式连接共有两种方式:data source object和URL方式,分别见第一种和第二种。
本站文章除注明转载外,均为本站原创或翻译。欢迎任何形式的转载,但请务必注明出处、不得修改原文相关链接,如果存在内容上的异议请邮件反馈至chenjj@pclwef.cn
文章转载自:IT专家网