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 <condition>”; ResultSet rs = st.executeQuery(Sql);
  
  
• Fetching data from result set-
  while (rs.next())
  {
  System.out.println(rs.getString(<requiredField>));
  }
  
  

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"));
		}		
	}
}