SQL Server Automation in Java

Why do we need database automation?

  • To get test data - If we automate the database, we can directly fetch the test data from 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-
    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 a 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"));
		}		
	}
}