如何在MS SQL Server 2008 Express中使用MS JDBC驱动程序？

My configuration:

我的配置：

• windows XP SP3
• Windows XP SP3
• JDBC 2005
• JDBC 2005
• MS SQL Server 2008 Express, exposed via tcp/ip on port 1433
• MS SQL Server 2008 Express，通过端口1433上的tcp / ip公开
• sqljdbc.jar in class path
• 类路径中的sqljdbc.jar

I tried:

我试过了：

try {
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
    con = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433/SQLEXPRESS2008;databaseName=Test;selectMethod=cursor", "sa", "");
}
catch (Exception e) {
    e.printStackTrace();
}

But it always throws an exception:

但它总是抛出异常：

java.sql.SQLException: No suitable driver

I also tried the following urls:

我也试过以下网址：

localhost:1433/SQLEXPRESS2008

localhost/SQLEXPRESS2008

localhost

Same results. Any help?

结果相同。有帮助吗？

6 个解决方案

#1

You have the wrong URL.

您的网址错误。

I don't know what you mean by "JDBC 2005". When I looked on the microsoft site, I found something called the Microsoft SQL Server JDBC Driver 2.0. You're going to want that one - it includes lots of fixes and some perf improvements. [edit: you're probably going to want the latest driver. As of March 2012, the latest JDBC driver from Microsoft is JDBC 4.0]

我不知道你的意思是“JDBC 2005”。当我查看微软网站时，我发现了一种称为Microsoft SQL Server JDBC Driver 2.0的东西。你会想要那个 - 它包括许多修复和一些性能提升。 [编辑：你可能会想要最新的驱动程序。截至2012年3月，Microsoft的最新JDBC驱动程序是JDBC 4.0]

Check the release notes. For this driver, you want:

查看发行说明。对于此驱动程序，您需要：

URL:  jdbc:sqlserver://server:port;DatabaseName=dbname
Class name: com.microsoft.sqlserver.jdbc.SQLServerDriver

It seems you have the class name correct, but the URL wrong.

看来你的类名是正确的，但是URL错了。

Microsoft changed the class name and the URL after its initial release of a JDBC driver. The URL you are using goes with the original JDBC driver from Microsoft, the one MS calls the "SQL Server 2000 version". But that driver uses a different classname.

Microsoft在初始发布JDBC驱动程序后更改了类名和URL。您使用的URL与Microsoft的原始JDBC驱动程序一起使用，一个MS称为“SQL Server 2000版本”。但该驱动程序使用不同的类名。

For all subsequent drivers, the URL changed to the form I have here.

对于所有后续驱动程序，URL已更改为我在此处的表单。

This is in the release notes for the JDBC driver.

这是JDBC驱动程序的发行说明。

#2

1. Download the latest JDBC Driver (i.e. sqljdbc4.0) from Microsoft's web site
2. 从Microsoft的网站下载最新的JDBC驱动程序（即sqljdbc4.0）

3. Write the program as follows:

   编写程序如下：

   import java.sql.*;
   class testmssql
   {
       public static void main(String args[]) throws Exception
       {
           Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
           Connection      con=DriverManager.getConnection("jdbc:sqlserver://localhost:1433;
                   databaseName=chapter16","sa","123");//repalce your databse name and user name
               Statement st=con.createStatement();
           ResultSet rs=st.executeQuery("Select * from login");//replace your table name
           while(rs.next())
           {
               String s1=rs.getString(1);
               String s2=rs.getString(2);
               System.out.println("UserID:"+s1+"Password:"+s2);
           }
           con.close();
       } 
   }
   

4. Compile the program and set the jar classpath viz: set classpath=C:\jdbc\sqljdbc4.jar;.; If you have saved your jar file in C:\jdbc after downloading and extracting.

   编译程序并设置jar类路径viz：set classpath = C：\ jdbc \ sqljdbc4.jar;。;如果在下载和解压缩后将jar文件保存在C：\ jdbc中。

5. Run the program and make sure your TCP/IP service is enabled. If not enabled, then follow these steps:
   1. Go to Start -> All Programs -> Microsoft SQL Server 2008 -> Configuration tools -> SQL Server Configuration Manager
   2. 转到开始 - >所有程序 - > Microsoft SQL Server 2008 - >配置工具 - > SQL Server配置管理器
   3. Expand Sql Server Network Configuration: choose your MS SQL Server Instance viz. MSQSLSERVER and enable TCP/IP.
   4. 展开Sql Server网络配置：选择您的MS SQL Server实例即。 MSQSLSERVER并启用TCP / IP。
   5. Restart your MS SQL Server Instance. This can be done also from the right click menu of Microsoft SQL Server Management Studio at the root level of your MS SQL server instance
   6. 重新启动MS SQL Server实例。这也可以通过MS SQL Server实例根级别的Microsoft SQL Server Management Studio的右键菜单来完成
6. 运行该程序并确保已启用TCP / IP服务。如果未启用，请按照下列步骤操作：转到开始 - >所有程序 - > Microsoft SQL Server 2008 - >配置工具 - > SQL Server配置管理器展开Sql Server网络配置：选择您的MS SQL Server实例即。 MSQSLSERVER并启用TCP / IP。重新启动MS SQL Server实例。这也可以通过MS SQL Server实例根级别的Microsoft SQL Server Management Studio的右键菜单来完成

#3

If your databaseName value is correct, then use this: DriverManger.getconnection("jdbc:sqlserver://ServerIp:1433;user=myuser;password=mypassword;databaseName=databaseName;")

如果您的databaseName值正确，则使用此命令：DriverManger.getconnection（“jdbc：sqlserver：// ServerIp：1433; user = myuser; password = mypassword; databaseName = databaseName;”）

#4

The latest JDBC MSSQL connectivity driver can be found on JDBC 4.0

可以在JDBC 4.0上找到最新的JDBC MSSQL连接驱动程序

The class file should be in the classpath. If you are using eclipse you can easily do the same by doing the following -->

类文件应该在类路径中。如果你正在使用eclipse，你可以通过以下方式轻松地做同样的事情 - >

Right Click Project Name --> Properties --> Java Build Path --> Libraries --> Add External Jars

右键单击项目名称 - >属性 - > Java构建路径 - >库 - >添加外部罐

Also as already been pointed out by @Cheeso the correct way to access is jdbc:sqlserver://server:port;DatabaseName=dbname

另外正如@Cheeso已经指出的那样，访问的正确方法是jdbc：sqlserver：// server：port; DatabaseName = dbname

Meanwhile please find a sample class for accessing MSSQL DB (2008 in my case).

同时请找一个访问MSSQL DB的示例类（在我的例子中是2008）。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class ConnectMSSQLServer
{
   public void dbConnect(String db_connect_string,
            String db_userid,
            String db_password)
   {
      try {
         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
         Connection conn = DriverManager.getConnection(db_connect_string,
                  db_userid, db_password);
         System.out.println("connected");
         Statement statement = conn.createStatement();
         String queryString = "select * from SampleTable";
         ResultSet rs = statement.executeQuery(queryString);
         while (rs.next()) {
            System.out.println(rs.getString(1));
         }
         conn.close();
      } catch (Exception e) {
         e.printStackTrace();
      }
   }

   public static void main(String[] args)
   {
      ConnectMSSQLServer connServer = new ConnectMSSQLServer();
      connServer.dbConnect("jdbc:sqlserver://xx.xx.xx.xxxx:1433;databaseName=MyDBName", "DB_USER","DB_PASSWORD");
   }
}

Hope this helps.

希望这可以帮助。

#5

Named instances?

命名实例？

URL: jdbc:sqlserver://[serverName][\instanceName][:portNumber][;property=value]

URL：jdbc：sqlserver：// [serverName] [\ instanceName] [：portNumber] [; property = value]

Note: backward slash

注意：反斜杠

#6

You can try the following. Works fine in my case:

您可以尝试以下方法。在我的情况下工作正常：

1. Download the current jTDS JDBC Driver
2. 下载当前的jTDS JDBC驱动程序
3. Put jtds-x.x.x.jar in your classpath.
4. 将jtds-x.x.x.jar放在类路径中。
5. Copy ntlmauth.dll to windows/system32. Choose the dll based on your hardware x86,x64...
6. 将ntlmauth.dll复制到windows / system32。根据您的硬件x86，x64选择dll ......
7. The connection url is: 'jdbc:jtds:sqlserver://localhost:1433/YourDB' , you don't have to provide username and password.
8. 连接URL是：'jdbc：jtds：sqlserver：// localhost：1433 / YourDB'，您不必提供用户名和密码。

Hope that helps.

希望有所帮助。