开发进行时...

SUMMARY

This article describes how to connect to SQL Server 2000 by using the Microsoft SQL Server 2000 driver for JDBC.

Note: For installation instructions for Microsfot SQL Server 2000 Driver for JDBC, see the Download

After you install the Microsoft SQL Server 2000 driver for JDBC, you can connect from your program to your database in two ways: with a connection URL, or with a JNDI data source. This article describes how to configure and test your database connection by using a connection URL.

One way of connecting to a database is by through JDBC Driver Manager by using the getConnection method of the DriverManager class. The simplest manner of using this mehtod takes a string parameter that contains an URL, a user name, and a password. The following sections in this article describe how to load the Microsoft SQL Server 2000 driver for JDBC from your JDBC program.

To Set the CLASSPATH Variable

The Microsoft SQL Server 2000 driver for JDBC.jar files must be listed in your CLASSPATH variable. The CLASSPATH variable is the search string that Java Virtual Machine(JVM) uses to locate the JDBC drivers on your computer. If the drivers are not listed in your CLASSPATH variable, you receive the following error message when you try to load the driver:
java.lang.ClassNotFoundException: com/microsoft/jdbc/sqlserver/SQLServerDriver
Set your system CLASSPATH variable to include the following entries:
\your installation path\Lib\Msbase.jar
\your installation path\Lib\Msutil.jar
\your installation path\Lib\Mssqlserver.jar

This is an example of a configured CLASSPATH variable:

CLASSPATH=.;c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\msbase.jar;c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\msutil.jar;c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\mssqlserver.jar

To Register the Driver
Registering the driver instructs JDBC Driver Manager which driver to load. When you load a driver by using the class.forName function, you must specify the name of the driver. This is the driver name for Microsoft SQL Server 2000 Driver for JDBC:
com.microsoft.jdbc.sqlserver.SQLServerDriver
The following sample code demonstrates how to register the driver:

Driver d = (Driver)Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();

[b]To Pass the Connection URL
[/b]

ou must pass your database connection information in the form of a connection URL. This is a template URL for Microsoft SQL Server 2000 Driver for JDBC. Substitute the values for your database:
jdbc:microsoft:sqlserver://servername:1433
The following sample code demonstrates how to specify a connection URL:

con = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433", "userName", "password");

The server name values can be an IP address or a host name (assuming that your network resolves host names to IP addresses). You can test this by pinging the host name and verifying that you receive a reply with the correct IP address.

The numeric value after the server name is the port number on which the database is listening. The values that are listed earlier in this article are sample default values. Make sure to substitute the port number that your database is using.

For a complete list of connection URL parameters, see the Microsoft SQL Server 2000 Driver for JDBC HTML Help, or see the Online Guide. See the "Connection String Properties" section.

Sample Code to Test the Connection
The following sample code tries to connect to the database and displays the database name, the version, and the available catalogs. Replace the server properties with the values for your server:

import java.*;
public class Connect{
private java.sql.Connection con = null;
private final String url = "jdbc:microsoft:sqlserver://";
private final String serverName= "localhost";
private final String portNumber = "1433";
private final String databaseName= "pubs";
private final String userName = "user";
private final String password = "password";
// Informs the driver to use server a side-cursor,
// which permits more than one active statement
// on a connection.
private final String selectMethod = "cursor";

// Constructor
public Connect(){}

private String getConnectionUrl(){
return url+serverName+":"+portNumber+";databaseName="+databaseName+";selectMethod="+selectMethod+";";
}

private java.sql.Connection getConnection(){
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con = java.sql.DriverManager.getConnection(getConnectionUrl(),userName,password);
if(con!=null) System.out.println("Connection Successful!");
}catch(Exception e){
e.printStackTrace();
System.out.println("Error Trace in getConnection() : " + e.getMessage());
}
return con;
}

/*
Display the driver properties, database details
*/

public void displayDbProperties(){
java.sql.DatabaseMetaData dm = null;
java.sql.ResultSet rs = null;
try{
con= this.getConnection();
if(con!=null){
dm = con.getMetaData();
System.out.println("Driver Information");
System.out.println("\tDriver Name: "+ dm.getDriverName());
System.out.println("\tDriver Version: "+ dm.getDriverVersion ());
System.out.println("\nDatabase Information ");
System.out.println("\tDatabase Name: "+ dm.getDatabaseProductName());
System.out.println("\tDatabase Version: "+ dm.getDatabaseProductVersion());
System.out.println("Avalilable Catalogs ");
rs = dm.getCatalogs();
while(rs.next()){
System.out.println("\tcatalog: "+ rs.getString(1));
}
rs.close();
rs = null;
closeConnection();
}else System.out.println("Error: No active Connection");
}catch(Exception e){
e.printStackTrace();
}
dm=null;
}

private void closeConnection(){
try{
if(con!=null)
con.close();
con=null;
}catch(Exception e){
e.printStackTrace();
}
}
public static void main(String[] args) throws Exception
{
Connect myDbTest = new Connect();
myDbTest.displayDbProperties();
}
}

If this code is successful, the output is similar to the following:

Connection Successful!
Driver Information
Driver Name: SQLServer
Driver Version: 2.2.0022

Database Information
Database Name: Microsoft SQL Server
Database Version: Microsoft SQL Server 2000 - 8.00.384 (Intel X86)
May 23 2001 00:02:52
Copyright (c) 1988-2000 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: )

Avalilable Catalogs
catalog: master
catalog: msdb
catalog: pubs
catalog: tempdb