HOW TO：开始使用 Microsoft JDBC

具体参考：http://support.microsoft.com/default.aspx?scid=kb;en-us;313100#5

HOW TO: Get Started with Microsoft JDBC

Article ID	:	313100
Last Review	:	January 9, 2004
Revision	:	1.0

This article was previously published under Q313100

IN THIS TASK

•

SUMMARY

•	To Set the CLASSPATH Variable
•	To Register the Driver
•	To Pass the Connection URL
•	Sample Code to Test the Connection
•	Basic Connectivity Troubleshooting

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 Microsoft SQL Server 2000 Driver for JDBC, see the Microsoft SQL Server 2000 Driver for JDBC Installation Guide.

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 method 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.

back to the top

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

back to the top

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();
				

back to the top

To Pass the Connection URL

You 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.

back to the top

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
					

back to the top

Basic Connectivity Troubleshooting

These are common error messages that may occur when you try to connect to your SQL server:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Login failed for user 'user'. Reason: Not associated with a trusted SQL Server connection.

This error message occurs if the SQL Server 2000 authentication mode is set to Windows Authentication mode. The Microsoft SQL Server 2000 driver for JDBC does not support connecting by using Windows NT authentication. You must set the authentication mode of your SQL Server to Mixed mode, which permits both Windows Authentication and SQL Server Authentication.

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]This version of the JDBC driver only supports Microsoft SQL Server 2000. You can either upgrade to SQL Server 2000 or possibly locate another version of the driver.

This error message occurs you try to connect to a SQL Server version earlier than SQL Server 2000. The Microsoft SQL Server 2000 driver for JDBC supports connectivity only with SQL Server 2000.

back to the top

---

另外也参考如下：
连接sql2000
try {
　　Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
　　conn =
　　DriverManager.getConnection(
　　"jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=Hotel;User=sa;Password=sa");
　　//   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
　　//   conn = DriverManager.getConnection("jdbc:odbc:hotel");
　　} catch (SQLException e) {
　　System.err.println(e);
　　} catch (ClassNotFoundException e) {
　　System.err.println(e);
　　}