Connect to Microsoft SQL Server in Java

During test automation, we are required to connect to different databases. In this tutorial, we will learn how to connect to Microsoft SQL Server using Java with a sample code snippet. But before that let’s first check the different scenarios in automation where we are required to connect with the database.

Why do we need to connect to databases in automation?

• To get test data – If we automate the database, we can directly fetch the test data from the database and then work on them in test automation script
• To verify result – In automation, we can verify the front end result with backend entry in the database
• To delete test data created – In automation it is good practice to delete the test data created, using database automation, we directly fire the delete query to delete the test data created
• To update certain data – As per the need of test script, the test data can be updated using update query

Connecting to SQL Server in Java

Database automation in SQL Server involves the following steps-

• Loading the required JDBC Driver class, which in our case is com.microsoft.sqlserver.jdbc.SQLServerDriver. You can download the jar from here and add it to your classpath or add it as maven dependency in your pom.xml file, in case you are using a maven project.
  Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver”);
• Creating a connection to the database-
  Connection conn = DriverManager.getConnection(“DatabaseURL”,”UserName”, “Password”);
• Executing SQL queries-
  Statement st = conn.createStatement();
  String Sql = “select * from [tableName] where ”; ResultSet rs = st.executeQuery(Sql);
• Fetching data from result set-
  while (rs.next())
  {
  System.out.println(rs.getString());
  }

Sample code to connect to SQL Server database

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

public class connectingToDBExample {

public static void main(String[] args) throws SQLException, ClassNotFoundException {

//Loading the required JDBC Driver class
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

//Creating a connection to the database
Connection conn = DriverManager.getConnection("jdbc:sqlserver://artOfTesting.com/testDB","userName","password");

//Executing SQL query and fetching the result
Statement st = conn.createStatement();
String sqlStr = "select * from testTable";
ResultSet rs = st.executeQuery(sqlStr);
while (rs.next()) {
System.out.println(rs.getString("name"));
}
}
}

Еще об этом:

SQL For Testers Top 40 SQL Query Interview Questions and Answers for Practice What is Automation Testing? Top RDBMS Interview Questions | DBMS Viva Questions 100+ САМЫХ ПОПУЛЯРНЫХ ВОПРОСОВ И ОТВЕТОВ НА ИНТЕРВЬЮ по SQL Manual Testing Interview Questions and Answers What is a database? Test Scenario – Definition, Template and Examples JAVA TUTORIAL – РУКОВОДСТВО ДЛЯ НАЧИНАЮЩИХ String in Java