如何使用JDBC连接到SQL Server 2008数据库?

I have MSSQL 2008 installed on my local PC, and my Java application needs to connect to a MSSQL database. I am a new to MSSQL and I would like get some help on creating user login for my Java application and getting connection via JDBC. So far I tried to create a user login for my app and used following connection string, but I doesn't work at all. Any help and hint will be appreciated.

我在本地PC上安装了MSSQL 2008，我的Java应用程序需要连接到MSSQL数据库。我是MSSQL的新手，我希望在为Java应用程序创建用户登录和通过JDBC连接方面得到一些帮助。到目前为止，我尝试为我的应用程序创建一个用户登录，并使用了以下连接字符串，但我根本就不工作。如有任何帮助和提示，我们将不胜感激。

jdbc:jtds:sqlserver://127.0.0.1:1433/dotcms 
username="shuxer"  password="itarator"

8 个解决方案

#1

There are mainly two ways to use JDBC - using Windows authentication and SQL authentication. SQL authentication is probably the easiest. What you can do is something like:

使用JDBC主要有两种方式——使用Windows身份验证和SQL身份验证。SQL认证可能是最简单的。你能做的是:

String userName = "username";
String password = "password";

String url = "jdbc:sqlserver://MYPC\\SQLEXPRESS;databaseName=MYDB";

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection conn = DriverManager.getConnection(url, userName, password);

after adding sqljdbc4.jar to the build path.

添加sqljdbc4之后。jar到构建路径。

For Window authentication you can do something like:

对于窗口身份验证，您可以执行以下操作:

String url = "jdbc:sqlserver://MYPC\\SQLEXPRESS;databaseName=MYDB;integratedSecurity=true";
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection conn = DriverManager.getConnection(url);

and then add the path to sqljdbc_auth.dll as a VM argument (still need sqljdbc4.jar in the build path).

然后将路径添加到sqljdbc_auth。dll作为VM参数(仍然需要sqljdbc4。jar在构建路径中)。

Please take a look here for a short step-by-step guide showing how to connect to SQL Server from Java using jTDS and JDBC should you need more details. Hope it helps!

如果您需要更多的详细信息，请在这里阅读一篇逐步介绍如何使用jTDS和JDBC从Java连接到SQL Server的简短指南。希望它可以帮助!

#2

You can use this :

你可以用这个:

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 sysobjects where type='u'";
         ResultSet rs = statement.executeQuery(queryString);
         while (rs.next()) {
            System.out.println(rs.getString(1));
         }
      } catch (Exception e) {
         e.printStackTrace();
      }
   }

   public static void main(String[] args)
   {
      ConnectMSSQLServer connServer = new ConnectMSSQLServer();
      connServer.dbConnect("jdbc:sqlserver://<hostname>", "<user>",
               "<password>");
   }
}

#3

If your having trouble connecting, most likely the problem is that you haven't yet enabled the TCP/IP listener on port 1433. A quick "netstat -an" command will tell you if its listening. By default, SQL server doesn't enable this after installation.

如果您的连接有问题，最可能的问题是您还没有启用端口1433上的TCP/IP侦听器。快速的“netstat -an”命令会告诉你它是否在听。默认情况下，SQL server在安装后不会启用它。

Also, you need to set a password on the "sa" account and also ENABLE the "sa" account (if you plan to use that account to connect with).

此外，您需要在“sa”帐户上设置密码，并启用“sa”帐户(如果您打算使用该帐户进行连接)。

Obviously, this also means you need to enable "mixed mode authentication" on your MSSQL node.

显然，这也意味着您需要在MSSQL节点上启用“混合模式身份验证”。

#4

I am also using mssql server 2008 and jtds.In my case I am using the following connect string and it works.

我还在使用mssql server 2008和jtds。在我的例子中，我正在使用下面的连接字符串，它可以工作。

Class.forName( "net.sourceforge.jtds.jdbc.Driver" );
Connection con = DriverManager.getConnection( "jdbc:jtds:sqlserver://<your server ip     
address>:1433/zacmpf", userName, password );
Statement stmt = con.createStatement();

#5

Try to use like this: jdbc:jtds:sqlserver://127.0.0.1/dotcms; instance=instanceName

尝试这样使用:jdbc:jtds:sqlserver://127.0.0.1/dotcms;实例都=

I don't know which version of mssql you are using, if it is express edition, default instance is sqlexpress

我不知道您使用的是哪个版本的mssql，如果是express edition，默认实例是sqlexpress

Do not forget check if SQL Server Browser service is running.

不要忘记检查SQL Server浏览器服务是否正在运行。

#6

You can try configure SQL server:

您可以尝试配置SQL server:

1. Step 1: Open SQL server 20xx Configuration Manager
2. 步骤1:打开SQL server 20xx配置管理器
3. Step 2: Click Protocols for SQL.. in SQL server configuration. Then, right click TCP/IP, choose Properties
4. 步骤2:单击SQL协议。在SQL服务器配置。然后，右键单击TCP/IP，选择Properties
5. Step 3: Click tab IP Address, Edit All TCP. Port is 1433
6. 步骤3:点击tab IP地址，编辑所有TCP。端口是1433

NOTE: ALL TCP port is 1433 Finally, restart the server.

注意:所有TCP端口都是1433，最后重新启动服务器。

#7

Simple Java Program which connects to the SQL Server.

NOTE: You need to add sqljdbc.jar into the build path

注意:您需要添加sqljdbc。jar到构建路径中

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

    public class Conn {
        public static void main(String[] args) throws InstantiationException, IllegalAccessException, ClassNotFoundException {

            Connection conn=null;
            try {
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
                conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=company", "sa", "root");

                if(conn!=null)
                    System.out.println("Database Successfully connected");

            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }

#8

This sample Java program connects to MySQL database using JDBC, executes a query and retrieves and prints the value of the database field.

这个示例Java程序使用JDBC连接到MySQL数据库，执行查询并检索和打印数据库字段的值。

This same sample code can be used to connect to any type of database, all you need to do is change the connection url (dbUrl in the sample). For this code to work properly, you will need to download the mysql driver for JDBC in other words Java Connectors from mysql.com site.

同样的示例代码可以用于连接到任何类型的数据库，您只需更改连接url(示例中的dbUrl)。要使此代码正常工作，您需要从mysql.com站点下载JDBC的mysql驱动程序。

If after downloading the URL it still doesn't work then it is probably due to the classpath. You will have to add the driver jar file in the classpath.

如果在下载URL之后它仍然不能工作，那么它可能是由于类路径。您将不得不在类路径中添加驱动程序jar文件。

import java.sql.*;
import javax.sql.*;

public class jdbcdemo{

public static void main(String args[]){
String dbtime;
String dbUrl = "jdbc:mysql://your.database.domain/yourDBname";
String dbClass = "com.mysql.jdbc.Driver";
String query = "Select * FROM users";

try {

Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection (dbUrl);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);

while (rs.next()) {
dbtime = rs.getString(1);
System.out.println(dbtime);
} //end while

con.close();
} //end try

catch(ClassNotFoundException e) {
e.printStackTrace();
}

catch(SQLException e) {
e.printStackTrace();
}

}  //end main

}  //end class