在Selenuim Automation Test中使用Java连接到MS-SQL服务器

I am trying to connect to MS-SQL server (Microsoft SQL Server 2008 R2) from my Selenium TestNG. I did some research but every where I got the solution for MySQL server. As per my knowledge, there has to be three major difference between coding for MySQL and MS-SQL

我正在尝试从我的Selenium TestNG连接到MS-SQL服务器(Microsoft SQL Server 2008 R2)。我做了一些研究,但我得到了MySQL服务器的解决方案。据我所知,MySQL和MS-SQL编码之间必须有三个主要区别

[Correct me if I am wrong]

[纠正我,如果我错了]

1. Database URL will be

数据库URL将是

public static String DB_URL = "jdbc:sql://11.22.33.44:1234/DBName"; instead of public static String DB_URL = "jdbc:mysql://11.22.33.44:1234/DBName";

public static String DB_URL =“jdbc:sql://11.22.33.44:1234 / DBName”;而不是公共静态字符串DB_URL =“jdbc:mysql://11.22.33.44:1234 / DBName”;

2. Driver String DBClass = "com.sql.jdbc.Driver"; instead of String DBClass = "com.mysql.jdbc.Driver";

   Driver String DBClass =“com.sql.jdbc.Driver”;而不是String DBClass =“com.mysql.jdbc.Driver”;

3. MS-SQL Jar File

   MS-SQL Jar文件

My Question is where can i find MS-SQL Jar file. Also, Am I going correct with above differences?

我的问题是我在哪里可以找到MS-SQL Jar文件。另外,我是否会纠正上述差异?

3 个解决方案

#1

You may download it from here and add it to your class path. If you are using maven, install it into your local repo using the following command first.

您可以从此处下载并将其添加到类路径中。如果您使用的是maven,请先使用以下命令将其安装到本地存储库中。

mvn install:install-file -Dfile=sqljdbc4.jar -DgroupId=com.microsoft.sqlserver -DartifactId=sqljdbc4 -Dversion=4.0 -Dpackaging=jar

Then add the maven dependency to your pom file as below, since the jar does not reside in any of the maven central repositories.

然后将maven依赖项添加到您的pom文件中,如下所示,因为jar不驻留在任何maven*存储库中。

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>sqljdbc4</artifactId>
    <version>4.0</version>
</dependency>

#2

package dbconnection;

   import org.testng.annotations.Test;
   import org.testng.annotations.AfterClass;
   import org.testng.annotations.BeforeClass;

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

public class DatabaseTesingDemo {
    //Connection object
    static Connection connection;
    //Statement object
    private static Statement statement;
    //Constant for DB URL
    //public static String DB_URL = "jdbc:sqlserver://58.2.92.60:1433/RBL_Feb2016";
    public static String DB_URL = "jdbc:sqlserver://localhost:1433/Ashish practice";
    //DB credential
    public static String userName = "sa";
    public static String password = "atyati@123";

@BeforeClass
public void setUp() throws Exception {
    try {
        System.out.println("Before");
        //Make the DB Connection
        String DBClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        Class.forName(DBClass).newInstance();
        //Get Connection DB
        Connection connection =  DriverManager.getConnection(DB_URL, userName, password);
        //Statement object to send the SQL statement to DB
        statement = connection.createStatement();   

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

@Test
public void test() {
    try {
        System.out.println("Executing Test");
        String query = "select * from userinfo where userID='1";
        //Get the content of customer table from DB
        ResultSet resultSet = statement.executeQuery(query);
        //Print the result until all the records are printed
        while(resultSet.next()) {
            System.out.println(resultSet.getString(1));
            System.out.println("\t" + resultSet.getString(2));
        }

    }catch(Exception ee) {
        ee.printStackTrace();           
    }
}

@AfterClass
public void tearDown() throws Exception {
    System.out.println("After");
    //CLose DB Connection
    if(connection != null) {
        connection.close();
    }
}

}

Error log is:

错误日志是:

Before
com.microsoft.sqlserver.jdbc.SQLServerException: The port number 1433/Ashish practice is not valid.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:691)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at dbconnection.DatabaseTesingDemo.setUp(DatabaseTesingDemo.java:32)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:85)
    at org.testng.internal.Invoker.invokeConfigurationMethod(Invoker.java:510)
    at org.testng.internal.Invoker.invokeConfigurations(Invoker.java:211)
    at org.testng.internal.Invoker.invokeConfigurations(Invoker.java:138)
    at org.testng.internal.TestMethodWorker.invokeBeforeClassMethods(TestMethodWorker.java:170)
    at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:104)
    at org.testng.TestRunner.privateRun(TestRunner.java:774)
    at org.testng.TestRunner.run(TestRunner.java:624)
    at org.testng.SuiteRunner.runTest(SuiteRunner.java:359)
    at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:354)
    at org.testng.SuiteRunner.privateRun(SuiteRunner.java:312)
    at org.testng.SuiteRunner.run(SuiteRunner.java:261)
    at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
    at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:86)
    at org.testng.TestNG.runSuitesSequentially(TestNG.java:1215)
    at org.testng.TestNG.runSuitesLocally(TestNG.java:1140)
    at org.testng.TestNG.run(TestNG.java:1048)
    at org.testng.remote.AbstractRemoteTestNG.run(AbstractRemoteTestNG.java:132)
    at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:236)
    at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:81)
Executing Test
java.lang.NullPointerException
    at dbconnection.DatabaseTesingDemo.test(DatabaseTesingDemo.java:47)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:85)
    at org.testng.internal.Invoker.invokeMethod(Invoker.java:639)
    at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:816)
    at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:1124)
    at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:125)
    at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:108)
    at org.testng.TestRunner.privateRun(TestRunner.java:774)
    at org.testng.TestRunner.run(TestRunner.java:624)
    at org.testng.SuiteRunner.runTest(SuiteRunner.java:359)
    at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:354)
    at org.testng.SuiteRunner.privateRun(SuiteRunner.java:312)
    at org.testng.SuiteRunner.run(SuiteRunner.java:261)
    at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
    at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:86)
    at org.testng.TestNG.runSuitesSequentially(TestNG.java:1215)
    at org.testng.TestNG.runSuitesLocally(TestNG.java:1140)
    at org.testng.TestNG.run(TestNG.java:1048)
    at org.testng.remote.AbstractRemoteTestNG.run(AbstractRemoteTestNG.java:132)
    at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:236)
    at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:81)
After
PASSED: test

I am using Microsoft SQL Server 2008 R2.

我正在使用Microsoft SQL Server 2008 R2。

#3

I think your DB name contains a blank space, which would cause the driver to throw this error. Could you please point to a DB which has no blank space characters and let me know how it goes?

我认为您的数据库名称包含一个空格,这将导致驱动程序抛出此错误。你能不能指向一个没有空白字符的数据库,让我知道它是怎么回事?

"jdbc:sqlserver://localhost:1433/**Ashish practice"**